Shelly Cashman Excel 2013 Chapter 9: SAM Project 1a Andrew Ramage 1

Subject:MS ExcelPrice:19.87 Bought3

Share With

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.