Excel Spreadsheet Assignment 2
BICS 363
Spring, 2003
Due Week Of 03-11-2003

Assignment:
Prepare a spreadsheet to calculate "break-even" price for cattle feeders.

• Name the file usernameF (Username is your user name.) If your username is ABC1234, name your file ABC1234F. Excel will automatically append ".XLS" as an extension. There will be an automatic one point deduction for improper file names.
• Submit your assignment on disk with a hard copy printout. No points will be given for files that do not work.
• Start with an attractive title for your sheet.
• Place you name and userid below the title.
• Be sure that all cells are appropriately formatted--2 decimal places for money amounts, integers for days on feed, etc.
• Include units with each input and output text item. That makes sure the user knows how data should be entered -- prices in $/cwt, etc.

Input Information: (values you enter)

• Weight (lbs) into the lot
• Incoming price (dollars per 100 lbs.(cwt)). You can either use a delivered price, or treat commission and trucking separately.
• Weight (lbs) out of the lot.
• Estimated rate of gain (lbs. per day)
• Date into the feedyard (mm/dd/yy format)
• Percent death loss (about .5% for steers, 1% for heifers)
• Interest rate (annual percent). You can use separate interest rates for cattle and feed if you choose.
• Cost of gain, dollars per 100 lbs. This figure may include yardage and feed mark-up, or you can calculate them separately.

Output Information: (values calculated by the formulas)

• Days on feed (lbs gained/gain per day)
• Cost of the animal (Incoming price * weight into the lot)
• Date out of the feedyard (date in + days on feed)
• Cost of gain (cost per lb. of gain * lbs gained)
• Interest cost (a good estimate is interest on the animal plus interest on half the feed)
• Death loss allowance (Assume an animal dies about half way through the feeding period. If death loss is assumed at 1%, the allowance is 1% of the delivered cost of the animal plus 1% of half the feed.) You can calculate the lost interest on the animal and feed if you wish.
• Total cost of the finished animal
• "Break even" sale price in dollars per 100 lbs.
• Protect your formula (output) cells so that they cannot be changed accidentally. However, DO NOT protect input cells.
• Use appropriate formatting for all input and output cells
• Make your spreadsheet "user friendly" and readable.
• If you use colors to highlight cells, be sure that your text remains readable. Pastels work better than more saturated colors.
• Be sure to apply a "reality check" to all calculations. Calculations that are obviously unrealistic will result in serious discounts.

What If?

• Once you get your spreadsheet completed, examine its utility by changing values for your inputs and observe their effect on the "bottom line" break-even price.
• Try out the Goal Seek function (under Tools on the Main Menu) to run the calculation backward. For example, choose a break-even price and use goal seek to find what the feeder price would need to be if all other costs remained constant.

Here's an example. If your formulas are correct, you should get these numbers.

Grading will be based on at least the following:

• Is the sheet  usable, readable and accuracte?.
• Are all output values  based on formulas? Constants in the output section defeat the purpose of the system and will result in serious grade discounts.
• Are the input cells unlocked and the output cells locked?
• Are the formulas correct? Do they pass a "reality check?"
• Does the sheet recalculate properly? (change in inputs produces appropriate changes in outputs)?
• Is the spreadsheet easily readable? (do background colors make text hard to see?)