Scenario Analysis Spreadsheet: Compare Best-Case, Base-Case, and Downside Business Plans

Overview

Scenario analysis is a way to examine how a plan changes when important assumptions change. Instead of rewriting a forecast for every possibility, you create a central assumptions table and allow the model to select the values for a best-case, base-case, or downside scenario.

A useful scenario analysis spreadsheet separates three layers:

• Inputs: The operating assumptions, such as customers, conversion rate, average order value, hiring date, payment timing, or unit cost.
• Calculations: Formulas that turn inputs into revenue, expenses, profit, cash movement, and other measures.
• Outputs: A comparison table and decision summary that show what changes across scenarios.

The purpose is not to predict the future precisely. It is to identify which assumptions matter, understand the size of the possible outcome range, and agree on actions before conditions change. A three-case financial model is often sufficient for planning, while a sensitivity analysis spreadsheet is better when you need to test one driver at a time.

For a complete planning workflow, pair the model with a strategic planning spreadsheet that assigns owners and quarterly targets. Link cash-focused decisions to a cash flow forecast template, because a profitable plan can still create a short-term cash requirement.

How to estimate

Build the spreadsheet in a sequence that keeps assumptions visible and formulas reusable.

1. Define the decision. Start with a question such as, “Can we hire one additional employee next quarter?” or “What sales level makes this launch financially acceptable?” State the decision, planning period, and measures that will determine the answer.
2. Choose the drivers. Use independent drivers rather than entering a final revenue or profit figure directly. For example, calculate revenue from customers multiplied by average revenue per customer, or units sold multiplied by price.
3. Create a scenario table. Place assumptions in rows and scenarios in columns. A simple layout might use columns B, C, and D for Best Case, Base Case, and Downside. Keep the assumption name and unit in column A.
4. Add a selector. In Excel or Google Sheets, create a cell called SelectedScenario and add a dropdown containing Best Case, Base Case, and Downside. A lookup formula can then retrieve the relevant value for each model input.
5. Build the calculation layer. Reference the selected assumptions rather than typing scenario values into operating formulas. This prevents the model from becoming a collection of disconnected cases.
6. Summarize the outputs. Show revenue, gross profit, operating expenses, operating profit, ending cash, and any decision-specific metric side by side for all three scenarios.

In Excel, a lookup formula such as XLOOKUP can return the value from the selected scenario column. In Google Sheets, XLOOKUP may also be available depending on the account and feature set; INDEX and MATCH provide a broadly understood alternative. For example, a formula can use the selected scenario as the lookup value and the row of scenario names as the lookup range. Keep formulas simple enough that another person can audit them.

Use sensitivity analysis when the question is narrower. For example, a two-variable data table can show profit at several price and volume combinations. Use break-even analysis when the main question is the sales level required to cover fixed and variable costs. A break-even analysis spreadsheet is usually more focused than a full scenario model.

Inputs and assumptions

The quality of the result depends more on the assumptions than on the visual design. Record each assumption with a unit, period, owner, and rationale. Avoid labels such as “high sales” or “low costs” without defining what they mean.

Define the base case first. It should represent the plan the team currently expects to execute, not an ideal outcome. Then define the best and downside cases by changing specific drivers. A downside case might combine slower customer growth, longer collection timing, and a higher unit cost. Document why those drivers were selected.

Do not change every assumption simply to make the scenarios look different. Excessive changes make it difficult to identify the main source of risk. A good model distinguishes controllable assumptions, such as hiring timing, from external assumptions, such as demand or supplier cost. Include a notes column for the source, owner, review date, and confidence level.

Use consistent signs and units. Enter expenses as positive inputs and subtract them in the profit formula, or enter them as negative values and add them consistently; do not mix both conventions. Add checks for missing inputs, negative volumes, duplicate scenario names, and formulas that return errors. Review the finished file with a structured spreadsheet audit checklist.

Worked examples

Suppose a business sells a service. The model calculates monthly revenue as:

Revenue = Customers × Average Revenue per Customer

It calculates gross profit as:

Gross Profit = Revenue − (Customers × Variable Cost per Customer)

Assume the base case uses 100 customers, average revenue of 120 per customer, and variable cost of 45 per customer. The resulting revenue is 12,000, variable cost is 4,500, and gross profit is 7,500 for the modeled period. These figures are illustrative model inputs, not a forecast for a particular market.

Now create two alternative cases by changing drivers rather than overriding outputs. The best case might use 125 customers and an average revenue of 125, while the downside might use 75 customers and an average revenue of 110. Add monthly fixed expenses to calculate operating profit. If customers pay after delivery, also apply collection timing to estimate the cash received during the period.

Your comparison table might include:

• Revenue and growth versus the base case
• Gross margin percentage
• Operating profit or loss
• Minimum cash balance
• Required funding or spending reduction
• Trigger that would cause management to switch plans

For example, the decision rule could be: proceed with the planned hire only if the base case maintains a defined cash buffer and the downside case has a documented spending response. The spreadsheet should not make that judgment automatically unless the rule is explicit. Use a decision summary to connect numbers to actions, owners, and dates.

For sales-driven planning, compare these results with a sales forecast spreadsheet. For working-capital risk, review an accounts receivable aging template rather than assuming every sale becomes cash immediately.

When to recalculate

Recalculate the model whenever a decision depends on changed inputs. At minimum, review it before an annual budget, launch, hiring commitment, major purchase, funding request, or quarterly forecast. Revisit it when pricing inputs, supplier costs, staffing plans, payment timing, sales conversion, or cash balances change.

Use a simple update routine:

1. Copy the prior version or save a dated snapshot.
2. Update actual results and replace outdated assumptions.
3. Record what changed and who approved the change.
4. Recalculate all scenarios, not only the selected case.
5. Check formulas, totals, units, and cash timing.
6. Review whether the decision triggers and actions still apply.

Refresh the model when benchmarks, rates, pricing, or operating conditions move, but avoid changing assumptions without documenting the reason. If the model is used in monthly reporting, connect its key outputs to a KPI dashboard spreadsheet so actual performance can be compared with the selected plan.

Finally, treat the scenario analysis spreadsheet as a decision tool, not a one-time report. Keep the assumptions centralized, test one driver at a time when investigating risk, and convert material differences into a named action with an owner and trigger date. That process makes the workbook easier to update and more useful when the next planning decision arrives.