- Calculate and evaluate costs using the worksheets.
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.
Instructions Project 3, Step 4: Instructions for This Excel Workbook In this Project 3 workbook, you will analyze managerial and cost information to help improve the EBITDA of LGI. 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. Use the following information to complete this workbook: LGI sells Loot boxes for gaming companies. The Loot boxes come in two forms, standard and deluxe, and both are sourced from external suppliers. The standard product is prepacked Loot box; LGI puts its label on it before selling it. The deluxe product is more upscale; it is customized based on the customer's specifications. The deluxe Loot box is emblazoned with the LGI label, but the boxes are sourced from much farther afield. Most LGI sales come from the standard Loot box, but the marketing director believes that customization is the product of the future. He is impressed with the large margins they earn on the Deluxe Loot Boxes. Open Tab 1. Tab 1: Determine the profit margins for each type of Loot box. First, determine the costs using the Income Statement from Project 1. Tab 1: Use Tab 1 in this worksheet to view the sales quantity (volume) and selling price for each product. LGI allocates product costs on a historical cost method. Purchases are split based on volume sold between the two types of Loot Boxes. All other overhead costs (selling, administration and depreciation costs) are split equally between the two products. Tab 2: Use the additional information provided in Tab 2 and calculate a more accurate split among the two types of Loot Boxes. Tab 3. Use activity-based costing based on the cost drivers. Tab 4. Use cost-volume-profit analysis to determine an alternative selling price for the deluxe Loot box. Calculate the breakeven point for the deluxe box based on your suggested selling price. After completing the shaded boxes, follow the instructions in Project 3, Step 4 to submit your work. Tab 1 Largo Global Production and Cost Information 2020 Product Type Standard Loot Box Deluxe Loot Box Volume (Number of Products)8020 Selling Price Per Unit $ 18.20$ 27.85 Total Sales Cost of Sales Selling and Admin Charges Depreciation Purchases Gross Profit 1Determine Purchases 2020 Cost of Sale Plus: Closing Inventory Less: Opening Inventory Purchases 2Use traditional Allocation Method to Allocate Cost Allocated Cost Total Costs Standard Loot Box Deluxe Loot Box Split Purchases Based on volumes sold Selling and General Admin Half half Depreciation Half half Total Cost of Products Number of Products Cost per Product Total Cost of Prouducts / Number of Products 3Determine the Profit Margins per product Standard Loot Box Deluxe Loot Box Total Sales Less: Cost of Products Profit % Profit (Profit/ Sales ) Tab 2 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. 1Allocated the costs based on this new information Allocated Cost Standard Loot Box Deluxe Loot Box Total Purchases Freight charges Selling Costs General Admin Costs Depreciation Total Costs of Products Number of Products Cost per Product 2Determine the Profit Margins per product based on the allocations above Standard Loot Box Deluxe Loot Box Sales Less: Cost of Products Profit % Profit (Profit/ Sales ) Tab 3 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) Manufacturing overhead$ AmountCost driverStandard Loot Box Deluxe Loot Box Sum of the Cost Drivers Cost of Standard Loot Box Cost of Deluxe Loot Box Check Purchases Number of units purchased 8020100 Freight charges Kilometers travelled 1,0004,0005,000 Selling Costs Number of sales orders 4610 Admin instrative Costs Number of employees 235 Depreciation Square Feet 200010003000 Total Allocated costs Allocated cost per unit Number of Units sold 8020100 2. Use ABC costing determined in 1 above to calculate the profit and profit margins of the two products Standard Loot Box Deluxe Loot Box Total Sales Less: Costs Net (profit /loss) Profit margin Tab 4 1Assume 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 $ Profit margin as % New Selling Price S 2Provide a Proof For your Claculation: Total $ Total Less Costs Profit 3Calculate the New Breakeven Point in Volume based on the new Selling Price you detertmined above. You can assume that only the Purcahses costs are Variable, all other costs are fixed. $ Sales Price / unit Variable Costs /unit Marginal Cost / unit Fixed Costs Break Even Project 3 Questions - Report Template 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 costs were allocated between the standard and deluxe models of the product? Is the marketing manager correct that LGI is making significant margins on the Deluxe Loot Box? Please elaborate on your answer and include evidence from Tab 1 of the Excel workbook. [insert your answer here] 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. [insert your answer here] 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. [insert your answer here] 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 Largo 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. [insert your answer here] 5. If Largo Global Inc. is unable to sell the Deluxe Loot Box for more than $27.86, discuss possible alternatives. Doing the same thing LGI 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 [insert your answer here] Project 3 Review and Practice Guide Project 3 Review and Practice Guide