Shelly Cashman Excel 2016 | Module 9: SAM Project 1b Rio Grande Music Camp FORMULA AUDITING, DATA VALIDATION, AND COMPLEX PROBLEM SOLVING   GETTING STARTED Open the file SC_EX16_9b_FirstLastName_1

Subject:MS ExcelPrice:20.01 Bought7

Share With

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

Rio Grande Music Camp

FORMULA AUDITING, DATA VALIDATION, AND COMPLEX PROBLEM SOLVING

 

GETTING STARTED

Open the file SC_EX16_9b_FirstLastName_1.xlsx, available for download from the SAM website.

Save the file as SC_EX16_9b_FirstLastName_2.xlsx by changing the “1” to a “2”.

If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.

With the file SC_EX16_9b_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.

If cell B6 does not display your name, delete the file and download a new copy from the SAM website

This project requires you to use the Solveradd-in. If this add-in is not available on the Data tab in the Analysisgroup (or if the Analysisgroup is not available), install Solver as follows:

In Excel, click the File tab on the ribbon, and then click the Options tab in the left pane of Backstage view.

Click the Add-Ins tab in the left pane of the Excel Options dialog box.

Click the Manage arrow, click Excel Add-Ins in the Manage list, and then click the Go button.

In the Add-Ins dialog box, click the Solver Add-In check box, and then click the OK button.

Follow any remaining prompts to install Solver.

 

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.

11.     Alison also wants to focus on one or two types of lessons at a time when comparing the average profit per program. Return to the New Rates worksheet and create another type of report as follows:

a.        Create a Scenario PivotTable report using the range B10:F10 as the result cells to compare the average profit per program depending on the rate changes in a PivotTable. (Hint: The defined names of the range B10:F10 appear in the report.)

b.        Use New Rates PivotTable as the name of the worksheet containing the PivotTable.

c.         Format cells B4:F6 in the New Rates PivotTable worksheet using the Accounting number format with 0 decimal places and $ as the symbol. (Hint: Depending on how you complete this substep, the number format may appears as Custom.)

12.     Go to the New Schedule worksheet. Alison wants to determine the number of morning and afternoon lessons the camp can offer to make the highest weekly profit without exceeding the maximum room capacities, meeting the ensembles scheduling needs, and considering other practical conditions. Use Solver to find this information as follows:

a.        Use cell G16 (Total_Weekly_Profit) as the objective cell in the Solver model, with the goal of determining the maximum value for that cell.

b.        Use the range B4:F5, which shows the number of morning and afternoon music lessons, as the changing variable cells.

c.         Use the constraints shown in Table 2 below.

Table 2: Solver Constraints

 

Constraint	Cell or Range
Each type of lesson is scheduled at least once in the morning and once in the afternoon	B4:F5
Each morning and afternoon lesson value is an integer	B4:F5
Each type of lesson is scheduled 2 times per week or more	B6:F6
Each type of lesson is scheduled 15 times per week or less	B6:F6
The total number of morning lessons is 10 or less	Total_Morning_Lessons
The total number of afternoon lessons is 15 or less	Total_Afternoon_Lessons
The total number of lessons per week is 20	Total_Weekly_Lessons
Ensemble morning lessons are scheduled 5 times per week or less	Group_Morning_Lessons
Ensemble afternoon lessons are scheduled 8 times per week or less	Group_Afternoon_Lessons
The total number of ensemble lessons scheduled per week is 15 or less	Group_Total_Lessons

d.        Use Simplex LP as the solving method.

e.        Save the Solver model in cell A26.

f.          Solve the model, keeping the Solver solution.

13.     Alison wants to document the answer Solver found, including the constraints and a list of the values Solver changed to solve the problem. Produce an Answer report for the Solver model as follows:

a.        Solve the model again, this time choosing to produce an Answer report.

b.        Use New Schedule Answer Report as the name of the worksheet containing the Answer report.

14.     Mark the workbook as Final to indicate you are finished revising the workbook.

Your workbook should look like the Final Figures on the following pages. (The values in cells D3, C7, and B8 of the Program Enrollment worksheet generated by Goal Seek Analysis has intentionally been blurred out in Final Figure 2.) The New Schedule Answer Report and New Schedule worksheets are not provided to prevent manual entry of the Solver results.Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.

 

Final Figure 1: Music Programs Worksheet

 

Final Figure 2: Program Enrollment Worksheet

 

Final Figure 3: Current Rates Worksheet

 

Final Figure 4: New Rates Scenario Report Worksheet

 

Final Figure 5: New Rates PivotTable Worksheet

 

Final Figure 6: New Rates Worksheet