excel_lec

In short, the subject of engineering economics involves the ability for one to judge the attractiveness of proposed investments. Such analysis often take the form of Life Cycle comparisons. For example, we may wish to purchase one of two pieces of equipment. Item 'A' may cost twice as much as item 'B'. However, Item 'A' may have an expected life of 15 years while item 'B' may last only ten years. Furthermore, each item has its own unique operating and maintenance costs. Each item may also provide certain cost benefits in certain areas that the other does not. In order to decide which item to purchase, one must perform an economic analysis in such a fashion that each item can be assigned a monetary value, perhaps a present worth value, so as to allow an apples-to-apples comparison. An economic analysis can also provide other metrics of comparison such as Return-On-Investment and Benefit/Cost ratios.

Although this topic can become rather complex, we will look at an example of the integration of Excel and MathCAD using some basic principles of Engineering Economics. Specifically, we will look at the following four issues:

The present worth of a future value
The future worth of a present value
The present worth of a uniform series of payments
The present worth of a gradient series of payments

In all cases, the calculation can be performed with a rather simple formula. This formula is based upon the expected interest rate over the life of the calculation and the total term of that calculation. However, there are also tables of 'Compound Interest Factors' used to simplify some of these calculations. For example, we may want to determine the present worth of a future value of $20,000 fifteen years from now based upon an interest rate of 8%. The calculation is done by multiplying the principal by an economic factor from an appropriate table. We will use a spreadsheet to display tables of these factors. MathCAD will pass data to the spreadsheet, the spreadsheet will apply the hlookup( ) function to lookup an appropriate value based on interest rate and term length. Excel will return the value to MathCAD.

It can be argued that since the formulas are relatively simple, and that both Excel and MathCAD are quite capable of performing such calculations independently, it is unnecessary to combine the two. Although this may be true, the advantage of doing so is:

it allows this sheet to be a part of a larger worksheet in which it is desired to view active equations used to perform the calculations being performed. But it also allows the use of a spreadsheet to effectively and attractively display the factors used.
Since the spreadsheet already existed, it allows existing work to be used instead of being reinvented
it's instructional

The factor to determine the present worth of a future value is calculated as

where 'i' is the interest rate and 'n' is the length of the term. This factor is retrieved from a table and multiplied by the principle. Using the example stated above, the present worth can be written symbolically as:

PW = (P/F, 8%, 15) * $20,000

That is, present worth is equal to the present worth factor for 8% interest and a term length of 15 years multiplied by the principle amount of $20,000. This can be shown to equal 0.315 * 20,000 = 6,305. That is, the amount of $20,000 fifeteen years from now is worth $6,305 today based on an annual interest rate of 8%.

This is exactly the inverse of the present worth of a future value. The factor to determine the future worth of a present value is calculated as

where 'i' is the interest rate and 'n' is the length of the term. This factor is retrieved from a table and multiplied by the principle. For example, the future worth of a present value of $1000 invested for a period of 30 years at 6% interest can be written symbolically as:

FW = (F/P, 6%, 30) * $1,000

That is, future worth is equal to the future worth factor for 6% interest and a term length of 30 years multiplied by the principle amount of $1,000. This can be shown to be equal to 5.743 * $1000 = 5,743. That is, if you put $1,000 in the bank today at an interest rate of 6%, it would be worth $5,743 thirty years from now.

A uniform series of payments is simply making the same payment every year. The factor for finding the present worth of a uniform series of payments is calculated as

where 'i' is the interest rate and 'n' is the term length. This factor is retrieved from a table and multiplied by the principle. For example, suppose you invested $50 per month ($600 per year) for 30 years at an interest rate of 12%. This can be written symbolically as:

PW = (P/A, 12%, 30) * $600

That is, the present worth is equat to the present worth of an annual series factor for 12% interest and a term of 30 years multiplied by the principal amount of $600. This can be shown to be equal to $4833.

To take it one step farther, one can determine the future value of this money 30 years from now as follows:

FW = (F/P, 12%, 30) * $4833

