I attached the Excel workbook to this questions. Please elaborate and detail all your answers

1. How much of the costs were allocated between the standard and deluxe models of the product? Is the marketing manager correct that the company is making significant margins on the Deluxe Loot Box? Please elaborate on your answer and include evidence from Tab 1 of the Excel workbook.

2. The IT manager indicated that splitting the costs, as you have done in the calculations performed in Tab 2, does nothing to improve the bottom line. She said: "The amounts are relatively small and as it all comes from the same pot in the end, there is no need to split the costs, it just creates additional coding categories for the system." Explain why the calculation performed in Tab 2 is important. In your discussions, also indicate the benefit of accurate costing when trying to improve profit margins.

3. Based on the calculations in Tab 3 using ABC, comment on the profits made for each product type. Explain in your report why this has changed under ABC costing. Also indicate which one of the two systems; that is, historical cost or ABC, provides the best answers for decision making to improve cost management to improve EBITDA.

4. The marketing manager suggested that to improve EBITDA and increase sales volume, the Deluxe Loot Box should be sold at the same margin as the Standard Loot Box. Base your answers on the ABC calculations performed in Tab 3 to indicate how much the company will need to charge for the Deluxe Loot Box. Indicate how many Deluxe Loot Boxes the company will have to sell at the new price to break even. Discuss whether changing the price is a viable option for Largo. Provide evidence from the Excel workbook, Tab 4.

5. If the company is unable to sell the Deluxe Loot Box for more than $27.86, discuss possible alternatives. Doing the same thing the company is already doing is not an option; you must suggest how to improve EBITDA.

Discuss the facts that should be considered in determining whether manufacturing the Loot Boxes in-house could lead to a price reduction. No calculations are required for Question 5

