Home 
Search 
Today's Posts 
#1




Forecast and Trends
If my data is cyclical in nature, what is the best function to use to
forecast my next year or two years of sales volume? (I have higher volumes in certain times of the year and I need to account for this when projecting my sales.) Thanks! 
#2




Forecast and Trends
Hi,
If your data is cyclical then you are probably best of to use a sinusoidal function. If there is an overall upward trend from period to period you might want to add a linear function. Thus I would recommend a function like: f(t) = at + b + c*sin(dt+e) Problem is the builtin functions for regression in Excel do not support such functions so you will need the Solver to perform the least squares method. For this you would need the column representing time to have numeric values or else you should provide an auxiliary column with consecutive numeric values (better off with 0, 1, ...). Say this is in column A:A starting from A2. You will need five cells for the five constants ae. Say these are in F2:J2. Next to your dependent variable, say in D2, enter and copy down the formula: =$F$2*A2+$G$2+$H$2*SIN($I$2*A2+$J$2) Next to it, in E2, enter the square difference of the dependent (assumed in column C:C) from the forecasted: =(D2C2)^2 Take the sum of column E:E and ask SOlver to minimize it by changing F2:J2. As this is a nonlinear problem and the builtin solver is not very industrial strength, your initial values in F2:J2 will have to be relatively close to the values you expect. Write back if you need further assistance. HTH Kostis Vezerides 
#3




Forecast and Trends
This stuff is great, but it might be a little over my head. Here is my data,
unfortunately I didn't follow how to do the equations. I sort of figured out how to use Solver, but I wasn't sure what data I need in what columns, since I only have two rows right now. If you can't help me any more, I understand but I want to say thank you for your help already Kostis! Month Year Default Claims Paid Jan04 17,414,897.94 Feb04 10,699,109.47 Mar04 18,332,334.50 Apr04 14,275,140.03 May04 12,305,352.33 Jun04 13,907,155.18 Jul04 11,963,018.44 Aug04 19,201,480.28 Sep04 15,623,457.98 Oct04 7,077,725.63 Nov04 15,740,422.12 Dec04 13,761,418.33 Jan05 21,340,245.83 Feb05 9,409,514.83 Mar05 10,572,805.35 Apr05 12,339,659.95 May05 11,986,746.47 Jun05 10,252,392.46 Jul05 12,416,685.61 Aug05 17,892,569.26 Sep05 26,618,694.92 Oct05 7,581,879.50 Nov05 15,579,836.07 Dec05 21,710,331.63 Jan06 21,665,556.58 Feb06 13,653,795.27 Mar06 14,457,680.21 Apr06 18,774,698.52 May06 17,775,539.97 Jun06 16,774,408.35 "vezerid" wrote: Hi, If your data is cyclical then you are probably best of to use a sinusoidal function. If there is an overall upward trend from period to period you might want to add a linear function. Thus I would recommend a function like: f(t) = at + b + c*sin(dt+e) Problem is the builtin functions for regression in Excel do not support such functions so you will need the Solver to perform the least squares method. For this you would need the column representing time to have numeric values or else you should provide an auxiliary column with consecutive numeric values (better off with 0, 1, ...). Say this is in column A:A starting from A2. You will need five cells for the five constants ae. Say these are in F2:J2. Next to your dependent variable, say in D2, enter and copy down the formula: =$F$2*A2+$G$2+$H$2*SIN($I$2*A2+$J$2) Next to it, in E2, enter the square difference of the dependent (assumed in column C:C) from the forecasted: =(D2C2)^2 Take the sum of column E:E and ask SOlver to minimize it by changing F2:J2. As this is a nonlinear problem and the builtin solver is not very industrial strength, your initial values in F2:J2 will have to be relatively close to the values you expect. Write back if you need further assistance. HTH Kostis Vezerides 
#4




