TEACHING MRP EXPERIENTIALLY THROUGH THE USE OF LOTUS 1-2-3


Developments in Business Simulation & Experiential Exercises, Volume 14, 1987 

 179

TEACHING MRP EXPERIENTIALLY THROUGH THE 
USE OF LOTUS 1-2-3 

 
David L. Schroeder, Oklahoma State University 

James W. Gentry, University of Wisconsin-Madison 
 

ABSTRACT 
 
The use of spreadsheets as a supplemental planning tool with 
simulation games is becoming relatively common [1, 3, 11]. 
This paper will discuss one such application. The approach 
discussed is unique in that the spreadsheet usage deals with 
the Materials Requirement Planning (MRP) problem 
embedded in a logistics simulation game. While most of the 
students had been exposed previously to MRP and some of 
them to spreadsheet usage, this application presented an 
opportunity for them to work with MRP systematically and 
efficiently rather than just “muddling through.” 
 

INTRODUCTION 
 
The use of an experiential learning approach such as the live 
case method or a complex simulation game can provide a 
very rich environment for having students use specific tools 
to tackle subsets of the overall problem. Numerous ABSEL 
papers have discussed such pedagogical approaches. For 
example, Peters [9] discussed the use of various operations 
research techniques to help in the planning for a production 
game. Jauch and Gentry [8] discussed the use of a greatly 
simplified, interactive version of a business policy game to 
let students experiment with their decisions prior to using 
them in the game itself. Gentry [4] discussed the use of 
PERT to help students plan and complete a survey research 
project (a “live case”) within a semester. At last year’s 
conference, Anderson and Lawton [1], Shane and Bailes 
[10], and Sherrell, Russ, and Burns [11] all discussed the use 
of decision support systems as supplemental tools to help in 
planning for simulation games. Thus many ABSEL 
participants have come to the realization that not only do 
complex experiential exercises provide an interesting, 
enjoyable, and realistic stimulus for learning, but they also 
can provide a laboratory for the application of procedures 
taught elsewhere. This paper will discuss in detail one such 
application, the handling of an MRP system through the use 
of spreadsheet analysis. 
 
Background 
 
The course involved is Business Logistics and Channel 
Management, taught primarily at the undergraduate level. It 
is required of all marketing undergraduates, but is a common 
elective of undergraduate management science students as 
well. Given its resting place in the marketing curriculum, the 
course emphasizes the physical distribution components of 
logistics. However, the materials management components 
are covered to some degree. 
 
Like in most courses, a divide and conquer approach is taken 
as topics such as transportation, warehousing, location 
analysis, customer service, and inventory control are covered 
separately through lectures and through the use of cases. The 
topics are clearly not independent, though, as changes in one 
area usually have strong effects on the other areas. For 
example, it is not uncommon to see decisions such as the 
switch to a sturdier package reducing costs in areas such as 

transportation, warehousing, and materials handling. Cases 
can cover these tradeoffs to some extent, but frequently the 
tradeoffs either are not very explicit in the cases or they 
involve certain aspects of the course which have not been 
covered as yet. 
 
Thus, the systems nature of the distribution function requires 
the use of some type of integrative tool. Luckily, James 
Heskett developed such a tool when he was at Ohio State 
and some of his students have computerized the simulation 
game. The version of the Heskett game used in our courses 
was LOGSIMX [2]. The Game. LOGSIMX [2] was 
presented by DeHayes at the second ABSEL conference in 
Bloomington. It has been the most frequently cited logistics 
game at ABSEL conferences [5, 6, 7]. 
 
The game involves the production and distribution of 
Wondawata, an energy source that is substitutable for 
gasoline. Each world consists of four small markets, each of 
which is home to one of the four competing firms. In the 
geographic center of the world is a large market with about 
33% of the total demand, of which each firm has 25% of the 
market share initially. Each firm has approximately one-half 
of its home market initially, and approximately one-sixth of 
the competitors’ markets. There is no means of competing 
through marketing mix variables such as price, promotion, 
or product differentiation. Thus, product availability (both 
past and present) is the primary determinant of market share. 
 
Two modes of transportation are available for both raw 
materials and finished goods. Varying lead times and 
varying vehicle load configurations make production 
scheduling and the distribution of finished goods quite 
complex at the start of game play. The product consists of 
three components, which are needed in different quantities 
(but in constant proportion) for each unit of Wondawata. 
 
