14 March 2022
Project 3 Questions – Report Template
Instructions: Answer the five questions below. They focus entirely on improving the EBITDA of Largo Global Inc. (LGI) based on the information provided in the Excel workbook. Provide support for your reasoning from the readings in Project 3, Step 1, and the discussion in Project 3, Step 3. Be sure to cite your sources in APA 7th ed. style.
Provide a detailed response below each question. Use 12-point font and double spacing. Maintain the existing margins in this document. Your final Word document, including the questions, should not exceed 5 pages. Include a title page in addition to the five pages. Any tables and graphs you choose to include are excluded from the five-page limit. Name your document as follows: P3_Final_lastname_Report_date.
You must address all five questions and make full use of the information on all tabs.
You are strongly encouraged to exceed the requirements by refining your analysis. Consider other tools and techniques that were discussed in the required and recommended reading for Project 3. This means adding an in-depth explanation of what happened in the year for which data was provided to make precise recommendations to LGI.
Title Page
Name
Course and section number
Faculty name
Submission date
Questions:
1. How much of the fixed costs were allocated between the Standard and Deluxe Boxes based on the Lumpsum Allocation Method? Is the CEO correct that LGI is not contributing much to company profit? Please elaborate on your answer and include evidence from Tab 1 of the Excel workbook.
[insert your answer here]
2. The intern suggested splitting the costs, as you have done in the calculations performed in Tab 2, based on sales volumes. Explain the impact of calculation performed in Tab 2. In your discussions, please elaborate on why the answer has changed from the calculations you performed in Tab 1. Also indicate the benefit of accurate costing when trying to improve profit margins.
[insert your answer here]
3. Based on the calculations in Tab 3 using ABC, comment on the profits made for each product. Explain in your report why profits have changed under ABC costing method. Also indicate which of the systems – that is the traditional systems (using lumpsum or volume-based cost allocation in Tab 1 and Tab 2) or the ABC systems, (Tab 3) provide the best answers for decision making to improve cost management in order to improve EBITDA.
[insert your answer here]
4.The sustainability manger is concerned about the Anti-Deluxe Action group’s impact on the company and suggested that materials and process of making the Deluxe Boxes should be changed. As the process of making the Sustainable Deluxe Boxes, will be less intensive, a suggestion is made that the selling price for the Sustainable Deluxe Boxes could be $23 per unit. Discuss whether changing the price to $23 is a viable option for LGI? Provide evidence from the Excel workbook, Tab 4.
[insert your answer here]
5. If Largo Global Inc. decides to sell the Sustainable Deluxe Boxes at the price the CEO demands to maintain the same profit percentage as for Standard Boxes do you think the new price calculated in Tab 4 is a viable option? Why is it important for LGI to know what their Breakeven quantity is? Also indicate which other (non-numerical information) should be considered when deciding to pursue the Sustainable Deluxe Box option.
[insert your answer here]
Tab 1 Lumpsum Allocation
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.
Question 1
Standard Boxes Profit Maximization ( obtain Column C to H from Project 2) Annual information ( for 12 Months)
Number of Standard Boxes sold per month (in millions) Price Revenue VC /unit VC FC / per month (millions) Total Costs (FC+VC) 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 $ 1,320.00 $ 600.00 $ 120.00 $ 720.00 $ 600.00
5.5 $ 21.60 $ 118.80 $ 10.00 $ 55.00 $ 10.00 $ 65.00 $ 53.80 $ 1,425.60 $ 660.00 $ 120.00 $ 780.00 $ 645.60
6 $ 21.20 $ 127.20 $ 10.00 $ 60.00 $ 10.00 $ 70.00 $ 57.20 $ 1,526.40 $ 720.00 $ 120.00 $ 840.00 $ 686.40
6.5 $ 20.80 $ 135.20 $ 10.00 $ 65.00 $ 10.00 $ 75.00 $ 60.20 $ 1,622.40 $ 780.00 $ 120.00 $ 900.00 $ 722.40
7 $ 20.40 $ 142.80 $ 10.00 $ 70.00 $ 10.00 $ 80.00 $ 62.80 $ 1,713.60 $ 840.00 $ 120.00 $ 960.00 $ 753.60
7.5 $ 20.00 $ 150.00 $ 10.00 $ 75.00 $ 10.00 $ 85.00 $ 65.00 $ 1,800.00 $ 900.00 $ 120.00 $ 1,020.00 $ 780.00
8 $ 19.60 $ 156.80 $ 10.00 $ 80.00 $ 10.00 $ 90.00 $ 66.80 $ 1,881.60 $ 960.00 $ 120.00 $ 1,080.00 $ 801.60
8.5 $ 19.20 $ 163.20 $ 10.00 $ 85.00 $ 10.00 $ 95.00 $ 68.20 $ 1,958.40 $ 1,020.00 $ 120.00 $ 1,140.00 $ 818.40
9 $ 18.80 $ 169.20 $ 10.00 $ 90.00 $ 10.00 $ 100.00 $ 69.20 $ 2,030.40 $ 1,080.00 $ 120.00 $ 1,200.00 $ 830.40
9.5 $ 18.40 $ 174.80 $ 10.00 $ 95.00 $ 10.00 $ 105.00 $ 69.80 $ 2,097.60 $ 1,140.00 $ 120.00 $ 1,260.00 $ 837.60
10 $ 18.00 $ 180.00 $ 10.00 $ 100.00 $ 10.00 $ 110.00 $ 70.00 $ 2,160.00 $ 1,200.00 $ 120.00 $ 1,320.00 $ 840.00
10.5 $ 17.60 $ 184.80 $ 10.00 $ 105.00 $ 10.00 $ 115.00 $ 69.80 $ 2,217.60 $ 1,260.00 $ 120.00 $ 1,380.00 $ 837.60
11 $ 17.20 $ 189.20 $ 10.00 $ 110.00 $ 10.00 $ 120.00 $ 69.20 $ 2,270.40 $ 1,320.00 $ 120.00 $ 1,440.00 $ 830.40
11.5 $ 16.80 $ 193.20 $ 10.00 $ 115.00 $ 10.00 $ 125.00 $ 68.20 $ 2,318.40 $ 1,380.00 $ 120.00 $ 1,500.00 $ 818.40
12 $ 16.40 $ 196.80 $ 10.00 $ 120.00 $ 10.00 $ 130.00 $ 66.80 $ 2,361.60 $ 1,440.00 $ 120.00 $ 1,560.00 $ 801.60
12.5 $ 16.00 $ 200.00 $ 10.00 $ 125.00 $ 10.00 $ 135.00 $ 65.00 $ 2,400.00 $ 1,500.00 $ 120.00 $ 1,620.00 $ 780.00
13 $ 15.60 $ 202.80 $ 10.00 $ 130.00 $ 10.00 $ 140.00 $ 62.80 $ 2,433.60 $ 1,560.00 $ 120.00 $ 1,680.00 $ 753.60
13.5 $ 15.20 $ 205.20 $ 10.00 $ 135.00 $ 10.00 $ 145.00 $ 60.20 $ 2,462.40 $ 1,620.00 $ 120.00 $ 1,740.00 $ 722.40
14 $ 14.80 $ 207.20 $ 10.00 $ 140.00 $ 10.00 $ 150.00 $ 57.20 $ 2,486.40 $ 1,680.00 $ 120.00 $ 1,800.00 $ 686.40
Deluxe Boxes Profit Maximization ( Columns C to H obtain from Project 2) Annual information ( for 12 Months)
Number of Deluxe 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 (millions)
1 $ 30.00 $ 30.00 $ 20.00 $ 20.00 $ 3.00 $ 23.0000 $ 7.00 $ 360.00 $ 240.00 $ 36.00 $ 276.00 $ 84.00
1.2 $ 29.50 $ 35.40 $ 20.00 $ 24.00 $ 3.00000 $ 27.0000 $ 8.40 $ 424.80 $ 288.00 $ 36.00 $ 324.00 $ 100.80
1.35 $ 29.00 $ 39.15 $ 20.00 $ 27.00 $ 3.00000 $ 30.0000 $ 9.15 $ 469.80 $ 324.00 $ 36.00 $ 360.00 $ 109.80
1.5 $ 28.50 $ 42.75 $ 20.00 $ 30.00 $ 3.00000 $ 33.0000 $ 9.75 $ 513.00 $ 360.00 $ 36.00 $ 396.00 $ 117.00
1.55 $ 28.00 $ 43.40 $ 20.00 $ 31.00 $ 3.00000 $ 34.0000 $ 9.40 $ 520.80 $ 372.00 $ 36.00 $ 408.00 $ 112.80
1.6 $ 27.50 $ 44.00 $ 20.00 $ 32.00 $ 3.00000 $ 35.0000 $ 9.00 $ 528.00 $ 384.00 $ 36.00 $ 420.00 $ 108.00
1.65 $ 27.00 $ 44.55 $ 20.00 $ 33.00 $ 3.00000 $ 36.0000 $ 8.55 $ 534.60 $ 396.00 $ 36.00 $ 432.00 $ 102.60
1.7 $ 26.50 $ 45.05 $ 20.00 $ 34.00 $ 3.00000 $ 37.0000 $ 8.05 $ 540.60 $ 408.00 $ 36.00 $ 444.00 $ 96.60
1.75 $ 26.00 $ 45.50 $ 20.00 $ 35.00 $ 3.00000 $ 38.0000 $ 7.50 $ 546.00 $ 420.00 $ 36.00 $ 456.00 $ 90.00
1.8 $ 25.50 $ 45.90 $ 20.00 $ 36.00 $ 3.00000 $ 39.0000 $ 6.90 $ 550.80 $ 432.00 $ 36.00 $ 468.00 $ 82.80
1.85 $ 25.00 $ 46.25 $ 20.00 $ 37.00 $ 3.00000 $ 40.0000 $ 6.25 $ 555.00 $ 444.00 $ 36.00 $ 480.00 $ 75.00
1.9 $ 24.50 $ 46.55 $ 20.00 $ 38.00 $ 3.00000 $ 41.0000 $ 5.55 $ 558.60 $ 456.00 $ 36.00 $ 492.00 $ 66.60
1.95 $ 24.00 $ 46.80 $ 20.00 $ 39.00 $ 3.00000 $ 42.0000 $ 4.80 $ 561.60 $ 468.00 $ 36.00 $ 504.00 $ 57.60
2 $ 23.50 $ 47.00 $ 20.00 $ 40.00 $ 3.00000 $ 43.0000 $ 4.00 $ 564.00 $ 480.00 $ 36.00 $ 516.00 $ 48.00
2.05 $ 23.00 $ 47.15 $ 20.00 $ 41.00 $ 3.00000 $ 44.0000 $ 3.15 $ 565.80 $ 492.00 $ 36.00 $ 528.00 $ 37.80
2.1 $ 22.50 $ 47.25 $ 20.00 $ 42.00 $ 3.00000 $ 45.0000 $ 2.25 $ 567.00 $ 504.00 $ 36.00 $ 540.00 $ 27.00
2.15 $ 22.00 $ 47.30 $ 20.00 $ 43.00 $ 3.00000 $ 46.0000 $ 1.30 $ 567.60 $ 516.00 $ 36.00 $ 552.00 $ 15.60
2.2 $ 21.50 $ 47.30 $ 20.00 $ 44.00 $ 3.00000 $ 47.0000 $ 0.30 $ 567.60 $ 528.00 $ 36.00 $ 564.00 $ 3.60
2.25 $ 21.00 $ 47.25 $ 20.00 $ 45.00 $ 3.00000 $ 48.0000 -$ 0.75 $ 567.00 $ 540.00 $ 36.00 $ 576.00 -$ 9.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 profit each product makes?
2) Calculate the 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 $ 2,030.40 $ 513.00 $ 2,543.40
Subtract: Variable Costs $ 1,080.00 $ 360.00 $ 1,440.00
Equals: Contribution Margin $ 950.40 $ 153.00 $ 1,103.40
Subtract: Fixed Costs $ 120.00 $ 36.00 $ 156.00
Equals: Profit $ 830.40 $ 117.00 $ 947.40
Contribution Margin Ratio % 46.81% 29.82% 43.38%
Profit % (based on revenue) 40.90% 22.81% 37%
Tab 2 Volume Based Allocation
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) How much fixed costs are allocated to each product based on the sales volume method suggested by the intern?
2) Also calculate the new 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
Total Fixed Costs (Millions- from Tab1) 156.00
New Profit Calculation Standard Boxes ($Millions) Deluxe Boxes ($Millions) Total Boxes($ Millions)
Revenue $ 2,030.40 $ 513.00 $ 2,543.40
Subtract VC $ 1,080.00 $ 360.00 $ 1,440.00
Equals: Contribution Margin $ 950.40 $ 153.00 $ 1,103.40
Subtract Fixed Costs $ 133.71 $ 22.29 $ 156.00
Equals: Profit $ 816.69 $ 130.71 $ 947.40
Profit % (based on Revenue) 40.22% 25.48% 37.25%
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 this method . They collected information about the cost drivers and the break up of the total 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 87,000 $ 3.78 $ 43.22 $47.00
Maintenance $50.00 Direct Labour Hours 1,000 9,000 10,000 $ 5.00 $ 45.00 $50.00
Purchase order processing $9 Number of purchases orders 500 4,500 5,000 $ 0.90 $ 8.10 $9.00
Inspection $34 Number of employees 1,000 6000 7,000 $ 4.86 $ 29.14 $34.00
Indirect Materials $5.00 Labour Hours 1,000.00 9,000.00 10,000.00 $ 0.50 $ 4.50 $5.00
Supervision $7.00 #of inspections 200 800 1000 $ 1.40 $ 5.60 $7.00
Supplies $4.00 Units manufactured 1,000.00 9,000.00 10,000.00 $ 0.40 $ 3.60 $4.00
Total Allocated costs $156.00 $ 16.84 $ 139.16 $ 156.00
Number of boxes per year 108 18
Allocated Cost per Box $ 0.16 $ 7.73
Question 1
Standard Boxes Deluxe Boxes Total
Revenue $ 2,030.40 $ 513.00 $ 2,543.40
Subtract: Variable Costs $ 1,080.00 $ 360.00 $ 1,440.00
Equals: Contribution $ 950.40 $ 153.00 $ 1,103.40
Subtract: Fixed Costs $ 16.84 $ 139.16 $ 156.00
Equals: Profit $ 933.56 $ 13.84 $ 947.40
Profit % (based on Revenue) 45.98% 2.70% 37.25%
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 markeing manger believes that a lower selling price will also entice current Deluxe Box customers to accept the switch over.
2) The new Sustainable Deluxe Boxes will still attract 60% of 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 to LGI.
4) The Standard Box costs and revenue will remain the same as that calculated under the ABC method
5) The variable costs for the Sustainable Deluxe Bxes will reduce to $11.
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 $ 2,030.40 414.00 $ 2,444.40
Subtract: Variable Costs $ 1,080.00 198.00 1,278.00
Equals: Contribution Margin $ 950.40 216.00 $ 1,166.40
Subtract: Fixed Costs $ 16.84 $ 83.50 $ 100.34
Equals: Profit $ 933.56 132.50 $ 1,066.06
Profit % (based on revenue) 45.98% 32.01% 44%
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 profit percentage for the Sustainable Deluxe Boxes as the profit percenatge indicated under the ABC costing method for Standard Boxes (See Tab 3) .
Required (Complete the grey spaces).
1) How much additional profit (in percentage) will be required from the Sustainable Deluxe Boxes given the percentage that can currently be achieved on Sustainable Deluxe Boxes
%
Required profit 45.98% See Question 1
Subtract: Existing profit 32.01% See Q 1 above
Equals: Difference in additional profit required 13.97%
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 GP% 45.98%
Equals: Mark up percentage on cost 54.02%
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 $
Variable Costs $ 198.00
Fixed Cost $ 83.50
Total Costs $ 281.50
Revenue $ 521.09
Units sold (per year) 18.00
Selling Price(Revenue) per unit $ 28.95
Question 5
Required: Prove that your calculation in Q 4 is correct. Complete the grey boxes.
Proof: Total $
Revenue $ 521.09
Subtract: Variable Costs $ 198.00
Equals: Contribution Margin $ 323.09
Subtract: Fixed Costs $ 83.50
Profit $ 239.59
Profit % 45.98%
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 $ 28.95
Subtract: Variable costs $ 11.00
Equals: Unit Contribution Margin $ 17.95
Fixed Costs (in total for Sustainable Deluxe Boxes) $ 83.50
Breakeven Quantity 4.65
Break-even Value $ 134.67
Why Choose Us
- 100% non-plagiarized Papers
- 24/7 /365 Service Available
- Affordable Prices
- Any Paper, Urgency, and Subject
- Will complete your papers in 6 hours
- On-time Delivery
- Money-back and Privacy guarantees
- Unlimited Amendments upon request
- Satisfaction guarantee
How it Works
- Click on the “Place Order” tab at the top menu or “Order Now” icon at the bottom and a new page will appear with an order form to be filled.
- Fill in your paper’s requirements in the "PAPER DETAILS" section.
- Fill in your paper’s academic level, deadline, and the required number of pages from the drop-down menus.
- Click “CREATE ACCOUNT & SIGN IN” to enter your registration details and get an account with us for record-keeping and then, click on “PROCEED TO CHECKOUT” at the bottom of the page.
- From there, the payment sections will show, follow the guided payment process and your order will be available for our writing team to work on it.