A SIMULATION OF INVESTMENT ANALYSIS, PORTFOLIO MANAGEMENT AND REPORTING USING LOTUS 1-2-3


Developments in Business Simulation & Experiential Exercises, Volume 15, 1988 

 102

A SIMULATION OF INVESTMENT ANALYSIS, PORTFOLIO 
MANAGEMENT AND REPORTING USING LOTUS 1-2-3 

 
John R. Wingender, Oklahoma State University 

Steven Ball, Ford Motor Company 
 

ABSTRACT 
 
In this research we have designed a system to facilitate a 
portfolio management simulation in an Investments course. 
The Investment Decision Support System (IDSS) provides 
computer aided instruction for stock selection, modern 
portfolio theory implementation and performance 
evaluation, and investment report updates. The emphasis of 
the IDSS is to improve the students’ learning process. 
 

INTRODUCTION 
 
This paper presents an Investment Decision Support System 
(IDSS) that allows the student in Investments an opportunity 
to test and apply various finance models and formulas 
presented in the course. The students must state the 
investment objectives of their portfolio at the beginning of 
the semester. This approach encourages them to select 
combinations of equities which will help them obtain their 
objectives. Each student is given a fictitious sum of money 
at the beginning of the semester to construct a personal 
portfolio. This portfolio may consist of common stocks and 
cash. The cash can be held in a money market account. The 
students are provided with descriptions of theories and terms 
throughout the spreadsheet to teach them new ideas or to 
prompt their memory. 
 
Students have been constructing and managing simulated 
portfolios in Investment courses for a long time as a means 
of learning about the investment process. However, the 
traditional methods of simulating stock analysis, stock 
trading and portfolio management can have several 
drawbacks, which include: the validation and recording of 
transactions; expensive computer hardware and software; 
and a lack of all desirable features in an easy-to-use software 
package. For example, the investment simulation approach 
has usually been conducted in one of the following formats: 
 
1. The students are responsible for analyzing, selecting, 

monitoring and reporting their results. They are often 
permitted to use the reporting aid of their choice, i.e., 
mainframe, micro, PC, or by hand. Each time the 
student trades a stock or takes some action they must 
report it to the professor. The professor spends a lot of 
time recording and monitoring transactions. In this type 
of a situation professors often present the student with a 
limited number of stocks from which to choose their 
investments in an attempt to make oversight easier. This 
scenario creates a decision making environment that is 
unnecessarily artificial, thus diminishing the value of 

the whole process as a learning experience. 
 
2. There are several programs that are designed for 

mainframe computer systems, such as the PROSTRAT 
mainframe simulation by Gitman (1981) and Computer 
Aided Invested Analysis by Riley and Montgomery 
(1982). These computer programs require a mainframe 
system (such as a VAX or IBM), computer accounts, 
and computer familiarity and accessibility. These 
programs provide the advantage of emphasizing 
important concepts that a professor feels the student 
should consider while performing the assignment. 
While this attribute is desirable, the procedure can be 
expensive. There is also the problem of accurately 
recording and monitoring student transactions, as well 
as the inability of most of these systems to generate a 
detailed report. 

 
3. The third option that has been employed entails the use 

of a microcomputer in conjunction with an investment 
analysis software package like Modern Portfolio Theory 
on the IBM PC by Computer Handholders, Inc. This 
software package, which doesn’t have a reporting 
mechanism, costs $1,000. Many other software 
packages primarily have only reporting applications. 

 
SIMULATION OBJECTIVES 

 
The primary goal of this simulation is to increase the level of 
learning that the student attains from the assignment. 
Learning is a very broad concept. The learning objectives 
sought in the simulation are listed in the following chart 
which is adapted from Butler, Markulis, Strong (1985). 
 
Learning Objectives Description of Learning 
1. Basic Knowledge Student recalls or recognizes 

information. 
2. Comprehension Student changes information into 

a different symbolic form. 
3. Application Student discovers relationships, 

generalizations, and skills. 
4. Analysis Student solves problems in light 

of conscious knowledge of the 
relationship between components 
and the principle that organizes 
the systems. 

