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
- 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:
- Use the Trace Precedents arrows to find the source of the error in cell B15, the income per Guppy class.
- 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).
- Copy the formula and the formatting from cell B15 to the range C15:F15.
- Correct the Name error in cell B18 as follows:
- Use Error Checking to determine the source of the error in cell B18, which should calculate the average income per session.
- Correct the error by editing the formula in cell B18.
- 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:
- The cells in the range B8:F10 should allow only whole number values greater than 0.
- 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.
- 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.
- 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:
- Set the maximum session income for Otter lessons (cell D15) to 2,000.
- Change the session fee for Otter lessons (cell D4) to determine the fee the club needs to charge to achieve the income goal.
- 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:
- Set the minimum session income for Seal lessons (cell C13) to 620.
- 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.
- 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:
- Set the average session income for Guppy lessons (cell B14) to 1,200.
- 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.
- 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:
- Use Average Attendance as the scenario name.
- Use B9:F9 as the changing cells.
- 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.
- Add another new scenario to compare the profit with low class attendance as follows:
- Add another scenario to the workbook, using Low Attendance as the scenario name.
- Use B9:F9 as the changing cells.
- 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
Cell |
New Value |
Guppy_New_Students (B9) |
6 |
Seal_New_Students (C9) |
5 |
Otter_New_Students (D9) |
10 |
Eel_New_Students (E9) |
6 |
Dolphin_New_Students (F9) |
6 |
- Show the Max Attendance scenario values in the Current Rates worksheet.
- 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:
- 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.)
- Use New Rates Scenario Report as the name of the worksheet containing the report.
- 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:
- 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.)
- Use New Rates PivotTable as the name of the worksheet containing the PivotTable.
- 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.)
- 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:
- 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.
- Use the range B5:F6, which shows the number of morning and afternoon swim classes, as the changing variable cells.
- 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 |
B5:F6 |
Each morning and afternoon class value is an integer |
B5:F6 |
Each type of lesson is scheduled 3 times per week or more |
B7:F7 |
Each type of lesson is scheduled 10 times per week or less |
B7:F7 |
The total number of morning classes is 10 or less |
Total_Morning_Classes (G5) |
The total number of afternoon classes is 15 or less |
Total_Afternoon_Classes (G6) |
The total number of classes per week is 20 |
Total_Weekly_Classes (G7) |
Swim team morning classes are scheduled 6 times per week or less |
Team_Morning_Classes (D21) |
Swim team afternoon classes are scheduled 8 times per week or less |
Team_Afternoon_Classes (D22) |
The total number of swim team classes scheduled per week is 12 or less |
Team_Total_Classes (D23) |
- Use Simplex LP as the solving method.
- Save the Solver model in cell A27.
- Solve the model, keeping the Solver solution.
- 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:
- Solve the model again, this time choosing to produce an Answer report.
- Use New Schedule Answer Report as the name of the worksheet containing the Answer report.
- 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
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
- 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:
- Use the Trace Precedents arrows to find the source of the error in cell B15, the income per Guppy class.
- 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).
- Copy the formula and the formatting from cell B15 to the range C15:F15.
- Correct the Name error in cell B18 as follows:
- Use Error Checking to determine the source of the error in cell B18, which should calculate the average income per session.
- Correct the error by editing the formula in cell B18.
- 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:
- The cells in the range B8:F10 should allow only whole number values greater than 0.
- 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.
- 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.
- 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:
- Set the maximum session income for Otter lessons (cell D15) to 2,000.
- Change the session fee for Otter lessons (cell D4) to determine the fee the club needs to charge to achieve the income goal.
- 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:
- Set the minimum session income for Seal lessons (cell C13) to 620.
- 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.
- 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:
- Set the average session income for Guppy lessons (cell B14) to 1,200.
- 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.
- 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:
- Use Average Attendance as the scenario name.
- Use B9:F9 as the changing cells.
- 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.
- Add another new scenario to compare the profit with low class attendance as follows:
- Add another scenario to the workbook, using Low Attendance as the scenario name.
- Use B9:F9 as the changing cells.
- 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
Cell |
New Value |
Guppy_New_Students (B9) |
6 |
Seal_New_Students (C9) |
5 |
Otter_New_Students (D9) |
10 |
Eel_New_Students (E9) |
6 |
Dolphin_New_Students (F9) |
6 |
- Show the Max Attendance scenario values in the Current Rates worksheet.
- 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:
- 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.)
- Use New Rates Scenario Report as the name of the worksheet containing the report.
- 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:
- 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.)
- Use New Rates PivotTable as the name of the worksheet containing the PivotTable.
- 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.)
- 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:
- 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.
- Use the range B5:F6, which shows the number of morning and afternoon swim classes, as the changing variable cells.
- 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 |
B5:F6 |
Each morning and afternoon class value is an integer |
B5:F6 |
Each type of lesson is scheduled 3 times per week or more |
B7:F7 |
Each type of lesson is scheduled 10 times per week or less |
B7:F7 |
The total number of morning classes is 10 or less |
Total_Morning_Classes (G5) |
The total number of afternoon classes is 15 or less |
Total_Afternoon_Classes (G6) |
The total number of classes per week is 20 |
Total_Weekly_Classes (G7) |
Swim team morning classes are scheduled 6 times per week or less |
Team_Morning_Classes (D21) |
Swim team afternoon classes are scheduled 8 times per week or less |
Team_Afternoon_Classes (D22) |
The total number of swim team classes scheduled per week is 12 or less |
Team_Total_Classes (D23) |
- Use Simplex LP as the solving method.
- Save the Solver model in cell A27.
- Solve the model, keeping the Solver solution.
- 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:
- Solve the model again, this time choosing to produce an Answer report.
- Use New Schedule Answer Report as the name of the worksheet containing the Answer report.
- 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
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
- 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:
- Use the Trace Precedents arrows to find the source of the error in cell B15, the income per Guppy class.
- 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).
- Copy the formula and the formatting from cell B15 to the range C15:F15.
- Correct the Name error in cell B18 as follows:
- Use Error Checking to determine the source of the error in cell B18, which should calculate the average income per session.
- Correct the error by editing the formula in cell B18.
- 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:
- The cells in the range B8:F10 should allow only whole number values greater than 0.
- 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.
- 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.
- 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:
- Set the maximum session income for Otter lessons (cell D15) to 2,000.
- Change the session fee for Otter lessons (cell D4) to determine the fee the club needs to charge to achieve the income goal.
- 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:
- Set the minimum session income for Seal lessons (cell C13) to 620.
- 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.
- 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:
- Set the average session income for Guppy lessons (cell B14) to 1,200.
- 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.
- 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:
- Use Average Attendance as the scenario name.
- Use B9:F9 as the changing cells.
- 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.
- Add another new scenario to compare the profit with low class attendance as follows:
- Add another scenario to the workbook, using Low Attendance as the scenario name.
- Use B9:F9 as the changing cells.
- 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
Cell |
New Value |
Guppy_New_Students (B9) |
6 |
Seal_New_Students (C9) |
5 |
Otter_New_Students (D9) |
10 |
Eel_New_Students (E9) |
6 |
Dolphin_New_Students (F9) |
6 |
- Show the Max Attendance scenario values in the Current Rates worksheet.
- 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:
- 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.)
- Use New Rates Scenario Report as the name of the worksheet containing the report.
- 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:
- 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.)
- Use New Rates PivotTable as the name of the worksheet containing the PivotTable.
- 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.)
- 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:
- 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.
- Use the range B5:F6, which shows the number of morning and afternoon swim classes, as the changing variable cells.
- 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 |
B5:F6 |
Each morning and afternoon class value is an integer |
B5:F6 |
Each type of lesson is scheduled 3 times per week or more |
B7:F7 |
Each type of lesson is scheduled 10 times per week or less |
B7:F7 |
The total number of morning classes is 10 or less |
Total_Morning_Classes (G5) |
The total number of afternoon classes is 15 or less |
Total_Afternoon_Classes (G6) |
The total number of classes per week is 20 |
Total_Weekly_Classes (G7) |
Swim team morning classes are scheduled 6 times per week or less |
Team_Morning_Classes (D21) |
Swim team afternoon classes are scheduled 8 times per week or less |
Team_Afternoon_Classes (D22) |
The total number of swim team classes scheduled per week is 12 or less |
Team_Total_Classes (D23) |
- Use Simplex LP as the solving method.
- Save the Solver model in cell A27.
- Solve the model, keeping the Solver solution.
- 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:
- Solve the model again, this time choosing to produce an Answer report.
- Use New Schedule Answer Report as the name of the worksheet containing the Answer report.
- 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
Shelly Cashman Excel 2016 | Module 9: SAM Project 1a
Shelly Cashman Excel 2016 | Module 9: SAM Project 1aShelly Cashman Excel 2016 | Module 9: SAM Project 1a
Bartlesville Swim Club
Bartlesville Swim Club
FORMULA AUDITING, DATA VALIDATION, AND COMPLEX PROBLEM SOLVING
FORMULA AUDITING, DATA VALIDATION, AND COMPLEX PROBLEM SOLVING
FORMULA AUDITING, DATA VALIDATION, AND COMPLEX PROBLEM SOLVING
GETTING STARTED
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.
Open the file SC_EX16_9a_FirstLastName_1.xlsx, available for download from the SAM website.SC_EX16_9a_FirstLastName_1.xlsxFirstLastName
- 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.
Save the file as SC_EX16_9a_FirstLastName_2.xlsx by changing the “1” to a “2”.SC_EX16_9a_FirstLastName_2.xlsx FirstLastName
- 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.
.xlsx
- 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.
SC_EX16_9a_FirstLastName_2.xlsxFirstLastName
- 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.
- 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.
. Solver
- 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
PROJECT STEPS
- 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.
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:
Go to the Swim Lesson Sessions worksheet. Correct the first error in this worksheet, a divide by zero error, as follows:Swim Lesson Sessions
- Use the Trace Precedents arrows to find the source of the error in cell B15, the income per Guppy class.
- 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).
- Copy the formula and the formatting from cell B15 to the range C15:F15.
- Correct the Name error in cell B18 as follows:
- Use Error Checking to determine the source of the error in cell B18, which should calculate the average income per session.
- Correct the error by editing the formula in cell B18.
- 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:
- The cells in the range B8:F10 should allow only whole number values greater than 0.
- 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.
- 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.
- 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:
- Set the maximum session income for Otter lessons (cell D15) to 2,000.
- Change the session fee for Otter lessons (cell D4) to determine the fee the club needs to charge to achieve the income goal.
- 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:
- Set the minimum session income for Seal lessons (cell C13) to 620.
- 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.
- 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:
- Set the average session income for Guppy lessons (cell B14) to 1,200.
- 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.
- 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:
- Use Average Attendance as the scenario name.
- Use B9:F9 as the changing cells.
- 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.
- Add another new scenario to compare the profit with low class attendance as follows:
- Add another scenario to the workbook, using Low Attendance as the scenario name.
- Use B9:F9 as the changing cells.
- Update the cell values in the range B9:F9 to match the low attendance values shown in bold in Table 1 below:
Use the Trace Precedents arrows to find the source of the error in cell B15, the income per Guppy class.
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).B14B5
Copy the formula and the formatting from cell B15 to the range C15:F15.
- Use Error Checking to determine the source of the error in cell B18, which should calculate the average income per session.
- Correct the error by editing the formula in cell B18.
- 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:
- The cells in the range B8:F10 should allow only whole number values greater than 0.
- 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.
- 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.
- 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:
- Set the maximum session income for Otter lessons (cell D15) to 2,000.
- Change the session fee for Otter lessons (cell D4) to determine the fee the club needs to charge to achieve the income goal.
- 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:
- Set the minimum session income for Seal lessons (cell C13) to 620.
- 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.
- 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:
- Set the average session income for Guppy lessons (cell B14) to 1,200.
- 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.
- 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:
- Use Average Attendance as the scenario name.
- Use B9:F9 as the changing cells.
- 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.
- Add another new scenario to compare the profit with low class attendance as follows:
- Add another scenario to the workbook, using Low Attendance as the scenario name.
- Use B9:F9 as the changing cells.
- Update the cell values in the range B9:F9 to match the low attendance values shown in bold in Table 1 below:
Correct the Name error in cell B18 as follows:
- Use Error Checking to determine the source of the error in cell B18, which should calculate the average income per session.
- Correct the error by editing the formula in cell B18.
- 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:
- The cells in the range B8:F10 should allow only whole number values greater than 0.
- 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.
- 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.
- 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:
- Set the maximum session income for Otter lessons (cell D15) to 2,000.
- Change the session fee for Otter lessons (cell D4) to determine the fee the club needs to charge to achieve the income goal.
- 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:
- Set the minimum session income for Seal lessons (cell C13) to 620.
- 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.
- 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:
- Set the average session income for Guppy lessons (cell B14) to 1,200.
- 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.
- 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:
- Use Average Attendance as the scenario name.
- Use B9:F9 as the changing cells.
- 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.
- Add another new scenario to compare the profit with low class attendance as follows:
- Add another scenario to the workbook, using Low Attendance as the scenario name.
- Use B9:F9 as the changing cells.
- Update the cell values in the range B9:F9 to match the low attendance values shown in bold in Table 1 below:
- The cells in the range B8:F10 should allow only whole number values greater than 0.
- 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.
- 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.
- 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:
- Set the maximum session income for Otter lessons (cell D15) to 2,000.
- Change the session fee for Otter lessons (cell D4) to determine the fee the club needs to charge to achieve the income goal.
- 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:
- Set the minimum session income for Seal lessons (cell C13) to 620.
- 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.
- 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:
- Set the average session income for Guppy lessons (cell B14) to 1,200.
- 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.
- 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:
- Use Average Attendance as the scenario name.
- Use B9:F9 as the changing cells.
- 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.
- Add another new scenario to compare the profit with low class attendance as follows:
- Add another scenario to the workbook, using Low Attendance as the scenario name.
- Use B9:F9 as the changing cells.
- Update the cell values in the range B9:F9 to match the low attendance values shown in bold in Table 1 below:
Session Enrollment
- The cells in the range B8:F10 should allow only whole number values greater than 0.
- 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.
- 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.
- 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:
- Set the maximum session income for Otter lessons (cell D15) to 2,000.
- Change the session fee for Otter lessons (cell D4) to determine the fee the club needs to charge to achieve the income goal.
- 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:
- Set the minimum session income for Seal lessons (cell C13) to 620.
- 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.
- 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:
- Set the average session income for Guppy lessons (cell B14) to 1,200.
- 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.
- 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:
- Use Average Attendance as the scenario name.
- Use B9:F9 as the changing cells.
- 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.
- Add another new scenario to compare the profit with low class attendance as follows:
- Add another scenario to the workbook, using Low Attendance as the scenario name.
- Use B9:F9 as the changing cells.
- Update the cell values in the range B9:F9 to match the low attendance values shown in bold in Table 1 below:
whole number greater than0
Session EnrollmentEnter the minimum, average, or maximum enrollment per lesson type.
StopEnrollment ErrorSession enrollment must be greater than 0.
- Set the maximum session income for Otter lessons (cell D15) to 2,000.
- Change the session fee for Otter lessons (cell D4) to determine the fee the club needs to charge to achieve the income goal.
- 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:
- Set the minimum session income for Seal lessons (cell C13) to 620.
- 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.
- 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:
- Set the average session income for Guppy lessons (cell B14) to 1,200.
- 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.
- 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:
- Use Average Attendance as the scenario name.
- Use B9:F9 as the changing cells.
- 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.
- Add another new scenario to compare the profit with low class attendance as follows:
- Add another scenario to the workbook, using Low Attendance as the scenario name.
- Use B9:F9 as the changing cells.
- Update the cell values in the range B9:F9 to match the low attendance values shown in bold in Table 1 below:
- Set the maximum session income for Otter lessons (cell D15) to 2,000.
- Change the session fee for Otter lessons (cell D4) to determine the fee the club needs to charge to achieve the income goal.
- 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:
- Set the minimum session income for Seal lessons (cell C13) to 620.
- 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.
- 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:
- Set the average session income for Guppy lessons (cell B14) to 1,200.
- 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.
- 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:
- Use Average Attendance as the scenario name.
- Use B9:F9 as the changing cells.
- 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.
- Add another new scenario to compare the profit with low class attendance as follows:
- Add another scenario to the workbook, using Low Attendance as the scenario name.
- Use B9:F9 as the changing cells.
- Update the cell values in the range B9:F9 to match the low attendance values shown in bold in Table 1 below:
D152,000
D4
- Set the minimum session income for Seal lessons (cell C13) to 620.
- 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.
- 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:
- Set the average session income for Guppy lessons (cell B14) to 1,200.
- 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.
- 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:
- Use Average Attendance as the scenario name.
- Use B9:F9 as the changing cells.
- 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.
- Add another new scenario to compare the profit with low class attendance as follows:
- Add another scenario to the workbook, using Low Attendance as the scenario name.
- Use B9:F9 as the changing cells.
- Update the cell values in the range B9:F9 to match the low attendance values shown in bold in Table 1 below:
- Set the minimum session income for Seal lessons (cell C13) to 620.
- 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.
- 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:
- Set the average session income for Guppy lessons (cell B14) to 1,200.
- 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.
- 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:
- Use Average Attendance as the scenario name.
- Use B9:F9 as the changing cells.
- 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.
- Add another new scenario to compare the profit with low class attendance as follows:
- Add another scenario to the workbook, using Low Attendance as the scenario name.
- Use B9:F9 as the changing cells.
- Update the cell values in the range B9:F9 to match the low attendance values shown in bold in Table 1 below:
C13620
C8
- Set the average session income for Guppy lessons (cell B14) to 1,200.
- 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.
- 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:
- Use Average Attendance as the scenario name.
- Use B9:F9 as the changing cells.
- 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.
- Add another new scenario to compare the profit with low class attendance as follows:
- Add another scenario to the workbook, using Low Attendance as the scenario name.
- Use B9:F9 as the changing cells.
- Update the cell values in the range B9:F9 to match the low attendance values shown in bold in Table 1 below:
- Set the average session income for Guppy lessons (cell B14) to 1,200.
- 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.
- 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:
- Use Average Attendance as the scenario name.
- Use B9:F9 as the changing cells.
- 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.
- Add another new scenario to compare the profit with low class attendance as follows:
- Add another scenario to the workbook, using Low Attendance as the scenario name.
- Use B9:F9 as the changing cells.
- Update the cell values in the range B9:F9 to match the low attendance values shown in bold in Table 1 below:
B141,200
B9
- Use Average Attendance as the scenario name.
- Use B9:F9 as the changing cells.
- 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.
- Add another new scenario to compare the profit with low class attendance as follows:
- Add another scenario to the workbook, using Low Attendance as the scenario name.
- Use B9:F9 as the changing cells.
- Update the cell values in the range B9:F9 to match the low attendance values shown in bold in Table 1 below:
Current Rates
- Use Average Attendance as the scenario name.
- Use B9:F9 as the changing cells.
- 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.
- Add another new scenario to compare the profit with low class attendance as follows:
- Add another scenario to the workbook, using Low Attendance as the scenario name.
- Use B9:F9 as the changing cells.
- Update the cell values in the range B9:F9 to match the low attendance values shown in bold in Table 1 below:
Average Attendance
B9:F9
- Add another scenario to the workbook, using Low Attendance as the scenario name.
- Use B9:F9 as the changing cells.
- Update the cell values in the range B9:F9 to match the low attendance values shown in bold in Table 1 below:
- Add another scenario to the workbook, using Low Attendance as the scenario name.
- Use B9:F9 as the changing cells.
- Update the cell values in the range B9:F9 to match the low attendance values shown in bold in Table 1 below:
Low Attendance
B9:F9
Table 1: Cell Values for the Low Attendance Scenario
Table 1: Cell Values for the Low Attendance Scenario
Table 1: Cell Values for the Low Attendance Scenario
Cell |
New Value |
Guppy_New_Students (B9) |
6 |
Seal_New_Students (C9) |
5 |
Otter_New_Students (D9) |
10 |
Eel_New_Students (E9) |
6 |
Dolphin_New_Students (F9) |
6 |
Cell |
New Value |
Guppy_New_Students (B9) |
6 |
Seal_New_Students (C9) |
5 |
Otter_New_Students (D9) |
10 |
Eel_New_Students (E9) |
6 |
Dolphin_New_Students (F9) |
6 |
Cell
New Value
Guppy_New_Students (B9)
6
Seal_New_Students (C9)
5
Otter_New_Students (D9)
10
Eel_New_Students (E9)
6
Dolphin_New_Students (F9)
6
Cell
New Value
Cell
Cell
CellCell
New Value
New Value
New ValueNew Value
Guppy_New_Students (B9)
6
Guppy_New_Students (B9)
Guppy_New_Students (B9)
Guppy_New_Students (B9)Guppy_New_Students (B9)
6
6
66
Seal_New_Students (C9)
5
Seal_New_Students (C9)
Seal_New_Students (C9)
Seal_New_Students (C9)Seal_New_Students (C9)
5
5
55
Otter_New_Students (D9)
10
Otter_New_Students (D9)
Otter_New_Students (D9)
Otter_New_Students (D9)Otter_New_Students (D9)
10
10
1010
Eel_New_Students (E9)
6
Eel_New_Students (E9)
Eel_New_Students (E9)
Eel_New_Students (E9)Eel_New_Students (E9)
6
6
66
Dolphin_New_Students (F9)
6
Dolphin_New_Students (F9)
Dolphin_New_Students (F9)
Dolphin_New_Students (F9)Dolphin_New_Students (F9)
6
6
66
- Show the Max Attendance scenario values in the Current Rates worksheet.
- 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:
- 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.)
- Use New Rates Scenario Report as the name of the worksheet containing the report.
- 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:
- 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.)
- Use New Rates PivotTable as the name of the worksheet containing the PivotTable.
- 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.)
- 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:
- 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.
- Use the range B5:F6, which shows the number of morning and afternoon swim classes, as the changing variable cells.
- Use the constraints shown in Table 2 below.
Show the Max Attendance scenario values in the Current Rates worksheet.Max AttendanceCurrent Rates
- 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.)
- Use New Rates Scenario Report as the name of the worksheet containing the report.
- 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:
- 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.)
- Use New Rates PivotTable as the name of the worksheet containing the PivotTable.
- 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.)
- 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:
- 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.
- Use the range B5:F6, which shows the number of morning and afternoon swim classes, as the changing variable cells.
- Use the constraints shown in Table 2 below.
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:New Rates
- 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.)
- Use New Rates Scenario Report as the name of the worksheet containing the report.
- 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:
- 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.)
- Use New Rates PivotTable as the name of the worksheet containing the PivotTable.
- 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.)
- 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:
- 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.
- Use the range B5:F6, which shows the number of morning and afternoon swim classes, as the changing variable cells.
- Use the constraints shown in Table 2 below.
B11:F11Hint
New Rates Scenario Report
- 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.)
- Use New Rates PivotTable as the name of the worksheet containing the PivotTable.
- 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.)
- 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:
- 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.
- Use the range B5:F6, which shows the number of morning and afternoon swim classes, as the changing variable cells.
- Use the constraints shown in Table 2 below.
New Rates
- 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.)
- Use New Rates PivotTable as the name of the worksheet containing the PivotTable.
- 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.)
- 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:
- 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.
- Use the range B5:F6, which shows the number of morning and afternoon swim classes, as the changing variable cells.
- Use the constraints shown in Table 2 below.
B11:F11Hint
New Rates PivotTable
New Rates PivotTableAccounting0$HintCustom
- 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.
- Use the range B5:F6, which shows the number of morning and afternoon swim classes, as the changing variable cells.
- Use the constraints shown in Table 2 below.
New Schedule
- 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.
- Use the range B5:F6, which shows the number of morning and afternoon swim classes, as the changing variable cells.
- Use the constraints shown in Table 2 below.
G17
B5:F6
Table 2: Solver Constraints
Table 2: Solver Constraints
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 |
B5:F6 |
Each morning and afternoon class value is an integer |
B5:F6 |
Each type of lesson is scheduled 3 times per week or more |
B7:F7 |
Each type of lesson is scheduled 10 times per week or less |
B7:F7 |
The total number of morning classes is 10 or less |
Total_Morning_Classes (G5) |
The total number of afternoon classes is 15 or less |
Total_Afternoon_Classes (G6) |
The total number of classes per week is 20 |
Total_Weekly_Classes (G7) |
Swim team morning classes are scheduled 6 times per week or less |
Team_Morning_Classes (D21) |
Swim team afternoon classes are scheduled 8 times per week or less |
Team_Afternoon_Classes (D22) |
The total number of swim team classes scheduled per week is 12 or less |
Team_Total_Classes (D23) |
Constraint |
Cell or Range |
Each type of lesson is scheduled at least once in the morning and once in the afternoon |
B5:F6 |
Each morning and afternoon class value is an integer |
B5:F6 |
Each type of lesson is scheduled 3 times per week or more |
B7:F7 |
Each type of lesson is scheduled 10 times per week or less |
B7:F7 |
The total number of morning classes is 10 or less |
Total_Morning_Classes (G5) |
The total number of afternoon classes is 15 or less |
Total_Afternoon_Classes (G6) |
The total number of classes per week is 20 |
Total_Weekly_Classes (G7) |
Swim team morning classes are scheduled 6 times per week or less |
Team_Morning_Classes (D21) |
Swim team afternoon classes are scheduled 8 times per week or less |
Team_Afternoon_Classes (D22) |
The total number of swim team classes scheduled per week is 12 or less |
Team_Total_Classes (D23) |
Constraint
Cell or Range
Each type of lesson is scheduled at least once in the morning and once in the afternoon
B5:F6
Each morning and afternoon class value is an integer
B5:F6
Each type of lesson is scheduled 3 times per week or more
B7:F7
Each type of lesson is scheduled 10 times per week or less
B7:F7
The total number of morning classes is 10 or less
Total_Morning_Classes (G5)
The total number of afternoon classes is 15 or less
Total_Afternoon_Classes (G6)
The total number of classes per week is 20
Total_Weekly_Classes (G7)
Swim team morning classes are scheduled 6 times per week or less
Team_Morning_Classes (D21)
Swim team afternoon classes are scheduled 8 times per week or less
Team_Afternoon_Classes (D22)
The total number of swim team classes scheduled per week is 12 or less
Team_Total_Classes (D23)
Constraint
Cell or Range
Constraint
Constraint
ConstraintConstraint
Cell or Range
Cell or Range
Cell or RangeCell or Range
Each type of lesson is scheduled at least once in the morning and once in the afternoon
B5:F6
Each type of lesson is scheduled at least once in the morning and once in the afternoon
Each type of lesson is scheduled at least once in the morning and once in the afternoon
Each type of lesson is scheduled at least once in the morning and once in the afternoonEach type of lesson is scheduled at least once in the morning and once in the afternoon
B5:F6
B5:F6
B5:F6B5:F6
Each morning and afternoon class value is an integer
B5:F6
Each morning and afternoon class value is an integer
Each morning and afternoon class value is an integer
Each morning and afternoon class value is an integerEach morning and afternoon class value is an integer
B5:F6
B5:F6
B5:F6B5:F6
Each type of lesson is scheduled 3 times per week or more
B7:F7
Each type of lesson is scheduled 3 times per week or more
Each type of lesson is scheduled 3 times per week or more
Each type of lesson is scheduled 3 times per week or moreEach type of lesson is scheduled 3 times per week or more
B7:F7
B7:F7
B7:F7B7:F7
Each type of lesson is scheduled 10 times per week or less
B7:F7
Each type of lesson is scheduled 10 times per week or less
Each type of lesson is scheduled 10 times per week or less
Each type of lesson is scheduled 10 times per week or lessEach type of lesson is scheduled 10 times per week or less
B7:F7
B7:F7
B7:F7B7:F7
The total number of morning classes is 10 or less
Total_Morning_Classes (G5)
The total number of morning classes is 10 or less
The total number of morning classes is 10 or less
The total number of morning classes is 10 or lessThe total number of morning classes is 10 or less
Total_Morning_Classes (G5)
Total_Morning_Classes (G5)
Total_Morning_Classes (G5)Total_Morning_Classes (G5)
The total number of afternoon classes is 15 or less
Total_Afternoon_Classes (G6)
The total number of afternoon classes is 15 or less
The total number of afternoon classes is 15 or less
The total number of afternoon classes is 15 or lessThe total number of afternoon classes is 15 or less
Total_Afternoon_Classes (G6)
Total_Afternoon_Classes (G6)
Total_Afternoon_Classes (G6)Total_Afternoon_Classes (G6)
The total number of classes per week is 20
Total_Weekly_Classes (G7)
The total number of classes per week is 20
The total number of classes per week is 20
The total number of classes per week is 20The total number of classes per week is 20
Total_Weekly_Classes (G7)
Total_Weekly_Classes (G7)
Total_Weekly_Classes (G7)Total_Weekly_Classes (G7)
Swim team morning classes are scheduled 6 times per week or less
Team_Morning_Classes (D21)
Swim team morning classes are scheduled 6 times per week or less
Swim team morning classes are scheduled 6 times per week or less
Swim team morning classes are scheduled 6 times per week or lessSwim team morning classes are scheduled 6 times per week or less
Team_Morning_Classes (D21)
Team_Morning_Classes (D21)
Team_Morning_Classes (D21)Team_Morning_Classes (D21)
Swim team afternoon classes are scheduled 8 times per week or less
Team_Afternoon_Classes (D22)
Swim team afternoon classes are scheduled 8 times per week or less
Swim team afternoon classes are scheduled 8 times per week or less
Swim team afternoon classes are scheduled 8 times per week or lessSwim team afternoon classes are scheduled 8 times per week or less
Team_Afternoon_Classes (D22)
Team_Afternoon_Classes (D22)
Team_Afternoon_Classes (D22)Team_Afternoon_Classes (D22)
The total number of swim team classes scheduled per week is 12 or less
Team_Total_Classes (D23)
The total number of swim team classes scheduled per week is 12 or less
The total number of swim team classes scheduled per week is 12 or less
The total number of swim team classes scheduled per week is 12 or lessThe total number of swim team classes scheduled per week is 12 or less
Team_Total_Classes (D23)
Team_Total_Classes (D23)
Team_Total_Classes (D23)Team_Total_Classes (D23)
- Use Simplex LP as the solving method.
- Save the Solver model in cell A27.
- Solve the model, keeping the Solver solution.
- 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:
- Solve the model again, this time choosing to produce an Answer report.
- Use New Schedule Answer Report as the name of the worksheet containing the Answer report.
- Mark the workbook as Final to indicate you are finished revising the workbook.
Use Simplex LP as the solving method.Simplex LP
Save the Solver model in cell A27.A27
Solve the model, keeping the Solver solution.
- Solve the model again, this time choosing to produce an Answer report.
- Use New Schedule Answer Report as the name of the worksheet containing the Answer report.
- Mark the workbook as Final to indicate you are finished revising the workbook.
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:
- Solve the model again, this time choosing to produce an Answer report.
- Use New Schedule Answer Report as the name of the worksheet containing the Answer report.
- Mark the workbook as Final to indicate you are finished revising the workbook.
Answer
New Schedule Answer Report
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.
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.Session EnrollmentNew Schedule Answer ReportNew Schedule
Final Figure 1: Swim Lesson Sessions Worksheet
Final Figure 1: Swim Lesson Sessions Worksheet
Final Figure 1: Swim Lesson Sessions Worksheet
Final Figure 2: Session Enrollment Worksheet
Final Figure 2: Session Enrollment Worksheet
Final Figure 2: Session Enrollment Worksheet
Final Figure 3: Current Rates Worksheet
Final Figure 3: Current Rates Worksheet
Final Figure 3: Current Rates Worksheet
Final Figure 4: New Rates Scenario Report Worksheet
Final Figure 4: New Rates Scenario Report Worksheet
Final Figure 4: New Rates Scenario Report Worksheet
Final Figure 5: New Rates PivotTable Worksheet
Final Figure 5: New Rates PivotTable Worksheet
Final Figure 5: New Rates PivotTable Worksheet
Final Figure 6: New Rates Worksheet
Final Figure 6: New Rates Worksheet
Final Figure 6: New Rates Worksheet