Change the value in yellow highlighted cells to observe how changing the parameter affects the calculated values. Blue highlighted cells contain formulas that calcuate certain values based upon the provided parameters. Do not alte Current Ordering Model Variables Annual demand Average daily demand Lead time for a new order in days Purchase cost per unit Ordering cost for each order Holding or carrying cost per unit per year Current Ordering Model Number of units ordered per order Annual purchase cost (material cost) Number of orders per year Annual ordering cost Average number of units in inventory Average dollar value of inventory Annual holding or carrying cost Total annual cost Reorder point hlighted cells to observe how changing the parameter affects the calculated values. ormulas that calcuate certain values based upon the provided parameters. Do not alter the content of these cells. D d L C Co Ch 15,000 40 7 $125.00 $250.00 $5.00 Q C*D D/Q (D/Q)*Co Q/2 C*(Q/2) (Q/2)*Ch Annual purchase cost + Annual ordering cost + Annual holding cost d*L 560 $1,875,000.00 27 $6,696.43 280 $35,000.00 $1,400.00 $1,883,096.43 280 these cells. Change the value in yellow highlighted cells to observe how changing the parameter affects the calculated values Blue highlighted cells contain formulas that calcuate certain values based upon the provided parameters. Do not EOQ Model Variables Annual demand Average daily demand Lead time for a new order in days Purchase cost per unit Ordering cost per order Holding or carrying cost per unit per year Economic Order Quantity Model EOQ (optimal number of units to order per order) Number of orders per year Average inventory in units Average dollar value of inventory Reorder point Annual purchase cost (material cost) Annual ordering cost Annual holding or carrying cost Total Annual Cost serve how changing the parameter affects the calculated values. ate certain values based upon the provided parameters. Do not alter the content of these cells. D d L C Co Ch 15,000 40 7 $125.00 $250.00 $5.00 SQRT((2*D*Co)/Ch) D/Q Q/2 (C*Q)/2 d*L C*D (D/Q)*Co (Q/2)*Ch Purchase Cost + Ordering Cost + Holding Cost 1,224.7 12.2 612.4 $76,546.55 280 $1,875,000.00 $3,061.86 $3,061.86 $1,881,123.72 Change the value in yellow highlighted cells to observe how changing the parameter affects the calculated values. Blue highlighted cells contain formulas that calcuate certain values based upon the provided parameters. Do not alte Production Run Model Variables Annual demand Average daily demand Lead time for a new order in days Purchase cost per unit Holding or carrying cost per unit per year Set-up cost Daily production rate EOQ Model Without Instantaneous Inventory Receipt (Production Run Model) Length of production run in days Optimal production quantity (Q*) Length of each production cycle Maximum inventory level Average inventory Average dollar value of inventory Number of set-ups Annual holding cost Annual set-up cost Annual purchase cost (material cost) Total annual cost Reorder point ighted cells to observe how changing the parameter affects the calculated values. rmulas that calcuate certain values based upon the provided parameters. Do not alter the content of these cells. D d L C Ch Cs p 15,000 40 7 $125.00 $5.00 $500.00 60 aneous Inventory Receipt (Production Run Model) t SQRT((2*D*Cs)/(Ch(1-(d/p)))) Q/p Q(1-(d/p)) (Q/2)*(1-(d/p)) ((Q/2)*(1-(d/p)))*C D/Q (Q/2)*(1-(d/p))*Ch (D/Q)*Cs D*C Annual holding cost + Annual set-up cost + Annual purchase cost d*L 50.0 3000.00 50.00 1000.00 500.00 $62,500.00 5.00 $2,500.00 $2,500.00 $1,875,000.00 $1,880,000.00 280 Change the value in yellow highlighted cells to observe how changing the parameter affects the calculated values. Blue highlighted cells contain formulas that calcuate certain values based upon the provided parameters. Do not a Quantity Discount Model Variables Annual demand Purchase cost per unit Holding or carrying cost per unit per year Ordering cost per order Holding or carrying cost as a percentage of unit cost D C Ch Co I 15,000 $125.00 $5.00 $250.00 4.00% Discount Number Discount Minimum Qty 1 2 3 4 0 751 1501 2001 s the calculated values. ed parameters. Do not alter the content of these cells. Discount Maximum Qty Discount Discount Cost EOQ Q 750 1000 2000 None 0.00% 5.00% 10.00% 15.00% $125.00 $118.75 $112.50 $106.25 1,224.74 1,256.56 1,290.99 1,328.42 1224.74 1256.56 1501.00 2001.00 Purchase Cost $1,875,000.00 $1,781,250.00 $1,687,500.00 $1,593,750.00 Ordering Cost Holding Cost $3,061.86 $2,984.33 $2,498.33 $1,874.06 $3,061.86 $2,984.33 $3,377.25 $4,252.13 Total Cost $1,881,123.72 $1,787,218.67 $1,693,375.58 $1,599,876.19 Change the value in yellow highlighted cells to observe how changing the parameter affects the calculated values. Blue highlighted cells contain formulas that calcuate certain values based upon the provided parameters. Do not alter Safety Stock Safety Stock With Normally Distributed Demand and Constant Lead Time Average daily demand Standard deviation of demand Lead time in days Desired level of service Number of standard deviations corresponding to desired service level Reorder point Safety Stock With Constant Demand and Normally Distributed Lead Time Daily demand Average lead time in days Standard deviation of lead time Desired level of service Number of standard deviations corresponding to desired service level Reorder point Safety Stock With Variable Demand and Lead Time Average daily demand Standard deviation of demand Average lead time in days Standard deviation of lead time Desired level of service Number of standard deviations corresponding to desired service level Reorder point ging the parameter affects the calculated values. es based upon the provided parameters. Do not alter the content of these cells. onstant Lead Time d sd L Z (d*L)+(Z*(sd*SQRT(L))) 40 10 7 97.00% 1.8808 329.76 ributed Lead Time d L sL Z (d*L)+(Z*(d*sL)) d sd L sL Z (d*L)+(Z*SQRT((L*(sd^2))+((d^2)*(sL^2)))) 40 7 2 97.00% 1.8808 430.46 40 10 7 2 97.00% 1.8808 438.48