Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 built-in 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 a-e. 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: =(D2-C2)^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 built-in 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 Jan-04 17,414,897.94 Feb-04 10,699,109.47 Mar-04 18,332,334.50 Apr-04 14,275,140.03 May-04 12,305,352.33 Jun-04 13,907,155.18 Jul-04 11,963,018.44 Aug-04 19,201,480.28 Sep-04 15,623,457.98 Oct-04 7,077,725.63 Nov-04 15,740,422.12 Dec-04 13,761,418.33 Jan-05 21,340,245.83 Feb-05 9,409,514.83 Mar-05 10,572,805.35 Apr-05 12,339,659.95 May-05 11,986,746.47 Jun-05 10,252,392.46 Jul-05 12,416,685.61 Aug-05 17,892,569.26 Sep-05 26,618,694.92 Oct-05 7,581,879.50 Nov-05 15,579,836.07 Dec-05 21,710,331.63 Jan-06 21,665,556.58 Feb-06 13,653,795.27 Mar-06 14,457,680.21 Apr-06 18,774,698.52 May-06 17,775,539.97 Jun-06 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 built-in 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 a-e. 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: =(D2-C2)^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 built-in 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 market-dependent 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 plus-or-minus 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 Jan-04 17,414,897.94 Feb-04 10,699,109.47 Mar-04 18,332,334.50 Apr-04 14,275,140.03 May-04 12,305,352.33 Jun-04 13,907,155.18 Jul-04 11,963,018.44 Aug-04 19,201,480.28 Sep-04 15,623,457.98 Oct-04 7,077,725.63 Nov-04 15,740,422.12 Dec-04 13,761,418.33 Jan-05 21,340,245.83 Feb-05 9,409,514.83 Mar-05 10,572,805.35 Apr-05 12,339,659.95 May-05 11,986,746.47 Jun-05 10,252,392.46 Jul-05 12,416,685.61 Aug-05 17,892,569.26 Sep-05 26,618,694.92 Oct-05 7,581,879.50 Nov-05 15,579,836.07 Dec-05 21,710,331.63 Jan-06 21,665,556.58 Feb-06 13,653,795.27 Mar-06 14,457,680.21 Apr-06 18,774,698.52 May-06 17,775,539.97 Jun-06 16,774,408.35 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 Jan-04 17,414,897.94 Feb-04 10,699,109.47 Mar-04 18,332,334.50 Apr-04 14,275,140.03 May-04 12,305,352.33 Jun-04 13,907,155.18 Jul-04 11,963,018.44 Aug-04 19,201,480.28 Sep-04 15,623,457.98 Oct-04 7,077,725.63 Nov-04 15,740,422.12 Dec-04 13,761,418.33 Jan-05 21,340,245.83 Feb-05 9,409,514.83 Mar-05 10,572,805.35 Apr-05 12,339,659.95 May-05 11,986,746.47 Jun-05 10,252,392.46 Jul-05 12,416,685.61 Aug-05 17,892,569.26 Sep-05 26,618,694.92 Oct-05 7,581,879.50 Nov-05 15,579,836.07 Dec-05 21,710,331.63 Jan-06 21,665,556.58 Feb-06 13,653,795.27 Mar-06 14,457,680.21 Apr-06 18,774,698.52 May-06 17,775,539.97 Jun-06 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 built-in 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 a-e. 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: =(D2-C2)^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 built-in 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 ice-cream 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 Jan-04 17,414,897.94 Feb-04 10,699,109.47 Mar-04 18,332,334.50 Apr-04 14,275,140.03 May-04 12,305,352.33 Jun-04 13,907,155.18 Jul-04 11,963,018.44 Aug-04 19,201,480.28 Sep-04 15,623,457.98 Oct-04 7,077,725.63 Nov-04 15,740,422.12 Dec-04 13,761,418.33 Jan-05 21,340,245.83 Feb-05 9,409,514.83 Mar-05 10,572,805.35 Apr-05 12,339,659.95 May-05 11,986,746.47 Jun-05 10,252,392.46 Jul-05 12,416,685.61 Aug-05 17,892,569.26 Sep-05 26,618,694.92 Oct-05 7,581,879.50 Nov-05 15,579,836.07 Dec-05 21,710,331.63 Jan-06 21,665,556.58 Feb-06 13,653,795.27 Mar-06 14,457,680.21 Apr-06 18,774,698.52 May-06 17,775,539.97 Jun-06 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 built-in 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 a-e. 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: =(D2-C2)^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 built-in 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 |