Session 3: Build a Forecasting Tool

The Spreadsheet Trap

A Forecast That Says One Number Is Lying

A forecast that produces a single number is making a quiet claim. The claim is that the future is knowable to within whatever precision the cell displays. Revenue: $4.2M. Headcount: 28. Cash at year-end: $1.1M. Three numbers. Each almost certainly wrong. Silent about by how much.

Nobody believes the claim when stated this baldly. Yet a typical board pack contains hundreds of forecasts presented exactly this way. Each one a single number. Each one polished. Each one wrong in some direction the model never names.

The leaders who get further with forecasting are not the ones with better point estimates. They are the ones who stop producing point estimates. A range, a scenario, a bounded set of plausible outcomes is more honest about what is and is not known. It is more useful in the room where the decision is made.

That is the first job of the tool you build today. Replace single numbers with bounded scenarios.

Assumptions You Cannot Find in Two Minutes

The second failure mode is structural. Most spreadsheet models bury their assumptions inside formulas. Three or four cells deep. No comments. Half the time inherited from someone who already left the company.

Then the chair asks a question about churn, pricing, or growth. And suddenly nobody knows where the assumption actually lives. The model exists. The model is unsearchable.

A good forecasting model does the opposite. It puts every key assumption on the surface. Named. Adjustable. Clearly labelled. Growth rate: 12% per year. Average deal size: $48,000. Churn: 4% per quarter. Headcount in Q3: 31. Six lines. Fully visible. Easy to change. The leader sees the levers, not the cells.

Models You Cannot Interrogate

The third failure mode happens after the meeting. A spreadsheet model answers the question it was built to answer. Any other question requires editing the spreadsheet, and most edits introduce bugs. So the model gets opened less and less. The team falls back on the version of the truth that lives in the partner-in-charge's head.

A scenario modeller flips the relationship. The leader opens it on Monday morning, moves a slider, reads the updated narrative, asks the next question. The model becomes a conversation. The conversation is the work.

That is the boundary today's session crosses. Spreadsheet forecasts on one side. Decision tools on the other. The point of this session is to build the second one and stop confusing it with the first.

5D Applied to Forecasting

Same Five Steps. New Artifact.

The 5D Framework is the same one used in Sessions 1 and 2: Define the problem, Discover the inputs, Design the program, Determine success, Deploy the program. The framework does not change. The application does. A forecasting tool is not a dashboard. The Define step changes. The Discover step changes. Design, Determine, and Deploy all change with the new use case.

The familiar shape is the speed advantage. Sessions 1 and 2 took ninety minutes each to walk through the 5Ds because the framework itself was new. Today the framework is muscle memory. Two activities, two artifacts, one working tool. The leader leaves with a file that opens in a browser on the same laptop that watched the build.

Today's Emphasis Shifts to Deploy

One difference is worth naming up front. In Sessions 1 and 2, the heavy lifting was Define and Discover. Get the program clear. Map the data. The work was largely upstream of the artifact.

Today the headline is Deploy. The failure mode for forecasting is specifically the model that gets built once and shelved. A forecasting tool nobody opens on Monday is a slide. That is why the activities push harder on the discipline of use: the FAST goal inside the Program Plan, the threshold alert inside the Modeller, the commitment a leader writes at the end of the session.

A good build is the easy half. Keeping it alive is the hard half.

Discover for Forecasting

A Forecasting Tool Reads the Future

A dashboard reads the current state of the business. The pipeline that feeds it is short. ERP for last week's revenue. CRM for this week's pipeline. Spreadsheet for the cash forecast. Mostly structured. Mostly owned. The Map from Session 2 covered it.

A forecasting tool reads the future. It needs different inputs.

It needs at least twenty-four months of time-series history to learn what normal looks like. It needs assumptions about the next twelve to thirty-six months, sourced from people rather than systems. It needs a model of the cost structure: which costs scale with revenue, which costs are fixed, which costs step up at headcount thresholds.

These inputs do not live in the ERP. They live in a hiring plan HR keeps in a Google Sheet. In a price book nobody has updated since the last off-site. In the partner-in-charge's read of the next two quarters. The Map from Session 2 is the starting point. The forecasting tool needs a different layer on top of it.

Six Inputs Cover Most Models

Across industries, six inputs cover most forecasting models. The list is short on purpose. A leader who can name all six for their business has the raw material for a working tool.

Revenue drivers. Volume times price, or growth rate on a base. The two or three signals that explain most of the top line. For a services firm, often number of clients times average fee. For SaaS, often new ARR plus net retention. For manufacturing, often units shipped times unit price.

Cost structure. Fixed costs that do not move with revenue (rent, software, base salaries) and variable costs that do (commissions, COGS, hosting). Most teams under-estimate the fixed portion until they try to model a downside, at which point the missing rent line shows up as a thirty-percent miss.

Headcount plan. The biggest cost lever in most businesses and the longest lead time. A hire decided today does not produce revenue for six months, but the salary lands immediately. The model needs the plan by month and by role, not a single annual number.

