ProjectUpdate.xlsx

Tab 1 Lumpsum Analysis

14-Mar-22
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. Step 2: Assume the company operates for 12 months of the year convert the information you populated in Columns C to H to annual information and populate Columns I to M for both the Standard and Deluxe Boxes. Step 3: Assume for this project that the only variable costs in this company are materials and labour. All other overhead costs will be assumed to be fixed.
Standard Boxes Profit Maximization ( obtain Column C to H from Project 2) Annual information ( for 12 Months)
Standard boxes sold per month (millions) Price Revenue (price x volume) Variable Cost per Standard box Variable Cost (cost per unit x volume) Fixed cost per month (millions) Total Cost (Fixed + Variable) Monthly Profit (revenue – all costs) Annual Revenue (millions) Annual VC (millions) Annual FC (millions) Annual Total Costs (millions) Annual Profit
5 $ 22.00 $ 110.00 $ 10.00 $ 50.00 $ 10.00 $ 60.00 $ 50.00
5.5 $ 21.60 $ 118.80 $ 10.00 $ 55.00 $ 10.00 $ 65.00 $ 53.80
6 $ 21.20 $ 127.20 $ 10.00 $ 60.00 $ 10.00 $ 70.00 $ 57.20
6.5 $ 20.80 $ 135.20 $ 10.00 $ 65.00 $ 10.00 $ 75.00 $ 60.20
7 $ 20.40 $ 142.80 $ 10.00 $ 70.00 $ 10.00 $ 80.00 $ 62.80
7.5 $ 20.00 $ 150.00 $ 10.00 $ 75.00 $ 10.00 $ 85.00 $ 65.00
8 $ 19.60 $ 156.80 $ 10.00 $ 80.00 $ 10.00 $ 90.00 $ 66.80
8.5 $ 19.20 $ 163.20 $ 10.00 $ 85.00 $ 10.00 $ 95.00 $ 68.20
9 $ 18.80 $ 169.20 $ 10.00 $ 90.00 $ 10.00 $ 100.00 $ 69.20
9.5 $ 18.40 $ 174.80 $ 10.00 $ 95.00 $ 10.00 $ 105.00 $ 69.80
10 $ 18.00 $ 180.00 $ 10.00 $ 100.00 $ 10.00 $ 110.00 $ 70.00
10.5 $ 17.60 $ 184.80 $ 10.00 $ 105.00 $ 10.00 $ 115.00 $ 69.80
11 $ 17.20 $ 189.20 $ 10.00 $ 110.00 $ 10.00 $ 120.00 $ 69.20
11.5 $ 16.80 $ 193.20 $ 10.00 $ 115.00 $ 10.00 $ 125.00 $ 68.20
12 $ 16.40 $ 196.80 $ 10.00 $ 120.00 $ 10.00 $ 130.00 $ 66.80
12.5 $ 16.00 $ 200.00 $ 10.00 $ 125.00 $ 10.00 $ 135.00 $ 65.00
13 $ 15.60 $ 202.80 $ 10.00 $ 130.00 $ 10.00 $ 140.00 $ 62.80
13.5 $ 15.20 $ 205.20 $ 10.00 $ 135.00 $ 10.00 $ 145.00 $ 60.20
14 $ 14.80 $ 207.20 $ 10.00 $ 140.00 $ 10.00 $ 150.00 $ 57.20
Deluxe Boxes Profit Maximization ( Columns C to H obtain from Project 2) Annual information ( for 12 Months)
Deluxe boxes sold per month (millions) Price Revenue (price x volume) Variable Cost per Deluxe box Variable Cost (cost per unit x volume) Fixed cost per month (millions) Total Cost (Fixed + Variable) Monthly Profit (revenue – all costs) Annual Revenue (millions) Annual VC (millions) Annual FC (millions) Annual Total Costs (millions) Annual Profit (millions)
1 $ 30.00 $ 30.00 $ 20.00 $ 20.00 $ 3.00 $ 23.00 $ 7.00
1.2 $ 29.50 $ 35.40 $ 20.00
1.35 $ 29.00 $ 39.15 $ 20.00
1.5 $ 28.50 $ 42.75 $ 20.00
1.55 $ 28.00 $ 43.40 $ 20.00
1.6 $ 27.50 $ 44.00 $ 20.00
1.65 $ 27.00 $ 44.55 $ 20.00
1.7 $ 26.50 $ 45.05 $ 20.00
1.75 $ 26.00 $ 45.50 $ 20.00
1.8 $ 25.50 $ 45.90 $ 20.00
1.85 $ 25.00 $ 46.25 $ 20.00
1.9 $ 24.50 $ 46.55 $ 20.00
1.95 $ 24.00 $ 46.80 $ 20.00
2 $ 23.50 $ 47.00 $ 20.00
2.05 $ 23.00 $ 47.15 $ 20.00
2.1 $ 22.50 $ 47.25 $ 20.00
2.15 $ 22.00 $ 47.30 $ 20.00
2.2 $ 21.50 $ 47.30 $ 20.00
2.25 $ 21.00 $ 47.25 $ 20.00
Question 2
The Company currently operates by selling 9 Million Standard Boxes and 1.5 Million Deluxe Boxes per month. The CEO is convinced that under the current cost allocation which allocates fixed costs on a lump sum method (arbitrarily using a monthly allocation basis) , Deluxe boxes is not contributing much to company profit and with recent threats from environmental groups thinks that LGI should consider to no longer produce Deluxe Boxes. Required (place answers in the in the Grey Spaces provided)1)Calculate how much operating profit each product makes? 2)Calculate the Operating Profit percentage (based on sales)for each product. HINT Use the annual information calculated in Question 1 to complete Question 2
Standard Boxes Deluxe Boxes Total
Number of Boxes per month (in Millions) 9 1.5 10.5
Number of Boxes per year (millions) 108 18 126
$ (in millions) $ (in millions) $ (in millions)
Revenue
Subtract: Variable Costs
Equals: Contribution Margin
Subtract: Fixed Costs
Equals: Operating Profit
Operating Profit % (based on revenue)

