Shelly Cashman Excel 2016 | Module 9: SAM Project 1a Bartlesville Swim Club FORMULA AUDITING, DATA VALIDATION, AND COMPLEX PROBLEM SOLVING   GETTING STARTED Open the file SC_EX16_9a_FirstLastName_1

Subject:MS ExcelPrice:19.87 Bought3

Share With

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

Bartlesville Swim Club

FORMULA AUDITING, DATA VALIDATION, AND COMPLEX PROBLEM SOLVING

GETTING STARTED

• Open the file SC_EX16_9a_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as SC_EX16_9a_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_9a_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 Analyzegroup (or if the Analyzegroup 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. Louisa Torres is the general manager of the Bartlesville Swim Club in Bartlesville, Oklahoma. As an office assistant at the club, you help Louisa 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 swimming lessons.

Go to the Swim Lesson Sessions worksheet. Correct the first error in this worksheet, a divide by zero error, as follows:

1. Use the Trace Precedents arrows to find the source of the error in cell B15, the income per Guppy class.
2. Correct the error by editing the formula in cell B15, which should divide the income per session (cell B14) by the classes per session (cell B5).
3. Copy the formula and the formatting from cell B15 to the range C15:F15.
4. Correct the Name error in cell B18 as follows:
   1. Use Error Checking to determine the source of the error in cell B18, which should calculate the average income per session.
   2. Correct the error by editing the formula in cell B18.
   3. Go to the Session Enrollment worksheet. To make it easier to add the correct session enrollment information, add data validation to the range B8:F10 as follows:
      1. The cells in the range B8:F10 should allow only whole number values greater than 0.
      2. Add an Input Message using Session Enrollment as the Input Message title and Enter the minimum, average, or maximum enrollment per lesson type. (including the period) as the Input message.
      3. Add an Error Alert using the Stop style for the Error Alert, and use Enrollment Error as the Error Alert title and Session enrollment must be greater than 0. (including the period) as the Error message.
      4. Louisa thinks the club can make as much as $2,000 in income per session from the Otter lessons. Use Goal Seek to determine how to achieve this goal as follows:
         1. Set the maximum session income for Otter lessons (cell D15) to 2,000.
         2. Change the session fee for Otter lessons (cell D4) to determine the fee the club needs to charge to achieve the income goal.
         3. Louisa also wants to make at least $620 in income per session from the Seal lessons. Use Goal Seek to determine how to achieve this goal as follows:
            1. Set the minimum session income for Seal lessons (cell C13) to 620.
            2. Change the minimum session enrollment for Seal lessons (cell C8) to determine the minimum number of Seal students the club needs to achieve the income goal.
            3. Louisa wants to average $1,200 in income per session from the Guppy lessons. Use Goal Seek to determine how to achieve this goal as follows:
               1. Set the average session income for Guppy lessons (cell B14) to 1,200.
               2. Change the average session enrollment for Guppy lessons (cell B9) to determine the average number of Guppy students the club needs to achieve the income goal.
               3. Go to the Current Rates worksheet. Louisa has already created a scenario named Max Attendance that calculates profit based on the maximum number of students attending each session. Add a new scenario to compare the profit with average attendance as follows:

1.Use Average Attendance as the scenario name.

2. Use B9:F9 as the changing cells.

3. Accept the current values in the range B9:F9 as the values for the changing cells because these cells show the average number of students per session.

4. Add another new scenario to compare the profit with low class attendance as follows:

1. Add another scenario to the workbook, using Low Attendance as the scenario name.

2. Use B9:F9 as the changing cells.

3. Update the cell values in the range B9:F9 to match the low attendance values shown in bold in Table 1 below:

Table 1: Cell Values for the Low Attendance Scenario

1. Show the Max Attendance scenario values in the Current Rates worksheet.
2. Go to the New Rates worksheet, which contains three scenarios showing the profit with a $10 or $15 rate increase or a $10 rate decrease. Compare the average profit per session based on the scenarios as follows:
   1. Create a Scenario Summary report using the range B11:F11 as the result cells to show the average profit per session depending on the rate changes. (Hint: The defined names of the range B11:F11 appear in the report.)
   2. Use New Rates Scenario Report as the name of the worksheet containing the report.
   3. Louisa also wants to focus on one or two types of lessons at a time when comparing the average profit per session. Return to the New Rates worksheet and create another type of report as follows:
      1. Create a Scenario PivotTable report using the range B11:F11 as the result cells to compare the average profit per session depending on the rate changes in a PivotTable. (Hint: The defined names of the range B11:F11 appear in the report.)
      2. Use New Rates PivotTable as the name of the worksheet containing the PivotTable.
      3. 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.)
      4. Go to the New Schedule worksheet. Louisa wants to determine the number of morning and afternoon classes the club can offer to make the highest weekly profit without exceeding the maximum legal pool capacities, meeting the swim team scheduling needs, and considering other practical conditions. Use Solver to find this information as follows:
         1. Use cell G17 (Total_Weekly_Profit) as the objective cell in the Solver model, with the goal of determining the maximum value for that cell.
         2. Use the range B5:F6, which shows the number of morning and afternoon swim classes, as the changing variable cells.
         3. Use the constraints shown in Table 2 below.

Table 2: Solver Constraints

1. Use Simplex LP as the solving method.
2. Save the Solver model in cell A27.
3. Solve the model, keeping the Solver solution.
4. Louisa 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:
   1. Solve the model again, this time choosing to produce an Answer report.
   2. Use New Schedule Answer Report as the name of the worksheet containing the Answer report.
   3. 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 value in cells D4, C8, and B9 of the Session Enrollment worksheet generated by the Goal Seek Analyses has intentionally been blurred out in the Final Figure.) 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: Swim Lesson Sessions Worksheet

Final Figure 2: Session 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