You are given the following details of the books of Walker Industries, 453 Trander Rd, BRISBANE 4000 at 1 June
AYB225 - Report Page 1
QUEENSLAND UNIVERSITY OF TECHNOLOGY
QUT Business School
School of Accountancy
AYB225 Management Accounting
SEMESTER ONE 2020
ASSESSMENT ITEM TWO:
EXCEL SCHEDULES AND MANAGEMENT REPORT
(Weighting Total: 25%)
Due Date and Time: Friday 15 May 2020, 11:59pm
Submission: Online submission via Blackboard
Group or Individual: Individual
Length for report: 1000 words
This assessment item assesses the following learning outcomes:
Knowledge and Technological Skills (KS)
1.2 Apply technical and technological skills appropriate and effective for real world business
purposes and contexts.
Higher Order Thinking (HO)
2.2 Exercise independent judgment and initiative in adapting and applying knowledge and
skills for effective planning, problem solving and decision making in diverse contexts.
Professional Communication (PC)
3.1 Use information literacy skills, and communicate effectively and professionally in written
forms and using media appropriate for diverse purposes and contexts.
AYB225 - Report Page 2
This assignment is an individual assignment. This means you cannot collaborate with
anyone in any way in the completion of the Excel Schedules and the Management
Report.
Submission Requirements:
Your submission has two parts. All parts must be submitted to Blackboard by the due date
and time for a complete submission of this assessment item. Refer to the instructions on
Blackboard under Assessment Item 2 regarding uploading of files.
The following files must be uploaded into Blackboard:
1. Excel file containing the completed Schedules 1, 2, 3 and 4 as well as the ‘Budget
Assumptions’ worksheet provided.
2. Management Report to Partners
Make sure you check your files once uploaded to ensure it is readable.
No marks will be given for submissions where the file is co
upt and unreadable.
This unit uses the Turnitin software to detect plagiarism. Turnitin compares submissions
to other submissions, both cu
ent and past, and online sources such as textbooks,
scholarly articles, websites and blogs.
Late Assessment and Extensions
In both your QUT study and your professional life you will be expected to meet deadlines. In
keeping with this expectation, assessment submitted after the due date will not be marked and
will receive a grade of 1 or 0%. If special circumstances prevent you from meeting the
assessment due date, you can apply for an extension. If you don’t have an approved extension,
you should submit the work you have completed by the due date and it will be marked against
the assessment criteria.
AYB225 - Report Page 3
Congratulations!
You have been successful in your application for a part-time position at T&K Taxation
Services commencing 1st January 2020.
T&K prepare tax returns and provide taxation advice to a
oad range of clients across Brisbane.
The firm was established in 2010 by Te
y Thompson and Ke
y Chua who met while studying
accountancy at Queensland University of Technology. Your role will focus on supporting Te
y
and Ke
y with administrative and accounting issues. The following events have taken place.
01 January 2020
Te
y and Ke
y are pleased that you could join their team, and just in time to finalise the budget.
They have assigned your first task, to complete the 2020 budget using the 2019 actual figures
together with some basic budget assumptions they have provided to you. Despite having some
usier months around key reporting deadlines in May and October, Te
y asks that you keep
the budget simple and to phase the annual budget evenly over the coming 12 months.
Te
y and Ke
y are optimistic about the year ahead and have set a challenging budget for their
staff to work towards. There have been rumours that a competitor might be opening up an office
in the same building that T&K operate, however Te
y and Ke
y are not concerned as they
have built up a loyal client base in their ten years of operation.
01 March 2020
The rumours were co
ect. Only days after the 2020 budget was signed off by Te
y and Ke
y,
R&H Block opened a
anch right next door. R&H Block are a nationwide provider of taxation
services and are well known in the industry for providing fast and efficient taxation services at
an affordable price.
While Te
y and Ke
y remain adamant that any impact of R&H on their business will be
minimal, they think it would be wise to at least consider some potential downside cases. They
have asked you to assist them with this task by preparing a scenario analysis assessing the
potential impact on their profitability if they lose 10% or 20% of their billable hours.
AYB225 - Report Page 4
01 July 2020
The impact of R&H Block has been more severe than Te
y and Ke
y had anticipated. Not
only have they lost clients, but some of their own staff have been poached by the competitor.
In response Te
y and Ke
y have had no option but to reduce their rates and offer a pay increase
to their most valued employees.
On top of everything, a virus pandemic has had a significant impact on the cashflow of many
clients’ businesses and as a result some clients are delaying their need for tax services while
others are unable to pay their bill.
With all these unanticipated changes, Te
y and Ke
y feel that the original budget is redundant,
and they are at a loss to understand how their business has performed for the first six months of
the year. You suggest that ‘flexing’ the original budget in response to changes in actual activity
will provide a revised outlook and more accurate feedback on performance to date, helping to
identify actions that can get them back on track.
They like this idea and ask you to go ahead and ‘flex’ the original budget and to calculate
volume and price variances for their revenue and professional labour costs. They have asked
that you prepare a management report outing your main findings and any advice you have for
improving their budgeting process going forward.
**The full requirements of the assignment are on pages 4 and 5**
AYB225 - Report Page 5
ASSIGNMENT TASKS:
The partners of T&K Taxation have asked you to help with their budgeting process and analyse
the results. The requirements are described for you below;
TASK A: EXCEL
Assist T&K Accounting Services with Budgeting and Variance Analysis
10 marks
a) Using the 2019 actual data provided in the ‘Schedule 1’ and the assumptions provided
in ‘2020 Budget Assumptions’ worksheet of the student workbook, complete a budget
for T&K Taxation for the year ending 31 December 2020. Assuming the budget is
phased evenly over the 12 months.
Complete your calculations using Schedule 1 provided in the student workbook
2 marks
) Using the budget prepared in (a) above, provide a scenario analysis using the two
following changes to billable hours due to the new competitor:
i) 10% decrease
ii) 20% decrease
Complete your calculations using Schedule 2 provided in the student workbook
2 marks
c) Prepare a flexible budget analysis using the actual billable hours for the six months
ending 30 June 2020.
Complete your calculations using Schedule 3 provided in the student workbook
2 marks
d) Compute variances on both revenue and professional labour costs to assist in the
explanation of some of the key variances. Specifically, calculate the following:
i) Sales volume variance for revenue split into volume and price variance
ii) Professional labour efficiency variance
iii) Professional labour price variance
Complete your calculations using Schedule 4 provided in the student workbook
2 marks
e) You are eligible for an additional 2 marks for professional presentation and calculations
within the excel worksheets. Schedules should be professionally presented and use
formulas where possible that link cells from the ‘2020 budget assumptions’ worksheet
and other schedules. See the notes for this assessment on p7 for further information.
2 marks
AYB225 - Report Page 6
TASK B: MANAGEMENT REPORT
15 marks
Write a Management Report to the partners of T&K evaluating their budgeting process. The
eport should include the following:
a) Analysis of key variances from 2019 Actuals to 2020 Budget. Include a graph to show
this visually. The graph should use information from Schedule 1. Your analysis should
include the results of the scenario analysis in Schedule 2.
4 marks
) Analysis of key variances between budget and actuals for the six months ending 30 June
2020 including commentary on price and volume variances for revenue and professional
labour costs. Your commentary should provide explanations and possible reasons for
the variances based on the background information. A graph should be included as part
of the analysis to highlight key variances. The graph should use information from
Schedule 3 and 4.
5 marks
c) Using the background information, your knowledge of management accounting
practices and the information provided in Schedules 1-4, provide THREE
ecommendations to T&K Taxation on how they can improve their budgeting process
going forward.
6 marks
Write your report using the proforma provided on blackboard.
Your report should be no more than 1,000 words
AYB225 - Report Page 7
NOTES FOR THIS ASSESSMENT:
In addition to the tasks the partners of T&K Accounting Services have asked you to complete,
they have indicated that there are a few issues you are not to include:
• Use all data as given, including all figures without trying to fine-tune those figures in
any way.
• The partners have stressed that your responses to the questions should be based only on
the facts with which you have been provided. Do not go outside these facts.
E
ors and omissions
If you believe there is an e
or in the assignment, or that an essential instruction has been
omitted, please contact the Unit Coordinator.
Format and presentation of assignment
All students are required to follow the standardised format for their assignment. You must
address items and follow the setting out as described. The assignment must be completed using
provided Schedules in Excel and Proforma in Word (see Blackboard) and comply with all
instructions on all assignment documents provided. Marks are given for presentation in both
the excel workbook and management report.
Excel workbook presentation
You are eligible for up to 2 marks for your workings in Excel. The following will be considered
to obtain these 2 marks:
• Formulas will need to refer to other cells