Excel FoundationsModule 46: Supply Chain, Logistics & Operations Research in Excel

46.1 Supply Chain Optimization: Economic Order Quantity (EOQ), Safety Stock & Reorder Points

Calculate mathematically optimal order quantities (EOQ), determine safety stock buffers under lead-time variance, and establish dynamic reorder points.

Free preview

You are previewing a full lesson.

This lesson is a free preview. No account is needed to read it.

46.1 Supply Chain Optimization: Economic Order Quantity (EOQ), Safety Stock & Reorder Points

The video for this lesson has not been published yet. The written lesson below is the full material for this session.

Instant AutoSumAutomates SUM formula insertion over highlighted rows and columns.

⌥Alt=

Toggle FiltersTurns on header dropdown filters for data sorting and filtering.

⌃Ctrl⇧ShiftL

Convert to Excel TableFormats range as a self-expanding, structured Excel table.

⌃CtrlT

Format Cells DialogOpens complete number, border, fill, and font formatting controls.

⌃Ctrl1

Lock Reference ($)Cycles through absolute and mixed reference locks in formulas.

F4

Lesson notes & syllabus guide

46.1 Supply Chain Optimization: Economic Order Quantity (EOQ), Safety Stock & Reorder Points

Supply Chain management me do opposing financial forces hoti hain: Ordering Cost (bar-bar order place karne ka administrative aur shipping kharcha) aur Holding Cost (warehouse me inventory store karne aur capital lock hone ka cost).

1. The Economic Order Quantity (EOQ) Formula

$$\text{EOQ} = \sqrt{\frac{2 \times D \times S}{H}}$$

$D$: Annual Demand in units (e.g. 10,000 units/year)
$S$: Fixed Order Cost per order (e.g. ₹500/order)
$H$: Annual Holding Cost per unit (e.g. ₹10/unit/year)

Excel Formula:

= SQRT((2 * Annual_Demand * Order_Cost) / Holding_Cost)

Example: $D = 10,000$, $S = 500$, $H = 10$ $\implies \text{EOQ} = \sqrt{(2 \times 10000 \times 500) / 10} = \mathbf{1,000\text{ units}}$.
Total Orders per Year = $10,000 / 1,000 = 10\text{ orders}$.

2. Safety Stock Buffer Under Lead-Time Uncertainty

Supplier dispatch karne me delay kar sakta hai. Stockout prevent karne ke liye Safety Stock (SS) calculate kiya jata hai:

$$\text{Safety Stock} = Z \times \sigma_{\text{LT}} \times \sqrt{L}$$

$Z$: Service Level Factor (e.g. 95% service level $\implies Z = 1.65$, 99% $\implies Z = 2.33$)
$\sigma_{\text{LT}}$: Standard deviation of daily demand
$L$: Average Lead Time in days

Excel Formula:

= NORM.S.INV(Service_Level_Pct) * STDEV.S(Daily_Demand_Range) * SQRT(Lead_Time_Days)

3. Dynamic Reorder Point (ROP)

$$\text{Reorder Point (ROP)} = (\text{Average Daily Demand} \times \text{Lead Time}) + \text{Safety Stock}$$ Jab warehouse me stock ROP level par girta hai, procurement team instantly nayi purchase order trigger karti hai!

Interactive Spreadsheet Practice Sandbox

Practice live Excel formulas directly in your browser. Real calculation verified by HyperFormula.

Formula practice lab

Exercise

Exercise 1: Basic SUM Addition

Calculate total revenue in cell D6 by summing all items in column D (cells D2 to D5).

Every formula you enter is evaluated by a real spreadsheet engine in your browser — results are not pre-computed.

Exercise 1: Basic SUM Addition practice sheet
#	A	B	C	D
1	Product	Quantity	Unit Price	Total
2	Laptop	5	45000	225000
3	Mouse	20	500	10000
4	Keyboard	15	1200	18000
5	Monitor	8	12500	100000
6	Grand Total

Chapter 46 Knowledge Assessment: Supply Chain & Operations Research

Pass mark 70%

Test your understanding of EOQ inventory math, transportation linear programming via Solver, and ABC classification.

This quiz has 5 questions. Sign in to answer them — results are saved to your account.

Practical Project 46: Multi-Echelon Supply Chain & Transportation Network Optimization Model

