Seasonal Trend Lines

[Guest]

Hi All,

I can add lots of different trend lines to my Excel charts... except one...
I need to reflect seasonal fluctuations, so that whilst during the summer
months work levels tend to fall, the overall trend of the business is
increasing & I'd like to see a trend line showing the future expected growth,
but which still shows a drop-off during the summer.

Is this possible with a trend line, or do I need to use a function to create
the trend figures & then chart them?

Thanks in advance for your help

Regards
Colin Foster

 

[MartinW]

Hi Colin,

I'm assuming that you are using an XYscatter chart with your data,
let's say five years worth in say A1:B2000 or whatever.

Apply your trendline to all the data and format it to be say, blue.
Then right click on the chart goto Source data and add series.

Add a new series for each set of data at yearly intervals. Each
series accessing only the data from that year.
So that you will have five new series.

Now do the same for quarterly intervals so that you will have another
20 series.

Now apply a trendline to each of your yearly series and format them
to be say yellow.

Repeat the process for each of your Quarterly series and format
them to be red.

Hide each yearly and quarterly series so that all that is left on the
chart are the trendlines.

You should be left with a chart showing all of your data points
and three separate different colored trendlines showing quarterly
trends, yearly trends and five yearly trends.

HTH
Martin

 

[Jim Cone]

Colin,
It has been several years since I have done any seasonal forecasts.
However, I still don't believe there is any Built-in way to display seasonal
forecasts using an Excel chart.
I had to construct a separate set of adjustments and then plot that data.
Once you get the "amount" by which you adjust your forecast, it can
be enlightening to go back and see how accurate a seasonal forecast
would have been when compared to actual data.
There are always decisions to be made. <g>
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"Colin Foster" <
(e-mail address removed)>
wrote in message
Hi All,
I can add lots of different trend lines to my Excel charts... except one...
I need to reflect seasonal fluctuations, so that whilst during the summer
months work levels tend to fall, the overall trend of the business is
increasing & I'd like to see a trend line showing the future expected growth,
but which still shows a drop-off during the summer.

Is this possible with a trend line, or do I need to use a function to create
the trend figures & then chart them?
Thanks in advance for your help
Regards
Colin Foster

 

[Del Cotter]

I can add lots of different trend lines to my Excel charts... except one...
I need to reflect seasonal fluctuations, so that whilst during the summer
months work levels tend to fall, the overall trend of the business is
increasing & I'd like to see a trend line showing the future expected growth,
but which still shows a drop-off during the summer.

Chapter 20 of Michael Middleton's book _Data Analysis Using Excel_ has a
good discussion of how to do this.

 

[Mike Middleton]

And there's an example worksheet at
http://www.mikemiddleton.com/LinearTrendSeasonalForecast.xls
that shows a "hybrid" method based on one of the three approaches detailed
in Ch. 20 "Time Series Seasonality" (regression with indicator variables,
regression with lagged variables, and classical time series decomposition).

- Mike
www.MikeMiddleton.com