Advanced Project 9-7

These instructions are compatible with both Microsoft Windows and Mac operating systems.

Pool & Spa Oasis forecasts growth for three categories of its business. You create scenarios, build a one-variable data table, and use Solver to maximize productivity for a work crew.

[Student Learning Outcomes 9.1, 9.3, 9.4]

File Needed: PoolAndSpa-09.xlsx (Available from the Start File link.)

Completed Project File Name: [your name]-PoolAndSpa-09.xlsx

Skills Covered in This Project

Create and manage scenarios.

Build a one-variable data table.

Use Solver with constraints.

Steps to complete This Project

Mark the steps as checked when you complete them.

Open the PoolAndSpa-09 workbook and click the Enable Editing button. The file will be renamed automatically to include your name.

Review formulas.

Select cell D5 on the Forecast worksheet, note the formula, and review the text box explanation.

Select cell B12. This is a reference to cell C3 on the Growth Factors sheet. Both cells D5 and B12 refer to the Growth Factors sheet.

Click the Growth Factors worksheet tab. The general growth rate applies across the board. Each categorys potential is increased or reduced by an adjustment factor.

Select cells B3:C3 and B6:C8 and create range names from the selection.

Create scenarios for the growth rate.

Create a scenario named .15% for the existing data with cell C3 as the changing cell.

Add a scenario named .5% and change the value to .005.

Add scenarios for 1.25% and 1.75% growth rates. In the Scenarios Values dialog box, you can enter the decimal equivalent (.0125 and .0175) or the percentage.

Show the 1.25% scenario in the worksheet.

Return to the Forecast worksheet. Cell B12 displays the growth factor from the scenario.

Build the data table.

Select cell E18. It refers to cell H5, projected sales next year. Cells F18 and G18 refer to cells H6 and H7, respectively.

Fill the growth rate percentages in cells D21:D28.

Complete the data table range using cell B12 as the column input. The results are incorrect, because data table formulas must refer to data on the same worksheet.

Edit formulas to use a reference on the same sheet as the data table.

Select cell E18 and click the Trace Precedents button [Formulas tab, Formula Auditing group].

Click the Trace Precedents button again to trace precedents of the precedent (Figure 9-113). The formulas in row 18 indirectly refer to cell D5 with its reference to Growth Factors!$C$3. A data table cannot use data on a different sheet.

Remove the arrows and select cell D5. If you refer to cell B12 on this sheet instead of cell C3 on the Growth Factors sheet, the data table command will work.

Edit the formula in cell D5 to show (1+$B$12) in place of (1 + Growth Factors!$C$3). The formula in cell D5 includes a reference to cell C6 on the Growth Factors sheet, but cell C6 is not part of the data table calculation, so it need not be changed (Figure 9-114).

Copy the formula in cell D5 to cells E5:G5 and then to cells D6:G7. The data table updates.

Redefine the bottom borders in rows 5 and 6 as needed.

Format data table results as Currency with no decimal places.

Apply conditional formatting to cells D19:D28 to display light red fill with dark red text if the value is equal to the value in cell B12.

Figure 9-114 Edited formula

Figure 9-113 Formula precedents

Edit a Solver problem to find minimum wages.

Click the Crew1 worksheet tab.

Start a Solver problem to find the minimum value for cell E9 by changing cells D5:D8. Constraints for cell D8 are already entered.

Add constraints for each of cells D5:D7 that reflect the information in column C in the worksheet.

Add a constraint that cell D9 be less than or equal to 150.

Use the GRG Nonlinear solving method.

Keep the Solver solution.

Uninstall the Solver Add-in.

Save and close the workbook (Figure 9-115).

Figure 9-115 Excel 9-7 completed

Upload and save your project file.

Submit file for grading.