Management Accounting: Overhead Allocation for Energetic Ltd
Not what you're looking for?
i need to produce a spreadsheet. The spreadsheet should be designed in a way that would allow an inexperienced user to alter any variable and for the product costs and pie charts to update automatically.
i also need help starting a report to senior management that should:
(i) explain clearly your proposed overhead allocation approach and why you believe it to be more effective than the previous one
(ii) comment on the key findings from the new overhead allocation system
(iii) discuss any limitations that you think the new approach still has.
Introduction
Task 1 is designed to test your knowledge and understanding of overhead cost analysis and also to improve your spreadsheet design skills. The task requires you to produce a spreadsheet that shows an overhead analysis and total costs of two products showing the results both graphically and as tables of figures.
Task 1
Energetic Ltd is a company that builds bespoke bicycles for racing. It produces two models the Econo-bike and the Luxi-bike. Annual production is expected to be 2000 units of the Econo-bike and 1000 units of the Luxi-bike. Both models are based on carbon fibre frames and Energetic Ltd buys these from the manufacturer for €800 each. The other direct costs and requirements of the building for each bike are shown in the table below.
Cost Econo-bike (€) Luxi-bike(€)
Forks 120 180
Wheels 80 110
Components 75 190
Saddle and handlebars 75 110
Workshop labour hours 7 per bike 12 per bike
Finishing department labour hours 12 per bike 20 per bike
Workshop machine hours 12 per bike 14 per bike
Finishing department machine hours 4 per bike 6 per bike
Average wage rate (Workshop) €13.00 per hour
Average wage rate (Finishing department) €18.00 per hour
In addition to the workshop and finishing department Energetic Ltd operate a quality control department and a parts warehouse. Total wages in the quality control department are €200,000 per year and in the warehouse €160,000 per year.
Total manufacturing overhead costs for the company are as shown in the table below:
Overhead Total annual cost
Premises costs €350,000
Light and power €160,000
Training €40,000
Plant depreciation €100,000
In addition, the company has provided the following information:
Workshop Finishing Dept Quality Dept Warehouse
Floor area (square meters) 800 1000 120 500
Number of staff 10 20 6 4
Value of Plant €250,000 €120,000 0 €60,000
Quality control visits per year 250 380
Deliveries from warehouse per year 690 890
The company uses absorption costing to allocate overheads to product costs. Currently, the company is using a simple blanket absorption rate across the company based on the number of units produced. However, Energetic Ltd has experienced a drastic increase in overheads over the last few years combined with increased competition in the industry. Therefore, senior management has decided to monitor their production overheads more closely. They also intend to include the reduction of overheads as part of the departmental performance targets to encourage departments to work more efficiently. However, they have been informed by their accountant that the existing overhead allocation system is too crude to allow a detailed overhead analysis.
You have been asked by one of the senior managers to prepare a report to management proposing a new overhead allocation system. They do not want to give up absorption costing at this stage but hope that you might come up with a more accurate way of allocating the overheads to products. As part of this task they would like you to create a spreadsheet that will work out the full product costs while showing how the overheads are allocated to the departments. They intend to use this spreadsheet for monitoring the overheads over the next few years. Therefore it is essential that the spreadsheet is designed in such a way that it can be easily updated every year. They also want to be able to carry out "what-if" analysis on the figures (this means they need a spreadsheet in which they can alter any variable and see the effect this has on overall costs. They also want the spreadsheet to include pie charts showing the overall cost make up of each of the two products.
Purchase this Solution
Solution Summary
Your tutorial is 790 words and includes a three page Excel spreadsheet that automatically updates when the yellow cells are changed. The pie charts also update with any changes. The pie charts show the products before and after implementing activity-based costing. The direct method of allocating overhead to the departments was used and explained.
Solution Preview
See excel spreadsheet report and chart (attached). These update anytime you change the yellow input cells so you can play with it and do the "what-if" analysis as much as you like. It can be updated an infinite number of times. Click in cells to see calculations and study how this was done.
Report to Management
Introduction
Until now, we allocated total overhead evenly across the units, resulting in a cost per unit of $705.67 for econo-bikes and $958.67 for luxi-bikes. This assumed that overhead consumed per bike was identical. That is, each bike, regardless of features and construction, was assumed to use the same overhead resources, the same machine utilities, the same warehousing effort, the same quality control costs and so forth. I have created a new way to allocate costs that better reflect the workshop and finishing departments actual use of overhead resources and then allocated the costs to the bikes based on their use of the departments.
Proposed Overhead Allocation
The first task was to assign costs to the workshop and finishing departments. These two departments use building space, utilities, training costs, quality control time and delivery time in their work on the bikes. So, these costs were spread to the two departments based on their use of warehouse space, headcount, value of plant, QC visits and the number of warehouse deliveries (see Excel for schedule showing this process).
I could have also allocated building, training and depreciation to the quality control and warehouse ...
Education
- BSc, University of Virginia
- MSc, University of Virginia
- PhD, Georgia State University
Recent Feedback
- "hey just wanted to know if you used 0% for the risk free rate and if you didn't if you could adjust it please and thank you "
- "Thank, this is more clear to me now."
- "Awesome job! "
- "ty"
- "Great Analysis, thank you so much"
Purchase this Solution
Free BrainMass Quizzes
Change and Resistance within Organizations
This quiz intended to help students understand change and resistance in organizations
Marketing Management Philosophies Quiz
A test on how well a student understands the basic assumptions of marketers on buyers that will form a basis of their marketing strategies.
Managing the Older Worker
This quiz will let you know some of the basics of dealing with older workers. This is increasingly important for managers and human resource workers as many countries are facing an increase in older people in the workforce
Six Sigma for Process Improvement
A high level understanding of Six Sigma and what it is all about. This just gives you a glimpse of Six Sigma which entails more in-depth knowledge of processes and techniques.
Lean your Process
This quiz will help you understand the basic concepts of Lean.