SOLVE THE QUESTION USING EXCEL SHOW THE RELEVANT FORMULAS USED AND SHOW USE OF SOLVER PLEASE

Vinay Plc produces three grades of their products at the same factory: the Standard; Deluxe and Super. The three grades are sold worldwide.

Up to now, the company has absorbed production overheads to the three products on the basis of direct labour hours, but the introduction of activity based costing is being seriously considered. The management accountant has produced the following analysis.

Product

Standard Deluxe Super

Annual output (units)

4,000 3,200 800

Annual Direct Selling price per Labour hours unit

() 400,000 8,000 440,000 7,000

160,000 16,000

Direct Material cost per unit () 800 1,200 1,800

The annual overhead costs to be absorbed to the three products are as follows:

Delivery costs to customers Set-up costs Purchase order costs

4,800,000 12,000,000 7,200,000

Overhead costs are driven by the following: Deliveries to customers The number of deliveries of products to customers warehouse. Set-ups - The number of times the assembly line process is re-set to

accommodate a production run of each of the three products. Purchase orders - The number of purchase orders.

The annual cost driver volumes relating to each activity and for each product are as follows:

Product

Standard Deluxe Super

Number of customer deliveries 200 160 140

Number of set-ups

70 80 50

Number of Purchase Orders 800 600 200

All direct workers are paid at a hourly rate of 12. The company holds no stock of direct materials, work in-progress or finished goods.

BUS239 (2021) Page 3 Required:

1. a) Calculate the total profit from each of the three products using the following overhead absorption methods:

   1. (i) The existing direct labour hours method. (10 Marks)

   2. (ii) Activity Based Costing method. (20 Marks)

2. b) Outline the advantages and disadvantages of the two methods of costing used in Part a). Which method do you recommend and why? (20 marks)