Pricing assumptions. Planned price increases. Discount strategy. Payment terms. A five-percent price rise the team is too nervous to put in writing is exactly the kind of assumption that belongs in the model, so it can be argued about with numbers in front of everyone.

Time-series history. Twenty-four months minimum. Thirty-six ideal. Forecasting without history is interpolation. With history it is extrapolation. The board cares about the second one.

One business-specific variable. Every business has one assumption that drives more outcome than any other. For a consultancy, utilisation. For SaaS, gross churn. For manufacturing, one large customer. This one gets a slider. Because every interesting conversation about the future will come back to it.

Drivers vs Outputs

Move Drivers. Watch Outputs.

The distinction sounds obvious. In most spreadsheet models, it is not respected.

A driver is a number you can change. Growth rate. Headcount. Churn. In a well-built model, every driver is a slider or an input field. The leader can move it and watch the model respond.

An output is a number the model produces. Revenue. Margin. Runway. The leader cannot edit an output directly. They edit drivers, and outputs recalculate.

The trap. A "revenue" cell in a spreadsheet is often a hardcoded value the team overwrote when the original formula stopped giving the answer they wanted. The cell looks like an output. It is acting as a driver. The model is no longer trustworthy because the line between input and output has collapsed.

A good model surfaces every driver on the same view as the outputs. The slider for headcount sits above the line that shows cash position. Moving the slider moves the line. Visible. Immediate. Physical.

They Will Move Two

One discipline you cannot skip. Two to three drivers. Not five. Not ten. Three.

A scenario modeller with thirty drivers is unusable. The leader will not move thirty things. They will move two. The two that matter. The rule is to pick the two to three drivers that explain most of the variance in the outcome, expose those as sliders, and park the rest as constants the leader can override later.

For a typical services firm: number of clients, average fee, headcount. For SaaS: new ARR, gross churn, gross margin. For manufacturing: units shipped, unit price, one key cost input. If a leader cannot name their two to three drivers in a sentence, the modeller is going to be a mess.

The interview in Activity 3.2 (next section) exists to force the answer. Walking out of the activity with more than three drivers is a sign to go back and cut before starting the build.

Design Your Build

Interview · Build · Self-Serve

Three steps move a forecasting program from idea to working tool.

Step one: Interview. A conversation in plain language. The leader describes the business: the recurring decision, the data sources, the program shape, the FAST goal, the deployment. No formulas are written at this stage. The output is the AI Program Plan, a five-section document one of the activities in this session produces.

Step two: Build. The Plan is handed back to the AI in a second pass. The AI produces an HTML file in three visible passes (described below). Each pass is checkpointed. The leader looks at the output before the next pass begins.

Step three: Self-serve. Once the file is built, the AI is no longer in the loop. The leader opens the file on Monday morning, moves a slider, reads the narrative, asks the next question. The tool is on the leader's laptop and answers to nobody.

Twenty-five minutes plus thirty-five minutes plus a double-click for the rest of the year. That is the whole workflow.

Three Visible Passes

The three-pass pattern is the same one used to build the dashboard in Session 1. It is not a stylistic preference. It is a guard against the standard AI failure mode: AI runs ahead, applies styling to numbers that are still wrong, and produces a file that looks complete and is not.

Pass one. Numbers and layout. The math first. Sliders, the projection table, the recalculation logic. No fonts. No colors. The single question to answer at this checkpoint: do the numbers tie to the Brief? If yes, advance. If no, fix before continuing.

Pass two. Styling and palette. The design system applied. Layout breathes. Colors carry meaning. The single question at this checkpoint: does the file look like a tool a senior leader would open Monday morning? If yes, advance. If no, adjust.

Pass three. Narrative and chart. The self-rewriting paragraph at the top. The line chart that moves when sliders move. The sensitivity readouts under each slider. The single question at this checkpoint: does the narrative read like a senior analyst wrote it, and does the chart actually update? If yes, the file is done. If no, fix before delivering.

Three checkpoints. Three small corrections. One working file. The AI Buddy in Activity 3.3 will pause at each checkpoint and ask if the output looks right. The right answer is not always yes. Catch the problem in pass one when it is one line of math, not in pass three when it is three sheets of formatting.

Three Scenarios

Base · Upside · Downside

A scenario modeller with one projection is a forecasting spreadsheet with sliders. A scenario modeller with three projections is a decision tool. The shift from one to three is structural, not cosmetic.

Base case. The most likely outcome given current assumptions. The numbers a leader would put in a board pack if asked for a single answer.

Upside. What happens if the one or two assumptions the leader is most uncertain about land in their favour. Not "everything goes well." One or two specific things change. The rest of the model is held constant.

Downside. Same principle. Same one or two things land badly. The point is symmetry: the upside and downside should be drawn from the same set of uncertain assumptions, just moved in opposite directions.