5. Synthesis Student generates unique 
communication, produce a plan 
or set of abstract relationships. 

6. Evaluation Student develops ability to



Developments in Business Simulation & Experiential Exercises, Volume 15, 1988 

 103

create standards of judgement to 
weigh evidence and analyze 

 
The performance of the proposed Investment Decision 
Support System (IDSS) can be evaluated with respect to 
these particular kinds of learning. The students participating 
in the simulation are not assumed to have been exposed to 
many of the investment and portfolio management concepts 
when they initially state the portfolio’s objective/strategy 
and begin to construct their portfolio. Therefore, the students 
are forced to read and to understand many of the facts, the 
terminology, and the concepts of Investments in order to 
create a statement of objectives, to simply work through the 
IDSS, and to construct a portfolio. 
 
It is hypothesized that the learning process is facilitated in 
terms of the students ability to understand and to retain (and 
only then having the potential to apply the idea or theory) 
through the application of an idea. This application can help 
the student to discover the relationships and skills necessary 
to apply the knowledge introduced in class. The application 
of Investment theory is required first in the statement of 
objectives/strategies for the student’s portfolio. The second 
application is in the construction of an investment portfolio 
which attempts to achieve the portfolio’s objectives. The 
third application is in the monitoring of the portfolio’s 
composition, the rebalancing of the portfolio’s individual 
assets and the analysis of the impact of additional 
investments on the portfolio’s risk-return characteristics. 
 
Dissatisfaction with simulations in economics is usually 
related to two key factors, design and implementation, 
according to Gold, Pray and Claridge (1985). The simulation 
approach with the IDSS encourages the student to consider 
the tradeoffs and relationships involved with investments. 
On the other hand, there may be a tendency for users of 
decision support systems to have too much trust in the 
information which comes out of the computer. It must be 
pointed out to the students that the IDSS is for storing, 
reporting, quantifying, and supporting their decision making, 
not for actually making the decision. No portfolio manager 
accepts these too]s as an answer unto themselves. Another 
consideration for stock valuation methods is the inability of 
quantitative analysis to incorporate the psychological 
momentum of the market which can play a significant role in 
stock price movements. The what if" scenarios and data 
tables in the IDSS are specifically designed to illustrate the 
importance of the underlying assumptions made in the 
models. 
 
Since a critical part of the learning hierarchy is synthesis, the 
student must produce a unique plan based on a set of abstract 
relationships with the IDSS. The IDSS requires the student 
to attempt this process through constructing, trading, and 
managing their portfolios according to the objectives they 
have set forth. Evaluations are based on the students' ability 
to maintain their portfolios in assets that fit their stated 
objectives profitably. Thus, total return to the portfolio is not 
the only criterion upon which the grade is based. 

 

THE INVESTMENT DECISION SUPPORT SYSTEM 
(IDSS) 

 
A desirable decision support system for investment 
simulation would be one that included the following 
capabilities: 1) a design to minimize the number of manual 
calculations; 2) an implementation on a micro- computer, as 
opposed to an expensive mainframe system; and 3) the 
incorporation of both stock analysis and portfolio 
performance reporting within the same applications 
program. Seyedian and Agazadeh’s (1987) approach 
reported in the 1987 ABSEL proceedings was the starting 
point to which this IDSS has added several different 
features. 
 
Students in Investment courses are exposed to many models, 
formulas and theories. However, the quantitative tools 
derived from these various theories are rarely applied by the 
students in a real investment decision making process and 
environment. The proposed Investment Decision Support 
System (IDSS) allows the student to simulate managing a 
portfolio of common stock and cash, to continue to update 
their portfolio positions, to easily calculate the current 
market value of their portfolio, and to report their progress. 
The specific aspects of the IDSS Include: 
 
1. Construct a portfolio. 
 
2. Evaluate the portfolio’s performance with respect to the 

‘market” during the investment horizon using the 
Standard and Poor’s 500 Composite Index as a 
benchmark. 

 
3. Manipulate the various inputs of the portfolio analysis 

