© TAFE NSW – Higher Education Semester 1, 2021 Page | 1
ACACT302A Advanced Management Accounting
Case studies - EXCEL Assignment – Semester 1 2021
Case studies - EXCEL assignment – Assessment 3 (20%)
Due: Friday 7th May at 10pm via upload to Moodle
Format: to be submitted as an excel spreadsheet.
The assignment comprises 4 questions and is worth 150 marks.
The assignment includes the following topics:
· Topic A Activity-based costing (80 marks)
· Topic D Strategy and Balanced Scorecard (30 marks)
· Topic E Process costing (20 marks)
· Topic E Hy
id costing (20 marks)
Responses MUST BE PROVIDED IN AN EXCEL SPREADSHEET
✓ Please create your own spreadsheet as a template will not be provided. Use Office
365 (not google sheets) for your assignment response.
✓ Each question response should be prepared in a separate tab of the one spreadsheet.
Clearly identify each question in the spreadsheet.
✓ Only one file is to be uploaded through the Moodle.
✓ Formulas are expected to be used for all calculations. Responses without formulas will
eceive ZERO marks (with the exception of question 2).
✓ Responses are expected to be set out in detail.
✓ Marks will be awarded for clarity and formatting as well as co
ectness.
✓ Assignments not presented in EXCEL will not be accepted.
✓ The assignment must be your own work so please do not download templates from
the internet nor use weekly solution spreadsheets uploaded to Moodle by your
teacher(s).
© TAFE NSW – Higher Education Semester 1, 2021 Page | 2
Question 1 (80 marks)
Lucia's Hospitality Group operates a restaurant, an event space as well as providing catering
for private events. They are operating at full capacity. At present, Lucia's Hospitality Group
uses a single, group-wide overhead allocation rate. Pricing is set at 145% of cost.
However, the management accountant believes they can make better process
improvements if they implement activity-based costing. She has gathered the following
udget information:
The proposed allocation bases for overhead are:
Restaurant Events Catering Total
Revenue $1,326,000 $3,120,000 $1,170,000 $5,616,000
Food & drink costs $624,000 $1,144,000 $546,000 $2,314,000
Direct labour cost $234,000 $374,400 $156,000 $764,400
Depreciation $28,000 $23,000 $13,000 $64,000
Transport costs $15,000 $18,000 $40,000 $73,000
$2,400,600
Overhead costs:
Hospitality support staff $385,000
Marketing costs $350,000
Cleaning $60,000
Maintenance $45,000
Administration $60,000
Utilities $80,000
$980,000
$1,420,600
Number of guests per year 15, XXXXXXXXXX, XXXXXXXXXX,600
Number of services per year XXXXXXXXXX
Maintenance Depreciation
Cleaning Number of services per yea
Hospitality support Number of guests per yea
Marketing Revenue
Utilities Food & drink costs
Administration Equally
© TAFE NSW – Higher Education Semester 1, 2021 Page | 3
Required:
1. Calculate the budgeted cost per service for restaurant, events and catering using direct
labour costs to allocate overhead costs (2 marks).
2. Calculate the budgeted cost per service for restaurant, events and catering using the
proposed cost drivers under activity-based costing to allocate overhead. Prepare a table to
measure % usage of each activity by each service area (24 marks).
3. Prepare a full profitability report for each service type under the two costing methods
using data calculated under parts 1 and 2 (19 marks).
4. Illustrate your calculations with tables of useful data and visualisations (e.g. charts,
conditional formatting) (25 marks).
5. Prepare a report for management explaining your findings. Prescribe a set of
ecommendations for management (10 marks).
The response to this question is to be prepared using excel.
Question 2 (30 marks)
Choose a well-known for-profit organisation that you are interested in. Email your teacher
for approval of the organisation you have chosen.
Required:
1. Provide an overview of the organisation, its operations, and the industry it operates in.
Determine whether the organisation is following a cost leadership or product
differentiation strategy (150 words) (3 marks).
2. Construct a balanced scorecard for your chosen organisation which is in line with its
strategy. Students who copy an existing scorecard from the internet, textbook, class
notes etc, will be awarded ZERO marks. The scorecard is to include a mix of financial and
non-financial measures that are clearly linked to the organisation’s strategy and its
operations. Strategic objectives and performance measures are to be included for all
four perspectives (financial, customer, internal business process, and learning and
growth). You are to include six measures in total with at least one for each of the four
perspectives (9 marks).
3. Explain how each measure you have chosen is critical to the organisation achieving its
strategy (250 words) (12 marks).
4. Choose two measures you would recommend the organisation not include in their
alanced scorecard and explain why (100 words) (4 marks).
Target and actual performance columns / data are not required.
The response to this question is to be prepared using excel and is to be well-presented (2
marks).
© TAFE NSW – Higher Education Semester 1, 2021 Page | 4
Question 3 (20 marks)
Ha
our Ltd makes kayaks. Materials are added at the beginning of the process and
conversion costs are incu
ed uniformly through the production process. At the beginning of
June, work in process is 80% complete and at the end of the month it is 70% complete.
Ha
our Ltd uses the weighted-average method of process costing.
Other data for the month includes:
Required:
a) Calculate equivalent units for direct materials and conversion costs.
) Calculate cost per equivalent unit for direct materials and conversion costs.
c) Summarise the costs to account for and assign them to units completed (and transfe
ed
out) and to units in ending work in process.
d) Prepare journal entries to record transfe
ing of direct materials to processing and from
processing to finished inventory.
e) What might motivate the production manager to over- or under-estimate the
percentage of completion of conversion.
The response to this question is to be prepared using excel.
Beginning work-in-process inventory (units) 200
Units started (units) 850
Units placed in finished goods (units) 900
Conversion costs $270,000
Cost of direct materials $400,000
Beginning work-in-process costs:
Materials $100,000
Conversion $70,000
© TAFE NSW – Higher Education Semester 1, 2021 Page | 5
Question 4 (20 marks)
Jason Ltd manufactures two types of curtains: standard and decorative. The company has
five operations: cutting, sewing, lining, trimming and threadwork.
The decorative curtain goes through all the operations, but the standard curtain does not
equire trimming or threadwork.
Jason Ltd needs to determine the cost of two work orders for the month of May.
Work order M66 is for 2,000 standard curtains
Work order P61 is for 1,500 decorative curtains
Assume the above two work orders are the only production planned for May.
Cost of direct materials purchased:
Standard curtains $130,000
Decorative curtains $300,000
There was no opening inventory of any direct materials at the beginning of May.
Closing inventory values for May were as follows:
Closing direct materials – standard curtains $25,000
Closing direct materials – decorative curtains $35,000
Budgeted conversion costs for each operation for May are as follows:
Cutting $25,000
Sewing $46,000
Lining $68,000
Trimming $40,000
Threadwork $55,000
Conversion costs are all applied on a per unit basis.
Required:
a) Calculate the budgeted conversion cost rates for each operation.
) Using the information calculated in (a), calculate the budgeted cost of goods
manufactured for the two May work orders.
c) Calculate the cost per curtain for each of the two work orders.
The response to this question is to be prepared using excel.