The three scenarios bound the conversation. They do not eliminate uncertainty. They make uncertainty legible. A toggle switches between them; numbers, narrative, and chart all update. The leader stops debating which scenario is right and starts asking what each implies for the decision in front of them.

Sliders, Not Cells

Every driver gets a control. A slider for percentages. An input field for integers. A dropdown for categories.

The principle is physical. A cell with a number requires the leader to know that the cell is a driver: click into it, type, trust that the dependency graph will update. A slider tells the leader, with no instruction, that the value is meant to be changed.

The user interface is the documentation. That is why the modeller opens in a browser, not in Excel and not in a BI tool. In a browser, the physical metaphor is right. The slider moves. The number updates. The line on the chart redraws. Nothing about the interaction needs to be explained.

The Self-Rewriting Narrative

The same principle that powered the dashboard auto-narrative in Session 1 powers the scenario narrative here. A paragraph at the top of the modeller, generated from the active scenario, naming specific numbers, updating whenever a slider moves.

Three sentences. No filler. The first names the headline number: "Base case projects Year 3 revenue of $5.4M with 28% gross margin." The second names the one assumption that most affects the outcome and its current value: "Utilisation is set to 72%, the assumption that moves this scenario the most." The third names one thing the leader should pay attention to: "Cash dips below three months of runway in Q3 Year 2 if utilisation drops below 70%."

The narrative is what makes the modeller a tool a leader can hand to a colleague without a briefing. The colleague opens the file, reads the paragraph, understands the scenario in fifteen seconds, and starts asking their own questions. That is what turns a model into a tool.

Three Years. Then Fiction.

The horizon discipline. Year one is a budget, with monthly granularity and specific numbers the team is committing to. Year two is a plan, quarterly granularity. Year three is a direction, annual granularity, signalling shape rather than precision.

Beyond three years a model is writing fiction. The uncertainty compounds too fast. A five-year projection is signalling ambition, not analysis. Three years is the honest horizon, and the Modeller built in Activity 3.3 reflects that: month-by-month for Year 1, quarter-by-quarter for Year 2, annual for Year 3, and nothing past Year 3.

From Forecasting Tool to Decision Tool

Questions You Could Not Ask in Time Before

The right way to evaluate a scenario modeller is not whether the numbers are correct. The numbers are an output of assumptions. The assumptions are debatable. The right way is whether the leader uses it to ask questions they could not ask before.

Before signing a contract. What happens to year-two margin if this client lands?

Before approving a hire. What does the cash position look like in eight months if utilisation drops five percent?

Before raising capital. What does the downside need to look like to justify the dilution?

Before walking into a renewal. What does our position look like if this customer churns?

None of these have one right answer. All of them have a numeric answer more useful than a qualitative one. All of them sharpen the decision. The tool does not predict the future. It bounds the conversation about it.

The Monday Test

The discipline of use is the difference between a program and a slide. A tool the leader opens on Monday morning before the leadership meeting, to check three scenarios against the week's news, is a program. A tool that gets built once and forgotten is a slide. The difference is not the quality of the build. It is the discipline of the FAST goal and the named commitment.

Commit, in writing, to one specific decision in the next thirty days where you will open this file before you make the call. A hiring decision. A pricing change. A board prep. A vendor negotiation. A capital decision. Be specific. Date inside the next thirty days. Decision named. That commitment is what carries the tool from a session artifact into the work.

Back to the Board Meeting

Remember the founder from the opening. The chair leaning forward. The churn question. The "let me come back to you on that" she never did. You can answer it now.

You open your modeller. You move the churn slider. You read the narrative that just wrote itself. You say, "If our biggest client churns next quarter, here is what Year 2 margin does. Here is what headcount has to do to compensate. Here is the decision in front of us." You sit down. The chair nods.

That is the boundary the program moves. The numbers were always there. The question was always answerable. What was missing was a tool that closed the loop in seconds instead of hours. In front of the room, instead of in a follow-up email that never gets sent.

Six months ago, building that tool took a small engineering team, a project plan, and a budget. Today it took you, an AI Buddy, and one Friday afternoon.

Brief, Map, Pipeline Spec, Scenario Modeller

Four artifacts now travel together. The Brief from Session 1 names the program. The Data Map from Session 2 names what AI can reach. The Pipeline Spec from Session 2 names the plumbing. The Scenario Modeller from this session is the decision tool the program produces.

The first three are infrastructure. The fourth is the working file on the leader's laptop, the one that answers the question the business actually asks. A leader who keeps all four in a single folder, named clearly, accessible to the small group of people who will use them, has more documented AI infrastructure than most mid-market finance teams in any industry.

The artifacts are not deliverables. They are operating documents. They are revised as the business changes, as new sources come online, as the FAST goal gets re-measured against the actual cadence of use. The program is the discipline of keeping all four alive in the same folder, refreshed by the same person on the same cadence.

This is the end of the Dashboards and Finance series. You walked in three sessions ago with nothing. You walk out with a tool a finance team six months ago would have paid a contractor twenty thousand to build. Use it on Monday.