Spreadsheets: Enhancing learning application of engineering economy techniques

Spreadsheets: Enhancing learning application of engineering economy techniques

Alloway, James A Jr

Successful engineering projects must be both technically and economically feasible. Inadequate economic analysis results in suboptimal decisions which incur costs for an organization and ultimately, its customers. Increased competitive pressures, trends toward smaller and flatter organizations, and reduced time to market, place renewed emphasis on all engineers to perform accurate, timely economic analyses.

Despite these requirements, many engineers hesitate to apply formal time value of money techniques, Reasons encountered by the author while working in industry include: (1) they don’t use them frequently enough to be comfortable with them, (2) they perceive the methods as being too time consuming, (3) the methods are too complicated for practical application, (4) too difficult to get accurate cost data for analysis, (5) frequently revised estimates make it difficult to keep analysis current, and (6) some decision makers do not understand the results of the more formal analysis. Even among those who do use formal methods, few incorporate the effect of inflation or income taxes in their analysis, and almost none consider the uncertainty of their estimates.

Computers, coupled with appropriate software, will minimize or eliminate many of these problems. Educators must do more to incorporate effective computer analysis into the classroom to encourage use after graduation. This paper reviews the three major software categories and concludes that electronic spreadsheets currently provide the greatest benefit/cost ratio. The powerful modeling capability of a spreadsheet is demonstrated with an example.

THE BASIC PROBLEM

To improve the learning process, educators must help students develop the following skills: problem identification, model selection and formulation, assessment of model assumptions, accurate calculation of the final answer and meaningful presentation of results. While many engineering economy courses still stress the use of hand solutions, the previous goals are often better achieved with the use of the computer.

Appropriate computer use in the classroom enhances student learning and increases the likelihood of correct application of engineering economy techniques after graduation. Three items are required to assure positive computer experiences: (1) ready access to hardware, (2) technical support, and (3) easy to use software.

Hardware access is generally not a problem. Technical support to keep the hardware operating and properly configured will eliminate the primary cause of user frustration. Continuing decreases in hardware cost place software at the bottleneck in effective computer usage.

SOFTWARE CATEGORIES

The choice of software directly impacts the ease of learning, the development of problem formulation skills, and the transfer of skills to other courses and future careers. Software can be classified into three categories, each with its own costs (monetary and learning), availability and flexibility:

A. Pre-written application software (such as those purchased from a software vendor or those that accompany most engineering economy texts).

B. Custom software written by students in a programming language such as BASIC, FORTRAN, Pascal or C.

C. Productivity tools, such as electronic spreadsheets.

Pre-written application software is designed to accommodate a broad range of users and may not always address the specific requirements of every user. Such software accompanies many current engineering economy text books. The advantages of this approach are that it relieves the student of the drudgery of looking up values in tables and performing calculations by hand.

These advantages can also become disadvantages. The code is often proprietary and the logic and assumptions used are not shared with the end user. Price and user support vary considerably.

This “Black box” approach is generally unsuitable for learning a new subject since it does not give the student experience in formulating problems and does little to reinforce underlying concepts. The student is essentially reduced to a clerk, copying numbers from the book in response to prompts on the screen and waiting for the answer to appear. It hides all assumptions that go into the solution, and each package has is own set of commands and input requirements. If each course uses this approach, a student may have to learn 4 or 5 packages each semester.

Custom software provides an exact solution a specific problem and may be necessary if application software is not available. This is the “Glass box” approach: every minute detail of the operating system and language is painfully to the programmer and often overshadows the actual analysis. For students and others who will only solve a particular type of problem once or twice, the time and effort to code, debug and verify a program is seldom justified. With the exception of computer language classes, the course should focus on the subject matter, not the programming effort!

Development time is the longest of any approach, even for those students who have libraries of often used functions. Advantages include that the student must formulate the problem and is aware of all assumptions made and has great control of the output format.

Electronic spreadsheets (such as Lotus 1-2-3, Microsoft Excel or Quattro) can be considered a hybrid between these two ends of the software spectrum. They incorporate the advantages of each approach while avoiding the disadvantages.

