Chemical Engineering July 2023 - 34

Engineering Practice
Optimization of Inventory Management with
Process Integration Using Spreadsheet
A recently developed optimization technique called the automated targeting model is
demonstrated for its application on inventory management
Hoong Teng Loh and
Dominic C. Y. Foo
University of Nottingham Malaysia
S
upply chain management
that involves raw material
sourcing, product manufacturing
and delivery has
often been viewed as one of the
critical factors for successful business,
especially for the manufacturing
(a)
(b)
industry.
An important aspect
of supply chain management is inventory
management, where stock
level of raw material or product (or
both) could be optimized to ensure
maximum profit or minimum cost or
losses. In the post-pandemic era,
artificial intelligence (AI), automation
and digitalization are believed to be
the main trends in shaping the future
of supply chain management [1].
Over the past two decades, several
important toolboxes based on
process integration principles have
been developed for supply chain
management. In particular, the supply
chain composite curve [2] is a
useful graphical tool that may be
used to set a minimum production
rate for aggregate planning in stocklevel
strategy (Figure 1a). The grand
composite curve (Figure 1b), on the
other hand, allows users to view the
inventory level for a production supply
chain [3]. These graphical tools
have the advantages of visual display,
which facilitates good discussion and
decision making among production
personnel. However, they suffer with
limitations, such as inaccuracy and
tediousness. To overcome the above
issues, algebraic targeting tools were
also developed [4]. Furthermore, the
recently developed automated targeting
model (ATM) is another useful
tool to incorporate financial consideration
in optimizing supply chain [5]
and inventory planning [6, 7].
In this article, the use of ATM is
demonstrated for the optimization
34
FIGURE 1. Shown here are the supply chain composite curves (a) and the grand composite curve (b)
of inventory level of maintenance
spare parts for a process plant, for a
known maintenance schedule.
Automated targeting model
The basic framework of ATM is shown
in Table 1. As shown, the time period,
t, is listed in Column 1, from earlier to
later time period (may be in weeks,
months or even years).
In Columns
2 and 3, show the demand and supply
for each period. Note that the demand
values are given, while supply
values are to be determined as part
of the ATM. Net inventory at the end
of t period, It, is given in Column 4. In
order to maintain a minimum inventory
(Imin), the actual inventory is to be
calculated for each period t in the last
column, given as the summation of
net and minimum inventory (It + Imin).
For more details,
see Ref. 4.
The main advantage
of ATM is
that, its basic form
is a linear model,
which enable it to
be used for inventory
planning even
using a spreadsheet
software,
such
as Microsoft
Time Period,
t (month/
week)
1
2
...
t
Excel (with the use of Solver add-in).
Equations (1-3) are the basic formulation
of the ATM. The net inventory of
each time period t(It) is given as the
difference between demand, Dt, and
supply, St, of that period, along with
the inventory by from previous period,
(It - 1), as illustrated by Equation (1).
The inventory and supply of all time
periods should take non-negative values,
shown in Equations (2) and (3).
It = I(t-1) + St - Dt, for all t
It ≥ 0, for all t
St ≥ 0, for all t
(1)
(2)
(3)
The objective function of the
model is to minimize the total purchase
price, given as in Equation (4).
TABLE 1: BASIC STRUCTURE OF AN ATM
Demand, Dt
(units)
D1
D2
...
Dt
Supply,
St (units)
S1
S1
...
St
Net Inventory,
It
(units)
I0
I1
I2
...
It
CHEMICAL ENGINEERING WWW.CHEMENGONLINE.COM
Actual
Inventory,
(units)
I0 + Imin
I1 + Imin
I2 + Imin
...
It + Imin
JULY 2023
http://WWW.CHEMENGONLINE.COM
Chemical Engineering July 2023

Table of Contents for the Digital Edition of Chemical Engineering July 2023
