This preview shows pages 1–7. Sign up to view the full content.
This preview has intentionally blurred sections. Sign up to view the full version.
View Full DocumentThis preview has intentionally blurred sections. Sign up to view the full version.
View Full DocumentThis preview has intentionally blurred sections. Sign up to view the full version.
View Full Document
Unformatted text preview: Excel Design & Audit Tips E XCEL R EVIEW 20012002 Spreadsheet models are a natural tool for managers but they can be hard to create from scratch and hard to understand and use if they're not well constructed. This short guide provides tips on how to create a welldesigned worksheet and also discusses some auditing techniques. Specific references are to Excel 2000. Paula Ecklund Spring 2001 Contents Page I. Getting Started .................................................................................................... 1 Inputs Outputs Relationships II. Data Entry and Organization ............................................................................... 2 Constant Values RowColumn Structure The Final Report Documentation III. Creating Formulas ............................................................................................... 6 Numbers in Formulas Reproducing Formulas Relative and Absolute Addressing Simple Formulas Range Names Intermediate Quantities Flow of Calculation IV. Auditing and Debugging Your Worksheet .......................................................... 9 See the Organization of a Large Worksheet View Detailed Information About Cells Use the Excel's Auditor to Correct Problems and Examine Cell Relationships V. For more Information ........................................................................................ 11 Using Excel's Online Help 1 I. Getting Started Before you begin building a worksheet, spend some time thinking about the data you're going to model and the problem you want to solve. Try to identify the inputs and outputs and the relationships among the data. Inputs Known Values Any known values or given data. These are quantities you don't control. Do you need any additional data to build the model? Decision Variables Quantities you control and can manipulate to optimize your model's solution. In some cases, decision variable values may be efficiently optimized by using tools like Excel's Solver. Outputs The Object What you're trying to solve, find, show, or optimize: the "bottom line". Any Constraints Limits to inputs or outputs, tradeoffs, conditions you must meet. Examples of constraints are budgetary limitations or limits to available resources. Relationships Relationships Between Variables Relationships between the known values and decision variables, expressed in formulas. Not all the relationships may at first be clear, especially in a complex problem. They may become more clear as you work through the development of your model. A Pattern Whether or not your problem fits into a "family" of problems for which you might employ a generic model structure. For example, problems dealing with resource allocation or scheduling have traditional formats you might want to adopt for your model. 2 II. Data Entry and Organization Organize your spreadsheet model around your data. Start building the model by entering the data you know about. You don't necessarily need to know just how you're going to use all the data you know about....
View
Full
Document
 Spring '01
 Ecklund

Click to edit the document details