Community discussion

Lesson Q&A & Peer Discussions

Stuck on a formula or edge case? Ask questions, upvote solutions, and earn community XP.

No questions yet on this lesson

Be the first to ask a doubt or start a discussion with peers and mentors!

Excel FoundationsModule 46: Supply Chain, Logistics & Operations Research in Excel

46.1 Supply Chain Optimization: Economic Order Quantity (EOQ), Safety Stock & Reorder Points

Calculate mathematically optimal order quantities (EOQ), determine safety stock buffers under lead-time variance, and establish dynamic reorder points.

Free preview

You are previewing a full lesson.

This lesson is a free preview. No account is needed to read it.

46.1 Supply Chain Optimization: Economic Order Quantity (EOQ), Safety Stock & Reorder Points

The video for this lesson has not been published yet. The written lesson below is the full material for this session.

Instant AutoSumAutomates SUM formula insertion over highlighted rows and columns.

⌥Alt=

Toggle FiltersTurns on header dropdown filters for data sorting and filtering.

⌃Ctrl⇧ShiftL

Convert to Excel TableFormats range as a self-expanding, structured Excel table.

⌃CtrlT

Format Cells DialogOpens complete number, border, fill, and font formatting controls.

⌃Ctrl1

Lock Reference ($)Cycles through absolute and mixed reference locks in formulas.

F4

Lesson notes & syllabus guide

46.1 Supply Chain Optimization: Economic Order Quantity (EOQ), Safety Stock & Reorder Points

1. The Economic Order Quantity (EOQ) Formula

$$\text{EOQ} = \sqrt{\frac{2 \times D \times S}{H}}$$

$D$: Annual Demand in units (e.g. 10,000 units/year)
$S$: Fixed Order Cost per order (e.g. ₹500/order)
$H$: Annual Holding Cost per unit (e.g. ₹10/unit/year)

Excel Formula:

= SQRT((2 * Annual_Demand * Order_Cost) / Holding_Cost)

Example: $D = 10,000$, $S = 500$, $H = 10$ $\implies \text{EOQ} = \sqrt{(2 \times 10000 \times 500) / 10} = \mathbf{1,000\text{ units}}$.
Total Orders per Year = $10,000 / 1,000 = 10\text{ orders}$.

2. Safety Stock Buffer Under Lead-Time Uncertainty

Supplier dispatch karne me delay kar sakta hai. Stockout prevent karne ke liye Safety Stock (SS) calculate kiya jata hai:

$$\text{Safety Stock} = Z \times \sigma_{\text{LT}} \times \sqrt{L}$$

$Z$: Service Level Factor (e.g. 95% service level $\implies Z = 1.65$, 99% $\implies Z = 2.33$)
$\sigma_{\text{LT}}$: Standard deviation of daily demand
$L$: Average Lead Time in days

Excel Formula:

= NORM.S.INV(Service_Level_Pct) * STDEV.S(Daily_Demand_Range) * SQRT(Lead_Time_Days)

3. Dynamic Reorder Point (ROP)

Interactive Spreadsheet Practice Sandbox

Practice live Excel formulas directly in your browser. Real calculation verified by HyperFormula.

Formula practice lab

Exercise

Exercise 1: Basic SUM Addition

Calculate total revenue in cell D6 by summing all items in column D (cells D2 to D5).

Every formula you enter is evaluated by a real spreadsheet engine in your browser — results are not pre-computed.

Exercise 1: Basic SUM Addition practice sheet
#	A	B	C	D
1	Product	Quantity	Unit Price	Total
2	Laptop	5	45000	225000
3	Mouse	20	500	10000
4	Keyboard	15	1200	18000
5	Monitor	8	12500	100000
6	Grand Total

Chapter 46 Knowledge Assessment: Supply Chain & Operations Research

Pass mark 70%

Test your understanding of EOQ inventory math, transportation linear programming via Solver, and ABC classification.

This quiz has 5 questions. Sign in to answer them — results are saved to your account.

Practical Project 46: Multi-Echelon Supply Chain & Transportation Network Optimization Model

Community discussion

Lesson Q&A & Peer Discussions

Stuck on a formula or edge case? Ask questions, upvote solutions, and earn community XP.

No questions yet on this lesson

Be the first to ask a doubt or start a discussion with peers and mentors!