Original Data Equated Day Factors Holiday Factors Normalized Data Initial Seasonal Factors SeasonallyAdjusted Data Initial SeasonallyAdjusted Data Initial Growth Rate Adjustments Events ID: 760406
Download Presentation The PPT/PDF document "Chapter 6: Estimating Trend" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
Chapter 6: Estimating Trend
Original Data
Equated Day
Factors
Holiday Factors
Normalized Data
Initial Seasonal Factors
Seasonally-Adjusted Data:
Initial
Seasonally-Adjusted Data:
Initial
Growth Rate
(Adjustments)
Events
(Adjustments)
Seasonally-Adjusted Data:
Final
Growth-Adj Seasonal Factors
1
6 -
Slide2Estimating Trend
This chapter will walk through the manual process of estimating trend. It will also describe how to modify and update trend estimates.
IntroductionEstimating TrendModifying TrendUpdating Trend – Monthly ProcessFinal Notes
Introduction
IntroductionEstimating TrendModifying TrendUpdating TrendFinal Notes
2
6 -
Slide3Development of the “Final Seasonal Factors” involves adjusting the data for growth & events.
Original Data
Equated Day
Factors
Holiday Factors
Normalized Data
Initial Seasonal Factors
Seasonally-Adjusted Data:
Initial
Seasonally-Adjusted Data:
Initial
Growth Rate
(Adjustments)
Events
(Adjustments)
Seasonally-Adjusted Data:
Final
Growth-Adj Seasonal Factors
Estimating Trend
Introduction
Estimating Trend
Modifying Trend
Updating
Trend
Final Notes
3
6 -
Slide4When you estimate growth & events you are estimating trend.
Growth Rate
(Adjustments)
Events
(Adjustments)
Estimating Trend
=
Estimating Trend
Introduction
Estimating Trend
Modifying Trend
Updating
Trend
Final Notes
4
6 -
Slide5Estimating Trend
Estimate Trend
The development of growth-adjusted seasonal factors, and the estimation of trend, is a feedback loop.
Seasonally-Adjusted Data:
Initial
Seasonally-Adjusted Data:
Final
Growth-Adjusted Seasonal Factors
Introduction
Estimating Trend
Modifying Trend
Updating
Trend
Final Notes
5
6 -
Slide6Estimating Trend
There are a number of “Rules of Thumb” to bear in mind as we walk through the process of estimating trend.
Limit trend changes to 3-4 per year.Check annual actual vs estimate difference.Follow big spikes when necessary.Events are more common than growth rate changes.Estimate a few years at a time.Trend estimates are best guess; art and science.
Estimating Trend
IntroductionEstimating TrendModifying TrendUpdating TrendFinal Notes
6
6 -
Slide7Estimating Trend
Ideally, the trend estimate inputs, the actual vs estimate comparison, and the chart, all just fit onto a single screen view.
IntroductionEstimating TrendModifying TrendUpdating TrendFinal Notes
TrendModel: Trend
7
6 -
Slide8Estimating Trend
And we’ll start with a clean slate, with growth set at 0%, no events, no Start point showing on the chart, & with our using the “Initial” seasonal factors.
IntroductionEstimating TrendModifying TrendUpdating TrendFinal Notes
TrendModel: Trend
8
6 -
Slide9Estimating Trend
At first glance, it looks like this period can be described as basically flat, with a step function after 9/11 & another in mid-2002.
Introduction
Estimating TrendModifying TrendUpdating TrendFinal Notes
TrendModel: Trend
9
6 -
Slide10Estimating Trend
But such a depiction leaves many significant gaps between the estimated trendline and the actuals.
Introduction
Estimating TrendModifying TrendUpdating TrendFinal Notes
TrendModel: Trend
10
6 -
Slide11Estimating Trend
Inserting a few more step functions and a couple of changes in growth rate appears to align better with the data behavior.
Introduction
Estimating TrendModifying TrendUpdating TrendFinal Notes
TrendModel: Trend
11
6 -
Slide12Estimating Trend
To capture this overall interpretation, we start with the growth rate remaining set at
0.0%, and the Start point bumped up to 25.5 B.
IntroductionEstimating TrendModifying TrendUpdating TrendFinal Notes
TrendModel: Trend
12
6 -
Slide13Estimating Trend
It looks like we would do well to drop the trendline in May 2001, to a level just above 24 B; we do this by inserting a “
-5.0%” event in May.
IntroductionEstimating TrendModifying TrendUpdating TrendFinal Notes
TrendModel: Trend
13
6 -
Slide14Estimating Trend
With 9/11, the level jumps to around 28 B, achieved by inserting a
+15% event in September.
IntroductionEstimating TrendModifying TrendUpdating TrendFinal Notes
TrendModel: Trend
14
6 -
Slide15Estimating Trend
But the 9/11 spike was huge, so the September bump is increased to
+45% to match, then a -20% drop is inserted in October, to get back to the 28 B level.
IntroductionEstimating TrendModifying TrendUpdating TrendFinal Notes
TrendModel: Trend
15
6 -
Slide16Estimating Trend
This looks pretty good for 2001, but notice that there’s a 0.9% difference between actuals and estimates for the year. How can we lower this?
Introduction
Estimating TrendModifying TrendUpdating TrendFinal Notes
TrendModel: Trend
16
6 -
Slide17Estimating Trend
The difference is lowered by further reducing the May event, from
-5.0% to–5.5%.
IntroductionEstimating TrendModifying TrendUpdating TrendFinal Notes
TrendModel: Trend
17
6 -
Slide18Estimating Trend
2002 starts fine. A
+14.0% event is inserted in June to capture much of the increase; growth is dropped to -8.0% in July to align with growth thru 2003.
IntroductionEstimating TrendModifying TrendUpdating TrendFinal Notes
TrendModel: Trend
18
6 -
Slide19Estimating Trend
Clearly we need to capture the July spike, done here by inserting a
+30.0% jump in July. But where does August’s precise-looking -23.1% come from?
IntroductionEstimating TrendModifying TrendUpdating TrendFinal Notes
TrendModel: Trend
19
6 -
Slide20Estimating Trend
Employing the Event Reversal Formula ensures the trendline returns to the identical level it was at before.
Introduction
Estimating TrendModifying TrendUpdating TrendFinal Notes
Event Reversal Formula
= 1 / (1 + Event%) - 1
TrendModel: Trend
20
6 -
Slide21Estimating Trend
The July event, and reversal, help bring the total estimate and actual for the year to nearly match; but it’s still high – for the second year in a row.
Introduction
Estimating TrendModifying TrendUpdating TrendFinal Notes
TrendModel: Trend
21
6 -
Slide22Estimating Trend
We begin addressing the 2-year highs by further dropping Oct 2001, reducing it from
-20.0% to -21.0%; the Jun ‘02 event must be raised to +15.5% to offset.
IntroductionEstimating TrendModifying TrendUpdating TrendFinal Notes
TrendModel: Trend
22
6 -
Slide23Estimating Trend
We insert a
-6.0% drop in Dec ‘02, later correcting it in Mar ‘03. The 2002 totals almost match perfectly, but now the 2003 estimate is too low.
IntroductionEstimating TrendModifying TrendUpdating TrendFinal Notes
TrendModel: Trend
23
6 -
Slide24Estimating Trend
So we insert another event, a
+5.0% increase for 3 months, starting in May ‘03. Now the 2003 totals are close as well.
IntroductionEstimating TrendModifying TrendUpdating TrendFinal Notes
TrendModel: Trend
24
6 -
Slide25Estimating Trend
A
+14.0% event to start 2004, combined with a -8.0% drop in June, captures the spike. The -8.0% underlying annual growth rate is still left unchanged.
IntroductionEstimating TrendModifying TrendUpdating TrendFinal Notes
TrendModel: Trend
25
6 -
Slide26Estimating Trend
The level in mid-2004 is almost identical to what it had been at the end of 2003, suggesting the -8.0% growth rate should perhaps be changed.
Introduction
Estimating TrendModifying TrendUpdating TrendFinal Notes
TrendModel: Trend
26
6 -
Slide27Estimating Trend
Here the growth rate is changed in January to 0.0%, and the event is dropped, from +14.0% to +12.0%.
Introduction
Estimating TrendModifying TrendUpdating TrendFinal Notes
TrendModel: Trend
27
6 -
Slide28Estimating Trend
To finish off 2004, a
+11.5% spike is inserted in Oct, and growth is shot up to +25% the next month. The ‘04 total difference is close, & offsets ‘01 & ‘03.
IntroductionEstimating TrendModifying TrendUpdating TrendFinal Notes
TrendModel: Trend
28
6 -
Slide29Estimating Trend
That’s a lot of work, and is admittedly quite subjective. But it aligns well with trend, the total ‘01 – ’04 difference is small, & shifts
are limited to 4 per year.
IntroductionEstimating TrendModifying TrendUpdating TrendFinal Notes
4
Growth Rate Changes
13 Events
Total Diff
=
0.0
TrendModel: Trend
29
6 -
Slide30Estimating Trend
Except for substantial volatility in late 2007, the 2005-07 period shows remarkably steady growth.
Introduction
Estimating TrendModifying TrendUpdating TrendFinal Notes
TrendModel: Trend
30
6 -
Slide31Estimating Trend
From mid-2007 thru mid-2009, sales swung enormously. The trendline attempts to pick up the swings while avoiding using too many changes.
Introduction
Estimating TrendModifying TrendUpdating TrendFinal Notes
TrendModel: Trend
31
6 -
Slide32Estimating Trend
Sales continue to plunge thru 2013, despite another short spike in the summer of 2011. But by the start of 2012, stability appears to have returned.
Introduction
Estimating TrendModifying TrendUpdating TrendFinal Notes
TrendModel: Trend
32
6 -
Slide33Estimating Trend
Sales volumes bottomed out in late 2014, and have grown in spurts since.
Introduction
Estimating TrendModifying TrendUpdating TrendFinal Notes
TrendModel: Trend
33
6 -
Slide34Estimating Trend
Estimates vs Actuals matched almost exactly for the total 2001-16 time period.
Introduction
Estimating TrendModifying TrendUpdating TrendFinal Notes
TrendModel: Trend
34
6 -
Slide35Estimating Trend
For the entire 16-year period, there are a total of 60 growth or event changes, close to 4 per year.
Introduction
Estimating TrendModifying TrendUpdating TrendFinal Notes
TrendModel: Trend
8 Growth Rate Changes
52 Events
35
6 -
Slide36Estimating Trend
The new weighted average seasonal factors are similar to the initial, but June is higher, and the average relies heavily on the later years.
Introduction
Estimating TrendModifying TrendUpdating TrendFinal Notes
TrendModel: Trend
36
6 -
Slide37Estimating Trend
Seasonal factors are split out into two 8-year periods, resulting in a significantly lower June factor in the earlier period.
IntroductionEstimating TrendModifying TrendUpdating TrendFinal Notes
TrendModel: Trend
37
6 -
Slide38Estimating Trend
The two new sets of seasonal factors are added to the table of seasonal factors found in the “Inputs” tab.
Introduction
Estimating TrendModifying TrendUpdating TrendFinal Notes
TrendModel: Inputs
38
6 -
Slide39Estimating Trend
Formulas in the “Calc” tab are updated to pick up both new sets of seasonal factors.
Introduction
Estimating TrendModifying TrendUpdating TrendFinal Notes
TrendModel: Calc
39
6 -
Slide40TrendModel: Trend
Estimating Trend
To start modifying the trend, we go back to having the original 2001-04 period on display.
Introduction
Estimating Trend
Modifying Trend
Updating
TrendFinal Notes
Modifying Trend
40
6 -
Slide41TrendModel: Trend
Estimating Trend
The “Seasonal Factors in Use” is revised to pick up the “Final” set of factors; the seasonally-adjusted data changes, though not by much.
Introduction
Estimating Trend
Modifying Trend
Updating
TrendFinal Notes
41
6 -
Slide42TrendModel: Trend
Estimating Trend
So long as the “Seasonal Factors in Use” is set on the “Final” factors, the seasonality measure is “live”, changing with every revision of growth & events.
Introduction
Estimating Trend
Modifying Trend
Updating
TrendFinal Notes
42
6 -
Slide43TrendModel: Trend
Estimating Trend
Event estimates were slightly modified to better capture the trend in the seasonally-adjusted data.
Introduction
Estimating Trend
Modifying Trend
Updating TrendFinal Notes
43
6 -
Slide44TrendModel: Trend
Estimating Trend
The growth rate and event estimates are modified for the entire 2001-16 period.
Introduction
Estimating Trend
Modifying Trend
Updating TrendFinal Notes
44
6 -
Slide45TrendModel: Inputs
Estimating Trend
We now turn to updating the model, how to handle new monthly data as it comes in.
Introduction
Estimating Trend
Modifying TrendUpdating TrendFinal Notes
Updating Trend
45
6 -
Slide46TrendModel: Calc
Estimating Trend
The “Calc” tab shows the new actuals coming in. The “Seasonally-Adjusted Data”, “Estimated Trend”, & “Forecasts” all need to be updated.
Introduction
Estimating Trend
Modifying Trend
Updating TrendFinal Notes
46
6 -
Slide47Estimating Trend
Updating the “Calc” tab can also be accomplished by simply copying down all the formulas from the prior month.
IntroductionEstimating TrendModifying TrendUpdating TrendFinal Notes
TrendModel: Calc
47
6 -
Slide48TrendModel: Trend
Estimating Trend
After updating the data, and expanding the chart to show 2017, a number of further tweaks in growth & event estimates seemed appropriate.
Introduction
Estimating Trend
Modifying TrendUpdating TrendFinal Notes
Before…
After:
48
6 -
Slide49Final Notes
Estimating Trend
We can see what the entire 2001-16 period looks like. After a long & marked decline, activity has been slowly rising in recent years.
IntroductionEstimating TrendModifying TrendUpdating TrendFinal Notes
TrendModel: Trend
49
6 -
Slide50Estimating Trend
Here’s a quick review of the “Rules of Thumb” to follow as you go through the process of estimating trend.
Limit trend changes to 3-4 per year.Check annual actual vs estimate difference.Follow big spikes when necessary.Events are more common than growth rate changes.Estimate a few years at a time.Trend estimates are best guess; art and science.
IntroductionEstimating TrendModifying TrendUpdating TrendFinal Notes
50
6 -