Chapter 7: Forecasting, Budgeting, and What-If Analysis in Excel

Accounting Analytics: An Integrated Approach

In this chapter

Part II of V: Accounting Analytics with Excel

Chapter 4 of 5 in this Part

• 7.1 From Explaining to Predicting and Deciding
• 7.2 Correlation and Regression
• 7.3 Forecasting and Backtesting

• Guided Tutorial 7.1
• 7.5 Budgets, Flexible Budgets, and Variances
• Guided Tutorial 7.2
• 7.7 Cost-Volume-Profit Analysis and What-If Tools
• Guided Tutorial 7.3
• 7.9 Communicating Forecasts and Models
• 7.10 Looking Ahead

This chapter turns from explaining the past to estimating the future and evaluating decisions. Each module follows a section of the chapter, and each guided tutorial answers one of the controller’s three requests.

Learning objectives

1. Measure the relationship between two variables with correlation and regression in the Data Analysis ToolPak, and judge the result with R², p-values, and residuals.
2. Forecast with TREND and FORECAST.LINEAR, and evaluate a forecast by backtesting it against simple benchmark methods.
3. Compare actual results with a static and a flexible budget, and explain sales-volume and flexible-budget variances.
4. Build a contribution margin model with documented assumptions, and calculate break-even points.
5. Evaluate a decision with Scenario Manager, data tables, and Goal Seek, and describe how Solver finds the best plan under constraints.
6. Evaluate how precise a regression-based expectation is and how consistent a forecast is with experience, and identify the evidence needed to follow up unusual results and unapproved changes.

The objectives run from measuring relationships and forecasting, through budgets and variances, to models that evaluate a decision and the auditor’s tests of an expectation or a forecast. Each pairs a tool with the test that makes it trustworthy.

A forecast, a budget review and a decision

• The controller is preparing the planning meeting for next year
• She needs a revenue forecast she can defend against the sales team
• She wants to know how the year compared with a budget she suspects
• And whether to repeat the Furniture Seasonal Promotion

Together, the requests use what happened in 2026 to forecast and plan 2027. The chapter teaches the Excel tools that answer such questions, and the discipline of testing a model before anyone relies on it.

7.1 From Explaining to Predicting and Deciding

From what happened to what could happen

• Predictive analytics estimates what is likely, such as next quarter’s revenue
• Prescriptive analytics recommends what to do, such as whether to repeat a promotion
• A forecast cannot be checked against the records until the period is over
• So every model here is paired with a test

The tests are a residual plot that shows where a regression fails, a backtest that compares a forecast with what happened, a flexible budget that separates volume from price, and a documented list of assumptions. Part II has now used all four analytics types from Chapter 1.

7.2 Correlation and Regression

Correlation measures a straight-line relationship

• CORREL, or the ToolPak’s correlation matrix, ranges from −1 to +1
• It measures only linear association, so a curve can show almost none
• It says nothing about cause: a third variable may drive both
• One unusual observation can change it, and the conclusion with it

With the start-up month included, monthly revenue seems to move with the number of customers invoiced; without it, revenue moves more with the average size of a line, as Tutorial 7.1 shows. Look at the data before reading any coefficient. The ToolPak is an add-in you enable under File, Options, Add-ins, or under Tools, Excel Add-ins on a Mac.

Regression fits a least-squares line

• The line minimizes the squared vertical distances from the points
• The intercept is the value at zero; the slope is the change per unit of x
• Accountants use it to split mixed costs and model costs against activity
• Auditors use it to build expectations to compare with recorded amounts

Datar and Rajan treat regression as one of the quantitative methods for estimating how costs behave. The dependent variable is the one you want to explain; the independent variable is the one you explain it with.

Read four numbers in the regression output

Figure 7.2 · Regression Output from the Data Analysis ToolPak

R Square is the share of the variation the line explains, and Significance F the chance of a fit at least this good if the true slope were zero and the regression’s assumptions held. The slope’s p-value below the conventional five percent threshold marks it as statistically significant, and with one x variable it equals Significance F.

One start-up month makes a trend look significant

Figure 7.1 · Monthly Revenue with Two Fitted Trends

Fitted to all the months, the line slopes upward and its slope is significant, but almost half of that slope comes from the start-up month, when the company issued about half its usual invoices. Without that month, the line rises about half as steeply and its slope is no longer significant, which is not proof that there is no trend, only that the data give no reliable evidence of one.

Watch out: a significant slope does not prove a trend

