The second 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 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 the last article Lewis was completing some management accounting tasks. Today he’s working on the year-end accounts for Gorgeous Threads & Co, the clothing manufacturer he works for, which is owned and run as a partnership by Mel and Chris.
Producing year-end accounts
Lewis’s colleague normally produces the final accounts but has called in sick today and Lewis has been asked to take over. He finds the spreadsheet on the organisation’s shared computer drive, and discovers that the accounts have been prepared up to the extended trial balance (ETB).
Click here to download the spreadsheet: Advanced Synoptic Series – Part 2 and replicate Lewis’s tasks.
Gorgeous Threads expects all the finance team to demonstrate competent accounting knowledge and precise spreadsheet skills simultaneously, to ensure that spreadsheet functions used achieve the accounting tasks required. This means that Lewis has to precisely follow instructions regarding formatting and using formulas.
He must be very methodical in his work and think about the accounting principles he needs to apply, to help him work out how to use spreadsheets to prepare the accounts accurately.
Lewis can see that there is a problem with the ETB as the Trial Balance (TB) columns do not match, and neither do the Statement of financial position (SFP) columns ones. He hasn’t really got time to check every cell, so decides to start by checking those that are most likely to have errors:
- ledger balances linked to other sheets
- addition and subtraction of adjustments
- extension of the balances
He enables show formulas (discussed in part 1) so he can quickly and easily see the cells that contain formulas and links. The extract below shows him that both sales and purchases are linked to the Workings sheet.
Net sales = sales less sales returns
Net purchases = purchases less purchase returns plus carriage inwards
In the Workings sheet he selects the net sales cell, and uses trace precedents from the formula auditing section of the formulas tab to show which other cells have been used to calculate the value:
By looking at the formula bar he can see that the sales returns have been correctly deduced from the sales.
He does the same for the net purchases:
This time he can see the formula is incorrect as B9, carriage inwards, has been deducted instead of added.
Balancing the trial balance
He amends the formula and then checks the corrected figure is reflected in the ETB, making the TB columns balance.
It is, and as Lewis knows a change to the net purchases will alter the profit, he decides to see whether if he recalculates the profit the ETB will balance.
Firstly, he thinks about how he would calculate the profit or loss manually on an ETB – he would simply find the difference between the two Statement of profit or loss (SPL) columns and enter it on the smaller side, just as if he were manually balancing a T account.
He can see that the debit column has the smaller total so writes the following formula in F31:
However, this gives him an error notice for a circular reference as the net profit figure he is trying to calculate would be included in the column sum formula that is already adding up the debit column.
Lewis’s colleague had manually typed in the profit figure to get round this problem but Lewis uses the formula =G32-SUM(F5:F30) to remove the need for manual inputting and achieve the correct accounting calculation:
The two SPL columns now balance, however, the two SFP columns still do not, even though the spreadsheet was correctly formatted to enter the profit figure into the credit column.
Manual double entry principles
Therefore Lewis turns his attention to the adjustments and notices that cell F25 has a little green triangle in the corner:
When he clicks on it, he gets an inconsistent formula warning, meaning the formula is different to those in cells around it.
Lewis’s understanding of manual double entry principles means he knows that if you have a credit adjustment to a debit balance, it should be deducted. He can see from the figures, and when he looks at the formula bar, that this has happened, so he is not worried by the warning in this instance.
Has the ETB been extended correctly?
Lewis doesn’t find any errors in the adjustments so finally checks that the ETB has been extended correctly. He draws on his knowledge and understanding of accounting principles to help him check that all the income and expenditure accounts are on the SPL and everything else is on the SFP.
Inventory doesn’t quite fit this rule, so he pays it particular attention:
The ledger balance of £63K must be the opening inventory as closing inventory is calculated as a year- end adjustment.
Opening inventory goes on the SPL as part of the cost of goods sold (COGS) calculation along with closing inventory. Closing inventory is shown on the SFP as well as the SPL as it is the value of what the organisation has left at the end of the year and therefore Gorgeous Thread’s owns, in other words an asset.
As the inventory is in the correct columns, he looks at accounts which can have debit or credit balances such as the Bank and VAT:
In this case, the bank balance is overdrawn but has been correctly extended to the credit column on the SFP as it is a current liability. The VAT is correct as well so he moves onto prepayments and accruals as they are a common sources of confusion:
Lewis now spots an error as he can see that the accrual row has not been extended correctly. There isn’t a ledger balance and the adjustment is in the credit column so the figure should be extended to a credit column as well. It’s correctly in the SFP, as other payables are current liabilities, just in the wrong column.
The prepayments are correct so Lewis adjusts the accruals figure and can see that this change has balanced the ETB:
Lewis has dealt with a number of tricky accounting areas whilst correcting Gorgeous Thread’s ETB. More detailed articles are available on accounting adjustments in ETBs, which focus on the fundamental accounting theory needed to inform all accounts preparation whether using spreadsheets or not.
In the next instalment Lewis will not only have to prepare the appropriation account now he has the correct profit figure, but he will also have to effectively communicate his understanding of this integral partnership report.
Gill Myers is a self-employed accounts consultant. She has taught AAT qualifications since 2005 and written numerous articles and e-learning resources.