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

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
 * 4) Location (cell range),
 * 5) Name,
 * 6) The Data Class it is (Raw Data, Statistical Summary, Calculated Variable, Score etc.),
 * 7) Data Type (e.g. Integer, Text, Currency, Date, etc.) and
 * 8) Description (a description of the data or what it's 'purpose' is).
 * 9) Raw Data -- Present your raw data in tabular form -- with columns representing variables and rows representing cases.
 * 10) Calculated Data
 * 11) Summary Statistics -- Usually Summary statistics result from calculations across rows for a single column.
 * 12) Derived variables are often based on calculations across columns for a row.
 * 13) Presentation (Reporting)
 * 14) Emphasize the final information you wish to show without excessive background details.
 * 15) 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]]





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]]











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]]