Excel Test, Practiacl Portion

Fall 2004

• You may print these instructions if you wish.
• You may use your book and a single page of notes.
• Make sure that you save often.
• If you get stuck, enter a number and continue with the next section.
• At the end of class, print your spreadsheet in normal and formula view.

You have decided that you wish to start a catering service for Thanksgiving dinner. You wish to build a spreadsheet to help decide if this will allow you to make any money. Since you are going to work out of your home you will only consider the cost of food.

• Start with this worksheet
• All cells should be appropriately formatted.
• Place your name in cell A1
• Put the date in cell E1
• Merge Cells A2 and B2
• Merge Cells A3 and B3
• Put a 10 in cell c2, this is the number of guests you expect to serve, All computations involving the number of guests shoul refer to this cell.
• Put 7.35 in cell C3, this is the price you will charge for each guest you serve. Any computation involving this number should reference this cell.
• Format the information in cells A4 through F5 as in the example below.
• In A6 through F10 you will plan your menu.
  • Column A will be used to indicate if you wish to order the item in conlumn b. Put a y if you want that item, and an n if you dont. Put all y's in there for now, except for Stuffing, put a n in that cell, (who wants ickey stuffing anyway?).
  • Column B is the item you will prepare, you don't need to change any information in this column.
  • Column C is used to compute the number of items you need to order. Find the number for this column by looking up the item in column B in the chart Starting in cell f21
  • Column D is the actual number of items you need to order. This should be baised on the number of guests and the servings per unit. Since you can't order fractional number of items, you should use the ROUNDUP function in your computation.
  • Column E contains the actual number of items to order. This just a copy of column D if column A contains a Y, but 0 if it does not.
  • Column F is the price you pay for each item, this will include a lookup in the table starting at F21 and the units ordered from column e.
  • Place a border around this chart.
• In cell F12 compute the total cost of the items ordered
• In Cell F13 compute the tax baised on the rate in cell g18
• In cell F14 compute the total cost of the meal.

• In cell G2 compute your gross profits (based on guests and price per guest)
• In cell G3 compute your net profits (baised on the gross profits and the Final Cost)

• Complete the labels and format the two charts.
• The first chart should compute the monthly payment required for each interest rate, for each period (careful here), baised on the final cost of the meal.
• The second chart shows the final interst paid, by multiplying the monthly payment by the number of months and subtracting the final cost