As a student at USI and a member of the budget committee for theMadrigal Feaste, you have been given a partially completed workbook, MadrigalFeaste.xlsx, and asked to complete the project. (Download and save the file as LastNameFirstNametudent_MadrigalFeaste.xlsx)(that's YOUR last and first name, not the words LastNameFirstName!). The Madrigal Feasteincludes dinner and dancing. Your committee prepared a tentative budget outlining income and expenses. The primary sources of income are the University's contributions and ticket prices. Expenses include the actual cost of the dinner, facilities, parking, and other costs at a luxurious hotel in the city. Your goal is to balance the income and expenses, decide on the most appropriate ticket price per student, and ensure your budget falls within the limitations you must work with.
1. Calculate the "Maximum number of Valet Parking Stalls.
Goal Seek
Currently the estimated budget has a deficit. The fastest way to try to reconcile the income and expenses is to use Goal Seek. The goal is to break even, that is, to have a zero balance. Your instinct is to adjust the ticket price per person to reach the goal.
a. Use Goal Seek to achieve a $0 balance by changing the ticket price per person.
One-Variable Data Table
You believe that between 200 and 500 students will attend. Because the ticket revenue, chair setup, catering cost, and valet parking expenses are dependent on the number of students, you decide to create a one-variable data table to compare the budget effects (Total Income, Total Expenses and Balance) based on different numbers of students attending.
a. Use increments of 20 to complete the series from 200 to 500 vertically in column E.
b. Enter the appropriate references to the dependent variables in the correct location for a one-variable data table.
c. Complete the one-variable data table, and then format the results with Accounting Number Format with two decimal places.
d. Apply custom number formats to make the formula references appear as descriptive column headings. Format the headings and substitution values.
Two-Variable Data Table
The break-even point for the one-variable data table is identical to the current model because all other variables are held constant. You want to compare the balances of different combinations of attendees varying from 200 to 500 in increments of 20 and ticket prices varying from 50 to 100 in 10 dollar increments. This will require you to use a two-variable data table based on Balance..
a. Use increments of 20 to complete the series from 200 to 500 vertically in column E.
b. Use increments of 10 to complete the series from 50 to 100 horizontally in row 21.
c. Enter the reference to the "based on" variable in the correct location for a two variable table
d. Complete the two-variable data table, and then format the results with Accounting Number Format with two decimal places.
e. Apply a fill color to the cells in the table that show a positive balance.
f. Apply a custom number format to make the formula reference appear as a descriptive column heading. Format the headings and substitution values.
Scenario Manager
You negotiated different cost per meal and ballroom rental rates based on 500, 400, 300, or 200 attendees. You estimated tentative ticket prices per attendee. To help you decide the target number of attendees, you need to use Scenario Manager.
a. Create four scenarios based on the following data and generate a summary report using the total revenue, total expenses and balance as the results:
Meal Ticket Ballroom
Attend Cost Price Rental
500 15.95 75 12500
400 17.95 85 12500
300 19.95 90 11995
200 22.95 95 11995
b. Generate a scenario summary report using total revenue, total expenses, and balance as the results.
c. Clean up the summary as discussed in the book.
Use Solver
You realize a perfect break-even point may be unrealistic, but if you maximize the ending balance, you will donate any remaining balance to charity. For this analysis, you will use Solver to keep the expenses constant while changing the number of attendees and ticket price per person.
a. Maximize the Balance.
b. Use the number of attendees and the ticket price per person as changing variable cells.
c. Set a constraint for the number of attendees.
d. Set constraints for the ticket price per person.
e. Set an appropriate integer constraint.
f. Set a constraint that ensures the valet parking expense is less than or equal to the number of valet parking spaces times the valet price per vehicle.
g. Solve the problem, but keep the original values in the Budget worksheet.
h. Generate the Answer Report and Name the Sheet "Solver Answer Report".
You must turn in your assignment in class, on the "N" drive Friday September 20, 2013 whenever I make the folders available during the last five minutes of class.
Attachment:- 351473_1_MadrigalFeaste.xlsx