Introduction to Management Accounting

Excel and the Performance Report

Back to Performance Reports for a while; and here's a secret for you: we didn't type out the performance report and the variation from budget report in full. We got our Excel spreadsheet to do some of the work.

Variances

For example, Excel calculated the Variances for us and told us whether the variance was adverse or favourable or even whether there was no variance at all.

Here's how we told Excel to do all of that:

The title of the performance report begins in cell A1: follow it down from there.

Column D shows the variance formula: simplicity itself at =C3-B3 for the direct materials, for example. Just enter this formula and copy it down from row 3 to row 16, deleting the entry in row 11 since it's not needed.

Column E shows how to programme the decision of whether a variance is adverse, favourable or zero: the formula is the same for all rows, with this example taken from row three, the direct materials variance

=IF(D3<0,"Adverse",IF(D3=0,"","Favourable"))

You could put "Unfavourable" instead of "Adverse" if you like and "No Variance" instead of " " where there is no variance if you like. Copy this formula from row three to row 16, too, just as we did with the variance formula.

By the way, this is an example of a NESTED IF statement and Excel's Help file can teach you how to work with these if this example isn't enough for you.

Notice that some of the variance formulae are red and others are black: we used CONDITIONAL FORMATTING to get Excel to make any adverse variance print out in red and any favourable variance to print out in black:

Select the range to format: D3:D16 in this case

For the adverse variances

Format
Conditional Formatting
Cell Value is less than 0
Format … colour = Red

For the favourable variances

Format
Conditional Formatting
Cell Value is greater than or equal to 0
Format … colour = Black … you could leave this blank as black is the default colour; but we have included this option in case, for example, you want to change the favourable text colour to blue, green … whatever you like.

Try this out and see how it works: Excel's Help is always there if you get stuck! You know =SUM(range) functions don't you? We thought so, so we didn't mention them!

Variations from Budget

Excel also calculated the percentage variations from budget and we programmed it to advise us whether a variation had to be investigated or not.

This table starts in cell F1 so check the formulae carefully with that in mind.

Notice that we have used column A as the basis of the row titles: why type or copy them all over again if you don't have to?

The variations from budget formula is:

for direct materials this translates to =B3/C3-1 and then format the cell as a % to two decimal places

We need another NESTED IF statement for the Action needed/not needed decision. For direct materials, we have

=IF(OR(G3>0.1,G3<-0.1),"Action needed",IF(G3=0,"","No action needed"))

Did you spot that we slipped in an OR statement in the middle of that NESTED IF statement? Sly aren't we? What it does is to say

If the value in cell G3 is EITHER greater than 0.1 (that's 10%) OR the value in cell G3 is less than minus 0.1 (that's less than -10%) THEN …

Of course, we have used the 10% we were given as the cut off point, but if it's, say, 5% then change 0.1 to 0.05; if the cut off percentage is 25% then change the 0.1 to 0.25 and so on.

Notice, we've left the various decision formulae (adverse/favourable, action/no action) in the totals rows as well as individual rows: they may not be needed and if you left them out it wouldn't necessarily be a problem. The totals and sub totals should be left alone, however.

Sign me up ... click here:

References

Duncan Williamson has a PDF File section at http://www.duncanwil.co.uk/pdfs.html

For this section, you should take a look at these pages concerned with drawing good charts and graphs:

http://www.duncanwil.co.uk/pdfs/charting.pdf and

http://www.duncanwil.co.uk/pdfs/charts.pdf

As even more background help and advice for budding and genuine management accountants, there's always room to take a look at how to draw good graphs and charts. So take look at http://www.duncanwil.co.uk/chartfeed.html

Spreadsheets are popular! Management Accountants use them every day. Here's a page to take the novice through the basics of designing them ... properly! http://www.duncanwil.co.uk/spreaddes.htm for a good look at how to design a good spreadsheet

Here's a goody for automation freaks: http://www.duncanwil.co.uk/sumifacc.html will be very useful for anyone wanting to automate some of their accounting functions without having to spend a lot of time and money on new software.

You are now ready to attempt these questions:
Question

These links have been disabled for the trial version
15

18

Previous Organise a Factory Visit and Watch the Television

The eShop is now open:

Duncan Williamson
3 March 2003