/
Chapter 6:  Estimating Trend Chapter 6:  Estimating Trend

Chapter 6: Estimating Trend - PowerPoint Presentation

ellena-manuel
ellena-manuel . @ellena-manuel
Follow
343 views
Uploaded On 2019-06-27

Chapter 6: Estimating Trend - PPT Presentation

Original Data Equated Day Factors Holiday Factors Normalized Data Initial Seasonal Factors SeasonallyAdjusted Data Initial SeasonallyAdjusted Data Initial Growth Rate Adjustments Events ID: 760406

estimating trend trendfinal notes trend estimating notes trendfinal trendmodel trendupdating trendmodifying introduction growth introductionestimating factors data event seasonal modifying

Share:

Link:

Embed:

Download Presentation from below link

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.


Presentation Transcript

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 -

Slide2

Estimating 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 -

Slide3

Development 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 -

Slide4

When 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 -

Slide5

Estimating 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 -

Slide6

Estimating 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 -

Slide7

Estimating 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 -

Slide8

Estimating 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 -

Slide9

Estimating 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 -

Slide10

Estimating 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 -

Slide11

Estimating 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 -

Slide12

Estimating 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 -

Slide13

Estimating 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 -

Slide14

Estimating 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 -

Slide15

Estimating 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 -

Slide16

Estimating 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 -

Slide17

Estimating 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 -

Slide18

Estimating 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 -

Slide19

Estimating 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 -

Slide20

Estimating 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 -

Slide21

Estimating 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 -

Slide22

Estimating 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 -

Slide23

Estimating 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 -

Slide24

Estimating 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 -

Slide25

Estimating 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 -

Slide26

Estimating 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 -

Slide27

Estimating 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 -

Slide28

Estimating 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 -

Slide29

Estimating 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 -

Slide30

Estimating 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 -

Slide31

Estimating 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 -

Slide32

Estimating 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 -

Slide33

Estimating Trend

Sales volumes bottomed out in late 2014, and have grown in spurts since.

Introduction

Estimating TrendModifying TrendUpdating TrendFinal Notes

TrendModel: Trend

33

6 -

Slide34

Estimating Trend

Estimates vs Actuals matched almost exactly for the total 2001-16 time period.

Introduction

Estimating TrendModifying TrendUpdating TrendFinal Notes

TrendModel: Trend

34

6 -

Slide35

Estimating 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 -

Slide36

Estimating 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 -

Slide37

Estimating 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 -

Slide38

Estimating 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 -

Slide39

Estimating 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 -

Slide40

TrendModel: 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 -

Slide41

TrendModel: 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 -

Slide42

TrendModel: 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 -

Slide43

TrendModel: 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 -

Slide44

TrendModel: 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 -

Slide45

TrendModel: 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 -

Slide46

TrendModel: 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 -

Slide47

Estimating 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 -

Slide48

TrendModel: 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 -

Slide49

Final 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 -

Slide50

Estimating 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 -