Adding It All Up Original Data Equated Day Factors Holiday Factors Normalized Data Initial Seasonal Factors SeasonallyAdjusted Data Initial SeasonallyAdjusted Data Initial Growth Rate ID: 760341
Download Presentation The PPT/PDF document "Chapter 3: Normalizing the Data –" 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 3: Normalizing the Data –
Adding It All Up
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
3 -
Slide2Normalizing the Data: Adding It All Up
Normalizing monthly data refers to the process of adjusting each month’s data so that every month is of equivalent length.
IntroductionNet Daily FactorsNormalization FactorsNormalized Data
Average
Month Lengths, 2016
1. Introduction
2
3 -
Slide3Normalizing the Data: Adding It All Up
How do we normalize the data?
IntroductionNet Daily FactorsNormalization FactorsNormalized Data
Multiply the EDFs by the Holiday Factors (all non-holidays have a “factor” of 1.00) to derive Net Daily Factors.Add up the Net Daily Factors for each month to arrive at each month’s length.Divide each month’s length by the average month’s length to arrive at a Normalization Factor for each month.Divide each month’s data by its Normalization Factor to express it as “Normalized Data”, where every month is of equal length, and now ready to be seasonalized.
3
3 -
Slide4Normalizing Data Template: Inputs
Normalizing the Data: Adding It All Up
In order to calculate the Net Daily Factors, we need to bring in the developed Equated Day Factors (EDFs) & Holiday Factors.
Introduction
Net Daily Factors
Normalization FactorsNormalized Data
2. Net Daily Factors
4
3 -
Slide5Normalizing the Data: Adding It All Up
Formulas in a “
Calc” tab pick up the EDFs & Holiday Factors for the entire covered period.
IntroductionNet Daily FactorsNormalization FactorsNormalized Data
Normalizing Data Template: Calc
Net
Month Length (Jan 2016):
19.45
5
3 -
Slide6Normalizing the Data: Adding It All Up
Net Daily Factors are calculated by simply multiplying each day’s EDF by it’s Holiday Factor; summing them arrives at the “true” Net Month Length.
Introduction
Net Daily FactorsNormalization FactorsNormalized Data
Normalizing Data Template: Calc
Net
Month Length (Jan 2016):
19.45
6
3 -
Slide7Normalizing the Data: Adding It All Up
Monthly and annual totals are calculated for the entire period, along with overall averages.
Introduction
Net Daily FactorsNormalization FactorsNormalized Data
Normalizing Data Template: Output
Totals by Year
Overall Totals
Totals by Month
7
3 -
Slide8Normalizing the Data: Adding It All Up
(Note: table does not capture 3
rd & 4th Friday factors.)
IntroductionNet Daily FactorsNormalization FactorsNormalized Data
Normalizing Data Template: Output
Monthly data can be put into a table to more easily observe how month lengths vary over time.
8
3 -
Slide9Normalizing the Data: Adding It All Up
Introduction
Net Daily FactorsNormalization FactorsNormalized Data
Normalizing Data Template: Output
Year lengths vary slightly, and leap years are not necessarily the longest.
9
3 -
Slide10Normalizing the Data: Adding It All Up
Introduction
Net Daily FactorsNormalization FactorsNormalized Data
Normalizing Data Template: Output
Month over month lengths can change significantly.
10
3 -
Slide11Normalizing the Data: Adding It All Up
Introduction
Net Daily FactorsNormalization FactorsNormalized Data
Normalizing Data Template: Output
Month lengths can also change dramatically year-over-year.
11
3 -
Slide12Normalizing the Data: Adding It All Up
Introduction
Net Daily FactorsNormalization FactorsNormalized Data
Normalizing Data Template: Output
Almost half the time, year-over-year month lengths change by 5% or more; more than 10% of the time, they change by 10% or more.
12
3 -
Slide13Normalizing the Data: Adding It All Up
IntroductionNet Daily FactorsNormalization FactorsNormalized Data
Normalization Factors compare each month’s Net Length with the Average Net Month Length.
Normalization Net Month Length Factor Average Month Length
=
Example: Jan 2016 19.45 Days / 20.67 Days = 0.94
=
3. Normalization Factors
13
3 -
Slide14Normalizing the Data: Adding It All Up
Normalization Factors are calculated for every month for the entire period.
Introduction
Net Daily FactorsNormalization FactorsNormalized Data
Normalizing Data Template: Output
Totals by Year
Overall Averages
Totals by Month
14
3 -
Slide15Normalizing the Data: Adding It All Up
IntroductionNet Daily FactorsNormalization FactorsNormalized Data
Normalizing the data requires dividing each month’s Actual amount by its Normalization Factor.
Normalized Actual Data Data Normalization Factor
=
Example: Jan 2016 29.393 Billion / 0.94 = 31.236 Billion
=
4. Normalized Data
15
3 -
Slide16Normalizing the Data: Adding It All Up
Original Actuals are normalized for every month for the entire period.
Introduction
Net Daily FactorsNormalization FactorsNormalized Data
Normalizing Data Template: Output
Totals by Year
Overall Annual Averages
Totals by Month
16
3 -
Slide17Normalizing the Data: Adding It All Up
Introduction
Net Daily FactorsNormalization FactorsNormalized Data
So what impact is made on the Original Actuals when the data is normalized? Here are the Actuals.
Actuals
17
3 -
Slide18Normalizing the Data: Adding It All Up
Introduction
Net Daily FactorsNormalization FactorsNormalized Data
While not always the case, normalizing the data usually helps smooth out some of the volatility in the data.
Normalized
Actuals
18
3 -
Slide19Normalizing the Data: Adding It All Up
IntroductionNet Daily FactorsNormalization FactorsNormalized Data
An aside on Retail Trade: Many in this industry like to divide the “months” into weeks of 4-4-5. There are some issues with this approach.
Some 4-week months may not capture the 1st day and/or last day of the calendar month.Decembers are obviously especially crucial, but when they can “end” several days before or after New Year’s Day, year-to-year comparisons can be compromised.Fails to capture the significance of what day of the week Christmas falls.Some holidays may be uncooperative with this approach. (e.g., Memorial Day falling in May or June).Every 5-6 years has an extra week that may be problematic.
19
3 -