Advocating the use of spreadsheets in the engineering curriculum has sparked some debate. Typically, the controversy focuses on issues of efficiency. For problems of the nature and size encountered in most economic evaluations, data requirements are minimal and any difference in computational efficiency will not be evident to the user. The “correct” software approach will depend on the amount of data the degree of similarity between problems, and the familiarity of the user with the software. The final results will be the same despite the approach chosen; only the level of effort required to reach them will differ.

SPREADSHEET ADVANTAGES

The electronic spreadsheet is frequently the best choice for both learning and applying engineering concepts. They are relatively inexpensive and further savings are available through educational versions at site licenses of all major spreadsheets.

They do not require extensive training in order to use them initially. They can be applied to almost every subject, spreading the learning time out over many courses, thus avoiding the need to traverse a learning curve for each new software package. Frequent use of spreadsheets develops familiarity, speed and confidence in applications.

Spreadsheets allow the end user to rapidly develop an application without getting inundated with the housekeeping details of programming languages. Input, output, graphics, printing, and file handling, which are major consumers of time in using custom written software, are built in. Frequently used functions, such as statistics and economic measures of merit, are also included. They relieve the student of the drudgery of number crunching, but still focus on problem formulation and assumptions. Once the model is set up, it is easy to modify for similar problems.

The structure imposed by the spreadsheet generally results in more complete problem formulation. Errors in logic or assumptions are often easier to detect. This insight is usually not as apparent when examining a hand solution and is totally lacking when using application software. Finally, the flexible nature of the spreadsheet makes it easy to correct the inevitable mistakes that occur during the learning process and still submit a neat final product. This flexibility makes it easy for the student to examine the problem from several points of view, which can enhance the learning of underlying concepts.

There are several characteristics that make a problem amenable to spreadsheet solution. Fortunately, the vast majority of engineering economy problems meet these criteria, which include:

a) Structured, repetitive calculations that can be expressed as formulas that rely on a few functional relationships.

b) Problems are similar, but not identical.

c) The parameters of the problem are subject to change.

d) The results and the underlying calculations must be documented.

e) Graphical output and control over the format of the graphs is required.

f) The user desires control of the format and appearance of the output.

SPREADSHEET VERSUS CLOSED FORM SOLUTIONS

Problem formulation with a spreadsheet requires a slightly different approach than the pencil and calculator approach. The emphasis is on the actual cash flows, rather than on assumptions that must be satisfied to use the closed form expressions given in text books. This emphasis on cash flows helps develop good modeling skills and a better understanding of the problem.

Spreadsheets provide functions (called (a)functions in Lotus 1-2-3) to simplify economic analysis problems. Functions include: (a) present worth of any cash flow ((a)NPV), (b) internal rate of return ((a)IRR), (c) ordinary annuities ((a)FV, (a)PV, (a)PMT, (a)TERM), and (d) depreciation functions ((a)DDB, (a)SLN, (a)SYD, (a)VDB). MACRS is available with the (a)VDB function.

Including inflation and income taxes in the analysis is very simple with a spreadsheet. A template is created once for the basic problem and then modified as necessary for the problem at hand. Skill in creating a flexible template minimizes future modifications. Again, the ease of use will encourage application of these tools in professional practice.

The spreadsheet is also capable of analysis that would be impractical with the pencil and calculator approach. Consider the situation where the analyst desires to specifically consider uncertainty in the analysis.

EXAMPLE

Monte Carlo simulation using a spreadsheet is illustrated with problem 15-20, page 568, from DeGarmo, Sullivan and Bontadelli [3]. The objective is to determine the expected present worth for an alternative when several cash flows are not known with certainty. The input area in Figure 1 summarizes the problem statement. (Figure 1 omitted)

The spreadsheet model consists of four regions for ease of construction, debugging, verification and future changes. The input area shows the distribution and parameter values far each cash flow type. The simulation area determines the present worth for each trial. The summary information area provides statistics that the modeler will use to reach a decision, and the random number generation area provides the values used in the simulation section.

