Financial Modelling Excel Templates

Start with a balanced opening statement, then enter estimates for up to 12 equal-length forecast periods. Keep a reference to the records behind each set of inputs.

Opening cash, receivables, inventory and fixed asset carrying value must equal payables plus debt and total equity. Enter equity from the records, including retained earnings. The tool shows an opening difference and pauses projections until you correct it.

Opening balances are historical inputs. Revenue and the operating and funding assumptions are estimates. The fictional defaults illustrate the calculation. Replace them with the matter’s records and estimates.

Enter first-period revenue and growth per period. All other entered assumptions repeat each period. The tax rate is your assumption, applied to positive accounting profit and paid in the same period.

The model links profit to cash through changes in receivables, inventory and payables. Closing cash follows cash movements, while profit and funding movements change equity.

Closing receivables equal period revenue multiplied by collection days divided by period days. Inventory and payables use cost of sales on the same basis. These closing-balance assumptions approximate timing; they do not track individual invoices.

Operating cash adds depreciation back to profit, subtracts increases in receivables and inventory, then adds increases in payables. This follows the indirect-method principles in AASB 107.

Capital expenditure is an investing cash outflow. Draws, repayments, equity contributions and dividends are financing cash flows. Interest is an operating payment calculated on opening debt using annual interest multiplied by period days divided by 365.

Funding movements occur at period end. Repayment is capped at opening debt plus new draws. Negative projected cash remains a funding shortfall; the model never adds an automatic loan.

The statement table shows assets less liabilities and equity for each period. Its cash check separately compares closing cash with receipts, supplier payments and the other cash movements. A difference is preserved, with no balancing plug.

Enter an alternative set of assumptions to see each statement difference beside the base case. The scenarios share opening records and period settings.

Download a free .xlsx workbook with Inputs, Base and Alternative sheets, a Comparison sheet and model conventions. Input cells stay editable. Statement formulas reference those cells and earlier periods, so changing an assumption recalculates the model in Excel. The workbook keeps the exported horizon. Change the period count in the browser before exporting again.

Source references are exported as text. All processing happens in your browser, from entered records to workbook download. The template has no bank connection or accounting-system integration.

In the fictional first period, extending collection from 15 to 30 days holds another $12,000 in receivables. Adding $2,000 of debt reduces that cash difference to $10,000. First-period profit is unchanged because the new debt arrives at period end.

The fictional base case starts with $20,000 cash and earns $24,000 revenue over 30 days. Cost of sales is $9,600, cash operating expenses are $6,200 and interest is $300.

Depreciation of $400 reduces profit before tax to $7,500 and closes fixed asset carrying value at $28,800 after $1,200 capital expenditure. At the entered 25% tax assumption, profit is $5,625 and closing cash is $23,025.

Depreciation is a non-cash expense. With tax set to zero in both cases, raising depreciation from $400 to $900 reduces profit and carrying value by $500 while cash stays unchanged.

AASB 116 requires systematic allocation over useful life. This worksheet uses your entered charge, capped at carrying value plus current capital expenditure. It assumes assets are available at period start and omits residual values and disposals.

Review the assumed collection pattern and debt schedule against the underlying records. A timing assumption longer than a period can produce negative implied receipts during rapid growth. Inspect those cash rows before using that scenario.

This simplified model excludes GST, deferred tax, tax-loss relief and other balance-sheet accounts. A balanced result proves the arithmetic within its assumptions. It does not prove a forecast or a valuation.