AAT Advanced Diploma Synoptic Assessment

AAT Advanced Diploma in Accounting
Synoptic assessment – Sample assessment 1 –Task 2.3

AAT Advanced Diploma synoptic assessment

Sample assessment1

Task 2.3 – Assessment book

Candidate

This sampletask is for familiarisation purposes only and must not be used in place of a ‘live’ task.
When you feel prepared to sit the live assessment please contact your Training Provider who can schedule a live assessment for you.

All rights reserved. Reproduction is permitted for personal and educational use only. No part of this content may be reproduced or transmitted for commercial use without the copyright holder’s written consent. 1

AAT Advanced Diploma in Accounting
Synoptic assessment – Sample assessment 1 –Task 2.3

Task 2.3(14 marks)

You are Sam Jones, a part-qualified Accounting Technician. You work for BLM & Co, a business which manufactures and sell sinks. BLM & Co is owned and run by Brian and Lakmani Moore in partnership.

You cover all aspects of bookkeeping and accounting for the business.

Today’s date is 31 January 20X9.

You are preparing the final accounts for BLM & Co for the year ended 31 December 20X8.

The statement of profit or loss for BLM & Co shows a profit for the year ended 31 December 20X8 of £250,000.

The business is still operated as a partnership.

You are given the following information arising from the partnership agreement:

Partners earn commission each month of 1.5% on the sales they personally made in that month, provided these sales are greater than £10,000. They earn no commission in the month otherwise.
Lakmani is entitled to a salary of £25,000 per annum.
Brian has taken drawings of £91,200 over the year, and Lakmani has taken £84,400.
Interest on drawings has been calculated at £300 for Brian and £180 for Lakmani for the year ended 31 December 20X8.
The residual profit after adjustments is shared between Brian and Lakmani in the ratio 3:2.

Details of the partners’ sales are in a separate spreadsheet.Download the spreadsheet file from the assessment environment.

Save the spreadsheet file in the appropriate location, and rename itin the following format:
‘your initial-surname-AAT no-dd.mm.yy-Task2.3’ in the appropriate folder.
For example:J-Donnovan-123456-12.03.xx-Task2.3

Ahigh degree of accuracy is required.You must save your work as an .XLS or .XLSX file at regular intervals to avoid losing your work.

You are required to prepare the appropriation account for BLM & Co for the year ended 31 December 20X8.

(a)Use the partnership data supplied in the table in the spreadsheet in the following manner:

Format all headings to havebold size 12 font.
Merge and centre ‘Sales’ and ‘Commission’ over their respective columns. Align the month column, including the heading, to the left. Align the other columns, including the name headers, to the right.
Use a custom list to sort the whole table from January to December.
Use an IF statement to calculate the commission on sales for Brian and Lakmani for each month.
Show your results in columns D and E.
Total each of the columns B:E.
In the shaded area, Custom Format the range B6:E18 in GBP currency with the thousand separator and twodecimal places (e.g. £3,200.00). (4 marks)

The task continues on the following page.
Task 2.3 (continued)

(b)(i)Design a partnership appropriation statement in the spreadsheet to appropriate the profit for the year ended 31 December 20X8 between the two partners in accordance with the partnership agreement. Start with the profit for the year, and show clearly:

Separate lines for each of the entitlements or charges adjusting this profit. Provide direct references from your answer to (a) where possible.
The residual profit available for appropriation.
Each partner’s share of that residual profit.
The total amount distributed to each partner for the year.

(ii)Custom format your statement to show GBP currency with the thousand separator, no decimals, and black font for positive. Ensure that any negative figures are Custom Formatted to red with a minus sign. (8 marks)

Lakmani now asks you what figure the profit for the year would need to be if she were to have a total amount distributed to her of £120,000. Assume all other data is unchanged.

(c)Amend your spreadsheet to show this:

Open a new worksheet and name it ‘Goal Seek’.
Return to the ‘BLM 1’ worksheet. In your partnership appropriation statement, use Whatif Goal Seek analysis to amend your data.
When the completed Goal Seek dialogue box is showing – but BEFORE you click the OK button – take a screenshot (without pasting). Then complete the Goal Seek analysis.
Return to the ‘Goal Seek’ worksheet and paste the screenshot.(2 marks)

At the end of this task you should have one spreadsheet(saved as an .XLS or .XLSX file)to upload to the assessment environment.This should have two worksheets titled ‘BLM 1’ and ‘Goal seek’, with information and data in them.