Using Add-in = Fourier Analysis

[MichaelRobert]

I want to analyze 36 months of historical sales figures to see if I can
identify any time-series that will allow me to project them into the future.

My rusty math memory tells me that I can use Fourier Analysis to find out
the underlying frequencies and amplitudes. The Fourier Analysis add-in - plus
what I have read on the web - tells me that the number of data points that I
use must be a power of 2, so my choices are to use 2, 4, 8, 16, or 32 months
of data (I use 32). I have also learned that the frequencies reported by the
Add-in are listed in ascending multiples of 1/(32 months). From this I can
calculate the value of each frequency returned by the Add-in.

What I don't yet know how to do with the output from Fourier Analysis is:
1. How do I interpret the number reported for each frequency? I assume that
each one is a measure of the amplitude of the reported frequency, but I do
not know how to handle the 'real' and 'imaginary' components of the number.
At first blush, I think I would ignore the imaginary, but ...
2. Once I have the frequencies and the amplitudes of each component
waveform, I assume that I select to use only the more significant (higher
amplitude) frequencies. Help on this selection process will be appreciated.
3. To make my projection, how do I make sure that I am applying the
waveforms at the correct part of their cycle. For example, I could start all
of the waveforms at t=0, but that is not necessarily where the cycle should
start.

Overall, it seems that I am looking for the equation for the full waveform
that Fourier Analysis is calculating; so that if I insert the output of the
FA into it, I will get the equation for the projected waveform.

Thanks for your help.

Mike

 

[Lars-Åke Aspelin]

I want to analyze 36 months of historical sales figures to see if I can
identify any time-series that will allow me to project them into the future.

My rusty math memory tells me that I can use Fourier Analysis to find out
the underlying frequencies and amplitudes. The Fourier Analysis add-in - plus
what I have read on the web - tells me that the number of data points that I
use must be a power of 2, so my choices are to use 2, 4, 8, 16, or 32 months
of data (I use 32). I have also learned that the frequencies reported by the
Add-in are listed in ascending multiples of 1/(32 months). From this I can
calculate the value of each frequency returned by the Add-in.

What I don't yet know how to do with the output from Fourier Analysis is:
1. How do I interpret the number reported for each frequency? I assume that
each one is a measure of the amplitude of the reported frequency, but I do
not know how to handle the 'real' and 'imaginary' components of the number.
At first blush, I think I would ignore the imaginary, but ...
2. Once I have the frequencies and the amplitudes of each component
waveform, I assume that I select to use only the more significant (higher
amplitude) frequencies. Help on this selection process will be appreciated.
3. To make my projection, how do I make sure that I am applying the
waveforms at the correct part of their cycle. For example, I could start all
of the waveforms at t=0, but that is not necessarily where the cycle should
start.

Overall, it seems that I am looking for the equation for the full waveform
that Fourier Analysis is calculating; so that if I insert the output of the
FA into it, I will get the equation for the projected waveform.

Thanks for your help.

Mike

For the amplitude you have to use both the real and the imaginary
part.
Use the IMABS function for this

For the phase you also have to use both the real and the imaginary
part.
Use the IMARGUMENT function for this

The more of the component that you use, the more accurate/detailed the
projection will be. But do remember that future always bring in new
facts, in addition to historical data, that will influence your
sales.

Hope this helps / Lars-Åke

 

[Shane Devenshire]

Hi,

Time series are often analysed using Box Jenkins, which Excel does not have.
However, you might look at

http://www.jaxworks.com/toc.htm

or various other sites.

 

[MichaelRobert]

Lars-Ake:

Thanks for the ideas. How/where would I apply the IMABS and IMARGUMENT
functions?

Mike

 

[Lars-Åke Aspelin]

Lets say you have your input data in A1:A32 and your output data in
B1:B32

In cell C1 you put
=IMABS(B1)

In cell D1 you put
=IMARGUMENT(B1)

Copy C11 down thru C3232

In cell E1 you put
=1/32*SUMPRODUCT(C$1:C$32*COS((ROW()-1)*(ROW($1:$32)-1)/16*PI()+(D$1$32)))

Note: This is an array formula that has to be confirmed by
CTRL+SHIFT+ENTER rather than just ENTER

Copy cell E1 down thru E32, and copy it further down ot get your
projection of future sales.

