Calculate a trendline forecast

[torkattack]

Hello,
This is my first post. I found an article on Microsoft Office Online from MrExcel about calculating a forecast for Sales based on historical data. I tried this LINEST formula by selecting two cells side by side and entering the =linest(c2:c35) etc. This formula does not seem to work for me and I'm wondering if it is becasue I'm using Excel 2003. Does anyonw know of a formula to forescast Sales in Excel 2003. I have never done this kind of formula before, so please try to make your explanations simple for me. Thanks in advance. Lori

 

[cornflakegirl]

Welcome to the board.

LINEST works fine in 2003. It comes up with a straight line of best fit (using least squares) for your existing data. The equation for such a line is y=mx+b, and LINEST returns the values of m and b (gradient and intercept).

This sort of line can be used to forecast sales, but it's only really useful if you have a pronounced trend that you expect to continue. If you need to factor in things like seasonality, it becomes less useful.

What is it that you are actually trying to do?

 

[torkattack]

Thanks Emma,
I have three years of historical data by month for Retail Sales. I would like to predict the last quarter sales for this year based on historical data.
I did a spreadsheet with Col. A being the Month number such as 1,2,3.. and Column B being the date such as Jan-08, Feb-08... and Column C being the actual Sales number such as $10,000, $12,000 etc. I've never used this formula before so I really don't know any of the steps to perform the action to get results. I guess what I really need is to be shown how to do this, is that possible? Thanks Lori

 

[cornflakegirl]

Okay. Well, if you're sure that your data are suitable for a straight line trendline, then I would recommend that you use the TREND function. This uses the same methodology as LINEST, but it will predict you the new values that you want, instead of just giving you the equation for the line. The Help entry is good for this, especially if you scroll down to the example. Have a go and shout if you get stuck.

 

[torkattack]

Thanks Emma,
I was able to figure out both the TREND and the LINEST; however neither one gave me the result I was looking for. The predictions for future sales were way off based on the history. I think this is probably because I have seasonal changes in sales. Anyow, I do very much appreciate the help you have given me. Lori

 

[torkattack]

Hi again Emma,

I spent some time today familiarising myself with the TREND and LINEST functions. I can now see that they definately are not able to predict future sales the way I want them to. Our business is seasonal and the majority of Sales are in the last quarter related to the holiday season. Is there a function that takes seasonal fluctuations into account? Thanks Lori

 

[cornflakegirl]

Not that I'm aware of - it would make my job easier too.

My usual methodology would be to maybe use TREND to predict the full year's sales (or more likely, whatever huge target the marketing team have agreed on!) then determine the % of sales that fall in each month past on historical data.

Just found this site: http://www.exceluser.com/solutions/seasonality-sales.htm which talks about deseasonalising sales by using a moving average. Vaguely remember that technique from my CIMA training. I guess you could do that, then use TREND, then reseasonalise?

 

[torkattack]

Thanks again Emma,

I will take soem time and experiment with the info you gave me.
You've been a great help! Lori