You are on the budget committee for the formal Valentines Day Ball at your university. The ball includes dinner and dancing. Your committee have prepared a tentative budget outlining income and expenses in the attached Dance.xlsx file. The primary sources of income are contributions from student organizations 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. Currently, the estimated budget has a deficit. Use Goal Seek to achieve a $0 balance by changing the ticket price per person. Answer question 1 on the Q&A worksheet. 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 based on different number of students attending. Complete the one-variable data table. There are four columns: # Attend, Total Revenue, Total Expenses, and Balance. In the first column, enter the series of substitution values ranging from 200 to 500 at increments of 20 students. Enter references to the total revenue, total expenses, and balance formulas in the correct location for the one-variable data table. Complete the one-variable data table and answer questions 2 through 4 on the Q&A worksheet. Now you want to compare the balance of different combinations of attendees and ticket prices per person using a two-variable data table. Using the same series of substitution values for # Attend from the one-variable data table. Enter the series of substitution values for ticket price from $50 to $100 at $10 increments. Enter the reference to the total income formula in the correct location for the two variable data table. Complete the two-variable data table and answer questions 5 and 6 on the Q&A worksheet. Question 6 requires three combinations to list. (Optional, 10 extra points if you complete this part correctly, no partial points)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. You use Scenario Manager to help you decide the target number of attendees. Create a first scenario named 500 Attend, using the number of attendees, meal cost per person, ticket price per person, and ballroom rental variables as the changing cells. Enter these values for the first scenario: 500, $15.95, $75 and $12500. Create a second scenario named 400 Attend, using the same changing cells, entering these values for the second scenario: 400, $17.95, $85, and $12500. Create a third scenario named 300 Attend, using the same changing cells, entering these values for the third scenario: 300, $19.95, $90, and $11995. Create a fourth scenario named 200 Attend, using the same changing cells, entering these values for the fourth scenario: 200, $22.95, $95, and $11995. Then generate a scenario summary report using the total revenue, total expenses, and balance as the results, and then answer question 7 through 9 on the Q&A worksheet.

File Home Insert Page Layout Formulas Data Review View Help FOXIT PDF Power PivotTell me what you want to Cut Wrap Text Merge & Center Calibri 11A A General s-96 , 58. 8 Conditional Forma Formatting Table Paste 00 +.0 Format Painter Clipboard Font Alignment Number R3 Question What is the ticket price per person to balance the initial budget using Goal Seek? For the one-variable data table, how many attendees creates the largest deficit? For the one-variable data table, what is the largest deficit? Your Answers 1) 2) 3) 5) 6) 4 5 4) For the one-variable data table, how many attendees creates a break-even point? For the two-variable data table, what ticket prices do not produce a break-even point? For the two-variable data table, list the combinations of ticket prices and attendees that start generating profit. 7) For the scenario summary, which scenario provides the highest positive balance and by 8 For the scenario summary, which scenario provides a negative balance? What is the 9) Is this balance close enough that you might achieve break even? How is this possible? 10 what amount? 11 balance? Assignment 7 - Excel Review FOXIT PDF Power Pivot Tell me what you want to do Clear Reapply Advanced Columns Fill Duplicates Validation File Home Insert Page Layout Formulas Data Show Queries EEE From Table Recent Sources View Help Connections Properties ZIA From From From From Other Existi Refresh All- ZI Sort Filter Text to Flash Remove Data C Access Web Text Sources Cnnections Query a, Edit Links Get External Data Get & Transfornm Connections Sort & Filter Data Toc B9 Xf75 Valentine's Day Ball One-Variable Data Table: Attendees # Attend Input Section 4 No. of Attendees 5 % Attendees using Valet 6 Cost per Chair Setup 7 Valet Parking per Car 8 Caterer's Meal Cost per Person 20.95 9 Ticket Price per Person 10 2.00 19.95 $75.00 Limitations 12 Maximum Attendees 13 Maximum Parking Stalls 14 Minimum Ticket Price 15 Maximum Ticket Price 16 17 $50.00 100.00 Income 18 Student Club Contributions 19 Ticket Revenue 20 Total Income 21 8,500 30,000 $38,500 Two-Variable Data Table: Attendees and Price Per Ticket Expenses 23 Advertising 24 Ballroom Rental 25 Chairs/Table Setup 26 Valet Parking 27 Decorations 28 DJ Cost 29 Cleanup Costs 30 Meal Cost 31 Contingency 2 Total Expenses 3,345 800 3,990 4,575 3,000 8,380 5,000 $44,090 $(5,590) 34 35 36 37 Balance BudgetQ&A File Home Insert Page Layout Formulas Data Review View Help FOXIT PDF Power PivotTell me what you want to Cut Wrap Text Merge & Center Calibri 11A A General s-96 , 58. 8 Conditional Forma Formatting Table Paste 00 +.0 Format Painter Clipboard Font Alignment Number R3 Question What is the ticket price per person to balance the initial budget using Goal Seek? For the one-variable data table, how many attendees creates the largest deficit? For the one-variable data table, what is the largest deficit? Your Answers 1) 2) 3) 5) 6) 4 5 4) For the one-variable data table, how many attendees creates a break-even point? For the two-variable data table, what ticket prices do not produce a break-even point? For the two-variable data table, list the combinations of ticket prices and attendees that start generating profit. 7) For the scenario summary, which scenario provides the highest positive balance and by 8 For the scenario summary, which scenario provides a negative balance? What is the 9) Is this balance close enough that you might achieve break even? How is this possible? 10 what amount? 11 balance? Assignment 7 - Excel Review FOXIT PDF Power Pivot Tell me what you want to do Clear Reapply Advanced Columns Fill Duplicates Validation File Home Insert Page Layout Formulas Data Show Queries EEE From Table Recent Sources View Help Connections Properties ZIA From From From From Other Existi Refresh All- ZI Sort Filter Text to Flash Remove Data C Access Web Text Sources Cnnections Query a, Edit Links Get External Data Get & Transfornm Connections Sort & Filter Data Toc B9 Xf75 Valentine's Day Ball One-Variable Data Table: Attendees # Attend Input Section 4 No. of Attendees 5 % Attendees using Valet 6 Cost per Chair Setup 7 Valet Parking per Car 8 Caterer's Meal Cost per Person 20.95 9 Ticket Price per Person 10 2.00 19.95 $75.00 Limitations 12 Maximum Attendees 13 Maximum Parking Stalls 14 Minimum Ticket Price 15 Maximum Ticket Price 16 17 $50.00 100.00 Income 18 Student Club Contributions 19 Ticket Revenue 20 Total Income 21 8,500 30,000 $38,500 Two-Variable Data Table: Attendees and Price Per Ticket Expenses 23 Advertising 24 Ballroom Rental 25 Chairs/Table Setup 26 Valet Parking 27 Decorations 28 DJ Cost 29 Cleanup Costs 30 Meal Cost 31 Contingency 2 Total Expenses 3,345 800 3,990 4,575 3,000 8,380 5,000 $44,090 $(5,590) 34 35 36 37 Balance BudgetQ&A