Revenue Projections Spreadsheet

Projecting church gifts is difficult but not impossible. If you have a few years of giving history and a good spreadsheet, you can get a pretty accurate forecast of how much a church will receive in a current fiscal year with a high degree of statistical confidence, about 98%. I use this with the most important revenue stream for a church, gifts and offerings, but it can be used for any revenue stream that is relatively stable (no major fluctuations from year to year).

To do this, you’ll need at least four years’ worth of giving data with totals by month. The more history you’ve got, the more accurate your projection will be. If you keep up this spreadsheet, each year, the forecasts will get more accurate. Here’s how to do the financial forecast:

1. Look on the Free Resources tab for the Annual Revenue Projections spreadsheet and open it.
2. Fill in the church’s or organization’s name
3. Change the “Year’s Row” to reflect the years for which you have data
4. Enter all the data for all the years prior to the current fiscal year
5. Enter current year data through the most recent month in the current year’s column
6. Change the formula in the row “Through the rest of year”
   1. If you have current year data for seven months, then change the formula in B20 to add up the giving for the last five months of the year.
   2. Repeat for all prior years – you want to add up in row 20 the amount given in each respective prior years’ last five months
   3. The current year’s row 20 cell cannot follow the pattern of the previous cells because there isn’t any data to add. Instead, average the percentage data for all the previous years.
   4. This step, #6, is crucial – it is the only step that is changed each month. As you input data for the most recently completed fiscal month in the current year you’ll need to change what months are added in row 20.
   5. The spreadsheet will give you a figure in “Projected Total as of EOM” (end of month). That is the forecasted receipts figure based on the data you have so far. I suggest copying that figure into the row underneath for the appropriate month so you can see from month-to-month how the forecast changes.

I’ve used this spreadsheet for about 10 years and it uncanny how accurate it is when you’ve got six years of prior giving history and six months of current year giving data. In fact, give the spreadsheet a test: since you know what the total giving was for your most recently completed fiscal year, enter the data as if that year were still in progress and see what the model forecasts with six months’ of data or seven. Compare the forecast with the actual year-end figure to see if it was within a statistical margin (4% or less).

The model isn’t flawless but it is about 98% to 99% accurate. Your Finance Committee will be impressed! This spreadsheet does not account for variances that skew giving such as major gifts or deaths or departures of major givers so consider those events when entering your data. Make sure you save this spreadsheet to your files so you can update it each month.

Lead On!

Steve