Management Accounting 1
Assessment Title: Excel Team Assignment (15%) and Individual Self Reflection (10%)
Weighting: 25 Marks: 15 marks technical and 10 mark professional competency
Learning Outcome 4: Formulate and recommend possible solutions using management accounting techniques and tools, in a given scenario, working in teams in some instances.
Objective: The objective of this assignment is to effectively work in teams to formulate and recommend possible solutions in a practical scenario using management accounting techniques and tools, especially excel spreadsheets. The assignment has been set on a topic (ABC) you have not yet covered in order
to develop your research skills as a team.
This assignment comprises two parts as follows:
Part 1 (15%): Work in teams (groups) of 3 members (you must obtain your lecturer’s approval for a different number) to complete the case study using the excel spreadsheet program as noted in the details of assignment.
You need to submit one excel file for question 1. You will also need to submit 1 file to Turnitin for questions 2 to 4. Only the group/Team leader should submit these files, otherwise penalties will apply. Clearly label each file with the group leaders’ student number and name. Include clearly in both files the student names and numbers of each team member.
Word Limit: 1000 words for written questions 2 – 4.
Part 2 (10%): Individual self-reflection on team work; as noted in part 2 below. Each student will need to submit a self-reflection document separately.
Forming Teams – You must discuss the selection of teams with your lecturer, who may decide to allocate you into teams or he/she may decide to form teams using a random process. Any work which has been copied or shared between groups or teams will result in a Fail grade for all students concerned. So please make sure that the answer to this team assignment is your own work and not copied from any source.
Please check the marking rubric for each part to ensure that you have followed all the guidelines for presenting your work.
The assignment will need to be submitted electronically on the portal, via the moodle site. There will be three separate links– one for the excel spreadsheet, another for the team written work and the third for your individual written self reflection on team work.
Please make sure you follow the guidelines noted in your subject outline especially those relating to the presentation of written work, late assignment policy and academic integrity.
PART 1: Technical competency (Assessable Value 15%)
CASE STUDY USING EXCEL SPREADSHEET
EzyTax is a tax accounting firm offering an income tax preparation service for small businesses and individuals. The firm charges individuals $149 per return and small businesses are charged $399.
Individual returns are normally relatively straightforward to process and the firm uses the ATO’s free etax software to prepare and submit the returns. The individual returns do not tend to vary much in the workload required to complete them.
The processing of small business tax returns is more complex and requires a specific software package that the EzyTax has purchased for an annual licence fee of $25,000 plus $2.50 for each return processed. Processing of business returns varies as to the workload each one requires.
The manager of the firm, Martine Matilda, is concerned that last year only a small profit was made despite the volume of work being higher than ever. She commented: “We are good tax accountants for our clients but we don’t seem to be keeping our own house in order!”
To help improve the situation Matilda decided to recruit a dedicated management accountant with a proven track record, Katrine Pedersen.
On her first day in the job, Matilda tells Pedersen “I want you to get to the bottom of our recent poor profitability but I do not want you to buy in any expensive ‘solutions’ which may quickly become out of date and are still prone to error. Also any new systems must be understandable to staff and clients, whom we must justify our price to.”
One of the first concerns Pedersen has is that EzyTax’s pricing is not optimal. She has noticed that whilst the firm has been extremely busy with a record number of 9,000 small business returns last year, they have had relatively few individual returns (3,000). Katrine has also done some basic market research where she has found other tax agents charging individuals as little as $100 for a basic return.
As a starting point, Pedersen decides to use an Activity Based Costing (“ABC”) approach to help assess the cost of preparing each type of return. From the firm’s timesheet and other records she is able to access the following information for the previous year:
Interview client $1,615,000
Obtain Missing data $800,000
Input data $390,000
Verify return with client $875,000
Rectify errors after ATO query $40,000
Submit return $50,000
Other information Pedersen collected is:
– On average each individual takes 20 minutes to interview and each business takes 2 hours. Last year a total of 19,000 hours were spent interviewing clients.
– Follow-up conversations to obtain missing data relates to business returns only.
– Data input took a total of 9,750 hours, with it taking an average of 15 minutes for an individual and 1 hour for a business.
– Verification of the return with the client took an average of 10 minutes for individuals and 55 minutes for small businesses.
– There were 1,065 returns requiring rectification and only 22 of these were for individuals.
– Submission time was approximately the same across all returns.
1. Use ABC to estimate the cost of an individual and a business return. Make clear any assumptions you have made.
(Note: 2 of these 14 marks are allocated for clear presentation and a good use of links and automated functionality in Excel).
Hint – For the activities which apply to both products, such as “interview client”, your initial analysis
may look like this:
Then you may apply this to the products as below:
2. How might these estimates help explain the poor profitability experienced last year and what
implications do they have for the firms’ pricing policy?
3. Explain the advantages and disadvantages of ABC, relating your discussion to EzyTax.
4. Katrine uses the ABC approach based on the above data as a ‘starting point’. Can you suggest any other ways of altering the firm’s costing and pricing policies which will be more beneficial to EzyTax?
2 marks are allocated for presentation, grammar, spelling and appropriate Harvard referencing where needed
2 marks are allocated for appropriate teamwork as evidenced by your minutes and any other observations made by your lecturer
Total Technical Mark = 30 marks, weighted to 15% overall
Points to follow when creating your team excel workbook:
1. Create your excel workbook for the assignment – It is best to create one workbook and name each worksheet in the workbook with the question number, instead of creating many different files for each question. This will create consistency in submission and only one file per team per assignment will need to be submitted
2. Naming the workbook and worksheets – Name the workbook with a student name in the group and their number and save under this name. Name the worksheet in the workbook with the question number.
3. Each question must begin on a fresh worksheet not continued from the previous question.
4. Minutes of the meeting must be attached for group assignment separate on a word document.
5. Cover page for assignment – make sure you have a cover page with a list of the student names and numbers in the team.
6. Use the excel formulas in your worksheet. Do not use calculators to arrive at the answer.
The team should have a minimum of three meetings (the team may have as many meetings as they need to) and should maintain minutes of those meetings which should be included with the team assignment as an attachment. The minutes of meetings should (at the very least) contain –
1) Where and when the meeting was held
2) People present; absent; apologies
3) What was discussed, any disagreements/conflicts, points that were agreed on;
3) The follow up action relating to each member of the team;
4) Any other relevant matters, anything else that is significant to the successful completion of the assignment.
An agenda template and a template for minutes of meeting are attached (after Part 2 of the assignment information) for your team to use.
PART 2: Professional Competency Skills (Assessable Value 10%)
Individual Reflection on Team work
Word count: 750 words
Aim: The content task in this assignment is completed in teams. It is important that you reflect on this experience to learn about the ways in which teams work and the ways that you work in teams.
Refer to the content in the power point presentation on team work and check management and communication text books for further detail on team communication. Your reflection should incorporate the information from your reading as you will use relevant theory to critically reflect on your team experience.
What You Need to Do:
1. Refer to the content in the powerpoint presentation and communication text books on teamwork
2. Read the guide below on reflective writing style
3. Check the marking rubric to see how marks are allocated for this assignment
4. Incorporate relevant theory to critically reflect on your experience. Ensure you include the information under all of the headings listed above. Ensure you reference a minimum of three sources
5. Edit your work carefully to ensure it is well written and error free. If you would like any help with this, see Academic Success Centre staff.
Task: Write your reflection on the team experience. Your reflection must incorporate the following headings.
? Group description e.g. members (use pseudonyms), meetings (e.g. times, locations, attendance)
? My role
? Others’ roles
? Group stages
? Conflict in group and, if applicable, how the conflict was resolved
? Leadership and decision making in the group
? Ways that the group experience worked well and ways it could have been improved
Reflective Writing Guide:
Reflective writing is:
• your response to experiences, opinions, events or new information
• your response to thoughts and feelings
• a way of thinking to explore your communication practice
• an opportunity to gain self-knowledge
• a way to achieve clarity and better understanding of the ways you communicate
• a chance to develop and reinforce writing skills
• a way of making meaning out of what you study
Good reflective writing usually involves four key elements:
1. reporting and responding to a critical issue or experience;
2. relating this issue or experience to your own knowledge in this field;
3. reasoning about causes and effects of this issue/experience according to relevant theories or literature and/or similarities or differences with other experiences you’ve had; and
4. reconstructing your thinking to plan new ways to approach the issue or engage in similar experiences in the future
Reflective writing is different from other university styles, such as academic essays. It uses ‘I’ and ‘we’ and has the
following language features:
1. People are added to the writing when the writer is expressing personal experiences or opinions e.g. I, me, you, we, us.
2. People (i.e. I, me, you, we, us) are removed when referring to the theory.
3. The actions (verbs) are usually those of feeling and thinking e.g. feeling, felt, considered, experienced, wondered, remembered, discovered, learned.
4. When reflecting the language is usually modified e.g. may, perhaps, might.
QUT library (2014) Reflective writing (http://www.citewrite.qut.edu.au/write/reflectivewriting.jsp, accessed
4 June 2014)
Team Name: ………………………………………………………… Team Members: ……………………………………………………..
Date @ Time
Any other business
MINUTES OF MEETING
Team Name: ………………………………………………………… Team Members: ……………………………………………………..
Date @ Time
ITEM NOTES ACTION
Present/Welcome Names of attendees. Name of Chair
Minutes Minutes of previous meeting accepted/not
accepted as an accurate record.
items Summary of discussion and decisions Action to be
taken before next meeting and name of person responsible
Any other business
MINUTES CONFIRMED . . . . . . . . . . . . . . . . . . . . . (SIGNED)
. . . . . . . . . . . . . . . . . . . . . (DATE)
Assignment Marking Sheet (Technical only)
The excel spreadsheet well designed (easy to understand, applicable and appropriate) and are outstandingly well linked, so that simulation is possible.
Explanation of poor profitability
Implications for pricing policy 2.00
Advantages of ABC for EzyTax
Disadvantages of ABC for EzyTax
Q4 Changes to costing policies
Changes to pricing policies 2.00
Presentation, grammar &
Teamwork (from minutes and
Weighted Total 15.00
Signature: ……………………………………………………………………… Date: …………………
~~~~~~~~~~~~~~~~~~ End of Assignment ~~~~~~~~~~~~~~~~~~~