Slides 2c: Using Spreadsheets for Modeling - Excel Concepts (Updated 1/19/2005)

1. Slides 2c: Using Spreadsheets for Modeling - Excel Concepts(Updated 1/19/2005) • There are several reasons for the popularity of spreadsheets: • Data are submitted to the modeler in spreadsheets • Data can be analyzed easily using statistical and mathematical tools readily available in the spreadsheet. • Data and information can easily be displayed using graphical tools. • However, many people are familiar with and have access to Excel at home and at work, but have not used it to create and analyze business models. A short review of key Excel functions used in modeling follows.

2. Basic Excel Functions and Operators • Arithmetic Operations • Addition of cells A1and B1: • Subtracting cell B1 from A1: • Multiplication of cell A1 by B1: • Division of cell A1 by B1: • Cell A1 raised to the power in cell B1: = A1 + B1 = A1 - B1 = A1 * B1 = A1 / B1 = A1^ B1

3. Basic Excel Functions and Operators • Relative and absolute addresses • All row and column references are considered relative unless preceded by a “$” sign • When copied, ‘relative addresses’ change relative to the original cell position.Example: Cell E5 =A1+B$3+$C4+$D$6 = C5+D$3+$C8+$D$6 Cell G9

4. Basic Excel Functions and Operators • The F4 key • Pressing F4 will automatically put a $ sign in highlighted portions of formulas. • Specific operations: • Press the F4 key once: The sign “$” appears in front of all rows and columns of the highlighted area of the formula. • Press the F4 key twice: The “$” sign appears in front of only the row references of the highlighted area of the formula. • Press the F4 key third time: The “$” sign appears in front of only the column references of the highlighted area of the formula. • Press the F4 key forth time: All the “$” signs are eliminated.

5. Basic Excel Functions and Operators • Arithmetic functions • Sum Function: =SUM(A1:A3) • Returns the sum A1+A2+A3 • Average Function: =Average(A1:A3) • Returns the arithmetic average of cells A1, A2, A3 • Absolute Value Function: =ABS(A3) • Returns the absolute (positive) value of the entry in cell A3.

6. Basic Excel Functions and Operators • Arithmetic Functions – continued • Square Root Function: =SQRT(A3) • Returns ÖA3 • Maximum Function: =MAX(A1:A9) • Returns the Maximum of the entries in cells A1 through A9. • Minimum Function: =MIN(A1:A9) • Returns the Minimum of the entries in cells A1 through A9.

7. Basic Excel Functions and Operators • Statistical Functions: • The RAND() function: =RAND() • Generate a random number between 0 and 1 from a uniform distribution. • Probabilities and variable values under the normal distribution • NORMDIST=NORMDIST(25,20,5,TRUE)Returns P(X<25) when m = 20, σ = 5 • NORMSDIST=NORMSDIST(1.0) Returns P(Z<1) when m = 0, σ = 1

8. Basic Excel Functions and Operators • Statistical functions – Other probability distributions • Poisson Distribution =POISSON(7,5,TRUE) • Returns P(X<7) for Poisson with l = 5. • Exponential Distribution =EXPONDIST(40,1/20,TRUE) • Returns P(X<40) for the exponential distribution with 1/m=20

9. Basic Excel Functions and Operators • IF Statement: =IF(A4>4,B1+B2, B1-B2) • Returns B1+B2 if A4>4, and B1-B2 if A4£4. • SUMIF Statement: =SUMIF(F1:F12, “>60”,G1:G12) • Returns G1+G2+…+G12 only if F1+F2+…+F12>60 • SUMPROD Statement: =SUMPRODUCT(B5:D5,B8:D8) • Returns (B5*B8)+(C5*C8)+(D5*D8)

10. Basic Excel Functions and Operators • VLOOKUP Function: =VLOOKUP(6.6,A1:E6,4) • If the values in column A of a given table [A1:E6] are sorted (in an ascending order), VLOOKUP finds the largest value in column A that is less than or equal to 6.6, identifies the row it belongs to, and returns the value in the fourth column that correspond to this row.Note: If the values in column A are not sorted, =VLOOKUP(6.6,A1:E6,4,FALSE) finds the value 6.6 in column A, identifies the row it belongs to, and returns the value in the fourth column that corresponds to this row.

11. Basic Excel Functions and Operators • Statistical • Data Analysis [Selected from the Tools menu]. • Descriptive Statistics • Mean: =AVERAGE(A5:A8) • Standard Deviation: =STDEV(A5:A8) • Moving Average • Exponential Smoothing • Regression • Many Others

12. Excel Competency (Pass/Fail) Exercise The objective of this exercise is to demonstrate your ability to use an Excel spreadsheet for a simple exercise demonstrating the savings of an individual at retirement, depending on such factors as amount saved, interest rate, etc. All necessary information is given. Your job is to type in the spreadsheet exactly as given below and follow the directions to complete the retirement projections. Also, once you are done, note the ease of changing the assumptions, and doing "what-if" (sensitivity analysis) on the result! The reason that this is easy is the way that we have shown you to separate the assumptions (parameters) from the formulas in the model. The beginning layout for the spreadsheet is given below. Once you have the correct formulas in row 12, simply copy that row down (drag, or use the copy command) through row 52. Print two versions of the spreadsheet - one with the data projections, and one with the formulas (select Tools, Options, and then check Formulas). Do not hand in a diskette. [Note: The row and columns labels above are just for your reference. They are already pre-coded in every Excel spreadsheet.]