Financial Modeling

Turn off automatic error checking distractions, set calculation mode deliberately, disable unnecessary auto-formatting, and ensure iterative calculation is off unless the model intentionally needs circularity. Set workbook precision, separators, and links carefully. The goal is predictable calculation, clean formatting, and no accidental circular references or external-link surprises.

Common convention: blue font for hardcoded inputs, black for formulas, green for links to other worksheets, red for external links or important flags, and sometimes purple for macros / special cases. The exact convention can vary by bank, but consistency matters more than the specific palette. Never hide hardcodes inside black formulas.

Core shortcuts include Ctrl+Arrow to move across ranges, Ctrl+Shift+Arrow to select, Ctrl+[ and Ctrl+] to trace precedents / dependents, F2 to edit, Alt shortcuts for ribbon commands, Ctrl+PageUp/PageDown to move tabs, and F5 / Go To Special for blanks, constants, and formulas. Speed comes from navigating without the mouse.

Use Paste Special to control exactly what is pasted. Key options are values, formulas, formats, column widths, transpose, and operations such as add / multiply. In modeling, values are used to break links or freeze outputs, formulas to extend logic, formats to standardize presentation, and transpose for reorienting data.

Useful formats include zero as dash, negatives in parentheses, one decimal for percentages and multiples, x for multiples, turns for leverage, bps for margin changes, and explicit currency / unit labels. Good formatting makes outputs readable without changing values. Avoid formatting that hides real zeros or makes negative numbers ambiguous.

Put assumptions in dedicated input cells and reference them. Use color coding, named or clearly labeled assumption rows, and checks for constants inside formulas. Avoid formulas like revenue x 1.05; instead reference the growth assumption. Hardcodes inside formulas make models hard to audit and easy to break.

Common functions include SUM, AVERAGE, MIN, MAX, IF, IFERROR, ROUND, INDEX/MATCH or XLOOKUP, SUMIF/SUMIFS, COUNTIF/COUNTIFS, CHOOSE, EOMONTH, YEARFRAC, NPV, XNPV, IRR, and XIRR. In banker models, simple transparent formulas are usually better than clever formulas that are hard to audit.

Group tabs logically: cover / index, assumptions, financial statements, operating schedules, debt, valuation, returns, sensitivities, outputs, and backup data. Use consistent tab colors, left-to-right flow, clear checks, and an output dashboard near the front. Keep raw data separate from calculations and avoid circular navigation across many tabs.

Tower layout stacks calculations vertically, often one block per schedule, with time across columns. Matrix layout uses grids with scenarios, cases, or operating segments across rows and columns. Tower layout is easier to audit for standard models; matrix layout is powerful for multi-scenario or segment-heavy models but requires tighter integrity checks.

Matrix integrity means every row, column, scenario, and time period in a grid follows the same logic and references. It is critical because one broken formula in a matrix can silently corrupt a whole sensitivity or segment model. Use consistent formulas, checksums, row / column totals, and formula-map audits.

Choose the horizon based on business maturity, visibility, investment cycle, and valuation method. DCFs often use 5-10 years until steady state. LBOs often use the sponsor hold period, usually 5 years. Infrastructure or project-finance models may need longer. The forecast should be long enough to reach normalized margins, capex, and working capital.

Use a consistent sign convention and label it clearly. Common banking convention shows revenue and cash inflows positive, expenses positive in P&L rows but subtracted in formulas, and cash outflows negative in cash-flow schedules. Debt balances are usually positive, repayments negative in cash flow. Consistency matters more than the specific convention.

Group assumptions by topic: transaction, operating forecast, margins, working capital, capex, tax, debt, valuation, scenarios, and outputs. Use clear units, sources, dates, and color coding. Keep assumptions close enough to the model to audit but centralized enough to run scenarios cleanly. Avoid duplicate assumptions in multiple tabs.

Centralized assumptions are best for key drivers and scenario control. Threaded assumptions, placed near calculations, can be clearer for detailed schedules where the assumption only affects one block. Use centralized inputs for high-level cases and outputs; use threaded assumptions sparingly when proximity improves auditability. Avoid having the same driver in both places.

The three-section layout separates inputs, calculations, and outputs. Inputs contain assumptions, calculations process them, and outputs show summary tables / charts. It is useful for complex models because users can change assumptions without touching formulas and reviewers can audit logic separately from presentation. It also reduces accidental overwrites.

Build historical financials first, then income statement forecast, supporting schedules, working capital, PP&E / D&A, debt and interest, cash flow statement, balance sheet, and checks. The balance sheet should be last because it depends on many schedules. Finish with error checks, debt / cash plug, and sensitivity outputs.

In a first-pass model, leave out complex items such as detailed interest, unusual gains / losses, minority interest, pensions, detailed tax attributes, and non-recurring items. Build a clean operating forecast first: revenue, COGS, SG&A, EBITDA, D&A, EBIT, tax, and net income. Add complexity after the model balances.

Net income flows into retained earnings. The standard formula is: ending retained earnings = beginning retained earnings + net income - dividends +/- other direct equity movements. This links the income statement to the balance sheet. If retained earnings are not linked correctly, the balance sheet will not balance.

