INVESTMENT ANALYSIS APPLICATIONS USING IN HOUSE SPREADSHEET MODELS Developments in Business Simulation and Experiential Learning, Volume 25, 1998 INVESTMENT ANALYSIS APPLICATIONS USING IN-HOUSE SPREADSHEET MODELS Lee Tangedahl, University of Montana Timothy A. Manuel, University of Montana ABSTRACT This paper describes a spreadsheet package which has been used in investments classes as well as in computer application classes. The spreadsheet package is menu driven and (among other things) generates statistical and regression analysis and solves the quadratic equations necessary to find the efficient frontier. The program allows the student to easily apply the major results of modern portfolio theory in a realistic fashion using actual stock return data. INTRODUCTION We have designed a spreadsheet package which can be used to illustrate several quantitative investment analysis tools commonly encountered in investments classes. The package has also been used to demonstrate spreadsheet modeling techniques in computer application classes. Many of the security selection and portfolio evaluation techniques used by practicing investors today involve quantitative analysis measures. These techniques can be very difficult to illustrate in the classroom without computer aided tools. Various commercial packages provide some, although usually not all, of what we provide our students. Unfortunately in order to obtain the desired software the instructor may have to choose a specific textbook, or require students to purchase software in addition to the text. In some cases multiple software packages may have to be purchased. Choosing a book because of the software is potentially suboptimal and it may not be feasible to ask the students to purchase additional investment software packages. By developing our own software in-house we have been able to tailor the package to our needs and distribute it free of charge to our students. Upon request we will provide the software to interested parties. Employers often indicate that they desire to hire graduates who are familiar with computer tools and computer systems. They expect students to be familiar with certain basic packages and to be able to quickly adapt computer applications to new situations. The software package that we have designed allows the students to engage in investment applications and can be used by the instructor to illustrate structured problem solving with computer-based tools. The first section of this paper discusses the major functions of the software focusing on the investment applications which may be used in a finance class. The next section highlights how the package has been used to illustrate spreadsheet design methods using Excel 97 or Quattro Pro. This paper concludes with a brief discussion of our proposed additions to the software, however, we hope to learn of many new ideas and improvements at the upcoming conference. MAJOR FUNCTIONS OF THE SOFTWARE Setup and Basic Statistical Calculations The software has different components of varying complexity. Asset returns may be input by hand or imported from electronic data sources. We provide the students with a database of five years of monthly common stock returns for a sample of firms drawn from the CRSP tapes provided by the University of Chicago. This sample is updated yearly and placed on the network so that students have access to reasonably current data each year. They may select stocks by their ticker symbol and create their own database. The monthly S&P500 stock returns are also provided in the database. CRSP also provides equal and value weighted 9 Developments in Business Simulation and Experiential Learning, Volume 25, 1998 indices on their entire database, which may be easily included. The other indices could be used to illustrate differences in performance results based on varying benchmarks. Using the historical data, the package calculates the expected returns and standard deviations of individual assets and portfolios. This is used to illustrate the basic concept of diversification that underlies most risk pricing models such as the Capital Asset Pricing Model (CAPM) and the Arbitrage Pricing Model (APM).1 The program also calculates beta coefficients, correlations, coefficient of determination, and partitions systematic and unsystematic risk. These tools allow the students to evaluate for themselves how effective expost betas actually are (or are not) in measuring the riskiness of a security held by itself and held in a portfolio under various market conditions. The package can also calculate standard portfolio performance measures such as Jensen’s alpha, and the Treynor and Sharpe index numbers. These statistics purport to determine whether a portfolio or a mutual fund has earned a high enough rate of return to justify the riskiness of the portfolio. Jensen’s alpha is the actual average rate of return minus the minimum required rate of return on a portfolio of the given systematic risk. The Treynor and Sharpe performance measures indicate the return per unit of risk for a fully diversified portfolio and a less than fully diversified portfolio respectively. These measures are part of the financial analyst’s basic toolkit and the software package allows the instructor to quickly and easily demonstrate their use. Since mutual fund reporting procedures are quite unstandardized, these evaluation methods can be a valuable tool for the student to use for their own investments or for their clients’ portfolios. Another advantage of bringing computers into the classroom is the ease with which the instructor can highlight the sensitivity of the results to given variables. Simply by modifying the returns, perhaps by removing outliers, the instructor can highlight if/how the performance measures are affected. For examples, some of the beta measurement problems can be demonstrated by switching the reference index. Likewise, the instability of performance results over time can be illustrated by calculating the performance results using various subperiods of data. 1 These models and the majority of the investment concepts discussed in the article may be found in the investment texts cited in the bibliography. A Simple Financial Planning Model We also include a subprogram on financial planning. With the rapid growth of the financial services sector geared toward the individual investor, illustrating the planning concepts and uncertainties in a realistic fashion is increasingly useful. The typical financial planning model can be demonstrated by the decision of how much to save for retirement and where to put your money. More and more retirement plans are defined contribution, so most students will face these choices. We have students calculate how much money should be invested each month to provide a specified annuity for retirement after taking into account the effects of expected inflation in both the income producing and retirement years. Incomes normally rise during the earning years so we use a growth annuity to calculate the investment amounts needed. To illustrate where the money should be invested we present historical average rates of return on various investment types and calculate how the amounts invested vary with different investment classes. This tool is especially helpful in illustrating the sensitivity of the results to the assumptions used and to highlight the need to start saving at an early age. The method is very similar to what many professional financial planners use and students seeking employment in this area will already be 10 Developments in Business Simulation and Experiential Learning, Volume 25, 1998 familiar with applications of this type of methodology. Calculation of the Efficient Frontier One of the main conclusions of modern portfolio theory is the benefit investors receive from diversification of their investments. All investors should diversify, but how much diversification is enough? The efficient frontier describes the best diversified combinations that still can be expected to yield a given expected return level. The efficient frontier is derived by considering how different assets’ returns move relative to One another, i.e., the extent of the correlation of the assets’ return streams, and choosing the least correlated combinations while maintaining the portfolio’s expected return. The most sophisticated part of the program is the efficient frontier calculation. This procedure uses historical return data from any set of stocks, bonds or mutual funds, etc., and finds the best diversified combinations. The package finds the combination which provides the lowest risk at the given desired rate of return level (no short sales are allowed). We specify a step function for the desired rate of return and recalculate the optimum combination at each different return level. The result is graphed with a smooth curve with expected return on the vertical axis and risk on the horizontal axis. The solution points are the percentages of the total portfolio amount that should be invested in each security or fund. For example, the lowest risk alternative that will still yield a 14% per year expected rate of return may be to put 70% of your money in XYZ mutual fund, 20% in ABC long term bonds and 10% in GHI stock. The efficient frontier program requires a quadratic programming solution because of nonlinearity in the relationship between portfolio standard deviation and expected return for combinations of securities whose returns are not perfectly correlated. As a result, students have had difficulty implementing the major practical result of modern portfolio theory without a computer package. In the past we have been largely unable to give students practical experience in this technique because of the lack of software available to them. This problem is what initially motivated us to develop the software package. With the program, we can easily illustrate the benefits of adding securities to a portfolio in order to improve the relationship between risk and return by tracking how the risk is reduced at the given return level as more investment opportunities are added. Students can see and manipulate the correlation matrix. This is useful in emphasizing the critical effect the correlations exert on overall portfolio risk as more securities are added. We can also demonstrate approximately how many securities need to be added before the diversification benefits diminish dramatically. The systematic component of investment risk is highlighted by showing that some level of systemic risk is inevitable in an unhedged portfolio. if the instructor desires, the risk free rate can be introduced and the students can then find the optimal capital allocation line, estimate the optimal risky portfolio and the “market” price of bearing risk. These results can be used to illustrate the basic results of the CAPM concepts. SPREADSHEET DESIGN The securities analysis software is a series of spreadsheets which compute statistics, planning outputs, and the efficient portfolio frontier (with a graph) for a set of securities. The software was set up to read monthly return data (for up to 20 securities and up to 60 months) for any combination of securities selected from a large database created by the instructor. The software has been presented in two ways, first as a “canned” package with menus providing macro programmed control, and second as a “do- it-yourself” spreadsheet assignment. In the first case, no knowledge of spreadsheets is required; menus allow the student to select any combination of securities from the database and to display the 11 Developments in Business Simulation and Experiential Learning, Volume 25, 1998 desired outputs. A rather lengthy set of macro instructions reads the selected data into the spreadsheet, performs all the calculations (including repeated use of the solver to find weights for the minimum risk portfolios of the efficient frontier), and allows the student to view the results. One of the main menu options is a help file, which provides step by step instructions for use of the software. In the second case, the student creates the spreadsheets “from scratch” (although a starting template could be used) and manipulates that spreadsheet as desired, including use of the solver. This second approach allows the students some flexibility in tailoring the output to their own desires. The efficient frontier problem in particular is useful as a realistic application of advanced spreadsheet design models. A drawback to this approach is that the student needs some understanding of both spreadsheet applications and securities analysis concepts. Nevertheless, additional learning activities are available to the student who works on designing the program. For example, the student can graphically observe the performance of the portfolio while manually manipulating the weights. Depending on the training and skill level of your students, the advanced approach may require significantly more instruction in using spreadsheets than some instructors wish to provide. The software was first created in Quattro Pro 5 for DOS and is now being used as a student assignment in Excel 97. Excel 97 provides functions for the correlation and covariance matrices and provides matrix operations making computation of the minimum risk portfolio more straightforward than was possible in either Quattro or earlier versions of Excel. The increased speed of computers has also made it feasible to use data sets larger than 20 by 60 if the instructor feels this is desirable. PROPOSED ADDITIONS TO THE SOFTWARE We would like to add option pricing and options’ applications to this package. Students have difficulty calculating option prices easily because of the need to evaluate the cumulative normal density function at the points d1 and d2 in the basic Black-Scholes option pricing model. We would also like to add the capability to create option profit graphs for various option and stock and option combinations such as straps, straddles, horizontal and vertical spreads, covered calls, etc. It would not be difficult to include duration and convexity calculations and a contingent immunization methodology. These are tools which allow institutional investors to better estimate the amount of yield risk their bond portfolio is facing if interest rates change. Bond price elasticity with respect to interest rates increases with time to maturity and decreases with the size of the coupon. The two dimensions make it more difficult to characterize the yield risk an investor bears; as a result the concept of duration was developed. Duration is a modified measure of time to maturity, where the duration of a coupon bond is equal to the maturity of a zero coupon bond with the same yield. The use of duration allows yield risk to be modeled in one dimension and allows institutional investors to more easily measure and manage their exposure to changes in interest rates. For instance, contingent immunization is an advanced bond investing procedure designed to minimize losses if rates move adversely while still allowing realized yields to rise if favorable interest rate changes occur. REFERENCES Bodie, Zvi & Kane, Alex & Marcus, Alan (1993). Investments (2nd ed.). Homewood, IL: Richard D. Irwin Publishing. Radcliffe, Robert C. (1997). Investment Concepts. Analysis and Strategy (5th ed.). Reading, Massachusetts: Addison Wesley Educational Publishers Inc. Reilly, Frank K. and Brown, Keith C. (1997). Investment Analysis and Portfolio Management (5th ed.). Fort Worth, TX: Dryden Press. 12 Table of Contents Volume 25, 1998 Marketing Goes to the Movies Bringing Experiential Learning to a Principles of Marketing Course Investment Analysis Application Using In-house Spreadsheet Models SugarCoated Statistics: An Exercise for the First Day of Class Improving Undergraduate Student Involvement in Management Science and Business Writing Courses Using the Seven Principles in Action Establishment and Funding for Interuniversity / Multidisciplinary Student experiences The Prospects of Creative Teaching: A Discussion with Patricia Sanders The Simulation and Classroom Assessment Techniques Developments of Management Skill Assessment Games as Instruments of Assessment: A Framework for Evaluation The Role of Artificial Intelligence in Business Curricula Threshold Solo Competitor: A Management Simulation (V1.0) a Windows-Based. Play Alone, Total Enterprise Simulation and Assessment Instrument Toward An Understanding of One's self-concept Total Enterprise Simulations and the Internet: Improving Student Perceptions and Simplifying Administrative Workloads The Expatriate an Assignment Orientation Game An Expatriate's Nightmare: An Experiential Exercise in Coping with Overseas Assignments Analyzing Experiential Exercise: Using the Scientific Method for Problem Solving The Second Component to Experiential Learning: A Look Back at How ABSEL has handled the Conceptual and Operational Definitions of Learning Predictive Models of Learning: Participant Satisfaction of Experiential Exercises in Business Education Accelerating Moral Development through Use of Experiential Ethical Dilemmas Ethical Dilemmas to use with Business Simulations to Teach Ethics The Class Approach in Behavioral Simulation in a Business Policy/Strategic Management course: A Progression toward Greater Realism An Exploration of the Emergence of Process Prototypes in a Management Course Utilizing a Total Enterprise Simulation How Organizations Are Improving Their Performance Utilizing Electronic Commerce: Examples From The Internet The Market Access Planning System (Maps): A Computer-Based Decision Support System For Facilitating Experiential Learning In International Business An Excel Workbook For Student Planning And Interface With A Simulation Game Design Of Multi-Media Based Pedagogy For Leadership Training Panel Discussion On Using The Internet For Courses Valuing And Enhancing Teaching: Sharing Tips Via The Web The Buddy Project: A Semester Long Project Aimed At Developing An Appreciation For Diversity Enhancing The Excitement And Learning Retention In The Classroom: The Power Of Magic The Supervised Management Internship: A Job Or Learning Experience Team Ware™ An Online Moderated Class Discussion Facility And Beyond A Neophyte Distance Educator's Experience Learning Management By Practicing Management: A Report Of Significant Student Service In 1997 Integration Of Academic And Service Learning: Students' Perceptions About Its Effects And Outcomes The Value Of Incorporating A Service Learning Component Into Course Content: A Presentation And Roundtable Discussion Business Games Teach: Thoughts on the Sources of Conflicting Conclusions on their Effectiveness Antecedents Of Learning In The Simulation: A Replication Using Student Journals To Enhance Learning From Simulations Technological Change And Intertemporal Movements In Consumer Preferences In The Design Of Computerized Business Simulations With Market Segmentation Integrating The Marketing Curriculum Using Collaborative Learning Teaching Time Management In A Sales Program: The Application Of A Computer Simulation Game Adapting Interactive Computer Simulations For Content Based Esl Instruction Multimedia And Student Expectations Synthesizing Data For Media Simulations Composing A Team Health Promoting Behaviors-A Decision Making Exercise Does it really Work? An Application of the Group Interaction Framework Administering the MIT Beer Game: Lessons Learned A Paperless Economy? Instructing Students on the Aspects of Successful Electronic Commerce Maximizing Learning Gains in Simulations: Lessons from the Training Literature Observing General Ability in a Total Enterprise Gaming Simulation FReach Teach: A Computer-Based System for Teaching Advertising Media Planning An Integrated Business Instruction System An Experiential Exercise you can Tinker With Experiential Exercises or Computer Simulations? Cash Flow Statements: Are They Important in Business Simulations? Holistic Cognitive Strategy in a Computer-Based Marketing Simulation Game: An Investigation of Attitudes Towards the Decision-Making Process Barnga: A Game on Cultural Clashes The Many Faces of Culture: Understanding Country and Corporate Culture Students' View of the Use of Business Gaming in Hong Kong Assessing General Management Interest What is the Future of Business Gaming? Starting a Small Music Trivia Business Exercise and Other Innovative Icebreakers An Integrated Approach to Behavioral Skill Development Career Focus: A Student and Business Learning Experience The Use of Concept Mapping in Teaching Strategic Management An Experiential Approach to Developing Mission Statements Business Games in Brazil-Learning or Satisfaction A Simulation within a Simulation: Job Layoff's and Emotional Reactions