Corgan, Inc., manufactures beds. Activity based costing

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:

Regularâ?"
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

Deluxeâ?"
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
Worked

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.

Required:

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.