Extrapolate? Forecast? Trend? I don't know what to call this problem.

[Emma Eve]

Hello and thank you for reading my post.

This is what I need to do: I have data for three months. I need to determine what data the remaining 9 months will have based on the first three months. For example, January I had 1 credit memo. February I had 1 credit memo. March I had 3 credit memos. Based on this how many credit memos could I expect to have in April? How many could I expect to have in May, June and so on? Then I can sum the months to see what I can expect at year end based on actual and projected numbers. Is this possible in Excel? My co-worker says guess, but I want to have something concrete to put behind the numbers if asked where I got them.

Month # of Credit Memos
Jan 1
Feb 1
Mar 3
Apr ?
May ?

 

[Joe4]

Excel can do pretty much anything that you tell it to, but you need to define the rules.
The problem with a three month sample, is it really does not give a enough of a basis for a good estimation of future growth.

For example, in your example, nothing changed form January to February, then it increased in March. But that increase, is it that it tripled, or just increased by 2 (so depending on that answer, April could be 5 or 9).
Or perhaps even 3, if we assume that it is the same for two months before increasing.

And what if you had an example like this?
Jan 100
Feb 120
Mar 80

So first it went up, then it went down. So what do we think April is going to do? What basis do we have to make that decision?

Excel can do Forceasting and Trending, which may be of help, once you figure some things out.
Have a look here: https://magoosh.com/excel/how-to-use-the-forecast-and-trend-function-in-excel/
There are also other Tutorials and YouTube vidoes on the subject.

 

[ekrause]

The logic can easily be built into Excel but you need to come up with the logic.

Here are some logic examples:

1. Average - based on prior months' actuals you would expect 1.66 credit memos per month
2. Month of Quarter - April, July, October follow Jan's value; May, August, November follow Feb's value; etc.
3. Prior Year - need more data here but seasonality might play a larger part e.g. 1st quarter could be the least busy credit memo quarter but you aren't capturing that data
4. Credit Memo per [metric] - number of credit memos per orders January had 3 orders 1 credit memo for instance
5. ...

There are tons of other possibilities. You need to figure out the methodology you want to forecast future months.

 

[Emma Eve]

Well, from the website you sent me I think TREND is the best. I can forego what happens each month. The most important prediction is the total credit memos at year end.