Shelly Cashman Excel 2016 | Module 9: SAM Project 1b | Help in Homework |

24 / 7 Live Support
[email protected]
+44-7309-655-268
+91-9828671065

PROJECT STEPS
1. Alison Silverstein is the director of the Rio Grande Music Camp in the outskirts of Boulder, Colorado. As an office assistant at the camp, you help Alison gather and analyze financial data. She asks for your help completing an Excel workbook that contains errors and missing information. She also wants to make it easier to enter correct data and determine the financial effects of changing the rates and schedules for music lessons.
Go to the Music Programs worksheet. Correct the first error in this worksheet, a divide by zero error, as follows:
a. Use the Trace Precedents arrows to find the source of the error in cell B14, the income per Novice lesson.
b. Correct the error by editing the formula in cell B14, which should divide the income per program (cell B13) by the lessons per program (cell B4).
c. Copy the formula and the formatting from cell B14 to the range C14:F14.
2. Correct the Name error in cell B17 as follows:
a. Use Error Checking to determine the source of the error in cell B17, which should calculate the average income per program.
b. Correct the error by editing the formula in cell B17.
3. Go to the Program Enrollment worksheet. To make it easier to add the correct program enrollment information, add data validation to the range B7:F9 as follows:
a. The cells in the range B7:F9 should allow only whole number values greater than 0.
b. Add an Input Message using Program Enrollment as the Input Message title and Enter the minimum, average, or maximum enrollment per lesson type. (including the period) as the Input message.
c. Add an Error Alert using the Stop style for the Error Alert, and use Enrollment Error as the Error Alert title and Program enrollment must be greater than 0. (including the period) as the Error message.
4. Alison thinks the camp can make as much as $20,000 in program income from the Level II lessons. Use Goal Seek to determine how to achieve this goal as follows:
a. Set the maximum program income for Level II lessons (cell D14) to 20,000.
b. Change the program fee for Level II lessons (cell D3) to determine the fee the camp needs to charge to achieve the income goal.
5. Alison also wants to make at least $8,000 in income per program from the Level I lessons. Use Goal Seek to determine how to achieve this goal as follows:
a. Set the minimum program income for Level I lessons (cell C12) to 8000.
b. Change the minimum program enrollment for Level I lessons (cell C7) to determine the minimum number of Level I students the camp needs to achieve the income goal.
6. Alison wants to average $15,000 in income per program from the Novice lessons. Use Goal Seek to determine how to achieve this goal as follows:
a. Set the average program income for Novice lessons (cell B13) to 15,000.
b. Change the average program enrollment for Novice lessons (cell B8) to determine the average number of Novice students the camp needs to achieve the income goal.
7. Go to the Current Rates worksheet. Alison has already created a scenario named Max Enrollment that calculates profit based on the maximum number of students enrolled for each program. Add a new scenario to compare the profit with average enrollment as follows:
a. Use Average Enrollment as the scenario name.
b. Use B8:F8 as the changing cells.
c. Accept the current values in the range B8:F8 as the values for the changing cells because these cells show the average number of students per program.
8. Add another new scenario to compare the profit with low program enrollment as follows:
a. Add another scenario to the workbook, using Low Enrollment as the scenario name.
b. Use B8:F8 as the changing cells.
c. Update the cell values in the range B8:F8 to match the low enrollment values shown in bold in Table 1 below:
Table 1: Cell Values for the Low Attendance Scenario
Cell New Value
Novice_New_Students (B8) 16
Level_I_New_Students (C8) 20
Level_II_New_Students (D8) 18
Level_III_New_Students (E8) 18
Advanced_New_Students (F8) 20
9. Show the Max Enrollment scenario values in the Current Rates worksheet.
10. Go to the New Rates worksheet, which contains three scenarios showing the profit with a $20 or $25 rate increase or a $10 rate decrease. Compare the average profit per program based on the scenarios as follows:
a. Create a Scenario Summary report using the range B10:F10 as the result cells to show the average profit per program depending on the rate changes. (Hint: The defined names of the range B10:F10 appear in the report.)
b. Use New Rates Scenario Report as the name of the worksheet containing the report.