Practice 20: Sensitivity Analysis & Data Tables

Key Topics & Core Concepts

Two-Variable Data Table Architecture: Configuring matrix grids evaluating two independent input parameters simultaneously.
Corner Reference Binding: Linking the top-left corner cell of the table matrix directly to the primary output calculation formula.
Array Formula Generation: Utilizing the Data Table command to populate the array table using the {=TABLE(row_input, col_input)} structure.
Custom Number Masking: Applying custom format masks (;;;) to visually conceal the corner linking formula without breaking calculation dependency.

Assignment Overview & Deliverables

In this concluding spreadsheet modeling practice lab, you will construct a two-variable sensitivity data table evaluating the combined impact of changing nightly rental rates and occupancy levels on Perfect Vacation Rentals annual net profit.

Starter Workbook: Download PerfectRentals-09.xlsx (or continue with your completed file from Practice 19)
Completed File Name: [your initials] Practice20_DataTables.xlsx
Active Worksheets: Forecast Model and Sensitivity Matrix

Step-by-Step Instructions

Step 1: Open Starter & Review Base Assumptions

Download and open PerfectRentals-09.xlsx in Excel (or continue from Practice 19).
Click Enable Editing if opened in Protected View.
Save the workbook as [your initials] Practice20_DataTables.xlsx.
Click the Sensitivity Matrix worksheet tab.
Review the sensitivity matrix structure laid out across range B5:H12.

Step 2: Enter Row and Column Input Intervals

In row 5, enter nightly rates across columns C through H: $220, $240, $260, $280, $300, and $320.
In column B, enter occupancy percentages down rows 6 through 12: 50%, 55%, 60%, 65%, 70%, 75%, and 80%.
Format row 5 as Currency ($#,##0) and column B as Percentage (0.0%).

Step 3: Link Corner Formula to Primary Net Income

Click cell B5 (the intersection corner of the sensitivity matrix).
Type: ='Forecast Model'!B12 (linking to Net Operating Income) and press Enter.
To hide the distracting corner number while preserving the calculation link, press Ctrl + 1, go to Custom, and type ;;; (three semicolons) in the Type field. Click OK.

Step 4: Execute the Two-Variable Data Table Command

Select the entire matrix range including headers and corner cell: B5:H12.
On the Ribbon, go to Data > What-If Analysis > Data Table....
In the dialog box:
- Row input cell: Click the Forecast Model tab and select cell $B$5 (Base Nightly Rate).
- Column input cell: Select cell $B$4 (Occupancy Rate).
Click OK.
Verify that Excel populates the inner cells with array formula {=TABLE(B5,B4)} displaying net income projections across all rate and occupancy permutations.

Step 5: Apply Conditional Formatting & Save Workbook

Format the inner result matrix (cells C6:H12) as Currency ($#,##0).
Select range C6:H12. On the Home tab, click Conditional Formatting > Color Scales and apply the Green - Yellow - Red Color Scale.
Auto-fit all column widths to eliminate any text truncation or ### display errors.
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
Data Table Setup	Matrix bounded by input vectors with valid corner linking cell.
Two-Variable Solving	`{=TABLE(row, col)}` syntax successfully populating matrix.
Corner Masking	Custom number format mask (`;;;`) concealing corner formula cleanly.
Visual Formatting	Dynamic 3-color scale highlighting highest and lowest profit outcomes.