Project 3 excel sheet

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 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 110.00 118.80 127.20 135.20 142.80 150.00 156.80 163.20 169.20 174.80 180.00 184.80 189.20 193.20 196.80 200.00 202.80 205.20 207.20 Deluxe Boxes Profit Maximization ( Columns C to H obtain from Project 2) Deluxe boxes sold per month Revenue (price x Price (millions) volume) $ 30.00 $ 1 30.00 $ 29.50 $ 1.2 35.40 $ 29.00 $ 1.35 39.15 $ 28.50 $ 1.5 42.75 $ 28.00 $ 1.55 43.40 $ 27.50 $ 1.6 44.00 $ 27.00 $ 1.65 44.55 $ 26.50 $ 1.7 45.05 $ 26.00 $ 1.75 45.50 $ 25.50 $ 1.8 45.90 $ 25.00 $ 1.85 46.25 Variable Cost per Standard box $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 10.00 10.00 10.00 10.00 10.00 10.00 10.00 10.00 10.00 10.00 10.00 10.00 10.00 10.00 10.00 10.00 10.00 10.00 10.00 Variable Cost per Deluxe box $ 20.00 $ 20.00 $ 20.00 $ 20.00 $ 20.00 $ 20.00 $ 20.00 $ 20.00 $ 20.00 $ 20.00 $ 20.00 1.9 1.95 2 2.05 2.1 2.15 2.2 2.25 $ $ $ $ $ $ $ $ 24.50 24.00 23.50 23.00 22.50 22.00 21.50 21.00 $ $ $ $ $ $ $ $ 46.55 46.80 47.00 47.15 47.25 47.30 47.30 47.25 $ $ $ $ $ $ $ $ 20.00 20.00 20.00 20.00 20.00 20.00 20.00 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 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 Number of Boxes per month (in Millions)of Boxes per year Number (millions) Revenue Subtract: Variable Costs Equals: Contribution Margin Subtract: Fixed Costs Equals: Operating Operating Profit %Profit (based on revenue) Standard Boxes 9 108 $ (in millions) Deluxe Boxes 1.5 18 $ (in millions) Total 10.5 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 (cost per unit x volume) $ 50.00 $ 55.00 $ 60.00 $ 65.00 $ 70.00 $ 75.00 $ 80.00 $ 85.00 $ 90.00 $ 95.00 $ 100.00 $ 105.00 $ 110.00 $ 115.00 $ 120.00 $ 125.00 $ 130.00 $ 135.00 $ 140.00 Variable Cost (cost per unit x $ 20.00 $ 24.00 $ 27.00 $ 30.00 $ 31.00 $ 32.00 $ 33.00 $ 34.00 $ 35.00 $ 36.00 $ 37.00 Fixed cost per month (millions) $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 10.00000 10.00000 10.00000 10.00000 10.00000 10.00000 10.00000 10.00000 10.00000 10.00000 10.00000 10.00000 10.00000 10.00000 10.00000 10.00000 10.00000 10.00000 10.00000 Fixed cost per month (millions) $ 3.00000 $ 3.00000 $ 3.00000 $ 3.00000 $ 3.00000 $ 3.00000 $ 3.00000 $ 3.00000 $ 3.00000 $ 3.00000 $ 3.00000 Total Cost Monthly Profit (revenue Annual Revenue (Fixed + all costs) (millions) Variable) $ 60.0000 $ 50.00 $ 65.0000 $ 53.80 $ 70.0000 $ 57.20 $ 75.0000 $ 60.20 $ 80.0000 $ 62.80 $ 85.0000 $ 65.00 $ 90.0000 $ 66.80 $ 95.0000 $ 68.20 $ 100.0000 $ 69.20 $ 105.0000 $ 69.80 $ 110.0000 $ 70.00 $ 115.0000 $ 69.80 $ 120.0000 $ 69.20 $ 125.0000 $ 68.20 $ 130.0000 $ 66.80 $ 135.0000 $ 65.00 $ 140.0000 $ 62.80 $ 145.0000 $ 60.20 $ 150.0000 $ 57.20 Total Cost (Fixed + $ 23.0000 $ 27.0000 $ 30.0000 $ 33.0000 $ 34.0000 $ 35.0000 $ 36.0000 $ 37.0000 $ 38.0000 $ 39.0000 $ 40.0000 Annual Revenue Monthly Profit (revenue (millions) all costs) $ 7.00 $ 8.40 $ 9.15 $ 9.75 $ 9.40 $ 9.00 $ 8.55 $ 8.05 $ 7.50 $ 6.90 $ 6.25 Annual information ( $ $ $ $ $ $ $ $ 38.00 39.00 40.00 41.00 42.00 43.00 44.00 45.00 $ $ $ $ $ $ $ $ 3.00000 3.00000 3.00000 3.00000 3.00000 3.00000 3.00000 3.00000 $ $ $ $ $ $ $ $ 41.0000 42.0000 43.0000 44.0000 45.0000 46.0000 47.0000 48.0000 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 $ $ $ $ $ $ $ $ 5.55 4.80 4.00 3.15 2.25 1.30 0.30 (0.75) Annual information ( for 12 Months) Annual VC (millions) Annual FC (millions) Annual Total Costs (millions) Annual Profit Annual information Months) Annual VC Annual FC ( for 12Annual Total Costs Annual Profit (millions) (millions) (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 Operating Profit %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 Cost of Deluxe Boxes Total Cost Check (must 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, 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 $23 per box and end up making substantially higher profit than they ever did on the Deluxe Boxes. Based on knowledge of pri suggest that it may in time even result in much higher sales volumes. The marketing manager believes that a lower selling pric 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 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 the production of a Sustainable Deluxe Boxes will only be considered if it can achieve at least the same operating profit percen 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 p Standard 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 % Subtract: Required Operating Profit Equals: Cost % 100.00% 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. Total $ Proof: 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 Selling price Subtract: Variable costs Equals: Unit Contribution Margin Fixed Costs (in total for Sustainable Deluxe Boxes) Breakeven Quantity Break-even Value Total $ 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 manager believes that the company could sell the Sustaianable Deluxe Boxes for n the Deluxe Boxes. Based on knowledge of price elasticity of demand s/he/they eting manager believes that a lower selling price will also entice current Deluxe 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 chieve at least the same operating profit percentage for the Sustainable g method for Standard Boxes (See Tab 3) . e Sustainable Deluxe Boxes to meet the same percentage as the ved on Sustainable 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

Project 3 excel sheet

We offer the best custom writing paper services. We have answered this question before and we can also do it for you.

GET STARTED TODAY AND GET A 20% DISCOUNT coupon code DISC20

Leave a Comment