About This Model
You don’t have to be a modeling guru or a psychic to produce a meaningful and useful forecast for your business. Building a useful forecast model is as much about the journey, and what you learn about your business along the way, as it is about the end result. After all, the only sure thing about a forecast is that it will be wrong! What’s important is gaining a good understanding of what drives your business’s financial performance, and being able to answer questions like: “what will happen if margins decline by 5%”, “what will happen if interest rates increase by 3%”, and similar what-if style questions.
Spreadsheets and similar modeling tools are particularly good at helping you gain such insight into your business, and we’ve built this small business forecasting tool specifically to do this.
– It is quick, easy, and free to use this Sumwise model –
After opening the model (by clicking the button above), select each of the tabs in turn and enter your own assumptions into the yellow shaded input cells. The grey shaded cells contain the calculations and cannot be altered. For more information on what each tab or worksheet does, we suggest you read the more detailed information below.
Please note that the above model is for demonstration purposes only, and any changes you make here will not be saved. If you want to save your changes, you’ll need to login to Sumwise. Logging in to Sumwise for the first time only takes a minute!
More about the Forecast Financial Statements
Cash Flow Statement
Forecasting cashflows is undoubtedly the most important of the three financial statements for small businesses. Unlike the income statement, the cash flow statement is not affected by accounting assumptions like depreciation rates, revenue recognition policies, and the like. And, after all, cash is king — so this should be your focus. The cashflow statement in this model uses the indirect method. This method starts with your earnings line in the income statement (earnings before interest, tax and deprecation) and then adjusts this for all timing differences (such as changes in working capital) and cash flows not included in this figure (such as interest paid, tax paid, capital expenditure, and the like). The end result or bottom line of the cash flow statement is your Net Cash Flow.
For many companies the income statement (or profit and loss statement) gives a more realistic picture of true performance. It’s advantage over a cash flow view of your business, is that it better matches the earning of income, and incurring of expenses, to specific accounting periods. If you spend money on fixed assets or investments that will cost you cash upfront but benefit your company over many years, then your net income or net profit forecast will give you a more complete picture of your true performance, than simply looking at your cash flows. The bottom line of the income statement is your Profit or Loss After Tax, but often people will look at other important earnings lines such as Earnings Before Interest, Tax and Depreciation (EBITDA).
The balance sheet gives you a snapshot of your business at a point in time. It doesn’t tell you anything about how you got there, just what you have (assets) and what you owe (liabilities) at discrete points in time. It can tell you something about the future too — for example — trade debtors, or accounts receivables, usually “turn into cash” (i.e. are collected) within a few months of the balance date. Likewise, creditors or accounts payable will need to be paid within a certain amount of time e.g. 30, 60, or 90 days.
More about the Worksheets
The FORECAST Worksheet
The FORECAST worksheet contains the forecast financial statements (income statement, cash flow, and balance sheet) for each of the next three years. This is the top level worksheet and all of the other worksheets feed their results into here. There are a number of assumptions that are entered directly into the FORECAST worksheet, including:
- Other Revenue (as distinct from more detailed Sales revenue which is entered in the Sales worksheet).
- Other Cost of Goods Sold percentage.
- Borrowings, Repayments, and Interest Rate.
- New Equity.
- Tax Rate.
- Percentage of revenue in Accounts Receivable at the end of each year.
- Percentage of expenditures in Accounts Payable at the end of each year.
The SALES Worksheet
The sales worksheet is where you enter the forecast sales assumptions for your business. The model lets you enter the units and prices for each year for each of two product lines. It then calculates the gross sales income for each product and the total sales income. Below this total income calculation line, you can enter the forecast cost of sales for each of the products, and the model will calculate your per product and total cost of sales. The results for total sales income and total costs of sale are fed through to the FORECAST worksheet. The sales forecast can be calculated by providing any two of the following three required variables: units, prices, and sales. The default is that sales are calculated by providing the forecast units and prices, but this can be changed by clicking in the tag area above the column headers and selecting a different option.
The EMPLOYEES Worksheet
The EMPLOYEES worksheet or tab is where you enter details of the various roles your business employs. The assumptions you need to enter include:
- The names of the various roles — you can simply overwrite the generic role 1, role 2, etc., labels that are currently there with roles that are meaningful to you in your business e.g. CEO, Accountants, Marketing, etc.
- The starting base salary for each role — this will be the base salary for year 1 of the forecast.
- The annual growth in base salary for each role.
- The oncosts or extras percentage, to cover things like statutory leave entitlements and other benefits.
- The number of employees in each role for each year of the forecast.
The model will use this data to calculate the total salary and wages costs for each role for each year of the forecast. The Total row will add these all up, and this total will appear in the FORECAST worksheet in the Salaries row.
The EXPENSES Worksheet
This worksheet contains all the forecast assumptions for indirect expenses (or overheads) like accounting fees, rent, electricity, marketing, etc. In fact, any expenses that are not directly product or service related (these should be in cost of sales) or salary related (these should be in the EMPLOYEES worksheet), should be entered in this EXPENSES worksheet.
The CAPEX Worksheet
This worksheet contains the assumptions about forecast capital expenditure. Enter the dollar amounts you expect to spend on fixed assets and other longer term assets like computers, furniture, buildings, machinery, etc. You can enter your assumed expenditure in three categories, for which depreciation is calculated over 3, 5, and 20 years, respectively. The depreciation calculation uses the straight-line method. The total capital expenditure for each year is fed through to the Cash Flow statement in the FORECAST worksheet, and the total depreciation for each year is fed through to the Income Statement.
Sumwise is a new type of online spreadsheet designed specifically for financial modeling. Current spreadsheet technologies (like Excel and Google Docs) are powerful and flexible, but they are also unstructured and error prone. And spreadsheets, once built, are not easy to modify or extend.
Sumwise addresses all of these problems with clever features like: user defined row and column names; rows and columns that can be tagged with meaningful keywords; and cell groups that share a common formula and formatting across a range a related cells.
Sumwise was invented and developed by a small dedicated team of software developers and spreadsheet experts in Sydney, Australia. If you’re interested in learning more please visit our website or send us an email.