Excel Forecasting Question

[KatWil]

I have a table that I'm trying to forecast the next 11 months of data for. I have a data from the past 13 months. How would I go about doing it. 1 month of data is on a different table than the other 12 months because the 12 months of data is from out last fiscal year, versus the one1 month of data is from this fiscal year. I don't know if incorporating a sumifs formula like I did to pull my data from the tab I input it on and summarize into a table would help or not.

The first photo is verified data from the last fiscal year, I'd like to use to help project the next 11 months.
The second photo is verified data from this fiscal year so far, it's only 1 month of data but I don't know if it'll be helpful to use to project the next 11 months or not, which will go on the same table as the verified 1 month of data from this fiscal year.
Any help is greatly appreciated

 

[6StringJazzer]

You are asking what could be a complicated question, and I suspect it's more complicated than you were expecting. This is a lot more than just knowing the right Excel formula. This is really a business analysis problem, and there are consultants who would come in and charge you thousands of dollars to do this.

To forecast data, you must have two things:

1. Sufficient data for a reliable forecast
2. An understanding of the variables that cause your data to change over time

The more data you have, the more reliable your forecast will be because of the greater accuracy of the model. The longer into the future you are trying to forecast, the less reliable your forecast will be, because of increasing uncertainty in the variable values over time.

In your case you are trying to forecast over a period that is 85% as long as what you have data for. The forecast for the first month might be pretty good but the forecast into the 11th month is a crapshoot. Also we don't know the variables that will cause your forecast to change over time.

The simplest type of forecast is linear extrapolation. You take the slope of the existing data, and assume that slope will continue into the future.

Then you can extrapolate using more complex relations, such as quadratic or exponential. You do a curve fit to the existing data and use the equation of that curve to extrapolate.

There are also ways to forecast taking into account seasonal changes, like sales of skis.

For all of these methods, you will get a more reliable forecast if you have a better correlation of your data with the curve you choose to model it. Unfortunately you have posted a picture instead of data that I can copy and paste, so I am unable to do even a cursory analysis of your data. Also, I don't know what all of your data means but I am guessing that each column could have a different model for forecasting.

Just eyeballing Wages and Benefits, I see they go up and down with no rhyme nor reason. This suggests that you have volatile staffing. To project those you are going to have to be able to project staffing, which is going to require a bunch of business forecasts that go way beyond what we can project just by what is in your data. This will depend on the business climate you operate in, and a bunch of other things specific to your business. I can't even begin to guess at that without knowing a lot more about your business. On top of all that you would need to factor inflation into that, and that itself is hard even for economists.

What else can you tell us about your business and your data and what it means?

 

[KatWil]

You are asking what could be a complicated question, and I suspect it's more complicated than you were expecting. This is a lot more than just knowing the right Excel formula. This is really a business analysis problem, and there are consultants who would come in and charge you thousands of dollars to do this.

To forecast data, you must have two things:

1. Sufficient data for a reliable forecast
2. An understanding of the variables that cause your data to change over time

The more data you have, the more reliable your forecast will be because of the greater accuracy of the model. The longer into the future you are trying to forecast, the less reliable your forecast will be, because of increasing uncertainty in the variable values over time.

In your case you are trying to forecast over a period that is 85% as long as what you have data for. The forecast for the first month might be pretty good but the forecast into the 11th month is a crapshoot. Also we don't know the variables that will cause your forecast to change over time.

The simplest type of forecast is linear extrapolation. You take the slope of the existing data, and assume that slope will continue into the future.

Then you can extrapolate using more complex relations, such as quadratic or exponential. You do a curve fit to the existing data and use the equation of that curve to extrapolate.

There are also ways to forecast taking into account seasonal changes, like sales of skis.

For all of these methods, you will get a more reliable forecast if you have a better correlation of your data with the curve you choose to model it. Unfortunately you have posted a picture instead of data that I can copy and paste, so I am unable to do even a cursory analysis of your data. Also, I don't know what all of your data means but I am guessing that each column could have a different model for forecasting.

Just eyeballing Wages and Benefits, I see they go up and down with no rhyme nor reason. This suggests that you have volatile staffing. To project those you are going to have to be able to project staffing, which is going to require a bunch of business forecasts that go way beyond what we can project just by what is in your data. This will depend on the business climate you operate in, and a bunch of other things specific to your business. I can't even begin to guess at that without knowing a lot more about your business. On top of all that you would need to factor inflation into that, and that itself is hard even for economists.