This is would solve to be 29.96 * $4833 = $144,796. One should note another way to express this is:

FW = (F/P) * (P/A) * $600

A gradient series differes from a uniform series in that the amount invested per year increases uniformly. To determine the present value of a gradient series, one must first convert the gradient series into a uniform annual series, the determine the present value of the uniform annual series. The factor to do so is calculated as

For example, assume we wish to invest $100 per year into a 12% fund for 30 years. Furthermore, we wish to increase our annual investment by $100 per year. This is written symbollically as follows:

PW = (A/G, 12%, 30) * (P/A, 12%, 30) * $100

This solve to be 7.2974 * 8.0552 * $100 = $5,878.

The MathCAD worksheet developed to perform these calculations has the following features:

An Excel element used to calculate and display a table of the aforementioned factors.
Use of a vector to pass all necessary information to the spreadsheet in a single variable.
Use of conditionals and string concatenation to pass spreadsheet formulas to Excel.
The use of an Excel hlookup( ) function to pass an intermidiate solution to MathCAD.
A conditional formatting statement in Excel to highlight the intermediate solution.
The final calculation is handled by MathCAD using Excel's intermediate solution

In previous lectures, we've addressed issues of passing variables to and from an Excel component, the use of conditional statements inside of a programming structure, the use of Excel's hlookup( ) and vlookup( ) functions, and Excel's conditional formatting statements. Now let's address the use of MathCAD's string functions, particularly, string concatentation.

Virtually all programming languages employ some series of commands and functions to manipulate strings. This also carries over to programs such as MathCAD and Excel. For example, Excel employs no less that 27 different string manipulation functions. MathCAD employs approximately 10 functions dedicated to string manipulation. We've already seen one such command during the lecture on programming. The error(s) function is considered a string function since it is used to pass, to the user, the string contained in argument 's' as an error message.

The idea of programmatically manipulating a string is often a foreign concept to engineers and engineering technologist. We have a tendency and desire to concentrate on a numeric calculation to acheive some final solution to the problem at hand. However, string manipulation can be used effectively in the following scenarios:

To help automate the writing of a report
To allow a user to input English-like input and responses
To allow building a textual command (ie: to build a pathname to a file)
To build an Excel cell reference or function within MathCAD and pass it to Excel

Although other scenarios exist, it is the later upon which we will concentrate for this portion of the lecture. Very specifically, the concat(S1, S2, S3, ...) function. This function will take a series of string values and concatenate them, that is, add them together, to form a new string. The aruguments can be specific string values or they can be variables containing string values. For example:

Notice that assigning strings to variables allows for much more flexibility. For example, the string could be built using a series of variables from which to choose and a series of conditional statements. It is this technique we will explore in the worksheet below. We will use a conditional to construct a string representing a cell reference within Excel.

Of course, you already know a cell reference in Excel has the form '=A1' where the equal sign indicates a formula and 'A1' represents a cell reference. In the present case, the Excel Workbook inserted below as a MathCAD component contains four worksheets. The worksheets are named 'P|F', 'F|P', P|A', and 'A|G'. Within Excel, these worksheets can be referenced by name. The output to MathCAD MUST be a cell on worksheet #1. As such, we must able to request compound interest factors from any of the four worksheets and display it within worksheet #1 in cell 'A8' as specified. This address is constructed as a string by MathCAD. It takes the form '=sheet'!A1'. The syntax should be obvious. The equal sign indicates a formula. The expression 'sheet' is a string value representing the name of the sheet you wish to address. It MUST be a string. The exclamation point is a separator and the value 'A1' represents any cell we wish to address.

As described above in the introduction to engineering economics, the calculation of a present value of a gradient series must be accomplished in three steps: 1) find the equivalent annual series of a gradient series (A/G), 2) find the present worth of an annual series (P/A), 3) multiply these two factors together, then multiply by the principal to obtain a value. As such, this unique situation requires we extract two economic factors from Excel, the factor P/A and the factor A/G. At this time, you may wish to start Excel and open the workbook compound_interest_factors.xls. Review the worksheet named 'Discussion' for detailed information on the contstruction of the worksheet and how A/G is determined.

