A Practitioner’s Guide to the Quarter That Beat Its Budget, Missed Its Plan, and Earned Less Money
Julian R. Sterling
These are the four Excel workbooks that go with the book. Every figure the book prints is
reproduced in them by a live formula rather than a typed constant — change the units, the
discount, the attrition rate, the avoidable share of the corporate pool or the number of forecast
observations, and every dependent number moves. Each one ends with a Checks
sheet setting the printed figure beside the computed one: 49 controls in all, every one
green, recalculated in LibreOffice. If a control ever reads CHECK rather than PASS,
either the workbook is wrong or you have changed an input — and you now know which of the
book’s conclusions depended on it.
Free to download. No sign-up, no email address, nothing to fill in.
Everything described below is inside it, with the read-me.
The four workbooks
Chapters 2, 7 and 8
The five comparisons
The same quarter’s revenue of 216,340,000 held against all five numbers
the company already owns: the budget of 210,000,000, the prior year’s 218,900,000, the
July forecast of 221,000,000, the preceding quarter’s 208,600,000 and the underwriting
plan’s 232,500,000. Two read favourable and three adverse, and the spread between the
best and the worst reading is 10.6610 per cent of revenue. Every one is
correct.
A second sheet carries the quarterly phasing of all three documents, so the
90,000,000 between the approved budget and the plan the owners underwrote
can be seen where it actually accumulates rather than only in the quarter that has to explain
it. The budget is 0.0000 per cent above the prior year; the plan is 10.7143 per cent above it.
The_Five_Comparisons.xlsx · XLSX · 9 KB
Chapters 4 and 5
Price, volume and mix
The instrument decomposition on revenue and on gross profit, with the standard unit cost held
at 62,560.00 throughout so that nothing here is a cost story. Volume adds
16,560,000 of revenue and price removes 5,520,000; on gross profit volume adds
5,299,200 and price removes 5,520,000, so the segment sold 180 more units and earned
220,800 less. Both conventions for the cross term are on the page, along with
the 720,000 that moves between them — which is why the base has to be stated every time.
Then the group bridge, and the part worth playing with: the margin movement split into
-1.5733 points of mix and -1.4278 of price. Set the discount
to zero and the gross profit variance turns favourable by 1,919,200 while the margin stays
1.5733 points below budget. The mix effect survives any pricing assumption you care to impose,
and it is the larger of the two.
Price_Volume_and_Mix.xlsx · XLSX · 9 KB
Chapters 13, 14 and 16
Segment contribution and allocation
Contribution built from the segment expense lines, then the same 76,000,000 corporate pool
allocated on revenue and on gross profit, side by side. Instruments reports
-37,642,285.71 on one base and -25,717,899.36 on the other:
11,924,386.35 of reported profit created by the choice of denominator, with no
cash moving and no decision taken.
Then the closure test, with the avoidable share of the pool as a switchable input: the
five-year decay of the installed base at 8.00 per cent, and a year-one cost of
9,659,200 to close the group’s largest reported loss-maker. And the
discount judged over the life of what it placed — 6,951.67 a year per instrument on the
conservative reading, 10,076.67 on the marginal one, a year-one net of -4,268,700 and a payback
of 4.4114 years against a 12.5000-year implied life. The discount rate is a
cell: the placements are worth 1.5782 times the discount at 10.00 per cent and stop repaying
only above 20.4548 per cent.
Eight observations of the error of the forecast made at the start of each quarter. Mean
1.8568 per cent, sample standard deviation 2.0273, and a ratio of bias to
noise of 0.9159x — a forecast that is merely noisy is a forecast; one
whose mean error is not zero is something else. Mean absolute error falls from 2.4068 per cent
to 1.4926 from one subtraction and no new information, an improvement of
37.9843 per cent.
The correction applied to the submitted fourth quarter, and beside it the honest width of that
correction: one standard error either side spans 233,339,924.86 to 230,078,694.71, a band of
3,261,230.15 against a correction of 4,302,056.44. The observation count is an input, so the
thinness of the sample can be tested rather than asserted.
Forecast_Accuracy.xlsx · XLSX · 8 KB
Conventions used throughout
Blue text
a hardcoded input — you may edit these
Yellow fill
an assumption that decides the answer
Black text
a formula — do not overtype these
Checks sheet
the printed figure beside the computed one, with a PASS or a FAIL
Why the checks matter more than the models
A workbook that agrees with a book proves nothing on its own — the author wrote both. What
the Checks sheets do is different: they force the model to reproduce a number that was printed
before the model existed, from a formula rather than from the number itself.
On this book the discipline caught one thing that would have inverted a conclusion. The
downstream earnings of a placed instrument had been computed by dividing the consumables and
service contribution by the year’s 4,800 placements rather than by the installed base of
24,000 — an overstatement of exactly 5.0000x. On the wrong figure the
discount cleared its bar inside year one and the chapter had a tidy reversal. On the right one it
destroys value in year one, exactly as the variance report said, and repays only over the life of
what it placed: 4.4114 years against an implied 12.5000. The tidy version was
wrong and the true one is more interesting, which is usually the way round it goes.
Where a shortcut and the full computation disagree, both are shown. The de-biased fourth quarter
is 231,697,943.56 on the exact coefficient and 231,697,834.61 on the same coefficient rounded to
four decimals; the per-representative figures differ in the last cent depending on whether you
divide the variance or difference two rounded averages; and the full-year gross profit at the
budget margin is 78.67 apart on the rounded and unrounded rate. None of the three gaps is smoothed
away, because a reader who can see the gap is a reader who will not be surprised by it.
Opening the files
The workbooks open in Microsoft Excel, LibreOffice Calc, Google Sheets and Numbers. They use
no macros and no add-ins, so nothing needs to be enabled or trusted. If your spreadsheet asks
to update links on opening, decline — there are none.
Also by Julian R. Sterling
The other books with companion files. The full list of titles is on the
author page.
Credit AnalysisOne borrower, four defensible EBITDAs, and the add-back argument worth fifty times the covenant headroom.
Closing the DealTwo defensible bridges 20.70 million apart, a peg worth 7.00 million, and the six choices that remove 6.60 of a 12.00 earn-out.
CMBS and CRE CLOsWhere the loss actually lands, from appraisal reduction to realised severity, and what the B-piece is really being paid for.
How to Read a Commercial LeaseThe three refinements chapter 19 names and never performs, and the renewal rate below which the mark-to-market is worth nothing.
How to Read a Credit AgreementWhere the default actually comes from, the cure that costs 5.5 times the other, and the capacity nobody adds up.
How to Read a Real Estate Loan AgreementThe cure ratio in closed form, the four-point window in which the cheap cure works, and the cure sized to the wrong threshold.
Office Real EstateA six per cent yield that returns 3.2 per cent once the re-letting cycle is paid for, and the headline-to-net-effective rent arithmetic.
Private Equity Real EstateBoth worked waterfalls to the dollar, the two capital stacks, and the arithmetic of the promote made changeable.
Private Markets PerformanceThirty-one of the thirty-three figures chapter 19 publishes reproduce exactly — and the two that do not are named rather than quietly adopted.
Raising a Real Estate FundThe chapter 17 funnel run on a calendar — when the first close actually lands, and why more travel does not help.
Real Estate FinanceFour people look at one building and reach four numbers; the lender is whole only above 105,109,489, twelve per cent below today’s value rather than forty.
Real Estate Financial ModelingProperty, development and fund models built line by line, and the modelling test worked end to end.
Real Estate Fund ManagementThe waterfall of 6.11, the build-to-core of 8.7 and the proceeds gap, reproduced as live formulas rather than asserted.
REIT Analysis and ValuationFFO of 532.0, AFFO of 381.0, net asset value and dividend safety — every figure a formula you can change.
Retail Real EstateThe occupancy cost of every unit in a centre, the sixteen per cent of the rent roll no tenant can sustain, and the right-size-convert-or-hold decision priced.
Sale and LeasebackA €179.5 million transaction end to end, with rent cover measured on the entity that actually signs the lease.
Self-Storage Real EstateThe cohort engine behind a 590-unit store, and the rate increase on existing customers priced against the move-outs it causes.
The Fund Finance ProfessionalChapter 8 builds the reported-to-eligible NAV bridge; chapter 9 computes every ratio without it. Two points at every state — and what a subscription line does to the IRR.
The Growth Equity InvestorWhat a pro rata cheque really costs, and the band where defending your ownership loses money.
The Private Credit InvestorThe two coverage ratios are not measured on the same thing: the erosion is 47.7 per cent, not the 28.7 the headline implies.
The Private Equity Fund Controller PlaybookThe book defines IRR, DPI, RVPI and TVPI, tells you to update them at the exit, and prints not one value. Computed: a 1.833× deal inside a fund at 0.892 TVPI.
The Venture Capital AssociateWhat defending a position costs, and how many companies a reserve pool actually defends.
Treasury ManagementFive people quote the cash balance of one company on one morning, from 214,000,000 to 68,400,000, and all five are right.
Energy TradingA position report that is 91.7031 per cent hedged and correctly computed, on a book that is short 2,542,000 MWh — and a margin call of 198,400,000 the next morning.
Financial Risk ManagementA fund inside every limit that cannot meet a redemption — and the number that decides it is the one with no currency attached.
Business ValuationThree advisers land 26.8 per cent apart on one company, and the whole gap turns out to be 1.96 points of perpetual growth.
Quantitative FinanceThree models agree to a quarter of one per cent about a number that one unobservable input moves a hundred and three times as much.
Asset ManagementFour people quote four returns for one mandate, all correct and 2.7017 points apart — forty-eight times the manager’s net skill.
Alternative InvestmentsA manager reports 13.29 per cent and the endowment earns 6.26 — both correct, and only a third of the advertised advantage arrives.
Venture CapitalOne company out of twenty-eight returns 56.7 per cent of the fund, and half the capital goes in after the decision — at half the return.
Machine Learning for FinanceFive people quote the accuracy of one credit model, all five are right, and the number that decides how much money it makes is none of them.
Commercial Real Estate InvestingThe equity earned 8.6647 per cent and the investor received exactly 8.0000 — the preferred return, and nothing above it.
Mergers and AcquisitionsThe board paper says the deal creates 13,436,667 of value. The arithmetic says it destroys 17,530,855. Nobody is lying.
DerivativesThe treasury report says the hedge cost 1,233,698. That is the interest differential, not a cost.
Construction Cost ControlA contract sum of 26,301,102 became a final account of 29,153,363 on the building that was drawn — and 85.8 per cent of what was lost was knowable on the day it was signed.
Pricing StrategyA list price of 148.00, a pocket price of 112.51, and the nine deductions in between — with what one point of price is actually worth.