Start with net income. Add back non-cash expenses such as D&A and impairments. Adjust for changes in working capital. Add investing cash flows such as capex, acquisitions, and asset sales. Add financing cash flows such as debt issuance, debt repayment, dividends, and equity issuance. Ending cash links back to the balance sheet.

A plug is a line that balances the model when another variable is unknown. Common plugs are cash, revolver, or equity funding. Plugs should reflect business logic, not hide errors. In a debt model, excess cash may repay debt; if cash is short, the revolver draws. A balance-sheet imbalance should never be fixed with a fake plug.

If all income statement flows, working-capital changes, investing flows, financing flows, and equity movements are linked correctly, the cash flow statement calculates the exact change in cash needed to reconcile the balance sheet. Ending cash becomes the balancing asset. If cash is correct and the balance sheet still does not balance, another asset, liability, or equity roll-forward is wrong.

Use pre-tax income multiplied by an effective tax rate for a simple model. For more detail, separate cash taxes and book taxes, NOL usage, deferred taxes, non-deductible expenses, and jurisdictional mix. Ensure taxes do not become negative without logic. In valuation models, cash taxes matter more than accounting tax expense.

Simple method: forecast D&A as a percentage of revenue, percentage of PP&E, or in line with historical trend. Better method: use a PP&E schedule where depreciation comes from existing asset base plus new capex by useful life. Link D&A to the income statement, add it back in cash flow, and subtract it from PP&E / intangibles on the balance sheet.

Structure it from revenue to net income: revenue, growth, COGS, gross profit, operating expenses, EBITDA, D&A, EBIT, interest income / expense, pre-tax income, taxes, net income, and EPS if relevant. Include margin rows and growth rows below key lines. Keep historical and forecast periods aligned across columns.

Separate non-recurring items from run-rate operating performance. Include them in reported financials if required, but show adjusted EBITDA / EBIT excluding them when appropriate. Cash non-recurring items still matter for cash flow. Impairments are usually non-cash and added back in cash flow, but they may indicate real value deterioration.

Goodwill usually stays flat unless there is an acquisition, disposal, FX movement, or impairment. Under IFRS / US GAAP, goodwill is not amortized. Finite-lived intangibles are amortized over useful life. Link amortization to the income statement and balance sheet. Do not model goodwill impairment in base-case DCF unless there is a specific accounting scenario.

Common errors include missing retained earnings link, wrong working-capital signs, capex not linked to PP&E, D&A not subtracted from assets, debt repayment not linked to debt balance, cash flow statement not linked to cash, dividends not reducing equity, and circular interest / debt logic. Diagnose by checking whether the imbalance is constant, growing, or starts in a specific year.

Equity issuance increases cash and equity. Buybacks reduce cash and equity. Dividends reduce cash and retained earnings. SBC increases APIC and is added back in cash flow because it is non-cash, but it may dilute shares. Track share count separately for EPS and ownership calculations.

Model pension service cost in operating expense, net interest in finance cost, cash contributions in cash flow, and actuarial remeasurements in OCI / equity if needed. Roll forward the pension liability as opening liability + service cost + interest cost + remeasurements - contributions. Under IAS 19, remeasurements stay in OCI permanently. Under ASC 715, they sit in AOCI and are amortised into P&L, and non-service cost is shown outside operating income. If the target reports under local GAAP, check the measurement basis and the funding status.

The indirect method starts with net income, adjusts for non-cash items, then adjusts for working-capital changes to arrive at cash flow from operations. Then it adds investing cash flows and financing cash flows to calculate net change in cash. Ending cash equals beginning cash plus net change in cash.

Increases in assets usually use cash; decreases in assets provide cash. Increases in liabilities usually provide cash; decreases in liabilities use cash. Non-cash expenses are added back. Investing outflows such as capex reduce cash. Financing inflows such as debt issuance increase cash, while repayments and dividends reduce cash.

D&A reduces net income but is non-cash, so it is added back in CFO under the indirect method. The asset side is reduced through accumulated depreciation or lower PP&E / intangibles. Do not add back D&A twice. If D&A includes amortization of financing fees or intangibles, ensure classification is consistent.

Calculate changes in operating assets and liabilities. Increases in AR, inventory, and other operating assets are cash outflows. Increases in AP, accrued expenses, and deferred revenue are cash inflows. Aggregate them into change in net working capital or show line-by-line. Exclude cash, debt, and non-operating items.

Use the PP&E roll-forward: ending PP&E = beginning PP&E + capex - depreciation - disposals +/- FX / other. Solve for capex or check it against the cash flow statement. Depreciation is non-cash and added back in CFO; capex is a cash outflow in investing cash flow.

Debt issuance is a financing cash inflow. Debt repayment is a financing cash outflow. Interest paid is usually operating under IFRS or US GAAP presentation choices, but debt principal movements are financing. Link issuance and repayment to the debt schedule so ending debt on the balance sheet reconciles.

Cash reconciliation shows beginning cash plus net change in cash equals ending cash. Ending cash must link to the balance sheet. It is mandatory because it proves the CFS explains the movement in cash. If ending cash does not match the balance sheet, the model is not integrated correctly.

Typical order: cash flow from operations, cash flow from investing, cash flow from financing, net change in cash, beginning cash, FX effect on cash if relevant, and ending cash. CFO starts with net income and operating adjustments. CFI includes capex, acquisitions, and disposals. CFF includes debt, equity, and dividends.

