1 / 24

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. Excel Trendlines – Manual Extensions Percentage Rate of Growth Regression Moving Averages. Excel Trendlines. Linear vs. Exponential.

Mercy
Download Presentation

Beyond the Trend Line

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Beyond the Trend Line Extending that Line into the Future St. Louis CMG February 12, 2008 Wayne Bell – UniGroup, Inc.

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

  3. Excel Trendlines Linear vs. Exponential

  4. 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

  5. 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)))

  6. Linear Growth – Straight Line

  7. 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%

  8. Monthly Compound Growth

  9. 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))

  10. Annual Compound Growth

  11. 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

  12. 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.

  13. Regression – Sample Output

  14. 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

  15. 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

  16. TREND / GROWTH Example

  17. 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

  18. Moving Average • Simple average of previous values (Sample of 5) • =AVERAGE(A2:A6) • =AVERAGE(A3:A7) - OR - • Moving Average in Analysis Toolpak

  19. Moving Average

  20. 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%

  21. Weighted Moving Average

  22. 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

  23. 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

  24. Summary • 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: • http://regions.cmg.org/regions/stlcmg/files/Download/Beyond%20the%20Trend%20Line.ppt • The link for the datasheet is: • http://regions.cmg.org/regions/stlcmg/files/Download/Beyond%20the%20Trend%20Line.xls • For more information please contact me at: Wayne_Bell@UniGroupInc.com

More Related