• One unusual point at an end can tilt a line enough to make its slope significant
• Data sets with identical lines and fit statistics can look nothing alike (Anscombe)
• Plot the data and the residuals before you report a regression
• Investigate a point that stands apart; report with and without it, and why

A residual plot should look like a patternless band around zero. A single point far from the rest, or a curve, means the line describes the data poorly.

7.3 Forecasting and Backtesting

Trend functions extend the line

• FORECAST.LINEAR gives the line’s value at a new x; TREND gives several
• TREND spills its values into a spill range; A5# refers to the whole range
• A formula returns #SPILL! rather than overwrite cells in its way
• UNIQUE and SORT are dynamic arrays too, like Chapter 6’s FILTER

Add Trendline draws the same line on a chart and can show its equation and R Square. These tools make a linear forecast quick to produce, which is exactly why it needs testing: extending a line assumes the past continues, and that it was a line.

A forecast must beat the simple benchmarks

Table 7.1 · Benchmark forecasting methods and the linear trend

Method	Forecast of a future period	Excel example	Works well when
Mean	The average of the historical values	=AVERAGE(C3:C34)	The series fluctuates around a stable level
Naive	The last observed value	=C34	The series wanders without a stable level
Seasonal naive	The value of the same season a year earlier	=C23 for the next month, =C31 for the next quarter	There is a strong seasonal pattern
Drift	The last value plus, for each period ahead, the average change from the first value to the last	=C34+(C34-C3)/31 for the next period	The series grows or shrinks at a steady average rate
Linear trend, a regression	The least-squares line fitted to all the values, extended	FORECAST.LINEAR or TREND, as in Tutorial 7.1	The values lie close to a straight line

Hyndman and Athanasopoulos recommend comparing every method with the four benchmarks, because a method that cannot beat them is not worth using. Each is a single Excel formula. The last row, the linear trend, is a regression rather than a benchmark: it is the method Tutorial 7.1 tests against them. Drift draws its line through the first and last values only; the trend fits it to all of them.

A backtest forecasts a period with a known answer

• Fit the method to an earlier part of the history, and hold back a later part
• Forecast the held-back part and compare with what actually happened
• Take each absolute error as a percentage of actual revenue; average those for MAPE
• The most recent quarter with actual results is the natural holdout

A backtest cannot guarantee future performance, but a method that fails on the recent past should not be trusted with the next quarter. Budgets are negotiated as well as calculated, and a record of past forecast errors gives the finance team an objective reference when managers build in slack or push for optimism.

Check your understanding

What does each result tell you?

1. An R Square near zero
2. A slope whose p-value is well below five percent
3. One residual far from all the others
4. The method with the smallest backtest error

Ask students what they would do next in each case, not only what it means.

Each result calls for a next step

1. The line explains little of the variation
2. Evidence against a zero slope under the model’s assumptions; check whether one point drives it
3. An unusual residual: investigate it, and see whether dropping it changes the fit
4. A candidate method for this holdout; report an illustrative error range with its limits

The second and third belong together: Charles River’s trend was significant only with the start-up month in the data.

Guided Tutorial 7.1: Forecasting Revenue and Testing the Forecast

Forecast and test in nine steps

1. Build the monthly revenue series
2. Chart the series
3. Measure what moves with revenue
4. Fit a trend to all thirty-six months
5. Fit the trend again without the start-up month
6. Backtest three forecasts of the fourth quarter of 2026
7. Measure the forecast errors
8. Forecast the first quarter of 2027 with a range
9. Document the forecast

Students build the monthly revenue series and check it against the InvoiceLines total, measure what moves with revenue, fit the trend with and without the start-up month, and backtest three forecasts of the year’s last quarter before forecasting the next one.

The mean beat the trend in the backtest

Figure 7.3 · Backtesting Three Forecasts of the Fourth Quarter of 2026

The trend overshoots the quarter by several percent, while the mean of the earlier months is almost exactly right. The same quarter of the previous year is close on the total but has the largest monthly errors.

Checkpoint

Check that:

• C38 is zero, so the monthly series contains all of the revenue in InvoiceLines;
• without January 2024, revenue correlates more closely with the average line than with the number of customers;
• the trend’s slope is significant with January 2024 and not significant without it;
• the mean method has the smallest errors in the backtest;
• the forecast for the first quarter of 2027 is stated with a low and a high value.

The controller now has a forecast she can defend. It is not the most sophisticated method you could build, but it performed best on the most recent quarter with an answer, and its range shows how far the forecast would move with an error as large as the backtest’s. The range is not a confidence interval.

