Modeling Fundamentals

A financial model is a spreadsheet that turns a set of assumptions about a business into forecast financial statements and a decision number: a value, a loan balance, a return. This page covers the general toolkit, then what changes when the target has $1M to $10M of EBITDA and is bought with SBA or search fund money. For the full acquisition build, go to LBO Modeling for Small Deals. Dollar figures here are illustrative unless a source is named.

What a model is for

• Decide. What can we pay? Can the business carry this debt?
• Negotiate. Show a seller or lender what happens if a customer leaves or margins slip.
• Raise money. Investors and lenders will rebuild your numbers; a model they can audit in ten minutes is worth more than a clever one.
• Manage. After closing, the model becomes the budget and the covenant tracker.

Types of models

Best practices

These are conventions, not laws, but a reviewer at a lender or fund will expect most of them.

• Colors. Blue font for typed inputs, black for formulas on the same sheet, green for links to other sheets, red for flags and checks. Pick a legend and put it on the cover tab.
• One formula per row. The formula in the first forecast column is the same in every column to its right. If a row needs a different formula, split it into two rows.
• No hardcodes inside formulas. =B5*1.03 hides the 3% growth rate. Put 3% on the Assumptions tab and reference it.
• One time axis. The same column is the same period on every tab. Put the period dates in one header row and reference it.
• Inputs separate from calculations separate from outputs. Use an Assumptions tab for inputs and a Summary tab for outputs.
• Sign convention. Pick one: costs positive and subtracted, or costs negative and summed. Write it down. Mixed signs cause most silent errors.
• Units. Label every row ($, $000, %, x). Do not mix dollars and thousands on one sheet.
• Checks. Balance sheet balances; cash ties; sources equal uses; debt never negative; a master check cell that adds them up and shows on every tab.
• Scenario switch. One selector cell, with CHOOSE or INDEX pulling base, upside or downside inputs. Never maintain copies of the model.
• Avoid volatile and fragile functions. OFFSET and INDIRECT recalculate constantly and break silently when rows move. Prefer INDEX.
• Circularity. Avoid it when you can. If interest on average balances requires it, use an on/off breaker cell.
• Document. A notes column for sources of every historical number ("2024 tax return, line 21"), and a change log.
• Protect the formulas. Keep a locked clean copy before sending it out.