in order to quantitatively illustrate the effects of various 
inputs and assumptions on the outputs; e.g., to calculate 
the beta (market risk) 
of an asset considered for inclusion in the portfolio and 
to calculate the beta of the portfolio if the investment is 
acquired. 

 
4. Save the student time by calculating the current market 

value of the portfolio and provide a report of the 
results. These functions of the IDSS allow the students 
more time to think about the whole process and, 
therefore, to gain a better understanding of the material. 

 
5, Illustrate the complexity of applying theories and 

models in an real investment decision making process. 
 
6. Test the potential use and value of different investment 

strategies. 
 
This IDSS was designed so that a person who knows nothing 
about Lotus 1-2-3 or spreadsheet programs can enter the 
data, perform the analysis, and run the reports successfully. 
A set of macros (automatic commands) has been used in 
constructing the IDSS to automate the routine data entry, 
report generation, and graph preparation functions. The main 
macro menu has several choices, including a data entry 
selection. When this entry is chosen it will invoke a macro 
that will prompt the student for the date. After accepting the 
date as a text string (an easy format to enter) it is converted 
to a date value. This allows for an ‘if” statement to compare 
the entered date with the internal date in the computer. If the 
two dates are not equal, then execution will return to the top 
of the Enter macro; i.e., the user will have to supply



Developments in Business Simulation & Experiential Exercises, Volume 15, 1988 

 104

the proper date or they will be stuck in a loop. Students then 
turn in portfolio updates each week. 
 
The IDSS procedures are straightforward. Below is an 
example of the operating instructions the students need to 
successfully use the IDSS. 
 
1. Insert a DOS diskette in Drive A and enter the date and 

time. 
 
2. When you get the A> prompt, insert Lotus System Disk 

(Release 2) and enter Lotus (or whatever the driver file 
is named), then press the Enter key. You will get a 
menu; select 1-2-3. Now you will be in the spreadsheet 
file and you are ready to begin (the automatic File 
Retrieval will execute the proper file). The spreadsheet 
begins with a statement concerning the purpose of the 
IDSS. After you read this information, press the PgDn 
key one time to proceed to the next step. The comment 
located at the bottom of the screen prompts this action 
by the students. (Note: All of the helpful comments are 
written in capital letters so that they are easy to see.) 

 
3. The Main Menu macro will appear with the following 

choices: 
ENTER ANALYZE PORT EVAL PORT PERIODIC 
ENTER DATA CALCULATE CALCULATE REPORT 
PRINT FORMAT PORTFOLIO 
GRAPH REPORT EVALUATION 

 
If you have undertaken any transactions or the current 
market value of your stock has changed, select Enter 
and press Return. 

 
4. The computer will ask you to enter the date. Enter it in 

the space provided in the following format: 04/21/87 
(MM/DD/YY). If you do not enter the proper date, the 
program will not continue any further. 

 
5. When the proper date has been entered, the cursor will 

move to the data entry portion of the worksheet. Input 
the requested data just like the example. When all the 
data have been entered, the computer will ask you if the 
data are OK; press Return to continue if the data are 
correct. If not, select No then press Return and the 
cursor will return to the data entry portion of the 
worksheet allowing you to change the incorrect data. 

 
6. If you would like to see the results of your portfolio 

progress to date, choose Analyze Port from the Main 
Menu. 

 
7. To evaluate your portfolio’s performance with respect 

to the market, select Portfolio Evaluation. The cursor 
will move to an area of the worksheet that explains the 
Sharpe, Treynor, and Jensen measures of performance 
evaluation. Read these statements, making sure you 
understand the concepts and enter the requested data in 
the spaces provided. When this portion of the 
spreadsheet is completed, press the Return key and the 
cursor will return to the Main Menu. 

 
8. To evaluate the performance of your portfolio, select 

the Eval Port branch of the menu. Step 7 must be 
completed before this step can be used. Read the 

comments and enter the data requested in the blank 
spaces. The output variables will automatically be 
calculated. 

 
9. The student can easily obtain a periodic report of the 

