How to Build a Leveraged Buyout (LBO) Model

Worked example results

At the default 8.5x entry and exit multiples, the sponsor invests 48.270 and receives 132.911 at the end of 2030. The resulting sponsor return is 2.75x MOIC, 22.46% annual IRR, and 22.44% XIRR. These are investment-level returns after the modeled management incentive and transaction costs, before fund fees and carried interest.

The Base case meets all modeled mandatory principal payments and the 2.000 minimum cash requirement. It has no covenant exception during the five-year forecast. Its annual IRR exceeds the illustrative 20.0% target by 2.46 percentage points. The target is a reference for this exercise, not a recommended investment hurdle.

1. The fictional investment case

Northstar Industrial Components LLC manufactures precision fastening kits, machined components, and engineered subassemblies for diversified industrial original-equipment manufacturers. It owns its manufacturing facility and a small administrative building. The business has 217 employees in 2025: 172 in production and 45 in corporate functions. The case assumes the LLC has elected corporate income-tax treatment.

The acquisition closes on December 31, 2025. The seller's historical financial statements cover 2023-2025, with a December 31, 2022 opening balance sheet. The buyer then owns the business for up to five modeled years. The default exit occurs at the end of 2030, following that year's operating cash flows and scheduled financing movements.

The 2025 balance sheet includes cash of 3.001870, operating net working capital of 10.382000, net property, plant and equipment of 13.300000, and book equity of 16.683870. The 2025 nonrecurring expense relates to a completed factory consolidation. Its severance and relocation costs were fully paid in 2025; the forecast therefore does not inherit an unpaid restructuring liability.

Production payroll is already included in product cash cost of goods sold. Corporate payroll is already included in selling, general and administrative expense. Reintroducing either payroll amount as another operating expense would double count it. Depreciation is presented below EBITDA, so the product cost and gross-profit measures are cash measures before depreciation.

The case excludes leases, pensions, minority interests, foreign currencies, historical goodwill, and historical acquired intangibles. Those exclusions keep the example focused while retaining the main relationships needed for a conventional industrial buyout.

2. Start with the model in ten minutes

1. 1. Open the workbook and read Summary for the selected scenario, transaction economics, operating outlook, financing, and sponsor returns.
2. 2. Go to Assumptions. The only operating-case selector is F4: enter 1 for Base, 2 for Downside, or 3 for Upside. The selected case changes the active forecast drivers throughout the workbook.
3. 3. Review the acquisition date in F6, entry multiple in F8, exit multiple in F9, and selected exit year in F10. The model's annual timing assumes a year-end acquisition.
4. 4. Review the financing terms, minimum cash, management investment, incentives, and tax policies before interpreting the return. A valuation change alone does not describe an investment case.
5. 5. Read Transaction to see the equity check, purchase accounting, and post-acquisition opening balance sheet. Read Operations and Working Capital to understand how the business produces or consumes cash.
6. 6. Read Debt before relying on the equity return. Inspect unpaid principal, the minimum-cash shortfall, and covenant headroom. A mathematical balance-sheet reconciliation does not establish that a financing plan is viable.
7. 7. Read Returns for the enterprise-to-equity bridge, management participation, sponsor proceeds, and alternative exit years.
8. 8. Inspect Checks. Resolve failed accounting or formula controls. Treat operating or financing warnings according to their labels; do not clear a warning by changing its test formula.
9. 9. Change the case and inspect how operating cash, debt, and equity returns change. Restore Base when reproducing the default example.
10. 10. Save a separately named copy before replacing the synthetic inputs with another company's information.

When using the native sensitivity tables, use Excel's automatic calculation mode with data tables enabled. If the application is set to calculate automatically except for data tables, explicitly recalculate the tables after changes. The validation section records which calculations and checks were actually executed for the delivered file.

3. Workbook map

The physical tab order puts the decision views first, then the supporting calculations and source information. The logical flow is different: source data and assumptions feed the operating and transaction schedules; those schedules feed the financial statements and returns; the summary presents their results.

Historical periods use F for 2023, G for 2024, and H for 2025. Forecast periods use I for 2026 through M for 2030. The 2022 opening balances appear in column E where needed. A row that compares transaction alternatives or exit years can have a different meaning; always read its own header.

4. Modeling conventions and calculation flow

The model keeps inputs distinct from formulas. Blue text identifies editable numeric inputs, with a pale input fill; green text identifies a formula linked to another worksheet; black text identifies a calculation on the same worksheet. Summary uses dark text for presentation outputs. Change a designated input, then allow the formulas to propagate its effect. Historical amounts remain historical when the forecast case changes. Each forecast driver has one active-selection row on Assumptions, so a schedule can link to its chosen input without repeating case logic.

Dollar amounts are in millions. Product volumes are in millions of units and product prices are dollars per unit; multiplying those quantities gives revenue in USD millions. Employee compensation requires its displayed unit to be respected. A percentage is stored as a decimal, so 26% is 0.26. A valuation multiple of 8.5x is stored as 8.5.

Operating costs are positive amounts in their source and build schedules and are subtracted when calculating profit. Historical also records individual capex, repayment, and owner-distribution inputs as positive magnitudes, then subtracts them in its cash-flow subtotals. Statements presents expenses and cash outflows as negative figures; the investor cash-flow schedule also shows payments as negative and receipts as positive. The same sponsor investment is therefore a positive source of transaction funding and a negative sponsor cash flow at inception. Follow the owning schedule's convention when replacing data.

