Split the Workbook into Logic, Inputs and Outputs

On Monday your FY25 revenue forecast shows $5,200k. On Tuesday it shows $5,300k. Before anyone can explain the $100k, you need to know which of three things happened:

1. Logic change. Someone edited a formula, so the model now calculates differently. Example: the revenue formula now applies a price uplift it didn't apply before.
2. Assumption change. Someone edited a driver value. A driver is an input that pushes the forecast, such as a growth rate or a price. Example: revenue growth went from 4% to 6%.
3. Scenario switch. Someone selected a different scenario, a named set of driver values such as Base or Upside. No single value was edited. A different set simply became active.

All three can produce the same $100k movement. They mean very different things, though. A logic change might be a bug. An assumption change needs a reason and an owner. A scenario switch might just mean someone opened the wrong version. This lesson sets up the workbook so you can tell them apart.

Look at this formula for FY25 revenue:

=B5*1.04

B5 holds FY24 revenue of $5,000k, so the result is $5,200k. The 4% growth rate is real and it drives the forecast, but it is buried inside the formula. That makes it a hidden assumption, sometimes called a hardcode:

• Nobody can see it without clicking into the cell.
• Nobody can label it, so its unit and period are unknown.
• Nobody can log a change to it. If someone edits 1.04 to 1.06, that looks like a formula edit, and it is hard to tell from a real logic change.

The fix is to move the driver into its own labelled input cell. Put 4% in C3, label it 'Revenue growth, % per year, FY25', and change the formula to:

=B5*(1+C3)

The result is still $5,200k. Now the growth rate is visible, and a change to it is clearly an assumption change. Try the sliders to see how much the forecast depends on that one value.

A clear forecast workbook has three separate areas. They can be separate tabs, or clearly marked blocks on one tab.

• Inputs hold typed values such as growth rates, prices and headcount. They can also hold simple links to other inputs. For example, an Upside growth cell can point to the Base growth cell when the two are the same. Inputs contain no calculations.
• Calculations hold the model logic. Every formula references input cells or other calculation cells, and contains no typed driver values.
• Outputs show the results people read, such as revenue, gross margin and EBITDA by year. They pull from calculations.

These rules map neatly onto the three types of movement. An edit in the calculation area is a logic change. An edit in the input area is an assumption change. Switching which set of inputs is active is a scenario switch. Once each type lives in its own area, the place where something changed tells you what kind of change it was.

A cell that just says 4% raises questions. Is it growth or churn? Per month or per year? For FY25 or FY26? If someone reads it as monthly, they will think the forecast is wildly off.

Give every input a visible label with three parts:

• What it is: Revenue growth
• Unit: % per year
• Period: FY25

Together, these make 'Revenue growth, % per year, FY25'.

The unit matters most when two units are easy to confuse. A price can be per month or per year. A headcount can be people or FTEs (full-time equivalents). A cost can be in dollars or thousands of dollars. The period matters because drivers often differ by year, and a reviewer needs to know which year a value applies to.

Put the label in the cell next to the value, not in a comment that only appears on hover. Anyone opening the workbook should be able to read the period and units without clicking anything. Flip the cards to see bare numbers turned into clear inputs.

Excel has two tools for finding hardcodes, and each one finds something different.

• Formulas > Show Formulas (Ctrl+on Windows) displays every formula instead of its result. Scan the calculation area for numbers typed inside formulas, such as=B5*1.04`.
• Home > Find & Select > Go To Special > Constants selects cells that hold typed values instead of formulas. In the calculation area, this catches problems like a cell where someone typed 5,250 over a formula. It does not find numbers inside formulas, so use both tools.

Then decide what each hardcode is:

• Driver. It is a business assumption that could change, such as 1.04 for growth. Move it to a labelled input cell.
• Fixed conversion. It never changes, such as 12 months in a year or 1,000 to convert dollars to thousands. This can usually stay in the formula.
• Error. It shouldn't be there, such as a typed value overwriting a formula. Restore the formula.

If you're unsure whether a number is a driver, ask whether someone might ever want to change it in a scenario. If they might, treat it as a driver.

Moving drivers into input cells should change the layout, not the results. If FY25 revenue was $5,200k before you restructured, it must be $5,200k afterwards.

Work in this order:

1. Save a copy of the current version with a clear name, such as Forecast_FY25_v3_before_restructure.
2. Note the key outputs from that copy, such as revenue, gross margin and EBITDA for each forecast year.
3. Create the input area and move each driver into its own labelled cell.
4. Relink the formulas so they reference those inputs, for example changing =B51.04 to =B5(1+C3).
5. Compare the outputs with your saved copy. Every difference should be zero.

If a number has moved, something other than the layout changed. You might have linked a formula to the wrong cell, or found an overwritten value and restored the formula without noticing. Find the cause before you go any further. Otherwise your first logged change will be a movement nobody can explain, which is exactly the problem you're trying to fix.