Slides 2c: Using Spreadsheets for Modeling - Excel Concepts (Updated 1/19/2005) There are several reasons for the popularity of spreadsheets: –Data are.

Similar presentations

Presentation on theme: "Slides 2c: Using Spreadsheets for Modeling - Excel Concepts (Updated 1/19/2005) There are several reasons for the popularity of spreadsheets: –Data are."— Presentation transcript:

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.

4
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. Basic Excel Functions and Operators

10
–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. Basic Excel Functions and Operators

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.]