Jan's Illustrated Computer Literacy 101 Logo:Jegsworks Jan's Illustrated Computer Literacy 101
Home > Jan's CompLit 101 > Working with Numbers > Design > Analysis > Planning
Icon: Arrow - Previous pagePrevious    NextIcon: Arrow - Next page

Jan's Working with Numbers

    Design: Analysis: Planning

When you have to create your own spreadsheets out in the real world, it is not likely that you will be given specific directions like you are in these lessons. In fact, you may have just a vague idea of what you want to see.

The points below will help you design a workable spreadsheet. Thinking through the list at the beginning can save you a lot of time and trouble before you are through. Basically you work backwards from the end result that you want and figure out what you need to have to get there.

Keep in mind the three uses for spreadsheets:

  • Tracking Data: Add or change data regularly, like:

    • Income/expenses
    • Inventories
    • Sales
    • Time sheets > payroll calculations

  • Saving Data History: Data is fixed and will not change, like:

    • Quarterly or annual reports of sales or business
    • Past results of any type

  • Looking at Possibilities: Trying out different possible values, like:

    • What If: Substitute different values to see how they affect the result
    • Goal Seek: When you know the result you need, see what values can create that result

Planning Checklist for Spreadsheets

1. Set Goals

What is this spreadsheet for? What questions will it answer?

2. Identify Output and Input

A. Output - Final values that the spreadsheet will produce, like Total Sales, Average Price, Number of Members, Number in Stock.

B. Input = Values  Data you need in order to get the results you want, like Price for Each, Tax Rate, Name, Address, Item Number, Quantity, Amount

C. Input = Formulas  Functions and formulas that you will use to calculate the Results. Write these down in terms of the Values you listed. For example, Number in Stock = Number Bought - Number Sold, Selling Price = Our Cost + Overhead + Sales Tax + Profit.

3. Design Layout

The sheet must handle four activities well to achieve a good layout for your spreadsheet.

A. Entering Data- Make it easy to enter data. It is easier to enter data accurately when the spreadsheet uses the same order that the raw data comes in.

B. Using the Sheet- What paper or screen size will you use? Is the sheet easy to read? Is the most important information easy to find? Can the user see the row and column labels when there are many rows or columns?

C. Changing the Sheet Later- Will new records (rows) be added?  Might you need to add columns? Will they be added in the middle or at the beginning or at the end? Formulas must be carefully designed to be sure they will work correctly after adding new data.

D. Printing- Will the spreadsheet ever be printed? What size paper? Print all or just part? Must the printing fit on a certain number of pages? Should you repeat column and row labels on every page? Are grid lines needed? What should be in the Header and Footer? Black and White, Gray Scale, or Color? How well do your color choices work in each? Is the sheet still readable? Is there color coding that is no longer helpful once printed?

4. Test Calculations

A.  Easy numbers- Try out your spreadsheet design with data that is easy to calculate with, so you can check that the results are correct.

B.  Known Numbers- Try out your spreadsheet with realistic data for which you know the results.

C.  Special Numbers- Try special case data, like zero values, blank cells, very small numbers, and very large numbers. Are the columns wide enough to show the largest and smallest numbers you can expect to see? Does your spreadsheet handle these special cases well or does it go crazy? If error messages appear, do they explain what to do?

5. Document what you did

Either directly on the spreadsheet or in hidden comments or data validation messages, provide information about your work. Your documentation covers three kinds of information:

What it is: What is this spreadsheet for? Who designed it? When? When was the data last updated? Where did the data come from?

How to use it: Where to enter new data? Where to find results? How to enter new data? For example, do you enter a phone number like 1-222-333-4444 or 1 (222) 333-4444 or 12223334444 without any punctuation? Do you enter amounts of money as whole numbers only or should you include the decimal part?

How to change it: Explain what you did and why, especially your formulas and what parts of the spreadsheet depend on other parts. Are there features that were hard to make work correctly? What should not be changed, even if it seems to be an odd way to do things.

It is amazing how much you can forget about your own work after a few weeks. So write up details that you think you won't forget. It will be worthwhile in the long run.