|The Tao of Computing:|
|A Down-to-Earth Approach to Computer Fluency|
|by Henry M. Walker||Jones and Bartlett Publishers|
Software packages from several vendors provide capabilities that are designed to meet common needs. The following table identifies three types of packages:
|Package Type||Brief Description|
|database||store and selectively retrieve tables of data|
|spreadsheet||facilitate bookkeeping and computations for budgeting, analyzing lab data, and coordinating simple simulations|
|word processor||handle storage and formatting of reports and papers|
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:
|Product Name||Computing Environment||Components||Package Type|
|ThinkFree Office||Apple Macintosh||integrated package||database|
|OpenOffice||Unix/Linux||interface to external package||database|
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.
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.
A very simple, but detailed spreadsheet for one [fictional] trip is stored in file ~walker/105/labs/travel-spreadsheet-v1.xls .
Copy the above file to your account:
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.
Open OpenOffice by clicking on the icon showing a schematic image of a page with writing and a diagram.
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.
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.
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.
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.
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".
Change the width of a column as follows:
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.
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?
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.
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
Copy file ~walker/105/labs/travel-spreadsheet-v2.xls, and open it within OpenOffice.
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.
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.
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.
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.
Within the "Insert" menu, choose the "Chart" option. Within the dialog box that appears, choose these options:
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.
Experiment with several other "Chart" options to explore various types of graphs and charts that are easily generated for these data.
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.
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.
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).
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.
created February 17, 2005
last revised March 26, 2005