Acquisition purchase price paid is usually an investing cash outflow, net of cash acquired depending on presentation. Divestiture proceeds are investing cash inflows. Gains or losses on sale are removed from CFO under the indirect method because the cash proceeds are in investing. Transaction fees may be operating, investing, or financing depending on nature.

FX effects reconcile cash translated at different exchange rates. The usual structure is beginning cash plus CFO, CFI, CFF, plus effect of FX on cash equals ending cash. FX effects are not operating performance. In multi-currency models, separate transaction FX from translation FX and ensure ending cash ties to the balance sheet.

A standard debt schedule has opening debt balance, new issuance, mandatory amortization, optional repayment / cash sweep, PIK interest if applicable, ending debt balance, interest rate, cash interest, PIK interest, and covenant metrics. It rolls debt by tranche and feeds interest expense, financing cash flow, ending debt, and leverage calculations.

Mandatory amortization is scheduled debt repayment required by the loan agreement. Model it as a percentage of original principal or a fixed amount each period, capped by remaining debt balance. It reduces ending debt and appears as financing cash outflow. It is separate from optional repayment or cash sweep.

A bullet tranche has no required principal amortization until maturity. Opening debt remains outstanding unless optional repayments are allowed. Interest is paid each period on the outstanding balance. At maturity, the full principal is repaid or refinanced. In LBO models, bullet debt helps preserve cash flow for deleveraging other tranches.

Optional repayment is discretionary debt paydown using excess cash. Cash sweep is a formula-driven repayment based on available cash or excess cash flow. Model both after mandatory amortization and minimum cash needs. Use MIN formulas to cap repayment at outstanding debt and available cash. Follow the contractual waterfall by tranche.

Interest rate equals base rate plus margin, usually with a floor. The base rate depends on the currency: SOFR (USD), SONIA (GBP) or EURIBOR (EUR). SOFR and SONIA are overnight risk-free rates, used as Term SOFR or compounded in arrears, while EURIBOR is a forward-looking term rate (e.g. 3M/6M). Forecast the base rate from the forward curve, add the margin, apply the floor if needed, and multiply by the average or beginning debt balance. If hedged, split the floating exposure into hedged and unhedged portions. Sensitize base rates, because floating-rate debt affects cash interest and debt capacity.

Calculate interest for each tranche separately using the appropriate balance, rate, day-count / period convention, and cash versus PIK split. Sum cash interest, PIK interest, commitment fees, amortization of financing fees, and other debt costs as needed. Link total interest to the income statement and cash interest to the cash flow statement.

PIK interest is paid in kind by adding interest to the debt balance instead of paying cash. In a PIK-toggle, the borrower may choose cash pay or PIK subject to terms. Model cash interest and PIK interest separately. PIK increases ending debt, reduces near-term cash outflow, and increases future interest burden and leverage.

Model private placement debt as separate tranches, each with its own maturity, coupon, fixed / floating rate, amortization profile and covenant terms. Include them in gross debt and net debt. Check change-of-control, prepayment (including make-whole) and refinancing provisions. In an LBO, existing private placements are often repaid at closing, so the make-whole cost goes into uses of funds. If they stay in place, they may sit beside bank debt and bonds and can affect covenant headroom.

Calculate covenant EBITDA, net debt or senior debt, and leverage ratios each period. Compare them with covenant thresholds and show headroom. Use definitions from the loan agreement, not generic model EBITDA. Include add-backs, lease treatment, cash netting, and cure rights if applicable. Flag breaches with checks.

At the refinancing date, repay existing debt, raise new debt, pay refinancing fees, and update rates, maturities, and amortization. Recognize any prepayment penalties or write-off of unamortized fees if modeled. The cash flow statement shows debt repayment, new issuance, and fees. Interest expense changes from the refinancing date onward.

OID means debt is issued below par. Cash proceeds are lower than face value, but debt principal is repaid at par. In accounting, OID is amortized as additional interest expense using the effective interest method. In models, show gross debt, net proceeds, financing fees / OID, cash interest, and amortization if EPS or book debt matters.

The available-cash bridge starts with beginning cash, adds cash flow available for debt repayment, subtracts required minimum cash, mandatory amortization, and other required uses, then determines cash available for optional repayment. It protects minimum liquidity while using excess cash to pay down debt according to the waterfall.

Calculate available cash after minimum cash and mandatory amortization. Apply it to debt tranches in contractual order using MIN formulas: repayment equals the lesser of available cash and outstanding balance. Reduce available cash after each tranche repayment. Link repayments to ending debt and financing cash flow. Add checks so repayments never exceed debt outstanding.

Use MIN to cap repayment at the lower of available cash and debt outstanding. Example: optional repayment = MIN(available cash, opening debt after mandatory amortization). This prevents negative debt balances and ensures the sweep does not use more cash than available. For multi-tranche waterfalls, update available cash after each tranche.

Step-down sweep provisions reduce required sweep percentage as leverage falls. For example, 75% of excess cash flow above 4.0x leverage, 50% below 4.0x, and 25% below 3.0x. Model leverage ratio, lookup the applicable sweep percentage, then multiply excess cash flow by that percentage, capped by debt outstanding and available cash.