Model construction begins with determining the measure of merit (PW) as a function of the constant (MARR and investment) and variable (life, salvage, annual savings and annual expenses) elements. Each random cash flow is modeled separately and is assigned to a column. This approach makes it easier to debug the model and verify that the results for each are reasonable. The summary statistics at the bottom of each column facilitate this process.

In Figure 1, the values of the random variables in one row are used to determine the corresponding PW. Thus, each row in the spreadsheet represents one trial in the simulation. The cells for the first trial (row 15) are highlighted in Figure 1. Once the results are verified for this single trial, the formulas can be copied to as many rows as needed to run the simulation. Trials 3 through 998 are not shown. Since recalculation may be time consuming for large models, selecting the manual recalculation mode is helpful. The results of the formulas are shown in Figure 1, while the formulas are listed and described in Figure 2. (Figure 2 omitted)

The unique formulas for the summary information area appear in highlighted cells in Figure 1, column B. This information is useful when verifying formulas to ensure that the values are in the expected ranges. The cells are copied to the right to complete the model.

The specific results for life, salvage, savings and expenses in Figure 1 are based on the random numbers generated in the model segment shown in Figure 3. (Figure 3 omitted) Cells I15 through M15 return uniform random numbers on the interval (0,1) and are denoted R1 through R5. The first four values are used with the Box-Muller transformation, to generate normal random variables in cells N15 through P15. R5 is used to determine the life in cell B15. Newer versions of some software packages, see for example [6] and [7], eliminate the need for the separate calculations in Figure 3. (Figure 3 omitted)

Once the simulation is complete, it is a simple matter to evaluate the results graphically. Built-in commands are used to create Figure 4, a bar chart of the present worth for 1,000 trials. (Figure 4 omitted) This figure gives a better impression of the distribution of PW for the investment than the summary statistics and can be useful to the decision maker.

Figure 5 shows the cumulative average present worth. (Figure 5 omitted) This is one approach that can be used to determine if the simulation has reached steady-state, or if additional runs are necessary.

ALTERNATIVE MODELS

New features are constantly being added to spreadsheets. Unless one can guarantee that all students have access to the most recent versions, any models used in class should be based on the least sophisticated versions. The preceding model uses only the functions available in Lotus 1-2-3 Release 1A. The current version of this package, Release 4 [6], in addition to Excel [7], includes many new functions that simplify the model. For example, the correction factor used in cell B25 is not needed if one uses the (a)STDS function. New statistical distribution functions, such as (a)NORMAL, eliminate the need for the separate calculations shown in Figure 3. The Lotus 1-2-3 Release 4 model formulas are shown in Figure 6. (Figure 6 omitted)

Add-in software for spreadsheets is also available. These packages operate in conjunction with the spreadsheet and incorporate features and functions for a specific type of analysis. (a)Risk [5] is one popular simulation add-in that greatly simplifies the preceding model. The entire (a)Risk model is shown in Figure 7. (Figure 7 omitted) The expected values are displayed in the shaded areas. No that it is no longer necessary to have a row for each trial. Simulations are run without consuming large portions of the spreadsheet, and specific analysis features not present in the spreadsheet are available. The highlighted cell formulas are shown in Figure 8. (Figure 8 omitted)

(a)Risk is powerful and easy to use. However, it does represent an additional cost to students, both monetarily and in terms of learning how to use it effectively. It also hides many of the features of the simulation from the student. Use of such add-ins for instruction purposes must be carefully weighed against the learning objectives.

The Lotus 1-2-3 Release 1.1 (Windows), Release 4, and (a)Risk (with DOS version 2.2 [4]) models were compared for 20 simulations of 1,000 trials each. Approximate run times on a 486DX-33Mhz machine were (a)Risk: 6 seconds, Release 1.1: 14 seconds and Release 4: 43 seconds. While the Release 4 model requires less space, it carries a high performance penalty. There is no significant difference (p = 0.604) in the average PW values between the three methods.

INCORPORATING SPREADSHEETS INTO THE COURSE

