Manufacturing Company

For a manufacturing company that you are consulting for, managers are unsure about making inventory decisions associated with a key engine component. The annual demand is estimated to be 15,000 units and is assumed to be constant throughout the year. Each unit costs \$80. The companys accounting department estimates that its opportunity cost for holding this item in stock for one year is 18% of the unit value. Each order placed with the supplier costs \$220. The companys policy is to place a fixed order for Q units whenever the inventory reaches a predetermined reorder point that provides sufficient stock to meet demand until the suppliers order can be shipped and received. As consultant, your task is to develop and implement a decision model to help them arrive at the best decision. As a guide, consider the following: 1. Define the data, uncontrollable inputs, and decision variables. 2. Develop mathematical functions to compute the annual ordering cost and annual holding cost based on average inventory to find the total cost. 3. Implement the model on a spreadsheet! 4. Set up Data Table (two columns): Order Qty and Order Qty Cost (\$) 5. Use Solver to verify Order Quantity with smallest Total Cost. 6. Conduct what-if analyses to study the sensitivity of total cost to changes in the model parameters. 7. Explain your results and analysis in a memo to the vice president of operations.