Financial Model Template (Excel and Google Sheets)
By Newtown Analytics · · 5 min read
For smb owners & operators
This financial model template sets out the sheet structure, line items and formulas for a linked income statement, balance sheet and cash flow model that you can build in Excel or Google Sheets in a day, with the checks that keep it honest. We lay everything out in full below, so you do not need to download anything. The logic matches the worked example in the three statement financial model explained. It supports the wider approach in budgeting and forecasting. If you would prefer a model built and reviewed for your business, talk to us.
Workbook structure
| Sheet | Purpose | Contains typed numbers? |
|---|---|---|
| Cover | Title, version, date, author, purpose, key outputs, sheet index | Text only |
| Assumptions | Every driver, in one place, with source and date | Yes (the only place) |
| Historicals | Last two or three years of actuals | Yes (actuals) |
| Revenue | Drivers to revenue by product, channel or customer | No |
| Costs | Cost of sales and operating expenses by behavior | No |
| Capex and Depreciation | Asset additions and depreciation schedule | No |
| Working Capital | Receivables, inventory, payables from days or percentages | No |
| Debt | Opening balance, draws, repayments, interest | No |
| Income Statement | Profit and loss | No |
| Balance Sheet | Assets, liabilities, equity | No |
| Cash Flow | Operating, investing, financing | No |
| Scenarios | Switch cell and scenario inputs | Yes |
| Checks | All integrity checks, one master flag | No |
The rule that matters most: numbers are typed only on Assumptions, Historicals and Scenarios. Every other sheet is formulas.
Formatting conventions
| Convention | Meaning |
|---|---|
| Blue text | Typed input |
| Black text | Formula |
| Green text | Link from another sheet |
| Yellow fill | Key assumption to review |
| One formula per row | Same formula across all columns |
| Timeline across, line items down | Consistent on every sheet |
| Units in the header | For example, "$000" |
Assumptions sheet: starting set
| Item | Year 1 | Year 2 | Source and note |
|---|---|---|---|
| Revenue growth or driver | 1,200 | (formula) | Sales forecast |
| Cost of goods sold, % of revenue | 60% | 60% | Last 3 years |
| Operating expenses | 240 | (growth %) | Budget |
| Depreciation method and life | Straight line, 8 years | Accounting policy | |
| Interest rate on debt | 6% | 6% | Loan agreement |
| Tax rate | 25% | 25% | Accountant |
| Receivables, % of revenue | 15% | 15% | Collections history |
| Inventory, % of COGS | 40% | 40% | Operations |
| Payables, % of COGS | 20% | 20% | Supplier terms |
| Capital expenditure | 80 | Capital plan | |
| Debt repayment | 30 | Loan schedule | |
| Dividends | 40 | Board decision | |
| Minimum cash | 100 | Treasury policy |
(Illustrative values, matching the worked example.)
Income Statement sheet: lines and formulas
| Line | Formula idea |
|---|---|
| Revenue | Link from Revenue sheet |
| Cost of goods sold | Revenue × COGS % (or from Costs sheet) |
| Gross profit | Revenue - COGS |
| Operating expenses | Link from Costs sheet |
| Depreciation | Link from Capex and Depreciation |
| Operating profit | Gross profit - opex - depreciation |
| Interest expense | Link from Debt sheet |
| Profit before tax | Operating profit - interest |
| Tax | MAX(0, Profit before tax) × tax rate |
| Net income | Profit before tax - tax |
Use MAX(0, ...) for simple tax on profits, and extend the logic if you carry forward losses.
Balance Sheet sheet: lines and formulas
| Line | Formula idea |
|---|---|
| Cash | Closing cash from Cash Flow |
| Receivables | Revenue × receivables % |
| Inventory | COGS × inventory % |
| Fixed assets, net | Opening + capex - depreciation |
| Total assets | Sum |
| Payables | COGS × payables % |
| Debt | From Debt sheet |
| Equity | Opening + net income - dividends (+ new capital) |
| Total liabilities and equity | Sum |
| Check | Total assets - total liabilities and equity (should be 0) |
Cash Flow sheet: lines and formulas
| Line | Formula idea |
|---|---|
| Net income | Link |
| Add back depreciation | Link |
| Change in receivables | Opening - closing |
| Change in inventory | Opening - closing |
| Change in payables | Closing - opening |
| Cash from operations | Sum |
| Capital expenditure | Negative of capex |
| Cash from investing | Sum |
| Debt drawn or repaid | From Debt sheet |
| Dividends | Negative |
| Cash from financing | Sum |
| Net change in cash | Operating + investing + financing |
| Opening cash | Previous closing |
| Closing cash | Opening + net change |
Checks sheet
| Check | Test |
|---|---|
| Balance sheet balances | ABS(assets - liabilities - equity) < 0.01 in every period |
| Cash agrees | Cash on balance sheet equals closing cash on cash flow |
| Retained earnings roll | Opening + net income - dividends = closing |
| Fixed assets roll | Opening + capex - depreciation = closing |
| Debt roll | Opening + draws - repayments = closing |
| Minimum cash | Closing cash is at least the minimum cash assumption |
| Master flag | All checks pass: show "OK", otherwise "ERROR" in red on the Cover |
Building order
- Create the sheets and the timeline.
- Enter assumptions and historicals.
- Build the income statement from drivers.
- Add the schedules (capex, working capital, debt).
- Build the cash flow, then the balance sheet.
- Add the checks, and fix until all pass.
- Add scenarios and test extreme inputs.
- Review with someone who did not build it, using the model audit checklist.
For the step-by-step method, see how to build a financial model, and for the profit and loss forecast in detail, see how to forecast an income statement. Margins by product feed the cost lines; see unit economics.
FAQs
What should a financial model template include?
Assumptions, schedules, the three statements, scenarios and a checks sheet, with typed numbers only on input sheets.
Does the template work in Google Sheets?
Yes. The formulas and layout described here work in Excel and Google Sheets.
How long does it take to build?
A basic version takes about a day; a model with many products, entities or financing structures takes longer.
Is a template enough?
For many small businesses, yes. For complex businesses, we can help.
Keep reading
This article is for general information. It is not accounting, tax, legal or investment advice, and examples use illustrative numbers rather than client data. Confirm decisions that carry financial or legal consequences with a qualified professional.