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

Learning Objectives

After completing this chapter, you will be able to:

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.

Chapter slides View slides Download PowerPoint

Opening Scenario

The controller has read your analysis of the Furniture margin, and she is now preparing for the planning meeting for fiscal 2027. She has three requests. First, she needs a revenue forecast for the coming quarter that she can defend when the sales team pushes for a more optimistic number. Second, she wants to know how 2026 compared with the budget, because the budget will be the starting point for next year’s plan and she suspects that some of its assumptions were wrong. Third, the sales team has proposed repeating the Furniture Seasonal Promotion, and she wants a recommendation based on what the promotion actually cost and what it would need to achieve to pay for itself. Together, the requests use what happened in 2026 to forecast and plan 2027. This 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

Chapter 1 introduced four types of analytics. The earlier chapters of Part II used the first two: descriptive analytics to summarize what happened, and diagnostic analytics to explain why the Furniture margin fell. This chapter turns to the other two. Predictive analytics estimates what is likely to happen, such as next quarter’s revenue. Prescriptive analytics recommends what to do, such as whether to repeat a promotion or which products to make when capacity is short.

The move from description to prediction changes the nature of the work. A PivotTable of last quarter’s revenue is a statement of fact that anyone can check against the records. A forecast of next quarter’s revenue is an estimate that depends on assumptions about how the future will resemble the past, and a model of a promotion depends on assumptions about how customers will respond. Nobody can check a forecast against the records until the period is over. That is why this chapter pairs every modeling tool with a test: a residual plot that shows where a regression fails, a backtest that compares a forecast method with what actually happened, a flexible budget that separates a plan’s volume assumption from its prices, and a documented list of the assumptions behind every model.

7.2 Correlation and Regression

7.2.1 Correlation

A correlation coefficient measures how closely two numeric variables move together along a straight line. It ranges from −1 to +1. A value near +1 means that the variables rise together, a value near −1 means that one falls as the other rises, and a value near 0 means that there is little linear relationship. In Excel, =CORREL(array1, array2) returns the coefficient for two columns of equal length. The Data Analysis ToolPak, an add-in that comes with Excel, calculates a correlation matrix, the coefficients of every pair among several variables, at once: choose Data > Data Analysis > Correlation. To make the ToolPak available, choose File > Options > Add-ins, select Excel Add-ins in the Manage box, click Go, and check Analysis ToolPak; on a Mac, choose Tools > Excel Add-ins and check Analysis ToolPak (Microsoft, 2026d).

A correlation coefficient is easy to calculate and easy to misread. It measures only linear association, so two variables with a strong curved relationship can show a coefficient near zero. It says nothing about cause: two variables can move together because a third variable drives both. And it is sensitive to unusual observations. Charles River’s monthly revenue illustrates the last point. The first month of the data, January 2024, is a start-up month in which the company issued about half as many invoices as in a normal month. With that month included, monthly revenue appears strongly correlated with the number of customers invoiced. Without it, the correlation is much weaker, and revenue correlates more strongly with the average size of an invoice line instead, as Tutorial 7.1 shows. One month out of thirty-six changes the conclusion, which is a reason to look at the data before reading any coefficient.

7.2.2 Simple Linear Regression

Simple linear regression fits the straight line y = a + bx that minimizes the sum of the squared vertical distances between the data points and the line, which is why it is called a least-squares line. The dependent variable, y, is the one you want to explain or predict, and the independent variable, x, is the one you use to explain it. The intercept, a, is the value of the line when x is zero, and the slope, b, is the change in y for each one-unit increase in x. In accounting, regression is used to separate the fixed and variable parts of a mixed cost, to model how an expense moves with activity, and to develop the expectations that auditors compare with recorded amounts. Datar and Rajan (2021) treat it as one of the quantitative methods for estimating how costs behave.

The ToolPak’s Regression tool, under Data > Data Analysis > Regression, reports three groups of results, which Figure 7.2 shows for Charles River’s monthly revenue. The regression statistics measure the fit. R Square is the share of the variation in y that the line explains, from 0 to 1, and the standard error measures the typical distance of a point from the line, in the units of y. The ANOVA table, short for analysis of variance, tests whether the line explains more than chance would, and its Significance F is the probability of a fit at least this good if the true slope were zero and the regression’s assumptions held. The coefficients table gives the intercept and the slope, each with four further measures:

• a standard error, which measures how precisely the coefficient is estimated;
• a t statistic, the coefficient divided by its standard error, which is far from zero when the coefficient is large relative to its imprecision;
• a p-value, the probability of a t statistic at least as far from zero as this one if the true coefficient were zero and the assumptions held;
• a 95 percent confidence interval, the range of values for the true coefficient that are consistent with the data.

A p-value below 0.05 is the conventional threshold for calling a relationship statistically significant. With a single x variable, the slope’s p-value and Significance F are the same number. Neither is the probability that the true slope is zero, and neither measures how large an effect is or how accurate a forecast will be.

Figure 7.1: Monthly Revenue with Two Fitted Trends.

Figure 7.2: Regression Output from the Data Analysis ToolPak.

Two further checks belong with every regression. The first is the residuals, the differences between the actual values and the line. A residual plot, which the Regression tool draws when you check Residual Plots, should show no systematic pattern and a roughly even spread around zero. The usual p-values assume, among other things, a linear relationship and errors that are independent, of similar size, and roughly bell-shaped, so for a monthly series also look at the residuals in time order. A patternless plot is a useful check, not proof that every assumption holds. A single point far from the rest, or a curve in the residuals, means that the line describes the data poorly. The second check is the influence of individual points. As Figure 7.1 shows, the line fitted to all thirty-six months of Charles River’s revenue slopes upward, and its slope is statistically significant, but the start-up month accounts for almost half of that slope. Fitted without January 2024, the line rises about half as steeply, and its slope is no longer significant.

WarningWatch out

A statistically significant slope is not proof of a trend. A single unusual observation at one end of the data can tilt a line enough to make its slope significant, and Anscombe (1973) showed that data sets with identical regression lines and fit statistics can look nothing alike when they are plotted. Plot the data and the residuals before you report a regression, investigate any point that stands apart, and report the result both with and without it, with the reason for any exclusion. The reverse holds too: a slope that is not significant does not prove that there is no trend, only that the data give no reliable evidence of one.

7.3 Forecasting and Backtesting

7.3.1 Trend Functions

