Content uploaded by Abhulimhen Okouzi
Author content
All content in this area was uploaded by Abhulimhen Okouzi on Dec 23, 2016
Content may be subject to copyright.
INTERNATIONAL JOURNAL OF MULTIDISCIPLINARY SCIENCES AND ENGINEERING, VOL. 5, NO. 1, JANUARY 2014
[ISSN: 2045-7057] www.ijmse.org 11
Design and Development of Materials Requirements
Planning Simulation Model Using Visual Basic
Programming Language
1,*Okolie Paul Chukwulozie, 2Okouzi Solomon Abhulimhen, 1Chukwuneke Jeremiah Lekwuwa, and 3Sinebe Jude Ebieladoh
1Department of Mechanical Engineering, Nnamdi Azikiwe University, Awka, Nigeria
2Department of Production Engineering, University of Benin, Benin City, Edo State, Nigeria
3Special Adviser to the Governor on Local Government Projects Monitoring, Delta State, Nigeria
*pc.okolie@unizik.edu.ng
Abstract– This paper focuses on developing software for the
simulation of Materials Requirements Planning (MRP), which is
one of the inventory models. Such a simulation model will
enhance effective inventory decisions and policy formulation for
any profit oriented organization. It is in the light of this that an
overview of inventory models and software design and
development principles for simulation models became
imperative, with the aim of adopting the best design and
development principles that could solve the problem at hand.
Visual Basic programming language was employed for the
implementation of the materials requirements planning model
because of the nature of the problems MRP solves, the object-
oriented nature of the programming language and also for its
simplicity and time saves benefits for the users. To implement
the design and development of the Materials Requirements
Planning Simulation Model Using Visual Basic Programming
Language, the objects (components) of the software were
designed and codes were written for the events to occur at the
controls and objects. The application was tested and debugged.
The validation was ensured by using an already existing problem
of materials requirements planning. Guaranteed that the model
simulated effectively the test problem, and the results printed out
successfully, a standalone executable file was compiled with the
window name “Materials Requirements Planning (MRP)
Wizard”.
Keywords– Development, Simulation Model, Materials
Requirements Planning (MRP) and Policy
I. INTRODUCTION
ecision making is the whole process of the problem
through generation and evaluation of alternatives and
finally, to the choice itself. It could also expand to
include implementation of the decision and control of the
decision process to determine when additional decisions are
required. In this case, decision making becomes practically
synonymous with managing, Roger (1981). Hence, decision
making and policy formulation are integral functions of
management in industries and organizations. The credibility
and reliability of such decisions depend to a very large extent
on the means and tools for arriving at such decisions. In our
modern world, taking decisions have become more complex
as a result of complexity and uncertainties associated with the
factors considered. Therefore, there is the need to develop
system that can aid and enhance effective decision making,
thus the computer-based system. This class of systems
depends on the use of computer for managing business and
industrial applications; they depend upon computers for
performing their objectives. The use of computer has
dramatically changed the field of operations management
since computers were introduced into business in the 1950s.
Most manufacturing operations including very little once
now employ computers in among other operations inventory
management and production schedule. In addition, computers
are rapidly making inroads on the automation of office work,
and they are used in virtually all types of service operations.
Today, the effective use of computer has become an essential
part of the operation management field.
A computer based business or industrial system involves
six interdependent elements. These are hardware (machines),
software, people (programmers, managers or users),
procedures, data, and information (processed data). All six
elements interact to convert data into information. System
analysis relies heavily upon computers to solve problems.
Therefore, software is needed to implement such solutions.
A) Research Motivation
Inventory management decisions are middle level
managerial functions of aggregate planning. Aggregate
planning is concerned with matching supply and demand of
materials over the medium time range, up to approximately 12
months into the future. As a result of aggregate planning,
decisions and policies must be made concerning among
others, inventory level. Materials Requirements Planning is
only one of such inventory models which can be viewed as a
way of relating output plans to input requirement. The
resulting materials model is often simulated in order to
answer “what if” questions. This allows one to examine the
effects of proposed changes in the materials' plan before the
changes are made. It is based on this backdrop that the need
for the design and development of a system for such a
simulation becomes imperative. Although, software like
Lingo developed by Lingo Corporation of America already
D
INTERNATIONAL JOURNAL OF MULTIDISCIPLINARY SCIENCES AND ENGINEERING, VOL. 5, NO. 1, JANUARY 2014
[ISSN: 2045-7057] www.ijmse.org 12
exists in solving such problems, the formulation of the linear
equations structure for implementing this is always a
herculean task which is beyond the level of the averagely
skilled manager. Hence, “The design and development of
Materials Requirements Planning Simulation Model using
Visual Basic” which is an object- oriented programming
language.
Although, Materials Requirements Planning is not a
stochastic problem the program is taking advantage of the fact
that simulation returns numerical values.
B) The MRP Program Development Process
1. The aim of the program is to solve the problem of when to
order and how much to order.
2. A user interface was created using Visual Basic’s program
development tools. This involved two related activities:
a. Drawing the controls within the form
b. Defining the properties of each control.
3. The Visual basic instructions to carry out the actions
resulting from the various program events were written.
This involved writing a group of commands, called an
event procedure, for each control. Certain control like
labels do not have event procedures associated with them.
4. At different stages program validations were carried out to
ensure it executes correctly. The flow chart for this process
is shown in Fig. 4.
YES
NO
Fig. 4: Flowchart for developing the MRP simulation model using Visual Basic
Based on this framework the objectives of the paper are to
design and develop software to: Accept demand data as input,
Process the input, determine the quantity of materials to order,
determine when to order depending on the lead time of such
materials, Determine the total ordering cost of materials,
Determine the total holding cost of materials and Perform
quickly, sensitivity analysis to view different scenarios of
“What if”.
Inventory decisions and policies affect profitability of any
manufacturing and business concern. The choice among
policies depends upon their relative profitability. Hence, this
work:
Provides the solution to inventory problems of how
much to order and when to order.
Provides for the quick evaluation of the important costs
of inventory viz-a-viz holding cost and ordering cost
and consequently, the total cost of inventory.
Provides for comparative evaluations of different
inventory level with their associative cost implications.
Enhances effective choice of lot size rule.
1. Create MRP Project with design plan.
2. Create the user interface by placing the objects and controls on the form. This is form design.
3. Set the properties of the objects in the form at design time or by coding run-time properties
settings.
4. Write code for the events to occur at the controls and objects inserted in the form i.e. when a
mouse is clicked in selecting an object.
5. Save the form with MRP.frm and the project with MRP.vbp and then run the application.
END
START
Any
Errors?
7. Make an executable file by compiling the project into a stand-alone executable file.
6. Test and Debug the
application.
INTERNATIONAL JOURNAL OF MULTIDISCIPLINARY SCIENCES AND ENGINEERING, VOL. 5, NO. 1, JANUARY 2014
[ISSN: 2045-7057] www.ijmse.org 13
To show how systematic operation analysis is
advantageous to decision making.
Serve as a pedagogic tool in topics of inventory
management in higher institutions.
II. THEORETICAL FRAMEWORK
This paper is hinged on the duo of inventory theory and
system theory. The inventory theory comprises inventory
models. In inventory models the major objective consists of
minimizing the total inventory cost and to balance the
economics of large orders or large production runs against the
cost of holding inventory and the cost of going short,
Nahmias (1997).
The system theory views the System as an integrated set of
interoperable elements, each with explicitly specified and
bounded capabilities, working synergistically to perform
value-added processing to enable a User to satisfy mission-
oriented operational needs in a prescribed operating
environment with a specified outcome and probability of
success. By the International Council on Systems Engineering
(INCOSE)
Every system whether it is natural or man-made co-exists
with an environment. It is very important for a system to
adapt itself to its environment. Also, for a system to exist it
should change according to the changing environment.
Systems interact with their environment to achieve their
targets. Things that are not part of the system are
environmental elements for the system. Depending upon the
interaction with the environment, systems can be divided into
two categories, open and closed.
Open Systems: These are systems that interact with their
environment. Practically most of the systems are open
systems. An open system has many interfaces with its
environment. It can also adapt to changing environmental
conditions. It can receive inputs from, and delivers output to
the outside of system. An information system is an example
of this category. The open system construct is shown in
Fig. 1.
Closed Systems: Systems that don't interact with their
environment. Closed systems exist in concept only.
Fig. 1: An open system construct
A) Managing Inventory Systems
The inventory system is a typical example of an open
system. This is because the total value of all inventory include
finished goods, partially finished goods, and raw material,
Hillier and Lieberman (2001). Models for the management of
inventory systems when the products involved have certain or
uncertain demand abound. Although mathematical models
sometimes can help analyze these more complicated systems,
simulation often plays a key role as well.
Inventory Level: The relation between flow, time and
inventory level that is basic to all systems is:
Inventory Level = (Flow Rate) (Residence Time)
Where the flow rate is expressed in the same time units as
the residence time.
Flow In Flow Out
Fig. 2: A system component with inventory
Fig. 2 represents a manufacturing process that takes a fixed
amount of time depicting inventory system as an open system.
Jesen et al (2001).
The inventory level depends on the relative rates of flow in
and out of the system. Define y (t) as the rate of input flow at
time t and Y (t) the cumulative flow into the system. Define
z(t) as the rate of output flow at time t and Z(t) as the
cumulative flow out of the system. The inventory level, I(t) is
the cumulative input less the cumulative output.
B) Mathematical Inventory Models
The mathematical inventory models used can be divided
into two broad categories-deterministic models and stochastic
models-according to the predictability of demand involved.
The demand for a product in inventory is the number of units
that will need to be withdrawn from inventory for some use
(e.g., sales) during a specific period. If the demand in future
periods can be forecast with considerable precision, it is
reasonable to use an inventory policy that assumes that all
forecasts will always be completely accurate. This is the case
of known demand where a deterministic inventory model
would be used. However, when demand cannot be predicted
very well, it becomes necessary to use a stochastic inventory
model where the demand in any period is a random variable
rather than a known constant.
There are several basic considerations involved in
determining an inventory policy that must be reflected in the
mathematical inventory model. These are discussed below.
C) Components of Inventory Models
According to Tersine (1998), because inventory policies
affect profitability, the choice among policies depends upon
their relative profitability. Some of the costs that determine
this profitability are: the ordering costs, holding costs,
shortage costs, revenues, salvage costs, discount rates.
Inventory Level
(Residence Time)
INTERNATIONAL JOURNAL OF MULTIDISCIPLINARY SCIENCES AND ENGINEERING, VOL. 5, NO. 1, JANUARY 2014
[ISSN: 2045-7057] www.ijmse.org 14
In using quantitative techniques to seek optimal inventory
policies, the criterion of minimizing the total (expected)
discounted cost is used. Under the assumptions that the price
and demand for the product are not under the control of the
company and that the lost or delayed revenue is included in
the shortage penalty cost, minimizing cost is equivalent to
maximizing net income, Liu et al (1990). Another useful
criterion is to keep the inventory policy simple, i.e., keep the
rule for indicating when to order and how much to order both
understandable and easy to implement. Most of the policies
considered in this work possess this property.
As mentioned at the beginning of the chapter, inventory
models are usually classified as either deterministic or
stochastic according to whether the demand for a period is
known or is a random variable having a known probability
distribution.
Another component of an inventory model is the lead time.
Lead time is the amount of time between the placement of
an order to replenish inventory (through either purchasing or
producing) and the receipt of the goods into inventory. If the
lead time always is the same (a fixed lead time), then the
replenishment can be scheduled just when desired. Most
models in this work assume that each replenishment occurs
just when desired, either because the delivery is nearly
instantaneous or because it is known when the replenishment
will be needed and there is a fixed lead time.
Another classification refers to whether the current
inventory level is being monitored continuously or
periodically. In continuous review, an order is placed as soon
as the stock level falls down to the prescribed reorder point. In
periodic review, the inventory level is checked at discrete
intervals, e.g., at the end of each week, and ordering decisions
are made only at these times even if the inventory level dips
below the reorder point between the preceding and current
review times. (In practice, a periodic review policy can be
used to approximate a continuous review policy by making
the time interval sufficiently small.)
C) Deterministic Continuous-Review Models
The most common inventory situation faced by
manufacturers, retailers, and wholesalers is that stock levels
are depleted over time and then are replenished by the arrival
of a batch of new units, Silver et al (1998). A simple model
representing this situation is the Economic Order Quantity
model or, for short, the EOQ model. (It sometimes is also
referred to as the economic lot-size model.) Units of the
product under consideration are assumed to be withdrawn
from inventory continuously at a known constant rate,
denoted by a; that is, the demand is a units per unit time. It is
further assumed that inventory is replenished when needed by
ordering (through either purchasing or producing) a batch of
fixed size (Q units), where all Q units arrive simultaneously at
the desired time. For the basic EOQ model to be presented
first, the only costs to be considered are K = setup cost for
ordering one batch, c = unit cost for producing or purchasing
each unit, h = holding cost per unit per unit of time held in
inventory.
The objective is to determine when and by how much to
replenish inventory so as to minimize the sum of these costs
per unit time.
Continuous reviewwas assumed, so that inventory can be
replenished whenever the inventory level drops sufficiently
low. We shall first assume that shortages are not allowed (but
later we will relax this assumption). With the fixed demand
rate, shortages can be avoided by replenishing inventory each
time the inventory level drops to zero, and this also will
minimize the holding cost.
Dynamic Economic Quantity Model: This model differs
from the model just discussed in two respects, Taha (2007)
1. The inventory level is reviewed periodically over a
finite period number of equal periods.
2. The demand per period, though deterministic, is
dynamic, in the sense that it varies from one period
to the next.
A situation in which dynamic deterministic demand occurs
is Materials Requirements Planning (MRP).
The Model with Initial Stock Level: In the above model we
assume that there is no initial inventory. As a slight variation,
suppose now that there is initial stock level, how does this
stock influence the optimal inventory policy?
In general terms, suppose that the initial stock level is given
by x, and the decision
to be made is the value of y, the inventory level after
replenishment by ordering (or producing) additional units.
Thus, is to be ordered, so that
The cost equation presented earlier remains identical except
for the term that was previously . This term now becomes
so that minimizing the expected cost is given by
The constraint must be added because the inventory
level y after replenishing cannot be less than the initial
inventory level x.
The optimal inventory policy is the following, Axsäter, S.
(2000): If
Where
INTERNATIONAL JOURNAL OF MULTIDISCIPLINARY SCIENCES AND ENGINEERING, VOL. 5, NO. 1, JANUARY 2014
[ISSN: 2045-7057] www.ijmse.org 15
D) Materials Requirements Planning
Because of the dependencies and interrelationships
involved, managing the inventories of dependent-demand
products can be considerably more complicated than for
independent-demand products. A popular technique for
assisting in this task is materials requirements planning,
abbreviated as MRP. MRP is a computer-based system for
planning, scheduling, and controlling the production of all the
components of a final product.
Materials requirements planning (MRP) is a set of
calculations embedded in a system that helps operations make
volume and timing calculations for planning and control
purposes, Wight (1984).
Material Requirements Planning (MRP) is a computer-
based production planning and inventory control system.
MRP is concerned with both production scheduling and
inventory control. It is a material control system that attempts
to keep adequate inventory levels to assure that required
materials are available when needed. MRP is applicable in
situations of multiple items with complex bills of materials.
MRP is not useful for job shops or for continuous processes
that are tightly linked, Volman et al (1992).
Material Requirement Planning is a dependent demand
system that calculates materials requirements and production
plans to satisfy known and forecast sales orders. It helps to
make volume and timing calculations based on an idea of
what will be necessary to supply demand in the future, Nigel
Slack et al (2001).
In all these definitions of Materials Requirements Planning,
they all implied that it is a technique which assists a company
in the detailed planning of its production. MRP translates that
aggregate plan into an extremely detailed plan.
The system begins by “exploding” the product by breaking
it down into all its subassemblies and then into all its
individual component parts. A production schedule is then
developed, using the demand and lead time for each
component to determine the demand and lead time for the
subsequent component in the process. In addition to a master
production schedule for the final product, a bill of materials
provides detailed information about all its components.
Inventory status records give the current inventory levels,
number of units on order, etc., for all the components. When
more units of a component need to be ordered, the MRP
system automatically generates either a purchase order to the
vendor or a work order to the internal department that
produces the component.
Materials requirements planning systems typically require
certain data records which the MRP program checks and
updates. The most obvious inputs are customer orders and
forecast demand. MRP performs its calculations based on the
combination of both firm and forecast orders. All other
requirements calculated within the MRP process are derived
from these demands thus; MRP is dependent demand system.
It is important point to note that MRP is not cost driven i.e. it
does not seek to minimise cost. Instead MRP is stockout
driven - that is it will always order sufficient to avoid stock
outs (using the lot size rule for each item) and order as late as
possible. Beasley (N.A).
E) Demand Management
Taken together, the management of customer orders and
sales forecasts is called ‘demand management’. This is a set
of processes which interfaces with the customer market,
Hillier et al (2001). Depending on the business, these
processes may include sales order entry, demand forecasting,
order promising, customer service and physical distribution.
For example, if you place an order on the internet and ring up
a week later to check why your purchase has not arrived, you
will deal with a call centre service operator. He or she can
access the details of your particular order and advise why
there might have been a hold-up in delivery. In addition, you
could be given a delivery promise and information regarding
the mode of delivery. That single interaction with a customer
triggers a chain of events. The item has to be picked from a
warehouse; a stores operator must therefore be given the
appropriate information, the delivery must be booked and so
on. If demand information is not available or communicated,
any subsequent plans will be misleading. Therefore we now
need to consider some of the implications of managing
demand on MRP.
Customer orders: Sales functions typically manage a
dynamic, changing order book made up of confirmed orders
from customers. Of particular interest to the MRP process are
the records of exactly what each customer has ordered, how
many they have ordered and when they require delivery. But
customers may change their minds after having placed their
orders and because customer service and flexibility are
increasingly important competitive factors, MRP must be able
to react to this. Considering that each of several hundred
customers may make changes to their sales orders, not once
but possibly several times after the order has been placed,
managing the sales order book is a complex and dynamic
process.
Forecast demand: Using historical data to predict future
trends, cycles or seasonality is always difficult. Driving a
business using forecasts based on history has been compared
to driving a car by looking only at the rear-view mirror,
Hillier and Lieberman (2001). In spite of the difficulties;
many businesses have no choice but to forecast ahead.
Combining orders and forecasts: A combination of known
orders and forecast orders is used to represent demand in
many businesses. This should be the best estimate at any time
of what reasonably could be expected to happen. But the
further ahead you look into the future, the less certainty there
is about demand. Most businesses have knowledge of short-
term demand, but few customers place orders well into the
future. Based on history and on market information, a forecast
is put together to reflect likely demand, although different
operations will have a different mix of known and forecast
orders. A make-to-order business, such as a jobbing printer,
will have greater visibility of known orders over time than a
make-for-stock business, such as a consumer durables
manufacturer. Purchase-to-order businesses do not order most
of their raw materials until they receive a confirmed customer
order. For example, a craft furniture maker may not order
materials until the order is certain. Conversely, there are some
operations that have very little order certainty at the time they
INTERNATIONAL JOURNAL OF MULTIDISCIPLINARY SCIENCES AND ENGINEERING, VOL. 5, NO. 1, JANUARY 2014
[ISSN: 2045-7057] www.ijmse.org 16
take most of their decisions. Conceptually therefore there are
two related decisions about ordering:
timing - when to order
quantity - how much to order
Inventory Decisions: Consider about what have been done
with respect to these two decisions of:
timing - when to order
quantity - how much to order
With respect to the timing decision, always order as late as
possible, but never planned a stock out. This is a driving
principle in MRP, never order before it is needed, never plan
to stock out. Quantity decision rule can be varied in MRP and
are as follow:
lot for lot rule with respect to the quantity decision is to
always ordered as little as possible, i.e., just enough to
avoid a stock out. It is sometimes called LFL or L4L or
LL rule.
fixed order quantity rule (sometimes called FOQ or FO)
- the quantity ordered is an integer multiple of the same
fixed amount each time an order is made
fixed period requirements rule (sometimes called FPR) -
the quantity ordered should be enough for a fixed
number of periods .
Choice of lot size rule: How are the choice made between
different lot size rules (LFL, FPR and FOQ)? Redo the MRP
calculations with different lot size rules (e.g., a FOQ ordering
multiples of 100 each time). Then the different effects would
be seen but would still have to choose between them. Beasley
(N.A).
According to Beasley, All of the rules ensure forecast demand
is met, i.e. no stock outs, so this is not a distinguishing
feature. The LFL rule (by ordering as little as possible each
time) will keep average inventory levels low, but will result in
more orders on average. Both the FPR and FOQ rules will
have higher inventory levels, but will result in less order on
average. Choosing a lot size rule therefore comes down to
balancing the number of orders against the cost of holding
inventory. Hence, given cost information, it is possible to
derive the most effective (least costly) lot rule to use for any
particular item. Given all this information then (conceptually
at least) it will be possible to calculate what will be done, in
terms of when to place orders with external suppliers (or
internal suppliers) and the size of those orders, so that out of
stock of any item is never encountered i.e., always achieve the
planned production and meet the sales orders.
This process of calculating the orders needed is called an
MRP EXPLOSION and produces the materials requirements
(hence the name - Materials Requirements planning).
F) Essence of Simulation
The technique of simulation has long been an important
tool of the designer. Simulation plays essentially this same
role in many Operation Research studies.
To prepare for simulating a complex system, a detailed
simulation model needs to be formulated to describe the
operation of the system and how it is to be simulated.
Simulation models have several basic building blocks: Law
(1986).
Law and Kelton (1991) opined that great progress is being
made in developing special software for efficiently integrating
the simulation model into a computer program and then
performing the simulations. Nevertheless, when dealing with
relatively complex systems, simulation tends to be a relatively
expensive procedure. After formulating a detailed simulation
model, considerable time often is required to develop and
debug the computer programs needed to run the simulation.
Discrete-Event versus Continuous Simulation: Two broad
categories of simulations are discrete-event and continuous
simulations.
A discrete-event simulation is one where changes in the state
of the system occur instantaneously at random points in time
as a result of the occurrence of discrete events. For example,
in a queuing system where the state of the system is the
number of customers in the system, the discrete events that
change this state are the arrival of a customer and the
departure of a customer due to the completion of its service.
Most applications of simulation in practice are discrete-event
simulations.
A continuous simulation is one where changes in the state of
the system occur continuously over time. For example, if the
system of interest is demand and supply its state is defined as
the current demand and or supply, then the state is changing
continuously over time. Some applications of continuous
simulations occur in design studies of such engineering
manufacturing systems.
Continuous simulations typically require using differential
equations to describe the
rate of change of the state variables. Thus, the analysis tends
to be relatively complex.
G) Software Design Principles and Guidelines
Douglas, (2003) proposed eight software design principles
and guideline for software engineers.
In summary, good designs can generally be distilled into a
few key principles:
Separate interface from implementation
Determine what is common and what is variable with an
interface and an implementation
Allow substitution of variable implementations via a
common interface i.e., the “open/closed” principle
Dividing commonality from variability should be goal-
oriented rather than exhaustive
He concluded that design is not simply the act of drawing a
picture using a CASE tool or using graphical UML notation.
Design is a fundamentally creative activity.
H) Selecting the Software and Constructing a Computer
Program for Simulation
There are four major classes of software used for computer
simulations, Banks (1998). One is spreadsheet software.
Some excellent Excel add-ins now is available to enhance this
kind of spreadsheet modelling. The other three classes of
software for simulations are intended for more extensive
applications where it is no longer convenient to use
INTERNATIONAL JOURNAL OF MULTIDISCIPLINARY SCIENCES AND ENGINEERING, VOL. 5, NO. 1, JANUARY 2014
[ISSN: 2045-7057] www.ijmse.org 17
spreadsheet software. One such class is a general-purpose
programming language, such as C, FORTRAN, PASCAL,
BASIC, etc. Such languages (and their predecessors) often
were used in the early history of the field because of their
great flexibility for programming any sort of simulation.
However, because of the considerable programming time
required, they are not used nearly as much now. The third
class is a general-purpose simulation language. These
languages provide many of the features needed to program a
simulation model, and so may reduce the required
programming time substantially. They also provide a natural
framework for simulation modelling. Although less flexible
than a general-purpose programming language, they are
capable of programming almost any kind of simulation
model. However, some degree of expertise in the language is
needed.
Prominent general-purpose simulation languages include
the current version of GPSS, SIMSCRIPT, SLAM, and
SIMAN. The initial versions of these languages date back to
1961, 1963, 1979, and 1983, respectively, but all have stood
the test of time.
A key development in the 1980s and 1990s has been the
emergence of the fourth class of software, called applications-
oriented simulators (or just simulators for short). Each of
these simulators is designed for simulating fairly specific
types of systems, such as certain types of manufacturing,
computer, and communications systems. Some are very
specific (e.g., for oil and gas production engineering, or
nuclear power plant analysis, or cardiovascular physiology).
Their goal is to be able to construct a simulation “program”
by the use of menus and graphics, without the need for
programming. They are relatively easy to learn and have
modeling constructs closely related to the system of interest.
Traverse Software Tools for Material Requirements
Planning: A true planning tool that gives you the information
you need to solve tomorrow’s problems … today (Open
Systems Inc.)
Most manufacturing problems would be a lot easier to solve
if they can be seen coming. Materials planning involve
managing sales forecasts, creating master schedules, and
running MRP, In short, balancing future supply and demand.
TRAVERSE Material Requirements Planning is an example
of spreadsheet software which enables you to plan for the
future. You’ll be able to meet market demand and address
your company’s production plan. With TRAVERSE Material
Requirements Planning, it is possible to produce a Master
Production Schedule and plan the manufacturing of finished
goods in order to meet the expected demand from the sales
forecasts. Next, MRP functionality is used to determine the
raw materials needed and when it is appropriate to purchase
them in order to fulfil the production goals based on the
Master Production Schedule.
At the heart of the system is the MRP report, a time-phased
glimpse of the future demand for material components and
assemblies. Combining the best of both worlds, this report
features two formats for users: one for those who prefer
bucketless reporting and one for those who prefer the standard
MRP report based on daily, weekly, or monthly summaries.
Forecasting can be a challenge, but the TRAVERSE
software tools make the job easier than ever. Forecasts can be
automatically created for individual parts and assemblies
based on current history using multiple methodologies.
Forecast flexibility is built in at every step of the process.
I) Programming in Basic
Udosen (1997), opined that prior to any serious
programming, the system analyst or programmer must prepare
the program specifications which among other things should
include:
1. A description of the program;
2. Specifications and layout for each input file and output
of the program;
3. Any formulae and parameters or other special material
the programmer will need.
J) Visual Basic Applications
Visual Basic is an event-driven programming language for
creating applications that run under Microsoft’s Windows
operating systems. It is an object-oriented programming
developing system for creating applications, Byron (2001).
It has the following two major components:
1. An extensive collection of prewritten tools, called
controls. These controls are accessible as icons within a
graphical programming environment for creating
customized windows components. Examples of these
are menus, dialog boxes, text boxes, slide bars and
others.
2. A complete set of program commands, derived from
Microsoft’s implementation of the classic Basic
programming language. The command set includes
features that embrace contemporary programming
practices.
Object-Related Concepts: An object is a general term used
to describe all the forms and controls or “visual things” that
make up a program. The controls are graphic representation of
objects, which the user of the application can manipulate to
obtain information from (outputting) or provide information
(inputting) to the application, Udosen (2007). He opined that
this process of outputting and inputting information
interactively, using controls or objects is referred to as the
interface aspect of the program.
FORMS: A window is called a form. The form includes a
title bar at the top, and a menu bar. A user area (called a
client area) occupies the remaining space of the form. This
application is based upon a single form, Byron (2001).
CONTROLS: These are the icons with which the users
interact. The controls used in this application include
command button, text boxes, option buttons, check boxes,
labels and menus. The user will typically activate a control
(e.g., click on a command button) to produce an event, Byron
(2001).
OBJECTS: Forms and controls are referred to collectively
as objects. Most objects are associated with events; hence,
objects may include their own unique event procedures.
INTERNATIONAL JOURNAL OF MULTIDISCIPLINARY SCIENCES AND ENGINEERING, VOL. 5, NO. 1, JANUARY 2014
[ISSN: 2045-7057] www.ijmse.org 18
Objects are also associated with their own properties and
methods, Byron (2001).
PROPERTIES: Objects include properties that generally
define their appearance or behaviour. The choice of properties
depends on the type of object, Byron (2001).
METHODS: Some objects also include special program
statement called methods. A method brings about some
predefined action affecting the associated object. For
example, show is a method that can be used with a hidden
form to make it visible, Byron (2001).
III. METHODOLOGY AND DEVELOPMENT
This paper is based upon an event-driven paradigm, in
which each feature included within the program is activated
only when the user responds to a corresponding object (any of
the components) within the user interface. The program’s
response to an action taken by the user is referred to as an
event. The user initiates the event, but it is the program’s
response that actually defines the event. The group of Basic
command that brings about this response is called an event
procedure.
The overall approach to this paper is therefore threefold:
Creating a user interface that is appropriate to Materials
Requirements Planning.
Adding a group of Basic instruction to carry the actions
associated with each of the controls.
Model validation to ensure it works appropriately.
A) Materials Requirement Planning Program Components
The project consists of two separate files:
A project file whose extension is .vbp
A form file with extension .frm
Design: The design procedures involve creating the user
interface. This involves designing the form and the controls,
and then specifying their critical properties. The tables below
show the design of the different objects of the project.
Development: The development of the project then
involves keeping track of these two different files, and
accessing these files individually within the Visual Basic
environment, as needed. The flowchart for deigning each of
the objects is shown in figure 3. While that for developing the
Materials Requirements Planning Simulation Model using
Visual Basic is shown in Fig. 4.
YES NO
No more objects to insert!
Fig. 3: Flowchart for the design of inserted sets of objects on the form i.e., (Textboxes, Labels, Option buttons, Combo boxes, Command buttons, Menus and
Dialog box)
1. Insert the sets of particular objects into the form.
2. Set the properties of the objects in the form at design time or by coding run-time
properties settings.
3. Write code for the events to occur at the controls and objects inserted in the form i.e.
when a mouse is clicked in selecting an object.
Any
Errors?
4. Save the form with the inserted sets of objects and then run the application.
5. Test and Debug the
application.
END
START
INTERNATIONAL JOURNAL OF MULTIDISCIPLINARY SCIENCES AND ENGINEERING, VOL. 5, NO. 1, JANUARY 2014
[ISSN: 2045-7057] www.ijmse.org 19
B) Objects Design
Table 1: Form Design
OBJECT
PROPERTIES
SPECIFICATIONS
FORM
Appearance
1-3D
Caption
MATERIALS
REQUIREMENTS
PLANNING WIZARD
Visibility
True
Font
Ms Scan Serif
Name
Form1
MaxButton
True
MDIChild
False
MinButton
True
Window State
Normal
Height
9810
Left
105
Movable
True
Startup Position
3-Window
Top
105
Width
19215
Table 2: Common textboxes’ properties
PROPERTIES
SPECIFICATIONS
Appearance
1 - 3D
BackColor
&H000000F&
BorderStyle
1 - Fixed Single
ForeColor
&H80000008&
HideSelection
TRUE
Cause Validation
TRUE
DragIcon
None
DragMode
0 – Manual
Enable
TRUE
Locked
FALSE
MaxLength
0
Multiline
FALSE
OLEDragMode
0 – Manual
OLEDropMode
0 – None
ScrollBar
0 – None
TabStop
TRUE
Visible
TRUE
Font
MS Sans Serif
Table 3: Design of textboxes
PROPERTIES' SPECIFICATIONS
OBJECTS
NAME
ALIGNMENT
TEXT
HEIGHT
LEFT
TOP
WIDTH
TextBoxes
Text1
O-Left Justify
[ Empty ]
495
1920
4440
1215
Text2
O-Left Justify
[ Empty ]
495
3120
4440
1215
Text3
O-Left Justify
[ Empty ]
495
4320
4440
1215
Text4
O-Left Justify
[ Empty ]
495
5520
4440
1215
Text5
O-Left Justify
[ Empty ]
495
6720
4440
1215
Text6
O-Left Justify
[ Empty ]
495
7920
4440
1215
Text7
O-Left Justify
[ Empty ]
495
9120
4440
1215
Text8
O-Left Justify
[ Empty ]
495
10320
4440
1215
Text9
O-Left Justify
[ Empty ]
495
11520
4440
1215
Text10
O-Left Justify
[ Empty ]
495
12720
4440
1215
Text11
O-Left Justify
[ Empty ]
495
13920
4440
1215
Text12
O-Left Justify
[ Empty ]
495
15120
4440
1215
Text13
O-Left Justify
[ Empty ]
495
1920
5520
1215
TextBoxes
Text14
O-Left Justify
[ Empty ]
495
3120
5520
1215
Text15
O-Left Justify
[ Empty ]
495
4320
5520
1215
Text16
O-Left Justify
[ Empty ]
495
5520
5520
1215
Text17
O-Left Justify
[ Empty ]
495
6720
5520
1215
Text18
O-Left Justify
[ Empty ]
495
7920
5520
1215
Text19
O-Left Justify
[ Empty ]
495
9120
5520
1215
INTERNATIONAL JOURNAL OF MULTIDISCIPLINARY SCIENCES AND ENGINEERING, VOL. 5, NO. 1, JANUARY 2014
[ISSN: 2045-7057] www.ijmse.org 20
Text20
O-Left Justify
[ Empty ]
495
10320
5520
1215
Text21
O-Left Justify
[ Empty ]
495
11520
5520
1215
Text22
O-Left Justify
[ Empty ]
495
12720
5520
1215
Text23
O-Left Justify
[ Empty ]
495
13920
5520
1215
Text24
O-Left Justify
[ Empty ]
495
15120
5520
1215
Text25
O-Left Justify
[ Empty ]
495
1920
6600
1215
Text26
O-Left Justify
[ Empty ]
495
3120
6600
1215
Text27
O-Left Justify
[ Empty ]
495
4320
6600
1215
Text28
O-Left Justify
[ Empty ]
495
5520
6600
1215
Text29
O-Left Justify
[ Empty ]
495
6720
6600
1215
Text30
O-Left Justify
[ Empty ]
495
7920
6600
1215
Text31
O-Left Justify
[ Empty ]
495
9120
6600
1215
Text32
O-Left Justify
[ Empty ]
495
10320
6600
1215
Text33
O-Left Justify
[ Empty ]
495
11520
6600
1215
Text34
O-Left Justify
[ Empty ]
495
12720
6600
1215
Text35
O-Left Justify
[ Empty ]
495
13920
6600
1215
Text36
O-Left Justify
[ Empty ]
495
15120
6600
1215
TextBoxes
Text37
O-Left Justify
[ Empty ]
495
6600
1080
1215
Text38
O-Left Justify
[ Empty ]
495
6600
1800
1215
Text39
O-Left Justify
[ Empty ]
495
2640
240
8535
Text40
O-Left Justify
[ Empty ]
495
11760
2880
1215
Text41
O-Left Justify
[ Empty ]
495
12960
240
2535
Text42
O-Left Justify
[ Empty ]
495
2280
7440
2415
Text43
O-Left Justify
[ Empty ]
495
12600
7440
2415
Text44
O-Left Justify
[ Empty ]
495
2880
2760
1455
Text45
O-Left Justify
[ Empty ]
495
6720
2640
1575
Table 4: Design of labels
PROPERTIES' SPECIFICATIONS
OBJECTS
NAME
CAPTION
HEIGHT
LEFT
TOP
WIDTH
Labels
Label1
Period [Lead Time]
495
120
3720
1575
Label2
Demand
495
120
4440
1215
Label3
Stock @ Period End
495
120
5520
1935
Label4
Order
495
120
6600
1335
Label5
[ Empty ]
495
1920
3840
1095
Label6
[ Empty ]
495
3120
3840
1095
Label7
[ Empty ]
495
4320
3840
1095
Label8
[ Empty ]
495
5520
3840
1095
Label9
[ Empty ]
495
6720
3840
1095
Label10
[ Empty ]
495
8040
3840
1095
Label11
[ Empty ]
495
9240
3840
1095
Label12
[ Empty ]
495
10440
3840
1095
Label13
[ Empty ]
495
11640
3840
1095
Label14
[ Empty ]
495
12840
3840
1095
INTERNATIONAL JOURNAL OF MULTIDISCIPLINARY SCIENCES AND ENGINEERING, VOL. 5, NO. 1, JANUARY 2014
[ISSN: 2045-7057] www.ijmse.org 21
Label15
[ Empty ]
495
14040
3840
1095
Label16
[ Empty ]
495
15120
3840
1095
Label17
LOT SIZE RULE
495
9360
1080
1335
Label18
CURRENT STOCK
495
5040
1200
1575
Label19
SAFETY STOCK
495
5040
1920
1575
Label20
DATE
495
11880
240
975
Labels
Label21
LEAD TIME
495
14160
1200
1455
Label22
[ Empty ]
495
1920
5040
1215
Label23
[ Empty ]
495
3120
5040
1215
Label24
[ Empty ]
495
4320
5040
1215
Label25
[ Empty ]
495
5520
5040
1215
Label26
[ Empty ]
495
6720
5040
1215
Label27
[ Empty ]
495
7920
5040
1215
Label28
[ Empty ]
495
9120
5040
1215
Label29
[ Empty ]
495
10320
5040
1215
Label30
[ Empty ]
495
11520
5040
1215
Label31
[ Empty ]
495
12720
5040
1215
Label32
[ Empty ]
495
13920
5040
1215
Label33
[ Empty ]
495
15120
5040
1215
Label35
ORDERING COST
495
120
7440
2055
Label36
HOLDING COST
495
10680
7440
1815
Label37
Ordering cost/Unit
495
840
2760
1935
Label38
Holding Cost/Unit
375
4920
2760
1695
Label39
A PRODUCT OF
OKOUZI,SOLOMON
ABHULIMHEN
255
6360
8400
4455
Table 5: Common labels’ properties
PROPERTIES
SPECIFICATIONS
Appearance
1 - 3D
BackColor
&H000000F&
BackStyle
(None)
ForeColor
(None)
DragIcon
(None)
DragMode
0 – Manual
Enable
TRUE
OLEDropMode
0 – None
RightToLeft
FALSE
Alignment
2-Center
Visible
TRUE
LinkMode
0 – None
LinkTimeOut
50
Font
MS Sans Serif
MousePointer
0 – Default
MouseIcon
(None)
Use Mnemonic
TRUE
WordWrap
FALSE
AutoSize
FALSE
Table 6: Design of Command Buttons
PROPERTIES' SPECIFICATIONS
OBJECTS
NAME
CAPTION
HEIGHT
LEFT
TOP
WIDTH
Command Bottons
Command1
Plan
495
5160
7440
1215
Command2
Cancel
495
7200
7440
1215
Command3
Exit
495
9000
7440
1215
INTERNATIONAL JOURNAL OF MULTIDISCIPLINARY SCIENCES AND ENGINEERING, VOL. 5, NO. 1, JANUARY 2014
[ISSN: 2045-7057] www.ijmse.org 22
Table 7: Design of Option Buttons
PROPERTIES' SPECIFICATIONS
OBJECTS
NAME
CAPTION
HEIGHT
LEFT
TOP
WIDTH
Option
Buttons
Option1
Lot for lot
495
9360
1680
1695
Option2
Fixed Period Requirement
495
9360
2280
2295
Option3
Fixed Order Quantity
495
9360
2880
2295
Table 8: Design of ComboBoxes
PROPERTIES' SPECIFICATIONS
OBJECTS
NAME
LIST
TEXT
HEIGHT
LEFT
TOP
WIDTH
ComboBoxes
Combo1
I Week
LEAD TIME
315
14040
1800
1575
2 Weeks
3 Weeks
1 Month
2 Months
3 Months
4 Months
Combo2
2 Weeks
FIXED PERIOD
315
11760
2400
1575
3 Weeks
4 Weeks
2 Months
3 Months
4 Months
Table 9: CommonDialog Properties
C) Coding
The Visual basic instructions to carry out the actions
resulting from the various program events were written. This
involved writing a group of commands, called an event
procedure, for each control. Certain control like labels do not
have event procedures associated with them.
IV. MODEL VALIDATION AND DISCUSSION
A) Inventory Problem for Model Validation
Aldershot Manufacturing is a chair manufacturing
Company. The Production manager of Aldershot
Manufacturing wishes to develop a materials requirements
plan for producing chairs over an eight week period. She
estimates that the lead time between releasing an order to the
shop floor and producing a finished chair is two weeks. The
company currently has 260 chairs in stock and no safety stock
(safety stock is stock held in reserve to meet customer
demand if necessary). The forcast customer demand is 150
chairs in week 1, 70 in week 3, 175 in week 5, 90 in week 7
and 60 in week 8.
This problem satisfies the conditions for the use of material
requirement planning model in that:
The problem has forcast demand,
The lead time is constant,
The forcast demand is dynamic i.e., changes from one
period the another,
Inventory is continuously reviwed periodically at the
end of each period (week)
The problems to solve are how much quantity to order
and when to order such quantity to avoid stockout.
PROPERTIES
SPECIFICATIONS
CancelError
FALSE
Name
CommonDialog
Color
&H00000000&
Copies
1
FileName
Form1
MaxFileSize
260
Orientation
2-cdlLandscape
PrinterDefault
TRUE
Left
15240
Top
8280
INTERNATIONAL JOURNAL OF MULTIDISCIPLINARY SCIENCES AND ENGINEERING, VOL. 5, NO. 1, JANUARY 2014
[ISSN: 2045-7057] www.ijmse.org 23
This problem has been solved using the:
1. Lot for lot rule
2. Fixed period requirement rule
3. Fixed order quantity rule
As presented in tables 10, 11 and 12:
B) Result Presentation
The solutions for each of these rules are presented in the
Table 10 to Table 12.
Table 10: MRP with lot for lot rule
Week
1
2
3
4
5
6
7
8
Demand
150
0
70
0
175
0
90
60
On-hand @ week end
110
110
40
40
0
0
0
0
Order
0
0
135
0
90
60
0
0
Table 11: MRP with Fixed Period Requierments of Three weeks
Week
1
2
3
4
5
6
7
8
Demand
150
0
70
0
175
0
90
60
On-hand @ week end
110
110
40
40
90
90
0
0
Order
0
0
225
0
0
60
0
0
Table 12: MRP with Fixed Order Quantity of 190 chairs
Week
1
2
3
4
5
6
7
8
Demand
150
0
70
0
175
0
90
60
On-hand @ week end
110
110
40
40
55
55
155
95
Order
0
0
190
0
190
0
0
0
C) Validating the MRP Simulation Model
1. Enter the forcast demand in the demand role over the
required period. Note that this problem has only 8
periods.
2. Enter the current stock at hand (initial stock) of 260
and 0 for safety stock respectively in the provided
text boxes.
3. Enter the ordering cost per unit and the holding cost
per unit respectively in the text boxes provided.
4. (i) Click on lot for lot for lot for lot rule, or
(ii) Click on Fixed Period Requirement for FRP rule
and then select 3 weeks, or
(iii) Click on Fixed Order Quantity for FOQ rule and
then enter 190
5. Clicking on the arrow in the box labelled “LEAD
TIME”, and then from the dropdown that appears
select 2 Weeks.
6. Click the “Plan” button for each of 4 (i), 4(ii) and
4(iii) to display the results for lot for lot, fixed period
requirement and fixed order quantity rile
respectively.
Appendix 15, Appendix 16 and Appendix 17 show the
computer printout of the results of the problem when
implemented with the Materials Requirements Planning
simulation Model using Visual Basic programming language.
D) Discussions
The Lot for lot rule from the solution table 10 and
Appendix 15 both suggests that an order of 135 chairs is made
in week 3 to make up the deficiency of 170 requirements in
week 5, an order of 90 chairs required in week 7 is place in
week 5 while the order of the requirement of week 8 is placed
in week 6. The timing decision here is always to order as late
as possible but never to plan a stockout. This is to avoid
carrying cost.
The quantity decision here is always to order as little as
possible, i.e. just enough to avoid stock out, with a total cost
of N2062.50.
The Fixed Period Requirement specifies that the quantity
ordered should be enough for a period of 3 weeks. With this
in mind, table 11 and Appendix 16 both shows that 225 chairs
are to be ordered in week 3 to make up the requirements of
265 chairs needed in weeks 5, 6 and 7 while the requirement
of 60 chairs is ordered in week 6. This is with a total cost of
N2872.50.
The Fixed Order Quantity specifies an order of 190 chairs
any time an order is made. Table 12 and Appendix 18 both
confirm the order of 190 chairs twice that is, in week 3 and
week 5. They both also confirm an excess of 95 chairs at the
end of week 8 (and then up to the week 12 in the case of the
INTERNATIONAL JOURNAL OF MULTIDISCIPLINARY SCIENCES AND ENGINEERING, VOL. 5, NO. 1, JANUARY 2014
[ISSN: 2045-7057] www.ijmse.org 24
model). This has a total cost of N5630 from the model based
on the cost per unit chair ordered.
This correspondence between Table 10 to Table 12 and
Appendices 15, 16 and 17 validates this material
Requirements Planning Simulation Model.
E) Generating the MRP Stand-alone Executable Program
The MRP simulation model debugged, validated and
guaranteed that it simulated properly, the stand-alone version
was generated. The stand-alone is convenient because it can
be run independently of the Visual Basic development
system, and they can easily be transported from one computer
to another. The generation of the stand-alone resulted in a new
file with the name MRP.exe which can then be moved out of
the Visual Basic System or move to different computers, and
then execute on it own. The MRP.exe is a single file created
from the three files MRP.frm, MRP.vbp and MRP.vbw that
originally comprised the project. However, the new file is
substantially larger than the combined size of the original
three files. The exact file size will vary from one computer to
another. The stand-alone version has the window title
“MATERIALS REQUIREMENTS PLANNING (MRP)
WIZARD”.
V. CONCLUSION
A) Users Instructions
The model is used by following the steps below:
1. Enter the forecast demand in the demand role over
the required period. Note that the model only
provided for 12 periods.
2. Enter the current stock at hand (initial stock) and the
safety stock if any.
3. Enter the ordering cost per unit and the holding cost
per unit respectively in the text boxes provided.
4. Select among the option button the lot size rule by
clicking on any of the buttons.
a. If fixed period requirement is the selected lot
size rule, then click on the arrow on the box
labelled “FIXED PERIOD” from the dropdown
that appears; select the required fixed period
either in weeks or months.
b. If fixed order quantity is the selected lot size
rule, then enter the fixed quantity on the box
beside “Fixed Order Quantity”
5. Select the lead time by clicking on the arrow in the
box labelled “LEAD TIME”, then from the
dropdown that appears; select the required lead time
either in week(s) or month(s).
Note: Once the lead time is selected, the selected
lead time appear on the Period (lead time)
role above demand role over the entire
planning period.
6. Click the “Plan” button to display the results of the
stock at each end of period, the quantities to be
ordered and when to order such quantities, the total
ordering cost, the total holding cost and the total cost
of inventory. The time and date of planning is also
displayed by clicking the “Plan” button.
7. To cancel all entries click on the “Cancel” button,
then on the message box that appears click “Yes” to
cancel or “No” to exit the message box.
8. To exit the model window click on the “End” button,
then on the message box that appears click “Yes” to
exit or “No” to exit the message box.
B) Warnings for users
The model does not work if:
1. No lot size rule is selected.
2. No lead time is selected.
3. The fixed order quantity is not entered in the case of
fixed order quantity lot size rule.
REFERENCES
Axsäter, S. (2000). Inventory Control.Kluwer Academic Publishers,
Boston.
Byron, S.G. (2001). Theory and Problems of Programming with
Visual Basic: Schaum’s Outline. McGraw-Hill Inc., New
York.
Hillier, F.S and Lieberman, G.J., (2001).Introduction to Operations
Research.McGraw-Hill, New York.
Jensen, P.A. and Bard, J.F. (2001).Operations Research Models and
Methods. McGraw-Hill, New York.
Law, A. M., and Kelton W. D. (1991).Simulation Modeling and
Analysis, 2nd ed., McGraw-Hill, New York.*
Law, A. M. (1986). “Introduction to Simulation: A Powerful Tool
for Complex Manufacturing Systems,” Industrial
Engineering, 18(5): 46–63.*
Liu, B. and Esogbue, A.O. (1999).Decision Criteria and Optimal
Inventory Processes, Kluwer Academic Publishers,
Boston.*
Nahmias, S. (1997). Production and Operations Analysis, 3rd ed.
Irwin/McGraw-Hill, Burr Ridge, IL.
Roger, G.S. (1981). Operation Management in the Operation
Function. McGraw-Hill, New York.
Silver, E., Pyke, D. and Peterson, R. (1998).Inventory Management
and Production Planning and Scheduling, 3d ed., Wiley,
New York.
Taha, H.A. (2007). Operations Research: An Introduction, 8th ed.,
Pearson Prentice Hall Inc. NJ.
Tersine, R. (1988). Principles of Inventory and Materials
Management.3rd ed. North Holland, New York.
Udosen, U.J. (1997). Basic Programming for Technology.Essen
Classic Company, Uyo, Nigeria.
Volman, T.E., Berry, W.L., and Whybark, D.C.
(1992).Manufacturing Planning and Control Systems, 3rd
ed. Irwin, Burr Ridge, IL.
Wight, O. (1984).Manufacturing Resource Planning: MRP II. Oliver
Wight Ltd.*.
(*Any part of it is not directly quoted in the thesis only the principles
and ideas were used).