Minimum cash comes first. The model should preserve required operating cash before sweeping debt. Available cash for sweep equals ending cash before sweep minus minimum cash, after mandatory uses. If cash is below minimum, no sweep occurs and the revolver may draw. This prevents debt paydown that would leave the business underfunded.

Use the loan-agreement definition of ECF, not generic free cash flow. Start with EBITDA or cash flow, subtract cash taxes, interest, capex, working capital, permitted investments, mandatory amortization, and other baskets. Apply the required sweep percentage, step-downs, and de minimis thresholds. Cap repayment at eligible debt outstanding.

Pari passu sweep allocates repayment pro rata across eligible tranches rather than sequentially. Calculate each tranche's share of total eligible debt, then multiply total sweep amount by that share, capped by each tranche balance. This preserves equal ranking. Add checks that total repayment equals sweep amount and no tranche goes negative.

A revolver is a committed credit facility used for short-term liquidity needs. In a model, it acts as a cash plug: if cash falls below minimum cash, the revolver draws; if excess cash is available, the revolver repays first. It supports seasonality, working-capital swings, and downside liquidity.

Calculate cash before revolver. If cash before revolver is below minimum cash, draw amount equals MIN(revolver availability, minimum cash - cash before revolver). If cash is above minimum and revolver is outstanding, repay with excess cash capped by revolver balance. Use MAX to prevent negative draws and MIN to cap by availability.

Minimum cash is the operating cash balance the business needs to run. In the model, ending cash should not fall below this level unless there is a deliberate breach case. Cash available for debt repayment is cash above minimum cash. If cash before financing is below minimum, the revolver draws or other funding is needed.

Commitment fees are paid on undrawn revolver capacity. Formula: fee rate x unused commitment x period fraction. Unused commitment equals total revolver commitment minus drawn amount, adjusted for letters of credit if relevant. Commitment fees are usually included in interest / financing cost and reduce cash.

In bid-defense or acquisition financing models, revolver sizing should cover seasonal working capital, integration cash costs, downside underperformance, and minimum liquidity. It is not just a plug for day-one funding. Show peak revolver draw, availability, covenant headroom, and liquidity under downside cases.

Quarterly models should reflect seasonal working-capital swings. Forecast AR, inventory, AP, and cash flow by quarter. If cash falls below minimum during seasonal build, revolver draws; during seasonal release, cash repays revolver. Annual models can miss peak liquidity needs, so quarterly revolver sizing is important.

Beginning balance is simple and avoids circularity. Ending balance reflects repayments but can create circularity if cash sweep depends on interest. Average balance is usually most accurate because debt changes during the period, but may require iteration or a circularity switch. Banker models often use average balance with iteration or beginning balance for simplicity.

Use beginning balance as a proxy, use prior-period ending balance, or calculate interest before optional sweep and base the sweep on cash after interest. Another method is a circularity switch: use average balance when iteration is on and beginning balance when off. The cleanest non-circular approach sacrifices some precision for stability.

Interest income equals cash balance times interest rate, usually on average cash or beginning cash. It appears below EBIT and increases pre-tax income. In debt-heavy models, cash interest income is often minor but should be included if cash balances are material. Avoid circularity if cash balance depends on net income and interest income.

Prorate annual interest by the fraction of the year outstanding. If debt is issued mid-year, use period fraction or half-year convention as a simplification. Formula: debt balance x annual rate x stub fraction. For acquisitions, interest starts at closing, so year-1 interest should match the stub period rather than a full year.

Average debt balance usually equals AVERAGE(beginning debt, ending debt). Multiply by rate and period fraction. Use it only if ending debt is calculated before interest or if the model intentionally allows iteration. Do not average balances from inconsistent periods or include debt that has been refinanced / repaid mid-period without adjustment.

Create a coupon schedule by period. The coupon changes according to bond terms, such as rating step-ups, time-based increases, or leverage triggers. Interest equals bond balance times applicable coupon times period fraction. Add checks for trigger dates and ratings / leverage assumptions. Step-up coupons affect future cash interest and refinancing incentives.

During construction, interest may be capitalized into the asset base instead of expensed. It increases project cost and debt balance / asset value, then is depreciated over time after completion. In cash flow, interest may still be funded by debt or equity. Once operations start, interest is usually expensed normally.

Split debt into hedged and unhedged portions. For the hedged portion, use fixed swap rate plus margin or model floating rate plus swap settlement. For unhedged debt, use floating base rate plus margin. Include hedge maturity, notional amortization, mark-to-market if needed, and cash settlements. Show effective blended interest rate.

A circular reference occurs when a formula depends on its own result, directly or indirectly. Common modeling circulars include interest based on average debt when debt repayment depends on cash after interest, taxes based on interest when debt depends on taxes, or revolver draws based on ending cash. Circulars can be intentional but must be controlled.

In Excel settings, enable iterative calculation and set maximum iterations and maximum change. This allows circular formulas to converge. Use it only when the model intentionally includes circularity, such as interest on average debt with cash sweep. Add a clear circularity switch and warning so users know iteration is required.

A circularity switch lets the model toggle between circular and non-circular logic. Example: if switch is ON, calculate interest on average debt; if OFF, use beginning debt. Build with an IF statement referencing a control cell. This allows auditing without iteration and final output with more accurate circular calculations.