As inferred above, the production scheduling problem lends 
itself to an MRP approach. An insufficient number of 
component units has been ordered to meet gross production 
requirements in upcoming weeks. Consequently, all 
components need to be ordered by premium transportation in 
the short run, with lead times varying from immediate 
availability for two components to a week for the third. Lot 
sizing affects the lead times of regular transportation. 
Vehicle load lots are available with lead times of one to 
eight weeks, while less than vehicle load quantities increase 
lead times to two to eight weeks. Once the production levels 
are set (they must be set two weeks in advance of 
production, although they may be changed after that), 
determining how much of each component to order becomes 
a matter of making sure that four, eight, and twelve times the 
number of production units will be available for the 
respective components. Determining when to order becomes 
a function of using the lead times available for either of the 
two transportation types in vehicle load quantities. While 
most students adapt to this process over a few periods of 
game play, the use of a spreadsheet approach early can help 
the student organize the process more systematically and 
more 



Developments in Business Simulation & Experiential Exercises, Volume 14, 1987 

 180

simply, as well as providing immediate feedback on the 
result of various ordering decisions. 
 

MRP THROUGH A SPREADSHEET 
 
While MRP is presented in most management courses as a 
computer system, most student exercises are provided as 
small, hand-calculated problems. Moving the application to 
a spreadsheet emphasizes the “computer system” approach 
that is typical of MRP, allowing the student to provide the 
principal inputs, prepare the process, and readily make use 
of system outputs in decision making within the LOGSIMX 
simulation. 
 
Principal inputs to an MRP are the master production 
schedule and the bill of materials. In the case of LOGSflIX, 
the master production schedule, indicating the number of 
end items to be produced (units of Wondawata), is set by the 
student at least two weeks in advance, and can be forecast 
further using the general demand patterns provided. The bill 
of materials for each end item requires three dependent 
components- -eight units of Gelatin compound, twelve units 
of Exoticite, and four units of Heavy Water. Determination 
of gross requirements for each component is simply a matter 
of multiplying the independent demand indicated by the 
master schedule by eight, twelve, or four respectively, as 
indicated on the bill of materials. Determination of net 
requirements is normally accomplished using the 
relationship: 

 
Calculation of net material requirements is complicated by 
the fact that an insufficient number of units of each 
component has already been ordered for production in some 
of the coming weeks. Additionally, lead times vary 
depending upon the type of transportation chosen and the 
quantity ordered. Hence a new relationship is required: 

Implementation of the MRP master schedule and levels may 
be accomplished by the student using Lotus 1-2-3. Required 
competencies for building such a spreadsheet include: 1) 
Worksheet commands (setting column widths); 2) entering 
labels; 3) entering formulae; 4) pointing to and editing cells; 
and, 5) copying ranges. Knowledge of the @SUM, @MAX, 
and @IF functions provides for easier formula entry, a 
neater spreadsheet display, and possible error checking.1 
These competencies can be easily presented in one or two 
class periods so that students may develop their own 
template. An alternative would be to provide a template to 
the student, but this would be encouraged only when the 
simulation was used in a very limited time span. 
 
Adding each option for order placement (regular or premium 
transportation, vehicle-load or less than vehicle load 
quantities) and referencing these to the “Scheduled receipts” 
rows and cells corresponding to the lead time and previously 
placed orders will allow the student to make tentative 
“orders” and see the immediate results on the material 
requirements week by week. This approach avoids one of 
the potential pitfalls of beginning spreadsheet users in 
implementing an MRP template--circular references. Since 
the MRP approach proceeds backwards from the scheduled 
quantities and need dates and since the spreadsheet 
calculation moves forward, circular references are a 
possibility. The inclusion of the 
 
 
1Many of the “slash” commands, “@“ functions, and cell 
referencing schemes of Lotus 1-2-3 are common to other 
spreadsheet software (Visicalc) The Spreadsheet, MagiCalc, 
Supercalc, and others) such that the material presented here 
is not limited just to 1-2-3. 

 



Developments in Business Simulation & Experiential Exercises, Volume 14, 1987 

 181

rows for possible orders and for scheduled receipts tends to 
avoid circular references while encouraging the student to 
work toward zero or negative net requirements through their 
order placement. Figure 3 shows the output from a Lotus 
run. The inputs are concerned only with the raw material 
ordering (the lower half of each section), and the rest of the 
information is generated from that. 
 
