For nearly every client we work with, we create or maintain a forecast. In fact, we often recommend creating or updating a company forecast as the starting point of a client engagement. It’s a great way to come up to speed on a company while providing a clear deliverable and a chance to gauge the working relationship. If you’re thinking about hiring a part-time CFO but unsure of how to start, reach out to us about reviewing, creating, or updating your financial model.
This is the second of 2 blogs on forecasts. The first article discussed the many essential benefits a forecast provides. This one describes the best practices in creating one.
If you have read our first article on the reasons for building and maintaining a forecast, you have seen how the forecast provides numerous benefits and truly serves as a dashboard for the business. To get the maximum benefit, there are also a number of best practices we recommend.
First, our preference for smaller companies is to use a spreadsheet. Though templates exist, they don’t provide the flexibility of a spreadsheet and could limit your options for defining the structure of your model. That said, the flexibility and freeform of a spreadsheet also makes them prone to errors. Below we’ll provide a few tips on reducing common ones.
Poorly designed spreadsheets can be hard for people other than the person who built it to understand, manipulate, or maintain. So, when building a forecast, design it so the layout is clean, the flow logical, and formulas not overly complicated. If needed, break calculations into steps or sub-totals and add comments to explain assumptions or other logic. Cell or range names can also be used so users see a name like “Tax Rate” rather than a cell reference like “A30”.
Make Assumption Driven
A basic best practice for usability is to make the forecast assumption driven wherever possible. Don’t hard-code numbers when a formula could be used nor bury important logic within formulas. Here’s a simple example:
if payroll in Cell C10 is $100,000 and you expect payroll taxes to be 8% of that, create an assumption where you enter the assumption for payroll taxes (in this case, 8%), and write a formula multiplying wages by that tax assumption. Do not simply type in “$8,000” and also don’t make the formula “=8% * C10”. Stating all assumptions separately makes the drivers of the forecast visible and easy to change. This is especially important when sharing with others so they can see how numbers were derived and can easily make changes themselves.
Group assumptions together, preferably in one tab. That won’t always be possible or practical; for example, it may be better to put certain assumptions on the same page as the outputs they drive to avoid having to flip between tabs or the format. However, try to limit that so assumptions aren’t scattered throughout the model. Another best practice is to highlight or color code assumptions so they are distinguished from cells that contain labels or outputs. This makes it easier for users to identify what they can or shouldn’t touch.
Breaking out the assumptions also helps define key milestones you expect to reach. For example, a SaaS company might calculate sales by starting with existing customers, less any assumed terminations, plus an assumed number of new customers times an average sales price, plus any price increases or upsells. By separating these pieces out, you can better communicate the assumptions for each and explain any variances by identifying which assumption was off and by how much.
Outputs
A typical forecast should extend for at least 12 months by month and then can go to quarters or years. How long to extend the model will depend on how the file is being used and preferences of the reader. A budget will most commonly go out by month through the end of the fiscal year and might not go beyond that. A start-up raising money will want to show 3 to 5 years of projections and monthly totals might be appropriate until it turns cash flow positive. To avoid writing different formulas for quarterly or annual projections, you can keep all calculations monthly and then create summary pages to consolidate the monthly numbers into annual or quarterly results.
You will want to project both the income statement (also called Profit and Loss or P&L) and balance sheet. If you have those, you can easily add a statement of cash flows to create a full, 3-statement model. Though you will want a structure consistent with your accounting system’s Chart of Accounts, you likely will not need anywhere near the same level of detail. For example, you may not need breakdowns by department for all expenses and could report company-wide totals or, perhaps, only break out wages by department. Perhaps all general office, supplies, and miscellaneous administrative expense can be combined. If you have multiple bank accounts, a single cash line should be sufficient.
Typically, a forecast will be a blend of prior period actuals plus future, projected periods. Since a forecast should be a living, frequently updated, document, as each period closes, you can overwrite the forecasted numbers with actuals. Results for the year can now be a blend of actuals and forecast.
What can be very useful, is creating a variance analysis that compares what you previously forecasted to the actuals as well as your newest forecast. We recommend saving a copy of your initial forecast for the year and calling it your Budget or Benchmark. Then compare that to your actuals for closed periods or your current forecast for the balance of the year. Investigate any variances to see if they are simply timing differences or a true revenue or expenses variance.
Typically, you will want to create reports or analyses using the numbers in the forecast. Best practice is to build these into the file, directly pulling from outputs from the forecast. This allows you to format the reports how you want and as you update the forecast, the reports will automatically update. Common things to report on are various ratios, variances, more summarized formats, or charts.
How Detailed?
It’s likely the most important line items will be revenue and payroll expense. So, for these variables, it is useful to model these in detail. Every company will look at revenue projections differently: for some, they might go customer by customer; others might tie sale to units produced or specific projects. A retailer might be more concerned with how many selling days there are as well as seasonality such as higher sales around the holidays. Model what makes sense for your company which is another reason not to use someone else’s template. Also, build a bottoms-up model by which we mean determining the underlying drivers rather than applying a simplistic, global assumption like a fixed growth over last year or creating an annual target and allocating it back to months.
Payroll is usually a company’s largest expense. For smaller companies, you can often get to the individual person level though grouping like positions is fine. We recommend a model that can handle start and end dates for each person/position plus raises. Other things that can be built into a payroll model include bonuses, car allowances, and recruiting fees. Ideally, the model will also calculate headcount both because it’s an important metric but also because several expenses can be projected based on headcount.
Since every company is different, there may be other variables worth modeling in detail. These could include cost of sales, marketing budgets, and debt service.
Some expenses, like commissions could be modeled as a percentage of sales. Others, like travel might be tied to headcount. Still others, like rent, might stay fixed. For smaller expenses, the level of precision generally doesn’t need to be high so don’t spend too much time on those. For many expenses, historical actuals can provide a good guide so make use of that information.
Inputs from Others
Where input is needed from other team members it is critical that everyone is working from the same assumptions about things like projected revenue, new products, and timelines. Once those are agreed to, you can ask managers to project things like their headcount needs and spending on equipment or software.
Though we recommend forecasting in a spreadsheet, a weakness is they are not good collaboration tools. Though the workbook can be shared, version control can be an issue. Also, forecasts typically contain confidential information, most notably payroll details. This typically is not something you want accessible to more than a very few people. As a result, it’s more typical to ask for inputs from team members but have the owner of the forecast file, typically a CFO or controller, compile everything.
Another issue is that while you want input from others, not everyone is skilled at spreadsheet formulas. For example, we frequently work directly with company presidents and while we rely on them for the key assumptions, we don’t expect them to write formulas. What we generally tell them is to get us the story of what you expect or of how revenue and expenses are driven. Once we have that, we can determine how to model it.
Common Errors
As mentioned, spreadsheets are powerful, flexible, tools but prone to errors. Some tips for controlling those:
- The spreadsheet tool may alert you to certain mistakes like circular references or text in a cell where it expects a number or formula. Follow up on these.
- Where possible, foot and cross-foot columns of numbers. For example, in a typical Income Statement, you will have monthly totals and then an annual summary. Make sure , for example, that annual net income is the same whether you add up the monthly net income or first add up monthly revenue and monthly expense and calculate annual income from those.
- If you create summary reports, verify the key numbers match the detail. For example, that net income in your monthly income statement matches what you show in your year-by-year summary.
- Make sure your balance sheet balances. Replacing formulas with hard-coded numbers or having a value become non-zero can cause this error even in a sheet that initially looked fine.
- Most of these last two double-checks can be done with formulas so that you’re not relying on your eyeballs to spot them. For example, add a row that subtracts assets from liabilities plus equity and if it is not zero, you can easily spot it.
- Always step back and take a 10,000 foot look at the end results. That can be hard when you’ve been deep into writing and debugging formulas or keying in hundreds of numbers. However, that step often catches major errors and without the embarrassment of handing output to someone and having them almost immediately spotting it.
- Finally, maintain good version control. Make sure it’s clear what the latest version is to avoid updating an outdated version of a model. This is especially important if collaborating with others. Also, save copies of versions that have been shared with others. That way if a reader asks about numbers you provided in the past, you can go back and find the source.
If you think your business could benefit from a part-time or outsourced CFO, please reach out to us. We offer a free, initial conversation and, as this article describes, building or updating a forecast can be a great place to start.