ARIMA,ARMA or SARIMA anyone?

[nelsok]

Has anyone used either ARIMA, ARMA or SARIMA in excel before? If so could you provide some basic help. I have not used these type of models before and I need to use one now.


Thanks.

 

[Joe Was]

Look at the Worksheet Functions: SERIESSUM, FORECAST, GROWTH, LINEST, TREND, LOGEST or see this link to a MS Movie:

http://office.microsoft.com/en-us/excel/HA010929231033.aspx?pid=CL100570551033



ARIMA: AutoRegressive Intergrated Moving Average, came out of some work done in the 1960's and can be a good tool, but it and its derivatives take two variables and uses them as constants, which is the downfall for most users.

A1=Total Sales as Units or Transactions of a previous year
B1=1st percent portion of the bracketed pattern to a previous year
C1= 2nd percent portion of the bracketed pattern to a previous year
D1=3rd percent portion of the bracketed pattern to a previous year

[B through D are the portion of the year that are the first 1/3 of the median sales volume, middle 1/3 and last 1/3.]

To predict the sales volume this year per unit of sales per unit of time indicated in a range [each cell in this range is indicated by "t" in the formula below], corresponding to each cell in that range of time units, by a formula in a corresponding cell is:

=B1*A1(t-3)+C1*A1(t-2)+D1*A1(t-1)

You could Say have the first part of the year have 1/8th of the total sales, the middle 5/8ths and the last 2/8ths [1+5+2=8 where 8/8ths = 1 or the whole year!]

The total sales in that year was 10,000 units

Then
month 1 cumulative sales of year one = 1/8*10,000(1-3)+5/8*10,000(1-2)+2/8*10,000(1-1) or 8,750 below that reference years total sales at that month point in the base referenced data year.

month 3 cumulative sales of year three= 1/8*10,000(3-3)+5/8*10,000(3-2)+2/8*10,000(3-1) or 11,250 above that reference years sales at that month point in the base referenced data year.


Note: You could use Julian days for "t" just calculate the Julian days to the date you want to test, from the period ending with the last day of your reference year or the next day past that years data.

 

[psjoyce]

hello .. l just came across this thread - i'm trying to predict two years worth of monthly volumes using one years worth of monthly volumes. is there a "best fit" formula for this type of "forecast"?