Iteration ON improves accuracy for circular items but can hide errors and slow the workbook. Manual loop resolution using beginning balances or copy-paste values is more stable but less precise. Use iteration only for controlled circulars with checks. For fast interview models, non-circular simplifications are often safer.

Turn iteration off to reveal the circular warning. Use error checking, trace precedents / dependents, and isolate the schedule causing the loop. Check recent formula changes, interest, revolver, tax, and cash sweep blocks. Temporarily replace formulas with hardcodes to locate the loop, then rebuild with a clear switch or non-circular logic.

Use copy-paste-values when a circular calculation is needed only once or when model stability matters more than dynamic updating. Examples include locking a refinancing case, final bid output, or a circular tax / interest loop in a static deliverable. Document it clearly because pasted values will not update when assumptions change.

Separate circulars into controlled modules with switches and checks. Avoid chaining unnecessary loops. Interest circulars belong in the debt schedule; tax shield circulars in tax / debt blocks; WACC circulars in valuation should often be solved with fixed target capital structure instead. Label each circular and provide non-circular fallback logic for audit.

The days method forecasts working-capital accounts based on operating activity. DSO forecasts accounts receivable from revenue, DIO forecasts inventory from COGS, and DPO forecasts accounts payable from COGS or purchases. It ties working capital to business volume and is more operational than simple percentage-of-sales assumptions.

AR = revenue x DSO / days in period. Inventory = COGS x DIO / days. AP = COGS or purchases x DPO / days. Forecast the days metrics based on history, seasonality, and management guidance. Then calculate change in each working-capital account for the cash flow statement.

Use percentage of sales for smaller working-capital lines, limited data, early-stage models, or items not directly tied to COGS / revenue days. The days method is better for AR, inventory, and AP when operational detail matters. Percentage of sales is simpler but can miss changes in collection, inventory, or payment behavior.

Use quarterly revenue, COGS, DSO, DIO, and DPO assumptions that reflect seasonality. Annual averages can hide peak funding needs. Model inventory build, receivable collection, and payable timing by quarter. Link seasonal cash deficits to the revolver and show peak draw, not just year-end debt.

Reverse factoring extends supplier payment terms through a financing provider. Accounting classification can be trade payable or debt-like depending on terms and disclosure. In modeling, separate normal AP from supplier-finance balances, assess if the balance is debt-like in net debt, and normalize DPO if payment terms are artificially stretched.

Customer deposits are cash received before delivery and recorded as contract liabilities / deferred revenue (IFRS 15 / ASC 606) or advance payments. They are favorable working capital but create a future delivery obligation. They are common in project-based businesses such as machinery, engineering and construction. In valuation, determine whether deposits are normal recurring working capital, unusually high at closing, or debt-like because the buyer must fulfill without future cash inflow.

Compare actual closing working capital to the agreed normalized working-capital target. If actual exceeds target, purchase price increases; if below target, purchase price decreases. Define included accounts carefully and exclude cash, debt, tax, and debt-like items unless specified. Use historical monthly data to set the target and avoid seasonality distortion.

Maintenance capex is required to sustain current operations and earnings power. Growth capex expands capacity, launches new products, or enters new markets. For valuation, maintenance capex is deducted from free cash flow because it is needed to keep the business running. Sellers may overstate growth capex to flatter cash conversion.

Use the roll-forward: beginning PP&E + capex - depreciation - disposals +/- FX / other = ending PP&E. Link capex to the cash flow statement, depreciation to the income statement and cash flow add-back, and ending PP&E to the balance sheet. BASE stands for beginning balance, additions, subtractions, ending balance.

Separate existing asset depreciation and new capex depreciation. Existing assets can depreciate based on remaining useful life or historical D&A. New capex is depreciated over useful life, often with half-year convention in the first year. Sum depreciation from each asset vintage or asset pool and link to the income statement and PP&E roll-forward.

Asset disposals reduce gross PP&E and accumulated depreciation / net PP&E. Cash proceeds go to investing cash flow, and gain or loss goes to the income statement. Impairments reduce asset carrying value and hit the income statement, but are non-cash and added back in the cash flow statement. Keep disposal proceeds separate from capex.

Capex as percentage of sales ties investment to business growth and is useful for steady-state forecasts. Hardcoded capex is better for known projects, capacity expansions, or management budgets. A good model often uses hardcoded near-term project capex and percentage-of-sales long-term maintenance / normalized capex.

List major assets or asset classes separately with opening NBV, additions, useful life, depreciation method, disposals, impairments, and ending NBV. This is useful for infrastructure, real estate, manufacturing, and regulated assets. It improves depreciation accuracy but increases complexity, so use it only when asset-level detail matters.

Remove the sold asset from PP&E, record cash proceeds, recognize gain / loss subject to accounting rules, and add a right-of-use asset and lease liability for the leaseback if applicable. Future rent becomes D&A and interest under IFRS 16. In valuation, remember the cash inflow comes with future lease obligations.

A standard DCF tab includes operating forecast, EBIT, tax, NOPAT, D&A, capex, working capital, unlevered free cash flow, discount factors, present value of FCF, terminal value, enterprise value, net debt adjustments, equity value, shares, and implied share price. Put key assumptions and sensitivity outputs clearly near the top or bottom.

