Bastek is a manufacturer and distributor

CASE I – Carlozzi – MBA Managerial Accounting, Wilmington UniversityBastek is a manufacturer and distributor of supplies and equipment for scientific laboratories. Its keycustomers are in the oil, environmental testing, and chemical industries.Shirley Lee, the General Manager of Bastek’s Gas Products Division, is concerned about the profitability of theGas Traps product line and has asked you to help her make some decisions about the business.There are three products in this product line: HT36, UT66 and OT5A.HT36 is a gas trap that removes hydrocarbons from gas flows within gas chromatographs. UT66 is a larger“universal” trap that removes oxygen, hydrocarbons, moisture, carbon dioxide and carbon monoxide from gasflows. Both HT36 and UT66 were developed by Bastek and are manufactured in its Pennsylvania plant. OT5Ais an oxygen trap that Bastek purchases from another company and modifies and tests in the same Pennsylvaniaplant and then rebrands as a Bastek product.Below are the monthly volumes and prices for each product in the Gas Trap product line: Average monthly fixed costs associated with the Gas Trap product line are $70,000 in Manufacturing Overhead(MOH), $15,000 in Selling Expense, and $30,000 in Administrative Expense.Variable costs associated with each product are: The General Manager would like to see Operating Profit for the product line increase to $50,000 per month.You have been asked to analyze the data and make some recommendations. Please put together a report inExcel with the following schedules:1. Traditional Income Statement for the Total Gas Traps Product Line through Operating Profit (no productdetail on this one.)2. Contribution Margin Income Statement for the Total Gas Product Line (again no product detail on this one.)3. Compute and show the Break Even Point for the Gas Trap Product Line in Sales Dollars.4. Assuming the sales mix and fixed costs will not change, calculate how many more units of each productmust be sold to get to $50,000 in operating profit per month. Product Line revenue is used as a driver of fixed MOH for absorption for inventory valuation and COGScomputations. However, the manufacturing manager has determined that a certain amount of fixed MOH is tiedto each product and would go away if the product were discontinued: $9000 for HT36, $18,000 for UT66 and$10,000 for OT5A. Both the manufacturing manager and the Division General Manager assume that all otherfixed costs are shared and cannot be tied to individual products. Nevertheless, for profitability analysis, theDivision General Manager suggests you use the same revenue driver for distributing the rest of the fixed coststo each product when preparing product-specific schedules.5. Break out the Contribution Margin Income Statement by Product. Calculate the CM % for each product.6. Based on the apportionment of fixed costs in the schedule for question 5, compute the break-even amount foreach of the products in units and dollars.7. The marketing manager has data that leads her to believe that by investing $3500 each month in advertisingefforts geared toward chemists who work in the oil industry, the Product Line would see a 25-unit increase inmonthly sales for HT36 and a 6-unit increase in sales for UT66. Revise the Contribution Margin IncomeStatement to show the impact of this proposal on the CM% and Net Operating Income for each product and theTotal Product Line.8. The sales manager is dead set against any sort of price increase. He has data that shows that a majority ofcustomers know that OT5A is essentially the same product sold by the company that also supplies it to Bastekfor rebranding. Bastek is already selling this oxygen trap for $6 more. As for UT66, he believes that a priceincrease of 5% will lead to a 25% drop in volume. HT36 also would see a decline with a price increase. Heestimates that a 5% increase will lead to a 2% decline in volume.A. Revise the Contribution Margin Income Statement to show the impact of increasing the price of justUT66 by 5% for the products and total product line.B. Revise the Contribution Margin Income Statement to show the impact of increasing the price of justHT36 by 5% for the products and total product line.9. Based on your computations, the background information provided, and what you have learned aboutmanagerial accounting, put together a memo to Shirley Lee (Division GM) that summarizes your findings withreferences to supporting schedules in your accompanying Excel report. Make a recommendation on what stepsshould be taken to continue to understand and improve the profitability of the overall Product Line.NOTES: The memo should be in professional English with perfect grammar and formatting. Likewise, theExcel templates should be consistently formatted. Formulas and cell referencing should be used forcomputation.