Financial Projections Maker (3-Statement Model for Loans)

About the Financial Projections Maker (3-Statement Model for Loans)

A three-statement financial model built from your own assumptions: revenue streams, staff, operating expenses, fixed assets and depreciation, working capital (debtor, creditor and stock days), loans, equity and tax. It works month by month for 2 to 5 years and gives the profit and loss, the cash flow and a balance sheet that balances in every month, with the ratios lenders and investors ask about — DSCR, interest cover, the current ratio, debt to equity and break-even revenue — and up to three scenarios beside your base case.

It is made for a loan application or an investor pack in any country: amounts are in the currency you choose, Year 1 is the first twelve months of your forecast, and no market data is used — every figure follows from what you enter, and nothing is adjusted to reach a lender’s ratios. In the Excel workbook the model stays live: the totals, the links between the three statements and the balance sheet’s cash, fixed assets, loans and equity are formulas, so a figure you change there flows through.

Without a pass the result stays a free preview: the first 12 months of the profit and loss are on screen in full, while the later years, the cash flow, the balance sheet and the ratios are hidden, and the Excel workbook, the PDF and Copy summary unlock with a Premium pass.

Common uses

• Support a business loan application with monthly projections, the loan schedule and DSCR for each year.
• Show investors when a start-up becomes profitable, how low cash goes and how much funding it needs to stay above a minimum balance.
• Test a slower launch, lower prices or longer customer credit against the same plan before committing to a lease or a hire.
• Hand an accountant or adviser an Excel model whose statements stay linked when they change a figure.

What the model contains

• Profit and loss, by month and by year: revenue by stream, direct costs and direct staff (the cost of sales), gross profit, operating expenses, EBITDA, depreciation, operating profit, interest, tax and net profit, with the gross and net margins.
• Cash flow, by month and by year: net profit with depreciation added back and the changes in receivables, inventory, payables and tax owed (cash from operations), the fixed assets bought (investing), and equity, loans, repayments and dividends or drawings (financing), from the opening to the closing cash.
• Balance sheet at each month end and year end: cash, receivables, inventory, fixed assets at cost less depreciation; payables, tax owed, loans due within and after 12 months; opening equity, capital put in and retained profit — with a check line that is 0 when assets equal liabilities plus equity.
• Ratios for each year, sales drivers (units, customers, people), the loan schedules, the scenarios side by side and a list of points to check: cash running out, a DSCR below 1, negative equity, a loan still owed after the forecast.

How each figure is worked out

• Revenue: units × price, customers × monthly price (customers = last month’s × (1 − churn) + new customers), or an amount a month; each grows at its own rate from the month it starts (a yearly rate is compounded monthly), times the seasonality index of the calendar month ÷ 100. Prices and unit costs rise at the start of each forecast year.
• Staff: people × monthly pay, raised each forecast year, plus the employer’s on-costs as a share of pay. Ticked roles go in the cost of sales, the others in operating expenses.
• Depreciation: straight line, cost ÷ (useful life in years × 12) a month from the month an asset is bought — the cost spread over the periods it is used, as the SEC’s guide to financial statements describes; land (a life of 0) is not depreciated.
• Working capital at 30 days a month: receivables are the sales of the last debtor days (the days-sales-outstanding idea in reverse), payables the purchases of the last creditor days, and inventory stock days ÷ 30 months of this month’s direct material costs, bought as needed. Staff and operating expenses are paid in the month.
• Loans: a loan that arrives in month m is repaid monthly from month m + 1, after any interest-only months: equal instalments of B × r × (1 + r)ⁿ ÷ ((1 + r)ⁿ − 1) with r the yearly rate ÷ 12, equal principal, or all at the end. Interest is paid as it is charged. The schedule is the one the project report generator uses.
• Tax: your rate on each forecast year’s profit, charged month by month on the profit so far, with losses carried forward if you choose; paid monthly or once a year a set number of months after the year ends.
• Cash flow: the three activities of IAS 7 — operating, investing and financing — with the operating cash flow worked out by the indirect method: net profit adjusted for depreciation, which is not cash, and for the changes in receivables, inventory, payables and tax owed. Interest paid stays in the operating cash flow, inside net profit.
• Balance sheet: assets = liabilities + equity, the equation of the SEC’s guide. A negative cash balance is shown as a cash shortfall to fund among the current liabilities, and the summary gives the extra funding that keeps the cash balance you want in every month.

Ratios and their formulas