Start with EBIT, tax-effect it to get NOPAT, add back D&A, subtract capex, and subtract increase in net working capital. Formula: UFCF = EBIT x (1 - tax rate) + D&A - capex - change in NWC. Use unlevered free cash flow before interest because DCF enterprise value is capital-structure neutral.

WACC = cost of equity x equity weight + after-tax cost of debt x debt weight. Cost of equity often uses CAPM: risk-free rate + beta x equity risk premium. After-tax cost of debt equals pre-tax cost of debt x (1 - tax rate). Use target capital structure, not necessarily current debt / equity if it is not normalized.

Mid-year convention assumes cash flows are received evenly through the year rather than at year-end. Discount factor is 1 / (1 + WACC)^(year - 0.5). This increases present value compared with year-end discounting. Terminal value is still usually discounted from the end of the final forecast year unless specifically adjusted.

Gordon Growth terminal value equals final-year FCF x (1 + g) / (WACC - g). Exit multiple terminal value equals final-year EBITDA or EBIT times exit multiple. Build both side by side, discount each to present value, and compare implied multiples from Gordon Growth against market multiples for sanity.

If valuation date is mid-year, discount near-term cash flows by the exact fraction from valuation date to cash-flow date. Use XNPV or custom discount factors based on dates. For annual models, prorate current-year cash flow and discount stub FCF separately, then discount later years with adjusted exponents.

Track opening NOL balance, taxable income, NOL usage, and ending NOL. Cash taxes equal taxable income after NOL usage times tax rate, subject to jurisdiction limits. In UFCF, use cash taxes rather than simple EBIT tax if NOLs are material. DTA accounting is secondary to cash tax savings in valuation.

Create a two-dimensional data table with enterprise value or share price as output. Rows might be terminal growth or exit multiple; columns are WACC. Link the top-left cell to the output. Use reasonable ranges around the base case and label assumptions clearly. Check that data tables update correctly and do not break circular models.

Start with enterprise value from the DCF. Subtract net debt and debt-like items, add non-operating assets, and adjust for NCI, pensions, leases, associates, trapped cash, and other claims. Equity value equals EV minus claims senior to equity plus assets not captured in operating EV. Then divide by diluted shares.

Stage 1 is explicit high-visibility forecast. Stage 2 is fade period where growth, margins, ROIC, capex, and working capital gradually move toward normalized levels. Stage 3 is terminal value. Use fade formulas that transition from high growth to stable long-term assumptions, avoiding an abrupt jump into terminal year.

A comps sheet includes company list, business description, geography, financials, market data, enterprise value bridge, profitability metrics, growth metrics, leverage, and valuation multiples such as EV/Revenue, EV/EBITDA, EV/EBIT, and P/E. It ends with mean, median, quartiles, selected range, and implied valuation for the target.

Calendarization adjusts financials to the same period. Use quarterly data to align each company to the target's fiscal year or calendar year. Formula often adds relevant quarters from two fiscal years. This matters for NTM / FY+1 multiples when companies have different year-ends and seasonal businesses.

LTM equals latest fiscal year plus current year-to-date period minus prior year comparable year-to-date period. Example: FY 2025 + Q3 2026 YTD - Q3 2025 YTD. This updates annual results for the latest reported quarters. Ensure one-time items and discontinued operations are treated consistently.

Identify outliers based on business mix, growth, margins, leverage, accounting differences, one-time events, negative EBITDA, extreme multiples, and stale estimates. Do not delete inconvenient comps without explanation. Show the full universe, mark excluded companies, and explain why the selected set is more relevant.

Calculate mean, median, minimum, maximum, 25th percentile, and 75th percentile for each multiple. Median is often preferred because it is less affected by outliers. Quartiles help define the valuation range. Select a range based on business similarity, not just mechanical statistics.

Convert market capitalization, debt, cash, and enterprise value into a common currency using current spot rates. Financial statement items should use appropriate average or reported rates, but multiples are generally currency-neutral if numerator and denominator match. For target valuation, convert implied EV or equity value into the desired currency consistently.

Pure-play comps are more directly comparable. Conglomerates require segment analysis, SOTP, or may be excluded if the target segment is not material. Adjust for margin, growth, geography, cyclicality, capital intensity, and revenue model. Do not blindly apply a conglomerate multiple to a pure-play target without a relevance explanation.

Use consensus estimates for revenue, EBITDA, EBIT, and EPS. NTM blends the next four quarters; FY+1 and FY+2 use fiscal-year estimates. Enterprise value usually uses current market data, adjusted for expected balance-sheet changes if needed. Forward multiples are more relevant when historical earnings are distorted or growth is changing.

A precedents sheet includes announcement date, acquirer, target, target description, geography, transaction value, enterprise value, equity value, consideration mix, premium paid, financial metrics, and transaction multiples. It includes source notes and ends with statistical summary and selected valuation range for the target.

Filter by industry, geography, business model, size, date, control versus minority, public versus private, strategic versus sponsor buyer, and data availability. Remove distressed or unusual transactions unless relevant. The goal is not the biggest list; it is the most comparable transaction evidence for buyer behavior and control premiums.

