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
31 Responses to “Beautiful Budget vs. Actual chart to make your boss love you”
Would be considerably easier just to have a table with the variance shown.
On Step 3, how do you "Add budget and actual values to the chart again"?
There are a few ways to do it.
Easy:
1) Copy just the numbers from both columns (Select, CTRL+C)
2) Select the chart and hit CTRL+V to paste. This adds them to chart.
Traditional:
1) Right click on chart and go to "select data..."
2) From the dialog, click on "Add" button and add one series at a time.
One more way to accomplish it is just select the columns into chart. Press Ctrl+C and then press Ctrl+V
Regards
Neeraj Kumar Agarwal
Unfortunately, this doesn't seem to work for me in Excel 2010. The "Var 1" and "Var 2" columns cannot combine two fonts to display the symbol and the figure side-by-side.
Secondly, there is no option to Click on “Value from cells” option when formatting the label options. The only options provided are Series Name, Category Name or Value.
@TheQ47... the emoji font also has normal English letters, so if you use that font, then you should be ok. I am assuming your computer doesn't have that font or hasn't been upgraded for emoji support.
Reg. Excel 2010, you can manually link each label to a cell value. Just select one label at a time (click on labels, wait a second, click on an individual label) and press = and link it to the label var 1 or var 2.
I am using excel 2010, please explain how to apply Step 12
Regards
Neeraj Kumar Agarwal
Hi Neeraj,
"Value from cells" option is only available in Excel 2013 or above. In older versions, you have to manually adjust the label value by linking each label seperately.
Read this please: https://chandoo.org/wp/change-data-labels-in-charts/
Sir, you are just awesome.
Your creativity has no limit.
Regards
Neeraj Kumar Agarwal
Hi Chandoo,
I just found your website, and really love it. It helps me a lot to be an Excel expert 😉
Currently I am facing with a problem at step 11:
Var1 Var2
D30%
A5%
B0%
B4%
B7%
C10%
C13%
D27%
I42%
Though at mapping table, I used windings, here formula uses calibra. How I can change it? I am able to change only the whole cell. In this case numbers will be Windings too.
Thanks for your help!
Hi Mariann... Welcome to Chandoo.org and thanks for your comment.
If you wanted to use symbols from wingdings and combine them with % numbers, then you need to setup two labels. One with symbol, in wingdings font and another with value in normal font. Just add the same series again to the chart, make it invisible, add labels. You may need to adjust the alignment / position of label so everything is visible.
[…] firs article explains how you can enhance your charts with symbols. You can simply insert any supported symbol into your data and charts. To some extend you can […]
You're a good person, thank you to share your knowledge with us, I will try to do in my work
Great visualization of variance. My question is that is this possible in powerbi?
How would you go about it?
HELLO, WHY CANT I FIND VALUES FOR LABELS IN EXCEL 2013
Dear chanddo sir,
What to do if we have dynamic range for Chart. How this will work. can you able to make the same thing works on dynamic range.
Sir Chandoo,
Good Day!
First, I'd like to say that I am very grateful for your work and for sharing all these things with us.
I tried to do this chart but it seems that the symbols don't work with text (abs(var%),"0%") unless we keep the Windings font style.
The problem is, it converts the text into symbol as well and you wont see the 0% anymore. I'm using Windows 7.
WOW - Segoe UI Emoji
This is the greatest discovery for me this month 🙂 Thanks for sharing.
Here's my two-cents:
https://wmfexcel.com/2019/02/17/a-compelling-chart-in-three-minutes/
Sir This is awesome chart, and very easy to made because of your way to explain is very simple , everyone can do. Thank you
one problem i am facing, I hv made this chart , but when i am inserting data table to chart it is showing two times , how can i resolve this
in this chart when i am adding new month data for example first i made this chart jan to mar but when i add data for the apr month graphs updated automatically but labels are missing for that new month
Hi Renuka,
Please make sure the formulas for labels are also calculated for extra months. Just drag down the series and set label range to appropriate address.
So I am playing with the Actual chart here - but amounts are bigger than your - you have 600 as Budget - my budget is 104,000 - is there a way to shorten that I am unaware of
thank you - I LOVE YOUR SITE
Thanks for the tips and tricks on Excel. In the Planned versus Actual chart examples, you use multiple values (ex. multiple Categories in above). How can this be done when we have only 1 set of values? For example if I have only this:
Planned Actual
SOW Budget 417480 367551
How can I create a single bar chart like the one above?
Thank you Chandoo.
This one is just perfect for my Quarterly Review presentation on Operational Budget against Actual Performance for the Hospital I'm currently working with.
Just Subscribed today (10 minutes ago)
Is there a way to make the table of data into a pivot table to be able to add a slicer for the graph due to many different categories and months?
Hi, I tried to modify you template with something appropriate for me, and I found a problem. this template was modified by me started with excel 2010, then 2016 and finally 2019. Same thing - somehow appear an error - or didn't show the emoticons for positive percentage or doubled the emoticons for some rows. I suspect to be from excel. if is need it I can sand you my xlsx for study. Please help if you can.
Hi Chandoo,
Could you please check the Var Formula in Step1. You have mentioned budget-actual and when i did this i got different values but when reversed like actual-budget i got the actual value what you have demonstrated in step1.
Please share your view.
This is a great chart (budget vs. actual). However, in trying recreate it, I cannot color in the UP Down bars individually, and they all become formatted with the same color. I'm using Office 365. Look forward to the feedback.
Thanks.
Dan
pls explain in detail step 7
While in the Excel sheet you have used following formula for Var
Var = Actual - Budget
But
in the note, you have written
Var = Budget - Actual
Good Presentation and Data information.thank you so much chandoo.