I am looking for budgeting and forecasting guidance on how to budget an income statement, balance sheet and prepare a cash forecast. I just started my job and need to put together some numbers pretty quickly. How often do you update the cash forecast?
Hi, I am Kelly Battles
This is a very detailed topic to cover in a few paragraphs but here is how I would get started:
1) Begin with your current actual financials to guide the level of account detail you want to use in your budgeted/projection statements.
2) Then I would begin your budgeting and forecasting by building out your bookings plan and other top line items that may drive other assumptions e.g., units, pricing, new customer count. Once done then I would work your way down the income statement.
a. Translate bookings to revenue and deferred revenue if required by and based upon your revenue recognition policy
b. Estimate cost of goods sold
i. direct (product costs)
ii. departmental or indirect (Employee costs for customer support, and/or shipping and receiving)
c. Then estimate Operating Expenses by department, I typically break these down into two modules
i. Headcount and related expenses (salary, bonus, commission, PRT and benefits, vacation accrual)
ii. Discretionary OpEx (Employee Development, Facilities,
d. Estimate CapEx and use this to calculate fixed assets for the balance sheet and depreciation for the income statement
e. Estimate other income and expense
f. You should then be able to create a basic P&L budget at this point, I would review the trend of prior period actuals to your results to test if you are missing anything
3) Then for the balance sheet:
a. You have done deferred revenue and fixed assets above.
b. Now estimate other balance sheet items such as AR, Accruals, Prepaids, AP, leases etc .
c. Set up an excess cash (asset) and a necessary to finance (Debt or Equity) line as plugs in your balance sheet. If your budget is generating cash, the excess cash line will build to balance your balance sheet, and if not, you may eventually build up the debt/equity plug to balance it.
4) The build your cash flow statement by calculating changes to the balance sheet accounts, adjusting for non cash items and flowing net income through retained earnings.
5) In terms of the process, I typically use functional leads as budget contributors for departmental Headcount, OpEx and CapEx and then assume finance will do the majority of the rest centrally. Because our budgeting application is a SaaS platform, all budget contributors have real time access, input, and results independent of their location etc.
6) Once all in and statements are generated, I would then focus on iterating, making trade-offs to get the results right and performing sensitivity analyses.
7) In terms of frequency, we budget 2 years out, once per year and then have a rolling forecast that is memorialized once per quarter. We do shorter-term, less detailed cash flow forecasts weekly. We analyze our results monthly in detailed executive variance reports that are automated within our system.
I hope this helps and good luck!
Kelly
Anon,
Updating and giving my spin on Kelly's excellent and thoughtful input.
I'm answering from the perspective of using
1) Begin with your current actual financials. Extract from this the core items you want to watch, items which are controllable vs. not, items that are high
I start by building the three sheets with the variables (cash, sales, debt, rent, etc) all called out, with historicals as the basis. The blank cells going forward are where I will put my formulas. Make sure to use all three reports (IS/BS/CF), as they are necessarily interlinked.
2) Next I would choose a driver or two to focus on. Sales growth, headcount planning; something that is in your control (relatively) *and* will impact the rest of the financials. Set this up as your core variables. Try not to exceed 3, unless you are trying to do something on the scale of modeling the weather.
Tie those drivers to:
-Quote to cash cycle (even to the level of hiring sales rep, and how fast they generate cash)
-Rev rec: I generally reserve this for a *dependent* spreadsheet. This is so you have a good cash basis, and so when you are reviewing the rules for recognition you can model that independently.
-COGS, Direct Costs, Indirect / OPEX. Again try to tie these to your drivers. For example a 10% increase in sales might mean a 5% increase in Internal Sales, and then the salary, rent, etc flows from that.
-CAPEX: do two. One is the automated from the above (think new laptops), the other is an independent variable tied to, for example, investment in a new initiative.
-Investments: Loans, equity, any outside source of cash. This can be in two parts as well. One, an automatic draw on the bank when your cash goes below a certain level, the second a deliberate and strategic addition (like in the CapEx, if you're opening a new facility so timing a loan).
3) Now that you've modeled the variables, each in their correct place, add up the columns and set the linkages. For example, your cash line in the cashflow statement should drive the cash line in the balance sheet. The basic cashflow formula (last month + changes, etc = this month) works well and avoids circular references. A tip: trying to use the totals (for example, current assets) to drive another function can lead to circular references.
Here is another reason why I keep rev-rec separate; so that you don't have to model the effect of deferred revenue on cash, as from a managerial aspect it is an artificial factor. Your CEO might understand when you say "we expect to use significant cash because of the decrease in deferred revenue", but may wonder *why* you're making that point. Granted for external consumption you will need to be able to communicate that as well...so don't ignore it. Depending on your business you may want to exclude other items that don't directly affect cash, such as depreciation from the core model, so that they can be reviewed separately.
Review how the model moves forward. Would it have predicted the past? Start amending the links, drivers, and then dependent variables so that it is *reasonably* reflective of the history.
4) Finally, check sensitivity. Start by messing with the independent variables, then move on to dependent variables. If you have access to or can afford a few bucks for a simulator, running the entire model through a million or so iterations can help give you code coverage on how the variables interact. It can uncover errors, as well as "corner-case" situations where the sensitivity isn't apparent from a simple pivot table.
5) Now that it is baked, you can present it, present some of the corner-case discoveries, and start using it to model.
Note, there is a difference between a model and a forecast, at least in my mind. Forecasts suggest that you *can* predict the future, which to a degree may well be the case. Modeling is just an explanation of what might affect the future. Too many factors, or too much uncertainty, and it isn't a forecast. By modeling the interactions of the variables, one important and informative product of the process is being able to communicate the limitations of the forecast. So, instead of a 5-year "forecast", you might be able to communicate that the forecast is reasonably certain out 3 months, then past that, it is simply a model, and likely isn't predictive at all. Nevertheless, a longer term plan is critical for visibility into direction (think annual budgets), so they are absolutely useful.
6) To your final question, "how often", I give back "how often does your knowledge change relative to the known important variables, and how often does the model/forecast get put to use". If you're using a tool like Host Analytics or even the simple budgeting software in Xero or