What else can you tell us about your business and your data and what it means?

I work for a school district and the data I collected and shown above is from a once-a-month expenditure summary report I run and paste into a tab in my Excel file. I then have a separate tab, the one above, to pull all the data via my formulas into the categories shown.
In that same tab, I have 2 tables,, one with the data that is correct and current and pulled from the expenditure summary report and one that is my forecasting table, where I'm forecasting what the future months may look like. Then each month when I run the expenditure report for the month that just ended, I correct my forecasting table to reflect what the top table that pulled data from the expenditure report shows. It sounds redundant to have the 2 tables show the same data line by line but that's just what my boss requested. The forecasting doesn't need to be complicated or even accurate, I just wanted to get close if possible. I didn't see myself doing that only on 1 month of data, which is why I thought I should include the last year of data too.
I've included a link to my entire spreadsheet.
Ed Fund FY25 copy.xlsx

 

[6StringJazzer]

OK, let me try to recap. Your data jumps all over the place but without knowing why, it's not possible to do a reasonable forecast. Can you answer the "why" to any of these?

Examples:

• "Wages" are between $4-5M most months but in December and May it nearly doubles. It's not possible to do even a reasonable forecast without knowing why that happens. "Benefits" follows the same pattern presumably because they are tightly correlated to staffing level.
• "Utilities and maintenance" jumps around wildly, from a low of $5,700 to a high of $27,700. There is no apparent pattern to this. Without knowing why, it can't be forecasted.
• "Other" is usually under $7,000 but then in March it goes to an astronomical $76,700. There is no apparent pattern to this. Without knowing why, it can't be forecasted.
• "Supplies" jumps from a low of 64K to a high of 220K, a huge span. There is no apparent pattern to this. Without knowing why, it can't be forecasted.

The only thing stable enough to forecast is Transfers.

The forecasting doesn't need to be complicated or even accurate, I just wanted to get close if possible.

This isn't about being complicated or accurate, it's about doing a forecast that makes any sense at all. Given only this data if I had to do a forecast, I would do one of two things:

• Use a monthly average with an escalation of 3%. If I do that the forecast for some months are going to be way too high and some will be way too low, and it is just not a useful forecast.
• Just use the same figures month-by-month with an escalation of 3%. This assumes that whatever makes the numbers change from month to month happens in the same months every year. But I don't know that, and if that's wrong then the whole forecast is useless.

 

[KatWil]

OK, let me try to recap. Your data jumps all over the place but without knowing why, it's not possible to do a reasonable forecast. Can you answer the "why" to any of these?

Examples:

• "Wages" are between $4-5M most months but in December and May it nearly doubles. It's not possible to do even a reasonable forecast without knowing why that happens. "Benefits" follows the same pattern presumably because they are tightly correlated to staffing level.
• "Utilities and maintenance" jumps around wildly, from a low of $5,700 to a high of $27,700. There is no apparent pattern to this. Without knowing why, it can't be forecasted.
• "Other" is usually under $7,000 but then in March it goes to an astronomical $76,700. There is no apparent pattern to this. Without knowing why, it can't be forecasted.
• "Supplies" jumps from a low of 64K to a high of 220K, a huge span. There is no apparent pattern to this. Without knowing why, it can't be forecasted.

The only thing stable enough to forecast is Transfers.


This isn't about being complicated or accurate, it's about doing a forecast that makes any sense at all. Given only this data if I had to do a forecast, I would do one of two things:

• Use a monthly average with an escalation of 3%. If I do that the forecast for some months are going to be way too high and some will be way too low, and it is just not a useful forecast.
• Just use the same figures month-by-month with an escalation of 3%. This assumes that whatever makes the numbers change from month to month happens in the same months every year. But I don't know that, and if that's wrong then the whole forecast is useless.

I can answer one of the whys more clearly and that is Wages & Benefits. It nearly doubled in May & December since those are 3 pay period months for our school district. This fiscal year it will be November & May. Hence why benefits went up too,
Utilities, Other & Supplies There's really no apparent pattern because these categories include anything from reimbursements, gym supplies, software licensing, equipment, printer/copier services, etc.
thanks for the information

 

[6StringJazzer]

There's really no apparent pattern

You can't forecast a random number. The best you can hope for is averaging it out and hope that the end-of-year total is about the same.

Here are graphs of your data, which illustrate how challenging your data is to forecast. Looking at these charts, how you would expect the curve to continue for another 12 months? (I didn't notice before--how is Services negative in the last month?)