Key Topics & Core Concepts

Assignment Overview & Deliverables

Pool & Spa Oasis models forward sales projections across Pool Construction, Hot Tub Installation, and Maintenance Contracts. You will define growth scenarios, correct a cross-sheet precedent defect that invalidates a one-variable data table, and use Excel Solver to minimize weekly payroll expenditures under strict crew scheduling constraints.

Step-by-Step Instructions

Step 1: Open Starter & Define Growth Scenarios

  1. Download and open PoolSpa-09.xlsx, saving it as [your initials] PoolSpaOasis.xlsx.
  2. On the Forecast sheet, review cells D5 and B12, noting their dependencies on the Growth Factors tab.
  3. Switch to the Growth Factors worksheet tab. Select cells B3:C3 and B6:C8, then on the Formulas tab, click Create from Selection using the Left column.
  4. Open the Scenario Manager (Data > What-If Analysis > Scenario Manager).
  5. Create scenario .15% using cell C3 with the current value.
  6. Add scenario .5% with a value of 0.005.
  7. Add scenario 1.25% (value 0.0125) and scenario 1.75% (value 0.0175).
  8. Select the 1.25% scenario and click Show. Close the dialog.

Step 2: Trace Precedents & Fix Cross-Sheet Data Table Defect

  1. Return to the Forecast sheet. Notice cell B12 now reflects the 1.25% scenario.
  2. In the Data Table header row, confirm cell E18 points to H5, F18 to H6, and G18 to H7.
  3. Select cells D21:D28 and fill the growth rates from 0.75% through 2.50% in increments of 0.25%.
  4. Select the data table range D18:G28, open What-If Analysis > Data Table, and enter B12 as the Column input cell. Click OK.
  5. Notice the resulting rows replicate identical values. Select cell E18 and click Trace Precedents twice to audit the failure. The calculation fails because data table inputs cannot resolve references across external worksheets.
  6. Click Remove Arrows. Select cell D5 and rewrite its general rate reference to bind directly to cell B12 on the same worksheet:
    =C5*(1+$B$12)*'Growth Factors'!$C$6
  7. Copy cell D5 across E5:G5, then down through D6:G7. The Data Table will recalculate immediately.
  8. Format data table results in E19:G28 as Currency with zero decimal places. Reapply standard bottom borders to rows 5 and 6 as needed.
  9. Apply Conditional Formatting > Highlight Cells Rules > Equal To across cells D19:D28, setting the condition to cell =$B$12 formatted with Light Red Fill with Dark Red Text.

Step 3: Formulate and Solve Labor Minimization in Solver

  1. Switch to the Crew1 worksheet tab.
  2. On the Data tab, click Solver.
  3. Configure the objective parameter:
    • Set Objective: $E$9 (Total Payroll Expense)
    • To: Select Min (Minimization)
    • By Changing Variable Cells: $D$5:$D$8 (Assigned Employee Hours)
  4. Add employee minimum and maximum scheduling bounds based on column C:
    • $D$5 >= 35 and $D$5 <= 45
    • $D$6 >= 38 and $D$6 <= 50
    • $D$7 >= 40 and $D$7 <= 48
    • (Verify that constraints for cell D8: >= 32 and <= 40 are already present)
  5. Add the total crew capacity ceiling constraint:
    $D$9 <= 150
  6. Ensure the solving method is set to GRG Nonlinear (or Simplex LP).
  7. Click Solve. When the solution is found, choose Keep Solver Solution and click OK.
  8. Confirm that total hours in cell D9 evaluate to 145 and minimum payroll in cell E9 equals $3,050.50.

Step 4: Pre-Flight Audit & Canvas Submission

  1. Verify all numbers across both sheets against the reference solutions: Data Table row 1.25% must match cells H5:H7 exactly.
  2. Save your workbook as [your initials] PoolSpaOasis.xlsx.
  3. Perform an autograder pre-flight check at /teaching/ISMG-2050/submit.html.
  4. Submit your finalized workbook directly to Canvas before the cutoff.

Refer to Canvas for official submission cutoff and penalty policies.

Key Skills Evaluated