In the following project, you will perform What-If Analysis tocalculate budget information for your universitys Valentines Dayformal.
Question:
In the following project, you will perform What-If Analysis tocalculate budget information for your university’s Valentine’s Dayformal.
Steps to Perform:
Step | Instructions | Points Possible |
---|---|---|
1 | Download and open the file namedexploring_e06_grader_Capstone2.xlsx. Save the workbook aschapter6_LastFirst, replacing LastFirst with yourown name. | 0 |
2 | Use Goal Seek to achieve a $0 balance bychanging the Ticket Price per Person. | 6 |
3 | Beginning in cell E3, complete the series of substitution valuesranging from 200 to 500 atincrements of 20 students vertically down columnE. | 3 |
4 | Enter cell references to the Total Income, Total Expenses, andBalance formulas (in that order) for a one-variable data table incells F2, G2, and H2 respectively. | 3 |
5 | Apply custom number formats to make the formula referencesappear as the following: F2- Revenue, G2 –Expenses, H2 – Balance. | 3 |
6 | Create a one-variable data table for the range E2:H18 using theNumber of Attendees as the column input cell. | 7 |
7 | Copy the Number of Attendees substitution values from theone-variable data table (in cells E3:E18), and then paste thevalues starting in cell E22. | 4 |
8 | Type $50 in cell F21 and complete the series ofsubstitution values from $50 to $100 at$10 increments. | 2 |
9 | Enter the cell reference to the Balance formula (C34) in thecell E21 for a two-variable data table, then complete the tableusing cell B9 as the row input cell and B4 as the column inputcell. | 7 |
10 | Apply a Red, Accent 2, Lighter 60% fill color to the three cellsclosest to break-even without creating a deficit. | 2 |
11 | Apply custom number format to cell E21 to display #Attend. | 2 |
12 | Create a scenario named 500 Attend using theNumber of Attendees, Caterer’s Meal Cost per Person, Ticket Priceper Person, and Ballroom Rental variables as the changingcells. | 5 |
13 | Create a second scenario named 400 Attend,using the same changing cells. | 5 |
14 | Create a third scenario named 300 Attend, usingthe same changing cells. | 5 |
15 | Create a fourth scenario named 200 Attend,using the same changing cells. | 5 |
16 | Generate a scenario summary report using the Total Income, TotalExpenses, and Balance as the results. | 6 |
17 | Load the Solver add-in if it is not already loaded. Click theBudget worksheet and launch Solver. | 6 |
18 | Use the Number of Attendees and the Ticket Price per Person aschanging variable cells. | 6 |
19 | Set a constraint so that the Number of Attendees entered in theInput Section of the workbook does not exceed the specifiedlimitation in cell B12. | 6 |
20 | Set constraints so that the Ticket Price per Person entered inthe Input Section meets the requirements set in the rangeA14:B15. | 6 |
21 | Set an appropriate integer constraint. | 6 |
22 | Set a constraint that ensures the Valet Parking expense is lessthan or equal to the product of the Maximum Parking Stalls and theValet Parking per Car (in that order). | 0 |
23 | Solve the problem, but keep the original values in the Budgetworksheet. | 5 |
24 | Ensure that the worksheets are correctly named and placed in thefollowing order in the workbook: Scenario Summary, Answer Report 1,Budget. | 0 |
Total Points | 100 |
Valentine's Day BallOne-Variable Data Table: Attendees
Input Section
No. of Attendees400
% Attendees Using Valet50%
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,500Two-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,59
FOR REFERENCE, PLEASE GIVE STEP-By-STEP SOLUTION.
https://www.dropbox.com/s/y0m8nm0wuk1f2ly/Practice.xlsx?dl=0
Data Analysis and Decision Making
ISBN: 978-0538476126
4th edition
Authors: Christian Albright, Wayne Winston, Christopher Zappe