Mastering the 3-Statement Financial Operating Model

Tour the completed three-statement model, starting with the cover page and quarterly inputs, and explore the vertical schedule structure and variance analysis across the core financials.

Gather P&L and balance sheet actuals, mark blue font for hard codes and gray for actuals, and actualize monthly by copying December to January for the cash flow.

Design a professional cover page with a table of contents and hyperlinks for easy navigation to schedules and the balance sheet, plus model guidelines and error checks.

Enter quarterly inputs and drivers in the inputs tab, using yellow cells with blue font. Forecast pricing, unit growth, costs, and other inputs to flow through the model.

Model headcount compensation in the compensation tab, calculating monthly salaries, bonuses, benefits, and employer payroll taxes using start dates and end dates, annual raise percentages, and index-match lookups.

Navigate the revenue schedule to fetch digital net price and units via index match from inputs, and compute quarterly and monthly revenue by accounting for actualized and prior months.

Populate cost schedule by linking digital and physical units to revenue, calculating COGS per unit from inputs, applying T&E growth, and pulling compensation from compensation tab to complete the P&L.

Derive working capital balances from revenue, dso, cogs, days, and dpo; calculate accounts receivable, inventory, and accounts payable, plus accrue expenses and bonuses with a corkscrew schedule.

Model capital expenditures and depreciation in an asset schedule using Excel, applying straight-line depreciation and a blended tax depreciation over seven years to determine ending balances.

Import the 21% corporate tax rate, adjust earnings for depreciation and tax depreciation, and calculate total, current, and deferred taxes with a Corkscrew schedule.

Build the debt schedule in the 3-statement model using a corkscrew schedule, revolver, and cash sweep. Link beginning and ending cash balances to cash flow and interest calculations.

Track common equity and retained earnings on the corkshrew equity schedule by linking starting balances to endings and incorporating net income with a quarterly-to-monthly adjustment and payout ratio for dividends.

Enable iterative calculations to handle circularity in the debt schedule after completing P&L and cash flow statements; enable via file > options > formulas > enable iterative calculation, then okay.

Link revenue to schedules, calculate COGS, operating expenses, depreciation, interest and taxes, then compute net income and summarize P&L with annual and quarterly views.

Link net income from the P&L to the cash flow statement, add back non-cash items like deferred taxes and depreciation, and incorporate working capital changes to compute cash from operations.

Extract assets, liabilities, and equity from schedules to assemble the balance sheet, verify end-of-period cash balances using the cash flow statement, and compare with the income statement.

Learn how circularity affects cash and revolver in a 3-statement model, and how to manage it with an iterative calculation setting, a circularity breaker toggle, and if statements.

Install error checks across revenue, cost, and working capital schedules; validate balance sheet balances; and trace linking errors with precedence, conditional formatting, and cross-statement validation.

Explore how the outputs page links annual summaries of the three financial statements and supports variance analysis with KPIs and charts.

Practice running scenario exercises in a three-statement model by adjusting revenue and capital structure (equity and debt) to see impacts on income statement, cash flow, and balance sheet.

Learn how deferred revenue in SaaS affects the three financial statements, with a step-by-step model of cash, revenue recognition, and working capital flows.

Master the three-statement financial model by linking the three financial statements to assess a company's health and enable advanced models such as discounted cash flow and merger models.