vital statistics on their portfolio to date. To print this 
information, select the Periodic Report branch and 
invoke the macro by simultaneously pressing the Alt 
and P keys. Now the first disk of the IDSS has been 
completely utilized. 

 
The second disk includes some additional analysis that 
requires more data input. Insert the disk in Drive B with 
Lotus in Drive A. Press the Home key to get back to the 
beginning. Read the information and press the PgDn key to 
further proceed. Get comfortable with the matrices of 
variance-covariance so that you will understand the output 
for your variance-covariance matrix. Enter the number of 
stocks in your portfolio, then go to cell Gi. Input the 
historical prices of the stock. When all of the data are 
entered, check it for accuracy. Then proceed to the variance-
covariance matrix to view the output. Underneath the matrix 
are the formula and the answer for the variance measure of 
your portfolio risk. 
 
Now move to AM1 to analyze the systematic risk of a stock 
by constructing a characteristic line. Enter the data for a 
particular stock and the S & P 500. Then simultaneously 
press the Alt and C keys to invoke the characteristic line 
macro which will calculate and graph the security’s 
characteristic line. If you want to perform this analysis for 
other stocks, enter the new data in the same spot. Make sure 
to print your results before you change the data if you would 
like to save them. When you have calculated the 
characteristic line for each stock desired, go to cell Gi. 
Simultaneously press the Alt and R keys to print a report of 
the portfolio risk. All of the IDSS has now been completely 
used. This IDSS will enable students to store, calculate, 
graph, evaluate, and report the results of their portfolios, as 
indicated by the remaining Main Menu options. These 
capabilities are all attractive features of the IDSS. 
 

CONCLUSION 
 
The IDSS has been designed with the purpose of 
constructing an “effective” pedagogical tool for the student 
in an Investment course. The IDSS can be evaluated 
according to the following criteria: 1) the primary objective 
of facilitating learning through the investment simulation 
approach; 2) the secondary objectives (which are interrelated 
with and facilitated by the primary objective) of low cost, 
ease of use, convenience for instructors, and time-saving 
benefits; and 3) the effectiveness of the design and 
implementation of the IDSS as an appropriate method for 
advancing learning. It is important to also consider the 
manner in which the instructor has integrated the IDSS as a 
part of the overall learning process in the Investment course. 
 
This paper has explicitly defined the objectives of the IDSS, 
the criteria used in its design and implementation, and its 
limitations. These factors should allow for further research 
on the effectiveness of the IDSS as a learning tool, its design 
guidelines, and ability to test the theoretical constructs of the



Developments in Business Simulation & Experiential Exercises, Volume 15, 1988 

 105

concepts of learning through computer-based simulation. 
Now that it has been designed, the next step for the IDSS is 
for its implementation into the coursework. 
 

REFERENCES 
 
Butler, Richard J., Markulis, Peter, and Daniel R. Strang. 

(1985), Learning Theory and Research Design: How 
has ABSEL Fared?” Developments in Business 
Simulation and Experimental Learning, Volume 12, 
86-90. 

 
Gitman, Lawrence J., Aberderrahman, Robana, and William 

Biggs. (1981), PORTSTRAT:  A Portfolio Strategy 
Simulation. New York, NY: John Wiley and Sons. 

 
Gold, Steven C., Pray, Thomas, and Suzanne F. Claridge. 

(1985), ‘Criticisms of the Use of Simulations in 
Economics: A Rebuttal” Developments in Business 
Simulation and Experimental Exercises, Volume 12, 
91-95. 

 
Riley, William L, and Austin H. Montgomery. (1982), Guide 

to Computer Assisted Investment Analysis. New York, 
NY; McGraw-Hill. 

 
Seyedian, Majtaba, and Seyed-Mahmoud Agazadeh. (1987), 

