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
25 Responses to “Display Alerts in Dashboards to Grab User Attention [Quick Tip]”
I prefer the red,grey,light grey,black icon set. I've also used in-cell pie charts from Fabrice's Sparklines for Excel as an alert which could also provide another piece of information.
I prefer the red,grey,light grey,black icon set. I've also used in-cell pie charts from Fabrice's Sparklines for Excel as an alert which can also provide another piece of information.
For Excel 2007, your formula should do the same as the Excel 2003 version, so that non-alert rows are blank - if they are 0, the unnecessary green icon will show
Hi Chandoo,
Nice Post !! just to add something for EXL 2003, we can also 4 Ifs and link to the alert data
For Ex: If we have alert data in Cell A2 and want to split in 4 orders namely <25%, 25-50%, 50-75% and 75%< then we can following formula and put fonts as you have suggested :
=IF(A2<0.25,CHAR(153),IF(A2<=0.5,CHAR(155),IF(A2=0.76,CHAR(152)))))
And then using Conditional Formating we can dashboard reflected on different COLOURS as per their respective alert.
Best Regards
Rohit1409
Hi Chandoo,
Nice Post !!! just to add something for EXL 2003, we can also 4 Ifs and link to the alert data
For Ex: If we have alert data in Cell A2 and want to split in 4 orders namely <25%, 25-50%, 50-75% and 75%< then we can following formula and put fonts as you have suggested :
=IF(A2<0.25,CHAR(153),IF(A2<=0.5,CHAR(155),IF(A2=0.76,CHAR(152)))))
And then using Conditional Formating we can dashboard reflected on different COLOURS as per their respective alert.
Best Regards
Rohit1409
The Complete formula [Don't Know how it got cut ]
=IF(A2<0.25,CHAR(153),IF(A2<=0.5,CHAR(155),IF(A2=0.76,CHAR(152)))))
PS : Use in single line [I have split it to avoid cuts 😉 ]
Hi Chandoo..
why it is not displaying the complete formula..
anyways here is the balance
"=IF(A2<0.25,CHAR(153), IF(A2<=0.5,CHAR(155), IF(A2=0.76,CHAR(152)))))"
@Rohit... your formulas are fine. Just that the width of comment area is fixed and hence my website is cropping it at 640pixels. I just edited your formula and added few white spaces so that it wraps nicely.
Very good idea btw.. kudos!
Hi,
Maybe just go for 'bold' ; 'underline' or 'italic' to draw the users attention? Those methods (if those can be called methods) are used cross media type (books, journals, blogs, billboards, ...) to guide the readers eye to valuable information.
Just a basic thought
@Tom.. good idea..
[...] has a very nice writeup on how to add such alerts to dashboard sheets. Possibly related posts: (automatically generated)Divide your data set into workbooksHow to enforce [...]
Hi Chandoo,
You certainly grabbed my attention! although I wasn't sure what my brother (Suresh) and cousin (Shyam) were doing right, and I was doing wrong? 😉
I love your blog btw - Many thanks for all your hard work in unravelling the secrets and mysteries of Excel!
Best regards
Ramesh
I thought I saw an advertisment for a book about learning excel called excel himalaya or something. It cost about 35.00 us money but seemed to have the things I need to have my admin assistant to start to use. I was hoping to start with this book and then send her to school if she shows some interest and aptitude. Any help on this would be appreciated. Thanks
Great web site and information!!!!
@Jeff... checkout http://chandoo.org/wp/2010/08/25/excel-everest-review/
thanks, your website is awesome!
[...] Alerts to highlight focus areas [...]
[...] There are lots of numbers in this dashboard. I would suggest adding few more visualizations like showing indicators or applying conditional formatting or replacing a table with a chart. This would reduce the [...]
[...] is the same technique as alert icons in dashboard. Just that I also showed green [...]
[...] is the same technique as alert icons in dashboard. Just that I also showed green [...]
Hi Chandoo
Firstly thanks for all the cool tips on how to use Excel better.
I am new to the site and have a question which you may be able to assist with but dont know if these comment boxes are the best way of asking ?
I am looking at assets and trying to calculate the depreciation total by taking a year (say 2010) adding the expected life of the asset (say 10 years) then comparing that to a future date (say 2015) using an IF statement. The calculation in normal is - IF((year in col B (2010) plus 10years)>year 2015, add a years depreciation, otherwise leave blank). The converted date value does not appear able to add 10 years in order to compare it to 2015. Am I missing something ?
I use the “IF” Statement in conjunction with Conditional Formatting in MS Excel to give verbiage to alert one of a required action, dependant on a review date. This makes a visual stimulus, plus it clues one as to what the conditional format is trying to warn you about and what follow-up actions are required.
Wow, I'm really impressed with dashboards. I had no idea this stuff was even possible with excel. I'd like to offer an interactive dashboard to my customers, showing analytics of their data. I have a .pdf file with the datapoints. I'd like them to enter the data on my website, and be able to see their data. Is something like that possible.
Hi Chandoo,
I've recently purchased the package for both templates.
In the portfolio dashboard,under the calculations worksheet, I'm attempting to change the date range in the gantt chart to show only the range of the project that starts in late 2013. How do I do this?
Thanks
Adam
[...] is the same technique as alert icons in dashboard. Just that I also showed green [...]
Hi Chandoo,
I'm new at Excel Dashboard and found your blog really useful and helpful! It's very nice of you that you dedicate your time to do this.
Could you please explain how can I use Alerts based on dates on a Dashboar?
For example, if a target date is coming closer to the actual date, the alert is yellow or red.
I'd really appreciate some help!
Thank you
Where can I download the file Excel of Averall Statistics ???
Thanks a lot.