The forecast sequence deliberately avoids circular interest calculations. Interest and commitment fees use opening financing balances. Principal movements and PIK capitalization occur at year-end. Cash taxes therefore can be calculated before the principal waterfall, and the waterfall determines closing debt and cash. Those closing balances become the next year's opening balances. Excel iterative calculation is not required for this design.

This is an annual underwriting model. It describes year-end cash and financing positions. It does not establish that every payroll date, supplier payment, or seasonal inventory build is financed within the year. A live credit decision may require a monthly operating model, a 13-week cash flow, or both.

The formulas below use actual worksheet names and the specified cell locations. When a build links an assumption into a local driver row, an equivalent calculation can use that local row instead of repeating the cross-sheet reference. The purpose of the examples is to make the mathematics and references understandable, not to encourage overwriting a working formula without inspecting its dependencies.

5. Assumptions the control panel

=CHOOSE($F$4,I55,I56,I57)

6. Historical reconstruct the starting point

Historical is the source-data worksheet. Rows 9-23 contain the income statement and earnings bridge. Rows 27-44 contain the balance sheet. Rows 47-65 contain the indirect cash flow statement. Rows 68-84 contain product volumes, unit prices, unit costs, sales, and cash COGS. Rows 87-92 provide staffing and compensation context.

Start by checking revenue and cash COGS against the three product families. Historical H9 is 2025 revenue; H15 is reported EBITDA; H16 is the identified adjustment; and H17 is adjusted EBITDA. The bridge is conceptually:

Adjusted EBITDA = Reported EBITDA + Identified nonrecurring expense

An adjustment does not itself create cash. The 2025 expense was paid before closing, so this case has no related forward cash use. If another target has an unpaid liability, the forecast must include its settlement even if the expense is excluded from normalized earnings.

Historical H27 is closing cash, H33 is net PP&E, H39 is total debt, and H42 is book equity. These values feed the transaction and opening-balance-sheet calculations. They must agree with the historical statements, rather than being independently retyped on the acquisition tab.

Historical cash is rolled forward from the prior year's cash through operating, investing, and financing flows. Equity rolls forward through net income and explicitly specified owner distributions. The historical owner distributions of 2.4, 3.0, and 3.3 are financing cash flows. They are not operating expenses and do not continue automatically under the buyer's ownership.

For another company, preserve a record of the original source statements and add a transparent mapping to the model's lines. Do not change source amounts merely to make a historical reconciliation pass. Resolve differences such as leases, debt fees, accrued interest, or omitted balance-sheet accounts in a documented mapping.

7. Transaction purchase price and sources and uses

Transaction rows 8-14 calculate entry value and the seller's equity consideration. Entry enterprise value is adjusted EBITDA multiplied by the entry multiple. Cash is added and old debt is deducted to derive equity value; the seller working capital adjustment is then applied according to its sign.

Entry enterprise value = Entry adjusted EBITDA × Entry multiple
Seller equity consideration = Enterprise value + Acquired cash - Old debt + Working capital adjustment

At the defaults, 12.000 of adjusted EBITDA multiplied by 8.5x gives enterprise value of 102.000. Adding historical cash of 3.001870 and deducting old debt of 10.000 gives seller equity consideration of 95.001870 before the zero working capital adjustment.

Rows 17-24 show uses. These include the seller equity consideration, repayment of old debt, the cash funded onto the new balance sheet, acquisition fees, and financing fees. Rows 27-32 show sources: new senior debt, new junior debt, acquired cash used in closing, management rollover, and the sponsor's residual contribution.

The complete numerical sources-and-uses table appears in Worked example results. At the default inputs, uses and sources both equal 110.271870, and the sponsor contribution is 48.270000.

The acquired-cash convention is deliberate. Sellers receive credit for the cash in the equity bridge. That cash is then available as a transaction funding source, while the buyer separately funds the desired 2.000 opening operating balance. Omitting either side would change the equity check incorrectly.

The sponsor contribution is an appropriate residual: it is the amount of equity required to fund the proposed transaction. It should not be confused with an unexplained balancing plug in operating cash flow or accounting equity. Transaction row 80 checks whether the proposed inputs lead to an invalid funding structure.

Rows 34-38 calculate contributed common equity, sponsor and management capital percentages, entry debt leverage, and opening net debt. Management's 5.000 rollover is a dollar investment, not a 5% ownership instruction. It represents approximately 9.39% of the 53.270 common capital before the separate management profit participation.

8. Transaction purchase accounting and opening balance sheet

Rows 41-49 construct the simplified purchase-price allocation. The model begins with historical book equity, adds a 2.000 book PP&E write-up and 12.000 of identifiable acquired intangibles, and recognizes a deferred tax liability on those book-only increments. At the 26% assumption, the initial DTL is 3.640.

Opening PPA DTL = (PP&E step-up + Acquired intangibles) × Tax rate
Identifiable net assets = Historical book equity + PP&E step-up + Intangibles - PPA DTL
Goodwill = Seller equity consideration - Identifiable net assets

The default identifiable net assets are 27.043870 and goodwill is 67.958000. This is a residual purchase-accounting calculation, not an independent appraisal of goodwill. For an actual acquisition, the valuation of identifiable assets, liabilities, and tax bases requires transaction-specific work.

Ordinary acquisition fees of 2.040 are expensed at closing and reduce opening retained earnings. They are funded in sources and uses but do not increase goodwill. The historical seller equity is eliminated and replaced with the buyer group's contributed capital. These distinctions are consistent with the separate treatment of acquisition costs and financing costs described in EY's guidance.

