Laboratory Exercise on Spreadsheets

Background

Software packages from several vendors provide capabilities that are designed to meet common needs. The following table identifies three types of packages:

Many commercial software packages have similar capabilities, but run on different types of computers and may differ slightly in details of use. Three common packages are:

Summary

This laboratory exercise introduces several basic operations that are commonly part of a spreadsheet.

The details of the lab utilize the Open Office spreadsheet oocalc, although many elements are common in other spreadsheets as well.

Work proceeds through two extended examples. In the first, you are guided through basic steps at some length. In the second, you are asked to design your own spreadsheet for an application that is typical of many spreadsheet uses.

Example 1: Tracking Travel Expenses

When an individual travels for an employer or formal organization, it is common for the employer to reimburse the individual for actual expenses. Normally, the individual pays for various expenses, but keeps receipts. Upon completion of the trip, the individual fills out an expense account form and submits it to the employer. After reviewing the appropriateness of the expenses, the employer sends a reimbursement check.

Spreadsheet: Version 1

A very simple, but detailed spreadsheet for one [fictional] trip is stored in file ~walker/105/labs/travel-spreadsheet-v1.xls .

1. Copy the above file to your account:

   1. Within your home directory, open a terminal window and create a 105 subdirectory with the command

      
         mkdir 105
      

   2. Move to this subdirectory:

      
         cd 105
      

   3. Copy the file to this new subdirectory with the command:

      
         cp ~walker/105/labs/travel-spreadsheet-v1.xls .
      

      Note that the cp command expects you to give the name of the file you are copying first and the new name for the file second. In this case, the final dot (.) indicates you want to use the same file name, but in your current directory.

2. Open OpenOffice by clicking on the icon showing a schematic image of a page with writing and a diagram.

3. On first using OpenOffice, you may be asked about setting up your account to run this software package. You can safely use the default options for each choice asked.

4. Use the File option at the top of the screen to "Open" the file travel-spreadsheet-v1.xls in the 105 subdirectory of your account.

Upon opening travel-spreadsheet-v1.xls, you will see a typical spreadsheet configuration. Information is arranged in cells. Rows are labeled by numbers (on the left hand margin), and columns are labeled by letters (at the top margin). Cells (or rectangular blocks) then are labeled by a column/row combination.

Spreadsheets allow you to enter information by clicking your mouse on any cell and typing data.

5. Click on cell B3. Note that the cell now has a box around it, and the words "Expense Description" appear in an editing box at the top of the spreadsheet.

6. Move your mouse to the editing box, and change the wording to "Description of Travel Expense". After finishing editing, use the <Enter> key or the <Tab> key to record the change in preparation to moving to a new cell.

7. Clicking on the date "02/10/05" heading in cell A5, experiment with different display formats. Use the "Format" menu at the top of the spreadsheet to select "Cells". A new box will show you several alternative formats for dates. Select a different format, and click "OK".

8. Change the width of a column as follows:

   1. Move the mouse to the top header line, and position it on the vertical line between header cells (e.g. between the label "A" and "B").
   2. Hold down the left mouse button, and move the mouse left or right.
   3. Note which box or column changes in size.
   4. What happens if you make a column sufficiently small that the data do not fit in the space available?

Most numbers in the "Cost" column represent specific values entered by a user, in the same way that other data were entered. However, cell C31 is different. Clicking on C31 displays the expression =SUM(C6:C28) in the editing box. Here, the equal sign = indicates that the computer is to perform a computation before displaying a result in this cell.

The rest of this line shows the nature of that computation −− the machine is to compute a SUM of the cells in Column C, from rows 6 through 28.

9. In this spreadsheet, values for Breakfast and Lunch on February 13 have not been supplied. What happens if you complete this expense listing by inserting costs in the two relevant cells in Column C?

10. Suppose that in preparing this spreadsheet, you forgot to enter the cost of the opening reception on February 11. Move your mouse to the row number at the end of that day, and click, so the blank line before February 12 is highlighted. Then use the "Insert" menu at the top of the page to insert a "Row". This should add another line to the spreadsheet.

    1. Fill in "Opening reception" and an amount in this new line. Is this amount included in the "Total" at the bottom of the spreadsheet?
    2. Click again on the cell that computes the total. Is this the same as the =SUM you saw before? If not, how does it differ?

