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 D$5$ on the Forecast worksheet, note the formula, and review the text box explanation.

Select cell B$12.$ This is a reference to cell C$3$ on the Growth Factors sheet. Both cells D$5$ and B$12$ refer to the Growth Factors sheet.

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

Select cells B$3$:C$3$ and B$6$:C$8$ and create range names from the selection.

Create scenarios for the growth rate.

Create a scenario named $\mathrm{.}15\%$ for the existing data with cell C$3$ as the changing cell.

Add a scenario named $\mathrm{.}5\%$ and change the value to $\mathrm{.}005.$

Add scenarios for $1\mathrm{.}25\%$ and $1\mathrm{.}75\%$ growth rates. In the Scenarios Values dialog box, you can enter the decimal equivalent $(\mathrm{.}0125$ and $\mathrm{.}0175)$ or the percentage.

Show the $1\mathrm{.}25\%$ scenario in the worksheet.

Return to the Forecast worksheet. Cell B$12$ displays the growth factor from the scenario.

Build the data table.

Select cell E$18.$ It refers to cell H$5,$ projected sales next year. Cells F$18$ and G$18$ refer to cells H$6$ and H$7,$ respectively.

Fill the growth rate percentages in cells D$21$:D$28.$

Complete the data table range using cell B$12$ 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 E$18$ 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 D$5$ with its reference to Growth Factors!$C$$3.$ A data table cannot use data on a different sheet.

Figure $9-113$ Formula precedents

Remove the arrows and select cell D$5.$ If you refer to cell B$12$ on this sheet instead of cell C$3$ on the Growth Factors sheet, the data table command will work.

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

Figure $9-114$ Edited formula

Copy the formula in cell D$5$ to cells E$5$:G$5$ and then to cells D$6$:G$7.$ 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 D$19$:D$28$ to display light red fill with dark red text if the value is equal to the value in cell B$12.$

Edit a Solver problem to find minimum wages.

Click the Crew$1$ worksheet tab.

Start a Solver problem to find the minimum value for cell E$9$ by changing cells D$5$:D$8.$ Constraints for cell D$8$ are already entered.

Add constraints for each of cells D$5$:D$7$ that reflect the information in column C in the worksheet.

Add a constraint that cell D$9$ be less than or equal to $150.$

Use the GRG Nonlinear solving method.

Keep the Solver solution.

Uninstall the Solver Add$-$in$.$