# MIS4380-M12-ExcelQuiz.xlsx - Q1. Neha Shah is the...

• Homework Help
• 39
• 100% (1) 1 out of 1 people found this document helpful

This preview shows page 1 - 7 out of 39 pages.

Q1. Neha Shah is the purchasing agent for a firm that sells industrial valves and fluid control devices. Onemost popular valves is the KA1, which has an annual demand of 8,000 units. The cost of each valve is \$11the inventory carrying cost is estimated to be 7% of the cost of each valve. Neha has made a study of theinvolved in placing an order for any of the valves that the firm stocks, and she has concluded that the aveordering cost is \$45 per order. Furthermore, it takes about two weeks (10 business days) for an order to afrom the supplier, and during this time the demand per week for KA1 valves is approximately 120.a. Compute the EOQ, ROP, optimal number of orders per year, and total annual cost for KA1 valves.b. When, Q is 350 and the ordering cost is \$50, compute the holding cost.c.When, Q is 400 and the holding cost is \$9, compute the ordering cost.Hint: This is EOQ model. Refer to 12.4 &5.ROP should be computed using days.A complete Excel set up (25%) and correct answers (75%) are needed to be graded.
e of the10, ande costseragearrive
Q1InventoryEconomic Order Quantity ModelOrder Quantity DataAnnual Demand Rate, D8000Setup/Ordering Cost, S45Holding/Carrying Cost per Unit per Year, H\$7.70 (fixed amount)Unit price, P110Reorder Point Data (optional)Days per year or ......Daily demand rate, d24Lead time in days, L10Safety stock, ssReorder Point, dL+ss240ResultsOptimum Order Quantity, Q* (EOQ)305.79Maximum Inventory, Q*305.79Average Inventory, Q*/2152.89Number of Orders, D/Q*26.16Annual Holding Cost, HQ*/2\$1,177.29Annual Order Cost, DS/Q*\$1,177.29Annual Unit Costs, PD\$880,000.00\$882,354.57Cost Table (Sensitivity)Start graph at76.447078716 Increment byOrder Quantity, QHolding cost76.454709.14294.32101.933531.86392.43127.412825.48490.54152.892354.57588.64178.382018.20686.75203.861765.93784.86229.341569.71882.96254.821412.74981.07280.311284.311079.18305.791177.291177.29331.271086.721275.39356.751009.101373.50382.24941.831471.61407.72882.961569.71433.20831.021667.82Total Annual Cost, TcSetup/OrdercostEnter the data in the shaded area.Enter the data in the shaded area.
458.68784.861765.93484.16743.551864.03509.65706.371962.14535.13672.732060.25560.61642.162158.36586.09614.242256.46611.58588.642354.57637.06565.102452.68662.54543.362550.78305.790.00305.792354.57
25.48236Total cost5003.463924.283316.022943.212704.952550.782452.682393.812363.492354.572362.122382.602413.432452.682498.85Q1. Neha Shah is the purchasing agent for a firmost popular valves is the KA1, which has an athe inventory carrying cost is estimated to be 7involved in placing an order for any of the valvordering cost is \$45 per order. Furthermore, itfrom the supplier, and during this time the dema. Compute the EOQ, ROP, optimal number ofb. When, Q is 350 and the ordering cost is \$50c.When, Q is 400 and the holding cost is \$9, cHint: This is EOQ model. Refer to 12.4 &5.ROP should be computed using days.A complete Excel set up (25%) and correct answ
2550.782607.582668.512732.982800.512870.702943.213017.773094.15

Upload your study docs or become a

Course Hero member to access this document

Upload your study docs or become a

Course Hero member to access this document

End of preview. Want to read all 39 pages?

Upload your study docs or become a

Course Hero member to access this document

Term
Spring
Professor
N/A
Tags
Neha Shah