1 / 18

Trendlines In Excel

Trendlines In Excel

mahisyama
Download Presentation

Trendlines In Excel

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. Trend lines in Charts

  2. Trendlines in charts • A trend line is a straight line connecting multiple points on a chart. • A trend is a movement in a particular direction • The magnitude of the slope of a trend line, or steepness, indicates the strength of the trend. • Trendlines can be used to forecast future !

  3. Creating trendline in excel • Select the graph and right click • Select add Trend line

  4. Trendline types • A window opens with types of trend line to select from 6 Options • Select the default option for trend line – Linear • Click OK

  5. Trendline You can see the trendline now

  6. Formatting trendline • Right click on the trend line to format it. • Select style, color and weight of your choice

  7. Options - Name • Trendline Name • You can give custom name for your trendline such as .. Sales trend… etc.

  8. Options - Forecast • by using this forecast Option you can extend the trend line forward or backward to the number of periods as desired • Here I selected 6 periods (Months - in this case) forward

  9. Options - Forecast • You can Observe that the trendline got extended in to the future by 6 periods • This denotes if this trend continues, the sales of XYZ territory are likely to be like this

  10. Options – Display equation and R2 Value • by clicking “display equation on Chart” and display r-Squared value on chart, you will be able to display them on the graph • Next slide explains them • You can Set Intercept to any value by clicking it and adding value (0 is default). As this Option is not used often in simple trend lines , explanation is beyond the scope of this slide set.

  11. equation • This is the equation generated by excel that holds the mathematical relationship of Sales and months. • This equation will be unique to each data set • This equation can be used to compute sales of any future month • to put it simply , here “y” denotes the sale (which is on Y axis) and x denotes the month • So lets compute 13th month (Jan-2011 in this case) projection, using this equation • y= ((17.311)*13)+296.39 that equals 521.43 • So the likely sales projection for 13th month is 521.43

  12. R squared Value • This is the R_squared Value for this trendline. • This value denotes the reliability of the sales projections. • R squared value will range between 0 and 1 • If the R squared value is 1, then the trend is most predictable and reliable. • The reliability of trendline goes up if the R-squared value is nearest to 1 • Let us see the next example to understand it better.

  13. R squared Value = 1 • Here the R_squared Value is 1. • Just take a look at the sales progress. • With every passing month, this territory is adding $100 to the previous month. • So, going by the trend, you can be almost sure, that the 13th month sales are . • Remember, Trend lines and Forecasts means you are presuming the existing market conditions are not going to change radically.

  14. Six types of trend lines • Linear • Logarithmic • Polynomial • Power • Exponential • Moving Average

  15. Best fit Trendline • We have learnt that if R2 Value is near to 1, the reliability of trendline is better. • So, now we need to use a trend line from the five available trend lines in excel menu to arrive at the most appropriate one to ensure that our forecast is most reliable. • The other trendline left out is Moving average for which ,you will neither get the equation nor the R2 value. • Simplest way to find the best fit trendline is to check every trend line’s R2 Value and use the trendline with highest R2 value ( Which is nearest to 1- out of 5 types)

  16. Rule of thumb to use type of Trendline • 1. Linear trendline : use it if data values are increasing or decreasing at a steady rate. • 2. Logarithmic trendline : Useful when the rate of change in the data increases or decreases quickly and then levels out. • 3. Polynomial trendline : Used when there are data fluctuations like the sales following seasonal trends

  17. Rule of thumb to use type of Trendline • 4. Power trendline : Use with data that has values that increase at specific rate at regular intervals. • 5. Exponential trendline : Use when data values increase or decrease rates that are constantly increasing. • 6. Moving average trendline : Use it when uneven fluctuations are in data values

More Related