Posted: February 26th, 2023
Please see attached
<h
>Tab
Lumpsum Analysis
14 | ||||||||||||||||||||||||||||||||||||||||||||||
In Project 3 you will analyse managerial and costing information to improve the company’s EBITDA. You will use what you have learned about cost behavior and apply activity-based costing and cost-volume-profit analysis to make recommendations about LGI’s operational productivity. Step 1: Use the information you calculated in Project 2 Tab 3 Profit Maximization to populate has Columns C to H in | Question 1 | 12 | ||||||||||||||||||||||||||||||||||||||||||||
Standard Boxes | Annual information ( for 12 Months) | |||||||||||||||||||||||||||||||||||||||||||||
Standard boxes sold per month (millions) | Price | Revenue | Variable Cost per Standard box | Variable Cost (cost per unit x volume) | Fixed cost per month (millions) | Total | Monthly Profit (revenue – all costs) | Annual Revenue (millions) | Annual VC (millions) | Annual FC (millions) | Annual Total Costs (millions) | Annual Profit | ||||||||||||||||||||||||||||||||||
5 | $ 22.00 | $ 1 | 10 | $ 10.00 | $ 50.00 | $ | 6 | |||||||||||||||||||||||||||||||||||||||
5.5 | $ 2 | 1.6 | $ | 11 | 8 | $ 55.00 | $ 65.00 | $ 53.80 | ||||||||||||||||||||||||||||||||||||||
$ 2 | 1.2 | $ 12 | 7 | $ 60.00 | $ 70.00 | $ 57.20 | ||||||||||||||||||||||||||||||||||||||||
6.5 | $ 20.80 | $ | 13 | $ 75.00 | $ 60.20 | |||||||||||||||||||||||||||||||||||||||||
$ 20.40 | $ 142.80 | $ 80.00 | $ 62.80 | |||||||||||||||||||||||||||||||||||||||||||
7.5 | $ 20.00 | $ 150.00 | $ 85.00 | |||||||||||||||||||||||||||||||||||||||||||
$ 1 | 9 | $ 156.80 | $ 90.00 | $ 66.80 | ||||||||||||||||||||||||||||||||||||||||||
8.5 | $ 19.20 | $ 163.20 | $ 95.00 | $ 68.20 | ||||||||||||||||||||||||||||||||||||||||||
$ | 18 | $ 169.20 | $ 100.00 | $ 69.20 | ||||||||||||||||||||||||||||||||||||||||||
9.5 | $ 18.40 | $ 174.80 | $ 105.00 | $ 69.80 | ||||||||||||||||||||||||||||||||||||||||||
$ | 18.00 | $ 180.00 | $ 110.00 | |||||||||||||||||||||||||||||||||||||||||||
10.5 | $ 17.60 | $ 184.80 | $ 115.00 | |||||||||||||||||||||||||||||||||||||||||||
$ 17.20 | $ 189.20 | $ 120.00 | ||||||||||||||||||||||||||||||||||||||||||||
1 | 1.5 | $ 16.80 | $ 193.20 | $ 125.00 | ||||||||||||||||||||||||||||||||||||||||||
$ 16.40 | $ 196.80 | $ 130.00 | ||||||||||||||||||||||||||||||||||||||||||||
12.5 | $ 16.00 | $ | 200 | $ 135.00 | ||||||||||||||||||||||||||||||||||||||||||
$ 15.60 | $ 202.80 | $ 140.00 | ||||||||||||||||||||||||||||||||||||||||||||
13.5 | $ 15.20 | $ 205.20 | $ 145.00 | |||||||||||||||||||||||||||||||||||||||||||
$ 14.80 | $ 207.20 | |||||||||||||||||||||||||||||||||||||||||||||
Deluxe Boxes | ||||||||||||||||||||||||||||||||||||||||||||||
Deluxe boxes sold per month (millions) | Variable Cost per Deluxe box | Annual Profit (millions) | ||||||||||||||||||||||||||||||||||||||||||||
$ 30.00 | $ 3.00 | $ | 23 | $ 7.00 | ||||||||||||||||||||||||||||||||||||||||||
$ 29.50 | $ 35.40 | |||||||||||||||||||||||||||||||||||||||||||||
1.35 | $ 29.00 | $ 39.15 | ||||||||||||||||||||||||||||||||||||||||||||
$ 28.50 | $ 42.75 | |||||||||||||||||||||||||||||||||||||||||||||
1.55 | $ 28.00 | $ 43.40 | ||||||||||||||||||||||||||||||||||||||||||||
$ 27.50 | $ 44.00 | |||||||||||||||||||||||||||||||||||||||||||||
1.65 | $ 27.00 | $ 44.55 | ||||||||||||||||||||||||||||||||||||||||||||
1.7 | $ 26.50 | $ 45.05 | ||||||||||||||||||||||||||||||||||||||||||||
1.75 | $ 26.00 | $ 45.50 | ||||||||||||||||||||||||||||||||||||||||||||
1.8 | $ 25.50 | $ 45.90 | ||||||||||||||||||||||||||||||||||||||||||||
1.85 | $ 25.00 | $ 46.25 | ||||||||||||||||||||||||||||||||||||||||||||
1.9 | $ 24.50 | $ 46.55 | ||||||||||||||||||||||||||||||||||||||||||||
1.95 | $ 24.00 | $ 46.80 | ||||||||||||||||||||||||||||||||||||||||||||
$ 23.50 | $ 47.00 | |||||||||||||||||||||||||||||||||||||||||||||
2.05 | $ 47.15 | |||||||||||||||||||||||||||||||||||||||||||||
2.1 | $ 22.50 | $ 47.25 | ||||||||||||||||||||||||||||||||||||||||||||
2.15 | $ 47.30 | |||||||||||||||||||||||||||||||||||||||||||||
2.2 | $ 21.50 | |||||||||||||||||||||||||||||||||||||||||||||
2.25 | $ 21.00 | |||||||||||||||||||||||||||||||||||||||||||||
Question 2 | ||||||||||||||||||||||||||||||||||||||||||||||
The Company currently operates by selling 9 Million | Standard Boxes | Question 1 | ||||||||||||||||||||||||||||||||||||||||||||
Number of Boxes per month (in Millions) | ||||||||||||||||||||||||||||||||||||||||||||||
Number of Boxes per year (millions) | 108 | 126 | ||||||||||||||||||||||||||||||||||||||||||||
$ (in millions) | ||||||||||||||||||||||||||||||||||||||||||||||
Subtract: | Variable Costs | |||||||||||||||||||||||||||||||||||||||||||||
Equals: Contribution | ||||||||||||||||||||||||||||||||||||||||||||||
Subtract: Fixed Costs | ||||||||||||||||||||||||||||||||||||||||||||||
Equals: Operating Profit | ||||||||||||||||||||||||||||||||||||||||||||||
Operating Profit | % |
A new intern thinks that the profit for Deluxe Boxes are higher than those calculated using the lump sum method (as in Tab1). The intern suggests calculating the profits using an allocation method for fixed costs based on sales volume( the number of boxes sold) to split the Fixed Costs between the Standard and Deluxe Boxes. Required: (Complete the grey spaces): 1) First calculate the percenatge portion each product has of the total sales voume 1) How much fixed costs are allocated to each product based on the sales volume method suggested by the intern? 2) Also calculate the new operating profit percentage (based on sales) for each product. |
|||
Volumes (per Month) | |||
Volumes per year (Millions) | |||
Calculate the portion of Sales Volume (percentage sales volume) | |||
Calculate how much fixed costs are allocated to each product. | |||
New Profit Calculation | Standard Boxes ($Millions) | Deluxe Boxes ($Millions) | Total Boxes($ Millions) |
Revenue | |||
Subtract Variable Costs | |||
Equals: Contribution Margin | |||
Subtract Fixed Costs | |||
Operating Profit % |
LGI’s production managers think that the profit on Deluxe Boxes are much lower than the Intern suggested after recently attending a course at UMGC where they learned about ABC costing. They propose allocating the total fixed costs between Standard and Deluxe boxes based on the ABC method . They collected information about the cost drivers and the break up of the total fixed costs in | Table 1 | |||||||||
Manufacturing overhead | $ Amount (millions) | Cost driver | Standard Box | Deluxe Box | Totals of Drivers | Portion of Fixed Cost for | Standard Boxes | Portion of Fixed Cost of Deluxe Boxes | Total Cost Check (must agree to Column B7:B14) | |
Depreciation | $47.00 | Square feet | 7,000 | 80,000 | ||||||
Maintenance | $50.00 | Direct | Labour Hours | 1,000 | 9,000 | |||||
Purchase order processing | $9 | Number of purchases orders | 500 | 4,500 | ||||||
Inspection | $34 | Number of employees | 6000 | |||||||
Indirect Materials | $5.00 | |||||||||
Supervision | $7.00 | #of inspections | 800 | |||||||
Supplies | $4.00 | Units manufactured | ||||||||
Total Allocated costs | $156.00 | |||||||||
Number of boxes per year | ||||||||||
Allocated Cost per Box | ||||||||||
Subtract: Variable Costs | ||||||||||
Operating Profit % (based on Revenue) |
The sustainability manager is concerned about the long term sustainability implications of Deluxe Boxes on the environment and suggests changing to sustainable materials for the production of a Sustainable Deluxe Box. If the company switches the current quantity of Deluxe Boxes sold, to Sustainable Deluxe Boxes, there will be some cost implications. 1) The | Sustainable Deluxe Boxes | |
Quantity | 108.00 | 126.00 |
Selling price | $ 18.80 | |
Equals: | Operating Profit | |
Operating Profit % (based on revenue) | ||
The CEO is not convinced and still thinks that no form of a Deluxe Box, sustainable or not should be produced. The CEO indicates that consideration of the production of a Sustainable Deluxe Boxes will only be considered if it can achieve at least the same operating profit percentage for the Sustainable Deluxe Boxes as the operating profit percenatge indicated under the ABC costing method for Standard Boxes (See Tab 3) . Required (Complete the grey spaces). 1) How much additional operating profit (in percentage) will be required from the Sustainable Deluxe Boxes to meet the same percentage as the Standard Boxes are generating, given the percentage that can currently be achieved on Sustainable Deluxe Boxes |
||
Required profit | See Question 1 | |
Subtract: Existing profit | See Q 1 above | |
Equals: Difference in additional profit required | ||
Question 3 | ||
Required: Work out the percentage that the company should mark up on the costs of Sustainable Deluxe Boxes to achieve the same profit % as for the Standard boxes. (Complete the grey spaces) | ||
Revenue % | 100.00% | |
Subtract: Required Operating Profit | ||
Equals: Cost % | ||
Question 4 | ||
Assume the company can still sell the same quantity of the Sustainable Deluxe Boxes as for the Deluxe Boxes Required (Complete the grey spaces) Use the percentage calculated in Question 3 to determine at which price the company should sell the Sustainable Deluxe Boxes to reach the same profit percentage as for the Standard Boxes. |
||
Totals $ | ||
Plus : Fixed Costs | ||
Equals: Total Costs | ||
Determine Revenue | ||
Units sold (per year) | ||
Selling Price(Revenue) per unit | ||
Question 5 | ||
Required: Prove that your calculation in Q 4 is correct. Complete the grey boxes. | ||
Proof: | Total $ | |
Question 6 | ||
The marketing manger is concerned that the change could have a significant impact on sales as customers may see the sustainable boxes as an inferior product for which they still have to pay only a little bit less than the original price of the Deluxe Boxes. How many boxes would the company have to sell to break even on the new Sustainable Deluxe Boxes based on the new selling price? Complete the grey boxes. | ||
$ Per unit Sustainable Deluxe Boxes | ||
Subtract: Variable costs | ||
Equals: Unit Contribution Margin | ||
Fixed Costs (in total for Sustainable Deluxe Boxes) | ||
Breakeven Quantity | ||
Break-even Value |
[removed]
Place an order in 3 easy steps. Takes less than 5 mins.