

Materials
Requirement Planning 

MRP
Formulas 

The
yellow areas of the MRP worksheets contain Excel formulas.
These formulas are constructed by the MRP addin. Although
the formulas can be viewed directly on the worksheet through
the Formula toolbar, it is hard to interpret them
in mathematical form. This page provides a mathematical
basis to the computations. 
The Gross Requirements 

The computations
for a given part are driven by the Gross Requirements for
the part. When a part is represented in the master production
schedule, the gross requirements for the part are the values
entered as the independent demand for that part. This is illustrated
in the figure below for P400. The Link Parts command
places formulas in the gross requirements row of P400, row 10
in the figure, that reference the cells giving the independent
demands for P400. For example the cells in row 10 all have the
formula:
=PQR_MPS_Prod_1
The reference is to the independent demand in
row 5. 


Some parts are mentioned in the
Bills of Material (BOM) of other parts. In this case the formulas
that define the gross requirements are more complicated because
they depend on the BOM entries of one or more parts. A given
part may have independent demand and also be in the BOM's
of one or more other parts. Part PP is included as the BOM
of P400. The formula in the yellow cells of row 40 is
=INDEX(PQR_BOM1,1,2)*PQR_Part1_Rel
Before the multiplication symbol, *, the formula
references the BOM of P400. After the multiplication symbol,
the formula references the planned order releases of P400.
The demand for a component depends on the planned order releases
of the part. In general the formula may references several
parts and be quite complex. 

Formulas for the Parts 

Given
the gross requirements, scheduled receipts, and the initial
on hand inventory, all the other entries on a part form are
computed with mathematical formulas. We adopt the following
notation for the part quantities. For this section we are assuming
a fixed lot size.
To illustrate we use the P400 part with
the fixed order quantity (FOQ) lot method. Whenever the inventory
would otherwise become negative, this policy requires the receipt
of a fixed lot size. In the example, the lot size is fixed
at 300. The initial on hand inventory is 130. A delivery of
a lot of 300 is scheduled for time 2. The order for this lot
was placed prior to the beginning of this time horizon. Note
that the lead time is 2. The lot size, on hand inventory, scheduled
receipts, and lead time are all entered on white cells indicating
that these entries are data. 


Indices on the quantities
range from 1 to T, the time horizon. The time horizon
is 12 for the example. In some cases the index of 0 indicates
the initial value. 

Rows
of the Part Form 

Quantity 
Explanation 
d( t) 
Gross Requirements 
For this discussion of the part
formulas, the gross requirements are given. For the example,
the gross requirements are specified as data in row 5. 
e(t) 
Scheduled Receipts 
The numbers in this row are quantities
not scheduled on this form. The numbers in this row are
data. 
h(t) 
Projected onHand 
The initial value is a given,
but the remaining values are computed with the formula:

n(t) 
Net Requirements 
The requirements that cannot
be met with projected onhand or scheduled
receipts are the net requirements. These must be satisfied
from production during the time horizon.

x(t) 
Planned Order Receipts 
These are computed based on
the net requirements and the lot sizing method. The
example uses a fixed order quantity of 300. If
the inventory at time t  1, plus the scheduled
receipts at time t, less the demand at time
t, is negative, an order is planned to be
received at time t. The order is the specified
lot size unless the lot size cannot meet the demand.
If the fixed lot size cannot satisfy the net requirements,
the order is the net requirements less amount on hand.
This order size assures that the inventory will never
go below zero.

y(t) 
Planned Order Releases 
If an order is to be received
at some time, the order must be placed L periods
earlier. This is accomplished by setting the planned
order release equal to the planned order receipt L
periods later. The top condition requires that
the time of receipt must fall within
the time horizon.

z(t) 
Inventory on Hand 
The inventory onhand is the
previous inventory, plus planned receipts, plus scheduled
receipts, minus demand.

w(t) 
Work in Process 
This workinprocess is
the number of units released to production,
but not yet received.



The
column at the right of each part data computes averages
over the time horizon. 
Quantity 
Explanation 
Demand Rate 
The average demand per period over
the time horizon. 
Setup Cost 
The cost for a production run for
a manufactured part or an order for a purchased part. This
is data. 
Holding Cost 
This is the cost of holding one unit
for one period. It is computed by multiplying the Unit
Cost by the Interest Rate. 
Average WIP and OH 
This is the average over the time
horizon of the number of units in production and in inventory. 
Average Setup 
This the average number of setups
per period. 
Inventory Cost 
This is a measure of effectiveness
for the scheduling policy. It adds the cost of holding
WIP and inventory and the cost of setups. An optimum policy
would minimize this value. 
EOQ 
Economic Order Quantity. This is
the optimum lot size based on the averages over the time
horizon. It is computed with the standard EOQ formulas. 
EOP 
Economic Order Period. This is the
optimum period between orders assuming demand is continuous
over the time horizon and the EOQ is used. 


The
MRP worksheet uses only native Excel formulas. The addin constructs
the worksheet and inserts all the required formulas. 