Rows 53-76 contain the resulting opening balance sheet. The bridge uses column F for historical balances, G for purchase-accounting adjustments, H for financing and closing adjustments, and I for the opening total. It retains operating receivables, inventory, other current assets, payables, and accruals. Cash becomes 2.000. PP&E includes the book write-up; acquired intangibles and goodwill are added. New debt replaces old debt. Term financing fees are deductions from the associated debt carrying values; the revolver financing fee is a deferred asset while the facility remains available.

Opening paid-in capital equals the sponsor and management investments. Opening retained earnings include the closing expense. The post-acquisition opening balances feed the first forecast year; the 2025 historical balance sheet remains the seller's pre-acquisition statement. A difference between the historical closing position and the buyer's opening position is therefore expected and explained by this transaction bridge.

The model is a stock-style acquisition with book-only PP&E and intangible write-ups. It assumes no tax basis step-up. It does not model bargain-purchase accounting, contingent consideration, purchase-price adjustments after closing, or separate legal-entity consolidations. A transaction outside those assumptions requires a modified opening bridge.

9. Operations forecast the business

Operations builds each product family using volume, price, and cash unit cost. The previous period's volume is multiplied by one plus active volume growth. Price and unit cost are rolled forward in the same way. Product revenue equals volume multiplied by price, and product cash COGS equals volume multiplied by cash cost per unit.

Forecast units = Prior-year units × (1 + Volume growth)
Forecast price = Prior-year price × (1 + Price growth)
Product revenue = Forecast units × Forecast price
Product cash COGS = Forecast units × Forecast cash cost per unit

This structure lets an analyst distinguish demand from pricing and input-cost pressure. A revenue growth assumption alone would conceal those differences. A business can grow revenue while margins deteriorate if unit costs rise faster than prices.

Rows 42-45 consolidate sales, cash COGS, and gross profit. Rows 47-50 build corporate FTE, compensation, payroll, and other corporate expense. Forecast payroll reflects both staffing and compensation changes. Other SG&A grows from the prior period using its own driver. Production labor remains embedded in product cash cost; it is not added a second time.

Row 51 is adjusted EBITDA. Row 53 is the separately identified nonrecurring cash operating expense. Row 54 is reported EBITDA, row 55 is depreciation and amortization from Fixed Assets, and row 56 is EBIT. The Downside includes a first-year cash one-off expense; it reduces reported EBITDA and cash generation even though the adjusted EBITDA measure excludes it.

The Base case assumes product-specific annual volume growth and positive price increases. Its cost-growth assumptions are separately visible. For example, 2026 fastening-kit volume grows 6%, price grows 2%, and cash unit cost grows 1.5%. A reviewer can challenge each assumption without rewriting the schedule.

When reusing the model, replace the product categories with business drivers that match the target. A services company may require billable staff, utilization, and rates; a subscription company may require customer cohorts and churn. The fixed-asset and working-capital consequences must be changed alongside the revenue model.

10. Working Capital translate sales into cash requirements

Working Capital links revenue and cash COGS into rows 8-9. Receivable days, inventory days, and payable days determine the principal operating balances. Other current assets and accrued operating liabilities are percentages of revenue. The day-count denominator is the explicit 365-day convention in Assumptions F38, including for leap years.

Representative 2026 calculations are:

I12 = I8 / 'Assumptions'!$F$38 * I11
I15 = I9 / 'Assumptions'!$F$38 * I14
I18 = I9 / 'Assumptions'!$F$38 * I17
I21 = I8 * I20
I24 = I8 * I23

These calculate accounts receivable, inventory, accounts payable, other current assets, and accruals respectively. The model uses cash COGS as the common denominator for inventory and payable days. In a real manufacturing model, a more detailed purchasing or materials-cost denominator may be appropriate for payables.

Row 26 calculates operating net working capital:

Operating NWC = Receivables + Inventory + Other current assets - Payables - Operating accruals

Row 27 is the increase in NWC. Rows 29-33 show movements in the individual accounts. Row 35 expresses their combined cash impact, which is the negative of the NWC increase. The 2026 change compares the new forecast balances with the acquired operating balances, which are retained from the closing historical statements.

Higher sales can consume cash even when customer and supplier terms are unchanged. Higher collection or inventory days consume additional cash; higher payable days release cash. This is why the Downside combines weaker earnings with less favorable working capital: the model should test whether the financing survives both effects.

A one-dollar historical rounding difference should not be “fixed” by overwriting a source ratio. Source balances were rounded to 0.001 million, so observed days can differ slightly from whole-day targets. The model forecasts from its explicit active assumptions.

11. Fixed Assets investment depreciation and acquisition balances

Fixed Assets rows 8-10 calculate maintenance capex, growth capex, and total capex. Each forecast year's capital expenditure is linked to revenue and its active percentage. The two categories are separate so an analyst can distinguish spending needed to maintain the business from spending intended to support growth.

Rows 14-16 depreciate the acquired legacy PP&E over its assumed remaining life, subject to the remaining carrying value. Rows 18-21 roll forward new forecast capex and its depreciation. The new-capex calculation uses a 50% first-year depreciation fraction and a seven-year life by default. Each year's additions are a distinct cohort, so depreciation from earlier additions continues in later years.

