The third 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 are working 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.
In part two Lewis began producing Gorgeous Threads & Co’s final accounts. He corrected the extended trial balance (ETB) which had been started by a colleague and calculated the profit for the year figure. Now he needs to produce an appropriation account to show how that net profit is shared between the organisation’s two partners, in accordance with their partnership agreement.
As with Lewis’s previous tasks, he knows that in order to accurately prepare an appropriation account he will have to think through the manual accounting theory, and use his knowledge to help him decide which spreadsheet functions, formula and formatting to use to achieve the right results.
Gorgeous Threads have precise instructions for preparing financial information using spreadsheets that they expect the accounts team to follow. This includes usings separate worksheets for subcalculations and details, proformas for consistency, writing formulea, linking sheets and using relative and absolute cell referenceing.
This is what Lewis finds in the spreadsheet:
Click Advanced Synoptic Series – Part 3 Spreadsheet to download it and replicate Lewis’s tasks.
Lewis starts by pulling through the net profit from the ETB:
- C2 =
- Click on ETB tab and cell I31 (which is the net profit in the credit column of the SFP)
Next he calculates Chris’s salary:
- B4 =
- Click on Workings tab and cell B17
- Type *12 (to calculate the annual figure)
- As Mel’s salary is directly under Chris’s in both worksheets, Lewis can use relative cell referencing (discussed in part 1) to drag the formula from B4 to B5
Interest on capital is linked as well but this time to both the ETB and Workings worksheets. Chris’s will be:
- B7 =
- Click on ETB tab and cell I8 (which is Chris’s capital balance in the credit column of the SFP – be mindful not to link to the TB columns in case there are any adjustments)
- Type *
- Click on Workings tab and cell B20
As interest on capital is 3% for both partners its cell reference can be made absolute* in formulea, enabling Lewis to drag the formula from B7 to B8 to calculate Mel’s interest:
- Click back on B7
- In the formula bar, place the curser between B and 20 then press F4 on your keyboard. This is the shortcut to insert $ signs both before the letter and in-between the letter and number thereby absoluting the cell with that reference.
- Drag B7 to B8
Please note, calculating interest on capital has been included here as a spreadsheet skill rather than an accounting one.
Next Lewis needs to calculate all the subtotals:
Lewis is going to use the subtotals to calculate the how much profit is left for distrubution. However, he needs to think about the accounting theory first:
- Partnership agreements set out rules for sharing profits and losses
- Partners claim both salaries and interest on capital so they are deducted from the profit before what’s left is shared in the agreed ratios
- Partners are charged interest on drawings so it is added back to the profit before it is shared
Therefore, he write the following formula:
Lewis calculates that the residual available for distribtion is actually a loss of £1,810. Again he thinks about the theory before he continues:
- The terms of the agreement must be followed even if the has made a loss or that as a result of the agreement, a profit is turned into a loss
- Losses are shared between partners in the same way as profits
He has applied the first point and now shares the loss by linking to the profit sharing ratios on the Working sheet and absoluting the loss figure in C13 on the Appropriation Account worksheet:
He also calculated the final subtotal and checked that it matched the -£1,810, reformatted columns B and C to remove the inconsistencies and amended the headings.
Lewis is sure his work is correct but is unsettled by the loss. He therefore talks to his manager who asks him to write short but clear notes which firstly explain appropriation accounts, then analyse Gorgeous Thread’s appropriation account and finally identify the result.
This is what he writes:
Lewis’s manager is impressed with his notes, commenting that they are comprehensive but concise and will effectively communicate the points to Chris and Mel as neither of them have financial backgrounds.
As Lewis has shown an aptitude for writing his manager has asked him to prepare some further explanations, for a new apprentice who will be joining the team, on the interrelationship between financial and management accounting, which we will look at in part four.
* This means that when the formula is dragged from one cell to another the cell with the absolute referencing will stay the same, in other words it’s the opposite of relative cell referencing.
Gill Myers is a self-employed accounts consultant. She has taught AAT qualifications since 2005 and written numerous articles and e-learning resources.