First and foremost, hardware must be readily accessible and working. Instructors must set the example by using computers and spreadsheets. For example, using the PC to solve problems during office hours encourages students to do the same. Many students who struggle to solve problems by hand are amazed to see the speed and simplicity of the spreadsheet approach. The spreadsheet also facilitates creating “do-able” test and quiz problems. Spreadsheets greatly reduce the time spent keeping track of grades and determining projected course grades based on work completed to date.

Spreadsheets can easily be incorporated into classroom sessions. One may use either transparencies of screens or an on-line computer/projection device, depending on available resources. It is often helpful to have two worksheet versions: one with cell formulas and one with numerical results. This allows one to switch back and forth between the sheets to show the overall model, results and detailed formulas.

Student satisfaction is increased when they have access to a condensed introduction/review of spreadsheet features. They also benefit by having the built in functions ((a)functions) that they will be using in the course identified and receive some orientation to their use. See Chapter 17 in [3] for an example. Another excellent resource is [2].

The author’s experience indicates that a supervised weekly 2 hour PC lab session, with teaching assistants knowledgeable in both engineering economics and spreadsheet use, improves both the student performance and their course ratings. Such a lab makes it easy to identify students who were unclear on course concepts and avoids many of the negative aspects often associated with PC labs. Also eliminates the problem of several students submitting the same homework.

CONCLUSIONS

Many poor decisions (such as make/buy and economic life) can be traced back to use of inappropriate justification techniques. Electronic spreadsheets provide the means for powerful analysis without requiring excessive demands on time. Instructors can expose students to a wider variety of problem types and more realistic situations.

Spreadsheets are readily available in almost every work environment. Graduates who become comfortable with economic analysis with spreadsheets will be more likely to apply it upon graduation, since the software tools will be the same.

REFERENCES

[1] Banks, Jerry and John S. Carson, II, Discrete-Event System Simulation, Englewood Cliffs, NJ: Prentice Hall Inc., 1984.

[2] Cobb, Douglas, Douglas Cobb’s 1-2-3 Handbook, Louisville, KY, The Cobb Group, 1986.

[3] DeGarmo, E. Paul, William G. Sullivan and James A. Bontadelli, Ninth Edition Engineering Economy, New York, NY: Macmillan Publishing Company, 1993.

[4] O’Leary, Timothy J., and Linda I. O’Leary, The Student Edition of Lotus 1-2-3 Release 2.2 User’s Manual, Reading, MA, Addison-Wesley Publishing Company-Benjamin/Cummings Publishing Company, 1990.

[5] Palisade Corporation, The Student Edition of (a)Risk Version 1.55, Reading, MA, Addison-Wesley Publishing Company-Benjamin/Cummings Publishing Company, 1991.

[6] User’s Guide Lotus 1-2-3 Spreadsheet for Windows, Release 4, Lotus Development Corporation, Cambridge, MA, (1993).

[7] User’s Guide Microsoft Excel Version 4.0, Readmond, WA, Microsoft Corporation, 1993.

BIOGRAPHICAL SKETCH

JAMES A. ALLOWAY, JR. is an Assistant Professor in the department of Mechanical, Aerospace and Manufacturing Engineering at Syracuse University. Dr. Alloway’s work experience includes 8 years of university teaching and 10 years in industry. He received a BS in Industrial Engineering from Northeastern University, a ME in Management Engineering and a Ph.D. in Decision Sciences and Engineering Systems from Rensselaer Polytechnic Institute. His research interests are in quality engineering, multivariate quality control and in improving the effectiveness of classroom instruction through the use of personal computers. He is the author of chapter 17 in the Ninth Edition, Engineering Economy, by DeGarmo, Sullivan and Bontadelli. He is a Senior Member of IIE and ASQC and a member of the Human Factors and Ergonomics Society and the American Statistical Association.

ACKNOWLEDGEMENT

The author gratefully acknowledges the helpful suggestions of the referees and Dr. Hamid Parsaei for improving this manuscript.

Copyright Institute of Industrial Engineers, Inc. Spring 1994

Provided by ProQuest Information and Learning Company. All rights Reserved.