Excel has worksheet functions that apply a regression line without the ToolPak. FORECAST.LINEAR(x, known_y's, known_x's) returns the value of the least-squares line at a new x, such as the next month’s index, and TREND(known_y's, known_x's, new_x's) returns the values for several new x’s at once, spilling them into adjacent cells (Microsoft, 2026b). On a chart, Add Trendline draws the same line and can display its equation and R Square. These tools make a linear forecast quick to produce, which is exactly why it needs testing: extending a line into the future assumes that the pattern of the past continues, and that the pattern is a line.

TREND belongs to a family of dynamic array functions, which return several values from one formula. The values spill into the cells below or beside the formula, and the range they fill is called a spill range. FILTER, which you used in Chapter 6, is another, and this chapter adds UNIQUE, which returns the distinct values of a range, and SORT, which returns them in order. Another formula can refer to a whole spill range by adding # to the address of its first cell, as in A5#, so the reference grows and shrinks with the list. If the cells a formula needs are not empty, it returns the error #SPILL! instead of overwriting them.

7.3.2 Benchmark Forecasts

A forecasting method is worth using only if it does better than the simplest alternatives. Hyndman and Athanasopoulos (2021) describe four such benchmarks and recommend comparing every new method with them, because a method that cannot beat them is not worth considering. Table 7.1 lists them with their Excel equivalents, followed by the linear trend, the method Tutorial 7.1 tests against them.

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

In the seasonal naive formulas, C34 is the last value of the series, so the next period is in row 35 and the same season a year earlier is twelve rows above it for monthly values (row 23) and four rows above it for quarterly values (row 31). In the drift formula, (C34-C3)/31 is the average change: the difference between the first and the last of the 32 values in C3:C34, divided by the 31 steps between them. Drift and the linear trend both extend a slope, but they draw it differently: drift’s line passes through the first and the last values only, while the trend’s line is fitted to all of them.

7.3.3 Backtesting

A backtest evaluates a forecasting method on data that already has an answer. You fit the method to an earlier part of the history, forecast a later part that you held back, and compare the forecasts with what actually happened. The forecast error is the difference between the forecast and the actual value, positive when the forecast is too high, and it is often expressed as a percentage of the actual, so that errors for periods or product lines of different size can be compared. The mean absolute percentage error (MAPE) averages those percentages, ignoring their sign, over the periods forecast. Charles River’s data ends at December 2026, so the most recent quarter with actual results, the fourth quarter of 2026, is the natural holdout. A backtest cannot guarantee that a method will work in the future, but a method that fails on the recent past should not be trusted with the next quarter.

TipIn Practice

Budgets are negotiated as well as calculated. Managers who are evaluated against a budget have a reason to build slack into it, by underestimating revenue or overestimating costs so that the target is easier to meet (Datar & Rajan, 2021), while others push for optimistic plans that justify more resources. A statistical benchmark and a record of past forecast errors give the finance team an objective reference point for that discussion. The question to ask of any forecast is how it would have performed if it had been used in the past.

7.4 Guided Tutorial 7.1: Forecasting Revenue and Testing the Forecast

Context and objective. In this tutorial, you will prepare the revenue forecast the controller asked for. You will build a monthly revenue series from the InvoiceLines Table, measure what moves with it using correlation, fit a trend with the Data Analysis ToolPak, and examine what drives the trend. You will then backtest three forecasting methods on the fourth quarter of 2026, the most recent quarter with actual results, and use the best one to forecast the first quarter of 2027 with a range.

Prerequisites. You need the analysis workbook from Chapter 6, with the InvoiceLines Table, and the Analysis ToolPak loaded, as described earlier in this chapter. If you start here, download Charles River Furniture Analysis.xlsx as it stands at the end of Chapter 6 from Companion Files, and point its queries at your copy of CharlesRiver.xlsx as that page explains.

Step-by-step instructions.

Step 1. Build the monthly revenue series. EDATE, used below, returns the date a given number of months after a start date, so each row of the series starts one month after the row above it.

1. Add a worksheet named Forecast, and type the headings Month, MonthIndex, and Revenue in A1:C1.

2. Type 2024-01-01 in A2.

3. In A3, enter =EDATE(A2,1), and fill it down to A37, which shows December 2026.

4. Format A2:A37 with the custom number format mmm yyyy.

5. Type 1 in B2, enter =B2+1 in B3, and fill it down to B37.

6. In C2, enter this formula, and fill it down to C37:

   =SUMIFS(InvoiceLines[LineTotal],InvoiceLines[InvoiceDate],">="&A2,InvoiceLines[InvoiceDate],"<"&EDATE(A2,1))

7. Type Control total in A38, and enter this formula in C38:

   =ROUND(SUM(C2:C37)-SUM(InvoiceLines[LineTotal]),2)

C38 is zero: every invoice line falls in one of the thirty-six months, so the series accounts for all of the revenue in InvoiceLines. The ROUND matters because Excel stores decimals in binary and a difference of zero can otherwise show as a residue far smaller than a cent.

Step 2. Chart the series. A line chart suits a measure over time, as the section on charting in Chapter 6 explained.

1. Select A1:A37, hold Ctrl, and select C1:C37.
2. Choose Insert > Insert Line or Area Chart > Line with Markers.

Almost every month falls within a narrow band, but the first month is far below the rest. January 2024 is the start-up month of the data, in which Charles River issued only about half as many invoices as in a normal month. Figure 7.1 shows the same series with the trend lines you will fit later.

Step 3. Measure what moves with revenue. Revenue rises when more customers buy or when each purchase is larger, so a correlation matrix shows which of the two moves with it.

1. Type the headings Customers and AverageLine in D1:E1.

2. In D2, enter this formula, which counts the different customers invoiced in the month, and fill it down to D37. FILTER keeps the month’s rows, multiplying the two conditions so that a row must meet both, and UNIQUE removes the repeated customer IDs before COUNTA counts them.

   =COUNTA(UNIQUE(FILTER(InvoiceLines[CustomerID],(InvoiceLines[InvoiceDate]>=A2)*(InvoiceLines[InvoiceDate]<EDATE(A2,1)))))

3. In E2, enter this formula, the average line total of the month, and fill it down to E37:

   =AVERAGEIFS(InvoiceLines[LineTotal],InvoiceLines[InvoiceDate],">="&A2,InvoiceLines[InvoiceDate],"<"&EDATE(A2,1))

4. Choose Data > Data Analysis, select Correlation, and click OK.

5. Enter $C$1:$E$37 as the Input Range, and check Labels in first row.

6. Choose Output Range, enter $G$1, and click OK.

7. Run the tool again with $C$3:$E$37 as the Input Range, Labels in first row cleared, and $G$7 as the Output Range. Without the headings, Excel labels the three variables Column 1, Column 2, and Column 3.

With all thirty-six months, in G1:J4, revenue is strongly correlated with the number of customers and only weakly with the average line. Without January 2024, in G7:J10, the order reverses: the correlation with customers is much weaker, and the average line becomes the stronger partner. One start-up month with few customers made the number of customers look like the driver of revenue.

Step 4. Fit a trend to all thirty-six months.

1. Choose Data > Data Analysis, select Regression, and click OK.
2. Enter $C$1:$C$37 as the Input Y Range and $B$1:$B$37 as the Input X Range, and check Labels.
3. Choose New Worksheet Ply, and type Trend36.
4. Check Residuals and Residual Plots, and click OK.
5. On the Trend36 worksheet, read R Square, the MonthIndex coefficient, and its P-value.

R Square is low: the month index explains only about a sixth of the variation in monthly revenue. Yet the slope is positive and its p-value is below 0.05, so the trend is statistically significant. The residual plot shows why. One residual, January 2024’s, lies far below all the others, and the least-squares line, pulled toward it, tilts upward. Figure 7.2 shows the output.

Step 5. Fit the trend again without the start-up month.

1. Run the Regression tool again, with $C$3:$C$37 as the Input Y Range and $B$3:$B$37 as the Input X Range.
2. Clear the Labels check box, because the ranges no longer start with the headings.
3. Place the output on a new worksheet ply named Trend35.
4. Compare R Square, the slope, and its p-value with those on Trend36.

Without January 2024, R Square falls further, the slope shrinks to about half its former size, and its p-value rises above 0.05, so the slope is no longer statistically significant. The start-up month made the trend look significant. That does not prove that revenue has no trend: the months since February 2024 give no reliable evidence of one, and they are consistent with revenue fluctuating around a stable level. The backtest in the next steps asks which of the two descriptions forecasts better.

Step 6. Backtest three forecasts of the fourth quarter of 2026. Each method is fitted to February 2024 through September 2026, rows 3 to 34, as if the forecast had been prepared at the end of September.

1. Type the headings Method, Oct 2026, Nov 2026, Dec 2026, Quarter, Quarter error, and Monthly MAPE in A40:G40.

2. Type the labels Actual, Trend, Mean, and Same quarter last year in A41:A44.

3. In B41:D41, enter =C35, =C36, and =C37, the actual revenue of the three months.

4. In B42, enter the trend forecast for October, which is MonthIndex 34:

   =FORECAST.LINEAR($B35,$C$3:$C$34,$B$3:$B$34)

   In C42 and D42, enter the same formula with $B36 and $B37.

5. In B43:D43, enter =AVERAGE($C$3:$C$34), the mean of the fitted months.

6. In B44:D44, enter =C23, =C24, and =C25, the same months of 2025.

7. Type TREND check in I40, and enter this formula in I41, keeping I41:I43 clear so that its three values can spill:

   =TREND($C$3:$C$34,$B$3:$B$34,$B$35:$B$37)

Rows 42 to 44 now hold each method’s forecast of the three months, made only from the months before October 2026, next to the actual revenue in row 41. The three values that TREND spills into I41:I43 equal the trend forecasts in B42:D42, because the two functions fit the same line, and TREND returns all three months from one formula.

Step 7. Measure the forecast errors.

1. In E41, enter =SUM(B41:D41), and copy it to E42:E44.
2. In F42, enter =E42/$E$41-1, the error of the quarter’s total, which is positive when the forecast is too high, and copy it to F43:F44.
3. In G42, enter =AVERAGE(ABS(B42:D42-$B$41:$D$41)/$B$41:$D$41), the mean absolute percentage error of the three months, and copy it to G43:G44.
4. Format F42:G44 as percentages with two decimals.

The trend forecast overshoots the quarter by several percent, because it extends the upward slope of the fitted months, which the fourth quarter did not follow. The mean misses the quarter by a small fraction of a percent and has the smallest monthly errors. The same quarter of last year comes close on the quarter’s total, but only because its large monthly errors offset one another. Figure 7.3 shows the comparison. The simplest method is the best of the three on this quarter, which is the reason to test benchmarks before trusting a more elaborate forecast.

Figure 7.3: Backtesting Three Forecasts of the Fourth Quarter of 2026.

Step 8. Forecast the first quarter of 2027 with a range.

1. Type Forecast, 2027 Q1 in A47, and enter =3*AVERAGE(C3:C37) in B47, which applies the mean method to every month after the start-up month.
2. Type Low in A48 and High in A49.
3. Enter =B47*(1-G43) in B48 and =B47*(1+G43) in B49, a range based on the mean method’s monthly error in the backtest.

The forecast is close to the revenue of the last few quarters, and the range extends a few percent on either side of it. The range shows what a miss as large as the backtest’s would mean for the quarter. It is not a confidence interval: three months of errors cannot say how likely a larger miss is. Three months are also a thin basis for choosing a method, so a forecast that will be relied on outside the company should be tested again from another forecast origin first.

Step 9. Document the forecast.

1. On the Documentation worksheet, add the Forecast, Trend36, and Trend35 worksheets to the Worksheets entry.
2. Add a Forecast entry that records the method, the months it uses, the reason January 2024 is excluded, the backtest results, and the range.
3. Save the workbook.

A reviewer can now see how the forecast was made and why the simplest of the methods was chosen.

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 is the one that performed best on the most recent quarter with an answer, and its range shows her how far the forecast would move with an error as large as the backtest’s.

7.5 Budgets, Flexible Budgets, and Variances

7.5.1 The Master Budget and Its Drivers

A budget is a plan expressed in money. A master budget combines the operating budgets, for sales, production, purchasing, and expenses, with the financial budgets for cash and the balance sheet. Charles River’s budget is recorded in two Tables. BudgetLine holds the detail, one row for each account, month, and where applicable item and cost center, and its DriverType column records how each amount was calculated. Revenue is forecast units multiplied by a planned net price, cost of goods sold is the same units at standard cost, sales commissions are forecast revenue multiplied by a commission rate, payroll taxes and benefits are salaries multiplied by a burden rate, and recurring expenses such as rent follow their own drivers. The Budget Table summarizes the lines by cost center and account.

Knowing the drivers tells you which parts of a budget can be tested separately. Charles River’s operating-expense budget, for salaries, rent, depreciation, and the other recurring accounts, turns out to be close to what the company spent. The sales budget is built from two assumptions that can be checked one at a time: how many units would be sold, and at what price. For Charles River, the second assumption is close to reality and the first is not.

WarningWatch out

A summary does not always contain all of its detail. Charles River’s Budget Table summarizes the budget by cost center, and the operating-expense lines that have no cost center, such as rent, utilities, and insurance, do not appear in it. A total taken from the summary is therefore smaller than the budget’s true total. Before you rely on a summary table, reconcile it to its detail, as you did with invoice totals in Chapter 4. Exercise 7.2 asks you to do so.

7.5.2 Static and Flexible Budgets

A static budget is the budget prepared for a single, planned level of activity. Comparing actual results with it mixes two different questions: whether the company sold the volume it planned, and whether it earned the planned prices and incurred the planned costs on the volume it did sell. A flexible budget separates the two. It keeps the budget’s prices and costs per unit but recalculates the budget at the actual volume, so that it shows what the results would have been if everything except volume had gone according to plan (Datar & Rajan, 2021). Variable costs change with volume in a flexible budget, while fixed costs stay at their budgeted amounts. Figure 7.4 shows the three levels of comparison, and Table 7.2 defines the two variances that connect them.

Figure 7.4: Static Budget, Flexible Budget, and Actual Results.

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

In general, the flexible-budget variance combines a price effect and a cost effect. Charles River’s budget and its actual results both value the units sold at standard cost, so for its product lines the flexible-budget variance is entirely a price variance: actual revenue minus what the same units would have earned at the planned prices.

A static budget whose volume assumption is far from what happened says more about the forecast than about performance. Charles River’s sales budget planned for several times the units the company sold. Its quantities come from a sales forecast, as its DriverType records, and the company’s demand-planning forecast has also run well above actual orders in every year of the data, as Exercise 7.6 shows. Compared with that budget, every product line shows an enormous unfavorable variance that no pricing or cost decision could explain. Flexing the budget to the actual quantities removes the volume assumption and leaves the questions that managers can act on.

7.5.3 Variable and Fixed Costs in the Budget

Flexing also applies to expenses. A sales commission that is a percentage of revenue is a variable cost, so its flexible budget is the budgeted rate multiplied by actual revenue, not the static budget amount. A cost driven by headcount or by contract, such as salaries or rent, is fixed over the range of activity in question and stays at its budgeted amount. Comparing each expense with the right benchmark matters: a commission expense well below the static budget looks like savings, but most of the difference only reflects the smaller volume of sales. The driver behind a budgeted amount can also be tested directly. Charles River budgeted payroll taxes and benefits at a fixed percentage of salaries, and dividing the actual expense by actual salaries shows whether that rate held.

7.6 Guided Tutorial 7.2: Comparing 2026 with the Budget

Context and objective. In this tutorial, you will explain how fiscal 2026 compared with the budget. You will import the budget lines, price every 2026 invoice line at the budget’s planned net price for its item and month, and build a report that compares the static budget, the flexible budget, and the actual results for each product line. You will then split the price variance into promotional discounts and the rest, chart when the Furniture discounts arrived compared with when the budget planned them, and compare the operating expenses with their budget.

Prerequisites. You need the analysis workbook from Tutorial 7.1, with the InvoiceLines query and its StdCostAmount, ListAmount, and DiscountAmount columns from Chapter 6, and the Item query from Chapter 4. If you start here, download Charles River Furniture Analysis.xlsx as it stands at the end of Tutorial 7.1 from Companion Files, and point its queries at your copy of CharlesRiver.xlsx as that page explains.

Step-by-step instructions.

Step 1. Import the budget lines.

1. Choose Data > Get Data > From File > From Excel Workbook, and select CharlesRiver.xlsx.
2. In the Navigator, select the T77_BudgetLine and T1_Account Tables, and click Transform Data.
3. Rename the queries BudgetLine and Account.
4. In the Account query, use Home > Choose Columns to keep AccountID, AccountNumber, and AccountName.
5. In the BudgetLine query, filter FiscalYear to 2026, and clear Balance Sheet in the filter list of BudgetCategory, so that the query keeps the revenue, cost of goods sold, and operating-expense lines.
6. Merge BudgetLine with Item on ItemID, with a left outer join, and expand ItemGroup without the prefix.
7. Merge BudgetLine with Account on AccountID, with a left outer join, and expand AccountNumber and AccountName without the prefix.
8. Choose Home > Close & Load > Close & Load To, select Table and New worksheet, and click OK. The choice applies to both new queries, as in Tutorial 4.1, Step 7, so Power Query loads BudgetLine and Account into two new worksheets as Tables. Rename the worksheets BudgetLine and Account.

The BudgetLine Table now holds the 2026 budget, one row for each account and month, and for revenue and cost of goods sold, for each item, with the item group and the account number beside each row. The operating-expense rows have no item, so their ItemGroup is blank.

Step 2. Extract the planned prices. The budget has exactly one planned net price for each item in each month, and a query that references BudgetLine can extract them.

1. Choose Data > Get Data > Launch Power Query Editor to reopen the editor.
2. In the Queries pane, right-click BudgetLine, choose Reference, and name the new query PlannedPrices.
3. Filter BudgetCategory to Revenue, and use Home > Choose Columns to keep FiscalYear, Month, ItemID, and UnitAmount.
4. Rename UnitAmount to PlannedPrice.
5. Choose Home > Close & Load > Close & Load To, select Only Create Connection, and click OK, so that the query is used only inside other queries.

PlannedPrices lists one price for each item and month of 2026. Because it is only a connection, it adds no worksheet to the workbook, and the Queries & Connections pane shows it as Connection only.

Step 3. Price the 2026 invoice lines at the planned prices. Merging on the year, the month, and the item adds one planned price to each invoice line without adding rows.

1. Choose Data > Get Data > Launch Power Query Editor to reopen the editor.

2. Right-click InvoiceLines in the Queries pane, choose Reference, and name the new query FlexLines.

3. Filter FiscalYear to 2026, and filter ItemGroup to exclude Services, which the budget does not plan.

4. Choose Add Column > Custom Column, and add a column Month with the formula Date.Month([InvoiceDate]).

5. Set the type of Month to Whole Number, the type of Month in PlannedPrices, so that the two columns can be matched.

6. Choose Home > Merge Queries. In FlexLines, hold Ctrl and click FiscalYear, Month, and ItemID, in that order.

7. Choose PlannedPrices in the lower list, click its FiscalYear, Month, and ItemID in the same order, keep Left Outer, and click OK.

8. Expand PlannedPrice, without the prefix.

9. Add a custom column FlexRevenue with the formula below. Power Query’s if, then, and else work like Excel’s IF: it returns the value after then when the condition is true and the value after else otherwise.

   if [PlannedPrice] = null then [LineTotal] else [Quantity] * [PlannedPrice]

10. Set the type of PlannedPrice and FlexRevenue to Decimal Number.

11. Choose Home > Close & Load, and rename the new worksheet FlexLines.

FlexRevenue is what each line would have been billed at the budget’s price. A few lines have no planned price, because their seasonal item was launched in the month it was first sold, and the budget did not plan sales that month. For those lines, FlexRevenue keeps the actual amount, so they create no price variance.

Step 4. Build the flexible-budget report.

1. Add a worksheet named BudgetReport, and type Furniture, Lighting, Textiles, Accessories, and Total in B3:F3.

2. In A4:A18, type the row labels Static budget revenue, Static budget COGS, Static budget margin, Flexible budget revenue, Flexible budget COGS, Flexible budget margin, Actual revenue, Actual COGS at standard, Actual margin, Sales-volume variance, Flexible-budget variance, Promotional discounts, Price gap before discounts, Budgeted units, and Actual units.

3. In B4, enter the static budget revenue with this formula, and enter the same formula in B5 with "COGS" in place of "Revenue":

   =SUMIFS(BudgetLine[BudgetAmount],BudgetLine[ItemGroup],B$3,BudgetLine[BudgetCategory],"Revenue")

4. In B7, enter the flexible budget revenue with the formula below, and in B8, the same formula with StdCostAmount in place of FlexRevenue. The budget’s cost per unit is the standard cost, so the flexible budget’s cost of goods sold is the standard cost of the units actually sold.

   =SUMIFS(FlexLines[FlexRevenue],FlexLines[ItemGroup],B$3)

5. In B10, enter the same formula with LineTotal in place of FlexRevenue, and in B11, enter =B8.

6. Enter the margins: =B4-B5 in B6, =B7-B8 in B9, and =B10-B11 in B12.

7. In B17 and B18, enter the budgeted and the actual units:

   =SUMIFS(BudgetLine[Quantity],BudgetLine[ItemGroup],B$3,BudgetLine[BudgetCategory],"Revenue")
   =SUMIFS(FlexLines[Quantity],FlexLines[ItemGroup],B$3)

8. Copy B4:B12 to C4:E12 and B17:B18 to C17:E18.

9. In F4, enter =SUM(B4:E4), and copy it to F5:F12 and F17:F18.

10. Type Lines without a planned price in A20, and enter =COUNTBLANK(FlexLines[PlannedPrice]) in B20.

11. Type Row control in A22, and enter this formula in B22:

    =ROWS(FlexLines[LineTotal])-COUNTIFS(InvoiceLines[FiscalYear],2026,InvoiceLines[ItemGroup],"<>Services")

Each product line now shows its revenue, cost, and margin three ways, with the units behind them in rows 17 and 18. B20 counts only a handful of lines, the seasonal launches described in Step 3. B22 is zero: FlexLines has exactly the product lines of 2026 that InvoiceLines has, so the merge on year, month, and item added no rows. A merge on a key that appeared twice would repeat invoice lines and overstate every total.

Step 5. Calculate the two variances.

1. In B13, enter =B9-B6, the sales-volume variance.
2. In B14, enter =B12-B9, the flexible-budget variance.
3. Copy B13:B14 to C13:E14, and copy F12 to F13:F14.
4. Type Margin bridge difference in A21, enter =ROUND(B6+B13+B14-B12,2) in B21, and copy it to C21:F21.

Every cell of B21:F21 is zero: in every column, the static margin in row 6 plus the two variances in rows 13 and 14 equals the actual margin in row 12. The check tests the arithmetic of the bridge, not whether the source data are complete.

The sales-volume variance dwarfs everything else in the report. Compare rows 17 and 18: the budget planned for several times the units that each product line sold. A gap that large says more about the forecast behind the budget than about how the business was run, so the static budget is a poor measure of 2026’s performance, and the reason to judge 2026 against the flexible budget instead. Against the flexible budget, every product line earned less than planned on the units it sold, by a few percent of revenue.

Step 6. Split the price variance.

1. In B15, enter the promotional discounts:

   =SUMIFS(FlexLines[DiscountAmount],FlexLines[ItemGroup],B$3)

2. In B16, enter =B14+B15, the price gap before discounts.

3. Copy B15:B16 to C15:E16, and copy F14 to F15:F16.

Two causes separate cleanly. In every product line, the price gap before discounts is a steady few percent of flexible revenue: the budget planned net prices at a larger share of list price than Charles River’s price lists actually delivered. Furniture adds the promotional discounts on top. Figure 7.5 shows the report.

Figure 7.5: The Flexible-Budget Report by Product Line.

Step 7. Chart the Furniture prices by month. Dividing each kind of revenue by the list value of the same units turns it into a price as a percentage of list, which can be compared from month to month.

1. Add a worksheet named FurniturePrices, and type the headings Month, FlexRevenue, LineTotal, DiscountAmount, ListAmount, Planned, Before promotions, and After promotions in A1:H1.

2. Type 1 in A2 and 2 in A3, select A2:A3, and drag the fill handle down to A13, so that A2:A13 hold the months 1 to 12.

3. In B2, enter this formula, and fill it down to B13:

   =SUMIFS(FlexLines[FlexRevenue],FlexLines[ItemGroup],"Furniture",FlexLines[Month],$A2)

4. In C2, D2, and E2, enter the same formula with LineTotal, DiscountAmount, and ListAmount in place of FlexRevenue, and fill them down to row 13.

5. In F2, G2, and H2, enter =B2/E2, =(C2+D2)/E2, and =C2/E2, and fill them down to row 13.

6. Format F2:H13 as percentages with one decimal.

7. Select F1:H13, and choose Insert > Insert Line or Area Chart > Line with Markers.

8. Right-click the chart, choose Select Data, click Edit under Horizontal (Category) Axis Labels, select A2:A13, and click OK twice.

The planned price lies above the actual price before promotions in every month except September and October, when the budget planned the promotion’s discount and spread it over all of those months’ sales. The actual discounts were concentrated on the promotion’s orders, and they continued into November and December as those orders were invoiced. Figure 7.6 shows the chart.

Figure 7.6: Planned and Actual Furniture Prices by Month.

Step 8. Import the operating expenses from the ledger.

1. Choose Data > Get Data > From File > From Excel Workbook, select CharlesRiver.xlsx, select the T3_GLEntry Table, and click Transform Data, not Load, because the Table has hundreds of thousands of rows.
2. Name the query GLOpex, and filter FiscalYear to 2026 as the first step.
3. Merge GLOpex with Account on AccountID, and expand AccountNumber and AccountName without the prefix.
4. Filter AccountNumber to values between 6000 and 7999.
5. Filter VoucherNumber to exclude JE-2026-000296, the 2026 year-end closing entry you identified in the Part I case.
6. Add a custom column Amount with the formula [Debit] - [Credit].
7. Choose Home > Close & Load, and rename the new worksheet GLOpex.

GLOpex holds the 2026 postings to the accounts numbered 6000 to 7999 without the closing entry, so the Amount of each account adds up to its total for the year. The range holds the operating expenses and, at its end, the other income and expense accounts, such as interest expense.

Step 9. Compare the operating expenses with their budget. The formulas below spill: each returns a list of values, which fills the cells below it. A reference such as A5# means the whole list that spills from A5, however long it is.

1. Add a worksheet named OpexReport, and type the headings Account, Name, Budget, Actual, and Variance in A4:E4.

2. In A5, list the budgeted accounts with this formula:

   =SORT(UNIQUE(FILTER(BudgetLine[AccountNumber],BudgetLine[BudgetCategory]="Operating Expense")))

3. In B5, C5, and D5, enter these three formulas: the account name, the budget, and the actual amount.

   =XLOOKUP(A5#,Account[AccountNumber],Account[AccountName])
   =SUMIFS(BudgetLine[BudgetAmount],BudgetLine[AccountNumber],A5#,BudgetLine[BudgetCategory],"Operating Expense")
   =SUMIFS(GLOpex[Amount],GLOpex[AccountNumber],A5#)

4. In E5, enter =D5#-C5#, the variance of each account.

Most accounts are within a few percent of their budget, and several match it exactly. The list ends with 7030, Interest Expense, which the budget files under Operating Expense, although the income statement of Chapter 6 places interest below operating income, with the other income and expense. Two accounts stand out, and both are under budget: Payroll Taxes and Benefits, and Sales Commission Expense.

Step 10. Test the drivers of the two exceptions. Each of the two budgets was calculated as a rate times a base, and testing the driver separates the effect of the base from the effect of the rate. Why the rate differed takes evidence from outside the workbook.

1. In G5:G9, type the labels Budgeted burden rate, Actual burden rate, Budgeted commission rate, Flexed commission budget, and Actual commissions.

2. In H5, enter the budgeted burden rate, the budget for account 6060 divided by the budgeted salaries. The asterisk in "Salaries*" is a wildcard that stands for any text, so the criterion matches every account whose name starts with Salaries.

   =SUMIFS(BudgetLine[BudgetAmount],BudgetLine[AccountNumber],6060)/SUMIFS(BudgetLine[BudgetAmount],BudgetLine[AccountName],"Salaries*")

3. In H6, enter the actual burden rate:

   =SUMIFS(GLOpex[Amount],GLOpex[AccountNumber],6060)/SUMIFS(GLOpex[Amount],GLOpex[AccountName],"Salaries*")

4. In H7, enter the budgeted commission rate, the commission budget divided by the static budget revenue of the product lines:

   =SUMIFS(BudgetLine[BudgetAmount],BudgetLine[AccountNumber],6290)/BudgetReport!F4

5. In H8, enter =H7*BudgetReport!F10, the commission budget flexed to the actual revenue of the product lines.

6. In H9, enter =SUMIFS(GLOpex[Amount],GLOpex[AccountNumber],6290).

The budget assumed that payroll taxes and benefits would cost a larger percentage of salaries than they did, so the variance on account 6060 comes almost entirely from the lower rate, while salaries were close to budget. Why the actual rate was lower is a question for the payroll team before anyone calls the difference a saving. The commission variance is mostly volume. Flexed to the actual revenue, the commission budget comes within about a tenth of what Charles River recognized as commission expense. The rest of the difference is a lower rate than the budget assumed, and the rate earned depends on the mix of customer segments, because each segment earns its own commission rate. The actual expense also includes the commissions on design services, which the budget did not plan, so on product sales alone the actual rate was lower still.

Step 11. Document the analysis.

1. On the Documentation worksheet, add the BudgetLine, Account, PlannedPrices, FlexLines, and GLOpex queries to the Query steps entry, with the number of FlexLines rows that have no planned price.
2. Add the BudgetLine, Account, FlexLines, GLOpex, BudgetReport, FurniturePrices, and OpexReport worksheets to the Worksheets entry.
3. Add a Finding entry of three or four sentences that states the sales-volume variance and what it reflects, the price gap and the promotions, and the two expense assumptions that did not hold.
4. Save the workbook.

The Documentation worksheet now lists every query and worksheet that the budget comparison added, and the finding that answers the controller’s question.

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 comparison with the budget answers the controller’s second question. The static budget shows how far 2026 fell from the original plan, but its volume was so far from reality that it is a poor standalone measure of performance, and the size of the gap is a reason to ask how the volume was planned. Flexed to what was sold, the plan’s prices were slightly optimistic in every product line, the Furniture promotion cost more and later than the budget expected, and the operating expenses were close to plan once two assumptions are corrected.

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

7.7.1 Contribution Margin and Break-Even

Cost-volume-profit analysis models how profit responds to volume when costs are divided into variable and fixed parts. The contribution margin is revenue minus variable costs, the amount left over to cover fixed costs and earn a profit, and the contribution margin ratio is the contribution margin divided by revenue. Profit is the contribution margin minus fixed costs. The break-even point is the volume or revenue at which profit is zero, so break-even revenue equals fixed costs divided by the contribution margin ratio, and the margin of safety is the amount by which actual or planned revenue exceeds break-even (Datar & Rajan, 2021).

Consider a generic example. A business with fixed costs of $600,000 a year and a contribution margin ratio of 40 percent breaks even at revenue of $1,500,000. If it expects revenue of $2,000,000, its margin of safety is $500,000, or a quarter of expected revenue, and it earns a profit of $200,000. A target profit works the same way: to earn $300,000, revenue must cover the fixed costs and the target, so it must reach ($600,000 + $300,000) / 0.40, or $2,250,000. Exercise 7.3 applies these relationships to Charles River, using the operating-expense budget as the fixed costs.

The same logic answers questions about a single decision. A price reduction lowers the contribution margin of every unit that receives it, so the volume must rise enough to replace the lost margin. The required increase depends only on the contribution margin before and after the change, not on fixed costs, which the decision does not affect. Tutorial 7.3 uses this to evaluate the Furniture Seasonal Promotion.

7.7.2 What-If Tools

A model that separates its inputs from its calculations can answer what-if questions, and Excel provides tools for asking them systematically (Microsoft, 2026c). Table 7.3 summarizes them.

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 stores named scenarios, each a set of values for the changing cells, and its Summary button builds a report that compares the result cells across all of them. A data table, a what-if tool that has nothing to do with the Excel Tables of Chapter 4, recalculates a formula for every value in a row or column of inputs, or for every combination of two inputs, and fills in the results. Goal Seek works backward from a target: it changes one input cell until a formula cell reaches the value you specify, which makes it the quickest way to find a break-even point.

Solver extends Goal Seek to many inputs and to constraints. You specify an objective cell to maximize or minimize, the variable cells Solver may change, and the constraints the solution must satisfy, such as the hours available in each work center. For a linear model, one whose objective and constraints are sums of variable cells multiplied by constants, Solver’s Simplex LP method finds the optimum (Microsoft, 2026a), and Solver can then produce a Sensitivity report that shows, among other things, how much the objective would improve with one more unit of each constrained resource, its shadow price, and how far the resource can increase or decrease before that value changes. That report is available only when the model has no integer constraints (Frontline Systems, n.d.), so a production plan is usually solved first without requiring whole units. Exercise 7.4 builds such a model from Charles River’s routings, the lists of operations, and of the hours at each work center, that each manufactured item requires.

7.7.3 Documenting Assumptions

A model is only as good as its assumptions, and an assumption that is not written down cannot be reviewed. Assumption documentation, a written record of the assumptions behind a model, should state each key assumption, the value used, its source, and how sensitive the conclusion is to it. The sensitivity is what makes the documentation useful: an assumption that can move far without changing the recommendation needs little further evidence, while one that reverses the recommendation with a small change deserves the most scrutiny. Data tables and Goal Seek provide the evidence for that column, as Tutorial 7.3 shows.

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

Context and objective. In this tutorial, you will answer the controller’s third question. You will build a contribution margin model of the orders that received the Furniture Seasonal Promotion in 2026, with the model’s inputs calculated from the data and stored in named cells. You will then compare scenarios with Scenario Manager, map the result across discounts and volume responses with a data table, find the volume increase the promotion needs to pay for itself with Goal Seek, check that requirement against the order data, and document the assumptions behind your recommendation.

Prerequisites. You need the analysis workbook from Tutorial 7.2, with the InvoiceLines and GLOpex Tables, and the SalesInvoiceLine Table with the OrderDate and ItemGroup columns you added in Tutorial 5.2. If you start here, download Charles River Furniture Analysis.xlsx as it stands at the end of Tutorial 7.2 from Companion Files, and point its queries at your copy of CharlesRiver.xlsx as that page explains.

Step-by-step instructions.

Step 1. Set up the Model worksheet.

1. Add a worksheet named Model, and type the headings Input or result, Value, Name, and Source in A1:D1.
2. In A2:A7, type the labels Discount rate, Volume lift, Units on promotion lines, Price before discount per unit, Variable cost per unit, and Commission rate.
3. In A9:A11, type Contribution without promotion, Contribution with promotion, and Difference.
4. Type 0.1 in B2 and 0 in B3, format both as percentages, and color them blue, because they are the inputs the analysis will change.
5. Using the Name Box, name B2 through B7 Discount, Lift, PromoUnits, PriceBeforeDiscount, VariableCost, and CommissionRate.
6. Name B9 through B11 ContributionWithout, ContributionWith, and Difference in the same way.
7. Type each name in column C beside its cell, as documentation.
8. In D2:D7, type the sources PromotionProgram, promotion 8, Assumption, not a measured lift, InvoiceLines, promotion 8, InvoiceLines, promotion 8, Standard cost per unit, and Tutorial 7.2, actual 2026, and type Formula in D9:D11.

Every input and result of the model now has a label, a name, and a source, and the two inputs you will change stand out in blue.

Step 2. Calculate the inputs from the data. The promotion’s own lines tell you how many units it discounted, at what price, and at what cost.

1. In B4, enter the units on the promotion’s lines:

   =SUMIFS(InvoiceLines[Quantity],InvoiceLines[PromotionID],8)

2. In B5, enter the price per unit before the discount:

   =(SUMIFS(InvoiceLines[LineTotal],InvoiceLines[PromotionID],8)+SUMIFS(InvoiceLines[DiscountAmount],InvoiceLines[PromotionID],8))/PromoUnits

3. In B6, enter the standard cost per unit. This treats the whole standard cost as variable, an assumption you will document in Step 8.

   =SUMIFS(InvoiceLines[StdCostAmount],InvoiceLines[PromotionID],8)/PromoUnits

4. In B7, enter the actual commission rate of 2026, the year’s commission expense divided by all of its revenue:

   =SUMIFS(GLOpex[Amount],GLOpex[AccountNumber],6290)/SUMIFS(InvoiceLines[LineTotal],InvoiceLines[FiscalYear],2026)

The price before discount is well above the standard cost per unit, and the commission rate is about two percent. These four values describe the promotion as it actually ran.

Step 3. Build the model. The model compares the contribution of the promotion’s orders with and without the discount, assuming that without the promotion the same orders would have been placed at the price before discount.

1. In B9, enter:

   =PromoUnits*(PriceBeforeDiscount*(1-CommissionRate)-VariableCost)

2. In B10, enter:

   =PromoUnits*(1+Lift)*(PriceBeforeDiscount*(1-Discount)*(1-CommissionRate)-VariableCost)

3. In B11, enter =ContributionWith-ContributionWithout.

With no volume lift, the difference is the cost of the discount, net of the commissions the lower prices saved. Figure 7.7 shows the model.

Figure 7.7: The Promotion Model on the Model Worksheet.

Step 4. Compare scenarios with Scenario Manager.

1. Choose Data > What-If Analysis > Scenario Manager, and click Add.
2. Type the first scenario’s name from Table 7.4 in Scenario name, enter B2:B3 as the Changing cells, and click OK.
3. In the Scenario Values dialog, enter the scenario’s discount and lift as decimals, such as 0.1 for 10%, and click Add to go on to the next scenario.
4. Repeat the two actions for the other scenarios, and click OK after the last one.
5. Click Summary, choose Scenario summary, enter B10:B11 as the Result cells, and click OK.

Table 7.4: Scenarios for the promotion decision

Scenario	Discount	Lift
End the promotion	0%	0%
Repeat as in 2026	10%	0%
Repeat with 15% lift	10%	15%
Deeper discount	15%	15%

Excel adds a Scenario Summary worksheet, shown in Figure 7.8. Every scenario that keeps a discount has a lower contribution than ending the promotion. Even a 15 percent increase in volume recovers only part of the cost of a 10 percent discount, and a deeper discount with the same increase is worse than repeating the 2026 promotion.

Figure 7.8: The Scenario Summary Report.

Step 5. Map the result with a data table.

1. On the Model worksheet, enter =Difference in A15.
2. In B15:D15, type the discounts 0.05, 0.1, and 0.15.
3. In A16:A24, type the lifts 0 to 0.4 in steps of 0.05.
4. Select A15:D24, and choose Data > What-If Analysis > Data Table.
5. Enter $B$2 as the Row input cell and $B$3 as the Column input cell, and click OK.
6. Format the results as numbers with no decimals, and apply a conditional formatting rule that highlights the positive values.

The table shows where the promotion would break even. At a 10 percent discount, the difference turns positive only between a 30 and a 35 percent increase in volume. At 5 percent, a much smaller increase would be enough, and at 15 percent even a 40 percent increase leaves the promotion short.

Step 6. Find the break-even lift with Goal Seek. Goal Seek starts from the current values of the model’s inputs, so check first that B2 holds 10% and B3 holds 0%, the values from Step 1, and that B11 still shows a negative difference.

1. Choose Data > What-If Analysis > Goal Seek.
2. Enter B11 in Set cell, 0 in To value, and B3 in By changing cell, and click OK.
3. Note the lift that Goal Seek finds in B3, for the assumption table in Step 8.
4. Click Cancel in the Goal Seek Status dialog, so that B3 returns to 0.

At the 2026 discount, the promotion’s orders would have had to be almost a third larger for the promotion to pay for itself.

Step 7. Check the lift against the order data.

1. Select a cell in the SalesInvoiceLine Table, choose Insert > PivotTable, and place the PivotTable on a new worksheet named OrderVolume.
2. Drag OrderDate to Rows, right-click a date, choose Group, and select Months and Years.
3. Drag ItemGroup to Filters, and select Furniture.
4. Drag Quantity to Values.
5. Compare the units from orders placed in September and October 2026, the promotion’s months, with the other months of 2026 and with September and October of 2024 and 2025.

The Furniture units from orders placed in the promotion’s months were close to the other months of the year and a few percent above the same months of earlier years, far short of the increase the promotion needed. The comparison describes the orders; it cannot show how many would have been placed without the promotion, so it supports the zero-lift case rather than proving it. Because the PivotTable sums invoice lines, it counts only the part of each order already invoiced: November and especially December 2026 look low because some orders placed then were not yet invoiced when the data ends, which is a reason to read those months with care.

Step 8. Document the assumptions and the recommendation. The assumption documentation belongs next to the model it describes, so that a reviewer reads both together.

1. Type the assumption table in Table 7.5 in A27:D32 of the Model worksheet, below the data table, with its headings in row 27.
2. Complete its sensitivity column from what the data table and Goal Seek showed in Steps 5 and 6.
3. On the Documentation worksheet, add the Model, Scenario Summary, and OrderVolume worksheets to the Worksheets entry, and the nine names of the Model worksheet to the Parameters entry.
4. Add a Recommendation entry that states whether to repeat the promotion, the evidence, and the assumption that would have to change to reverse the recommendation.
5. Save the workbook.

Table 7.5: Assumptions of the promotion model

Assumption	Value used	Basis	Sensitivity
Discount rate	10%	The promotion’s DiscountPct in the PromotionProgram Table	A 5% discount would need a much smaller lift to break even
Volume lift	0%	A scenario, because orders in the promotion’s months rose only slightly and cannot show the promotion’s effect	The break-even lift is almost a third of the promotion’s volume
Orders without the promotion	Same orders at the price before discount	Promotion lines carry the customer’s usual price, with the discount applied on top	If some orders would have been lost without it, the promotion’s cost is lower
Variable cost per unit	Standard cost per unit	Item standard costs of the promotion’s items	Treating the fixed overhead in standard cost as fixed lowers the break-even lift slightly
Commission rate	2026 actual rate	Commission expense over all 2026 revenue, including design services, which earn a higher rate	Small; commissions fall with the discount

The Model worksheet now carries its own documentation: a reviewer can see each assumption, where it came from, and which one would change the answer.

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.

The recommendation follows from the model and its assumptions. If the same orders would otherwise have been placed at the usual prices, the promotion, repeated as it ran in 2026, would cost Charles River margin unless it lifted volume by roughly a third, and the 2026 orders show no increase anywhere near that. If the sales team wants a promotion, 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 exactly how low.

7.9 Communicating Forecasts and Models

A forecast presented as a single number invites more confidence than it deserves. Report a range alongside the point estimate, such as the range of recent quarterly values or the forecast plus or minus its backtest error, and state the method and the period it was fitted to. Such a range shows the size of the errors the method has made; it is not a confidence interval, which would need a statistical model of the forecast’s errors, so present it as what it is. A budget variance report should say which variances are meaningful and why: for Charles River, the sales-volume variance says more about the forecast behind the budget than about the business, and the report is clearer if it says so in a sentence than if it leaves a reader to discover it. A recommendation from a model should state the assumption that matters most and how far it would have to move to change the answer.

Models also need maintenance. The inputs of the promotion model come from 2026 data, and a refresh of the workbook updates them, but the assumptions about customer behavior do not update themselves. Some results do not update either: the ToolPak writes its output as values, a Scenario Summary is a snapshot of the scenarios when it was created, and Goal Seek and Solver leave behind the values they found, so each must be run again when its inputs change. Record in the workbook’s documentation when each assumption was last reviewed, and revisit the assumptions whenever the results they produce stop matching what happens.

NoteConnecting the Dots

The models in this chapter rest on the prepared, reconciled data of the earlier chapters: the invoice lines with their periods and product types, and the ledger totals you tested in Part I. The same questions recur in the later Parts of the book. A SQL query can produce the monthly series a forecast needs directly from the database, and an interactive report can present a variance analysis so that a manager can drill from a product line to its months. In every tool, a model deserves trust only after it has been tested against what actually happened.

7.10 Looking Ahead

This chapter has moved from explaining the past to estimating the future and evaluating decisions. You measured relationships with correlation and regression and learned to read the ToolPak’s output, and you saw how a single unusual month can make a weak trend look significant. You forecast with trend functions and learned to judge a forecast by backtesting it against simple benchmarks. You compared 2026 with the budget, flexed the budget to the actual volume, and separated a volume assumption that failed from a price assumption that was only slightly optimistic. Finally, you built a contribution margin model, asked what-if questions of it with Scenario Manager, data tables, and Goal Seek, and documented the assumptions behind its recommendation.

The controller now has a forecast she can defend, an explanation of the budget variances, and a recommendation on the promotion. Along the way, you have also seen several conditions in the data that an auditor would want to examine more closely, from invoices whose dates and numbers disagree to a planning forecast that has run well above actual demand. The next chapter looks at the Charles River data through the auditor’s eyes.

7.11 Chapter Summary

Predictive analytics estimates what is likely to happen, and prescriptive analytics recommends what to do. Both depend on assumptions that cannot be checked against the records until the future arrives, so every model in this chapter was paired with a test: residuals and influential points for a regression, a backtest for a forecast, a flexible budget for a plan, and a documented, sensitivity-tested list of assumptions for a decision model.

Correlation measures how closely two variables move together along a straight line, and simple linear regression fits the line itself. The Data Analysis ToolPak reports a regression’s R Square, coefficients, p-values, and residuals. A single unusual observation can create a correlation or a statistically significant trend. Charles River’s start-up month does both: it makes the number of customers look like the driver of revenue and makes a weak upward slope look statistically significant. The data and the residuals should therefore be examined before any result is reported. FORECAST.LINEAR and TREND extend a fitted line into the future, but a forecast deserves trust only after a backtest shows that it beats simple benchmarks such as the mean or the same season last year. For Charles River, whose monthly revenue since the start-up month shows no reliable trend, the mean outperformed the trend in the backtest.

A static budget is prepared for one planned volume, and a flexible budget recalculates it at the actual volume with the budgeted prices and costs. The sales-volume variance separates the effect of volume from the flexible-budget variance, which measures prices and costs on the units actually sold. Charles River’s sales budget planned for several times the volume the company sold, so its sales-volume variance says more about the forecast than about the business. Flexed to what was sold, the budget shows planned net prices that were slightly optimistic in every product line and Furniture promotion discounts that arrived later and larger than planned, while operating expenses were close to plan once the payroll burden rate and the volume-driven commissions were taken into account.

Cost-volume-profit analysis divides costs into variable and fixed parts. The contribution margin ratio and the fixed costs determine the break-even point and the margin of safety, and the contribution margin before and after a price change determines the volume increase a promotion needs. Scenario Manager, data tables, and Goal Seek ask what-if questions of such a model, and Solver finds the best plan under constraints and reports the value of constrained resources. Applied to the Furniture Seasonal Promotion, these tools showed that it would have needed roughly a third more volume on its orders to pay for itself, and the order data showed no increase near that.

7.12 Key Terms

Assumption documentation. A written record of each key assumption of a model: what it is, the value used, its source, and how sensitive the model’s conclusion is to it. It makes a model reviewable and shows where more evidence is needed.

Backtest. An evaluation of a forecasting method on data that already has an answer: the method is fitted to an earlier part of the history, forecasts a later part that was held back, and is judged by the forecast errors.

Benchmark forecast. A simple forecasting method, such as the mean, the last value, or the value of the same season last year, against which more elaborate methods are compared. A method that cannot beat the benchmarks is not worth using.

Break-even point. The volume or revenue at which total contribution margin equals fixed costs, so that profit is zero. Break-even revenue equals fixed costs divided by the contribution margin ratio.

Constraint. A limit that a solution must respect in an optimization model, such as the hours available in a work center or the demand for an item. In Solver, constraints are conditions on cells that depend on the variable cells.

Contribution margin. Revenue minus variable costs: the amount available to cover fixed costs and earn a profit. The contribution margin ratio is the contribution margin divided by revenue.

Correlation coefficient. A number from −1 to +1 that measures how closely two variables move together along a straight line, calculated in Excel with CORREL. It does not measure a curved relationship, and it does not show cause.

Cost-volume-profit analysis. A model of how profit responds to changes in volume, prices, and costs, built by dividing costs into variable and fixed parts. It gives the break-even point, the margin of safety, and the volume needed to earn a target profit.

Data table. An Excel what-if tool that recalculates a formula for each value in a row or column of inputs, or for every combination of two inputs, and fills in the results.

Flexible budget. A budget recalculated at the actual volume of activity, using the budgeted prices and costs per unit. Comparing actual results with it isolates the effects of prices and costs from the effect of volume.

Flexible-budget variance. The difference between actual results and the flexible budget. It measures the effect of actual prices and costs differing from the plan on the units actually sold.

Forecast error. The difference between a forecast and the actual value, often expressed as a percentage of the actual value. The mean absolute percentage error (MAPE) averages these percentages, ignoring their sign, over the periods forecast.

Goal Seek. An Excel what-if tool that finds the value of one input cell that makes a formula cell equal a target value, such as the volume at which a decision breaks even.

Margin of safety. The amount by which actual or planned revenue exceeds break-even revenue, in dollars or as a percentage of revenue.

Master budget. The complete budget of an organization for a period, combining the operating budgets for sales, production, purchasing, and expenses with the financial budgets for cash and the balance sheet.

P-value. In regression, the probability of a t statistic at least as far from zero as the one observed if the true coefficient were zero and the regression’s assumptions held. A p-value below 0.05 is the conventional threshold for statistical significance. It is not the probability that the coefficient is zero.

R Square. The share of the variation in the dependent variable that a regression line explains, from 0 to 1.

Residual. The difference between an actual value and the value a regression line predicts for it. A plot of the residuals shows where a line fits poorly and which observations stand apart.

Sales-volume variance. The difference between the flexible budget and the static budget. It measures the effect of selling a different volume and mix of units than planned, at the planned margins per unit.

Scenario. A named set of values for a model’s input cells. Excel’s Scenario Manager stores scenarios and compares their results in a Scenario Summary report.

Shadow price. In an optimization model, the amount by which the objective would improve if a constraint, such as the hours available in a work center, were relaxed by one unit. Solver reports it in the Sensitivity report of a linear model, with the range over which the constraint can change before the value changes.

Simple linear regression. A method that fits the straight line y = a + bx minimizing the squared differences between the actual values of a dependent variable and the line, using one independent variable.

Solver. An Excel add-in that finds the values of several variable cells that maximize or minimize an objective cell subject to constraints. For linear models, its Simplex LP method finds the optimum and can report shadow prices.

Spill range. The range of cells that a dynamic array formula, such as TREND, FILTER, UNIQUE, or SORT, fills with its results. The address of its first cell followed by #, as in A5#, refers to the whole range.

Static budget. A budget prepared for a single, planned level of activity and not adjusted for the activity that actually occurs.

7.13 Multiple Choice Questions

1. The correlation coefficient between two monthly accounting measures is −0.80. What does this indicate?

1. They are unrelated, because a negative coefficient means no relationship.
2. The first measure is, on average, 80 percent smaller than the second.
3. A rise in one measure causes the other to fall.
4. They move strongly in opposite directions along a straight line.

2. Removing a single unusual month from a data set changes the correlation between revenue and the number of customers from strong to weak. What should an analyst do?

1. Investigate the month and report both results, with any exclusion explained.
2. Report the weaker correlation, because unusual months should always be deleted.
3. Report neither, because correlation cannot be used with monthly data.
4. Report the stronger correlation, because it uses more of the data.

3. A regression of monthly revenue on a month index reports an R Square of 0.17. What does this mean?

1. Revenue grows by about 17 percent from one month to the next.
2. The line explains about 17 percent of the variation in monthly revenue.
3. The slope has a 17 percent chance of being zero in the population.
4. Each forecast from the line will fall within 17 percent of actual revenue.

4. Fitted to all thirty-six months, the slope of Charles River’s revenue trend has a p-value of about 0.01. Fitted without the start-up month, the p-value is about 0.07. What is the best conclusion?

1. Revenue has a strong upward trend that should be extended into the forecast.
2. The start-up month should be kept, because it makes the result significant.
3. The significance depends on one month, so test the trend before using it.
4. A p-value above 0.05 proves that revenue will not grow in the coming year.

5. What is a backtest of a forecasting method?

1. Recalculating a forecast after the period and replacing it with the actual amount
2. Fitting it to earlier data and testing its forecasts on held-back later data
3. Forecasting the past by running the method backward from the latest period
4. Checking that the forecast formulas contain no errors before the forecast is used

6. Why should a new forecasting method be compared with simple benchmarks, such as the mean or the value of the same quarter last year?

1. Excel cannot calculate forecasts more complex than the benchmarks.
2. Benchmarks are always more accurate than more elaborate methods.
3. Auditing standards require every forecast to be compared with the mean.
4. A method that cannot beat them is not worth its extra complexity.

7. How do TREND and FORECAST.LINEAR differ?

1. Both fit a least-squares line, but TREND takes a range of new x values.
2. FORECAST.LINEAR requires the Data Analysis ToolPak, while TREND does not.
3. TREND works only with dates, while FORECAST.LINEAR works only with numbers.
4. TREND fits a curved line, while FORECAST.LINEAR fits a straight line.

8. What is a flexible budget?

1. A budget that managers may change during the year without approval
2. The static budget with every revenue and cost amount raised by the same percentage
3. The budget recalculated at actual volume with budgeted prices and unit costs
4. The actual results rearranged in the format and order of the budget

9. What does the sales-volume variance measure?

1. The difference between the actual and the budgeted fixed costs
2. The effect on revenue of actual selling prices differing from the budgeted prices
3. The effect of selling more or fewer units than planned, at planned margins
4. The effect of producing more units than were sold in the period

10. Actual sales commission expense is 75 percent below the static budget, and actual revenue is far below the budgeted revenue. What is the best interpretation?

1. The sales team cut its commission rates, which saved the company money.
2. Much may reflect lower revenue, so flex the budget before judging the rest.
3. Part of the commission expense was never recorded in the ledger.
4. Commissions are a fixed cost, so the whole difference is a spending variance.

11. A company has fixed costs of $900,000 and a contribution margin ratio of 30 percent. What is its break-even revenue?

1. $270,000
2. $1,170,000
3. $2,700,000
4. $3,000,000

12. What does Goal Seek do?

1. It finds the value of one input that makes a formula cell equal a target.
2. It shows a formula’s result for every value in a range of one or two inputs.
3. It compares the results of several named sets of input values in a report.
4. It maximizes or minimizes a result subject to several constraints on its inputs.

13. An analyst wants to see how the difference in contribution changes for every combination of three discount rates and nine volume increases. Which tool fits best?

1. Goal Seek
2. A two-variable data table
3. A one-variable data table
4. The Correlation tool of the ToolPak

14. A production-mix model is solved with Solver’s Simplex LP method. Under what condition can Solver also produce a Sensitivity report with shadow prices?

1. When every variable cell is required to be a whole number
2. When the model is solved with the Evolutionary method instead
3. When it finds an optimum and the model has no integer constraints
4. Only when the model has a single constraint on its variable cells

15. Why does a documentation table for a model’s assumptions include a sensitivity column?

1. To show which assumptions most affect the conclusion and need review
2. To prove that every assumption used in the model is correct and complete
3. To record the formula used in each cell of the model
4. To list the people who approved each assumption and when

7.14 Applied Exercises

As in the earlier chapters, work on these exercises in a fresh copy of your analysis workbook, made after you finish this chapter’s tutorials. Put each exercise on its own worksheets, named after it, and do not rename or edit the queries the tutorials created. When an exercise needs a Table the tutorials filtered, import it again as a new query with the name the exercise gives.

7.14.1 Financial Accounting Exercises

Exercise 7.1: Forecasting Revenue by Product Line

Table group: Order-to-Cash, with the Item table from Master Data

Scenario. Charles River’s loan agreement requires management to send the bank, with the annual financial statements, a revenue forecast for the coming quarter by product line, which the bank uses to monitor the company’s covenants. Because the forecast goes to an external user who will rely on it, the controller asks you to test the forecasting methods of Tutorial 7.1 on each product line before choosing one.

Requirements.

1. Build a table of quarterly revenue by item group for 2024 through 2026 from InvoiceLines, with a quarter index from 1 to 12.

2. For each item group, fit three methods to the eight quarters of 2024 and 2025 and forecast the four quarters of 2026: a trend with FORECAST.LINEAR, the mean, and the same quarter of the previous year. Calculate the mean absolute percentage error of each method over the four quarters.

3. Choose a method for each item group, and explain the choice in one or two sentences per group. Consider whether the first quarter of 2024, which contains the start-up month, should be left out of the fit.

4. Forecast the first quarter of 2027 for each item group with your chosen method, give a range for each, as in Tutorial 7.1, and compare the sum of the item-group forecasts with the company-level forecast from Tutorial 7.1.

Deliverable. A backtest table by item group, the chosen methods with reasons, and a forecast of the first quarter of 2027 by item group with ranges, labeled as billed revenue by invoice date, the basis of InvoiceLines.

Exercise 7.2: Reconciling the Budget to Its Detail

Table group: Organizational Planning, with the Account table from Accounting Core

Scenario. The bank also receives Charles River’s annual budget, and last year’s budget summary for the bank was prepared from the Budget Table, which summarizes the budget by cost center and account. Before the 2027 budget is sent in the same form, the controller asks you to confirm that the summary agrees with the detailed budget lines.

Requirements.

1. Import the T76_Budget Table with Power Query as a query named BudgetSummary. Use Transform > Group By, with Advanced, to total BudgetAmount by FiscalYear and AccountID, and merge in AccountNumber and AccountName from the Account query of Tutorial 7.2, without the prefix.

2. Import the T77_BudgetLine Table again as a new query named BudgetLineAll, because the BudgetLine query of Tutorial 7.2 holds only 2026. Keep the Revenue, COGS, and Operating Expense categories of every year, and total BudgetAmount by FiscalYear and AccountID with Group By, again with Advanced.

3. Compare the two for 2026 by account, and list every account whose totals differ. Examine the BudgetLine rows behind the differences and identify what they have in common.

4. Repeat the comparison for 2024 and 2025.

5. Write a memo of half a page that explains the difference, states which Table should be used for the budget sent to the bank, and recommends a control that would detect the problem in future.

Deliverable. A reconciliation by account for 2026, the totals for 2024 and 2025, and a half-page memo.

7.14.2 Managerial Accounting Exercises

Exercise 7.3: Company Break-Even and Margin of Safety

Table group: Order-to-Cash, Accounting Core, and Organizational Planning

Scenario. As part of the 2027 plan, the chief financial officer asks how far revenue could fall before Charles River stopped earning an operating profit, using 2026 as the base.

Requirements.

1. Calculate the 2026 contribution margin ratio as revenue minus the standard cost of the products sold and minus the sales commission expense, divided by revenue. Use InvoiceLines for revenue and standard cost, and the GLOpex query from Tutorial 7.2 for commissions. As in Tutorial 7.3, this treats the whole standard cost as variable.

2. Use the 2026 operating-expense budget, excluding sales commissions and interest expense (account 7030, which the income statement of Chapter 6 places below operating income), as the fixed costs. Explain in two sentences why the budget is a reasonable source for them.

3. Calculate the break-even revenue and the margin of safety in dollars and as a percentage of 2026 revenue.

4. The fixed costs in (2) leave out the freight-out expense and the purchase price and manufacturing variances recorded in 2026, which GLOpex does not contain. Import the T3_GLEntry Table again as a new query named GLCostVariances, clicking Transform Data, and filter FiscalYear to 2026, AccountID to 54, 55, and 93 (accounts 5050, 5060, and 5080), and SourceDocumentType to exclude JournalEntry, whose only rows on these accounts are the closing entry. Add the three accounts’ totals to the fixed costs, and recalculate the break-even revenue and margin of safety.

5. Build a two-variable data table of break-even revenue for contribution margin ratios from 40 to 50 percent and fixed costs from $6 million to $10 million, and use Goal Seek to find the revenue that produces an operating income of $4 million under the assumptions of (4).

6. Write a memo of half a page that states your break-even estimate and the assumption that affects it most.

Deliverable. A break-even model with two sets of fixed costs, a sensitivity table, and a half-page memo.

Exercise 7.4: The Product Mix under a Capacity Constraint

Table group: Manufacturing and Order-to-Cash, with the Item table from Master Data

Scenario. This exercise uses a hypothetical situation. Suppose that an equipment upgrade in 2027 halves the hours available in every work center for a year, while demand for each manufactured item stays at its 2026 level. In 2026, Charles River’s plant used well under its capacity, so the constraint in this exercise is an assumption, not a description of the data. The operations manager asks which items to make.

Requirements.

1. Import the T44_Item Table again as a new query named ItemRouting, keeping ItemID, ItemCode, ItemGroup, StandardCost, StandardFixedOverheadCost, SupplyMode, and RoutingID, because the Item query of Chapter 4 does not have StandardFixedOverheadCost and RoutingID. Import the T50_RoutingOperation, T47_WorkCenter, and T48_WorkCenterCalendar Tables, and list the manufactured items that sold in 2026.

2. For each item, calculate its 2026 demand in units, its contribution per unit as the 2026 average price per unit minus the variable standard cost (StandardCost minus StandardFixedOverheadCost) minus the commission rate from Tutorial 7.3 times the price, and its run hours per unit at each work center, summing StandardRunHoursPerUnit over the operations of its routing.

3. Calculate each work center’s capacity as half of its 2026 available hours, and set up the production quantities as the variable cells, the total contribution as the objective, and the hours used at each work center as SUMPRODUCT formulas. SUMPRODUCT multiplies two ranges of the same size cell by cell and adds the products, so =SUMPRODUCT(Hours,Quantities) gives the hours that a production plan uses.

4. Use Solver with the Simplex LP method to maximize the total contribution, subject to hours used within capacity at every work center and production between zero and demand for every item. Do not require whole units, so that Solver can produce its Sensitivity report.

5. Using the answer and the Sensitivity report, identify the work centers whose capacity binds, the shadow price of their hours and the range over which it holds, the items Solver reduces, and the contribution lost compared with meeting all demand.

Deliverable. A Solver model, its Answer and Sensitivity reports, and a half-page recommendation to the operations manager.

7.14.3 Auditing Exercises

Exercise 7.5: Regression-Based Expectations for Revenue and Commissions

Table group: Order-to-Cash and Accounting Core

Scenario. Chapter 1 described a regression-based expectation for monthly revenue as a substantive analytical procedure. Your audit senior asks you to test whether such an expectation would be precise enough for Charles River, and to compare it with an expectation for sales commission expense. The standard on substantive analytical procedures of the Public Company Accounting Oversight Board (PCAOB) asks for expectations precise enough to identify potential material misstatements, and it notes that expectations built on monthly amounts are generally more effective than those built on annual amounts (PCAOB, n.d.-a).

Requirements.

1. Build a monthly table for 2024 through 2026 with revenue from InvoiceLines and the number of invoices from the SalesInvoice Table. GLOpex holds only 2026, so import the T3_GLEntry Table again as a new query named GLCommission, clicking Transform Data, and filter AccountID to 104 (account 6290, Sales Commission Expense) and SourceDocumentType to exclude JournalEntry, whose only rows on this account are the three year-end closing entries. Add the monthly commission expense to the table.

2. Regress monthly revenue on the number of invoices, with and without January 2024, and compare R Square and the standard error with the average monthly revenue. Explain whether the expectation would be precise enough to detect a misstatement of 5 percent of a month’s revenue, the amount your senior wants the procedure to detect.

3. Regress monthly commission expense on monthly revenue for 2024 and 2025, and use the fitted line to set an expectation for each month of 2026.

4. Flag the 2026 months whose recorded commission expense differs from the expectation by more than 3 percent, your senior’s threshold for follow-up, and describe the evidence you would examine for each. Look at the signs of the differences in consecutive months.

5. Import the T27_SalesCommissionAccrual Table, total the CommissionAmount of the accruals dated in 2026, and reconcile the total to the 2026 commission expense in GLCommission. Identify the source document type that explains the difference.

Deliverable. The regressions of requirements (2) and (3), a table of 2026 expectations and differences, and a half-page memo to your senior.

Exercise 7.6: Evaluating Management’s Forecast

Table group: Demand Planning and MRP, Order-to-Cash, and Organizational Planning

Scenario. Charles River’s demand forecast drives its purchasing and production plans and the quantities in its budget, and management may use it to support accounting estimates, such as the need for an inventory reserve. When a company’s estimates rest on assumptions, the PCAOB’s standard on auditing accounting estimates asks the auditor to evaluate whether the significant assumptions are consistent with, among other things, the company’s historical or recent experience (PCAOB, n.d.-b). Your senior asks you to evaluate the forecast against that experience.

Requirements.

1. Import the T78_DemandForecast Table with Power Query, keeping the rows whose IsCurrent is 1, the current version. Merge in ItemGroup from the Item query without the prefix, and total ForecastQuantity by item group and by the year of ForecastWeekStartDate, for 2024 through 2026. The forecast also covers the first weeks of 2027, which have no orders yet to compare with.

2. Import the T10_SalesOrderLine Table as a query named OrderLines. Merge in OrderDate from the SalesOrder query of Tutorial 5.2 and ItemGroup from the Item query, both without the prefix, total Quantity by item group and by the year of OrderDate, and calculate the ratio of forecast to orders for each year and item group. Keep Services, whose forecasts and orders are hours of design work, in a row of its own, and add a total for the four product groups.

3. Describe how the ratio has changed over the three years, and whether it differs among the item groups.

4. Compare the 2026 budget’s quantities with the 2026 forecast and with the 2026 orders of the four product groups, which are all the budget plans.

5. Each forecast records the baseline quantity that the forecasting method produced, the quantity after any planner adjustment, the planner, and the employee who approved it. Add a column with the ratio of ForecastQuantity to BaselineForecastQuantity, and list the forecasts adjusted up or down by more than half of their baseline. Import the T74_Employee Table as a query named Employee, and look up the planners and approvers of the listed forecasts in it. Explain which control the list tests and what it shows.

6. Write a memo of half a page that states whether the forecast is consistent with historical experience, the consequences for any estimate or plan that relies on it, what the adjusted forecasts show about the review of planners’ changes, and the evidence and explanations you would request from management.

Deliverable. A comparison of forecast and actual orders by year and item group, a list of the adjusted forecasts, and a half-page memo.

7.15 Further Reading

Datar, S. M., & Rajan, M. V. (2021). Horngren’s cost accounting: A managerial emphasis (17th ed.). Pearson. https://www.pearson.com/en-us/pearsonplus/p/9780136713845 The standard cost accounting text. Its chapters on cost-volume-profit analysis, on flexible budgets and sales-volume variances, and on estimating cost behavior with regression develop the managerial accounting behind this chapter’s models in depth.

Frontline Systems. (n.d.). Excel Solver: Create Solver reports. https://www.solver.com/excel-solver-create-solver-reports The developer of Excel’s Solver explains the Answer, Sensitivity, and Limits reports and when each is available. Its related pages explain how to read the shadow prices and reduced costs in the Sensitivity report.

Hyndman, R. J., & Athanasopoulos, G. (2021). Forecasting: Principles and practice (3rd ed.). OTexts. https://otexts.com/fpp3/ A free, widely used online textbook on forecasting. Its chapters on simple benchmark methods and on evaluating forecast accuracy expand on the backtesting approach of Tutorial 7.1, and later chapters cover methods for seasonal and trending series.

Microsoft. (2026c). Introduction to what-if analysis. Microsoft Support. https://support.microsoft.com/en-us/excel/introduction-to-what-if-analysis Microsoft’s overview of Scenario Manager, Goal Seek, and data tables, with links to step-by-step instructions for each tool and to Solver.

Nikitkov, A. (2024). Budget variance analysis case: Contrasting Excel with Tableau. Accounting Perspectives, 23(4), 605–612. https://doi.org/10.1111/1911-3838.12383 A teaching case in which you prepare a budgetary variance analysis for a board meeting, first in Excel and then as a visual analysis in Tableau. It extends the variance report of Tutorial 7.2 toward the presentation tools of Part IV.

Silvester, K. J. (2017). Faith’s Country Primitives: An Excel project in flexible budgeting and standard cost analysis. Journal of Business Case Studies, 13(4), 85–98. https://doi.org/10.19030/jbcs.v13i4.10030 An Excel case project that takes you through budgeting, flexible budgets, and standard cost variances in one integrated exercise, so that you can see how the individual variance calculations fit together.

Yahya-Zadeh, M. (2002). A linear programming framework for flexible budgeting and its application to classroom teaching. Issues in Accounting Education, 17(1), 69–93. https://doi.org/10.2308/iace.2002.17.1.69 An article that builds production and market constraints into flexible budgeting and variance analysis with linear programming. It connects the flexible budget of Tutorial 7.2 with the Solver model of Exercise 7.4.

7.16 References

Anscombe, F. J. (1973). Graphs in statistical analysis. The American Statistician, 27(1), 17–21. https://doi.org/10.1080/00031305.1973.10478966

Datar, S. M., & Rajan, M. V. (2021). Horngren’s cost accounting: A managerial emphasis (17th ed.). Pearson. https://www.pearson.com/en-us/pearsonplus/p/9780136713845

Frontline Systems. (n.d.). Excel Solver: Create Solver reports. https://www.solver.com/excel-solver-create-solver-reports

Hyndman, R. J., & Athanasopoulos, G. (2021). Forecasting: Principles and practice (3rd ed.). OTexts. https://otexts.com/fpp3/

Microsoft. (2026a). Define and solve a problem by using Solver. Microsoft Support. https://support.microsoft.com/en-us/office/define-and-solve-a-problem-by-using-solver-5d1a388f-079d-43ac-a7eb-f63e45925040

Microsoft. (2026b). FORECAST and FORECAST.LINEAR functions. Microsoft Support. https://support.microsoft.com/en-us/office/forecast-and-forecast-linear-functions-50ca49c9-7b40-4892-94e4-7ad38bbeda99

Microsoft. (2026c). Introduction to what-if analysis. Microsoft Support. https://support.microsoft.com/en-us/excel/introduction-to-what-if-analysis

Microsoft. (2026d). Load the Analysis ToolPak in Excel. Microsoft Support. https://support.microsoft.com/en-us/excel/load-the-analysis-toolpak-in-excel

Public Company Accounting Oversight Board. (n.d.-a). AS 2305: Substantive analytical procedures. https://pcaobus.org/oversight/standards/auditing-standards/details/AS2305

Public Company Accounting Oversight Board. (n.d.-b). AS 2501: Auditing accounting estimates, including fair value measurements. https://pcaobus.org/oversight/standards/auditing-standards/details/AS2501