Premium equals offer price per share divided by unaffected share price minus one. Use unaffected price before rumors or leaks, often one day, one week, or one month before announcement. Show multiple reference dates if the stock moved materially. Premium analysis matters for public M&A fairness and bid positioning.

Transaction multiples reflect market conditions at announcement. Hot markets may show higher multiples due to cheap financing and competitive auctions; cold markets may show lower multiples or distressed pricing. Segment precedents by period, note financing environment, and avoid applying old-cycle multiples mechanically to current-market valuation.

Use disclosed values where possible. If EV, EBITDA, or synergies are estimated, flag them clearly and source the assumptions. For private deals, multiples may rely on press reports or broker estimates, so apply lower confidence. Do not mix synergy-adjusted and unadjusted multiples without labeling. Sensitize questionable data points.

A standard LBO tab includes transaction assumptions, sources and uses, operating forecast, debt schedule, cash sweep, exit valuation, sponsor returns, management rollover / option pool, and sensitivities. The core flow is: entry purchase price, financing structure, cash flow debt paydown, exit value, equity proceeds, MOIC, and IRR.

Uses include equity purchase price, debt refinancing, transaction fees, financing fees, minimum cash, and other deal costs. Sources include new debt tranches, sponsor equity, rollover equity, existing cash used, and seller notes if applicable. Total sources must equal total uses. Sponsor equity is often the plug after debt sizing.

Sponsor IRR is based on cash flows to the sponsor: initial equity investment as a negative cash flow and exit equity proceeds plus dividends / recap proceeds as positive cash flows. Use XIRR for dated cash flows or IRR for annual periods. Exit proceeds equal exit enterprise value minus net debt and other claims, adjusted for sponsor ownership.

MOIC equals total cash returned to sponsor divided by sponsor equity invested. It measures multiple of money, not time. IRR annualizes return and is sensitive to timing. A 2.0x MOIC over 3 years has much higher IRR than 2.0x over 7 years. Use both metrics for sponsor returns.

Exit enterprise value equals exit EBITDA times exit multiple. Use the final forecast year or LTM EBITDA at exit. The exit multiple is usually based on entry multiple, comps, cyclicality, and conservatism. Sensitize exit multiple heavily because returns are very sensitive to exit valuation. Avoid relying on multiple expansion as the main value driver.

A dividend recap raises new debt during the hold period and pays proceeds to the sponsor as a dividend. It increases sponsor interim cash proceeds and can boost IRR, but increases leverage and risk. Model new debt issuance, cash dividend to sponsor, updated interest expense, leverage covenants, and exit net debt.

Returns disaggregation explains MOIC drivers. Break equity value creation into EBITDA growth, margin expansion, multiple change, debt paydown / cash generation, dividends / recap, and fees. This shows whether returns come from operational improvement or financial engineering. Sponsors prefer a credible operating story rather than relying on multiple expansion.

Management equity plans allocate a portion of equity upside to management, often with vesting, strike price, sweet equity, or ratchets. Model sponsor ownership and management ownership separately. At exit, allocate equity proceeds through the agreed waterfall. Carried interest is the sponsor GP's share of fund profits and is usually outside the deal-level company model unless fund economics are required.

Model add-ons with purchase price, timing, financing mix, acquired EBITDA, synergies, integration costs, and incremental debt. Add acquired financials from closing date and update debt schedule. Track blended entry multiple, pro-forma leverage, and returns contribution. Add-ons can create value if acquired at lower multiples or if synergies are credible.

A 1D data table flexes one input and shows the impact on one or more outputs. A 2D data table flexes two inputs, one across rows and one across columns, and shows one output. In valuation, 2D tables are common for WACC versus terminal growth or exit multiple versus EBITDA growth.

Link the top-left cell of the table to the valuation output, such as share price. Put one input range across the top, such as WACC, and another down the side, such as terminal growth. Use Data Table with row input and column input cells linked to the model assumptions. Format and check the center case against the model output.

A scenario manager stores sets of assumptions for base, downside, upside, or management cases. In banking models, many users prefer switch cells and CHOOSE formulas because they are more transparent than Excel's built-in Scenario Manager. Use scenarios when multiple assumptions must change together consistently.

Create a scenario switch cell: 1 for downside, 2 for base, 3 for upside. Store assumptions in columns by scenario. Use CHOOSE(switch, downside, base, upside) or INDEX to pull the active assumption. This makes scenarios visible, auditable, and easy to connect to output summaries.

Run sensitivities for key variables around the base case, calculate impact on output, sort by absolute impact, and display horizontal bars from largest to smallest. Tornado charts show which assumptions matter most. Use them for board-friendly communication of value drivers such as WACC, growth, margin, capex, and exit multiple.

Data tables can calculate slowly or inconsistently with iteration. Set calculation to automatic except data tables if needed, force recalculation with F9, and check center cases manually. For circular models, use a circularity switch or paste values for final sensitivities if data tables are unstable. Always sanity-check outputs.

Use RAND or NORM.INV/RAND to generate random inputs, run many rows of simulated assumptions, and calculate outputs for each row. Summarize mean, median, percentile outcomes, and downside cases. Without add-ins it is manual and can be slow, but useful for simple risk ranges. Keep assumptions transparent and avoid false precision.

