Corgan, Inc., manufactures two types of beds, Standard and Luxury. The Standard range is a mass market range sold through large furniture retailers, whereas the Luxury range is higher quality, sold through small furniture shops. The company currently applies overhead on the basis of machine hours worked. It is considering moving to an ABC costing system. Anticipated overheads for the upcoming accounting period are £1,620,000. Information about the company's products follows:
Estimated production volume : 20,000 units
Direct materials cost : £80 per unit
Direct labour per unit : 2 hours at £15 per hour
Current Selling Price : £200
Estimated production volume : 4,000 units
Direct materials cost : £160 per unit
Direct labour per unit : 6 hours at £15 per hour
Current Selling Price : £450
Corgan's overheads of £1,620,000 can be identified with three major activities: administration, marketing and management costs (£480,000), manufacturing overheads (£920,000), and quality control (£220,000). These activities are driven by number of orders processed, machine hours worked, and inspection hours, respectively. Data relevant to these activities follow.
Orders Machine Inspection
Processed Hours Hours
Regular 200 40,000 3,000
Deluxe 400 24,000 8,000
Total 600 64,000 11,000
The operations manager of Corgan Inc. would like to have a spreadsheet that shows the application rates that would be used per activity if ABC were to be adopted, as well as the full unit manufacturing costs on a per product basis, if the expected manufacturing volume is attained, under both ABC and traditional costing (applied overhead solely on the basis of machine hours worked).
The results must be shown both graphically and as tables of figures.
1. Your task is to design and produce this spreadsheet. The spreadsheet should be designed in a way that would allow an inexperienced user to alter any variable and for the results and charts to update automatically. This will mean that you will have to be sure that the formulas in your spreadsheet are designed such that it updates all relevant information each time a variable is changed.
2. What do your results suggest to you concerning the current selling prices of the beds? Comment on your results and suggest why changes to the selling price may be required.
See excel for spreadsheet and table.
The cost per unit using ABC shows that regular actually is costing less per unit than if you only use machine hours to allocate overhead. This means that you may be able to lower the price on regular and still make a handsome margin. For deluxe, however, the data shows that you may need ...
I created a "live model" which means that all computations are based on "pointing" to the raw data given. If you change any of the yellow cells, the table will update with the new amounts. A paragraph discusses what pricing strategies may need to change and why. A bar chart/graph of cost per unit by product line and by allocation method is shown in Excel for you.