2 Use Goal Seek to achieve a $0 balance by changing the Ticket Price per Person. 6 3 3 Beginning in cell E3, complete the series of substitution values ranging from 200 to 500 at increments of 20 students vertically down column E. 4 3 Enter cell references to the Total Income, Total Expenses, and Balance formulas (in that order) for a one-variable data table in cells F2, G2, and H2 respectively. 5 3 Apply custom number formats to make the formula references appear as the following: F2- Revenue, G2 - Expenses, H2 - Balance. 6 7 Create a one-variable data table for the range E2:H18 using the Number of Attendees as the column input cell. Format the results with Accounting Number Format with two decimal places. 7 4 Copy the Number of Attendees substitution values from the one-variable data table (in cells E3:E18), and then paste the values starting in cell E22. 8 2 Type $50 in cell F21 and complete the series of substitution values from $50 to $100 at $10 increments. 9 7 Enter the cell reference to the Balance formula (C34) in the cell E21 for a two-variable data table, then complete the table using cell B9 as the row input cell and B4 as the column input cell. Format the results with Accounting Number Format with two decimal places. 10 2 Apply a Red, Accent 2, Lighter 60% fill color to the three cells closest to break-even without creating a deficit. 11 Apply custom number format to cell E21 to display # Attend. 2 12 Create a scenario named 500 Attend using the Number of Attendees, Caterer's Meal Cost per Person, Ticket Price per Person, and Ballroom Rental variables as the changing cells. Deselect Prevent changes. Enter these values for the scenario: 500, 15.95, 75, and 12500. 13 5 Create a second scenario named 400 Attend, using the same changing cells. Deselect Prevent changes. Enter these values for the scenario: 400, 17.95, 85, and 12500. 14 5 Create a third scenario named 300 Attend, using the same changing cells. Deselect Prevent changes. Enter these values for the scenario: 300, 19.95, 90, and 11995, respectively. 15 5 Create a fourth scenario named 200 Attend, using the same changing cells. Deselect Prevent changes. Enter these values for the scenario: 200, 22.95, 95, and 11995, respectively. 16 6 Generate a scenario summary report using the Total Income, Total Expenses, and Balance as the results. Mac users should click OK in any error messages that are displayed until the summary report is generated. Created On: 07/05/2019 1 EX16_XL_CH06_GRADER_CAP_AS - Valentine Budget 1.3 Grader - Instructions Excel 2016 Project Step Instructions Points Possible 17 Load the Solver add-in if it is not already loaded. Click the Budget worksheet and launch Solver. Set the objective to calculate the highest balance possible. 18 Use the Number of Attendees and the Ticket Price per Person as changing variable cells. 19 6 Set a constraint so that the Number of Attendees entered in the Input Section of the workbook does not exceed the specified limitation in cell B12. 20 6 Set constraints so that the Ticket Price per Person entered in the Input Section meets the requirements set in the range A14:B15. 21 Set an appropriate integer constraint. 6 22 0 Set a constraint that ensures the Valet Parking expense is less than or equal to the product of the Maximum Parking Stalls and the Valet Parking per Car (in that order). Note, Mac users should NOT perform this step. 23 Solve the problem, but keep the original values in the Budget worksheet. Generate the Answer Report. 24 0 Ensure that the worksheets are correctly named and placed in the following order in the workbook: Scenario Summary, Answer Report 1, Budget. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. A B D E F G H J K L M N 19 Ticket Revenue 30,000 20 Total Income $ 38,500 Two-Variable Data Table: Attendees and Price Per Ticket 21 $ 22 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 32 Total Expenses 33 34 Balance 3,345 12,500 800 3,990 4,575 3,000 2,500 8,380 5,000 $ 44,090 $ (5,590) 35 36 37 38 39 40 41 42 43 44 45 46 47 10 Budget + 2 Use Goal Seek to achieve a $0 balance by changing the Ticket Price per Person. 6 3 3 Beginning in cell E3, complete the series of substitution values ranging from 200 to 500 at increments of 20 students vertically down column E. 4 3 Enter cell references to the Total Income, Total Expenses, and Balance formulas (in that order) for a one-variable data table in cells F2, G2, and H2 respectively. 5 3 Apply custom number formats to make the formula references appear as the following: F2- Revenue, G2 - Expenses, H2 - Balance. 6 7 Create a one-variable data table for the range E2:H18 using the Number of Attendees as the column input cell. Format the results with Accounting Number Format with two decimal places. 7 4 Copy the Number of Attendees substitution values from the one-variable data table (in cells E3:E18), and then paste the values starting in cell E22. 8 2 Type $50 in cell F21 and complete the series of substitution values from $50 to $100 at $10 increments. 9 7 Enter the cell reference to the Balance formula (C34) in the cell E21 for a two-variable data table, then complete the table using cell B9 as the row input cell and B4 as the column input cell. Format the results with Accounting Number Format with two decimal places. 10 2 Apply a Red, Accent 2, Lighter 60% fill color to the three cells closest to break-even without creating a deficit. 11 Apply custom number format to cell E21 to display # Attend. 2 12 Create a scenario named 500 Attend using the Number of Attendees, Caterer's Meal Cost per Person, Ticket Price per Person, and Ballroom Rental variables as the changing cells. Deselect Prevent changes. Enter these values for the scenario: 500, 15.95, 75, and 12500. 13 5 Create a second scenario named 400 Attend, using the same changing cells. Deselect Prevent changes. Enter these values for the scenario: 400, 17.95, 85, and 12500. 14 5 Create a third scenario named 300 Attend, using the same changing cells. Deselect Prevent changes. Enter these values for the scenario: 300, 19.95, 90, and 11995, respectively. 15 5 Create a fourth scenario named 200 Attend, using the same changing cells. Deselect Prevent changes. Enter these values for the scenario: 200, 22.95, 95, and 11995, respectively. 16 6 Generate a scenario summary report using the Total Income, Total Expenses, and Balance as the results. Mac users should click OK in any error messages that are displayed until the summary report is generated. Created On: 07/05/2019 1 EX16_XL_CH06_GRADER_CAP_AS - Valentine Budget 1.3 Grader - Instructions Excel 2016 Project Step Instructions Points Possible 17 Load the Solver add-in if it is not already loaded. Click the Budget worksheet and launch Solver. Set the objective to calculate the highest balance possible. 18 Use the Number of Attendees and the Ticket Price per Person as changing variable cells. 19 6 Set a constraint so that the Number of Attendees entered in the Input Section of the workbook does not exceed the specified limitation in cell B12. 20 6 Set constraints so that the Ticket Price per Person entered in the Input Section meets the requirements set in the range A14:B15. 21 Set an appropriate integer constraint. 6 22 0 Set a constraint that ensures the Valet Parking expense is less than or equal to the product of the Maximum Parking Stalls and the Valet Parking per Car (in that order). Note, Mac users should NOT perform this step. 23 Solve the problem, but keep the original values in the Budget worksheet. Generate the Answer Report. 24 0 Ensure that the worksheets are correctly named and placed in the following order in the workbook: Scenario Summary, Answer Report 1, Budget. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. A B D E F G H J K L M N 19 Ticket Revenue 30,000 20 Total Income $ 38,500 Two-Variable Data Table: Attendees and Price Per Ticket 21 $ 22 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 32 Total Expenses 33 34 Balance 3,345 12,500 800 3,990 4,575 3,000 2,500 8,380 5,000 $ 44,090 $ (5,590) 35 36 37 38 39 40 41 42 43 44 45 46 47 10 Budget +