A football field shows valuation ranges by method as horizontal bars on a common value axis. Typical methods include DCF, trading comps, precedent transactions, LBO, analyst targets, 52-week range, and SOTP if relevant. It should show low / high ranges, selected midpoint, and offer price or current trading reference.

Use method-specific drivers: WACC and terminal assumptions for DCF, quartile multiples for trading comps, precedent transaction ranges for M&A, exit multiple and IRR for LBO, and observed prices for market ranges. Avoid arbitrary ranges. Each bar should be traceable to a sensitivity or selected peer / transaction set.

Add vertical reference lines for current share price, unaffected price, offer price, and maybe analyst target / 52-week high. Premium analysis can be a separate bar or reference range based on precedent premiums. This lets decision-makers see whether the offer sits inside, above, or below valuation indications.

Do not average blindly. Explain why methods diverge: different growth assumptions, market optimism, control premium, cyclicality, margin normalization, or company-specific risk. Recheck inputs and comparability. If divergence is real, present it as a valuation debate and focus on which method best captures the asset's economics.

The most common cause is a broken cash flow statement or retained earnings link. Net income may not flow to retained earnings, cash flow may not link to balance-sheet cash, or working-capital signs may be wrong. Start with cash, retained earnings, debt, PP&E, and working-capital roll-forwards before hunting obscure errors.

A sanity check tests whether outputs make business sense. Examples: margins within realistic range, leverage not negative, cash not below minimum, revenue growth plausible, capex not zero forever, tax rate reasonable, and balance sheet balances. Sanity checks catch errors that formula checks may miss.

A constant imbalance usually means an opening balance-sheet issue or a one-time historical link error. Check starting balance sheet, retained earnings, cash, debt, and equity. If the imbalance is the same amount every year, the forecast mechanics may be fine but the model began out of balance or missed one opening adjustment.

A growing imbalance usually means a flow is missing or has the wrong sign each period. Check net income to retained earnings, D&A to PP&E, capex to PP&E and CFI, debt repayment to debt balance, working-capital changes, and FX / OCI. Identify the first year the imbalance starts, then audit formulas feeding that year.

A stress test pushes assumptions to extreme but plausible levels to see whether the model behaves correctly. Set revenue growth negative, margins compressed, capex high, working capital adverse, or interest rates up. Check that formulas do not break, debt sweeps stop at zero, covenants flag correctly, and valuation / returns remain logical.

F2 edits a formula and highlights references. Ctrl+[ jumps to direct precedents. Ctrl+] jumps to direct dependents. Together they let you trace model logic quickly without the mouse. Use them to audit suspicious outputs, broken links, and unexpected hardcodes.

Go To Special can select constants, formulas, blanks, visible cells, errors, precedents, and dependents. Use it to find hardcodes in formula areas, blanks in calculation blocks, formulas with errors, and inconsistent row logic. It is especially useful before handoff to catch hidden constants and broken formulas.

First identify intentional circulars and switches. Turn iteration off to see whether unexpected circular warnings appear. Test the model with circularity switch off and on. Check convergence, maximum iterations, and output stability. Avoid editing formulas blindly while iteration hides errors. Document any intentional circular references.

Undo immediately if possible. If not, use Find for #REF!, trace dependents, and identify the deleted source row / column. Restore from backup or rebuild links from neighboring formulas. Check named ranges, charts, data tables, and hidden sheets. #REF! cascades often spread through summary tabs and sensitivities.

A beauty save means final cleanup before sending: recalc, check balance checks, set active cell to A1 on each tab, hide unused rows, remove temporary notes, standardize formatting, clear filters, update print areas, break unnecessary external links, save on summary tab, and ensure outputs match the deck. It signals professionalism.

A typical test gives transaction assumptions, historical financials, forecast drivers, entry multiple, debt terms, and exit assumptions. You build sources and uses, operating forecast, debt schedule, cash sweep, exit value, and sponsor returns. Time can range from 30 minutes to several hours. The goal is clean structure, correct mechanics, and sensible outputs.

Know quick percentage changes, margin math, EBITDA from revenue and margin, leverage from debt / EBITDA, interest from rate x debt, tax-effecting, IRR / MOIC approximations, growth compounding, multiple valuation, and shares issued from value / share price. Fast mental checks help catch model errors under time pressure.

Spend roughly 5 minutes reading, 10 on sources and uses / assumptions, 15 on operating forecast, 15 on debt schedule and returns, 10 on sensitivities and checks, and 5 on cleanup. Build simple and robust. Prioritize correct mechanics over fancy formatting. Make sure the balance sheet / cash sweep / returns work before adding detail.

Common fatal bugs: sources and uses not balancing, negative debt after sweep, wrong signs in working capital, interest circular broken, IRR based on wrong cash flows, exit value using wrong EBITDA year, balance sheet imbalance, hardcodes hidden in formulas, and outputs not tying to assumptions. Add checks and keep the model simple.

Explain your assumptions, key drivers, and sensitivities clearly. Start with headline output, then what drives it: entry multiple, leverage, EBITDA growth, margin, cash conversion, exit multiple. Acknowledge limitations and say how you would improve the model with more time. Seniors care about judgment, not just spreadsheet mechanics.