Engineering Finance
Computation
Excel Add-ins

The primary tool used for computation is Microsoft Excel. This program has the benefit of being widely available in academic, governmental and industrial institutions. It is often updated by Microsoft to take advantage of increased power in personal and networked computers. It is available for both Windows and Apple operating systems. At has a variety of built-in functions including several for financial applications. It includes a powerful programming language called Visual Basic for Applications, VBA. The VBA language has been used extensively by the author to program add-ins to Excel that implement many of the computational procedures covered by this course as well as a variety of other topics related to Operations Research, Operations Management and Industrial Engineering.

We assume students of this course have command of the common procedures used in Excel, so we do not provide instruction in this area. The site often provides screen shots of worksheets to illustrate the methods and concepts of the course. Screen shots are from the author's MacIntosh computer running Mac Excel 2004. Although there are some differences with the Windows version, they are minimal. The add-ins used have been tested on Windows Excel 2013 but should also work with Windows Excel 2016 and Mac Excel 2011 (but not Mac Excel 2016). Although not tested in every case, most add-ins will run on earlier versions of Excel. The instructions for the add-ins are designed for Excel 2003 and 2004, but Excel 2013 has a different menu structure. Click the link below for a discussion of the changes in Excel 2007.

.
Excel 2007 for Windows
.

This section provides discussions of aspects of Excel and Excel add-ins that may not be familiar to students. More complete discussions are provided on the ORMM and OMIE sites. There are links to relevant portions of these sites on this page and throughout the instructional materials.

Division

Add-ins

Excel add-ins are files with the ".xla" suffix. This is in contrast to a data file that has the suffix ".xls" or ".xlsx". This is important because although data files are opened by double clicking their file icons or names or opening the file through the File/Open command of Excel, add-in files should not be opened in this way. An attempt to open an add-in file will probably result in a security message from the operating system because add-ins contain macros. Macros are VBA programs that create worksheets, perform the algorithms and evaluate the functions of the add-in. The system might ask for a password when an add-in is opened incorrectly. If installed correctly, the add-ins do not require a password. For a discussion about installing and using add-ins see the Using Add-ins section of the ORMM site. Click the tree icon to open that site.

.
Using Add-ins
.

Add-ins must be installed to obtain their functionality. Install an add-in by choosing the "Add-Ins..." command from the Excel Tools menu. The add-in dialog similar to the one below is presented. The dialog shows add-ins immediately available. They can be installed by checking the adjacent box. If the desired add-in does not appear in the list click the Select button (the button is labeled Browse on Windows). This button allows the user to browse to the location where the add-in is stored.

Add-ins for the Course

Several add-ins are used in the course from the OM/IE and ORMM collections. Rather than attempt to explain them here, we provide links to the Jensen Add-in materials. The add-ins are listed in the order of use. The lessons explain the particular features of the add-ins that are necessary for this course. In the table below the first column opens a separate window to the instructions for the add-in. The second column downloads an Excel .xls file that is the demonstration file for the add-in. The third column downloads a zipped file that holds the .xla file that contains the add-in programs. The zip files must be un-zipped before using. Most computers do this automatically. The download processor may ask where the downloaded files should be placed. Any convenient location on the hard drive can be used to store the downloaded files, but it is most convenient if the add-in and demo files are all stored in the same folder.

 
Instructions
Demo
Add-in
   
.
OM/IE: Estimate Add-in
.
.
ORMM: Random Variables Add-in
.
.
OM/IE: Investment Economics Add-in
.
.
OM/IE: Project Management Add-in
.

Before opening a demo file, install the add-in as described above. To install an add-in through the Tools menu, you must browse to the folder that holds the add-ins. Open a demo file by double clicking the file or through the Excel File/Open dialog.

Although you do not need to use them, the Add OMIE and Add ORMM add-ins are handy for installing other add-ins.

 
Instructions
Add-in
   
.
OM/IE: Add OM/IE
.
.
ORMM: Add ORMM
.

Add-ins are available for many topics in Operations Research, Operations Management and Industrial Engineering. The links below open pages where the add-ins are available for download. You can download the entire collection of Jensen add-ins by clicking the archive link at the bottom of the ORMM page.

.
Download OM/IE Add-ins
.
.
Download ORMM Add-ins
.
Menus and Functions

Once an add-in is installed, a new menu appears on the Excel menu bar as shown at the left. The Economics add-in is in the OM/IE collection, so the add-in commands are listed under that title. Add-ins in the ORMM collection are listed under an ORMM menu item. Note that the word Economics is checked in the list. Clicking on that item removes the check and the menu items immediately below disappear. This is useful to shrink the length of the menu when several add-ins are installed. Clicking Economics again makes the items reappear. The items prefaced with the underline, "_", call various algorithms associated with the add-in. Not all the items on the menu are used in the course. The lessons point out the necessary ones.

 

Relink Functions and Buttons

When user-defined functions or buttons are used in a workbook, they are linked to the add-in that provides the function. No problem occurs when the workbook is subsequently opened on the same computer. If a workbook created on one computer is opened on another the linking problem must be addressed because each function or button will still be linked to the computer that created it. The Economics, Estimate and Project Management add-ins have a Links command that simplifies the problem. The command removes all functions and buttons before a file is saved and later replaces the functions and buttons when the file is reopened. All the demo files are saved with the links removed. Click the icon below to see how this command works for the Economics add-in. Similar commands are provided for the Estimate and Project Management add-ins.

.
Economics: Links Command
.

All homework submitted for grading should have the links removed using the Links command.

The Random Variables add-in has slightly different method for handling the linking problem. When a file with links opens, a message from Excel alerts the user. Always respond by Canceling the dialog. Use the Relink Functions command from the add-in to solve the problem.

.
Random Variables : Relinking
.

Excel provides the Edit/Links command that corrects all links reliably. Click the icon to open a window describing the command.

.
Relink Buttons and Functions
.
Summary
Excel add-ins programmed in VBA extend the capacity of Excel. They are used extensively in industry and are part of this course. It may take a little effort to get started, but the effort will be rewarded.

Division

 

Navigation

Return to Top of Page

Engineering Finance
by Paul A. Jensen
Copyright 2005 - All rights reserved

Lessons Front Page Contents Lessons Resources