# 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

#### 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?