Forecast and Trends
Stephanie,
now I see... Well, I don't think you should call this data cyclical. At first I thought you were talking about a product with seasonal behavior but this is not the case. Judging from the headers and having charted the data: We have an overall growth pattern but large fluctuations from month to month, which is to be expected. Problem is, the fluctuations are rather large and they do not follow a specific pattern. In this case we have two choices: linear and exponential, unless there exist some other marketdependent conditions which would dictate a different type of function, e.g. quadratic. I give you two equations: Linear: =122198.98*K2+13264930 Exponential: =12055159.54*EXP(0.0131488112808613*K2) In both cases, K2 should contain the number of months between the start of your data and the month you want the projection for. You can use the function DATEDIFF(date2,DATE(2004,1,1),"m") to calculate this. For date2 you should use DATE(yr,month,day), i.e something like DATE(2007,5,1) for May 2007. However, I am afraid this is as far as my statistics will go. The number you will produce with these formulas is an estimate, however with low confidence. Maybe one of the resident experts, like Jerry Lewis, will jump in and direct you further so that you can also calculate the plusorminus expected fluctuation from the projection. HTH Kostis Vezerides steph44haf wrote: This stuff is great, but it might be a little over my head. Here is my data, unfortunately I didn't follow how to do the equations. I sort of figured out how to use Solver, but I wasn't sure what data I need in what columns, since I only have two rows right now. If you can't help me any more, I understand but I want to say thank you for your help already Kostis! Month Year Default Claims Paid Jan04 17,414,897.94 Feb04 10,699,109.47 Mar04 18,332,334.50 Apr04 14,275,140.03 May04 12,305,352.33 Jun04 13,907,155.18 Jul04 11,963,018.44 Aug04 19,201,480.28 Sep04 15,623,457.98 Oct04 7,077,725.63 Nov04 15,740,422.12 Dec04 13,761,418.33 Jan05 21,340,245.83 Feb05 9,409,514.83 Mar05 10,572,805.35 Apr05 12,339,659.95 May05 11,986,746.47 Jun05 10,252,392.46 Jul05 12,416,685.61 Aug05 17,892,569.26 Sep05 26,618,694.92 Oct05 7,581,879.50 Nov05 15,579,836.07 Dec05 21,710,331.63 Jan06 21,665,556.58 Feb06 13,653,795.27 Mar06 14,457,680.21 Apr06 18,774,698.52 May06 17,775,539.97 Jun06 16,774,408.35 
#5




Forecast and Trends
steph44haf 
Later today I will put a link in the lower left corner of my web site to a workbook showing a simple method for using trend and seasonality to obtain your forecasts. And tomorrow evening I'll use your data as an example when I teach my Exec MBA class. Thanks.  Mike www.mikemiddleton.com "steph44haf" wrote in message ... This stuff is great, but it might be a little over my head. Here is my data, unfortunately I didn't follow how to do the equations. I sort of figured out how to use Solver, but I wasn't sure what data I need in what columns, since I only have two rows right now. If you can't help me any more, I understand but I want to say thank you for your help already Kostis! Month Year Default Claims Paid Jan04 17,414,897.94 Feb04 10,699,109.47 Mar04 18,332,334.50 Apr04 14,275,140.03 May04 12,305,352.33 Jun04 13,907,155.18 Jul04 11,963,018.44 Aug04 19,201,480.28 Sep04 15,623,457.98 Oct04 7,077,725.63 Nov04 15,740,422.12 Dec04 13,761,418.33 Jan05 21,340,245.83 Feb05 9,409,514.83 Mar05 10,572,805.35 Apr05 12,339,659.95 May05 11,986,746.47 Jun05 10,252,392.46 Jul05 12,416,685.61 Aug05 17,892,569.26 Sep05 26,618,694.92 Oct05 7,581,879.50 Nov05 15,579,836.07 Dec05 21,710,331.63 Jan06 21,665,556.58 Feb06 13,653,795.27 Mar06 14,457,680.21 Apr06 18,774,698.52 May06 17,775,539.97 Jun06 16,774,408.35 "vezerid" wrote: Hi, If your data is cyclical then you are probably best of to use a sinusoidal function. If there is an overall upward trend from period to period you might want to add a linear function. Thus I would recommend a function like: f(t) = at + b + c*sin(dt+e) Problem is the builtin functions for regression in Excel do not support such functions so you will need the Solver to perform the least squares method. For this you would need the column representing time to have numeric values or else you should provide an auxiliary column with consecutive numeric values (better off with 0, 1, ...). Say this is in column A:A starting from A2. You will need five cells for the five constants ae. Say these are in F2:J2. Next to your dependent variable, say in D2, enter and copy down the formula: =$F$2*A2+$G$2+$H$2*SIN($I$2*A2+$J$2) Next to it, in E2, enter the square difference of the dependent (assumed in column C:C) from the forecasted: =(D2C2)^2 Take the sum of column E:E and ask SOlver to minimize it by changing F2:J2. As this is a nonlinear problem and the builtin solver is not very industrial strength, your initial values in F2:J2 will have to be relatively close to the values you expect. Write back if you need further assistance. HTH Kostis Vezerides 
#6




