Solution for QID #49859: Shelly Cashman Excel 2013 Chapter 9: SAM Project 1a Andre | StudyHelpMe

Shelly Cashman Excel 2013 Chapter 9: SAM Project 1a Andrew Ramage 1.  Go to the Current Profits 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): a. Use Average Class Capacity as the Scenario Name. b. Use B11:G11 as the Changing cells. c. Accept the current values in the range B11:G11 as the values for the Changing cells (Hint: The defined names of the cells B11:G11 will appear in the Scenario Values Dialog Box). 5.  Create a new scenario in the worksheet by completing the following actions: a. Update the cell values in the worksheet to match the values shown in Table 1 in the Assignment file. b. Add another scenario to the workbook, using the scenario name Low Class Capacity. c. Use B11:G11 as the Changing cells. d. Accept the current values in the range B11:G11 as the values for the Changing cells (Hint: The defined names of the cells B11:G11 will appear in the Scenario Values Dialog Box). 6.  Display the Max Class Capacity scenario values in the Current Operations worksheet. 7.  Go to the New Classes worksheet. Add data validation to the cell C10 with the following parameters: a. The cell should only allow Whole Number values greater than 0. b. The Input Message title should be Minimum Class Size and the Input message text should be Enter the minimum class size. (include the period). c. 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). 8.  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). 9.  Use Goal Seek to determine the Minimum Class Size required for the Parent-Child class that 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). 10.  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 D6 as the Changing cell, cell D13 as the Set cell, and 0 as the value to set D13 to. 11.  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. 12.  Return to the New Rates worksheet and create a Scenario PivotTable Report using the range B13:G13 as the Result cells. Rename the worksheet New Rates PivotTable. 13.  Go to the New Schedule worksheet and 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): a. Use cell H17 (Total_Weekly_Profits) as the Objective cell in your Solver model, with the goal of determining the maximum value for that cell. b. Use the range B5:G6 as the Changing Variable cells in your Solver Model. c. Use the constraints shown in Table 2 in your Solver Model. d. Use Simplex LP as the solving method for your Solver model. e. 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). f. Solve the model, keeping the solver solution. 14.  Generate an Answer Report for the Solver model (Hint: If you need to rerun the Solver model, the values in the New Schedule worksheet should not change). Rename worksheet containing the Answer report as New Schedule Answer Report.   15.  Mark the workbook as Final.