Spreadsheet: Version 2

Although Version 1 of the spreadsheet may serve adequately for some expense reports, employers sometimes require subtotals of expenses by categories, such as travel, meals, and lodging. A revised spreadsheet for this purpose is available as file ~walker/105/labs/travel-spreadsheet-v2.xls

11. Copy file ~walker/105/labs/travel-spreadsheet-v2.xls, and open it within OpenOffice.

12. The spreadsheet has different columns for various types of expenses. Each expense is shown twice: once under the appropriate category, and once under a summary column, "All Expenses".

    Review the various cells in the spreadsheet and explain how various entries are determined.

    1. How are the entries in the "All Expenses" column determined?
    2. Why do you think "All Expenses" contains a formula, rather than asking the user to enter the value a second time?
    3. How are the subtotals computed?
    4. How is the "Grand Total" determined?

13. As with version 1 of the spreadsheet, insert a new line for an "Opening Reception" for February 11, and add the notation "Opening Reception" and the relevant amount.

    Describe what cells you must adjust to make this addition.

14. The spreadsheet has a column entitled, "Daily Subtotals". At the end of each day, this column is supposed to show the total amount the user spent on the given day. Thus, cell H11 should contain the value $409.09.

    Insert the relevant formulae in appropriate cells in Column H, so these subtotals will be computed by the spreadsheet.

Many spreadsheets allow you to view some or all of the data entered in a graphical format.

15. Highlight the category titles and subtotals: Click on "Travel" in cell C3. Then, holding the shift key down, click on the amount for "Other" ($185.00). The block of titles and amounts now should be highlighted.

16. Within the "Insert" menu, choose the "Chart" option. Within the dialog box that appears, choose these options:

    1. Check "First row as label" (this means the category titles will be considered labels in what follows); click "Next"
    2. Choose the (default) graph of vertical bars, and click "Next"
    3. Choose the (default) vertical bars, check the box for "Show text elements in preview", and click "Next".
    4. Supply "Expenses by Category" in the box for the Main Title, and click "Create".

17. At this point, a chart should appear. Note you can click on the icon of an anchor, use editing option "cut", select a new cell at the bottom of the spreadsheet, and then use editing option "paste" to move the resulting chart to any desired part of the page.

18. Experiment with several other "Chart" options to explore various types of graphs and charts that are easily generated for these data.

Example 2: Finances for an Individual, Club, or Student Organization

The ideas described for a travel-expense report apply to many common tasks. One common application involves budgeting for an individual, club, or organization. In what follows, you are to choose a client (yourself), a club, an organization, or some other group. The group may be either real or fictitious. In planning, it is common to anticipate likely income and expenses for the client, to determine what programs/activities are feasible. If programs will cost more than available income, then either income must be increased, or expenses cut. This represents a fundamental approach of budget preparation for both individuals and organizations.

19. Create a spreadsheet that shows a possible budget for the client for an interval of time (e.g., a month or a year). Although the details clearly depend upon the client, your spreadsheet should contain at least the following elements.

    1. One part of the spreadsheet should contain "receipts" or "income". This should show all revenue expected for the time interval. Individual sources of income (e.g., dues, fundraisers, gifts) should be estimated, and this section should conclude with a "total receipts" or "total income" cell.
    2. One part of the spreadsheet should contain expected "expenses". This should show anticipated programs or activities of the client and their anticipated costs, together with a cell for "total expenses".
    3. A final part of the spreadsheet should show "surplus" −− that is, the computed amount "total receipts" − "total expenses".

    For this lab, you need not provide an in-depth budget plan for a client. However, your budget spreadsheet should contain at least two sources of income, at least four types of expenses, and at least one graph showing the relative amounts of income types or expense types (or both).

Work To Be Turned In

Parts 1-18 of this lab are designed to give you experience with elements of a spreadsheet. While you should work your way through these parts carefully, you are not expected to turn in the answers to these parts.

The only work to turn in for this lab involves part 19.

• You should e-mail your spreadsheet from part 19 to the instructor as an attachment.
• You should include a separate paragraph that provides a brief explanation of the client application in part 19.

created February 17, 2005 last revised March 26, 2005