Using a spreadsheet MRP implementation allows students to 
take advantage of the MRP outputs. Not only will the 
student be able to see a schedule of planned orders, but, if 
the template is properly designed, the student will be able to 
respond to changes in production levels in a more timely 
(and logistically less expensive) way. Students will find 
other applications of information from the MRP template for 
the LOGSIMX simulation. The MRP data alone provide 
potential information on “throughput” for the material 
handling decisions related to the sizing of the “raw material 
warehouse.” Including the calculation of transportation costs 
within the template would facilitate monitoring (“auditing”) 
of the LOGSIMX Financial Statement, as well as allowing 

students to see immediately the cost implications of their 
potential decisions. 
 
Students who become particularly adept at the use of 1-2-3 
may also extend the use of a spreadsheet to other aspects of 
the simulation which are numerically intensive, such as the 
forecasting of future production and the distribution 
function. 
 

PEDAGOGICAL APPROACHES 
 
Teaching the use of a spreadsheet in addition to the 
introduction of the LOGSIMX (and possibly other) 
simulation may seem to overburden an already heavy 
logistics curriculum. If students have been introduced to 
spreadsheet use in other (core) courses, little classroom time 
would be necessary to direct the implementation of an MRP 
spreadsheet. Presentation of the competencies required for a 
student with little or no Lotus 1-2-3 familiarity might require 
one to two 



Developments in Business Simulation & Experiential Exercises, Volume 14, 1987 

 182

class periods. An alternative would be to make a template 
available to those who might wish to make use of a 
microcomputer-based decision support system. While this 
alternative would mean less work on the part of the student 
and less classroom instruction time, it might also discourage 
the student from using the spreadsheet for other decisions 
related to the simulation. 
 

CONCLUSIONS 
 
Using Lotus 1-2-3 for an MRP implementation coupled with 
the LOGSIMX simulation provides the student a hands-on 
experience in operating an MRP system. The LOGSIMX 
simulation provides feedback as to the value of the MRP 
implementation, and the implementation allows the student 
to react to changes within the simulation. 
 

REFERENCES 
 
[1] Anderson, Phillip H. and Leigh Layton, “Integrating 

Personal Computers into a Course as a Decision 
Support Tool,” Proceedings, ABSEL, Reno, 1986. 

 
[2] DeHayes, Daniel W., Jr. and James E. Suelflow, 

Logistics Simulation Exercise (LOGSIMX), 
Unpublished Game Manual, Indiana University, 1971. 

 
[3] Dolich, Ira J., “Interacting Mainframe Computer 

Simulations and Microcomputer Information 
Systems,” Proceedings, 1984 American Marketing 
Association’ s Educators Conference. 

 

[4] Gentry, James W., “Teaching PERT Experientially in 
Marketing Research,” Proceedings, ABSEL, New 
Orleans, 1979. 

 
[5] Gentry, James W., “Group Size and Attitudes Toward 

the Simulation Exercise,” Simulation & Games, 11, 
December 1980 (No. 4), 451-460. 

 
[6] Jackson, George C., “Values for Selected Parameters 

in Physical Distribution Simulation and Games,” 
Proceedings, ABSEL, Reno, 1986. 

 
[7] Jackson, George C., James W. Gentry, and Fred 

Morgan, “A Computerized Logistics Game for 
Micros,” Proceedings, ABSEL, Orlando, 1985. 

 
[8] Jauch, Lawrence R. and James W. Gentry, “Interactive 

Simulation as a Supplementary Instructional Tool: Its 
Relation to Performance in a Business Simulation,” 
Proceedings, ABSEL, Knoxville, 1976. 

 
[9] Peters, Michael H., “Use of Simulation Administration 

to Achieve Pedagogical Objectives,” Proceedings, 
ABSEL, Dallas, 1980. 

 
[10] Shane, Barry and Jack Bailes, “A Decision Support 

System for Capital Funds Forecasting,” Proceedings, 
ABSEL, Reno, 1986. 

 
[11] Sherrell, Daniel L., Kenneth R. Russ, and Alvin C. 

