Financial Forecasting

Having answered the first of the two key questions, “How are we doing relative to what we planned to do?” We can now move to the second question, “Do we have enough cash to do what we planned to do?”

This question is more complex and, in the long run, more important. Organizations with sufficient cash do not go out of business. They have enough cash to pay salaries and wages, to pay the rent, to pay utilities. They even have enough to execute their plan. But how much cash is “sufficient?” You must have a certain minimum amount of cash on-hand to ensure that you can continue operations. This is the Cash Required.  Ultimately you want to compare the Cash Required to the Cash Available.

The Cash Required has three parts:

    1. Cash required in each of the remaining months in the year to support the budget,
    2. Cash required to provide for the change in unbudgeted variances, and
    3. Cash required to cover major identified risks.

The first task is to calculate the cash needed to support the budget.

So we start with the calendarized Budget. If you recall, the budgeted income by month was as shown below:

Calendarized Income Graph
Calendarized Income Graph

Meanwhile, the expenses by month look like this:

Expenses by Month

When you add the [positive] Income to the [negative] expense, you get the month-by-month Net Income, where a positive number is profit and a negative number is loss. Since you are on a cash basis, the Net Income number shows the cash generated or expended for the month.

The blue bars in the next graph show the monthly cash generated or expended. The green line shows the cumulative [i.e. Year-to-Date] cash generated or expended since the start of the budget year.Cum Cash Flow

The green line is the budgeted YTD Net Income / Cash Flow. At any time during the year, a positive variance (YTD Actuals B / (W) than Budget) is good. That means any variance plotted above the green line is good and any below the line is bad.

And here’s the first key:

The distance between zero line and the next low is the amount of cash you must have on hand to support the planned [budgeted] activities for the rest of the year.

In the chart above, which is shown from the viewpoint of the first of the year, the you will need slightly over $32,000 to cover the cumulative negative cash flow budgeted through November. This chart is be redrawn each month to reset the origin to zero. Once past November, the low point moves to February. Once you pass the last low point, you will want to extend this calculation to cover the next budget year, since the next low will probably be in the following November.

The second task is to calculate the cash required to provide for the changes in unbudgeted variances.

But wait. You have assumed that the income and spending for the rest of the year will be exactly on-plan. In our example of a Monthly Financial Report in the prior blog post, the YTD favorable variance came from reduced spending. Suppose the departments have just been slow to spend, but still intend to spend the money. For example, what if a missions trip has been delayed? What if some of the favorable income is because a fundraising event was held earlier than originally planned? This calls for a projection, a forecast, of the unplanned income and spending in each of the remaining months because the cash income and expense will differ from the monthly budgets.

You must ask each department to assess their income and spending for the rest of the year. From this information, you can project [Forecast] the variance at the end of the year. Then add this Forecast variance as an additional column on the Monthly Financial report as shown in the middle column below.

Projected Year End Variances

The right hand column is a “scratch” column that is not usually added to the reports to management or the board. It shows the amount of cash generated [positive] or used [negative] to move from the current variance to the year end variance.

For example, Global Outreach shows a $585 favorable YTD variance, but estimates they will be on-plan at the end of the year. This means that they will spend $585 more than they originally planned [budgeted] during the remaining months of the year.

Youth Ministries, on the other hand, are showing a $648 favorable variance, but are projecting that they will have a $1,037 favorable variance at the end of the year. That means they will spend $389 less than planned [more favorable] in the remaining months. This has the effect of generating cash.

Overall, both Income and Expense will end up in a net worse position at the end of the year than now. This means that the organization must have $2,721 more cash available than planned for the remained of the year to cover the change in variances.

You will now want to forecast when the change in variance, from the current position to the end of the year forecast, will occur. In short, it must be calendarized.

If you cannot make a reasonable estimate for when the change will happen, assume that the unfavorable changes will occur early and the favorable changes will be later in the year. This is a conservative approach, as it will cause you to hold more cash to cover the uncertainty.

In the chart below, notice that the “Amount to Change” column is the same as the “FY2014” column. All of the change amounts have been calendarized.

Variance Calendarization

The third task is to compute the cash required to cover major identified risks.

Risk is a whole separate topic in itself. Risks are events where we lack an important piece of information, such as, when it will happen, if it will happen at all, how much will it cost, etc. This lack of information keeps us from putting it in the budget, but we still have to make provision for it.

The first step is to identify the risks. We usually do this once per year in a brainstorming session. The risk is clearly stated with an item (subject), an event (present tense verb), causing an impact (object). For example, “The main air conditioner blower fails, requiring replacement.” Do not just list the category “air conditioner.” Make it a statement.

Then we assess the risks. We look at the probability the event will occur this year and estimate the impact the event would have if it did occur. We ignore risks that have an extremely low probability or that have such a small impact that we could cover the expense within normal cash flows.

Then we mitigate the risks that remain. We can buy down some of them with maintenance contracts and insurance, which we cover in the budget. Some we must cover by keeping cash on hand.

But, we don’t need to keep 100% of the impact on hand. Remember, most of these  events have a probability of happening. We can calculate an expected cost by multiplying the probability by the impact.

“But I don’t have a numerical probability or a firm impact.”

Just make a estimate. That’s a nice way of saying, just guess. Do you have a projector that costs $1,200 and has an average life of 3 years? The risk could be $400 the first year and $800 the second year and $1200 the third year.

Or, you could recognize that 50% of the failures happen after the average and that the probability of failure goes up over time and you have a warranty the first year. Then you would have $0 the first year $400 the second year, $700 the third year, $1,100 the fourth year, and $1,200 the fifth year (when you could just go ahead and buy a new one.)

The last step in the Risk calculation is to track the risk. As I said above, this calculation is performed at least once per year and does not change during the year unless a risk occurs or the environment changes. For example, you could decide in mid-year to purchase a maintenance contract with a full replacement term on a piece of equipment that was at risk of failure.

The sum of the expected costs is the risk amount that you will include in the cash required.

Side note: You could also set up a Line of Credit with a local bank. Then you would not have to provide for short-term cash to cover risks.

The comparison of Cash Required to Cash Available

The table below shows the final calculation of Cash Required.

It adds the Cumulative Cash Required for the Budget to the Cumulative Cash Required for Variances and to the results of the Risk Calculation.

Note that $34,867 is required to be available through November, the next low point in the calculation. Once you actually reach November, February becomes the next low point.

Cash Required
Cash Required

This is compared to the Cash Available. Cash Available is defined as the Cash at the end of the month, excluding amounts that are in restricted and designated accounts.

The excess of Cash Available over Cash Required is available for debt reduction, new projects, or investment. A shortfall of Cash Available indicates a need to increase income, slow spending, or obtain financing.

In Summary

While this may look complex, it is not time-consuming on a month-to-month basis. Even in a large organization, the Risk Calculation takes about 1 day per year. The Variance to Year End Forecast adjustment takes about 1 hour per month. The Cumulative Budget is a fall-out of the budgeting process and takes about 1 hour per year to calculate, once the budget is completed.