© 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%)...

1 answer below »
Question is in attached files I need answers in excel spread sheet, thanks.


© 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 Hybrid 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 receive 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 correctness. ✓ 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 budget 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,600 20,800 15,600 Number of services per year 629 213 158 Maintenance Depreciation Cleaning Number of services per year Hospitality support Number of guests per year 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 recommendations 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 balanced 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) Harbour Ltd makes kayaks. Materials are added at the beginning of the process and conversion costs are incurred 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. Harbour 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. b) 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 transferred out) and to units in ending work in process. d) Prepare journal entries to record transferring 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 require 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. b) 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.
Answered 2 days AfterMay 08, 2021

Answer To: © TAFE NSW – Higher Education Semester 1, 2021 Page | 1 ACACT302A Advanced Management Accounting...

Sugandh answered on May 10 2021
149 Votes
Q3
                     
        Beginning WIP inventory (units)    200         
        Units started (units)    850    
        Units placed in finished goods (units)     900    
        Conversion costs    270000
        Cost
of direct materials    400000
        Beginning WIP costs: Materials    100000         
        Beginning WIP costs: Conversion     70000
        Particulars     In Units     Cost Per Units         In peso
        Beginning    200    850
        Material     200    500        100000
        Conversion     200    350        70000    170000
        In the Month     850    788.2
        Material                 400000
        Conversion                 270000    670000
        Inventory In process    1050    800            840000
        Ending Inventory     150    800            120000
        Inventory to be processed    900    800            720000
            Material    Conversion
    A)    Equivalent Units     900    900
    B)    Cost per equivalent unit    800    350
    c)    Computation done Above
    d)     Journal Entries
        WIP Account    840000
        To Materials          500000
        To Conversion Cost          340000
        Finished Goods     720000
        To WIP         720000
    e)     The best manner is to ensure that the inventory control technquies are managed and the methods like JIT and the other methods are managed correctly as well as ABC method is kept in place
Q4
        Work order M66 (Standard curtains)
            Cutting     $ 25,000.00
        +    Sewing     $ 46,000.00
        +    Lining     $ 68,000.00
        =    Total conversion costs    $ 139,000.00
        /    Units in the work order    2,000 
    A)    =    Budgeted conversion cost rate    $ 69.50    per unit
        Work order P61 (Decorative curtains)
            Cutting     $ 25,000.00
        +    Sewing     $ 46,000.00
        +    Lining     $ 68,000.00
        +    Trimming     $ 40,000.00
        +    Threadwork     $ 55,000.00
        =    Total conversion costs    $ 234,000.00
        /    Units in the work order    1,500 
    A)    =    Budgeted conversion cost rate    $ 156.00    per unit
    B)            Work order M66 (Standard...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here