Bastek is a manufacturer and distributor of supplies and equipment for scientific laboratories. Its key customers 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 the Gas 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 gas flows. Both HT36 and UT66 were developed by Bastek and are manufactured in its Pennsylvania plant. OT5A is an oxygen trap that Bastek purchases from another company and modifies and tests in the same Pennsylvania plant and then rebrands as a Bastek product.

Below are the monthly volumes and prices for each product in the Gas Trap product line:

Bastek operates a just-time shop. Assume beginning inventory and ending inventory are always 0. 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 in Excel with the following schedules:

1. Traditional Income Statement for the Total Gas Traps Product Line through Operating Profit (no product detail 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 product must 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 COGS computations. However, the manufacturing manager has determined that a certain amount of fixed MOH is tied to 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 other fixed costs are shared and cannot be tied to individual products. Nevertheless, for profitability analysis, the Division General Manager suggests you use the same revenue driver for distributing the rest of the fixed costs to 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 for each 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 advertising efforts geared toward chemists who work in the oil industry, the Product Line would see a 25-unit increase in monthly sales for HT36 and a 6-unit increase in sales for UT66. Revise the Contribution Margin Income Statement to show the impact of this proposal on the CM% and Net Operating Income for each product and the Total Product Line.

8. The sales manager is dead set against any sort of price increase. He has data that shows that a majority of customers know that OT5A is essentially the same product sold by the company that also supplies it to Bastek for rebranding. Bastek is already selling this oxygen trap for $6 more. As for UT66, he believes that a price increase of 5% will lead to a 25% drop in volume. HT36 also would see a decline with a price increase. He estimates 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 just UT66 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 just HT36 by 5% for the products and total product line.

9. Based on your computations, the background information provided, and what you have learned about managerial accounting, put together a memo to Shirley Lee (Division GM) that summarizes your findings with references to supporting schedules in your accompanying Excel report. Make a recommendation on what steps should 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, the Excel templates should be consistently formatted. Formulas and cell referencing should be used for computation.