Operations
Economic Production Quantity (EPQ) Finite-Rate Lot-Size Formula
Calculate an idealized finite-rate production lot, peak inventory and annual setup-plus-holding cost, deriving daily demand from annual demand on a consistent operating-day basis.
Use consistent demand and production periods
B2 is annual demand D, B3 setup cost per run S, B4 annual holding cost per unit H, B6 production rate p per operating day and B7 operating days per year. B5 derives daily demand d=D/days; do not independently enter a contradictory daily demand.
The initial12000 units over240 operating days gives d=50 units/day. Production at200 units/day exceeds demand. The model assumes constant known demand and production rates, continuous consumption on the same time basis, repeatable setup cost, no shortages and no safety stock.
Find the continuous optimum
E2 computes Q*=sqrt(2DS/[H(1−d/p)]), initially1000 units. Every model input must be numeric and positive, and p must exceed d. Equality gives no finite optimum under this formula.
This is a continuous long-run optimum, not an exact factory schedule. Integer batch sizes, shared machines, downtime, lot-size restrictions and yield losses require additional constraints. Keep the raw optimum for the downstream calculations.
=IF(COUNT(B2:B4,B6:B7)<>5,"Enter five numeric model inputs",IF(OR(B2<=0,B3<=0,B4<=0,B6<=0,B7<=0),"Require positive model inputs",IF(B6<=B2/B7,"Production rate must exceed demand rate",SQRT(2*B2*B3/(B4*(1-B2/(B7*B6)))))))Show inventory and both annual cost components
E3 is peak inventory Q*(1−d/p), initially750 units. E4 is annual setup cost DS/Q, initially3000 currency units/year; E5 is holding cost H×peak/2, also3000. E6 adds them to6000.
The two costs are equal at the unconstrained optimum. These are relevant setup and holding costs only, excluding production, purchase and other costs. Consistent currency units are required; no exchange-rate conversion is performed.
=IF(ISNUMBER(E2),E4+E5,E2)Compare the instantaneous-replenishment limit
E7 independently computes EOQ=sqrt(2DS/H), approximately866.025404 units. As production rate grows while demand stays fixed, EPQ approaches this limit from above.
Do not set daily demand to zero while retaining positive annual demand to simulate this limit. Instead increase p, or compare E7 directly. At finite p the inventory accumulates gradually and peak stock is less than the run quantity.
=IF(COUNT(B2:B4)<>3,"Enter numeric demand,setup,holding cost",IF(OR(B2<=0,B3<=0,B4<=0),"Require positive demand and costs",SQRT(2*B2*B3/B4)))Experiment with the workbook
Double setup cost and the optimum increases by sqrt(2). Double holding cost and the optimum decreases by sqrt(2). Try p close to d: the required run quantity grows, revealing the limitations of the unconstrained steady-cycle model.
Change operating days to see daily demand recalculate. This changes the implied production-year capacity too because p uses the same operating-day basis. The model does not assume an arbitrary calendar-day conversion.
=IF(ISNUMBER(E2),E2*(1-B2/(B7*B6)),E2)Try the example
Use the original values or change them to test your understanding.