Are You Trendy ?
Chandoo is off Holidaying teaching excel in the Maldives and has lent me the keys to his Blog (Chandoo.org) and this week I plan to take it for a spin.
I will be posting 3 posts on Trend Analysis/Forecasting using Excel and a forth post on some Hidden Worksheet Properties which I stumbled onto last week !
Hopefully if I look after the Blog while Chandoo is gone, He will let me borrow the keys another day.
Forecasting
“Tomorrows weather will be fine and hot with a chance of showers in the morning.”
We have all seen this type of forecasting during the nightly news.
This week I am going to go through the basics of forecasting and trend analysis using Excel as a tool.
We will look at some simple trends and make predictions about future values.
In later posts we will look at more complex data and other methods of tackling these analysis.
Introduction
Often you may have a set of data and need to know what an intermediate or future value of that data may be.
This week we will investigate 3 methods of tackling this problem using Excel.
In this post we’ll look at doing forecasting manually
In the second post we’ll look at a few excel functions that assist us with forecasting
The third post will discuss a method of looking at any value along an Excel generated Trend Line and give you a tool to assist you in this.
Manual Forecasting
In all environments where numbers are collected and people make use of these numbers the ability to forecast or extrapolate data may be required.
In forecasting we are going to look at the trends that the data has and use these trends to help forecast future values or values outside the measured data. The trends can also be used to infill data where gaps may be missing in the collected data.
This post will look at doing this manually, albeit with some help from Excel.
We will examine a business that makes things and we will measure some measurement of those things every 5 days. In trend analysis it doesn’t matter what you measure or what your measuring it against.
We have collected some data which is tabulated
| Day | Measure |
| 5 | 7 |
| 10 | 10 |
| 15 | 24 |
| 25 | 30 |
| 30 | 40 |
One of the easiest ways to visualise this relationship is to draw a quick chart of one measure vs a base or in our case a time line.
This can be shown graphically as a simple Excel Scatter chart
You can see that there is some level of variability in the measurement as the data doesn’t quite fit a straight line.
Manually we can make an estimate of a line of best fit and draw it on the chart by adding a new data series consisting of 2 points.
There are 3 quick methods of using this line of best fit
- Manual Estimates
- Equal Triangles
- Equation for the line
Manual Estimates
If we want to know what the measurement would be for a location where no measurement was taken we can use the chart and 2 quick lines to show in this example that for 20 days we would expect a measurement of about 26 units.
This can also be used for extrapolation of our data past the limits of what was measured.
By extrapolating the Line of Best Fit beyond the data, the same technique can be applied to estimating what some future value maybe.
Equal Triangles
Equal Triangles is a technique where a simple ratios of 2 similarly shaped but different sized right angle triangles can be used to make estimates of missing or extrapolated data.
Using Equal Triangles the ratio of the height to the width of Triangle 1 (Red) is equal to the ratio of the height to the width of Triangle 2 (Blue).
So in the example above
Y1/X1 = Y2/X2
Y1 = 38 – 8 = 30
X1 = 30 – 5 = 25
Y1/X1 = 30/25 = 1.2
So for Triangle 2
Y2/X2 = 1.2
Y2 = ? – 8
X2 = 20 – 5 = 15
from Y2/X2 = 1.2
(? – 8 ) /15 – 1.2
We can rewrite this as
? = 8 + 1.2 x 15 = 26.0
Or
Unknown Y = Min Y + Ratio x (New X – Min x)
Once we have an equation we can setup a new series on out chart based on an equation in some cells and then directly plot the data onto our chart.
In this case we have used the equation =F105+1.2*(E111-E105)
Equation of the line of Best Fit
If we are using a straight line to model our line of best fit, we can also write an equation for the line in the form
Y = mX + c
Where: Y is the unknown measure
X is the X value for which we want to know the value of Y
m is the gradient of the line
c is the Y intercept of the line (or Y value when there is no X value or X =0 )
The gradient m is calculated as the Rise / Run or in our example 30/25 = 1.2
The Y Intercept is the value when x = 0. This can be back calculated from the first point (5,8)
C = 8 – (5 x 1.2) = 2.0
So the equation for our line of best fit is Y = 1.2 X + 2
We have used this in the next example =E136*1.2 + 2
The good thing about having an equation for the line is that we can use that to calculate any value of our measure.
So if we want to know the measure on a day outside the range we measured, say the 40th day
Downloads
You can download examples of all the above charts from the following link
https://chandoo.org/wp/wp-content/uploads/2011/01/Trends1.xls
Benefits of Manual Estimation
- Applicable to simple models
- Can be used without a computer or a calculator in the field
- Gives the user a better feel for the data
Problems of Manual Estimation
- Only applicable to simple models
- Reliant on the accuracy of your estimate of the trend
- No measure of how accurately your estimate fits the data
Next:
In the next post we will look at using excel functions to automatically estimate lines of best fit and other excel functions to aid in estimation of non-linear functions.
Further Readings
43 Responses to “Are You Trendy ?”
Hello Hui,
Nice post indeed.
We use trend analysis here with burndown charts (dev teams) to estimate when we'll reach milestones.
We use line of best fit for the 4 last points of measurement (once in a week) plus forecasted effort for next week. This gives us a good view with the actual effort and the trend.
Regards
[...] This post was mentioned on Twitter by Chandoo.org, N H Krishnan. N H Krishnan said: RT @r1c1: Latest: Are You Trendy ? http://goo.gl/fb/0dqVk [...]
Excellent post!!! I'm needing something like this in my work. Can I use this method that will help forecast if there are multiple variables: temperature, call volume, billing and marketing campaigns by week?
Wel come Hui, nice post. It will help me in future time limit settings for task given to the team....
hi Hui,
I have a question. I understand the equal triangle concept. However, isn't it still an "eye ball" approach to find out the best fit line? If we have two individuals using the same approach, they will come up with different equation to the manual trend line, right?
Fred
Great ! Watch out Chandoo......... Hui moving up?
I use trending in Fleet Gas Mileage analysis. Sometimes the odometer mileage,costdata, date are missing for a Fleet Car, and I have to estimate the mileage, based on the last two entries delta, Delta = MPG2-MPG1. Works Okay, but would like to have an EXCEL Formula to predict the missing mileage value automatically. Of course, the Fuel Cost would have been nice to add to the database. Not bad for Governement work..........
This is really exciting and a very insightful post . Thanks a lot ..... would love to see more posts on forecasting 🙂
This is childs play, anyone who has the most basic knowledge in algebra can do this and every highschool kid is able to do this. No offense intented, but the content of this post is quite disappointing.
@ Cyril, Walter, Istiyak, DCWright, Ramya, Thanks for your appreciation
@ Fred, You are correct and we will be looking at more scientific/rigorous approaches in part 2 of this series.
@ Zarif, I never said it wasn't childs play and I know lots of people who have never studied algebra or finished even mid level high school who use Excel daily, and this post was aimed at them. I also didn't think it is appropriate to jump into multiple variable regressions or multi dimensional polynomials on day 1. I think it is more important for people unfamiliar with a technique to understand the how and why rather than just how to do something. As I said at the start this is a 3 part series.
Hui, great post..... Can't wait to see the next 2 installments..... And Yes, this what makes a great post, you gradually walk through the content knowing very well that you have a wide range audience, of which some of them would greatly benefit from this "Childs Play".....
very good post. It reminded me of things that long ago that I did not use
WTG Hui... looking forward to the next installements..
~Vijay
I'm a slow learner. It's always good to refresh some of the basic skills that I have almost forgotten over the years. Thank you very much, Hui.
Wonderful post, great approach, getting the basics right is much more important than any other step... thank you, thank you. please continue the lessons. -bill
[...] the previous post we looked at Manual Forecasting techniques and how Excel can be used to assist. In this post we [...]
Hui,
Awesome post. I am looking forward to this week's topic.
[...] Dilbert is also running a series on estimating trend lines in Excel, which is well worth a look at: Are You Trendy This entry was posted in Charts, Excel, Maths, Newton and tagged curve fitting, Excel, [...]
[...] Are You Trendy (Part 1) [...]
Nice post...I Have a question on where the 28 came from, I thought it should read 30/25.
Y1/X1 = Y2/X2
Y1 = 38 – 8 = 30
X1 = 30 – 5 = 25
Y1/X1 = 30/28 = 1.2
Ur the best to analyse,, pls ask the solution over the numbers......
@Manny
Good pickup, I have updated the post
[...] link [...]
I've seen some charts in which there's some sort of "ball" like in this last chart, but this one is different because it's dynamic and "moves" according to the data, I've searched everywhere but couldn't find this, can anyone help me out?
@Vinicius
All the charts and data from the 3 Are You trendy ? posts are in the file at
http://chandoo.org/wp/wp-content/uploads/2011/01/Are-You-Trendy-Complete-Examples.xlsm
On the Trendlines, Trendy, Part 1 and Part 2 tabs there are examples of dynamic charts, including the one above.
or you can look here at:
http://chandoo.org/wp/2010/11/11/highlight-data-points-scatter-line-charts/
[...] http://chandoo.org/wp/2011/01/24/trendlines-and-forecasting-in-excel/ [...]
Excellent post!!!
In your Equal Triangles section do you also need make a fix? Change 24.07 to 26?
We can rewrite this as
? = 8 + 1.2 x 15 = 24.07
@Gabriole
Thanx and fixed
I think need to sternly discipline my proof reader
This can be useful, but it is assuming a linear relationship.
OF course, some data sets will have a linear relationship, but mostly there will be cyclical and periodic changes that a linear relationship will not be able to take account of. Essentially the best would be exponential weighted averages.
Hi Guru,
Like to ask bout forecast as follow:
YEAR
SALES
2000
300.965
2001
299.597
2002
317.748
2003
354.629
2004
483.148
2005
533.917
2006
318.904
2007
433.341
2008
603.774
2009
486.088
2010
764.710
2011
894.164
2012
?
2013
?
2014
?
2015
?
2016
?
What should I do? please help, thanks in advance..
[...] http://chandoo.org/wp/2011/01/24/trendlines-and-forecasting-in-excel/ [...]
Be specific over calculation part.......
[...] http://chandoo.org/wp/2011/01/24/trendlines-and-forecasting-in-excel/ [...]
working on seasonal rainfall trends. I produce a trend and line of best fit. however I want to know the meaning of the equation y=ax + b. what is a, x and b. where do I get the numbers to fit in the equation
@John
The equation for any straight line can be expressed as y=ax+b
where a is the gradient of the line and
b is the Y intercept of the line, ie the y value when x=0
In Excel you can use the Trend, Intercept or Linest functions
ex: for the Equation y = 2.5x + 4.5
x y1 7
3 12
7 22
15 42
Slope 2.5 =SLOPE(C3:C6,B3:B6)
Intercept 4.5 =INTERCEPT(C3:C6,B3:B6)
Linest 2.5 4.5
=LINEST(C3:C6,B3:B6)
i know im late but would this be something yall can help me out with, the file i uploaded conatains the data that i mirrored from an article, if you can advised me on how to obtain the points in the file.
heres the link to a thread i started:
http://forum.chandoo.org/threads/how-to-do-call-volume-forecasting-for-service-desks.15479/
hello,
how to get realtime spot:data forex from http://www.binary.com to make dynamic Sparklines chart tick by tick
thank you
[…] Coincidentally, Chandoo at Pointy Haired Dilbert is also running a series on estimating trend lines in Excel, which is well worth a look at: Are You Trendy […]
[…] Even manually: Read this article to learn more. […]
[…] wel praktisch om een impact prognose van je actiepunten te presenteren. Mijn advies is om deze handleiding te gebruiken bij het berekenen van je impact prognose. Zo geef je een onderbouwing waarom je […]
It's a shame you don't have a donate button! I'd without a doubt donate to this excellent blog!
I guess for now i'll settle for book-marking and adding your RSS feed to my Google account.
I look forward to new updates and will talk about this blog
with my Facebook group. Talk soon!
I do have a Donate button
It's at: http://chandoo.org/wp/about-hui/
Scroll down to Donate
Oh my goodness! Incredible article dude! Many thanks, However I am experiencing
troubles with your RSS. I don't understand why I can't
join it. Is there anybody getting identical RSS issues?
Anyone that knows the solution can you kindly respond? Thanx!!