Air Power Products, Inc. (API) is a new entrepreneurial venture located in the San Francisco Bay Area’s Livermore Valley, just west of the famous Altamont Pass wind power farms. API’s products are “personal” wind power units capable of producing sufficient power in windy locales to dramatically reduce homeowners’ and small business’ dependence on the main power grid.

All components of the wind power units, with the exception of the generator module, are produced at the company’s facility in Livermore. The generators are outsourced locally to Tesla Generators, Ltd. (TesGen), a manufacturing company specializing in small and efficient power generators.

The purchase price per unit of a TesGen generator is $150. TesGen does not offer quantity discounts on purchases. TesGen delivery performance is extremely reliable and there are no expected shortages. With each purchase order of TesGen generators, API incurs an ordering cost of $500 for the order. In addition to the ordering cost, API incurs an inventory carrying cost that is equal to $200 per each TesGen generator per year.

The management team of API is embarking on a strategic 5-year planning exercise with a focus on long-term cost reduction. Management is using an annual demand forecast of 15,000 API power units for their planning purposes.

One of the high profile processes identified as a candidate for possible cost savings is inventory planning, specifically related to the total inventory costs associated with outsourcing (procuring) the generators from TesGen. However, over the past year, through various Six Sigma process improvement efforts, API has freed sufficient capacity in API’s design and production departments to consider replacing the TesGen generator with one of API’s proprietary internal design and production. API management is confident an internally-manufactured generator can meet or exceed all quality and design requirements already required met by the TesGen generator.

If API were to decide to undertake internal production of generators, the expected production capacity (production rate) is 900 generators weekly. API management is confident that within a two-week lead time, internal schedules can be arranged to support production whenever inventory replenishment is required. All parameters of this production will be constant, shortages are not expected, production costs are expected to be $155 per unit per year, and setup costs will be $200 per production order. Due to various internal cash flow considerations, the per-unit inventory carrying cost of internally produced generators is expected to be 50% of the carrying cost of the outsourced TesGen generators.

1. Prepare your submission using Excel. Submit the Excel file so that your formulas can be reviewed. (TIP: Consider using the
https://drive.google.com/file/d/1Z8DpMjPOCm--DZqeYQgLngE-ZxKVvJ54/view?usp=sharing
Adjust as necessary to fit the case facts).

2. Based on facts given in the case, identify API’s optimal inventory policy for outsourcing generators to TesGen. Calculate the optimal order quantity and annual total cost of inventory for the generators. Clearly explain your results. NOTE: DO NOT ROUND YOUR EXCEL FORMULAS ROUND(). However, DO decrease your Excel displayed decimals to two places for currency and zero places for units. How to decrease display only.

3. Based on facts given in the case, identify what API’s optimal inventory planning policy would be if the company were to produce generators internally. Calculate what the optimal production order quantity and annual total cost of generator inventory would be. (TIP: Assume API operates production all 52 weeks of the year). NOTE: DO NOT ROUND YOUR EXCEL FORMULAS ROUND(). However, DO decrease your Excel displayed decimals to two places for currency and zero places for units. How to decrease display only.

4. Make a recommendation as to whether API should continue to outsource (purchase) generators from TesGen or initiate internal production. Clearly state the total annual savings expected from your recommendation.