This is the first in a series of articles which will explore some of the trickiest areas at Advanced level.
We are 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 is Lewis’s job to calculate any 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 and replicate Lewis’s tasks.
Before he can start calculating the over or under absorption he needs to correct the formatting as his colleague has not 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.
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:
- 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.
- What he would do on a calculator – multiple the number of direct machine hours by the OAR.
Having done the first profit centre it is 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:
- 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.
- 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.
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:
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:
Now he can use conditional formatting to see if either variance needs investigating. To do this he:
- Highlights the two cells showing the under/over percentages
- From the Home tab, pulls down the Conditional Formatting menu
- Hovers over Highlight Cells Rules and selects Between…
- Types -5% in box 1 and 5% in box 2 then select ‘Green fill and text’ from box 3
- 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.
Lewis used to find using 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 will 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.
Gill Myers is a self-employed accounts consultant. She has taught AAT qualifications since 2005 and written numerous articles and e-learning resources.