<= This conditional also checks if the user desire a P|G calculation. If not, the variable 'd' is assigned a numeric value of one (1). If the user does desire such a calculation, the variable 'd' is assigned a string value representing an Excel formula addressing cell 'B54' on worksheet 'A|G'. In the later situation, variable 'c' will contain the value of P|A, variable 'd' will contain the value of A|G, and the product will result in the factor for P|G as desired. In any other case, the value of 'd' is one, so the product of variables 'c' and 'd' will always equal variable 'c'.

The Excel component entered above was developed outside of MathCAD. It contains four worksheets as described above. Column 'A', which is generally hidden, is displayed so as to show the input and output cells and their values. Cells 'A1' through 'A4' receive input from MathCAD variable 'b'. They contain, in order, the principal, the term length, the interest rate (expressed as an integer, not a decimal), and the type of calculation desired (ie:P|A). Cell 'A6' receives input from variable 'c'. This variable is a string variable representing an Excel formula as described above. The cell evaluates the formula and displays the appropriate numeric value. Cell 'A7' receives input from variable 'd'. This variable takes on either a numeric value of one (1), or an Excel formula. Cell 'A8' provides output to variable 'a'. This output is a single number calculated as the product of the contents of cells 'A6' and 'A7'. Let's disect that.

As previously stated, cell 'A6' contains a formula constructed by MathCAD. This formula extracts a capital recovery factor from the spreadsheet and displays it. Furthermore, if the user specified a 'P/G' calculation, the MathCAD logic described above forces this cell to display a 'P/A' factor. Cell 'A7' very specifically contains one of two values, either a one (1) or the factor for the conversion of a gradient series to an annual series. This was also achieved through the MathCAD logic displayed above. As such, the contents of cell 'A8', which contains the Excel formula '=A6 * A7', will result in one of the following calculations:

(P/A) * (A/G)
(P/F) * 1
(F/P) * 1
(A/G) * 1

This value is returned to MathCAD as the overall compound interest factor to apply to the principal.

Now let's review the construction of the spreadsheet. It is best to open up the spreadsheet independently using Excel rather than double clicking on it within MathCAD.

Each worksheet of the workbook has exactly the same layout.

Cell 'B4' contains the title of the worksheet
Cell range 'B4:AE53' contain the table of calculated factors. This includes row and column headings. The row entries are formatted as percent, thus are decimal values.
Cell 'A2' and 'A3' on sheet 'P|F' contain the value of the term length and interest rate respectively as passed from MathCAD. This is explained above.
Cell 'A2' and 'A3' on sheets 'F|P', 'P|A', and 'A|G' contain references to cells 'A2' and 'A3' respectively on sheet 'P|F'. This allows a calculation of the appropriate compound interest factor for the respective sheet.
Cell 'B54' on each sheet contains an hlookup( ) function. Notice that columns 'D', 'F', 'H', and 'K' contain interest rates of 1.5%, 2.5%, 3.5% and 4.5% respectively. Also note that from column 'W' on, the progression of interest rates is from 16% to 18% to 20% to 25%, etc. In other words, column labels do not follw an arithmetic progression. However, row labels do. This is important. It means we CANNOT use vlookup( ) to lookup the appropriate factor.

The lookup function is written as follows: hlookup(A3/100,B4:AE53,A2,FALSE)

If you refer back to the development and/or assignment of 'b', 'c', and 'd', it becomes clear what happens.
The MathCAD logic results in the term length and the interest rate to be placed in cells 'A2' and 'A3' of the worksheet 'P|F'. We wrote a formula in the other sheets to duplicate these values. The lookup function determines the appropriate factor and holds it in cell B54 of each sheet. Cells 'A6' and 'A7' retrieve the value of cell 'B54' from the appropriate sheet. Cell 'A8' determines the overall compound interest factor by multiplying cells 'A6' and 'A7'. This value is returned to MathCAD.