Shelly CashmanExcel 2013| Chapter 9: SAM Project 1a

PROJECT DESCRIPTION

Maria Angeles is the manager of Flexible Soul Yoga Studio. Maria created a workbook that tracks the current operations and monthly profit of the studio, along with data on the new initiatives she would like to propose to the Studio’s owners. She has asked for your help auditing and correcting errors in her workbook, setting up scenarios and generating scenario reports, and determining the optimum class schedule for the studio.

GETTING STARTED

  • Download the following file from the SAM website:
  • SC_Excel2013_C9_P1a_FirstLastName_1.xlsx
  • Open the file you just downloaded and save it with the name:
  • SC_Excel2013_C9_P1a_FirstLastName_2.xlsx
  • Hint: If you do not see the .xlsx file extension in the Save file dialog box, do not type it. Excel will add the file extension for you automatically.
  • With the file SC_Excel2013_C9_P1a_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.
  • To complete this project you will need the Solver Add-in (typically found in the Data ribbon). To add Solver to the Data tab, complete the following steps:
  • Click on the File tab to open Backstage view and then click the Options button.
  • In the Excel Options dialog box, click on the Add-Ins option and then select the Excel Add-ins option from the Manage dropdown menu (See Figure 1) and click Go.

Figure 1: Excel Options Dialog Box

  • From the Add-Ins dialog box, click Solver Add-in option (See Figure 2) and click OK. The Solver option should now be available in the Analysis section of the Data tab.

Figure 2: Add-Ins Dialog Box

PROJECT STEPS

  1. Go to the CurrentProfits worksheet. Select cell B9 and use the Trace Dependent and Trace Precedent arrows to determine the source of the error in the cell. The formula should be dividing the Average Profit per Class by the Students per Class. Fix the error in cell B9 and then fill the formula into the range C9:G9.
  2. Select cell B14 and use the Trace Dependent and Trace Precedent arrows to determine the source of the error in the cell. The formula should be multiplying the Average Profit per Student by the Number of Students per Week. Fix the error in cell B14 and then fill the formula into the range C14:G14. Remove any precedent arrows from the worksheet.
  3. Use Error Checking to determine the source of the error in cell H14. Correct the error in the formula.
  4. Go to the Current Operations worksheet. Save the current worksheet data in a new scenario, using the parameters shown below(Hint: The worksheet already contains a scenario titled Max Class Capacity):
  5. Use Average Class Capacityas the Scenario Name.
  6. Use B11:G11as the changing cells.
  7. Accept the current valuesin the range B11:G11 as the values for the changing cells (Hint: The defined names of the cells B11:G11will appear in the Scenario Values Dialog Box).
  8. Create a new scenario in the worksheet by completing the following actions:
  9. Update the cell values in the worksheet to match the values shown in Table 1.

Table 1

Cell / New Value
B11 / 6
C11 / 4
D11 / 7
E11 / 4
F11 / 4
G11 / 3
  1. Add another scenario to the workbook, using the scenario name Low Class Capacity.
  2. Use B11:G11as the changing cells.
  3. Accept the current values in the range B11:G11as the values for the changing cells (Hint: The defined names of the cells B11:G11will appear in the Scenario Values Dialog Box).
  1. Display the Max Class Capacity scenario values in the Current Operationsworksheet.
  2. Go to the New Classes worksheet. Add data validation to the cell C10 with the following parameters:
  3. The cell should only allow Whole Number values greater than 0.
  4. The Input Message title should be Minimum Class Size and the Input Message text should be Enter the minimum class size. (include the period).
  5. The Error Alert should use the Stop style, with the title Class Size Error and the error message The minimum class size must be greater than 0. (include the period).
  6. Use Goal Seek to determine what Average Class Size for the Youth class would result in the class Profit for Average Number of Students to be equal to 36 (Hint: Cell B12 will be the set cell and Cell B9 will be the changing cell).
  7. Use Goal Seek to determine the Minimum Class Size required for the Parent-Child classthat will allow the Studio to break even (Hint: Cell C13 will be the set cell and the studio will break even when this cell is equal to 0).
  8. Use Goal Seek to determine the Student Fee per Class required for the Prenatal class that will result in the studio breaking even with the minimum number of class attendees. Use cell D6as the changing cell, cell D13 as theset cell, and 0 as the value to set D13 to.
  9. Go to the New Rates worksheet and create a Scenario Summary Report, using the range B13:G13 as the result cells. Rename the worksheet New Rates Scenario Report.
  10. Return to the New Ratesworksheet and create a Scenario PivotTable Reportusing the range B13:G13as the result cells. Rename the worksheet New Rates PivotTable.
  11. Go to the New Scheduleworksheetand use Solver to maximize the weekly profits of the studio by completing the following set of actions(Hint: Some of the cells have defined names):
  12. Use cell H17(Total_Weekly_Profits) as the objectivecell in your Solver model, with the goal of determining the maximum value for that cell.
  13. Use the range B5:G6as the changing variable cells in your Solver Model.
  14. Use the constraints shown in Table 2 in your Solver Model.

Table 2: Solver Model Constraints

Range / Constraint
B5:G6 / Integers
B5:G6 / >=0
B7:G7 / >=3
B7:G7 / <=10
H5 / <=30
H6 / <=25
H7 / =45
D21 / <=8
D22 / <=6
D23 / <=12
  1. Use Simplex LP as the solving method for your Solver model.
  2. Confirm that your Solver Parameters Dialog Box matches Figure 4, then save the model by clicking the Load/Save button in the Solver Parameters Dialog box, selecting cell A27 in the Load/Save Model Dialog Box (shown in Figure 3), and clicking the Save button(Hint: Once you save the model, Excel will return you to the Solver Parameters Dialog Box).

Figure 3: Load/Save Model Dialog Box

  1. Solvethe model, keeping the solver solution.

Figure 4: Solve Parameters Dialog Box

  1. Generate an Answer Report for the Solver model(Hint: If you need to rerun the Solver model, the values in the New Scheduleworksheet should not change).Rename worksheet containing the Answer report asNew Schedule Answer Report.
  2. Mark the workbook as Final.

Your workbook should look like the Final Figure on the following page. Note that the New Classes, New Schedule Answer Report, and New Schedule worksheets are not provided to prevent manual entry of the Goal seek and Solver outcomes. Save your changes, close the workbook, and exit Excel. Follow the directions on the SAM website to submit your completed project.

Final Figure 1: Current Profits Worksheet

Final Figure2: Current Operations

Final Figure 3: New Rates Scenario Report

Final Figure 4: New Rates PivotTable