Burns, “Enhancing Mainframe Simulations via 
Microcomputers: Designing Decision Support 
Systems,” Proceedings, ABSEL, Reno, 1986. 


	Table of Contents
	Volume 14, 1987
	Simulation Gaming as An Experimental Context for the Study of Multicriteria Decision Making
	How to Multiply Your Management Game
	Grading the Personnel In-Basket Exercise
	Simulation: The Players™ Perspective
	The Acute Susceptibility of Nominal Grouping to Negativity
	The Relative Value of the National ABSEL Meetings: An Analysis of Perceptions by Faculty and Deans
	A Survey of Behavioral Labs Used by American Business Schools
	A Structured Framework for Applying Conceptual Models to Problem Analysis: Hardening Up
	An Expert System Approach for Teaching Marketing Case Analysis
	From Theory to Practice: A Model for Teaching Beginning Advertising
	The Use of a Simple Forecasting Technique During an Interactive Computerized Business Game
	An Investigation of the Relationship Between Formal Planning and Simulation Team Performance and Satisfaction
	The Exponential Logarithm as an Algorithm for Business Simulations
	Learning macroeconomic Theory and Policy Analysis Via Microcomputer Simulation
	Laptop: A Principles of Marketing Simulation
	Teaching About Sampling in a Marketing Research Class
	Integrating Decision Support Systems and Business Games
	Demand Generation in a Service Industry Simulation: An Algorithmic Paradox
	ABSEL - At a Crossroads?
	Research on Predicting Performance in the Simulation
	An Expert System for Financial Planners
	"Developing Various Student Learning Abilities Via Writing, the Stock market Game, and Modified Marketplace Game in Beginning Macroeconomics"
	Professional Activity Reports: Getting back to Basics ΠA Preliminary Investigation
	"A Comparison of Performance, Attitudes, and behaviors of MBA and BBA Students in a Simulation Environment: A Preliminary Investigation"
	Decision Styles and Student Simulation Performance: A Replication
	Personal Power Strategies: An Experiential Exercise
	LTL I: A Trucking Simulation Game
	Total Enterprise Business Games: An Evaluation
	Can the Skill of Management be Taught?
	Developing Critical Thinking and Effective Communication
	Meaningful Notebooks and Enhanced Learning Using a Totally Menu-Driven Data base and Decision Support (DSS) Program
	Using a Computerized Menu-Driven Data Entry and fiWhat-Iffl Program as a Pedagogical Enhancement for Management Simulation
	Using a Joint Project Involving MBA Marketing Management and Undergraduate Marketing Research Students to Teach Marketing Research
	Testing the PAGE Technique: Results and Further Developments
	A Comparison of Student Perceptions with Accepted Expectations for Business Simulations
	"Conflict Management in Action: Verbal Strategies, Nonverbal behaviors and Conflict Styles"
	Dramatic Monologues as Surrogates for Experiential Learning
	Influencing One™s Superior: An Assessment and Exercise
	Personality Traits and Organizational Cultures: Two One-Hour Experiential Learning Exercises
	Computype: A Strategic Marketing Game
	Open System Simulations and Simulation Based Research
	"Teaching Forecasting to the Masses: Little Decision Support Training, But a lot of Answers"
	Goal Setting and Performance Evaluation with Different Starting Positions ΠThe Modeling Dilemma
	The Gamesmanship of Pricing: Building Pricing Strategy Skills Through Spreadsheet Modeling
	Teaching MPR Experientially Through the Use of Lotus 1-2-3
	Business Simulation Through Activities of a Manufacturing Company
	Simulated Stock Broker
	Teaching Employee Counseling Skills to Management Students: A Simulation
	Airline: A Strategic Management Simulation
	Developing Simulations and Experiential Exercises on the Personal Computer: Some Critical Issues
	A Powerful Tool ΠFor What?
	An Integrative Systems Approach to Simulating Free Enterprise in a Small Business Course in a Business School
	Profits: The False Prophet
	The Use of Expert Systems to Develop Strategic Scenarios: An Experiment Using a Simulated Market Environment
	An Interdisciplinary Workshop: Spreadsheet Modeling and Case Analysis Techniques for Beginning Executive MBA Students
	An Innovative Approach to an O. D. Class ΠThe Creative Interaction
	The Application of Spreadsheet Software Technology to Complete Taxpayer Elections
	Experiential Learning as the Cornerstone of an Industry Management Course
	"Identifying, Exercising and Enhancing Right Brain Skills in the Business Classroom"
	Utilizing Envisionary Technology in the Business Policy Course: An Experiential Pedagogy
	Business Simulation in the Policy Course: A Survey of American Assembly of Collegiate Schools of Business
	The Moving Van: Vehicle to Success of Disaster for the Two-Gender Work Force
	Oil and Gas Well Investment Analysis Using the Lotus 1-2-3 Decision Support System
	Team Cohesion Effects on Business Game Performance