Free Excel templates

A free working capital peg model in Excel, with locked box vs completion accounts

Twelve months of a seasonal manufacturer against five defensible peg definitions, the completion-date sensitivity that turns the choice into a lottery, and the same deal run through a locked box and through completion accounts on identical terms: seven linked sheets across two workbooks, one fictional company, no macros.

The file follows a fictional seasonal manufacturer whose working capital swings from EUR 16.9 million at its trough to EUR 23.9 million at its peak over one year. Five defensible definitions of the target working capital, the peg, produce a EUR 7.00 million spread on the same balance sheet at the same completion date. A companion workbook then prices the same deal through a locked box and through completion accounts on identical commercial terms: a EUR 6.56 million difference, EUR 11.90 million of which is not accounting at all but when the debt-like items were settled. Every one of those numbers is a formula you can move.

Get the template

Two Excel files, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other 129 free models.

Working_Capital_and_the_Peg.xlsx (17 KB) · Locked_Box_or_Completion_Accounts.xlsx (14 KB)

You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.

Need the full deal structure? The LBO Model, Pro Edition adds four debt tranches, covenants, a management ratchet and 31 checks, with no circular reference and no macros. $99 → · Watch the 60-second preview

The book behind this model

This file is one worked case from Closing the Deal. The book explains every assumption, every sheet and the decision the numbers support, chapter by chapter.

Get the book on Amazon → Or the DRM-free PDF edition, $19.99

Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada.

Want every model at once?

The Financial Model Library: all 130 models from this site in one download, organised by theme, plus two modelling books in DRM-free PDF (Financial Modeling from First Principles and Real Estate Financial Modeling).

Get the Library, $79 →

LBO Model, Pro Edition: seven-year buyout model with four debt tranches, covenants, a management ratchet, 31 checks, a blank copy and an 18-page guide. $99 → · See what is inside · Watch the 5-minute demo

The Pro Models Bundle: LBO, Development and Fund models, Pro Editions. $199 instead of $327 → · See what is inside

This free file stays free. Secure checkout by Stripe.

Not on Amazon: 36 paperbacks by Julian R. Sterling on how to read the documents, printed on demand and shipped worldwide. Browse the bookshop →

What is in the files

Blue type on a pale fill is an input; black type is a formula.

Working_Capital_and_the_Peg.xlsx — four sheets

Read me
What the workbook is, where to start, and the modelling notes behind the completion-month cell.
1. The twelve months
Working capital for each of the twelve months, the average, the median, the last month before signing, the same month a year earlier, and the enterprise value and net debt the peg feeds into. Includes the locked box timeline carried over from the second workbook.
2. The five pegs
Each of the five definitions against the actual working capital at completion: the peg, the adjustment, and the resulting equity price, with a note on when each definition is used and why.
3. The completion date
Every peg run against all twelve possible completion months, so a peg that swings with the calendar shows exactly how much of its adjustment is the season rather than the business.
4. Checks
Every figure the book prints set against what the model computes, plus four identities that hold for any twelve months you enter.

Locked_Box_or_Completion_Accounts.xlsx — three sheets

Read me
What the workbook is, where to start, and what changed in the most recent revision.
1. Assumptions
The common terms, then the locked box terms (net debt and working capital at the box date, the ticker rate, months to completion, leakage) and the completion accounts terms (net debt and working capital as determined after signing).
2. Both ways
The same deal priced under both mechanisms side by side, then the difference split between its two causes: when the debt-like items were settled, and where in the season the balance sheet falls.
3. Checks
Every figure the book prints set against what the model computes, plus five identities that hold for any inputs.

What the worked case shows

MeasureValue
Twelve-month average peg vs actual working capitalEUR 19.83m vs EUR 22.4m
Spread across the five peg definitionsEUR 7.00m
Average-peg adjustment, worst completion month (month 12)-EUR 2.93m
Average-peg adjustment, best completion month (month 6)+EUR 4.07m
Locked box vs completion accounts, same dealEUR 6.56m to the seller
Cause 1: timing of the debt-like itemsEUR 11.90m
Cause 2: where the balance sheet falls in the season-EUR 5.50m
Cause 3: ticker less leakage+EUR 0.16m

Read the first four lines together. The twelve-month average peg of EUR 19.83 million against actual working capital of EUR 22.4 million gives an adjustment of about EUR 2.57 million, but that same peg definition would have given -EUR 2.93 million had completion landed in the trough month and +EUR 4.07 million at the peak. That EUR 7 million swing on one fixed peg is the completion date, not the business. The locked box comparison then shows that the biggest single driver of the EUR 6.56 million gap between mechanisms is not the season at all, EUR 11.90 million of it is simply when the debt-like items get settled: before signing, while the seller can still walk away, or after, on the buyer's own draft.

How to use it on your own deal

  1. Open Working_Capital_and_the_Peg.xlsx, sheet 1, and overwrite the blue cells: your own twelve months of working capital, the enterprise value, the net debt and the completion month.
  2. Read sheet 3 before choosing a peg definition. If the adjustment swings by more than a small fraction of the deal on a completion-date shift alone, the business is seasonal enough that a flat average or median is the wrong peg.
  3. Open Locked_Box_or_Completion_Accounts.xlsx, sheet 1, and enter your own net debt and working capital at the box date and at completion, the ticker rate, months to completion and leakage.
  4. Read sheet 2's three causes separately: timing of the debt-like items, season, and ticker less leakage. They do not move together, and on a seasonal business the timing cause is usually the largest.
  5. Keep the Checks sheets: once you change inputs they stop matching the book, which is expected. Use their structure for your own tie-outs.

Questions people ask about it

Is this working capital peg model really free?

Yes. It is a companion file to a book. The file costs nothing. You enter an email address, the download opens at once, and you can leave the reader list in one click.

Does it use macros or circular references?

No macros and no circular reference: every adjustment reads the twelve months of working capital and one peg definition, never its own result. The files recalculate in Excel, LibreOffice or Google Sheets without iterative calculation.

Why does the same peg give a different adjustment depending on the completion month?

The twelve-month average peg does not move, but the actual working capital at completion does, because the business is seasonal. On the same balance sheet, completing in the trough month gives an adjustment of -2.93 million; completing at the peak gives +4.07 million. That 7 million swing is the calendar, not the business, which is the strongest argument for a peg matched to the season rather than a flat average.

Can I use it for a real deal?

You can reuse the structure: the peg definitions, the completion-date sensitivity, and the locked box against completion accounts comparison. Every company, balance sheet and figure in it is fictional, and nothing here is legal or accounting advice.

The rest of the files

These templates are two of the companion files for Closing the Deal: the full set adds the equity bridge, the earn-out workbook, the eighty questions of Appendix A and thirteen working documents. Free, like these.

Open the companion files →

Worked questions from the same book

Short articles that each answer one question with the arithmetic shown, on the same ground as this model.

Other free templates

This template comes from Closing the Deal. The book is on Amazon.

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 template stays free either way.