mbpracticeusingspreadshee(编辑修改稿)

mbpracticeusingspreadshee(编辑修改稿)内容摘要：

fonts, bold to distinguish allows universal change in appearance of a class items . assumptions, data, output.  Cell protection to prevent users from changing critical cells. 26 Helping users ( and yourself )  Use borders, underlining and shading rather than gridlines to make reports clearer.  Automate mon operations  macros with buttons or key strokes Don’t reassign standard key strokes . Ctrl P, ctrl S etc. 27 Quality Control  Plan spreadsheet before building them  Implement Using sound approaches  Test them with data  data with errors  limits of normal range  Code inspection  Get some else to audit them 28 Conclusion  Design stage sets the standard recovering from bad initial design is difficult  Data flow approach  Layout standards  Clear formulas and documentation  Make the finished product easy to use 29 Good Practice in building models  Evidence of Poor Quality in Spreadsheets  Criteria of good design  Correctness  Flexibility  Manageability  How we achieve good design  Planning using dataflow  Good Practice 30 Model Building Using Spreadsheets Presented by: Andy March 1998 Coaton 31 Model Building Exercise Using Excel Presented by: Andy March 1998 Coaton 32 Model Building Exercise  Build on simple DCF of ITNET  Create Multidimensional Scenario Manager  Create Sensitivity Analysis  Naming, functions, macros, data tables, range checking, plus others 33 Excel Knowledge 1. One sheet model with formulas 2. Functions 3. Macros or Multisheet workbooks 4. Naming, styles, data tables, charts, scenario manager. Load c:\marbella\MB\ 34 Naming  Naming variables easier to read than cell refs  Also name ranges  Use of Named ranges  Ensures that values are in the same column everywhere the range is used  Allows you to refer to the range by name  Click on data!B15  insertnamesdefine type in the name ‘Capex_1997’, ensure ‘refers to’ has correct cell references OK  Depreciation!D15: =capex_1997 35 Multidimensional Scenario Manager Driver Scenarios Inflation Scenarios Assumptions Selectors 36 One dimension Driver Scenarios Assumptions Selector Assumptions Scenarios 37 Build one of the dimensions  The parameter heading range is Named ‘scenario_parameters’  Test  Editgotoscenario_parameters  Highlights the headings 38 Build Driver Scenario Table New sheet called ‘scenarios’  insert worksheet  double click on tab and name it ‘scenarios’  Create Driver Scenario table top corner B8  Don’t forget our named range 3 mins 39 One dimension Driver Scenarios Assumptions Selector Assumptions Scenarios 40 Driver Scenario Table  Create Driver Scenario table top corner B8  cell B8 ‘=scenario_parameters’  copy to cells C8 to I8  Copy format highlight assumptions!b14:I14 editcopy on scenarios!b8 editpaste specialformats 41 Driver Scenario Table  Each row represents a scenario  See sheet SPECIAL for the output from Market Analysis session copy the data into your table  Name the table containing the scenario rows including the headers B8 to I12 as ‘scenarios’  insertnamesdefine type in the name ‘scenarios’, ensure ‘refers to’ has correct cell references B8:I12 OK 42 Create Range Check  Make easy to use and reduce errors  Check that parameters are in valid range  Example Competitive Advantage。