Linear Programming help in excel. Please help answer 5 & 6 and show all work For

Question

Linear Programming help in excel. Please help answer 5 & 6 and show all work

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 S80. The company's 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 S220. The company's policy is to place a fixed order for Qunits whenever the inventory level reaches a predetermined reorder point that provides sufficient stock to meet demand until the supplier's order can be shipped and received. As a 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 that influence total inventory cost. 2. Develop mathematical functions that compute the annual ordering cost and annual holding cost based on average inventory held throughout the year in order to arrive at a model for total cost. 3. Implement your model on a spreadsheet. 4. Use data tables to find an approximate order quantity that results in the smallest total cost. 5. Use Solver to verify your result. 6. Conduct what-if anal to study the sensitivity of total cost to changes in the mode lyses parameters.

Explanation / Answer

Annual Demand = 15,000 units
Cost of each unit = $ 80
Holding Cost = 18% of unit value
Ordering Cost = $ 220 per order

For implementation of a good decision model regarding inventory after considering all type costs assisted to it such as: holding cost and ordering cost, concept of EOQ is applied.

EOQ = ((2 * Annual Demand* Ordering Cost) / (Holding Cost))1/2

= ((2 * 15000 * 220) / (80*18%))1/2

= 677 units

Hence this quantity states that this manufacturing company should reorder the quantity when it has 677 units.

2)Mathematically, costs related to inventory are computed in the following manner:

1) Annual ordering cost = Ordering cost per order * Number of orders in a year

= 220 * 15000/677 = 220 * 22 = 4840

2) Holding cost = Holding cost per unit * Average inventory throughout the year
Average inventory throughout the year = 15,000/12 = 1250 units

Holding cost = 18%* 1250 = 225

Total cost = 4840 + 225 = 5065   

Related Questions

Navigate

• Browse (All)
• Browse L
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.