• DSCR = (EBITDA − tax paid in the year) ÷ (interest + principal repaid): EBITDA standing in for operating cash flow, less the taxes paid in cash, over the year’s debt service, as Corporate Finance Institute sets it out. Below 1, the year’s earnings do not cover the loan payments. When tax is paid in the year it is charged, this equals (net profit + depreciation + interest) ÷ debt service, the form many bank project reports use.
• Interest cover = operating profit ÷ interest.
• Current ratio = current assets ÷ current liabilities at the year end (CFI); current items are those due within a year.
• Debt to equity = total liabilities ÷ equity, as the SEC’s guide defines it; blank when equity is not positive.
• Break-even revenue = fixed costs ÷ contribution margin, with contribution margin = 1 − variable costs ÷ revenue — the US Small Business Administration’s method. Variable costs are direct materials and expenses that are a share of revenue; staff, other expenses, depreciation and interest are fixed. Margin of safety = (revenue − break-even revenue) ÷ revenue.

Lenders set their own minimum ratios in their loan terms, so the model shows each figure with its formula and no pass mark.

The Excel workbook and the PDF

The workbook has a summary, your assumptions, the profit and loss, cash flow and balance sheet by month and by year, the ratios, the sales drivers, the loan schedules and the scenarios. On the monthly sheets the revenue and cost lines are figures and everything that follows from them is a formula: totals and profits; the cash flow’s net profit and depreciation from the profit and loss and its working-capital lines from the balance sheet; the balance sheet’s cash from the cash flow, fixed assets from purchases and depreciation, loans from drawdowns and repayments, and capital and retained profit from equity in, net profit and dividends. The yearly sheets add up the months and the ratios are formulas of the statements, so changing a figure — a month’s sales, a cost — updates all three statements and keeps the check line at 0. Each formula’s result is stored with it, so the figures also show in previews and viewers that do not calculate. The PDF holds the same statements by year and by month, the ratios, the scenarios, the loan schedules and the assumptions, in the order a lender reads them.

Scenarios

Each scenario changes your base case by percentages — sales volume, prices, direct costs, operating expenses entered as amounts, pay — and can start every revenue stream some months later or give customers more days to pay. Pick a scenario to see all its statements, or compare the cases side by side: revenue, EBITDA and net profit each year, the lowest cash and when it happens, the extra funding needed, the lowest DSCR, the closing cash and equity. The example’s downside shows how a slower start can turn a funded plan into one that needs more money.

Which tool for which job

• A loan under an Indian bank scheme (Mudra, PMEGP, CGTMSE, Stand-Up India): the project report generator, with the scheme checks and the report layout banks there expect, and the CMA report generator for CMA data and MPBF.
• The next 13 weeks of cash, receipt by receipt: the cash flow forecast planner.
• One product’s break-even point or a single ratio: the break-even calculator and the financial ratio calculator.
• A month-by-month plan for a lender or investor anywhere, with all three statements linked: this tool.

Limitations

• Projections are illustrative: they follow your assumptions, which the tool does not judge, and no figure is adjusted to meet a lender’s ratios. They are not financial advice.
• Amounts are net of VAT, GST or sales tax, and in one currency: the model holds no sales tax collected or paid and converts nothing.
• Tax is one rate on each forecast year’s profit, with losses carried forward without limit if you choose — not a tax computation for any country (no allowances, deferred tax or instalment rules).
• Loans are repaid monthly and interest is paid as it is charged; there is no overdraft facility (a cash shortfall shows as funding to find), and no interest is earned on cash.
• Depreciation is straight line from the month of purchase; assets are not sold, revalued or impaired, and the opening fixed assets are depreciated over one remaining life.
• Working capital uses 30-day months; inventory follows this month’s direct material costs; staff and other expenses are paid in the month they are incurred.
• Revenue streams, staff and monthly expenses run from their first month to the end of the forecast: there is no end month (a single payment is a one-off expense).
• Year 1 is the first twelve months of the forecast, not the financial year of any country; a forecast runs 2 to 5 years.
• These are management projections, not accounts prepared under an accounting standard: for example, the cash flow starts from net profit and keeps interest paid among the operating cash flows, while IAS 7 as amended by IFRS 18 starts the indirect method from operating profit.

Privacy

Everything is calculated in your browser: your assumptions and results are never uploaded. The model on screen is kept in this browser as you type, so it is still there when you come back (New blank model clears it); a model file you save stays on your device.