Forecast and Trends
Mike,
Just checked your two forecasting spreadsheets & they're most informative & useful  thanks for making them available to the wider community. I'd checked this thread in the hope that it could resolve an issue that I've been asked to look into... Can a trendline forecast seasonal fluctuations? For example, if you have an icecream business, you would expect (Global Warming & current weather conditions here in the UK notwithstanding!!) higher sales in the summer than in the winter. At the same time, if the business was growing, you'd expect a general upward trend. What I've been asked is is it possible to show both the general upward trend INCLUDING the expected seasonal highs & lows? I wonder whether, rather than "just" using a trendline applied to the chart, it would be possible to create a formula or use an existing function within the Excel data & then simply chart that? Unfortunately, I fell asleep during statistics lessons at school, hence the posting here! Regards & thanks, in advance, for any suggestions. Regards Colin Foster "Mike Middleton" wrote: steph44haf  Later today I will put a link in the lower left corner of my web site to a workbook showing a simple method for using trend and seasonality to obtain your forecasts. And tomorrow evening I'll use your data as an example when I teach my Exec MBA class. Thanks.  Mike www.mikemiddleton.com "steph44haf" wrote in message ... This stuff is great, but it might be a little over my head. Here is my data, unfortunately I didn't follow how to do the equations. I sort of figured out how to use Solver, but I wasn't sure what data I need in what columns, since I only have two rows right now. If you can't help me any more, I understand but I want to say thank you for your help already Kostis! Month Year Default Claims Paid Jan04 17,414,897.94 Feb04 10,699,109.47 Mar04 18,332,334.50 Apr04 14,275,140.03 May04 12,305,352.33 Jun04 13,907,155.18 Jul04 11,963,018.44 Aug04 19,201,480.28 Sep04 15,623,457.98 Oct04 7,077,725.63 Nov04 15,740,422.12 Dec04 13,761,418.33 Jan05 21,340,245.83 Feb05 9,409,514.83 Mar05 10,572,805.35 Apr05 12,339,659.95 May05 11,986,746.47 Jun05 10,252,392.46 Jul05 12,416,685.61 Aug05 17,892,569.26 Sep05 26,618,694.92 Oct05 7,581,879.50 Nov05 15,579,836.07 Dec05 21,710,331.63 Jan06 21,665,556.58 Feb06 13,653,795.27 Mar06 14,457,680.21 Apr06 18,774,698.52 May06 17,775,539.97 Jun06 16,774,408.35 "vezerid" wrote: Hi, If your data is cyclical then you are probably best of to use a sinusoidal function. If there is an overall upward trend from period to period you might want to add a linear function. Thus I would recommend a function like: f(t) = at + b + c*sin(dt+e) Problem is the builtin functions for regression in Excel do not support such functions so you will need the Solver to perform the least squares method. For this you would need the column representing time to have numeric values or else you should provide an auxiliary column with consecutive numeric values (better off with 0, 1, ...). Say this is in column A:A starting from A2. You will need five cells for the five constants ae. Say these are in F2:J2. Next to your dependent variable, say in D2, enter and copy down the formula: =$F$2*A2+$G$2+$H$2*SIN($I$2*A2+$J$2) Next to it, in E2, enter the square difference of the dependent (assumed in column C:C) from the forecasted: =(D2C2)^2 Take the sum of column E:E and ask SOlver to minimize it by changing F2:J2. As this is a nonlinear problem and the builtin solver is not very industrial strength, your initial values in F2:J2 will have to be relatively close to the values you expect. Write back if you need further assistance. HTH Kostis Vezerides 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
How do I forecast future payments by analyzing past payments?  Excel Worksheet Functions 