Canisters Inc. sells a wide variety of drums, bins, boxes and other containers that are used in the chemical industry. One of the company's products is a heavy-duty corrosion-resistant metal drum, called the WD drum used to store toxic waste. Production is constrained by the capacity of an automated welding machine, which has a maximum capacity of 2,000 hours of welding time available. Each drum requires 0.40 hour of welding time, and production is currently maximized. At present, the welding machine is used exclusively to make WD drums. The accounting department has provided the following financial data concerning WD drums: WD Drums Selling price per drum Cost per drum $ 149.00 52.10 3.60 Direct Materials Direct Labor ($18/hour) Manufacturing Overhead Selling and Admin. Margin per drum 4.50 29.80 90.00 59.00 Management believes that a total of 6,000 WD drums could be sold each year if the company had sufficient capacity. As an alternative to adding another welding machine, management has considered buying additional drums from an outside supplier, Beater Industries. Beater would be able to provide up to 4,000 WD type drums per year at a price of 138 per drum, which Canisters would resell at its normal sales price after labeling. Meliha Cicek, production manager, has suggested that the company could make better use of the welding machine by manufacturing bike frames, which would require only 0.50 hours of welding time per frame and yet sell for far more than drums. Meliha believes that Canisters could sell 1,600 bike frames per year to manufacturers at a price of 239 each The accounting department has provided the following data concerning proposed product: The accounting department has provided the following data concerning proposed product: $ 239.00 Bike Frames Selling price per frame Cost per frame Direct Materials Direct Labor ($18/hour) Manufacturing Overhead Selling and Admin. Margin per frame $ 99.40 $ 28.80 $36.00 $ 47.80 $ 212.00 $ 27.00 The bike frames could be produced with existing equipment and personnel. Manufacturing overhead is allocated to products on the basis of labor hours. Most of the manufacturing overhead consists of fixed common costs such as rent on the factory building, but some of it is variable. The variable manufacturing overhead has been estimated at 1.35 per WD drum and 1.90 The variable manufacturing overhead cost would not be incurred on drums acquired from the outside supplier. Selling and administrative expenses are allocated to products on the basis of the revenues. Almost all of the selling and administrative expenses are fixed common costs, but it has been estimated that variable selling and administrative expenses amount to 0.75 per drum whether made or purchased and would be 1.30 per frame All of the company's employees -direct and indirect-are paid for full 40-hour workweeks and the company has a policy of laying off workers only in major recessions. 1. Compute the impacts and recommend to management which strategy would result in the highest profits to Drummond. Requirement 1: Make or buy the drums (or some combination - this is sheet 2) Requirement 2: Produce only the bike frames (sheet 3) Requirement 3: Make/buy combination of drums and frames (sheet 4) 2. What non-quantifiable factors should be considered and why? Requirement 4: Write a conclusion for management (sheet 5) SET UP YOUR EXCELWORKBOOK AS FOLLOWS: Sheet 1: Inputs (this sheet) Sheet 2: Relevant costs - Make drums or buy drums (or some combination) Sheet 3: Produce only bike frames Sheet 4: Combination of alternatives, what is best course of action for company. How many drums and/or bike frames should the company make or buy? Sheet 5: Written conclusion (letter to management identifying what Canisters should do) and non-quantifiable factors Be sure to reference your numbers computed on sheets 2 through 4 in your conclusion. Please submit your work under assignments in Canvas and name your file with the last names of your partners. For example, Smith_Jones.xls Only one group member should submit the analysis. Instructions: - All the inputs you need to complete the case are highlighted in blue on this sheet. You need to link (cell reference) to these numbers when preparing your analyses. - Clearly show your computations. For example, if you need to compute (example) total costs of direct materials you would show the calculation as follows: Direct materials: Cost per unit XX # of units XX Total cost of Direct materials XX Canisters Inc. sells a wide variety of drums, bins, boxes and other containers that are used in the chemical industry. One of the company's products is a heavy-duty corrosion-resistant metal drum, called the WD drum used to store toxic waste. Production is constrained by the capacity of an automated welding machine, which has a maximum capacity of 2,000 hours of welding time available. Each drum requires 0.40 hour of welding time, and production is currently maximized. At present, the welding machine is used exclusively to make WD drums. The accounting department has provided the following financial data concerning WD drums: WD Drums Selling price per drum Cost per drum $ 149.00 52.10 3.60 Direct Materials Direct Labor ($18/hour) Manufacturing Overhead Selling and Admin. Margin per drum 4.50 29.80 90.00 59.00 Management believes that a total of 6,000 WD drums could be sold each year if the company had sufficient capacity. As an alternative to adding another welding machine, management has considered buying additional drums from an outside supplier, Beater Industries. Beater would be able to provide up to 4,000 WD type drums per year at a price of 138 per drum, which Canisters would resell at its normal sales price after labeling. Meliha Cicek, production manager, has suggested that the company could make better use of the welding machine by manufacturing bike frames, which would require only 0.50 hours of welding time per frame and yet sell for far more than drums. Meliha believes that Canisters could sell 1,600 bike frames per year to manufacturers at a price of 239 each The accounting department has provided the following data concerning proposed product: The accounting department has provided the following data concerning proposed product: $ 239.00 Bike Frames Selling price per frame Cost per frame Direct Materials Direct Labor ($18/hour) Manufacturing Overhead Selling and Admin. Margin per frame $ 99.40 $ 28.80 $36.00 $ 47.80 $ 212.00 $ 27.00 The bike frames could be produced with existing equipment and personnel. Manufacturing overhead is allocated to products on the basis of labor hours. Most of the manufacturing overhead consists of fixed common costs such as rent on the factory building, but some of it is variable. The variable manufacturing overhead has been estimated at 1.35 per WD drum and 1.90 The variable manufacturing overhead cost would not be incurred on drums acquired from the outside supplier. Selling and administrative expenses are allocated to products on the basis of the revenues. Almost all of the selling and administrative expenses are fixed common costs, but it has been estimated that variable selling and administrative expenses amount to 0.75 per drum whether made or purchased and would be 1.30 per frame All of the company's employees -direct and indirect-are paid for full 40-hour workweeks and the company has a policy of laying off workers only in major recessions. 1. Compute the impacts and recommend to management which strategy would result in the highest profits to Drummond. Requirement 1: Make or buy the drums (or some combination - this is sheet 2) Requirement 2: Produce only the bike frames (sheet 3) Requirement 3: Make/buy combination of drums and frames (sheet 4) 2. What non-quantifiable factors should be considered and why? Requirement 4: Write a conclusion for management (sheet 5) SET UP YOUR EXCELWORKBOOK AS FOLLOWS: Sheet 1: Inputs (this sheet) Sheet 2: Relevant costs - Make drums or buy drums (or some combination) Sheet 3: Produce only bike frames Sheet 4: Combination of alternatives, what is best course of action for company. How many drums and/or bike frames should the company make or buy? Sheet 5: Written conclusion (letter to management identifying what Canisters should do) and non-quantifiable factors Be sure to reference your numbers computed on sheets 2 through 4 in your conclusion. Please submit your work under assignments in Canvas and name your file with the last names of your partners. For example, Smith_Jones.xls Only one group member should submit the analysis. Instructions: - All the inputs you need to complete the case are highlighted in blue on this sheet. You need to link (cell reference) to these numbers when preparing your analyses. - Clearly show your computations. For example, if you need to compute (example) total costs of direct materials you would show the calculation as follows: Direct materials: Cost per unit XX # of units XX Total cost of Direct materials XX