Useful Excel keys (from Microsoft's list of shortcuts): F4 cycles absolute and relative references; Ctrl+arrow jumps to the edge of a data region, and Ctrl+Shift+arrow selects to it; F2 edits the cell; Alt+= inserts a sum; Ctrl+` toggles showing formulas; Ctrl+1 opens Format Cells; Ctrl+D fills down; Ctrl+Page Down moves to the next sheet; F9 recalculates. More on the Excel page.

Accounting you need before you model

• Accrual accounting. Revenue is recorded when earned and costs when incurred, not when cash moves. A model forecasts the accrual statements and derives cash from them. Many small sellers keep cash-basis books, which the SBA SOP lists among the differences a quality of earnings report must document.
• Matching and depreciation. A long-lived asset is spread over its useful life. Depreciation reduces profit and tax but is not a cash cost, which is why it is added back in cash flow.
• Working capital. Receivables, inventory and payables. Growth consumes cash because customers pay after you spend.
• Debt and interest. Interest is an expense; principal repayment is not. Both reduce cash.
• Equity. Starting equity plus net income less distributions. In a pass-through entity, distributions include owners' tax payments.
• Goodwill. The purchase price above the fair value of identifiable net assets. For tax in an asset purchase it is generally amortized over 15 years (IRS section 197).
• Normalized earnings. Reported earnings adjusted for owner pay above or below market, one-time items, and related-party transactions.

A complete mini three-statement example

Illustrative numbers ($000) for a one-year forecast. Assumptions: revenue grows from 1,000 to 1,100; cost of goods sold is 60% of revenue; operating expenses are 280; depreciation is 50; interest is 8% of the opening debt of 300; tax is 25%; receivables are 15% of revenue; inventory rises 10 and payables rise 10; capex is 60; debt repayment is 30.

Property and equipment moves 400 + 60 capex - 50 depreciation = 410. Equity moves 350 + 64.5 net income = 414.5 (no distributions). Both sides total 794.5, so the check cell reads zero. Free cash flow to the firm for a DCF would be EBITDA 160 less taxes on EBIT (27.5) less capex 60 less working capital investment 15, equal to 57.5.

Building the three-statement model

1. Historicals. Enter three years plus the trailing twelve months, from the tax return and the accountant's statements. Compute growth, margins, and working capital days so you can see what the forecast departs from.
2. Revenue. Customers or units times price, not just a percentage.
3. Costs and EBITDA. Variable costs as a percent of revenue, fixed costs in dollars with inflation.
4. Supporting schedules. Working capital (days), fixed assets (opening + capex - depreciation = closing), debt (opening - repayments = closing), equity (opening + net income - distributions = closing).
5. Income statement. EBITDA less depreciation and amortization, less interest, less tax = net income.
6. Cash flow statement. Net income plus non-cash items, less change in working capital, less capex, plus debt drawn less debt repaid, less distributions.
7. Balance sheet. Cash comes from the end of the cash flow statement. Every other line comes from a schedule. Assets must equal liabilities plus equity; if not, the difference is the thing to hunt, usually a missed line in the cash flow statement or a sign flip.

The links, in one place:

• Net income flows to retained earnings (or member equity) and to the top of the cash flow statement.
• Depreciation lowers net income and fixed assets, and is added back in cash flow. It is the reason cash and income differ.
• Capex raises fixed assets and reduces cash; it is not on the income statement.
• An increase in receivables or inventory reduces cash. An increase in payables raises it.
• Debt repayment reduces cash and the liability; interest is an income statement expense.

DCF in five steps

1. Forecast unlevered free cash flow (UFCF) = EBITDA - cash taxes on operating income - capex - increase in net working capital.
2. Pick a discount rate. WACC = E/(D+E) x cost of equity + D/(D+E) x cost of debt x (1 - tax rate). Cost of equity is often built from a risk-free rate, a market premium, a beta and, for small companies, a size or company-specific premium. Aswath Damodaran (NYU Stern) publishes free datasets of betas, equity risk premiums and cost of capital by industry.
3. Terminal value. Gordon growth: TV = final-year UFCF x (1 + g) / (WACC - g), or an exit multiple on final-year EBITDA. Growth g must be at or below long-run nominal growth of the economy.
4. Discount. Value = sum of UFCF / (1 + WACC)^t + TV / (1 + WACC)^N. Mid-year discounting uses t - 0.5.
5. Bridge to equity. Enterprise value - debt - seller notes + cash = equity value.

Illustrative example ($000): free cash flows of 100, 110, 120, 130, 140; WACC 14%; terminal growth 3%.

The spread (869 to 1,457) is the lesson: two small changes in the discount rate and growth move value by more than 60%. A DCF is a way to organize your assumptions, not to prove a price.

PV of a cash flow   =CF/(1+WACC)^t
NPV (end of year)   =NPV(WACC, CF_year1:CF_year5)
Terminal value      =CF5*(1+g)/(WACC-g)
Dated flows         =XNPV(WACC, values, dates)

NPV in Excel assumes the first cash flow is one period away, so do not include the year-0 investment in its range; add it separately.

Sensitivity analysis and scenarios

• One variable at a time shows which input matters most. Vary revenue growth, margin, the exit multiple, the interest rate and customer loss by a fixed step and rank the effects on the output you care about.
• Two-variable tables (Data, What-If Analysis, Data Table) show interactions, such as entry multiple against exit multiple.
• Scenarios change several inputs together: a downside case combines lower revenue, thinner margin and a higher cost of debt.
• Break-even analysis asks what value of an input makes the output equal the threshold (such as debt service coverage of 1.25x) using Goal Seek.
• Present three numbers, not one: base, downside, and the break-even.

Documentation and handoff

• A cover tab with purpose, version, date, author, color legend and the master check.
• A source note beside every historical number: document, page, line.
• A change log with date, what changed and why.
• A list of known limitations: what the model does not include (for example, no state taxes).
• A clean copy with formulas protected for sending to lenders; keep the working copy separate.

Next, the valuation chapter covers comparable companies, precedent transactions and SDE multiples; the M&A basics chapter covers deal structure and diligence; and the glossary defines the terms used here.

How this changes for a $1M to $10M EBITDA company

• Price comes from multiples first. A seller and a broker talk in multiples of SDE or EBITDA. Use a DCF to test whether the multiple is reasonable, not to set it.
• Historical quality matters more than the forecast. The lender sizes debt on historical earnings. SBA SOP 50 10 8.1 requires the debt service test to be met on historical or adjusted historical earnings, and says projections can no longer be relied on to meet it. Your model has to start from a clean, supportable base year.
• The owner is a cost. The seller's pay is not the cost of running the business. Put a replacement manager in the forecast.
• Discount rates are higher and less precise. A public-company WACC from a database understates risk for a company with one location and ten key customers. Show a range, not a point.
• Concentration is a model input. One customer or one employee can be 20% of value. Build the case where they leave.
• Taxes follow the legal structure. Pass-through entities, asset purchases and goodwill amortization change after-tax cash flow, see the LBO page.
• Public data is thin. There is no EDGAR filing for a private company. Use SEC EDGAR for the public comparables that exist, and tax returns and bank statements for the target.

Practice path

1. Take a small public company 10-K from SEC EDGAR and rebuild its three statements for three years of history, forcing the balance sheet to balance.
2. Forecast it for five years with a scenario switch.
3. Run a DCF with a sensitivity table. Compare it to the market price and write down why they differ.
4. Build the LBO on the LBO page with your own invented small business, then break it with a customer loss.

Formula patterns used throughout a model

Growth                =Prior*(1+growth_rate)
Margin                =Revenue*margin_pct
Per-unit cost         =Units*cost_per_unit
Days-based balance    =Revenue/365*days     (receivables)   or  COGS/365*days  (inventory, payables)
Change in WC          =Current_NWC-Prior_NWC          (cash outflow if positive)
Depreciation (SL)     =Gross_PPE/Useful_life
Tax on pass-through   =MAX(0, Taxable_income*Owner_tax_rate)    (shown as a distribution)
Interest (beginning)  =Beginning_balance*Rate
Interest (average)    =AVERAGE(Beginning,Ending)*Rate   (needs circularity control)
Balance check         =Total_assets-Total_liabilities_and_equity
Coverage ratio        =EBITDA/Debt_service
Exit equity           =Exit_EBITDA*Exit_multiple-Debt+Cash
MOIC                  =Exit_equity/Equity_invested
IRR                   =IRR(equity_cash_flows)

Each of these is one formula per row, copied across columns. If you find yourself writing a different formula in one column, ask whether the row should be split.

Sources

• Microsoft: Keyboard shortcuts in Excel
• SBA SOP 50 10 8.1 (debt service coverage on historical earnings, Appendix 15)
• Aswath Damodaran, NYU Stern: data and valuation resources
• SEC: Accessing EDGAR data (free public filings)