XLDataTablesMonochrome2001

XLDataTablesMonochrome2001 - Tools for Excel Modelling...

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

View Full Document Right Arrow Icon
Tools for Excel Modelling Introduction to Data Tables and Data Table Exercises E XCEL R EVIEW 2001-2002
Background image of page 1

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

View Full DocumentRight Arrow Icon
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
Background image of page 2
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
Background image of page 3

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

View Full DocumentRight Arrow Icon
This page intentionally left blank.
Background image of page 4
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.
Background image of page 5

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

View Full DocumentRight Arrow Icon
Image of page 6
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 Right Arrow Icon
Ask a homework question - tutors are online