In life we all want to be in control. And usually we measure or judge whether things under control by comparing whats happening with what should be happening. In other words, we tend compare the actual events/results with planned events/results.
Though more interestingly most of us don’t really know if the “plan” is really under control or not. Anyways we will discuss the philosophy of perception vs reality some other time. So back to our topic comparisons and making budget vs actual reports.
Excel provides is immense flexibility to make variance analysis super easy either in numerical form using conditional formatting or custom formats or in the form of variance charts.
Today we are looking at yet another technique of reporting variances which is more of an in-the-cell charting. We are not going to make actual charts but it will be visually as helpful as normal charts. The final result of today’s technique is following:
Got a question? Contact details are here
Its really easy to do if you know how to use REPT() function. An overlooked and to many it doesn’t have any use. But it has quite a big role today. So lets learn it.
In cell Variance Charts – Step by Step
Download this workbook to help you walk through the steps discussed as under.
Step 1: In a separate column make a heading of Variance. In our case it will go in cell F5. And in cell F6 put this formula and double click the fill handle to populate the formula down the whole range:
=E6-D6
Step 2: Now that we have calculated the variances, for the sake of better understanding lets separate the negative and positive values in two columns. These are not needed if one has a good grip on IF() functions but for simplicity I will be employing helper columns.
Write “Negative” in cell G5 and in cell H5 “Positive”.
In cell G6 put this formula and double click the fill handle after pressing Enter key:
=IF(F6<0,F6,"")
In cell H6 put this formula and drag the fill handle down to fill the range:
=IF(F6>0,F6,"")
Step 3: Go to cell C18 and Insert a specific symbol which you can find in almost every font once you select “Block Elements”. Once inserted simply close the dialogue box.
Also put 300 in cell C19.
Step 4: Select cell J5 and K5 and merge them together. Write “Variance Chart” in merged cells
Step 5: Put this formula in cell J6:
=IF(G6="","",G6&" "&REPT($C$18,ABS(G6)/$C$19))
Align the column to the right.
And in cell K6 put the following formula and drag the fill handle down to fill the formula in appropriate range:
=IF(H6="","",REPT($C$18,H6/$C$19)&" "&H6)
Once you are done with above steps your chart will look like this:
Step 6: Now coloring part is manual as we have separate favourable and unfavourable variances from each other. Not always a negative figure is a bad thing and not always a positive figure is a good thing.
So items like Revenue, Operating profit etc their positive variance is favourable so I colored them Blue whereas items like expenses their, positive variance indicate increase in cost therefore it is an unfavorable variance so I colored them Red.
With a touch of borders to fine tune the look and this is what I have in the end!
Got a question? Contact details are here
So hope you have enjoyed learning a new technique as must as I enjoyed writing about it. Check out more Excel tutorials by clicking here
The ROUND function may be used to round off either the data or the cell address that you are retrieving them from.
Can you please tell me how to round the value with rept function?
Dear Hasaan, thank you for Tutorials, wich are really great.
At the time, i work in Reporting & Analysis, Variances, Actual – Budget, from ¨Expenses¨ and not Sales. Thus, in this tutorial formula, how can i turn negative numbers into positive, as i would be analyzing Expenses..Actuals below the Budget means positive, as expenses are below budget.
Best regards
Juan C
Excelente tutorial!!!! muy útil y conveniente. Como siempre muy agradecido por tus valiosos videos.
Muchas gracias!
Excellent tutorial. This works really well when analyzing various financial statements over a period of years. It is stuff like this that gave me an edge in my accounting classes.
In the final chart sample … How did you change the numbers on the Positive side to “negatives” and on the Negative side to be “Positive”?
So I checking out my options I found that if I modify the formulas for the “charts” to make the value entered in the chart to be opposite the actual value – in col. J: =IF(G6=””,””,-G6&” “&REPT($C$18,ABS(G6)/$C$19)) I added the – before G6; in col. K: =IF(H6=””,””,REPT($C$18,H6/$C$19)&” “&-H6) I added the – before the final H6.
This works but may not be the best solution. I’ll await your reply. Thank you!
Excelente manera de trabajar con el Repetir gracias por el ejemplo muy claro
Great stuff this.
Can you create a report showing Americas Division’s Fiscal Year budget and annual expenses. I have attached two documents to use (a) Word document that lists aspects of annual FY budget for the Americas Division, broken down into various categories, and (b) Excel document listing the Americas Division’s actual expenses for FY.
There are two parts I require
1) Use the financial data from the first two attached documents to create an FY14 budget vs. actuals report for the Americas Division.
2) Format your report to make the financials presentable and easy to understand for a layperson, such as a Program Director. Create borders and sections, alter wording, use myriad colors and pictures, go crazy. Or don’t. You have free reign to tackle this report however you’d like.
You sent an email? Where did you attached the files?
This is a very interesting & creative way to prepare a visual representation of variances. I have searched many sources for this function. My only concern is that my Income Statement is presenting expense amounts as negative figures and income amounts as positives figures. I tried copying and iterating the function in excel but couldn’t achieve the desired result as some of the chart bars were extremely widely spread across my Excel sheet and couldn’t fit in one page.
Hello,
First – great post. Very interesting use of REPT here, simple and dynamic.
Second, the numbers show as having decimals – how do i format it so that it just shows the rounded number to zero decimal points?
thanks
You can use ROUND function to wrap around the values or cell address from which you are fetching the values.
really perfect, thanks for sharing
Thanks for this useful tutorial, it really works.
please advise how to work with variance in percentage. the 300 variable doesn’t work with percentage.
thanks
Have the percentages calculated in the cell and refer to that cell in REPT function or do the calculation within the formula to show percentage values.
The chart is simple to create but powerful for data visulation. Great thanks and please keep up the good works!
Is there a way to use a comma separator for every 1,000?
Hey Tuan,
Thank you for visiting and commenting.
If you are talking about comma separator in the income statement then you can use comma separator from the ribbon above or hitting CTRL+SHIFT+1 will format the number with comma separated number with two decimal places.
If you are talking about comma separator in chart then you can use TEXT function within the formula to format the number the way you like.
For example string you want to add can be: =TEXT(A1,”#,##0_);(#,##0)”)
Hope this helps
– Hasaan
Am grateful for this insightful exposition on the in cell variance chart you explained above but my challenge is when i do drop down the corresponding cell either positive of negative that are not carrying value with show something like #value unlike your own. please it help me out
After which step you are getting this error?
– Welcome to PakAccountants
Hasaan, I am a CA myself and working in Industry with an MNC in Europe now for longtime and we have several nice way to show such variances. I really liked the way you have shown the Variance analysis. You are excellent , keep the goodwork on.
regards,
Santosh
Very beautifully explained except the logic of 300 in Cell ” C19 ” which i couldn’t grasp. Even though i achieved the desired result by copying and iterating the function in excel.
But i would be grate if you share the logic of adding 300 in Cell ” C19 “.
Further, I have a query regarding describing the data behavior in same cell as line chart (like ECG line) for up or down variation. if you have any excel work sheet then plz share or help.
Thanking you in anticipation.
Regards
Muhammad Asif Raza
Welcome to PakAccountants and glad you took time to comment.
300 is a divisor to keep the lines within limits. You can say that it is the “workaround scale” I achieved. As numbers can be big or small so with this divisor you can adjust the look of the graph easily.
Regarding the ECG style graph it is in the pipeline and will write about it soon IN SHA ALLAH TALLAH. Let us know if you have more ideas and I will definitely consider them.
This is a good tutorial…interesting way to create a visual representation of variances. I have a suggestion, though. For revenues and all the “profit” lines, subtract Budget from Actual as you have done. But for all the Expense lines, subtract Actual from Budget instead. This way, all the negative variances will be “bad” variances and would show up on the left-hand side of the variance chart. Then you could simply color the fonts on the left-hand side of the variance chart red, and the right-hand side blue. This is a great tutorial, and would be quite easy to implement in real-world situations. And as others have said, this is a good use of the under-utilized REPT function. Thanks!
Welcome to Excel portal John glad you left an insightful comment.
Yes it is a workable idea I am sure the readers now have just another way of producing graphical illustration.
Hasaan – you rock brother!
What an awesome way to use the REPT function. I feel like it’s severely under utilized especially when you see how well the formula can work to visually display and help you decipher how well your company is operating.
I think something like this would be great to use in a scrollable table in a dashboard. I’ve already conjured up some creative ways I could use this on the operation side of the business.
Keep killing it!
Brad
Thanks Brad.
Yes REPT function can go miles just like other formulas. I have one more experiment to run lets see if I can get it done using the same technique and will share the moment its done.
Sure why not! Would love to see these charts used in dashboards and adding more meaning to them. And thanks for commenting dear!
I am trying to get your https://pakaccountants.com/excel-budget-actual-variance-charts-in-cell/ to work, but i cant get the rept formula to work.
Is it possible that you can send me your example as excel to my email so i can see if it works on my computer.
Thanks in advance as i really like what you have done here.
I am working on making the collection of all the “worked” files made available for users. Will update the community once done!
When I insert the symbol in cell C18, it looks fine. But, when it is displayed in K6, it displays a cents symbol instead. I feel like there is something easy I can change in the formula or cell format that should fix this, but I’m not sure what.
Hi Melanie,
Welcome to PakAccountanats.
Can you please mention what you see in Number format drop down if you select cell K6? And also which font’s symbol are using?
Thank you
The issue is with the quote symbols. If you simply copy and paste the formulas from this web page, you get double quotes like this:
” “
…when instead you need them to be:
” ”
So just highlight the bad quote characters and type in ”
All in all a nice tip. Thanks!
Oh! I have a solution for this and will make them preformatted then it will be ok to copy directly.
AOA, none of your formula works.
Can you please tell where you are getting the problem? It will be easy for me to help you if you can elaborate the steps you are taking