Here is an example with a "square wave" input.

A1:A8 = 1, A9:A16 = 0, A17:A24 = 1, A25:A32 = 0

You will get the following Fourier components
B1 = 16
B2 = 0
B3 = 2-10.05...i
B4,B5.B6 = 0
B7 = 2-2.993...i
B8, B9, B10 = 0
B11 = 2-1.336...i
B12, B13, B14 = 0
B15 = 2-0.397...i
B16 = 0
B17 = 0
B18-B32 are same as B16:B2 but with the opposite sign of the imaginary
part (the complex conjugate)

You will get the following amplitudes:
C1 = 16
C3 = 10.25166
C7 = 3.599905
C11 = 2.40538
C15 = 2.039182
C2, C4, C5, C6, C8, C9, C10, C12, C13, C14, C16, C17 = 0
C18:C32 are same as C16:C2

You will get the following "phases":
D3 = -1.37445
D7 = -0.98175
D11 = -0.58905
D15 = -0.19635
D1, D2, D4, D5, D6, D8, D9, D10, D12, D13, D14, D16, D17 = 0
D1832 are the same as D162

In column E, the restored data, you will get the same as in
column A except some minor rounding errors. If not, check the
formulas.

If you have all this setup and checked you can change your input data
in A1:A32 and make a new Fourier transform to get the data in B1:B32.

Hope this helps / Lars-Åke

 

[Herbert Seidenberg]

Excel 2007
Using free online curve fitting.
No Fourier Analysis required.
Example fits to y=a*sin(x+b)+c
http://www.mediafire.com/file/ojzioznn2fu/Cyclic.xlsx

 

[MichaelRobert]

Lars-Ake:

Many thanks. I can see my way forward now! It will be a few days before I
can get back into this project. I'll let you know how it works out.

Thanks again.

Mike

 

[MichaelRobert]

Herbert:

Thanks for the link. And for adding the series equation.

Mike

 

[Dana DeLouis]

I want to analyze 36 months of historical sales figures to see if I can

identify any time-series that will allow me to project them into the

future.

Hi. Just some thoughts. Fourier Analysis assumes your data is
periodic, so future projections would just be a repeat of your data.
It will not be able to show growth, or something similar, into the future.

The first output of Fourier is the zero frequency. The second line is
frequency 1, third line is frequency 2, etc.
The reason the first output is a real number is that that Sin( 0 ) is
zero, and cancels out the imaginary part.
Same for line 17 in the output, which is frequency 16.

Sin( (2 Pi/32)*16*x), reduces to Sin(Pi x) and is always zero, hence
line 17 is always real.

On your part..."to see if I can identify any time-series.."

Another option is to do a poor-mans version of a spectrum analyzer.
=IMABS(B1)

and copy down to Row 17. We do not need to look at the complex
conjugates that are further down.
Make the small adjustment here to divide by 32 in Row 1 and 17, and
divide by 16 in rows 2-16. (2*x / 32 -> x / 16)
Then do something like a bar chart on this data.

What this shows is the relative contribution of each frequency.
Suppose we had 36 data points instead of 32 here to keep the math
simple. Divide 36 by a few of our frequencies, say 1-12

{36., 18., 12., 9., 7.2, 6., 5.14, 4.5, 4., 3.6, 3.27, 3.}

The first row in our output is just our average sales, and should have a
value.

If we had a large value in Row 2(freq 1) compared to the other values,
then this would indicate a strong presence of a 36 month sales cycle.
All other values close to 0 would be considered "noise" and would likely
be dropped from the equation when reverted back into the time domain.

Suppose row 4 was larger then others (freq 3), then this would indicate
a strong presence of a 36/3 = 12 month sales cycle.

Anyway, hope this helps a little.

= = =
HTH
Dana DeLouis

 

[Dana DeLouis]

Just a side note if you were confused with the output.
There are a few different definitions of the Fourier equation depending
on what one is working on.
The equation Microsoft uses is more appropriate for analyzing signals
and such, and I believe can cause a little confusion when listing it
under "data analysis" (in my opinion).
That is why we have to divide by the size of the data to convert Excel's
output to a format to do "Data Analysis".
I like it the way it is, but thought I would mention it.
The program for Fourier is basically the same. It's just a matter of
setting a flag to adjust the output during computations.

= = =
Dana DeLouis

<snip>