A Practitioner’s Guide to the Flat Book, the Hours It Averages Away, and the Margin Call That Arrives First
Julian R. Sterling
These are the companion files of Energy Trading by Julian R. Sterling.The book on Amazon →
The four Excel workbooks that go with the book, corrected and extended where they fell short of what
the book says they do: every figure is a live formula, and each file now closes on a checks sheet,
152 controls in all, every one green. Around them: the same four models with the inputs
emptied, thirteen working documents to print, forty questions that mark themselves, and three cases
the book names and never takes to a number. Nothing is locked, protected or watermarked.
Free to download. No sign-up, no email address, nothing to fill in.
Twelve workbooks, the printable documents and the read-me. Each group below can also be
downloaded on its own. Last revised 24 September 2026.
Get an email if these files are corrected
Optional. When a workbook on this page is corrected or a new worked case goes up, readers on the list get one email, plus a short note when a new book comes out. The downloads do not need it.
These carry the book’s own figures, so that every number it prints can be traced to a live
formula. Each carries its own copy of the curve; the four files are not linked to one another.
Download these four72 KB
Chapters 2 and 5
The price duration curve
The year’s 8,760 hours sorted and grouped into eight rows, and the two averages it
yields. The time-weighted average is 70.1872, and the annual baseload forward
is set equal to it by construction — which is why no figure in this book rests on a view
about the price level. The average over the plant’s 8,000 available hours is
73.6250, because the outage removes the cheapest hours of the year, and the
difference between those two numbers is the source of one of the book’s two headline
errors.
Also on the sheet: how many hours price below the average (a majority of them), the highest
hour as a multiple of the forward, and what deleting the top 100 hours does — it moves the
annual average by 2.1918 and removes 19.8548 per cent of a power station’s margin.
The dispatch built row by row off the curve: marginal cost 65.32, 3,900
running hours of 8,000 available, 2,418,000 MWh, and a gross margin of
60,792,240 of which 42.7202 per cent is earned in 350 hours. Then the same
plant valued four ways — must-run and baseload-hedged, each priced at the annual forward
and at the average of the hours the block actually covers.
Two results fall out. A baseload hedge priced on its own hours, dispatched with buy-back, is
worth exactly what the unhedged plant is worth: hedging a plant as a block does not destroy its
optionality. And the wider gap a careless reader would quote, 36,650,855.53, is two things:
17,051,415.53 of an average taken over the wrong hour set, and
19,599,440 of genuine convexity. A second sheet moves the 760 outage hours to
the top of the curve, where they cost 36,057,216, and now does it for any number of outage hours.
Added: the concentration table of Chapter 5, the buy-back in its two legs (a gain of
31,971,899.54 that must never be reported without the 49,023,315.07 given up on the leg the plant
delivers), the gas and heat-rate thresholds, and the starts that would close the gap to the flat
block: 1,059.4292 in all, not the 963.4292 the book reads as the whole count.
A book of 4,200,000 MWh sold at a fixed 78.50, hedged with a flat block bought at the forward,
booking a margin of 25,883,698.63. Then the load shape, as a multiplier on average load,
normalised so the year’s volume is exactly the contracted figure. The flat block costs
294,786,301.37; the hours cost 317,068,468.18.
The difference is the shape cost: 22,282,166.81, or 5.3053 per MWh, which is
86.0857 per cent of the margin the desk booked. The price the book is really
exposed to is not 70.1872 but the load-weighted 75.4925. The multipliers are
inputs: flatten them towards 1.00 and watch the cost fall away, which is the cheapest way to
see that the whole effect is a covariance between load and price. Added: the Amplitude sheet, which
scales the shape the way Chapter 9 describes (half the amplitude costs 11,173,483.73) and solves
the amplitude at which the shape takes the whole booked margin: 1.1627x, not the
1.1616x the book gets by dividing one figure by the other. And the Group sheet, which is
Chapter 15: 67,076,498.63 flat against 64,393,771.82 hour by hour.
The position report says the book is 91.7031 per cent hedged and short 760,000 MWh, and both
figures are correctly computed. The sheet then reconciles them to the plant’s actual
output and finds a true short of 2,542,000 MWh, 3.3447x the
reported one, because the hedge was sized on 8,000 hours and the plant runs 3,900.
Then the cash. A declared parallel rise of 40.00 puts 198,400,000 of variation
margin out of the door the next morning against 158,000,000 of cash and undrawn facility. With
both legs cleared on one exchange the same position calls 30,400,000: a factor
of 6.5263x, decided by where the paperwork sits. The declared move is a cell, so the call, the
day-one position, the move that exhausts liquidity and the implied position limit all move with
it. Corrected: the volume sold and the plant’s output were typed in, so changing the heat rate
moved nothing; both are now computed from the curve and the plant, which the file now carries. A
last sheet sets the limit at 20.00, 40.00 and 60.00.
The four workbooks with every input emptied. The checks sheets count what is still missing and test what holds for any inputs.
Chapters 2 to 15
Your curve, your plant, your customers, your paperwork
The price duration curve, the plant four ways, the shape and the group, and margin and liquidity, with the same formulas and every blue cell emptied. Nothing computes until every input is in. The amplitude step and the moves of 20, 40 and 60 stay.
The book’s inputs are listed in a text file inside the archive. Type them back in and its figures come back with them: 60,792,240.00 for the plant, 22,282,166.81 for the shape, 198,400,000.00 on the first morning.
Thirteen pages to print and use. A4, with margins wide enough for US Letter.
Chapters 1 to 18 · Conclusion
Thirteen working documents
The position report interrogated, with the four questions and the four refusals. The curve on one page and the level-or-shape test. Marginal cost and the three questions for any spark spread. Dispatch and concentration, with an availability report that says which hours. The plant valuation four ways and the three things to demand in writing. The outage plan read as a valuation, one column per window. The retail hedge proposal. The true short reconciled, with both legs of the forward sale. Two legs, two clocks. Liquidity as a position limit, with the three reliefs and the board minute. The group on four cells. The report that would survive. The one page, and Monday’s four tasks.
A workbook that marks itself. Answer in the yellow cells; the marking fills in as you go.
All parts of the book
Forty questions on the book
Seven on the report and the curve, eight on the plant, seven on the retail book and the true short, seven on cash, five on the group and the report, and six on the three cases below. Numbers are marked to a tolerance, multiple choice to the letter, and the score sheet counts your errors part by part: that is your revision list.
Nothing is hidden. The marking sheet holds every answer, every tolerance, and the reason for each answer.
New material, not a restatement. Three situations the chapters name and never take to a number. Each case is one workbook: the note is the first sheet, the model is the rest, and a checks sheet closes it. Type your own figures in and every finding recomputes.
Chapters 4 and 10 · Case one
The gas leg nobody counts
Chapter 4 concedes that a plant which burns less gas than it hedged is long fuel, and counts none of it. Sized on availability like the power block (ADDED), the gas hedge leaves the company long 18,302,400 MMBtu: exactly the 2,542,000 MWh buy-back at the heat rate, so the idle hours are a short spark spread, not a short power position. A fall of 1.00 in gas (ADDED) costs the year 11,040,960.00; a rise of 1.00 adds 18,914,960.00. The dispatch of Chapter 5 holds only while gas stays between 8.2778 and 9.5278, and if the gas hedge is cleared, the first morning’s 40,400,000 gap closes only if gas rises 1.1313 on the same night.
Chapters 7 and 15 · Case two
Three dates the diary allows
Chapter 7 says the real choice is between two or three dates somebody else’s diary allows, and prices only the two ends. Three windows (ADDED, as hours per row of the curve) cost 1,355,320.00, 4,306,644.00 and 11,796,864.00. At every one of them the Chapter 15 sign reverses: the hours add 8,219,353.19 to 10,915,609.19 to the group instead of costing it 2,682,726.81, because the cheap hours return to the must-run block. Moving the work from the winter window to the spring one is a false saving once deferral raises the chance of a forced outage at the top of the curve by 29.0 percentage points.
Chapters 13 and 14 · Case three
The three reliefs, priced
Chapter 14 names three ways out of the 40,400,000 first-morning gap and prices none. At a commitment fee of 0.35 per cent, initial margin of 5.00 per MWh and a funding cost of 6 per cent (all ADDED): shedding hedge gives up 40,400,000.00 of certainty either way; the facility costs 141,400.00 a year and fails when the move is restated at 60.00; clearing both legs saves 1,260,000.00 a year if the clearer margins the net and costs 1,260,000.00 if it margins the gross. It stops closing the gap only at 30.3810 per MWh of initial margin. The one number to ask the clearer for is the basis, not the rate.
the last sheet of every workbook: the printed figure beside the computed one, OK or CHECK, and the file ends ALL OK
ADDED
a figure the book does not give, used in the cases and marked wherever it appears
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 the book’s own headline. An early draft valued the power
station as a flat block covering its 8,000 available hours and priced that block at the
annual forward of 70.1872, which is the average of all 8,760 — including the 760
cheapest hours, the ones the outage removes. The block should have been priced at
73.6250, the average of the hours it actually covers. On the wrong price the
plant’s apparent option value was 36,650,855.53; corrected, it is
19,599,440, and the missing 17,051,415.53 turns out to be a mispricing rather than
optionality. A book about averages taken over the wrong set of hours had taken an average over the
wrong set of hours, and the correction is in the text rather than quietly removed from it.
Where a shortcut and the full computation disagree, both are shown. The hour-set component is
4,960,000 multiplied by an unrounded 3.43778539, not by the printed 3.4378, and the two differ by
72.47; the flat retail block costs 294,786,301.37 on the unrounded average and 294,786,240.00 on
the printed one. Neither gap is smoothed away, because a reader who can see the gap is a reader
who will not be surprised by it.
Go beyond the companion files
Energy Trading develops the complete framework behind these models, with the assumptions, trade-offs and worked examples tied together.
The workbooks open in Microsoft Excel, LibreOffice Calc, Google Sheets and Numbers; the PDF
prints on A4 or US Letter. 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.
What changed, and when
24 Sep 2026
The four workbooks corrected and extended to 152 checks. Margin and Liquidity now computes the volume sold and the plant’s output instead of typing them; the outage sheet works for any number of hours; the concentration table, the buy-back, the amplitude and the group of Chapter 15 added. Chapter 9’s break-even amplitude is 1.1627x, not 1.1616x; Chapter 6’s starts are 1,059.4292 in all, not 963.4292; Chapter 8’s flat block by row uses the unrounded load. Added: a blank set, thirteen working documents, forty questions that mark themselves, and three cases.
How do you calculate a clean spark spread?Power price less gas divided by efficiency, less carbon: 92.00 less 64.81 less 26.19 gives 1.00 per MWh, yet the plant still earns 32.6M a year off the curve.
How do you price a power plant tolling agreement?From the hours the plant runs: 30,349,019 of dispatch margin on 400 MW supports a toll of 5.37 per kW-month after a 15 per cent haircut, against 9.84 needed.
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.
Financial Planning and AnalysisRevenue three per cent above budget, operating profit sixteen per cent below it, and not one figure misstated.
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.
Trade FinanceSix routes to payment on one 4,200,000 export order cost between 178,040 and 223,268, a spread worth 14.8 per cent of the margin, and a day of buyer credit costs 1,031.76.
Cost AccountingOne factory costed twice on the same 13,440,000 of overhead, and 4,053,091 moves between four product families.
Contract ManagementA bid 5.50 a unit cheaper, signed on paper worth 9.81 a unit more — and 94.68 per cent of it was knowable on the day of signature.
Capital MarketsFour defensible readings of one bond, the cost of an issue under each convention, and the placement discount computed at a constant discount.
Financial RegulationThree ratios pass and the fourth is in breach by 1,956.0 million: the output floor, the liquidity buffer after encumbrance, and the loan split.
Bank ManagementFour defensible readings of one balance sheet, the deposit beta measured two ways, and the liquidity that lasts 3.9 days.
The Real Estate WorkoutFour ways out of one broken loan, discounted to today, and the 58.7 cents at which a payoff beats enforcement.
If this book helped — or didn’t — a few lines on Amazon are worth more than they look: they are what the next reader goes on. Write a review. The workbook stays free either way.