AirPower Products, Inc. AirPower 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. Assignment 1. Prepare your submission using Excel. Submit the Excel file so that your formulas can be reviewed. (TIP: Consider using the Examples_Chap12_Inventory_EOQ_POQ_Qty_Disc_VXX.xlsx Excel file included in Blackboard. 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 RAND(). 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 RCOND(). 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. Formulas Data Review View Tell me X Arial TO Paste - A A A General B 2 Wrap Text Merge Center F17 xfx $ % ) B C D H s 2920 5000 per unit per year 80.00 per order 20.00 per un Q 97 units per onder 30-2 per year HC OC PP TC 1 2 Economic order quantity EOQ) 3 Annual demand, units 4 Holding (Camrying) Costs 5 Ordering Costs Purchase Prices 7 Economic order quantity EOQ) 8 Optimal order quantity (EOQ) = 2-0 SYM 9 Number of onders per year-DIO 10 Inventory Costs 11 Holding Costs (OH)2 12 Ordering Costs DIOS 3 Purchase Price = PD 4 Annual Total Costs, 3 15 6 Production Order Quantity (POQ) 7 Annual demand 8 Production rate per Production Time Penod PTP) 9 Usage (demand) rate-D/PTP Holding Cost, $ 1 Setup Cost, $ Production Cost, $ 3 Production Order Quantity (POQ) Optimal production order quantity POO) Number of orders per year-DIO Inventory Costs Holding Costs = (O'HY2 11-din Setup Costs (DIO'Y'S Production Cost PD- Annual Total Costs, $ 2,416.61 2,416,61 81 760.00 86 593 22 586.593.21 d H 2920 10 units per day. 360/ Bunita per day 50.00 perunt per year 80.00 per order 28.00 perunt per year Q" N. 216 (05) 13.5. onders per year HC SC PC TC 1,086.74 1,080.74 31,760.00 83.921.46 Quantity Discount Model Annual demand, units Holding (Carrying) Cost, $ Ondering Costs D H s 2920 50.00 perunt per year 80.00 per order Price Breaks 199 100-199 200 and up P1 P2 P3 28.00 24.00 22.00 EOG 97 97 97 EOQ fit into Price Break YosFeasible Norasible Not feasible Optimal Value for Price Break 97 100 200 Total costs for each price break 1-99 Optimal Value 97 100-199 200 and up 100 Holding costs 2.416.61 2,500.00 5,000.00 Ordering Costs 2.416.61 2,336.00 1,168.00 Purchase Price, 5 Total Costs 81.760.00 86.593.22 70,080.00 74,916.00 54 240.00 70.400.00 200 Optimal quantity Optimal annual total cost, Q TC 200 70,408.00 Inventory examples + AirPower Products, Inc. AirPower 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. Assignment 1. Prepare your submission using Excel. Submit the Excel file so that your formulas can be reviewed. (TIP: Consider using the Examples_Chap12_Inventory_EOQ_POQ_Qty_Disc_VXX.xlsx Excel file included in Blackboard. 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 RAND(). 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 RCOND(). 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. Formulas Data Review View Tell me X Arial TO Paste - A A A General B 2 Wrap Text Merge Center F17 xfx $ % ) B C D H s 2920 5000 per unit per year 80.00 per order 20.00 per un Q 97 units per onder 30-2 per year HC OC PP TC 1 2 Economic order quantity EOQ) 3 Annual demand, units 4 Holding (Camrying) Costs 5 Ordering Costs Purchase Prices 7 Economic order quantity EOQ) 8 Optimal order quantity (EOQ) = 2-0 SYM 9 Number of onders per year-DIO 10 Inventory Costs 11 Holding Costs (OH)2 12 Ordering Costs DIOS 3 Purchase Price = PD 4 Annual Total Costs, 3 15 6 Production Order Quantity (POQ) 7 Annual demand 8 Production rate per Production Time Penod PTP) 9 Usage (demand) rate-D/PTP Holding Cost, $ 1 Setup Cost, $ Production Cost, $ 3 Production Order Quantity (POQ) Optimal production order quantity POO) Number of orders per year-DIO Inventory Costs Holding Costs = (O'HY2 11-din Setup Costs (DIO'Y'S Production Cost PD- Annual Total Costs, $ 2,416.61 2,416,61 81 760.00 86 593 22 586.593.21 d H 2920 10 units per day. 360/ Bunita per day 50.00 perunt per year 80.00 per order 28.00 perunt per year Q" N. 216 (05) 13.5. onders per year HC SC PC TC 1,086.74 1,080.74 31,760.00 83.921.46 Quantity Discount Model Annual demand, units Holding (Carrying) Cost, $ Ondering Costs D H s 2920 50.00 perunt per year 80.00 per order Price Breaks 199 100-199 200 and up P1 P2 P3 28.00 24.00 22.00 EOG 97 97 97 EOQ fit into Price Break YosFeasible Norasible Not feasible Optimal Value for Price Break 97 100 200 Total costs for each price break 1-99 Optimal Value 97 100-199 200 and up 100 Holding costs 2.416.61 2,500.00 5,000.00 Ordering Costs 2.416.61 2,336.00 1,168.00 Purchase Price, 5 Total Costs 81.760.00 86.593.22 70,080.00 74,916.00 54 240.00 70.400.00 200 Optimal quantity Optimal annual total cost, Q TC 200 70,408.00 Inventory examples +