the directions are in the additonal photos

Pool & Spa Oasis Projected Growth and Sales Category Pool Construction Hot Tub Installation Maintenance Contracts Actual Q4 Last Year $325,000 $275,000 $200,000 Q1 Next Year $341,762 $275,413 $170,255 Q2 Next Year S359.388 $275,826 $144,934 Q3 Next Year $377,924 $276,239 $123,379 Q4 Next Year S397,415 $276,654 $105,029 Target Sales Projected Next Year $1,476,489 $1,104,131 $543,596 $3,124,217 Current Growth Rate 0.15% Projected Sales Next Year by Category Pool Hot Tub Contracts $1,476,489 $1,104,131 $543,596 0.25% 0.50% Growth Rate Create and manage scenarios Build a one-variable data table Use Solver with constraints 1. Open the PoolAnd Spa-09 workbook and click the Enable Editing button. The file will be renamed automatically to include your name 2. Review formulas a Select cell D5 on the Forecast worksheet. The text box explains the formula which uses 3D references to the Factors sheet b. Select cell B12. This is a reference to the growth factor in cell F3 on the Factors sheet. c click the Factors worksheet tab. The company predicts a general growth rate, but each category's potential is Increased or reduced by an adjustment factor 3. Create scenarios for the growth rate. a Create a scenario named 15% on the factors sheet for the existing data'with cell F3 as the changing cell b. Add another scenario named 6% and change the value to .005 c. Add scenarios for 1.26% and 1.76% growth rates. In the Scenarios Values dialog box, you must enter the decimal equivalent of the percentage (0125 and 0175) d. Show the 1.26% scenario in the worksheet. e Return to the Forecast worksheet 4. Buld the data table a. Select cell E17. It refers to cell H5.Cells F17 and G17 refer to cells H6 and H7, respectively b. Use the Fil handle to complete the growth rate percentages in cells D20:027 c Select the data table range. a use cell B12 as the column input cell. The results are incorrect because data table formulas must use data on the same worksheet as the data table 5. Edit formulas to use a reference on the same sheet as the data table, a select cell D5, a precedent cell for cell H6 Cells E17.G17 in the data table Indirectly refer to (**Factors/$F$3) in cells b. Eat the formula in cells to show (1+$B$12) in place (1*Factors/8F83) (Figure 9-96) The data tanie done not use Factors/5F6 in its calculations, so it need not be changed Reset Progress Back to top a c. Copy the formula in cells to the cells E6:65 and then to cells D6:07 The data table updates d. Redefine the bottom borders of cells as needed 012 E Auto GI os Gallery re 3123.339316413183 Tui 1:37.00 1801 ME Mart 2000 110110210310076 lores Cure c. Copy the formula in cell D5 to the cells E5:65 and toise then to cells D6:G7. The data table updates, d. Redefine the bottom borders of cells as needed e. Format data table results as Currency with no decimal places 6. Edita Solver problem to find minimum wages. a. Click the Crewt worksheet tab. b. Start a Solver problem to find the minimum value for Figure 2.96 Edited formula to refer to cell on the same sheet as the cell E9 by changing cells 05:08. Constraints for cell D8 are already entered c. Add constraints for each of cells 05:07 that reflect the information in column C in the worksheet. d. Add a constraint that cell D9 be less than or equal to 150 e. Use the GRG Nonlinear solving method (Figure 9-97). The times whese Grande data table De S. . Figure 9-97 Solver constraints for work crew problem 1. Keep the Solver solution f. Keep the Solver solution 7. Uninstall the Solver Add-in. 8. Save and close the workbook (Figure 9-98). Pool & Spa Oasis Projected Growth and Sales w TITE Pool & Spa Oasis Pool Cleanup Crew 1 ITUDESTE DET SERA HOME PR Figure 9-98 Completed worksheets for Excel 9-7 Pool & Spa Oasis Projected Growth and Sales Category Pool Construction Hot Tub Installation Maintenance Contracts Actual Q4 Last Year $325,000 $275,000 $200,000 Q1 Next Year $341,762 $275,413 $170,255 Q2 Next Year S359.388 $275,826 $144,934 Q3 Next Year $377,924 $276,239 $123,379 Q4 Next Year S397,415 $276,654 $105,029 Target Sales Projected Next Year $1,476,489 $1,104,131 $543,596 $3,124,217 Current Growth Rate 0.15% Projected Sales Next Year by Category Pool Hot Tub Contracts $1,476,489 $1,104,131 $543,596 0.25% 0.50% Growth Rate Create and manage scenarios Build a one-variable data table Use Solver with constraints 1. Open the PoolAnd Spa-09 workbook and click the Enable Editing button. The file will be renamed automatically to include your name 2. Review formulas a Select cell D5 on the Forecast worksheet. The text box explains the formula which uses 3D references to the Factors sheet b. Select cell B12. This is a reference to the growth factor in cell F3 on the Factors sheet. c click the Factors worksheet tab. The company predicts a general growth rate, but each category's potential is Increased or reduced by an adjustment factor 3. Create scenarios for the growth rate. a Create a scenario named 15% on the factors sheet for the existing data'with cell F3 as the changing cell b. Add another scenario named 6% and change the value to .005 c. Add scenarios for 1.26% and 1.76% growth rates. In the Scenarios Values dialog box, you must enter the decimal equivalent of the percentage (0125 and 0175) d. Show the 1.26% scenario in the worksheet. e Return to the Forecast worksheet 4. Buld the data table a. Select cell E17. It refers to cell H5.Cells F17 and G17 refer to cells H6 and H7, respectively b. Use the Fil handle to complete the growth rate percentages in cells D20:027 c Select the data table range. a use cell B12 as the column input cell. The results are incorrect because data table formulas must use data on the same worksheet as the data table 5. Edit formulas to use a reference on the same sheet as the data table, a select cell D5, a precedent cell for cell H6 Cells E17.G17 in the data table Indirectly refer to (**Factors/$F$3) in cells b. Eat the formula in cells to show (1+$B$12) in place (1*Factors/8F83) (Figure 9-96) The data tanie done not use Factors/5F6 in its calculations, so it need not be changed Reset Progress Back to top a c. Copy the formula in cells to the cells E6:65 and then to cells D6:07 The data table updates d. Redefine the bottom borders of cells as needed 012 E Auto GI os Gallery re 3123.339316413183 Tui 1:37.00 1801 ME Mart 2000 110110210310076 lores Cure c. Copy the formula in cell D5 to the cells E5:65 and toise then to cells D6:G7. The data table updates, d. Redefine the bottom borders of cells as needed e. Format data table results as Currency with no decimal places 6. Edita Solver problem to find minimum wages. a. Click the Crewt worksheet tab. b. Start a Solver problem to find the minimum value for Figure 2.96 Edited formula to refer to cell on the same sheet as the cell E9 by changing cells 05:08. Constraints for cell D8 are already entered c. Add constraints for each of cells 05:07 that reflect the information in column C in the worksheet. d. Add a constraint that cell D9 be less than or equal to 150 e. Use the GRG Nonlinear solving method (Figure 9-97). The times whese Grande data table De S. . Figure 9-97 Solver constraints for work crew problem 1. Keep the Solver solution f. Keep the Solver solution 7. Uninstall the Solver Add-in. 8. Save and close the workbook (Figure 9-98). Pool & Spa Oasis Projected Growth and Sales w TITE Pool & Spa Oasis Pool Cleanup Crew 1 ITUDESTE DET SERA HOME PR Figure 9-98 Completed worksheets for Excel 9-7