Courses/Computer Science/CPSC 203/CPSC 203 Template/Labs Template/Week 2 - Lab 2: Spreadsheet Design Rules

From wiki.ucalgary.ca
Jump to: navigation, search

Introduction

Today's tutorial continues to build on the visual elements to spreadsheet design, introduced in the last tutorial.

Spreadsheet Design Rules

  • Some basic rules about design
  • Parts of a well designed spreadsheet

Some Basic Rules about Design

  1. Design the spreadsheet on paper first. Graph paper often works well.
  2. Test and edit your calculations. Where appropriate use intermediate calculations and check-sums to ensure calculations are correct.
  3. Keep the components of a calculation visible. No "magic numbers". Place fixed numbers used in a calculation in their own cell with a descriptive title.
  4. Be aware of the "space" or "geography" of the spreadsheet. Arrange your information so that it is well spaced and easy to take in at a glance.

Parts of a Well Designed Spreadsheet

Adapted from: The Elements of Spreadsheet Style. 1986. By J.M. Nevison

These parts can be considered components of a disciplined approach to building spreadsheet so they are self-documenting. The parts could each be in their own sheet, or they could be in a single sheet.

  1. Introduction -- What is this spreadsheet or workbook about. Note the title, purpose, author, creation and revision dates etc.
  2. Model and Assumptions -- Justify any models, summary statistics, or calculated variables you are using.
  3. Data Dictionary -- For every variable in the spreadsheet note: its
    1. Location (cell range),
    2. Name,
    3. The Data Class it is (Raw Data, Statistical Summary, Calculated Variable, Score etc.),
    4. Data Type (e.g. Integer, Text, Currency, Date, etc.) and
    5. Description (a description of the data or what it's 'purpose' is).
  4. Raw Data -- Present your raw data in tabular form -- with columns representing variables and rows representing cases.
  5. Calculated Data
    1. Summary Statistics -- Usually Summary statistics result from calculations across rows for a single column.
    2. Derived variables are often based on calculations across columns for a row.
  6. Presentation (Reporting)
    1. Emphasize the final information you wish to show without excessive background details.
    2. Use charts wherever appropriate to summarize large volumes of data.

Example

The following example shows incremental improvements, working towards good spreadsheet design.

Part 1

In this first iteration, two separate sheets are used:

  1. data
  2. presentation

Media:SpShtLec3_PresidentsDataAndAnalysis_1_20080205.xls

CPSC203 Template Spreadsheet Image 25.png

CPSC203 Template Spreadsheet Image 26.png

Part 2

In this second iteration, five separate sheets are used:

  1. introduction
  2. model and assumptions
  3. data dictionary
  4. data
  5. presentation

Media:SpShtLec3 PresidentsDataAndAnalysis 2 20080205.xls

CPSC203 Template Spreadsheet Image 27.png

CPSC203 Template Spreadsheet Image 28.png

CPSC203 Template Spreadsheet Image 29.png

CPSC203 Template Spreadsheet Image 30.png

CPSC203 Template Spreadsheet Image 31.png

Part 3

In this third iteration, the same five sheets are used (as in Part 2), with further refinements, descriptions, and presentation of the two models used.

Media:AnalysisExamples PresidentsDataAndAnalysis 4 20080306.xls

CPSC203 Template Spreadsheet Image 32.png

CPSC203 Template Spreadsheet Image 33.png

CPSC203 Template Spreadsheet Image 34.png

CPSC203 Template Spreadsheet Image 35.png

CPSC203 Template Spreadsheet Image 36a.png