beyond the trend line l.
Skip this Video
Loading SlideShow in 5 Seconds..
Beyond the Trend Line PowerPoint Presentation
Download Presentation
Beyond the Trend Line

Loading in 2 Seconds...

play fullscreen
1 / 24

Beyond the Trend Line - PowerPoint PPT Presentation

  • Uploaded on

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.
Download Presentation

PowerPoint Slideshow about 'Beyond the Trend Line' - Mercy

An Image/Link below is provided (as is) to download presentation

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

Beyond the Trend Line

Extending that Line into the Future

St. Louis CMG

February 12, 2008

Wayne Bell – UniGroup, Inc.

methods of forecasting
Methods of Forecasting
  • Excel Trendlines – Manual Extensions
  • Percentage Rate of Growth
  • Regression
  • Moving Averages
excel trendlines
Excel Trendlines

Linear vs. Exponential

percentage rate of growth
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
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
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
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))
  • 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
  • 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 output14
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
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
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
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%
  • 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
  • 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
  • Know Your Data
  • Chart your current data
  • Forecast your current data and overlay the charts
    • Forecast 2005 and 2006 into 2007.
    • Compare the forecast to the actual 2007.
  • Choose the forecast method that best fits your data!
  • The link for this presentation is:
  • The link for the datasheet is:
  • For more information please contact me at: