Study tips: Advanced aspects of Management Accounting

The first article of our series on some of the trickiest areas at Advanced level.

Study Tips: Advanced Level series

• Advanced aspects 1: Management accounting
• Advanced aspects 2: Financial Accounting
• Advanced aspects 3: Appropriation Accounts and effective communication
• Advanced aspects 4: Short-term decision making
• Advanced aspects 5: Spreadsheets
• Advanced aspects 6: Time management

We’re going to work through a business scenario to illustrate how the AAT Advanced Diploma in Accounting can be applied, in practice, to the typical day-to-day tasks of a part-qualified Accounting Technician.

Let’s join Lewis in the accounts department at Gorgeous Threads & Co, a clothing manufacture, where he is part of a small finance team and deals with all aspects of bookkeeping and accounting.

Yesterday Lewis’s colleague complied the actual figures for the organisation’s overheads and both the labour and machine hours worked for the last quarter (1).  Today it’s Lewis’s job to calculate any over or under absorption.

Calculating over or under absorption

Gorgeous Threads work with spreadsheets because they can be used flexibly by a number of members in a team and adjusted easily.

Everyone in the finance team is expected to demonstrate competent accounting knowledge and precise spreadsheet skills simultaneously, to ensure that the spreadsheet functions used achieve the accounting tasks required. However, in order to do that, everyone has to be meticulous about following instructions regarding formatting and using formulas, as mistakes at the start of tasks have implications for later functions and requirements which can be time consuming to go back and correct.

Lewis received the spreadsheet below from his colleague:

Download your  AAT Advanced Synoptic Series – Part 1 spreadsheet to replicate Lewis’s tasks.

Correcting the spreadsheet formatting

Before he can start calculating the over or under absorption he needs to correct the formatting, as his colleague hasn’t followed guidelines about the nature of the numbers in the cells. 

Those showing the quantity of hours are appropriate, but the cells containing the overheads should be formatted to currency.

Lewis highlights the two cells, reformats them to currency and then reduces the decimal places to only show whole numbers as that is the organisation’s policy when calculating overheads. 

He looks up the overhead absorption rates (OAR) for Quarter 1 and adds them to the spreadsheet, adjusting the column widths and formatting the cells appropriately:

Next he checks how each profit centre recovers its overheads and finds Apparel Manufacture use direct machine hours and Accessories Manufacture use direct labour hours.

Calculating the overheads that have been absorbed

Now Lewis can calculate the overheads that will have been absorbed in Quarter 1. He must use a formula to comply with Gorgeous Threads’ requirements.

To help him write the correct formula he first thinks about:

1. The accounting theory – as Apparel Manufacture absorb overheads using direct machine hours then for every direct machine hour worked, £28 will have been added to sales invoices and consequently recovered from customers.
2. What he would do on a calculator – multiple the number of direct machine hours by the OAR.

Having done the first profit centre, it’s tempting to drag the formula to the right in order to quickly replicate it for Accessories Manufacture.

This would result in a formula of =C3*C6 replicating the row numbers and replacing the column letters and is really useful when the calculations in a row are the same across a number of columns ie. relative. 

However, in this case Lewis can’t do that because Accessories Manufacture use labour not machine hours so the formula needed to calculate how much overhead was absorbed is not relative to Apparel Manufacture’s. 

Instead he needs =C2*C6

Gorgeous Threads require IF statements to be used to calculate over and under absorption. Before starting, Lewis again thinks about:

1. The accounting theory – over absorption is when enough money has been absorbed to cover the actual overheads incurred and have some left. Under absorption is the opposite.
2. The spreadsheet function – an IF statement is an instruction to give one of two possible outcomes depending on whether a statement is true or false. In other words, IF the overheads absorbed are more than the actual overheads, then it’s over absorption, if not, it’s under absorption.

Using the function argument

Lewis uses the function argument box to help him write functions. 

In the case of IF statements, it breaks the function down into three sections and automatically adds any punctuation required. Lewis has found this approach, coupled with thinking through the accounting and spreadsheet theory first, makes his production of complete and accurate IF statements more successful:

Having written the IF statement Lewis now manually checks it has produced the result he expected. 

He knows that £236,656 has been absorbed and that this is not enough to cover the actual overheads of £259,354 so the IF statement should return an outcome of “Under”.

It does, so he is happy the function is correct. Because the same calculation is required in the next column, this time he can drag it across as the cells are relative, which will save him time and ensure consistency.

Lewis tidies up the formatting by realigning the cells containing the IF statements to the right, and then adds another row to calculate the amount of under and over absorption:

Minimising variances

The owners of Gorgeous Threads know that there will always be a degree of over and under absorption as OARs are calculated on budgeted figures. 

However, the organisation aims to minimise variances so has a policy that variances of more than 5% should be reported to the Finance Manager for investigation.

Lewis’s penultimate task is to calculate the amount of under or over absorption as a percentage of the actual overheads, formatted as a percentage to two decimal places:

Using conditional formatting

Now he can use conditional formatting to see if either variance needs investigating. To do this he:

1. Highlights the two cells showing the under/over percentages
2. From the Home tab, pulls down the Conditional Formatting menu
3. Hovers over Highlight Cells Rules and selects Between…
4. Types -5% in box 1 and 5% in box 2 then select ‘Green fill and text’ from box 3
5. Presses OK

This highlights that Accessories Manufacture are within the 5% tolerance and that Apparel Manufacture are not and therefore Lewis will report this for investigation.

In summary

Lewis used to find spreadsheets challenging due to the quantity and variety of tasks and functions they can be used for. Since he has started thinking accounting theory through, and relating it to spreadsheet functionality though, he has found it easier. In the next instalment he’ll be using more spreadsheets to tackle some financial accounting tasks.

*  To show formulas in cells rather than their results – click on the Formulas tab, then in the Formula Auditing section click on Show Formulas (you may have to hover over the icons to find it).  Once selected it changes the layout and formatting of the whole sheet but they will revert as soon as it is deselected.

Read the next article in the series now: Advanced aspects of financial accounting.

Gill Myers is a self-employed accounts consultant. She has taught AAT qualifications since 2005 and written numerous articles and e-learning resources.