June 24, 2007
June 24, 2007
June 27, 2007
12.1297.1 - 12.1297.13
Spreadsheet Techniques for Engineering Professors: The Case of Excel and Engineering Economics
This paper provides engineering professors with techniques for using spreadsheets to improve teaching. It focuses on how to use the software, rather than classroom dynamics, by explaining methods for applying Excel to engineering economics that can be used in other disciplines. It first discusses intrinsic functions, and then it develops custom functions that use notation familiar to a student, such as PF(i , n) for (P|F, i , n). Next it covers how to produce diagrams and graphics via the drawing toolbar and custom cut-and-paste libraries, as well as how to show all formulas and logic rather than just numeric results. Then it describes hiding and protecting answers used to check student work, and it addresses common notational issues such as subscripts, super- scripts, and Greek symbols. Finally, it uses forms to enhance the foregoing procedures.
Students are encouraged to use spreadsheets via numerous examples in textbooks, but what about professors? Do they use spreadsheets to solve routine problems, such as those given for home- work, and in turn encourage students to do so? Probably not. One obstacle is the differences be- tween spreadsheet notation and that of textbook or manual solutions. Similarly, drawing and la- beling diagrams can be time consuming. Another major concern is that of security: Copying and pasting computer solutions is even easier than the time honored tradition of manual cheating.
This paper examines the foregoing issues by first exploring simple solutions and then progress- ing to more advanced methods. The presentation is based on Microsoft’s widely used Excel soft- ware , but other systems can be used as long as they support user-written routines known as macros to extend their capabilities. For example, OpenOffice.org’s free Calc  system has most of the capabilities of Excel, but it uses a different dialect of Basic for its macro language. EIOf- fice  is an integrated office system with a spreadsheet very similar to Excel that uses Java to program its macros. Most engineers are familiar with engineering economics, so examples are given for that field. The approach, however, is applicable to other engineering fields, and several references [4-13] allow extending the material presented below.
Factors and Functional Notation
Spreadsheets have a wealth of built-in or intrinsic functions. Excel’s financial functions are com- putationally similar to factors used in textbooks, but notational differences sometimes slow learning. The Visual Basic for Applications (VBA) component of Excel allows custom or user- written functions that correspond clearly to factors. For example, Table 1 shows how easily the challenging (P|A,g,i,n) factor is implemented as an electronic factor or Efactor.
Use the Tools|Macro|Security menu to allow macros by setting security at a medium or low level. Then click Tools|Macro|Visual Basic Editor (or key Alt + F11) and click the editor’s
Ristroph, J. (2007, June), Spreadsheet Techniques For Engineering Professors: The Case Of Excel And Engineering Economics Paper presented at 2007 Annual Conference & Exposition, Honolulu, Hawaii. https://peer.asee.org/2288
ASEE holds the copyright on this document. It may be read by the public free of charge. Authors may archive their work on personal websites or in institutional repositories with the following citation: © 2007 American Society for Engineering Education. Other scholars may excerpt or quote from these materials with the same citation. When excerpting or quoting from Conference Proceedings, authors should, in addition to noting the ASEE copyright, list all the original authors and their institutions and name the host city of the conference. - Last updated April 1, 2015