Closing net assets = Opening net assets + Capital additions - Depreciation
First-year depreciation on a new cohort = Cohort capex / Useful life × First-year fraction

The half-year convention is a stated modeling policy. It approximates additions occurring throughout the year. It is different from the year-end financing convention and does not imply that tax depreciation follows a particular statutory rule. Rows 44-48 hold the individual annual capex cohorts. A cohort receives the first-year fraction of annual depreciation when acquired, full annual depreciation in the intervening years, and the remaining fraction in its final stub year. This final stub prevents the model from losing half a year's depreciation when the useful life is shortened or the forecast is extended.

Rows 23-25 depreciate the PP&E purchase-accounting step-up over five years. Row 27 combines legacy, new-capex, and step-up PP&E. Rows 29-31 amortize acquired intangibles over ten years. Row 33 totals book depreciation and amortization; row 34 identifies depreciation deductible under the model's tax assumptions; row 35 isolates the acquisition-created book-only depreciation and amortization.

Goodwill in row 37 remains constant during the forecast. The case assumes no goodwill impairment and no goodwill amortization election. This is an underwriting assumption; the workbook is not an impairment-testing model. The acquired intangible value, useful life, and tax basis must be reassessed when using another target.

The model's cash flow statement deducts capital expenditure and adds back noncash depreciation. It must not deduct both capex and depreciation as cash outflows. Similarly, the PPA step-up is a closing accounting adjustment, not additional capex paid again during 2026.

12. Debt interest required payments and cash allocation

Debt is the central financing schedule. It combines operating cash generation, financing costs, tax payments, contractual principal requirements, limited borrowing capacity, and voluntary cash sweeps. Read this sheet from opening balances through cash allocation to closing balances before reviewing its accounting fee schedules.

Senior cash interest = Opening senior principal × Senior interest rate
Junior PIK interest = Opening junior principal × Junior PIK rate
Undrawn fee = Opening unused revolver commitment × Commitment fee rate

I37 = I10 + I9 - I25 - I36
I38 = MAX(0,I11 + I33 - I37)
I39 = IF(I7<'Assumptions'!$F$22,MAX(0,'Assumptions'!$F$17-I15),0)
I40 = MIN(I38,I39)
I41 = MAX(0,I37 + I40 - I11)

13. Debt financing fees and carrying values

Rows 83-86 roll forward senior financing costs; rows 88-91 do the same for junior financing costs. Each schedule separates opening unamortized cost, scheduled amortization, early-repayment write-off, and closing cost. Rows 93-95 track the revolver financing-fee asset.

The training policy uses straight-line term-fee amortization over the stated maturity. This is a transparent underwriting approximation. US GAAP generally uses the effective-interest method; another method requires an assessment that the difference is not material. The guide does not assert that this example's approximation is an accounting policy suitable for every real loan.

Regular contractual senior amortization is distinguished from an early voluntary repayment. A partial early prepayment writes off a proportional share of remaining fees after scheduled amortization. When a term loan is fully retired, its remaining fee balance becomes zero. This prevents a financing-cost deduction from remaining on the balance sheet after the related debt has disappeared.

The revolver's fee is associated with access to the facility. It remains a deferred asset while that facility remains available, even if drawings are zero, and amortizes over the facility's life. Repaying a revolver drawing does not by itself terminate the commitment.

Row 97 totals amortization and write-off expense. Row 98 combines cash financing expense, PIK, and financing-cost expense to produce book financing expense. Row 99 shows net debt carrying value for financial-statement purposes.

Use gross contractual principal for debt repayment and the exit debt bridge. Net carrying value is an accounting presentation. Subtracting net carrying value at exit would incorrectly give shareholders the benefit of unamortized debt fees even though lenders still receive principal.

14. Taxes cash tax losses and deferred tax

Taxes starts from reported EBITDA, including any nonrecurring cash operating expense. Row 9 deducts tax depreciation. Rows 10-11 deduct cash financing expense and PIK interest under the case assumptions. Row 12 is tax profit before use of loss carryforwards.

The model assumes cash and PIK interest are fully deductible. Financing-fee amortization and write-offs are never deductible in this example. That treatment is an explicit conservative permanent-difference assumption; it is not a statement of the US tax treatment of a particular financing instrument. The forecast contains no statutory interest-deduction cap, ownership-change limitation, tax-credit limitation, or jurisdiction-specific depreciation rule.

Rows 14-17 track tax losses. Opening available losses can offset positive tax profit without an annual utilization limit. A tax loss generated in the year increases the carryforward. The simplified model does not recognize a balance-sheet DTA for those losses. Its tax reconciliation explicitly adjusts for the unrecognized tax benefit when losses arise or are used. This is a scope convention, not a conclusion about the appropriate valuation allowance for an actual target, particularly where acquisition DTLs exist. The schedule is an illustrative tax model, not a complete GAAP tax provision.

Losses used = MIN(Opening losses, MAX(0, Tax profit before losses))
New losses = MAX(0, -Tax profit before losses)
Closing losses = Opening losses - Losses used + New losses
Cash tax = MAX(0, Tax profit before losses - Losses used) × Tax rate

Rows 23-25 roll forward the acquisition-related DTL. The initial liability equals 26% of the book-only PP&E and acquired intangible increments. The liability releases as those increments are depreciated or amortized for book purposes. The underlying tax bases remain unchanged in this modeled stock transaction.

Rows 27-29 calculate current tax, the deferred tax movement, and total book tax expense. Under the specified policy:

