Beyond the Trend Line

1 / 24

Beyond the Trend Line - PowerPoint PPT Presentation

Beyond the Trend Line. Extending that Line into the Future St. Louis CMG February 12, 2008. Wayne Bell – UniGroup, Inc. Methods of Forecasting. Excel Trendlines – Manual Extensions Percentage Rate of Growth Regression Moving Averages. Excel Trendlines. Linear vs. Exponential.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

PowerPoint Slideshow about 'Beyond the Trend Line' - Mercy

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

Beyond the Trend Line

Extending that Line into the Future

St. Louis CMG

February 12, 2008

Wayne Bell – UniGroup, Inc.

Methods of Forecasting
• Excel Trendlines – Manual Extensions
• Percentage Rate of Growth
• Regression
• Moving Averages
Excel Trendlines

Linear vs. Exponential

Percentage Rate of Growth

Used if you have a known value and are given a rate of growth

Normally, you are given an annual rate-of-growth.

Three methods:

Linear Growth – Straight Line

Monthly Compound Growth

Annual Compound Growth

Linear Growth – Straight Line
• Calculate the amount of growth.
• Current value is 100
• Growth Rate is 50%
• Amount is 50 per year
• Divide by 12 for monthly increase
• Add this increase to the prior month
• Xn+1=Xn*(1+(%/(12+%*n)))
Monthly Compound Growth
• Annual percentage divided by 12
• Increase the current monthly value by this amount
• Xn+1=Xn*(1+(%/12))
• The amount at the end of a year will be more than expected
• In this case, the base is 100
• Increase is 50% per year
• Actual increase is 163.2 or 63.2%
Annual Compound Growth
• Produces an exponential growth
• The beginning months of the year have a lower growth than the end months of the year
• At the end of the year, the value is exactly the percentage growth expected
• Xn+1=Xn*((1+%)^(1/12))
Regression
• Linear Regression – produces a straight line that best fits a single set of data
• Linear Trend Line
• Exponential Regression – produces an exponential curve that best fits a single set of data
• Exponential Trend Line
Regression
• Excel’s Data Analysis Toolkit has a Regression Tool
• Can be used to judge the correlation of one or more dependent variables to a dependent variable.
• Can provide the intercept and slope coefficients to “draw the line” for current and future data points.
• The Regression Tool is for Linear Regression only
• Exponential Regression can be performed with a minor change to the data
• Simply take the log (Excel Function ‘LN’) of the dependent variable.
Regression – Sample Output
• Items in Red are quick checks on the validity of the Regression
• Items in Green are ‘Rule of Thumb’ values
• In the case of this Sample, you can calculate the expected value for any Date:
• New_Value = Intercept_Coefficient + Date_Coefficient * Date
TREND / GROWTH Example
• Data is composed of two types of data
• Independent – What is known, such as Date
• Dependent – What is unknown – In this case, the Value is dependent on the Date
• =TREND(Dependent Variable Range, Independent Variable Range, New Independent Variable)
• =TREND(\$B\$2:\$B\$34,\$A\$2:\$A\$34,A2)
• B Column is the known Dependent variables
• A Column is the known Independent variables
Moving Averages
• The Moving Average projects values in the forecast period, based on the average value of the variable over a specific number of preceding periods.
• A Moving Average provides trend information that a simple average of all historical data would mask.
• The number of periods in the Moving Average affects the outcome:
• Small number of periods
• Large number of periods
Moving Average
• Simple average of previous values (Sample of 5)
• =AVERAGE(A2:A6)
• =AVERAGE(A3:A7)

- OR -

• Moving Average in Analysis Toolpak
Weighted Moving Average
• Assumes that the most current value is a better predictor than an older value.
• Build a table of Weights
• Incorporate these Weights into the Moving Average
• Table:
• Weights %
• Month-1 50.0 38%
• Month-2 35.0 27%
• Month-3 25.0 19%
• Month-4 15.0 12%
• Month-5 5.0 4%
Summary
• Known Starting Point – Known Rate of Increase – Long Term Forecast
• Linear Trending
• Compound Trending
• Data History – Grow at same Rate – Long Term Forecast
• Linear Regression
• Exponential Regression
Summary
• Old Data not as important as Current Values – Short Term Forecast
• Moving Average
• Weighted Moving Average
• Can combine methods.
• Use Moving Average to determine ‘Next’ value
• Use Regression or Trending for Long Term Forecast
Summary