‘Simulated Stock Broker.” Developments in business 
Simulation and Experimental Exercises, Volume 14, 
186-187. 


	Table of Contents
	Volume 15, 1988
	The Role of Experiential Knowledge and Human Information Processing in Decision Making
	A Semantic Differential Instrument to Evaluate Experiential Teaching Methods
	A Comparison of Two Approaches to Management Skill-Building in an Organizational Behavior Course: A Replication
	Integrating Simulations: A Model for Business Policy Success
	Capstone Renaissance = Simulation + Interaction + DSS
	A Hybrid Method of Executing a Management Simulation: Combining the Best of Mainframes and Microcomputers
	Providing an Experiential Dimension to Cost/Managerial Accounting Courses
	Utilization of Computerized Tax Research Services in the Tax Research Curriculum
	Using and Expert System Based Decision Aid in Accounting Information Systems
	Event-Extended Entity-Relationship Diagrams for Understanding Simulation Model Structure and Function
	Multiple Objectives in the Development of the Gordon Macro Game
	A Comparative Study of Strategic Performance Factors in Actual and Simulated Business Environments
	An Empirical Investigation of Integrated Spatial-Proximity MCDM-Behavioral Problem Solving Technology Group Decision models
	Computer Simulation of Human Interaction
	The Role of Experiential Learning and Simulation in Teaching Management Skills
	Expert Systems - The New Business Simulation Tool
	Integrating Prolog into and Undergraduate Logistics Course
	Simulating Material Requirements Planning on Lotus 1-2-3
	Innovation in Management Education: The Impact of the AACSB
	Experiential Learning in the International Environment
	Educational Testing with the Microcomputer
	A Simulation of Investment Analysis, Portfolio Management and Reporting Using Lotus 1-2-3
	The Use of an Expert System to Develop Strategic Scenarios
	Two Exercises for Teaching about Motivation
	Sex Roles and the Good Manager
	A Form and Process for Nonconfidential Peer Evaluations
	Simulation and the Recalcitrant Student
	Employee Rights-Student Rights: A Classroom Exercise
	Computer Simulated Competition: An Alternative to Team Play Management Simulation
	The Relationship of Locus of Control and Vividness of Imagination Measures to Simulation Performance
	Formal Planning, Simulation Team Performance, and Satisfaction: A Replication
	Experimental Analysis of Magnitude and Source of Students' Inequitable Classroom Perceptions in Three Reward Conditions
	Strategy Design, Process and Implementation in a Stable/Complex Environment: An Exploratory Study
	Matching a Strategy Simulation to the Business Policy Literature: A Black Box Approach to Simulation Development
	An Evolutionary Classroom Experiential and Computer Simulation Model of a Corporate Strategic Planning System
	Collective Bargaining in the City of Elson: A Public Sector Experience
	Should Students Play Games in Labor Relations?
	Applying Cognitive Educational Objectives to Business Management Cases
	Grading as a Teaching and Feedback Mechanism: Involving Students in the Grading Process
	Teaching Controversies: A New Approach to Computer-Assisted Instruction and Simulation Packages
	Simulating Demand in and Independent-Across-Firm Management Game
	Advertising Response in the Gold and Pray Algorithm: A Critical Assessment
	A Model for Pricing Decisions in First Period Marketing Simulation Games
	Jog Your Right Brain: An Exercise for the Classroom and for Research
	Six Thinking Hats: An Exercise to Combat Confusion and Develop Thinking Skills
	Communicating in Context: A Simulation for Learning Business Communication
	A Simulated Consulting Service for the Compete Marketing Simulation Game
	Action Exams in the Consumer Behavior Class
	Using Focus Groups to Teach Problem Definition in Basic Marketing Research
	The Use of Journals in Management Simulations: A Literature Review and an ABSEL Response
	An Initial Step Towards Developing and Using an Expert System with a Business Simulation
	Self-Managed Learning: An Experiential Course Design Using the QWL Paradigm
	A Review of Current Developments in Experiential Learning
	Bring the Real World into the Classroom
	Assessing Student Performance on a Business Simulation Experience
	Minimizing Startup Anxiety: Case Studies of Simulation Experiences
	A Tale of Two Shepards Or Using Simulation in a Class Without Walls
	Enhancing Business Simulations Through the Utilization of Experiential Activities Involving Local Community Executives
	Simulation with Integrated Spreadsheets: The Design and Development of a Conversational Marketing Concepts Decision Game
	Introducing INMART: An International Marketing Simulation
	Using a Computer-Based Business Plan Assistant in Conjunction with a Marketing Simulation Game
	Overview of the ABSEL Guide to Experiential Learning and Simulation Learning