Book tax expense = Cash tax - PPA deferred tax liability release

The deferred release increases book net income relative to cash tax, but it does not reduce the current cash tax payment. The cash flow statement therefore subtracts that noncash benefit when reconciling net income to cash from operations.

Rows 31-36 reconcile modeled book tax to pretax book income multiplied by the assumed 26% rate. Row 33 adds back financing-fee expense multiplied by that rate because those fees are permanently nondeductible under the model policy. Row 34 equals new losses less utilized losses, multiplied by the rate, and accounts for the omitted loss DTA. An effective tax rate that differs from 26% can therefore be explained, rather than overwritten with the assumed rate.

For a live target, replace these simplified policies with the actual tax structure and attributes. In particular, do not assume that a seller's losses remain available, that PIK is deductible on the modeled timing, or that an acquired book write-up creates tax amortization without investigating the transaction.

15. Statements connect the three financial statements

Statements presents the complete historical and forecast financial picture. The income statement is in rows 8-25, the balance sheet in rows 28-50, and the cash flow statement in rows 53-71. Forecast rows link to the owning schedules so that the same financial amount is not calculated independently in several places.

Revenue and operating costs come from Operations. Book depreciation and amortization come from Fixed Assets. Cash financing expense, PIK, and financing-fee expense come from Debt. Current and deferred tax come from Taxes. Net income is the resulting earnings after those items.

The balance sheet links cash and principal to Debt, operating accounts to Working Capital, asset carrying values to Fixed Assets, and the DTL to Taxes. Common paid-in capital begins with the acquisition investors' contributions. Retained earnings begins with the closing transaction expense and accumulates forecast net income. There are no post-closing sponsor injections or shareholder dividends in the detailed base structure.

The indirect cash flow statement begins with net income. It adds back depreciation and amortization, PIK interest, and financing-fee expense because those items reduced profit without a current operating cash payment. It reverses the noncash DTL benefit and includes the working-capital cash movement. Capital expenditure appears in investing cash flow. Actual debt drawings and repayments appear in financing cash flow.

Closing cash = Opening cash + Operating cash flow + Investing cash flow + Financing cash flow
Closing retained earnings = Opening retained earnings + Net income - Dividends
Assets = Liabilities + Equity

Three statements can balance even when an investment is unfunded. For example, a negative calculated cash balance can preserve the accounting identity. That is why the model separately tests liquidity, required principal service, and facility capacity. The cash reconciliation and the financing-viability tests answer different questions.

The financial statements are consolidated underwriting statements. The model does not allocate every balance among acquisition holding companies and operating subsidiaries, calculate every current-versus-noncurrent presentation, or create statutory financial statements for individual legal entities.

16. Returns exit value management participation and sponsor economics

Returns evaluates each modeled year as a possible exit, with the selected exit controlled by Assumptions F10. The default is year five. Each column uses that year's adjusted EBITDA, year-end debt, and year-end cash following all modeled operating and financing activity.

Rows 9-17 construct enterprise and equity value. Exit enterprise value is adjusted EBITDA multiplied by the exit multiple. Selling costs are a percentage of exit enterprise value. Gross debt principal is repaid, and the modeled cash balance is added. Equity proceeds are floored at zero, with any remaining lender shortfall shown separately.

The valuation method requires positive adjusted EBITDA. If exit EBITDA is zero or negative, the model suppresses sponsor returns and labels the detailed exit VALUATION REQUIRED, unless an invalid input or funding failure takes precedence. The enterprise-value floor prevents a negative mechanical valuation, but it does not establish a credible alternative value for a loss-making company. Such a case needs a different valuation approach before an investment return can be assessed.

Exit enterprise value = Exit adjusted EBITDA × Exit multiple
Raw equity proceeds = Exit enterprise value - Selling costs - Gross debt + Cash
Distributable common proceeds = MAX(0, Raw equity proceeds)

The closing cash convention gives the seller group credit for all cash. For another transaction, revise this bridge if an agreed minimum cash amount is included in enterprise value or excluded from seller proceeds. Apply the same economic convention consistently when comparing purchase and exit values.

Capital returned = MIN(Common proceeds, Common capital invested)
Common profit = MAX(0, Common proceeds - Common capital invested)
Management incentive = Common profit × Management profit share
Sponsor proceeds = Sponsor capital percentage × (Capital returned + Common profit - Management incentive)

Sponsor MOIC = Sponsor proceeds / Sponsor capital invested
Annual IRR with one exit distribution = Sponsor MOIC^(1 / Hold years) - 1
=IRR(AnnualSponsorCashFlows)
=XIRR(SponsorCashFlows,ActualCashFlowDates)

17. Sensitivity tables test entry and exit valuation together

Assumptions contains two native two-variable Excel What-If Data Tables. The IRR table occupies I8:N13 and the MOIC table I18:N23. They vary the actual entry-multiple input F8 and exit-multiple input F9, so each combination flows through the transaction, financing, accounting, and return logic.

The corner of each table links to the relevant selected return: I8 links to Returns F64 and I18 links to Returns F63. Horizontal headers J8:N8 and J18:N18 are exit multiples. Vertical headers I9:I13 and I19:I23 are entry multiples. Each axis tests 7.5x, 8.0x, 8.5x, 9.0x, and 9.5x. In Excel's Data Table dialog, the row input cell is F9, because the top row varies exit valuation; the column input cell is F8, because the first column varies entry valuation. The active operating case and selected exit year apply to both tables.

