Transcript Document

DECISION MODELING WITH
MICROSOFT EXCEL
Chapter 16
Inventory Model
Part 1
Copyright 2001
Prentice Hall Publishers and
Ardith E. Baker
The EOQ Inventory Model
Inventories are defined as idle goods in storage,
waiting to be used. For example, inventories of
Raw materials
In-process materials
Finished goods
Cash
Individuals
Inventories are held for many reasons:
1. Inventories smooth out the time gap
between supply and demand.
2. The possibility of holding inventory often
contributes to lower production costs.
3. Inventories provide a way of storing labor.
4. Inventory is a way of providing quick
customer service at the time an item is
needed.
The costs associated with inventory activity are
holding costs, ordering costs, and stockout costs.
STECO Example:
STECO stocks a short length optimal fiber
network cable (NC) used to connect Internet
routers to local area network equipment.
Consider the following costs:
Holding Costs:
3000 NCs x $8 per unit cost = $24,000
Note that this money is tied up in inventory. By
holding inventory, STECO forgoes the opportunity
to make other investments (opportunity cost).
Other components of holding costs include
breakage, pilferage, insurance, warehousing, and
special handling requirements.
As inventory increases, holding cost increases.
Ordering Costs: Every time an order is placed
(independent of the quantity ordered), an
ordering cost is incurred. This cost is related to
the amount of personnel time required for
accounting, invoicing, order checking, etc. when
an order is placed.
Stockout Costs: When a company runs out of
inventory, a stockout occurs (i.e., orders arrive
after inventory has been depleted. There are two
ways to treat such orders:
1. Backlogging (save up the orders and fill
them later after the inventory comes in).
In addition to backlogging cost, stockout
cost includes the lost profit from late
delivery of stock.
2. No Backlogging
In this case, a penalty cost (the per unit
cost of unsatisfied demand) occurs.
Stockout cost includes the lost profit from
not making the sale.
Stockout cost can also include the cost of losing
the customer, loss of goodwill, and of
establishing a poor record of service.
So, to avoid stockouts (and stockout costs), have
enough inventory on hand. However, carrying
inventory implies a holding cost. This cost can be
reduced by ordering more often. However, this
increases the ordering cost. It is important to
balance these three costs against each other.
For every type of inventory ordered, there are
two key questions that must be answered:
1. When should an order be placed?
2. How much should be ordered?
Some considerations are:
The extent to which future demand is known.
The cost of stocking out and management’s
policy (backlogging or not).
The inventory holding and ordering costs.
The possibility of long lead times (the time
from when an order is placed to when it is
received).
The possibility of quantity discounts.
STECO Wholesaling: The Current Policy
The monthly demand (orders received) is:
MONTH
DEMAND (UNITS)
January
February
March
April
May
June
July
August
September
October
November
December
Total Annual Demand:
Average Monthly Demand:
5,300
5,100
4,800
4,700
5,000
5,200
5,300
4,900
4,800
5,000
4,800
5,100
60,000
5,000
Over a period of several years, the demand has
remained at a steady rate of about 5000 NCs per
month.
STECO’s policy last year was to add 5000 NCs to
inventory each month. Since demand is expected
to be the same, this is also the current policy.
Based on this policy, assume
Shipments always arrive on the first day of
each month.
Demand is known and constant (at a rate of
5000 units per month).
No backlogging (i.e., no stockouts).
Plot of inventory on hand at any time:
5,000
Average
Inventory
2,500
0
1
2
3
4
5
6
Time
Given the previous assumptions, the cost of
operating the system above depends only on how
much new stock is ordered and on the holding
and ordering costs.
Since demand is 5000 NCs and we order 5000
every month, the average inventory is 5000/2 or
2,500 NCs.
The effect of ordering 10,000 NCs every other
month is shown below.
10,000
Average
Inventory
5,000
0
1
2
3
4
5
6
Time
The demand remains constant at 5000 NCs per
month. Average inventory is doubled but the
annual number of orders is cut in half.
A policy of increasing the order quantity increases
the holding costs and decreases the annual
ordering cost.
Developing the EOQ Model:
The Economic Order Quantity (EOQ) model is a
simple model which attempts to balance the cost
of placing orders with the cost of holding
inventory.
The EOQ model assumes
No stockouts are allowed.
There is a constant rate of demand.
The relevant costs are ordering and holding
costs.
The EOQ finds the optimal order quantity (the
quantity that minimizes the total cost).
Total cost is based on:
Ordering cost = Co
STECO estimates the cost of placing an order
for NCs, regardless of the number of units
ordered, to be
$20 (clerical & purchasing agent labor)
+ $ 5 (material & telecommunications costs)
$25
Holding cost = Ch
STECO estimates the cost of holding an NC in
inventory for one year is
20% (opportunity cost)
+ 4% (variable cost)
24% of its purchase price. Since each NC
costs $8, the holding cost = $8 x .24 = $1.92
First derive an expression for the annual holding
and ordering cost (AHO) as a function of the
order quantity.
The Annual Ordering Cost:
Annual Ordering Cost = Co x (no. of orders/yr)
Total Demand = 60,000
Order Quantity = 5,000
Therefore, STECO will place
60,000/5,000 = 12 orders per year
The general formula is N = D/Q
where N = no. orders/yr
D = annual demand
Q = order quantity
Annual Ordering Cost = CoN = Co(D/Q)
The Annual Holding Cost:
1. Annual holding cost is equal to Ch times
the average inventory
2. The average inventory is equal to ½ of the
maximum inventory when demand occurs
at a constant rate.
Annual Holding Cost = Ch(Q/2)
Therefore, add the two expressions to get:
AHO(Q) = Co (D/Q) + Ch(Q/2)
Now, substituting the values, we get
AHO(Q) =$25(60,000/Q) + 1.92(Q/2)
= (1,500,000/Q) + 0.96Q
When Q = 5,000 it is seen that
AHO(5,000) = $300 + $4,800 = $5,100
This graph shows the optimal order quantity that
minimizes AHO(Q):
Dollars
6,000
AHO(Q) = Annual holding and ordering cost
5,000
4,000
Annual holding cost = 0.96Q
3,000
2,000
1,000
Annual ordering cost = (1,500,000)/Q
0
1,000
2,000
3,000
4,000
5,000
Here is the spreadsheet model for this example:
=C3*C9
=C4*C5/C13
=C10*C13/2
This model is
=C9*C5
nonlinear because
of the Q in the
denominator of
the ordering cost
formula.
Here are the solver parameters:
This constraint
(specifying that
there be at least
one order per year)
is included to
prevent Solver
from testing an
unreasonable
candidate.
The EOQ Formula: Q*
Q* is the optimal order quantity or economic
order quantity expressed in terms of Co, Ch and D.
To develop a mathematical expression for Q*,
first set annual holding cost equal to annual
ordering cost.
Ch(Q*/2) = Co (D/Q*)
Solving for Q* we get
(Q*)2 = 2CoD/Ch
Q* = 2CoD/Ch
Ch can be estimated with i [a percentage of the
purchase price (P)] and P, this equation can be
rewritten as:
Q* = 2C D/iP
o
Remember, P = $8, i (the fraction of P that is
used to calculate Ch) is 0.24 and D is 60,000.
Now, solve for Q*
Q* = 2(60,000)25/1.92
Q* = 1250
Now that we have Q*, the optimal order quantity,
find the AHO(Q*):
AHO(Q*) =AHO(1250)
= (1,500,000)/1250 + (0.96)(1250)
= $1200 + $1200
= $2400
Sensitivity Analysis
Now we ask: How sensitive are the results of the
model to the assumptions and the data?
STECO should be concerned about how sensitive
the optimal order quantity and the optimal
annual cost are to the data.
If STECO errs in estimating the parameters Co and
Ch, how much effect will that error have on the
difference between the calculated Q* and AHO*
and the true Q* and AHO*.
If the results are highly sensitive to the values of
the estimates, should the optimal policy for the
model be implemented?
To see how the EOQ results will vary with
changes in the holding and ordering cost
estimates, consider four cases in which the true
parameters are different from the values selected
by STECO:
In STECO’s case, the EOQ model is insensitive to
approximately 10% variations in cost estimates.
Inventory with Quantity
Discounts Model
The following examples are variations in the
“classic” EOQ model.
Quantity Discounts
and STECO’s Overall Optimum:
Previously, the cost of purchasing the product
was assumed to be a constant, independent of Q.
However, STECO’s NC supplier will offer a
quantity discount as an incentive for more
business.
The supplier has agreed to offer a $0.10 discount
on every NC purchased if STECO orders in lots of
at least 5000 items.
Higher order quantities will reduce the number of
orders placed and increase the average inventory
level, resulting in a higher annual ordering cost.
The question is, will the discount be
advantageous to STECO? To answer this, first
develop an annual cost curve and then find the
order quantity that minimizes it.
Let
ATC(Q) be the annual total cost
AHO(Q) be the sum of the annual holding
and ordering cost
APC be the annual purchase cost
ATC(Q) = AHO(Q) + APC
{
AHO(Q) = Co(D/Q) + iP(Q/2)
APC = PD
Ch
ATC(Q) = Co(D/Q) + iP(Q/2) + PD
Let P = $8.00 per unit, the Regular price
equation is:
ATC(Q) = 25(60,000)/Q + .24(8.00)(Q/2) + 8.00(60,000)
Let P = $7.90 per unit, the discount price
equation is:
ATC(Q) = 25(60,000)/Q + .24(7.90)(Q/2) + 7.90(60,000)
The general shapes of the Regular and Discount
curves are shown below:
ATC(Q)
Regular Price
Note that the
discount
curve lies
below the
regular cost
curve.
Discount Price
Q*R Q*D
Q
Also note that the value of Q, say Q*D, that
minimizes the discount price ATC(Q) is larger
than the value of Q, say Q*R, that minimizes the
regular price = ATC(Q).
Now, assuming that the discount price holds only
if STECO orders at least B items at a time. Two
situations could arise:
The dark line portions indicate the actual cost
function that STECO faces.
ATC(Q)
ATC(Q)
Regular Price
Regular Price
Discount
Price
Discount
Price
Q*R Q*D
B
Q
Q*R Q*D
Q
B
If B < Q*D, STECO will achieve the minimum cost
by ordering Q*D.
If B > Q*D, the optimal decision is not obvious.
The general rule is:
If B < Q*D,
If B > Q*D,
order
order
Q*D
Q*R if regular price <
discount price
ATC(Q*R) < ATC(B)
B
if not
Here is the Excel version of the quantity discount
inventory model:
Solver optimizes a mixed integer nonlinear program
(MINLP) to evaluate the two EOQ functions, one with
and without the discount.
Here are the formulas:
Inventory and Production,
A Lot Size Model
STECO has an extensive and modern heattreatment fibre cable “jacketing” facility that it
uses to produce a number of specialty cable items
that it then holds in inventory.
Two important characteristics of this facility:
1. There is a large setup cost associated with
producing each cable product
2. Once the setup is complete, production is
at a steady and known rate.
Setup cost (analogous to ordering cost) is
incurred because it is necessary to change the
plastic fibre molds and the operating temperature
in the heat-treatment facility to meet the
specifications set forth by the cable standards
specification.
Each cable must have connectors attached and
undergo testing for frequency response.
An order quantity of network cables arrives from
production into inventory steadily over a period
of several days.
A modification in the EOQ formula is required.
Consider a product in which
d = no. of units demanded each day
p = no. of units produced each day during a
production run
Co = setup cost that is independent of the
quantity produced
ch = cost per day of holding inventory
Note that if p < d, demand is greater than
STECO’s ability to produce.
Below is a plot of inventory on hand for the
Production Lot Size model:
During a production
Inventory
On hand
Rate of
decrease
Rate of
increase
d
p-d
Items are
Q
removed
p
from
inventory at
a rate of d
items per day.
Q
d
Production
run
Each
production
run is Q/p
days long.
run, items are added
to inventory at a
rate of p units per
day and removed at
the rate of d units
per day (net effect
of p-d units per day).
Time (Days)
Cycle time
Each cycle time is
Q/d days long.
The formulas are:
Max. inventory = (p-d)(Q/p)
Avg. inventory = ½ (p-d)(Q/p)
= Q/2(1-(p/d))
Holding Cost per Day = ch(Q/2) (1-(p/d))
Setup Cost per Day = Co/(Q/d) = Co(d/Q)
DHS(Q) = Co(d/Q) + ch(Q/2) (1-(p/d))
The value of Q that minimizes DHS(Q) for the
production lot size model is:
Q* =
2Cod
ch(1-(d/p))
Substituting Q* for Q in the expression for DHS(Q)
gives us an expression for the minimum daily
holding and setup cost:
DHS(Q*) =
d
2Codch 1- 1p
Note that this expression does not depend on Q.
STECO must first estimate the various parameters
and then obtain Q*. For illustration, let
Average demand is 200 NCs per day
Setup cost is $100
Production rate is 400 NCs per day
Production cost is $1
Annual interest rate is 0.24
Number of working days per year is 240
Holding cost per day is
($1)(0.24)/240 = $0.001
The optimal production lot size for this product is:
Q* =
2(200)(100)
0.001(1-(200/400))
= 8944
The minimum daily holding and setup cost is:
= 2(200)(100)(0.001(1-(200/400)) = $4.47
A production run of this size yields a supply of
NCs large enough to satisfy demand for
8944 = 44.72 days
200
Here is the Excel model for this problem:
Here are the spreadsheet formulas: