EOQ CSV template (bulk SKU run)
Run Wilson EOQ across an arbitrary SKU file. Input columns: SKU, D (annual demand), S (order cost), C (unit cost), i (carrying cost percent). Output: Q*, annual holding, ordering and total, plus three sensitivity columns (Q* at i minus 3 pp / i / i plus 3 pp).
Input columns
- SKU - your part number. Free text.
- D - annual demand in units. Use rolling 12 months of stable history.
- S - cost per order, dollars. Defaults to $100 if blank.
- C - unit cost, dollars.
- i - carrying cost percent. Defaults to 25 if blank.
Output columns
- Q_star - Wilson optimal order quantity, rounded to the nearest unit.
- annual_holding - (Q*/2)*H, dollars per year.
- annual_ordering - (D/Q*)*S, dollars per year.
- annual_total - sum of holding and ordering, excludes purchase cost.
- cycle_days - 365 / (D / Q*).
- Q_minus3pp, Q_plus3pp - sensitivity Q* at i +/- 3 percentage points.
How to read sensitivity columns
For each SKU, the Q_minus3pp and Q_plus3pp columns tell you how much Q* would change under a plausible mis-estimate of carrying cost. If both sit within +/-15 percent of Q_star, the answer is robust. If the spread is wider, the SKU is sensitive to H and deserves a closer look at the holding-cost components[1].
Pushing back into QuickBooks / NetSuite
QuickBooks Online: Bulk Item Update with Q_star pasted into the Reorder Point field (or, depending on your setup, the Preferred Order Quantity field)[2]. NetSuite: CSV import to the Item Reorder Point field. Cin7 and Fishbowl have equivalent reorder-quantity fields per item.
Sample template (paste into a sheet)
SKU,D,S,C,i SKU-001,12000,85,40,0.25 SKU-002,3600,120,60,0.30 SKU-003,800,250,480,0.22 Then in row 2 column F: =ROUND(SQRT(2*B2*C2/(D2*E2)),0) # Q_star =B2*C2/F2 # annual_ordering =(F2/2)*D2*E2 # annual_holding =G2+H2 # annual_total