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...

HiI only want help in Schdule 3 and 4 of the excel .thanks


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 corrupt and unreadable. This unit uses the Turnitin software to detect plagiarism. Turnitin compares submissions to other submissions, both current 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 broad range of clients across Brisbane. The firm was established in 2010 by Terry Thompson and Kerry Chua who met while studying accountancy at Queensland University of Technology. Your role will focus on supporting Terry and Kerry with administrative and accounting issues. The following events have taken place. 01 January 2020 Terry and Kerry 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 busier months around key reporting deadlines in May and October, Terry asks that you keep the budget simple and to phase the annual budget evenly over the coming 12 months. Terry and Kerry 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 Terry and Kerry are not concerned as they have built up a loyal client base in their ten years of operation. 01 March 2020 The rumours were correct. Only days after the 2020 budget was signed off by Terry and Kerry, R&H Block opened a branch 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 Terry and Kerry 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 Terry and Kerry had anticipated. Not only have they lost clients, but some of their own staff have been poached by the competitor. In response Terry and Kerry 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, Terry and Kerry 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 b) 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 report 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 b) 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 recommendations 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. Errors and omissions If you believe there is an error 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