# inventory optimization excel

s Case II: XYZ (p) Ltd. is the sole bottler of Coca Cola at the Central India. The aim is to decide an optimal ordering quantity, Q, which minimizes the total cost of an inventory system when the demand occurs at a constant rate. Q First step is to check the EOQ by using the EOQ formula and then check the total cost at the price breaks, whichever gives the lowest the optimal ordering quantity. 2 ( Here D = 50000; Ordering Cost = \$5; Carrying Cost = 2.5%; Cost Price \$200. The two basic questions of inventory management are: 1) When should an order be placed for an Item? Mean Inventory Level is given by Singh, S. (2018) Solving Basic Inventory Models Using Excel. Q C Order quantity is instantaneous (Figure 1). Annual Production rate (P) is more than the annual demand (D). Q − The company presently has a policy of placing ten orders every year: Advise the management of Monu Tractors as to whether it should continue with its present policy or switch over to EOQ model. Scientific Research The annual demand of Pepsi at Nagpur is 2,000,000 bottles. t You can use the worksheet that most closely models your situation as a starting point. t p―Daily production rate; d―Daily demand rate; D = Annual Demand; p = Set up Cost; Therefore items are produced in lots or batches. This note can be used in courses like economics, operations management, operations research, supply chain management. Copyright © 2006-2020 Scientific Research Publishing Inc. All Rights Reserved. Figure 1. Excel Program for solving quantity discount model (Figure 6 and Figure 7). C Inventory order cycle of EOQ model. Even transportation cost reduces if items are bought in bulk. This note can be The model built here has considered both the cases i.e. Accordingly the model can calculate the daily demand and production rate. For example comparison of the company present policy with applicable inventory model. p d C ordering cost per order is \$5 and the carrying cost is 0.025 of the average inventory value. ) . Here Annual Demand is 2 million, the holding cost is ten dollars per bottle per year, the cost of setting up the production is \$1000 and daily production p = 10,000 bottles and daily demand = 6000 bottles. In this note I have introduced the basic models and also given their excel programs so by using this programs, students can solve the inventory related issues by applying the various elementary models. The demand is fixed and known. The excel programs given above would help the students to do the analyses of the various situations. C + Click the model names to display each worksheet model in your browser. As it has lots of benefits, one of the main reasons was unhampered production. There is mention of economic order quantity as minimal quantity cost in book purchasing and storing by Ralph Currier Davis [4] . Figure 8. o S―Cost of placing order; D―Annual demand; H―Annual per-unit carrying cost; Q―Order quantity; Annual Ordering Cost = S * D/Q; Annual Carrying Cost= H * Q/2; Total Inventory Cost= S * D/Q + H * D/2; Number of Orders = D/Q; Average Inventory = Q/2. Q d s James M. Cargal [6] also worked on the EOQ formula and he also tried to find why large number of organizations are still using EOQ formula even it has some unrealistic assumptions. t Q s just needs to enter the data in the white cells and all the results are The user c Q The Disadvantages of Having a Large Inventory. Excel Program for solving Production Quantity Model (Figure 4 and Figure 5).

This entry was posted in Uncategorized. Bookmark the permalink.