Workbook Q Search Sheet Home Insert Page Layout Formulas Data Review View '+ Share ~ Cut Calibri (Body) * 11 A- A = Wrap Text General _ AutoSum Copy AP . Fill Paste Format B I UVV . A Merge & Center $ % > Conditional Format Cell Insert Delete Format Sort & Formatting as Table Styles Clear x Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. Check for Updates F10 X V fx D F G H K M N 0 Largo Global Production and Cost Information 2020 Product Type Standard Loot Box Deluxe Loot Box Volume (Number of Products 80 20 Selling Price Per Unit 18.20 $ 27.85 Total Sales 1,456.00 $ 557.00 Cost of Sales 1120 280 Selling and Admin Charges 62.5 62.5 Depreciation 87 87 10 Purchases 11 Gross Profit 186.50 $ 127.50 1 Determine Purchases 2020 14 Cost of Sale 1400 Plus: Closing Inventory 404 Less: Opening Inventory 398 19 Purchases 406 20 2 Use traditional Allocation Method to Allocate Cost 24 Allocated Cost Total Costs Standard Loot Box Deluxe Loot Box Split 25 Purchases 1,406.00 $ 1,124.80 $ 281.20 Based on volumes sold 26 Selling and General Admin 125.00 $ 62.50 $ 62.50 Half half Depreciation 174.00 5 87.00 $ 87.00 Half half 28 Total Cost of Products 1,705.00 $ 1,274.30 $ 430.70 29 Number of Products 100 80 20 30 Cost per Product 17.05 $ 15.93 $ 21.54 Total Cost of Prouducts / Number of Products 31 32 33 34 3 Determine the Profit Margins per product 35 36 Standard Loot Box Deluxe Loot Box Total 37 Sales 1,456.00 $ 557.00 $ 2,013.00 38 Less: Cost of Products ,274.30 $ 430.70 $ ,705.00 39 Profit 181.70 $ 126.30 $ 308.00 40 % Profit (Profit/ Sales ) 2.48% 22.68% 15.30% 41 42 Instructions Tab 1 Tab 2 Tab 3 Tab 4 + Ready + 100%Workbook Q Search Sheet Home Insert Page Layout Formulas Data Review View '+ Share ~ Cut Calibri (Body) * 11 A- A = Wrap Text General _ AutoSum Copy AP . Fill Paste Format B I U V YA Merge & Center $ ~ % " 100 .0 Conditional Format Cell Insert Delete Format Sort & Formatting as Table Styles Clear x Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. Check for Updates D24 X V J C D G H M N Q R T U V Additional Information: You learn that 30% of freight charges are included in the purchases figure and should be split equally among the two products. 40% of the selling and administrative costs are due to administration. Perform a new calculation based on this new Information. 1 Allocated the costs based on this new information U A W N Allocated Cost Standard Loot Box Deluxe Loot Box Total Purchases 787.36 $ 196.84 $ 984.20 Freight charges 210.90 $ 10.90 $ 421.80 Selling Costs 25.00 $ 25.00 $ 50.00 General Admin Costs 37.50 $ 37.50 |$ 75.00 Depreciation 87.00 $ 87.00 5 174.00 Total Costs of Products 1,147.76 5 557.24 5 1,705.00 Number of Products 80 20 100 Cost per Produc 14.35 $ 27.86 17.05 2 Determine the Profit Margins per product based on the allocations above Standard Loot Box luxe Loot Box 18 Sales 1,456.00 $ 557.00 19 Less: Cost of Products 1,147.76 $ 557.24 20 Profit 308.24 5 (0.24) % Profit (Profit/ Sales ) 21.17% -0.04% 22 23 24 25 26 27 28 29 30 33 34 37 38 39 40 Instructions Tab 1 Tab 2 Tab 3 Tab 4 + Ready + 100%Workbook Q Search Sheet Home Insert Page Layout Formulas Data Review View '+ Share ~ Cut Calibri (Body) * 11 A- A = Wrap Text General _ AutoSum Copy AP . Fill Paste Format B I UV YA. Merge & Center $ % > .00 Conditional Format Cell Insert Delete Format Sort & Formatting as Table Styles Clear x Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. Check for Updates D21 X V fx C D F G H K M N P Q 1. Use the information from Tab 2 Part 1 under the total colum to fill in the figures under under the Amount Colum ( Column B) UI DWNH Manufacturing Amount Cost driver Standard Loot Box Deluxe Loot Box Sum of the Cost Cost of Standard Loot Cost of Deluxe Loot Check overhead Drivers Box Box Purchase $984.20 Number of units purchase 80 20 100 787.36 S 196.84 TRUE Freight charges $421.80 Kilometers travelled 1,000 4,000 5,000 84.36 $ $37.44 TRUE Selling Costs $50 Number of sales orders 10 20.00 $ 30.00 TRUE B Admin instrative $75 Number of employees 30.00 $ 15.00 TRUE Costs 10 Depreciation $174.00 Square Feet 2000 1000 3000 116.00 | $ 58.00 TRUE Total Allocated $1,70 1,037.72 667.28 TRUE 11 costs Allocated cost per 17.05 Number of Units sold 80 20 100 12.97 33.36 12 unit 13 14 2. Use ABC costing determined in 1 above to calculate the profit and profit margins of the two products 15 16 Standard Loot Box Deluxe Loot Box Total 17 Sales 1,456.00 $ 557.00 $ 2,013.00 18 Less: Costs 1,037.72 $ 667.28 $ 1,705.00 19 Net (profit /loss) 418.28 110.28 $ 308.00 20 Profit margin 29% -20% 15% 21 23 24 25 26 27 28 29 30 31 32 33 Instructions Tab 1 Tab 2 Tab 3 Tab 4 + Ready + 100%Workbook Q Search Sheet Home Insert Page Layout Formulas Data Review View '+ Share ~ Cut Calibri (Body) * 11 A- A = Wrap Text Accounting _ AutoSum Copy AP . Fill Paste Format B I U V YA Merge & Center $ ~ % " .00 Conditional Format Cell Insert Delete Format Sort & Formatting as Table Styles Clear x Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. Check for Updates C23 X V fx =C22/C21 C D G H K M N O P Q R S T U V w 1 Assume the Deluxe Product can be sold at the same profit margin as the Standard Product ( See Tab 3) . Calculate the New Selling Price for the Deluxe Product? Allocated CPU 33.36 Profit margin as % 29% New Selling Price TS 46.99 2 Provide a Proof For your Claculation: Total $ Total s 939.83 Less Costs 567.28 14 Profit ts 272.55 15 16 Calculate the New Breakeven Point in Volume based on the new Selling Price you determined above. You can 17 3 assume that only the Purcanses costs are Variable, all other costs are fixed. 18 19 Sales Price / unit 46.99 20 Variable Costs /unit 9.84 21 Marginal Cost / unit 23.52 22 Fixed Costs 470.44 23 Break Even 20.00 $0 = selling price x QBEP - variable cost x QBEP - total fixed cost 24 QBEP = Total Fixed Cost / (Selling Price - Variable Cost) 26 27 28 29 30 31 32 34 35 36 37 38 39 40 41 Instructions Tab 1 Tab 2 Tab 3 Tab 4 + Ready + 100%