Please answer the excel and word doc ( they are related)

Project 3 Questions – Report TemplateInstructions: 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 Analysis Method? Is the CEO correct that the Deluxe Box is not contributing much to
company operating 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 operating profit margins.
[insert your answer here]
3. Based on the calculations in Tab 3 using ABC, comment on the operating profits made for each
product. Explain in your report why operating profits have changed under ABC analysis. 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 operating profit.
[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]
Revised on 6-15-2023
In Project 3 you will analyse managerial and costing information to improve the company’s EBITDA. You will use what you have
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
Step 2: Assume the company operates for 12 months of the year convert the information you populated in Columns C to H 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
Note: The Total Fixed Cost of 156 is supposed to be constant for the production of total boxes including Standard Box and Delux
fixed costs of 156 are allocated based on a lump sum method (arbitrarily using a monthly allocation basis). On Tab 2, the total fi
volume (the number of boxes sold). On Tab 3, the total fixed costs of 156 are allocated based on the cost drivers.
Question 1
Standard boxes sold per month
(millions)
5
5.5
6
6.5
7
7.5
8
8.5
9
9.5
10
10.5
11
11.5
12
12.5
13
13.5
14
Revenue (price x
volume)
Price
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
22.00
21.60
21.20
20.80
20.40
20.00
19.60
19.20
18.80
18.40
18.00
17.60
17.20
16.80
16.40
16.00
15.60
15.20
14.80
$
Variable Cost per
Standard box
110.00 $
10.00
Deluxe Boxes Profit Maximization ( Columns C to H obtain from Project 2)
Deluxe boxes sold per month
(millions)
1
1.2
1.35
1.5
1.55
1.6
1.65
1.7
1.75
1.8
Revenue (price x
volume)
Price
$
$
$
$
$
$
$
$
$
$
30.00
29.50
29.00
28.50
28.00
27.50
27.00
26.50
26.00
25.50
$
$
$
$
$
$
$
$
$
$
Variable Cost per
Deluxe box
30.00 $
35.40
39.15
42.75
43.40
44.00
44.55
45.05
45.50
45.90
20.00
1.85
1.9
1.95
2
2.05
2.1
2.15
2.2
2.25
$
$
$
$
$
$
$
$
$
25.00
24.50
24.00
23.50
23.00
22.50
22.00
21.50
21.00
$
$
$
$
$
$
$
$
$
46.25
46.55
46.80
47.00
47.15
47.25
47.30
47.30
47.25
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
basis) , Deluxe boxes is not contributing much to company profit and with recent threats from environmental groups thinks th
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
Number of Boxes per month (in
Millions)
Number of Boxes per year
(millions)
Revenue
Subtract: Variable Costs
Equals: Contribution Margin
Subtract: Fixed Costs
Equals: Operating Profit
Operating Profit % (based on
revenue)
Deluxe Boxes
Total
9
1.5
10.5
108
$ (in millions)
18
$ (in millions)
126
$ (in millions)
e company’s EBITDA. You will use what you have learned about cost behavior and apply activityLGI’s operational productivity.
on to populate has Columns C to H in Question 1.
nformation you populated in Columns C to H to annual information and populate Columns I to
materials and labour. All other overhead costs will be assumed to be fixed.
of total boxes including Standard Box and Deluxe Box on Tabs 1, 2, and 3. On Tab 1, the total
a monthly allocation basis). On Tab 2, the total fixed costs of 156 are allocated based on the sales
ocated based on the cost drivers.
Annual information (
Variable Cost
Total Cost
Fixed cost per
Monthly Profit (revenue Annual Revenue
(cost per unit x
(Fixed +
month (millions)
all costs)
(millions)
volume)
Variable)
$
50.00 $
10.00 $
60.00 $
50.00
Annual information (
Variable Cost
Total Cost
Fixed cost per
Monthly Profit (revenue Annual Revenue
(cost per unit x
(Fixed +
month (millions)
all costs)
(millions)
volume)
Variable)
$
20.00 $
3.00 $
23.00 $
7.00
lion Deluxe Boxes per month.
d costs on a lump sum method (arbitrarily using a monthly allocation
nt threats from environmental groups thinks that LGI should
on 2
Annual information ( for 12 Months)
Annual VC
(millions)
Annual FC
(millions)
Annual Total Costs
(millions)
Annual Profit
Annual information ( for 12 Months)
Annual VC
(millions)
Annual FC
(millions)
Annual Total Costs Annual Profit
(millions)
(millions)
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 profits using an allocation method for fixed costs based on sales volume( the number of boxes sold) to split the Fixed Cos
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
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
Revenue
Subtract Variable Costs
Equals: Contribution Margin
Subtract Fixed Costs
Equals: Operating Profit
Operating Profit % (based on Revenue)
9
108
Standard Boxes
($Millions)
calculated using the lump sum method (as in Tab1). The intern suggests calculating
me( the number of boxes sold) to split the Fixed Costs between the Standard and
voume
olume method suggested by the intern?
each product.
Deluxe Boxes
Total
1.5
18
Deluxe Boxes
($Millions)
10.5
126
Total Boxes($
Millions)
Question 1
LGI’s production managers think that the profit on Deluxe Boxes are much lower than the Intern suggested after recently atten
ABC costing. They propose allocating the total fixed costs between Standard and Deluxe boxes based on the ABC method . They
break up of the total fixed costs in Table 1 below. How much overhead would be allocated to Standard and Deluxe Boxes ( in to
supporting calculations. Complete the grey spaces
Table 1
Manufacturing overhead
$ Amount (millions)
Cost driver
Depreciation
$47.00
Square feet
7,000
Maintenance
$50.00
Direct Labour Hours
1,000
Purchase order processing
$9
Number of purchases
orders
500
Inspection
$34
Number of employees
1,000
Indirect Materials
Supervision
$5.00
$7.00
Labour Hours
#of inspections
1,000
200
Supplies
$4.00
Units manufactured
1,000
Total Allocated costs
$156.00
Number of boxes per year
Standard Box
108
Allocated Cost per Box
Question 1
Standard Boxes
Revenue
Subtract: Variable Costs
Equals: Contribution
Subtract: Fixed Costs
Equals: Operating Profit
Operating Profit % (based
on Revenue)
Deluxe Boxes
Total
than the Intern suggested after recently attending a course at UMGC where they learned about
Deluxe boxes based on the ABC method . They collected information about the cost drivers and the
allocated to Standard and Deluxe Boxes ( in total and per unit) using this method? Show all
Deluxe Box
80,000
9,000
4,500
6000
9,000
800
9,000
18
Totals of Drivers
Cost for Standard
Boxes
Total Cost Check (must
Cost of Deluxe Boxes
agree to Column
B7:B14)
Question 1
The sustainability manager is concerned about the long term sustainability implications of Deluxe Boxes on the environment an
materials for the production of a Sustainable Deluxe Box. If the company switches the current quantity of Deluxe Boxes sold,
be some cost implications.
1)The Sustainable Deluxe Boxes could be made cheaper, and the sustainability manager believes that the company could sell the
per box and end up making substantially higher profit than they ever did on the Deluxe Boxes. Based on knowledge of price el
that it may in time even result in much higher sales volumes. The marketing manager believes that a lower selling price will al
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 metho
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
4)The Standard Box costs and revenue will remain the same as that calculated under the ABC method
5)In order to help overall profit, the variable costs per sustainable Deluxe box will be reduced to $11 per box vice the original $2
Required (complete the grey spaces)
1)Determine the profit and profit percentage for the Standard and Sustainable Deluxe Boxes
Quantity
Selling price per unit
Revenue
Subtract: Variable Costs
Equals: Contribution Margin
Subtract: Fixed Costs
Equals: Operating Profit
Operating Profit % (based on revenue)
Standard Boxes
108.00
$
18.80
Sustainable Deluxe
Boxes
18.00
23
Total
126.00
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 indicat
production of a Sustainable Deluxe Boxes will only be considered if it can achieve at least the same operating profit percentage
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 p
Boxes are generating, given the percentage that can currently be achieved on Sustainable Deluxe Boxes
%
Required profit
Subtract: Existing profit
Equals: Difference in additional profit
required
See Question 1
See Q 1 above
Question 3
Required: Work out the percentage that the company should mark up on the costs of Sustainable Deluxe Boxes to achieve 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
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
cations of Deluxe Boxes on the environment and suggests changing to sustainable
hes the current quantity of Deluxe Boxes sold, to Sustainable Deluxe Boxes, there will
manager believes that the company could sell the Sustaianable Deluxe Boxes for $23
e Deluxe Boxes. Based on knowledge of price elasticity of demand s/he/they suggest
anager believes that a lower selling price will also entice current Deluxe Box customers
ated to the old Deluxe Box under the ABC method used in tab 3.
ce, as this is a very select group of customers for LGI.
nder the ABC method
ll be reduced to $11 per box vice the original $20 per box.
eluxe Boxes
ble or not should be produced. The CEO indicates that consideration of the
ve at least the same operating profit percentage for the Sustainable Deluxe
od for Standard Boxes (See Tab 3) .
e Sustainable Deluxe Boxes to meet the same percentage as the Standard
stainable Deluxe Boxes
sts of Sustainable Deluxe Boxes to achieve the same profit % as for the
Boxes as for the Deluxe Boxes
mpany should sell the Sustainable Deluxe Boxes to reach the same profit
pact on sales as customers may see the
ttle bit less than the original price of the
on the new Sustainable Deluxe Boxes