XLDataTablesMonochrome2001

# XLDataTablesMonochrome2001 - Tools for Excel Modelling...

This preview shows pages 1–6. Sign up to view the full content.

Tools for Excel Modelling Introduction to Data Tables and Data Table Exercises E XCEL R EVIEW 2001-2002

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
Preface Data Tables are among the most useful of Excel’s tools for analyzing data in spreadsheet models. Some spreadsheet users shy away from using Data Tables because they don’t understand how Data Tables work or how to create them. In fact, Data Tables are simple to construct and use and are well worth the effort to learn. The XerTech example used here is drawn from a text used frequently in Fuqua’s Decision Models course: “Introductory Management Science: Decision Modeling with Spreadsheets”, by Epson, Gould, Schmidt, Moore, and Weatherford, 5 th edition. To get a copy of the XerTech Exercise.xls workbook mentioned on page 3 of this handout go to this URL: http://faculty.fuqua.duke.edu/~pecklund/ExcelReview/ExcelExercises.htm Paula Ecklund Spring 2001
Contents Page Preface Introduction . ................................................................................................. 1 The One-Variable Data Table: Basics How to Create a One-Variable Data Table. ....................................... 2 A Simple One-Variable Data Table Example. .................................... 3 Practice Creating a One-Variable Data Table . .................................. 4 The One-Variable Data Table: Modifications and Refinements Modifying the One-Variable Data Table. ............................................ 9 Making the One-Variable Data Table More Powerful by Adding Additional Formulas . ................................................... 10 Data Table Formatting Note . ............................................................. 10 An Extension: Create Scenarios for Key Values . .............................. 12 The Two-Variable Data Table . ..................................................................... 16 Graphing the Results of a Data Table Analysis. ........................................... 17

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
1 Introduction Data Tables are a tool used frequently in Excel models to track how small changes in inputs affect the results of formulas in your model that are dependent on those inputs. For example, you might be interested in knowing how changes in the price your firm charges for an item affect the firm’s net income. An analysis of this sort is often termed a sensitivity analysis . Excel has two varieties of Data Table: The One-Variable Data Table The Two-Variable Data Table Both varieties work in a similar fashion. You identify one or two key input variables in your model and describe the range of values you want those inputs to take on. Then you identify one or more formulas in your model that are dependent on those inputs. When you execute the Data Table command, Excel then iterates through a process of executing each formula you’ve identified, substituting in each formula each one of the values you’ve identified for the key input variables, and recording how the value changes change the results of the formulas. The One-Variable Data Table allows identification of a single input variable but an unlimited number of formulas. The Two-Variable Data Table allows identification of two input variables but only a single formula. The layout of your Data Tables is important and must follow Excel’s rules for Data Tables.

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
This is the end of the preview. Sign up to access the rest of the document.

## This note was uploaded on 12/20/2011 for the course BUS 101 taught by Professor Ecklund during the Spring '01 term at Duke.

### Page1 / 25

XLDataTablesMonochrome2001 - Tools for Excel Modelling...

This preview shows document pages 1 - 6. Sign up to view the full document.

View Full Document
Ask a homework question - tutors are online