By Jonas Tyrone Lobaton, MSc Quantitative Finance, FMVA®
Founder, Frac CFO · Updated October 24, 2026
Financial modeling in Excel connects business assumptions to projected profit, balance-sheet balances, and cash requirements. A model is reliable only when these statements reconcile and the underlying schedules explain why they change. This hands-on guide develops a fictional three-statement business example, shows reusable Excel formulas, and explains scenarios, cash shortages, and model QA.
In the first month, our sample business sells 100 units for $20,000 revenue and earns $4,300 net income, yet bank cash falls $5,200 to $24,800. The closing balance sheet still reconciles to zero. The difference arises from receivables, inventory, supplier payments, capital expenditure, and a loan principal repayment, not a spreadsheet mistake.
The example is a planning illustration, not a substitute for an audited opening balance sheet, accounting policies, tax calculations, credit agreements, or professional review. The complete 12-month model also tests base, upside, and downside assumptions, with explicit minimum-cash alerts.
TL;DR: build the model, then make it balance
- Profit is not cash: revenue of $20,000 and net income of $4,300 can coexist with a $5,200 cash decline.
- Build supporting schedules for revenue/COGS, accounts receivable (AR), inventory, accounts payable (AP), fixed assets, debt, and equity, not just a profit forecast.
- Cross-link net income to equity and operating cash flow, depreciation to PP&E, principal repayments to debt, and net cash flow to balance-sheet cash.
- Use an explicit pre-funding cash balance to calculate any minimum-cash shortfall. Do not balance the spreadsheet using unexplained debt or equity entries.
- Choose scenario inputs coherently and test both the balance-sheet reconciliation and every individual roll-forward. A mathematically balanced forecast can still omit real-world transactions.
- Use a downloadable SAMPLE to inspect working formulas and a BLANK workbook for a new project; the 12-month figures are fictional.
Table of Contents
- What a financial model measures
- Workbook structure and scenarios
- Worked three-statement example and formulas
- What-If, NPV, XIRR and Solver
- Template versus custom model
- Accounting, scenario and model QA
- Our modeling approach
- Frac CFO services
- FAQs
- Sources
What is a financial model in Excel?
A financial model translates explicit commercial, operating, working-capital, and financing assumptions into financial outcomes. Depending on the decision, the output may be a weekly liquidity forecast, product break-even analysis, budget, valuation, financing analysis, or integrated income statement, balance sheet, and cash-flow forecast.
Not every question requires three statements. A 13-week cash forecast can be better for near-term payroll and supplier decisions. An integrated model becomes useful for internal hiring, growth, inventory, investment and financing choices as well as lender and investor presentations, because it makes the trade-off between profit and liquidity visible.
Start by defining the decision and forecasting horizon. Then use documented actuals and assumptions, not arbitrary revenue growth or a bank balance used as a plug. For example: What happens to cash if collections slow down, margins compress and planned equipment spending is brought forward?
| Model | Typical decision |
|---|---|
| Revenue forecast | Price, volume and mix |
| 13-week cash flow | Immediate collections and payment timing |
| Budget and variance | Performance versus plan |
| Three-statement forecast | Profit, working capital, investment and financing |
| Investment/valuation | Cash-flow returns and risk |
Structure a financial model so every number can be traced
Use separate worksheets for Guide, Opening Balance Sheet, Assumptions, Revenue & Costs, Working Capital, Fixed Assets, Debt & Equity, Income Statement, Cash Flow, Balance Sheet, Dashboard, and QA Checks. Each statement pulls from its schedules. Keep dates, currencies and units consistent, and preserve the historical close used as the opening forecast balance sheet.
The companion example uses yellow-shaded blue-font input cells, black calculations, and green cross-sheet formulas. Never edit the navigation area; keep formulas separate from source values, maintain dated assumptions, and show who approved changes. A formula such as =Assumptions!F7*Assumptions!F8 is easier to audit than a hidden growth factor embedded within a long calculation.
Base, upside and downside scenarios
Set a scenario selector on the Assumptions sheet and use INDEX to return scenario-specific monthly unit growth, DSO, DIO and DPO. Changing a scenario should update the revenue forecast, customer collections, purchases, supplier payments, all three statements, and cash-floor alerts—not simply the income statement.
For example, =INDEX(Assumptions!$F$27:$F$29,Assumptions!$F$21) selects the monthly growth assumption for scenario number 1, 2 or 3. With matching columns, apply the same selector to DSO, DIO and DPO. Scenario analysis changes several coherent assumptions together; sensitivity analysis isolates the effect of one or two selected drivers.
Financial-model formatting and governance
Use a consistent time axis across statements and schedules, a single currency and units convention, copyable formulas, clear forecast start dates, and version control. Put model integrity checks and key assumptions on the Dashboard so a business owner can detect problems before sharing the workbook.
Build a three-statement financial model in Excel: a complete worked example
Start with a reconciled opening balance sheet: cash $30,000, AR $10,000, inventory $8,000, net fixed assets $20,000; offset by AP $8,000, debt $20,000 and equity $40,000. Both sides total $68,000.
Step 1: Forecast revenue and expenses
The first month’s fictional assumptions are 100 units sold at $200, unit COGS $80, cash/recognized payroll $5,000, rent $2,000, depreciation $500, and monthly interest $200. Assume no additional income, tax expense or entity-level tax cash payment in this simplified example.
Revenue = 100 × $200 = $20,000. COGS = 100 × $80 = $8,000. Gross profit $12,000 less payroll $5,000, rent $2,000 and depreciation $500 equals operating income $4,500; less interest $200 gives $4,300 net income.
Revenue = Assumptions!F7*Assumptions!F8
COGS = Assumptions!F7*Assumptions!F9
These show formula relationships using the SAMPLE workbook’s input cells. The actual income statement pulls from the revenue and operating cost schedule rather than typing results into the P&L.
Step 2: Forecast AR, inventory, AP and real cash collection timing
The sample uses a simplified 30-day planning month and the following scenario-specific working-capital drivers: DSO 22.5 days, DIO 37.5 days, DPO 27 days. For a steady-flow business these approximate ending working-capital balances; the business should replace them with detailed collection and purchasing schedules when customers are concentrated, seasonal, or pay in irregular milestones.
ClosingAR = Revenue*DSO/30
CashCollected = OpeningAR+Revenue-ClosingAR
ClosingInventory = COGS*DIO/30
InventoryPurchases = COGS+ClosingInventory-OpeningInventory
ClosingAP = InventoryPurchases*DPO/30
SupplierPaid = OpeningAP+InventoryPurchases-ClosingAP
March illustration: AR rises from $10,000 to $15,000, so customers pay $15,000 in cash against $20,000 earned revenue. Inventory rises from $8,000 to $10,000 after $10,000 purchases and $8,000 COGS. AP rises from $8,000 to $9,000, so supplier cash payments equal $9,000. Those amounts must be reconciled; purchases, COGS and supplier payments are not interchangeable.
Step 3: Link fixed assets, debt and equity
Assume $3,000 capital expenditure, $500 current-month depreciation, $1,000 principal repayment, no new borrowing and no owner distributions or contributions. Net PP&E: $20,000 + $3,000 − $500 = $22,500. Debt: $20,000 − $1,000 = $19,000. Equity: $40,000 + $4,300 profit = $44,300.
In this demonstration, interest is calculated on the opening debt at a 12% annual rate divided by 12: $20,000 × 12%/12 = $200 for month one. A real amortization schedule may need exact payment dates, fees, floating rates and more than one facility. Using opening debt avoids an unsupported circular reference between the month’s financing and interest.
Step 4: Reconcile indirect operating cash flow and pre-funding cash
Net income and actual bank cash movement are different. The example’s operating cash is:
OperatingCashFlow = NetIncome+Depreciation
-(ClosingAR-OpeningAR)
-(ClosingInventory-OpeningInventory)
+(ClosingAP-OpeningAP)
$4,300 + $500 − $5,000 − $2,000 + $1,000 = −$1,200 operating cash flow. Subtract $3,000 capital spending and $1,000 principal repayment for −$5,200 net cash movement. From $30,000 opening cash, the pre-funding ending cash is $24,800. This reconciles to customer collections $15,000 less suppliers $9,000, payroll $5,000, rent $2,000, interest $200, capex $3,000 and principal $1,000.
Step 5: Show that the closing balance sheet balances
| Account | Opening | Closing |
|---|---|---|
| Cash | $30,000 | $24,800 |
| Accounts receivable | $10,000 | $15,000 |
| Inventory | $8,000 | $10,000 |
| Net fixed assets | $20,000 | $22,500 |
| Total assets | $68,000 | $72,300 |
| Accounts payable | $8,000 | $9,000 |
| Debt outstanding | $20,000 | $19,000 |
| Equity including retained profit | $40,000 | $44,300 |
| Total liabilities and equity | $68,000 | $72,300 |
| Balance-sheet reconciliation | $0 | $0 |
Balance-sheet equality is necessary but not sufficient: incorrect journal entries can cancel each other out. Also check that each roll-forward and cash-flow source agrees with its business records.
Step 6: Calculate a minimum cash floor and funding gap
Assume the owner requires $20,000 closing cash. The base-case first month has $24,800 pre-financing ending cash, so there is $0 first-month closing-cash gap. Under a downside collection scenario, the gap may be positive even when the income statement remains profitable.
=MAX(0,MinimumCash-PreFundingClosingCash)
A positive funding gap is an alert, not an automatically funded draw. Actual borrowing requires facility availability, approvals, permitted draw limits, interest and repayment costs. A month-end cash-floor check also does not detect temporary mid-month deficits, so short-term liquidity may require a weekly or daily model.
The 12-month SAMPLE tests both the first cash-floor breach and the maximum monthly shortfall across the horizon. These are conditional outputs under fictional assumptions, not proof that external financing will be available.
Scenario comparison: 12-month modeled liquidity
The table below reproduces the companion SAMPLE workbook’s fictional inputs without assuming new emergency funding. Values are month-end cash results before any discretionary financing response.
| Scenario | Lowest closing cash | Largest cash-floor gap | Interpretation |
|---|---|---|---|
| Base | $23,810 | $0 | No modeled month-end breach |
| Upside | $28,783 | $0 | No modeled month-end breach |
| Downside | −$5,047 | $25,047 | Funding solution or operating intervention required |
Rounded figures; all scenario balances reconcile. A negative pre-funding cash balance is a liquidity warning, not a prediction that a bank will allow a negative account balance. The model does not assess borrowing availability or daily cash timing.
Excel functions and What-If tools worth knowing
Scenarios, Data Tables, Goal Seek and Solver
Use Scenario Manager to compare several sets of explicit assumptions, one- or two-variable Data Tables to isolate sensitivity, and Goal Seek to calculate the input needed for a target result. Solver can optimize objectives subject to operational constraints, such as production volumes or investment-selection limits. They are related features, not interchangeable modeling methods.
The SAMPLE workbook’s scenario selector uses an INDEX formula to pick revenue growth, DSO, DIO and DPO as a group. A downside scenario should not assume revenue falls while receivables and staffing automatically stay unchanged unless that is an intentional modeling decision. Stress-test supplier terms, salary commitments, capex and collection delays together when appropriate.
NPV, XNPV, IRR, XIRR and loan payment formulas
NPV assumes evenly spaced future period-end cash flows. If F5 holds the time-zero investment (negative), G5:J5 holds subsequent period-end cash flows and F2 holds the discount rate per period, the correct calculation is:
=F5+NPV(F2,G5:J5)
Do not place the initial time-zero cost inside the NPV function as though it were an end-of-period amount. For dated irregular cash flows, use =XNPV(AnnualDiscountRate,CashFlows,Dates), with valid corresponding dates and the same base date assumptions. IRR assumes regular periods, while XIRR handles irregular dates; neither resolves unrealistic business assumptions.
For a level-payment loan, Excel’s PMT can estimate installments using a period-consistent rate and number of periods (for example, =-PMT(APR/12,NumberOfMonths,LoanPrincipal) for an ordinary monthly annuity under its assumptions). Loan proceeds and principal repayments affect cash/debt balances, not sales revenue or operating profit. Interest expense affects the income statement.
For official instructions see Microsoft What-If Analysis and Microsoft financial functions. A basic operating forecast does not require Solver, NPV or IRR to function.
Build it yourself, start from a template, or commission a model?
| Route | Best fit | Actual scope |
|---|---|---|
| Learn with the SAMPLE workbook | You want to test your own Excel skills and understand statement mechanics | Fictional, simplified 12-month demonstration—not a complete accounting system |
| Three Statement Financial Model Excel Template | Business needs reusable linked statements and scenarios | Published at $29.99; five-year forecast with supporting schedules, scenario analysis and model checks |
| Custom Financial Model | Owners seeking a done-for-you integrated forecast | Published at $1,250; defined 60-month project with scenarios and a scoped handover |
| Custom Finance Project | Nonstandard workstreams, smaller scope or specialized financial analysis | Feasibility and scope reviewed before a written quote or payment |
The Investor Pitch Model Excel Template is a separate $29.99 product for startup valuation, cap-table, dilution, funding terms and investor returns, not a replacement for operational cash and balance-sheet forecasting. The Spreadsheet Audit & Repair service is appropriate when an existing workbook needs review rather than a new model.
Financial-model mistakes and controls that catch them
A balance sheet that balances is necessary but not enough. Both sides could be wrong from a missed customer invoice or a duplicated cost and still reconcile to zero. Review source completeness and every supporting roll-forward, and never hide an unexplained gap inside a balancing cash, debt or equity figure.
| Model check | Expected condition |
|---|---|
| Historical opening balance sheet | Assets = liabilities + equity before forecasting |
| Forecast balance sheet | Difference = $0 every modeled period |
| Cash flow | Opening cash + total net cash flow = closing cash |
| Receivables | Opening AR + revenue − collections = closing AR (subject to adjustments) |
| Inventory | Opening inventory + purchases − COGS = closing inventory (subject to adjustments) |
| Payables | Opening AP + eligible purchases − payments = closing AP |
| Fixed assets and debt | Each closing balance reconciles to opening plus appropriate movements |
| Equity | Opening equity + earnings + contributions − distributions = closing equity |
| Scenarios, formulas and liquidity | Valid scenario selection, no spreadsheet errors; cash breaches flagged rather than plugged |
Version-control the workbook. Investigate circular references, accidental mixed absolute/relative references, inconsistent time periods, and hard-coded business assumptions embedded in formulas. A fixed constant such as 12 for annual-to-monthly periods is not itself an error; assumptions such as growth, tax rates or customer terms should remain visible editable drivers.
A monthly cash-floor alert is insufficient for businesses with large intra-month payroll or supplier obligations. Use a more granular 13-week cash flow where cash timing is critical. When actual historical data exist, backtest forecasts against prior outcomes and distinguish arithmetic validation from economic model validity.
Our perspective: use the smallest model that answers the real decision
Start with the business question, then add only the schedules that change the conclusion. A pricing question may need contribution margin and break-even; hiring and financing decisions often need an integrated cash and balance-sheet forecast. Document inputs, show a dashboard, and preserve formula integrity so owners can inspect the outcomes without having to trust hidden assumptions.
AI and coding tools can construct, calculate and test Excel financial models when they have access to suitable data and tools. They cannot guarantee source completeness, realistic business assumptions or compliance with entity-specific accounting and lending requirements. The same independent review and source reconciliations should apply regardless of who built the spreadsheet.
How Frac CFO can help with financial modeling
For a reusable off-the-shelf integrated model, start with the Three Statement Financial Model Excel Template ($29.99). For a tailored 60-month, three-statement financial model, see the Custom Financial Model ($1,250) and review the actual project scope and input responsibilities before purchasing.
If the task differs from that fixed-scope package, request a quote through Custom Finance Project or explore One-time Projects. The Launch Kit is separately advertised as a $650 one-time setup service.
Ongoing STARTER, GROWTH, and SCALE arrangements have their own scope and payment options. The STARTER page advertises an annual-payment saving, so twelve monthly fees must not be presented as the confirmed discounted annual price. Support and onboarding are governed by each offering’s published terms.
Choose a ready-to-use template or request a documented forecast with revenue drivers, working-capital schedules, cash-floor alerts and independent integrity checks.
Financial Modeling in Excel – FAQs
Do I need three statements for every financial question?
No. Use a targeted cash, pricing or margin model for a narrow decision. A linked three-statement forecast is valuable for internal growth, capital spending, funding and scenario decisions, not only when a lender or investor requests it.
How can a profitable business have less cash?
Accrual revenue may remain uncollected while inventory, equipment and loan principal require cash outflows. The tutorial’s first month earns $4,300 net income yet loses $5,200 cash because collections and payment timing differ from recognized profit.
Why does the balance sheet fail to balance?
Check opening historical balances, missing receivables/payables, omitted capex/depreciation, incorrect debt movement, distributions or the sign on working-capital changes. Never automatically force cash or equity to make the difference disappear.
Does a positive funding gap mean the bank will provide a loan?
No. The calculated cash-floor shortfall is a planning alert. Actual financing depends on eligibility, facility limits, interest, loan covenants and lender approval. A month-end model may also miss short-lived within-month liquidity deficits.
How can I avoid circular interest formulas?
One simple method estimates the period’s interest using opening debt and a compatible interest rate. Complex revolvers and cash sweeps may require controlled iterative calculations and explicit policies; they should not be solved using unexplained balancing plugs.
What is the difference between scenario and sensitivity analysis?
Scenario analysis changes a coherent set of operating and financing assumptions together. Sensitivity analysis isolates one or two drivers to understand their individual impacts. Both help illuminate risk but cannot establish that underlying assumptions are correct.
Can AI build and test a financial model for me?
Yes, appropriate AI and spreadsheet tools can write formulas, populate data and perform mathematical checks. Business forecasts still require complete source records, calibrated assumptions and independent review for accounting, tax and financing judgments.
Can the model run in Google Sheets?
The core arithmetic and many formulas are portable, but scenario controls, chart formatting, data validation, add-ins and large-model performance can differ. Check the functions and validations after any conversion.
About the author
Jonas Tyrone Lobaton is the founder of Frac CFO. He holds a Master’s degree in Quantitative Finance and the FMVA® certification, with work spanning financial modeling, forecasting, optimization, dashboards, business finance, and spreadsheet automation.
References and further reading
- CFA Institute – Financial Modelling Practical Skills Module
- CFA Institute – Introduction to Financial Statement Modeling
- Microsoft – What-If Analysis
- Microsoft – Financial Functions Reference
- Frac CFO – Revenue Forecast in Excel
- Frac CFO – Build a Balance Sheet in Excel
- Frac CFO – Monte Carlo Cash Flow Simulation
All forecasts and balances in this article are fictional. The example excludes taxes, distributions, asset disposals, transaction fees and entity-specific accounting policies unless explicitly noted.

4 Responses