7.5 Budgets, Flexible Budgets, and Variances

A budget is built from drivers

• BudgetLine holds the detail; its DriverType records how each amount was set
• Revenue is forecast units times a planned net price; COGS the same units at standard
• Commissions follow revenue; payroll taxes and benefits follow salaries
• Each driver can be tested on its own

The master budget combines the operating budgets with the financial budgets for cash and the balance sheet. For Charles River, the operating-expense budget turns out close to what was spent, and of the two sales assumptions, price is close to reality and volume is not.

Watch out: a summary may not hold all its detail

• The Budget Table summarizes the budget by cost center
• Lines with no cost center, such as rent, utilities and insurance, are missing
• A total taken from the summary is smaller than the budget’s true total
• Reconcile a summary to its detail before you rely on it

It is the same control as reconciling invoice lines to invoice totals in Chapter 4. Exercise 7.2 asks students to reconcile the Budget Table to BudgetLine.

A flexible budget separates volume from price

Figure 7.4 · Static Budget, Flexible Budget, and Actual Results

The flexible budget keeps the budget’s prices and costs per unit but uses the actual volume, so it shows what would have happened if everything except volume had gone to plan (Datar and Rajan). Variable costs flex with volume; fixed costs stay at their budgeted amounts.

Two variances connect the three levels

Table 7.2 · The two variances between the budget levels

Variance	Calculation	What it measures	Whom to ask about it first
Sales-volume variance	Flexible-budget margin minus static-budget margin	The effect of selling more or fewer units than planned, and a different mix of them, at planned margins	Sales and marketing, and whoever prepared the volume forecast
Flexible-budget variance	Actual margin minus flexible-budget margin	The effect of actual prices and costs differing from plan on the units actually sold	Pricing, purchasing, and production

Both Charles River’s budget and its results value units at standard cost, so for its product lines the flexible-budget variance is entirely a price variance. A static budget whose volume is far from reality says more about the forecast than about performance.

Flex the costs that vary

• A commission tied to revenue is variable: flex it to the rate times actual revenue
• Salaries and rent are fixed over the range and stay at their budget
• A commission far below the static budget is mostly smaller sales, not savings
• Test a driver directly, such as actual burden over actual salaries

Comparing each expense with the right benchmark is the point. Tutorial 7.2 finds that the two largest expense variances come from a budgeted burden rate above the actual rate and a commission budget that was never flexed.

Check your understanding

What does each finding mean?

1. The budget planned several times the units sold
2. On the units sold, prices fell short of plan
3. Commission expense is far below the static budget
4. Actual payroll burden is a lower rate than budgeted

Ask students whom they would ask about each finding first, using the last column of the variance table.

Each finding has its meaning

1. A sales-volume variance that reflects the forecast more than the business
2. A flexible-budget variance, here entirely a price variance
3. Mostly volume: compare it with the flexed commission instead
4. A rate difference to explain before calling it a saving

Only the second is something pricing managers can act on; the others are about how the budget was built.

Guided Tutorial 7.2: Comparing 2026 with the Budget

Steps 1 to 6: flex the sales budget

1. Import the budget lines
2. Extract the planned prices
3. Price the 2026 invoice lines at the planned prices
4. Build the flexible-budget report
5. Calculate the two variances
6. Split the price variance

Students import the budget lines, extract the planned price of each item and month, price the year’s invoice lines at those prices, and build the flexible-budget report with its two variances and a split of the price variance.

Steps 7 to 11: prices and expenses

7. Chart the Furniture prices by month
8. Import the operating expenses from the ledger
9. Compare the operating expenses with their budget
10. Test the drivers of the two exceptions
11. Document the analysis

They then chart Furniture prices by month, import the operating expenses from the ledger, compare them with their budget, and test the drivers of the two exceptions.

Flexed, the plan’s prices were slightly optimistic

Figure 7.5 · The Flexible-Budget Report by Product Line

The sales-volume variances are very large and unfavorable, because the plan’s volume was far from reality. The flexible-budget variances are small and unfavorable in every line, a similar small price gap before discounts plus the promotional discounts.

The promotion cost more, and later, than planned

Figure 7.6 · Planned and Actual Furniture Prices by Month

The budget planned the promotion’s lower prices in September and October. The actual price after promotions fell sharply in those months and recovered only partly through December, because the promotion’s orders kept being invoiced after it ended.

Checkpoint

Check that:

• only a handful of FlexLines rows have no planned price, you can explain why, and the row control in B22 is zero;
• on the BudgetReport worksheet, the margin bridge difference in row 21 is zero for every product line, so the static margin plus the two variances equals the actual margin;
• the price gap before discounts is a similar small percentage of flexible revenue in every product line;
• the Furniture price after promotions falls in September and October and stays below the price before promotions through December;
• the burden rate and the flexed commission explain the two largest operating-expense variances.

The static budget shows the gap from the plan, but its volume was so far from reality that it is a poor measure of the year’s performance. Flexed, the plan’s prices were slightly optimistic in every line, and the operating expenses were close once the burden rate and the flexed commission are corrected.

7.7 Cost-Volume-Profit Analysis and What-If Tools

Contribution margin covers fixed costs, then profit

• Contribution margin is revenue less variable costs
• Profit is the contribution margin less fixed costs
• Break-even revenue is fixed costs divided by the contribution margin ratio
• The margin of safety is how far revenue sits above break-even

A target profit works the same way: revenue must cover the fixed costs plus the target, divided by the contribution margin ratio. Exercise 7.3 applies these relationships to Charles River, with the operating-expense budget as the fixed costs.

A price cut must be repaid by volume

• A discount lowers the contribution margin of every unit that receives it
• Volume must rise enough to replace the margin lost
• The lift needed depends only on the margins before and after the cut
• Fixed costs do not enter, because the decision does not change them

This is the logic Tutorial 7.3 applies to the Furniture Seasonal Promotion. The break-even lift is the volume increase at which the promotion’s contribution equals what the same orders would have earned at full price.

Four tools ask what-if questions

Table 7.3 · Excel’s what-if tools

Tool	Where to find it	Question it answers
Scenario Manager	Data > What-If Analysis > Scenario Manager	What are the results under each of several named sets of inputs?
Data table	Data > What-If Analysis > Data Table	How does a result change across a range of values of one or two inputs?
Goal Seek	Data > What-If Analysis > Goal Seek	What value of one input produces a target result?
Solver	Data > Solver, after loading the Solver add-in	What values of several inputs give the best result within a set of constraints?

Scenario Manager compares named sets of inputs, a data table recalculates across a range of one or two inputs, and Goal Seek works backward to a target. Solver handles many inputs and constraints; for a linear model its Simplex LP method finds the optimum, and its Sensitivity report gives each constraint’s shadow price, but only without integer constraints, as in Exercise 7.4.

Document every assumption and its sensitivity

• State each key assumption, the value used, and its source
• Add how sensitive the conclusion is to it
• An assumption that can move far without changing the answer needs little evidence
• One that reverses the answer with a small change deserves the most scrutiny

An assumption that is not written down cannot be reviewed. Data tables and Goal Seek supply the evidence for the sensitivity column, as Tutorial 7.3 shows.

Check your understanding

Which what-if tool answers each question?

1. Results under three named sets of inputs
2. The margin across a range of discount rates
3. The volume lift at which a promotion breaks even
4. The product mix that maximizes margin within capacity

Ask students what they would need to set up in the model before each tool could work: inputs kept in cells apart from the formulas.

Each question has its tool

1. Scenario Manager
2. A data table
3. Goal Seek
4. Solver

All four need a model whose inputs sit in cells, separate from its calculations, which is how Tutorial 7.3 builds the Model worksheet.

Guided Tutorial 7.3: Should Charles River Repeat the Furniture Promotion?

Model the promotion in eight steps

1. Set up the Model worksheet
2. Calculate the inputs from the data
3. Build the model
4. Compare scenarios with Scenario Manager
5. Map the result with a data table
6. Find the break-even lift with Goal Seek
7. Check the lift against the order data
8. Document the assumptions and the recommendation

Students calculate the model’s inputs from the data, compare scenarios, map the result across discounts and lifts with a data table, find the break-even lift with Goal Seek, and check that lift against the order data.

The model keeps inputs apart from results

Figure 7.7 · The Promotion Model on the Model Worksheet

The discount and the lift are the inputs in blue; the units, prices, costs and commission rate are calculated from the data. The difference between the contribution with and without the promotion is negative at the year’s discount and no lift.

Every discount scenario loses margin

Figure 7.8 · The Scenario Summary Report

The difference is zero when the promotion ends and negative in every other scenario, including a repeat with a lift and a deeper discount. Scenario Manager builds this summary from named scenarios in one step.

The volume lift is the assumption that matters

