Budget vs Actual Variance Reports with “In the Cell Charts” in Excel

23
11348

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:

in cell variance chart 1

Download Fully Worked Excel file
Don’t have time to go through the whole tutorial? You can now download the file for a little premium instantly and can read the tutorial later too!

Buy Now

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

in cell variance chart 2

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,"")

in cell variance chart 3

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.

in cell variance chart 4

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.

in cell variance chart 5

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:

in cell variance chart 6

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.

in cell variance chart 7

With a touch of borders to fine tune the look and this is what I have in the end!

in cell variance chart 1

Download Fully Worked Excel file
Don’t have time to go through the whole tutorial? You can now download the file for a little premium instantly and can read the tutorial later too!

Buy Now

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

23 COMMENTS

  1. 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.

    • 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

  2. 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

  3. 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

  4. 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.

  5. 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.

  6. 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 working on making the collection of all the “worked” files made available for users. Will update the community once done!

  7. 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

  8. 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!

    • 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

LEAVE A REPLY