Tab 2 Sales Volume Analysis

Question 1
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.
Standard Boxes Deluxe Boxes Total
Volumes (per Month) 9 1.5 10.5
Volumes per year (Millions) 108 18 126
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
Equals: Operating Profit
Operating Profit % (based on Revenue)

Tab 3 ABC Costing

Question 1
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 below. How much overhead would be allocated to Standard and Deluxe Boxes ( in total and per unit) using this method? Show all supporting calculations. Complete the grey spaces
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 1,000 6000
Indirect Materials $5.00 Labour Hours 1,000 9,000
Supervision $7.00 #of inspections 200 800
Supplies $4.00 Units manufactured 1,000 9,000
Total Allocated costs $156.00
Number of boxes per year 108 18
Allocated Cost per Box
Question 1
Standard Boxes Deluxe Boxes Total
Revenue
Subtract: Variable Costs
Equals: Contribution
Subtract: Fixed Costs
Equals: Operating Profit
Operating Profit % (based on Revenue)

Tab 4 CVP

Question 1
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 could be made cheaper, and the sustainability manager believes that the company could sell the Sustaianable Deluxe Boxes for $23 per box and end up making substantially higher profit than they ever did on the Deluxe Boxes. Based on knowledge of price elasticity of demand s/he/they suggest that it may in time even result in much higher sales volumes. The marketing manager believes that a lower selling price will also entice current Deluxe Box customers to accept the switch over to the Sustainable Deluxe Box.2)The new Sustainable Deluxe Boxes will still attract 60% of the fixed costs allocated to the old Deluxe Box under the ABC method used in tab 3. 3)The number of boxes sold will not currently be affected by this new selling price, as this is a very select group of customers for LGI.4)The Standard Box costs and revenue will remain the same as that calculated under the ABC method5)Because of the cheaper materials the variable costs for the Sustainable Deluxe Boxes will be reduce to $11 per box vice $20 per box previously. Required (complete the grey spaces) 1)Determine the profit and profit percentage for the Standard and Sustainable Deluxe Boxes
Standard Boxes Sustainable Deluxe Boxes Total
Quantity 108.00 18.00 126.00
Selling price per unit $ 18.80 23
Revenue
Subtract: Variable Costs
Equals: Contribution Margin
Subtract: Fixed Costs
Equals: Operating Profit
Operating Profit % (based on revenue)
Question 2
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 BoxesRequired (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 $
Variable Costs
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 $
Revenue
Subtract: Variable Costs
Equals: Contribution Margin
Subtract: Fixed Costs
Operating Profit
Operating Profit %
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
Selling price
Subtract: Variable costs
Equals: Unit Contribution Margin
Total $
Fixed Costs (in total for Sustainable Deluxe Boxes)
Breakeven Quantity
Break-even Value
Calculate your order
Pages (275 words)
Standard price: $0.00
Client Reviews
4.9
Sitejabber
4.6
Trustpilot
4.8
Our Guarantees
100% Confidentiality
Information about customers is confidential and never disclosed to third parties.
Original Writing
We complete all papers from scratch. You can get a plagiarism report.
Timely Delivery
No missed deadlines – 97% of assignments are completed in time.
Money Back
If you're confident that a writer didn't follow your order details, ask for a refund.

Calculate the price of your order

You will get a personal manager and a discount.
We'll send you the first draft for approval by at
Total price:
$0.00
Power up Your Academic Success with the
Team of Professionals. We’ve Got Your Back.
Power up Your Study Success with Experts We’ve Got Your Back.
Open chat
1
Hello. Can we help you?