Table 7.5 · Assumptions of the promotion model

Assumption	Value used	Sensitivity
Discount rate	10%	A 5% discount would need a much smaller lift to break even
Volume lift	0%	The break-even lift is almost a third of the promotion’s volume
Orders without the promotion	Same orders at the price before discount	If some orders would have been lost without it, the promotion’s cost is lower
Variable cost per unit	Standard cost per unit	Treating the fixed overhead in standard cost as fixed lowers the break-even lift slightly
Commission rate	2026 actual rate	Small; commissions fall with the discount

The book’s table also records the basis of each assumption, such as the promotion’s discount in the PromotionProgram Table and the commission rate over all revenue. The break-even lift is almost a third, and the invoiced orders show no increase near that, so the lift decides the recommendation. The orders describe what happened; they cannot show what the promotion caused, which is why zero lift stays an assumption.

Checkpoint

Check that:

• the Model worksheet’s inputs are calculated from the data and its results use the named cells;
• the Scenario Summary shows a negative difference for every scenario with a discount;
• the data table and Goal Seek agree on the break-even lift at a 10 percent discount;
• the Furniture units ordered in the promotion’s months show no increase near the break-even lift.

If the same orders would otherwise have been placed at the usual prices, the promotion, repeated as it ran, would cost margin unless it lifted volume by roughly a third, and the orders show no increase near that. A smaller discount, or one limited to the items and customers most likely to respond, has a far lower bar to clear, and the model shows how low.

7.9 Communicating Forecasts and Models

Give the range, the meaning and the key assumption

• Give a forecast as a range, with its method and the period it was fitted to
• Say which variances are meaningful: the volume variance reflects the forecast
• State the assumption that matters most, and how far it would have to move
• Record when each assumption was last reviewed, and revisit it

A forecast presented as a single number invites more confidence than it deserves. The same questions recur in later Parts: a SQL query can produce the monthly series, and an interactive report can let a manager drill from a product line to its months, but a model deserves trust only after it has been tested.

7.10 Looking Ahead

Back to the controller’s three requests

• Forecast: the mean beat the trend, so next quarter is stated as a range
• Budget: the volume assumption failed; flexed, prices were slightly optimistic
• Promotion: as it ran, it needed a third more volume; orders showed no such rise
• Next, Chapter 8 looks at the Charles River data through the auditor’s eyes

Along the way, students have seen several conditions an auditor would want to examine, from invoices whose dates and numbers disagree to a planning forecast that has run well above actual demand. Chapter 8 takes those up.

Summary

• Correlation and regression measure relationships; plots show what they hide
• A forecast earns trust by beating simple benchmarks in a backtest
• A flexible budget separates the volume assumption from prices and costs
• Contribution margin and break-even frame a decision; what-if tools explore it
• Every model documents its assumptions and how sensitive the answer is to each

Ask students which test in the chapter changed a conclusion: the start-up month and the backtest are the usual answers.

Key terms

Assumption documentation · Backtest · Benchmark forecast · Break-even point · Constraint · Contribution margin · Correlation coefficient · Cost-volume-profit analysis · Data table · Flexible budget · Flexible-budget variance · Forecast error · Goal Seek · Margin of safety · Master budget · P-value · R Square · Residual · Sales-volume variance · Scenario · Shadow price · Simple linear regression · Solver · Spill range · Static budget

Every term is defined at the end of the chapter. Backtest, flexible budget and break-even point are the ideas the later chapters rely on most.

Practice and next steps

• Financial Accounting
  • Exercise 7.1: Forecasting Revenue by Product Line
  • Exercise 7.2: Reconciling the Budget to Its Detail
• Managerial Accounting
  • Exercise 7.3: Company Break-Even and Margin of Safety
  • Exercise 7.4: The Product Mix under a Capacity Constraint
• Auditing
  • Exercise 7.5: Regression-Based Expectations for Revenue and Commissions
  • Exercise 7.6: Evaluating Management’s Forecast

Next: Chapter 8: Audit Analytics in Excel

Assign one exercise from each perspective; Exercise 7.6 compares management’s own demand forecast with past orders, an auditor’s test of consistency with experience. Chapter 8 turns to audit analytics.

About these slides

These slides accompany Accounting Analytics: An Integrated Approach, licensed under CC BY-SA 4.0. Every figure and table comes from the chapter:

https://aa.accountinganalyticshub.com/chapters/07-analysis-and-modeling/chapter.html

Students can find each figure and table, with the full explanation, in the online chapter.