These are different from an ordinary Excel structured table or a grid of independently typed outputs. Changing the entry multiple changes the acquisition enterprise value, seller consideration, acquisition fee, required sponsor capital, capital percentages, and purchase-accounting residual. The table should calculate the consequences through the model, not approximate them by changing the denominator of a finished return.

Changing the exit multiple affects sale enterprise value, selling costs, common proceeds, and the management incentive. Debt principal comes from the selected operating case. A valuation sensitivity is therefore conditional on the active business forecast; it does not replace a Downside operating case.

Use the tables to understand how much of the proposed return depends on paying less, exiting at a higher multiple, or improving the business. Test a small number of intersections manually by changing F8 and F9, recalculating, comparing the selected return, and restoring the original inputs. The final validation record states the intersections tested.

18. Quick Screen a compact preliminary underwriting model

Quick Screen provides a second way to practice the investment problem. It uses top-level revenue growth and EBITDA margin assumptions rather than reconstructing each product family and corporate cost category. Its active growth assumptions are on Assumptions row 164; its margin assumptions are on row 169.

The screen links initial funding from Transaction, then builds its own compact forecast of earnings, cash generation, debt, and exit returns. Sharing closing funding keeps the comparison consistent; the forecast does not link to the detailed model's sponsor result. The left panel holds linked starting facts in F; forecast row labels are in H and forecast values in I:M.

Rows 11-18 forecast revenue, adjusted and reported EBITDA, depreciation, capex, operating NWC, and its change. Depreciation remains a fixed percentage of revenue, using 2025 depreciation divided by revenue. NWC also remains at its 2025 revenue percentage. Capex uses the active maintenance and growth percentages shared with the detailed case. Consequently, the screen does not reproduce the detailed forecast's changing receivable, inventory, and payable days or its depreciation cohorts.

Rows 20-26 calculate financing costs and cash tax using a single blended debt pool. The cash interest rate is weighted by the original senior and junior principal shares; the senior component changes with the active reference rate. The PIK rate is the junior rate weighted by the original junior share of debt. Those weights remain fixed even as the blended pool is repaid. The screen also charges the unused commitment fee on the revolver commitment while the facility is available, but it does not draw that facility.

The simplified cash tax is the tax rate multiplied by positive reported EBITDA less depreciation, cash financing cost, and PIK. There is no loss carryforward. Rows 28-35 then calculate cash before principal, apply a full excess-cash sweep to the blended debt pool, and retain minimum cash where the available cash permits it. The core 2026 formulas are:

I11 = $F$8 * (1 + I8)
I12 = I11 * I9
I26 = MAX(0,I25) * $F$24
I29 = I28 + I14 - I18 - I16 - I23 - I26
I31 = MIN(I20 + I24,MAX(0,I29 - $F$19))
I32 = I20 + I24 - I31
I33 = I29 - I31

Rows 38-47 apply the exit valuation, deduct gross debt and selling costs, add cash, calculate the same profit-only management participation, and derive sponsor MOIC and annual IRR. A cumulative cash-floor gap or invalid input makes the screen's return unavailable, as does nonpositive exit adjusted EBITDA. It does not manufacture additional equity or assume unlimited borrowing to close a deficit.

The screen omits revolver drawings, contractual amortization and maturity balloons, the detailed repayment priority, purchase accounting, financing-fee amortization, NOL usage, and the detailed covenant tests. Its debt repayment is a voluntary full cash sweep of one pool. It therefore cannot establish that a proposed real capital structure meets its contractual obligations. Use Debt for that assessment.

Rows 67-71 compare selected-exit revenue, adjusted EBITDA, gross debt, sponsor MOIC, and sponsor annual IRR: I shows the screen, K shows the detailed result, and M shows screen less detailed. The results are expected to differ because of the independent revenue and margin forecast, cash-conversion assumptions, blended financing, and simplified tax. Explain those drivers before adjusting the screen. Forcing its return to match by pasting a detailed output would remove its teaching value.

Use this worksheet for early discussion and purchase-price framing. When adapting it, check the assumed working-capital and capex ratios against the target's capital intensity and seasonality. A compact model remains useful when the analyst can explain both its assumptions and the omitted detail.

19. Summary present the decision clearly

Summary consolidates the transaction, operating forecast, financing, and selected exit results. Its role is to help a reader understand the investment without rebuilding calculations on the front page. A summary number should be traceable to its owning schedule.

The transaction panel is in F8:F15. Selected MOIC, annual IRR, XIRR, sponsor proceeds, and execution status are in F17:F21. F22 shows annual IRR less the target in Assumptions F39, in percentage points. The operating and financing trajectory is in rows 26-37, and the chart compares adjusted EBITDA with gross debt through the hold. The first debt point is the acquisition financing, rather than the seller's old debt balance, so the chart shows the sponsor's actual deleveraging starting point.

Start with the purchase multiple, initial debt, sponsor equity check, and holding period. Then inspect revenue and EBITDA growth, debt reduction, cash accumulation, exit value, and management-adjusted sponsor proceeds. Read financing and covenant status alongside the return. A strong output presentation lets the investment team identify why the result is attractive and what conditions would cause it to fail.

When preparing an investment committee exhibit, retain the selected case, valuation assumptions, financing assumptions, and any funding or covenant warning. A pasted IRR without those assumptions is not a complete underwriting conclusion. The 20% sponsor target is a training benchmark, not a recommendation or an indication of the firm's actual required return.

