Excel 2010 Project

e06c1dance.xlsx

Budget

Valentine's Day Ball			One-Variable Data Table: Attendees
Input Section
No. of Attendees	400
% Attendees Using Valet	50%
Cost per Chair Setup	$ 2.00
Valet Parking per Car	$ 19.95
Caterer's Meal Cost per Person	$ 20.95
Ticket Price per Person	$ 75.00
Limitations
Maximum Attendees	500
Maximum Parking Stalls	240
Minimum Ticket Price	$ 50.00
Maximum Ticket Price	$ 100.00
Income
Student Club Contributions	$ 8,500
Ticket Revenue	30,000
Total Income		$ 38,500	Two-Variable Data Table: Attendees and Price Per Ticket
Expenses
Advertising	$ 3,345
Ballroom Rental	12,500
Chairs/Table Setup	800
Valet Parking	3,990
Decorations	4,575
DJ Cost	3,000
Cleanup Costs	2,500
Meal Cost	8,380
Contingency	5,000
Total Expenses		$ 44,090
Balance		$ (5,590)

Your Answers		Question
	1)	What is the ticket price per person to balance the initial budget using Goal Seek?
	2)	For the one-variable data table, how many attendees creates the largest deficit?
	3)	For the one-variable data table, what is the largest deficit?
	4)	For the one-variable data table, how many attendees creates a break-even point?
	5)	For the two-variable data table, what ticket prices do not produce a break-even point?
	6)	For the two-variable data table, list the combination of ticket prices, attendees, and balances that result in positive balances.
	7)	For the scenario summary, which scenario provides the highest positive balance and by what amount?
	8)	For the scenario summary, which scenario provides a negative balance? What is the balance?
	9)	Is this balance close enough that you might achieve break even? How is this possible?
	10)	What is the most profit you can generate within the constraints using Solver?
	11)	How much would you need to charge per ticket to reach that profit level?
	12)	How many total tickets would you need to sell at that price?
	13)	What constraints prevented Solver from reaching a higher profit level?