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.
Download the templates
Two Excel files, no macros, nothing locked. No account, no email address.
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
Measure
Value
Twelve-month average peg vs actual working capital
Average-peg adjustment, best completion month (month 6)
+EUR 4.07m
Locked box vs completion accounts, same deal
EUR 6.56m to the seller
Cause 1: timing of the debt-like items
EUR 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
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.
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.
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.
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.
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. There is no sign-up, no email address and no paid version.
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.