Practice 19: Scenario Modeling & Goal Seek Analysis

Key Topics & Core Concepts

Back-Solving with Goal Seek: Using Excel's iterative Goal Seek solver to adjust an independent input cell until a dependent formula hits an exact target value.
Scenario Manager Setup: Defining changing cells to store, view, and switch between competing business conditions (Expected, Best Case, Worst Case).
Scenario Summary Reporting: Compiling structured cross-scenario pivot reports comparing changing inputs against bottom-line result cells.
Cell Naming for Scenarios: Naming precedent cells prior to running scenarios so summary reports generate readable business labels instead of cell coordinates.

Assignment Overview & Deliverables

In this practice lab, you will model uncertainty in Perfect Vacation Rentals financial forecasts by back-solving minimum occupancy rates using Goal Seek and evaluating multi-variable revenue scenarios using Scenario Manager.

Starter Workbook: Download PerfectRentals-09.xlsx
Completed File Name: [your initials] Practice19_Scenarios.xlsx
Active Worksheets: Forecast Model and Scenario Summary

Step-by-Step Instructions

Step 1: Open Starter & Review Forecast Model

Download and open PerfectRentals-09.xlsx in Excel.
Click Enable Editing if opened in Protected View.
Save the workbook as [your initials] Practice19_Scenarios.xlsx.
Click the Forecast Model worksheet tab.
Review input assumptions in cells B4:B7 (Occupancy Rate, Base Nightly Rate, Operating Expense Ratio, and Annual Capital Reserve).

Step 2: Assign Defined Names to Precedent Cells

Select range A4:B7. Go to Formulas > Create from Selection, ensure Left column is checked, and click OK.
Select range A10:B12 (Gross Revenue, Operating Expenses, Net Operating Income).
Create defined names from the left column selection.
Confirm in the Name Box that cells now carry semantic names (e.g., Occupancy_Rate, Net_Operating_Income).

Step 3: Back-Solve Required Occupancy Using Goal Seek

Determine the occupancy rate required to generate a Net Operating Income of exactly $150,000.
Select cell B12 (Net Operating Income formula cell).
On the Ribbon, go to Data > What-If Analysis > Goal Seek....
In the dialog, verify:
- Set cell: B12
- To value: 150000
- By changing cell: $B$4 (Occupancy Rate)
Click OK. When Goal Seek Status finds a solution, note the calculated occupancy rate and click OK to keep the result.

Step 4: Build Comparative Cases in Scenario Manager

Go to Data > What-If Analysis > Scenario Manager....
Click Add to create the baseline scenario:
- Scenario name: Baseline Current
- Changing cells: B4:B5
- Values: keep current occupancy and base rate. Click OK.
Click Add to create the Best Case:
- Scenario name: Peak Expansion
- Values: Occupancy = 0.85, Base Rate = 310. Click OK.
Click Add to create the Conservative Case:
- Scenario name: Off-Season Slump
- Values: Occupancy = 0.55, Base Rate = 220. Click OK.

Step 5: Generate Scenario Summary Report & Save

In the Scenario Manager dialog, click the Summary... button.
In the Report type section, verify Scenario summary is selected.
In the Result cells field, select B10:B12 (Revenue, Expenses, and NOI). Click OK.
Verify that Excel generates a new Scenario Summary worksheet tab with defined names displayed in the left column.
Format all monetary values as Currency ($#,##0) and percentages as Percentage (0.0%).
Save your completed workbook.

Practice exercises are formative and prepare you for homework submissions. Refer to Canvas for official submission cutoff and penalty policies.

Key Skills Evaluated

Skill Area	Verification Target
Goal Seek Back-Solving	Iterative target resolution on net operating income cell by modifying occupancy rate.
Defined Name Scoping	Semantic names created from selection for clean summary report presentation.
Scenario Manager	Multiple condition sets configured with changing variable input vectors.
Summary Reporting	Dedicated Scenario Summary worksheet generated comparing changing vs. result cells.