20. Checks and Read Me review without changing the business logic

Checks is a terminal review worksheet. It reads the source data and completed schedules and independently tests their consistency. Other business calculations do not depend on a global PASS flag. This prevents an audit formula from becoming an invisible source of operating or financial assumptions.

Review historical balance sheets, historical cash flows, sources versus uses, the post-acquisition opening balance sheet, forecast balance sheets, cash roll-forwards, debt roll-forwards, asset roll-forwards, deferred taxes, equity allocations, and investor return relationships. Also inspect nonnegative debt, facility capacity, remaining financing fees, and agreement between the exit bridge and distribution waterfall.

Rows 9-45 hold reconciliation differences and constraint checks; zero is correct. Rows 50-53 contain exception counts, including input guards. I52:M52 count missing selected inputs separately for each forecast year. Rows 55-59 show unpaid principal, the cash-floor gap, the cumulative maximum gap, covenant exceptions, and lender shortfall at exit. I62 is the accounting status and I63 is the financing status. The same one-dollar monetary tolerance governs reconciliation flags; ratio-identity tests use the same numerical threshold as a decimal, not a dollar amount.

An accounting check can pass while a covenant fails. A funding gap can exist without a spreadsheet error. Conversely, a financially attractive scenario is not usable if it contains a broken cell reference. Keep these categories distinct when diagnosing a result.

Read Me gives an in-workbook reminder of the model's units, timing, formula conventions, and reuse process. It is a convenient starting point for someone who receives the spreadsheet without the full guide. The guide explains the reasoning; the worksheet notes should remain concise enough to be useful during modeling.

21. Rebuild the model from a blank workbook

1. 1. Establish the acquisition date, historical and forecast periods, financial units, and sign conventions. Create consistent period columns before entering formulas.
2. 2. Enter the 2022 opening balance sheet and 2023-2025 historical source data. Reconcile the historical balance sheets, cash, debt, fixed assets, and equity.
3. 3. Reconcile reported EBITDA to the adjusted earnings base. Document each proposed adjustment and its expected cash consequences.
4. 4. Create the assumptions panel, including one case selector and driver groups with active values above the three case rows.
5. 5. Calculate entry enterprise value and seller equity consideration. Build sources and uses, derive sponsor capital, and calculate investor capital percentages.
6. 6. Build the purchase-price allocation and closing balance sheet. Distinguish consideration, deal expense, term financing costs, and revolver fees.
7. 7. Forecast each product's volume, price, and unit cost. Add corporate staffing, compensation, and other expense to calculate EBITDA.
8. 8. Forecast operating working capital and its cash movements. Match each balance to the correct economic driver and opening balance.
9. 9. Build capital expenditure, depreciation cohorts, acquired intangible amortization, and the book-only PPA depreciation needed by the tax schedule.
10. 10. Calculate opening-balance cash interest, commitment fees, and PIK. Determine taxable profit, loss usage, and cash taxes before calculating principal repayments.
11. 11. Build the required-principal and revolver-draw logic, apply limited cash to required payments, and then apply the permitted sweeps. Expose any unpaid amount.
12. 12. Roll debt fees forward, recognize book financing expense, and complete current and deferred tax expense.
13. 13. Complete the income statement, balance sheet, and indirect cash flow statement. Reconcile cash, assets, debt, and retained earnings for every year.
14. 14. Calculate exit enterprise and equity value for each possible exit year. Allocate capital and profit between sponsor and management, then calculate returns.
15. 15. Build the compact screen, selected-output summary, sensitivity tables, and independent checks. Test changes before finalizing formatting.

Build in stages. If the opening balance sheet does not balance, do not proceed by inserting goodwill or equity merely to force equality. Trace the actual transaction adjustment. If cash does not reconcile, locate the missing cash or noncash item before using a return from the model.

22. Reuse the model for a different company

Begin with a duplicate of the delivered workbook. Change the filename and company identity so the synthetic teaching case cannot be mistaken for the new target. Keep a record of the model version and the date of the information used.

Replace historical amounts in Historical, including opening balances and the supporting product or operating data. Update the transaction date and verify period labels against the new data. The date headers and exit cash-flow dates move with Assumptions F6, but changing that date does not transform the historical source figures into a different year's accounts. This template assumes a December 31 acquisition and full-year forecast periods; a midyear acquisition needs an explicit stub period and revised interest, depreciation, working-capital, and return timing.

Reassess the target's operating model. Three manufacturing product families are appropriate for this case, but a distributor, services firm, or software company has different drivers. If rows are added or removed, extend all totals, downstream links, and checks. Do not retain apparently convenient formulas that no longer represent the business.

Replace the debt terms with the proposed term sheet: commitments, amounts, margins, reference rates, floors if any, fees, maturities, amortization, and sweep priorities. Change the covenant definitions to match the relevant agreement. If interest is intended to reflect average intra-year balances, modify the timing framework deliberately and test its convergence or algebraic solution.

Update the purchase-price bridge and tax structure. Determine which cash, debt-like items, working capital adjustment, leases, pensions, and transaction costs belong in the seller consideration and financing. Reassess asset values and useful lives, tax basis, losses, interest deductions, and financing-cost tax treatment.

Replace the management waterfall with the actual economic terms. Record whether the rollover is common or preferred, whether incentive awards have exercise prices or hurdles, and how later equity contributions affect ownership. The simple profit share in this case should not be treated as a standard term of every management plan.

