Party worksheet: You are going to have a party for 210 people. You have decided to hire a caterer to prepare the food for you. You have done some research and have selected your caterer who has given you the following prices. The prices are PER PERSON. Format the costs in currency. Setup the Party spreadsheet with appropriate row and column headings.

Snacks 2.36 Main Dish 8.25 Veggies 2.50

Soup 1.80 Salad 1.99 Dessert 2.90

Formulas: Place the number of guests in any open cell. Make this cell reference an absolute cell reference when making calculations. (Use function formulas and cell references to make all calculations)

Total for snacks for all guests

Total for Main Dish for all guests

Total for Veggies for all guests

Total for Soup for all guests

Total for Salad for all guests

Total for Dessert for all guests

Total food bill for all guests

You will need to pay a 20% tip based on the total food bill. How much is the tip? How much is the total food cost with tip?

The caterer is also offering you a first time customer discount of 22% off of the food bill (not including tip amount). What is the new cost based on this discount?

IF and PMT Functions: Based on this information you now need to determine the party cost.

The party is expensive and you have decided that if food is going to cost more than $3000 you will eliminate 50 guests if less than $3000 you will only eliminate 25 guests. What is the new guest total? NOTE: You do not need to recalculate previous food totals, tip, etc. Just include the new guest total.

You also want to know which food item to order from a wholesaler. You have decided that if individual food item costs more than 2.40 per person you will order from the distributor, otherwise you will order from the caterer. Write this formula so either the word distributor or caterer appears in the cell.

You do not have enough money in savings to pay for this party. You have decided to take out a loan to pay for the party. You are going to borrow $5000 from the bank so you can pay for the food, room rental and new clothing. The bank will charge you 9.9% interest and give you a 3 year loan. What will your monthly payment be?

Format the Worksheet as follows:

Insert a new Row 1 and add the title Party Planning.Center the title across the top, background shade blue with white font, size 24.

Change all appropriate data to currency with a floating dollar sign and highlight the grand total in yellow.

Adjust column widths as necessary.

Add your name to cell A20 (or any available empty cell in column A)

Rename Sheet 1 to Party Costs and change the tab color to red.

Create a pie chart with whatever information you feel is relevant. Move the chart to its own sheet. Make sure that the labels and percentages show in the chart. Remove the legend. Be creative with the colors, format, etc. of the pie chart.