A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Charts and Charting
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Seasonal Trend Lines



 
 
Thread Tools Display Modes
  #1  
Old July 27th, 2007, 10:38 AM posted to microsoft.public.excel.charting
Colin Foster[_2_]
external usenet poster
 
Posts: 18
Default Seasonal Trend Lines

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
  #2  
Old July 27th, 2007, 12:14 PM posted to microsoft.public.excel.charting
MartinW
external usenet poster
 
Posts: 848
Default Seasonal Trend Lines

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


"Colin Foster" 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



  #3  
Old July 27th, 2007, 03:37 PM posted to microsoft.public.excel.charting
Jim Cone
external usenet poster
 
Posts: 882
Default Seasonal Trend Lines


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"

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
  #4  
Old July 27th, 2007, 05:38 PM posted to microsoft.public.excel.charting
Del Cotter
external usenet poster
 
Posts: 553
Default Seasonal Trend Lines

On Fri, 27 Jul 2007, in microsoft.public.excel.charting,
Colin Foster said:
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.

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.
  #5  
Old July 27th, 2007, 11:44 PM posted to microsoft.public.excel.charting
Mike Middleton
external usenet poster
 
Posts: 653
Default Seasonal Trend Lines

And there's an example worksheet at
http://www.mikemiddleton.com/LinearT...alForecast.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

"Del Cotter" wrote in message
...
On Fri, 27 Jul 2007, in microsoft.public.excel.charting,
Colin Foster said:
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.

--
Del Cotter
NB Personal replies to this post will send email to
,
which goes to a spam folder-- please send your email to del3 instead.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:49 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.