After adapting the model, run the accounting checks and targeted stress tests again. Reusing formulas does not carry the original validation forward automatically. A new debt tranche, a negative earnings year, a shorter maturity, or an additional forecast year can expose a dependency that the original case never activated.

23. Practice exercises and expected directions

Exercise 1 - Purchase-price discipline. Change entry multiple from 8.5x to 9.0x while leaving exit multiple at 8.5x. Entry enterprise value and acquisition fees rise. The dollar debt amounts remain tied to entry EBITDA and the stated leverage multiples, so sponsor equity rises. Explain the resulting change in sponsor returns and in management's capital percentage.

Exercise 2 - Working capital matters. Add ten days to the active receivable assumption for one forecast year. Identify the increased receivable balance, the cash outflow, reduced debt repayment or higher revolver need, and the effect on the following year's interest. Restore the assumption afterward.

Exercise 3 - Financing cost. Increase the senior spread by one percentage point. Trace cash interest, tax relief under the example's full-deductibility policy, cash available for repayment, remaining debt, and equity proceeds. The after-tax effect is not simply one percentage point multiplied by initial debt for five years because principal changes.

Exercise 4 - PIK is still debt. Increase junior PIK from 4% to 6% without changing cash interest. Current cash interest does not rise from that change, but junior principal compounds faster and the tax calculation changes under the stated policy. Explain why exit equity can fall even though current-year cash interest is unchanged.

Exercise 5 - Maturity without refinancing. Shorten senior maturity to three years. Inspect the principal balloon, available borrowing, actual repayment, unpaid principal, cumulative financing status, and return availability. A later recovery in cash does not erase the fact that the original financing path failed to meet an earlier contractual payment.

Exercise 6 - Management participation. Change the management profit share from 10% to 15%. The company's enterprise value and debt do not change. Management participation in common profit increases and sponsor proceeds decrease, provided the investment creates profit above contributed common capital.

Exercise 7 - Zero-debt case. Set senior and junior entry leverage to zero. Explain the larger equity requirement, lower interest, absence of principal repayments, and cash accumulation. Check that unused facility fees and financing-fee logic behave according to the remaining commitments.

Exercise 8 - Screen versus detailed model. Compare the Quick Screen's revenue and EBITDA path with Operations. Identify which top-level screen assumptions would have to change to resemble the detailed forecast. Do not paste the detailed sponsor return into the screen; explain the differences through business and cash drivers.

24. Scope simplifications and interpretation

The workbook includes the main mechanisms needed to teach a mid-market LBO: a supported historical starting point, an operating forecast, transaction funding, an opening purchase-accounting balance sheet, debt and cash mechanics, taxes, integrated statements, management participation, returns, and sensitivity analysis. Its simplifications are explicit so another analyst can decide what must change for a real transaction.

Annual opening-balance interest and year-end principal movements make the financing calculation non-circular. They also limit the model's ability to measure intra-year borrowing, interest, and repayment timing. The model does not include automatic refinancing, subsequent acquisitions, interim dividends, rescue equity, hedging, or lender enforcement following a covenant breach.

The tax model applies its stated 26% assumption, unrestricted modeled loss utilization, no recognized loss DTA, full deduction of cash and PIK interest, and no deduction for financing-fee expense. Those are training policies. They are not a tax opinion or a complete implementation of US federal and state tax law.

The accounting model uses simplified asset lives, straight-line fee amortization, a book-only purchase allocation, and no goodwill impairment. It omits management share-based compensation expense and legal-entity consolidations. Sponsor returns are before fund-level fees and carry.

The Base result is one set of consequences of the selected inputs. A strong model makes those consequences traceable and reacts correctly to alternative inputs. It does not establish that the forecast will occur. The investment judgment lies in the evidence for the operating assumptions, price, financing resilience, and exit expectations.

References

The references below support the conventional Excel and financial-modeling concepts discussed in the guide. The synthetic company facts and workbook calculations are original training assumptions.

1. 1. Microsoft Support. XIRR function. Dated cash flows and a 365-day convention. https://support.microsoft.com/en-us/excel/functions/xirr-function?nochrome=true
2. 2. Microsoft Support. IRR function. Regular periods, ordered cash flows, and ignored empty cells. https://support.microsoft.com/en-us/excel/functions/irr-function?nochrome=true
3. 3. Training The Street. Onsite Private Equity Training. Debt, cash sweeps, rollover, management incentives, and return modeling. https://trainingthestreet.com/onsite-private-equity-training/
4. 4. Wall Street Prep. Private Equity Masterclass. Integrated LBO, sources and uses, purchase accounting, and screening models. https://www.wallstreetprep.com/self-study-programs/private-equity-masterclass/
5. 5. EY. Financial Reporting Developments: Issuer's accounting for debt and equity financings. September 2026; sections 2.3 and 2.4.3.1. https://www.ey.com/content/dam/ey-unified-site/ey-com/en-us/technical/accountinglink/documents/ey-frd13740-211us-10-08-2026.pdf
6. 6. EY. Financial Reporting Developments: Business combinations. September 2026; sections 6.2 and 7.1.2. https://www.ey.com/content/dam/ey-unified-site/ey-com/en-us/technical/accountinglink/documents/ey-frdbb1616-09-22-2026.pdf
7. 7. PwC. Demystifying deferred tax accounting. Book-tax basis differences and acquisition effects. https://www.pwc.com/us/en/services/tax/library/demystifying-deferred-tax-accounting.html