| CSC 105 | The Digital Age | Spring 2009 |
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.
You will work 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.
Use the following steps to copy a simple, but detailed spreadsheet for a (fictional) trip into your account:
mkdir 105
cd 105
cp ~weinman/courses/CSC105/labs/travel-spreadsheet-v1.ods .
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 the OpenOffice spreadsheet (oocalc) as follows:
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.
Select Open under the File menu, and then open the file travel-spreadsheet-v1.ods in the 105 subdirectory of your account.
Upon opening travel-spreadsheet-v1.ods, 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. This will also move your cursor to a new cell.
In this exercise you will experiment with display formats. Click on the date "02/10/05" heading in cell A5, and then select Cells under the Format menu at the top of the spreadsheet. A dialog box labeled "Format Cells" will appear. Near the top of the dialog box, notice the set of "tabs", labeled Numbers, Font, etc. Click on the Numbers tab to bring it to the front. You should then see several alternative formats for dates. Select a different date format, and click "OK". (If the date field now shows "###", that is ok. You can fix it in the next exercise.)
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. Click your mouse on the row label for row 18 (i.e., click directly on the "18"). The blank line before February 12 should become highlighted. Then select Rows under the Insert menu. This should insert a new row into the spreadsheet at row 18.
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. In the following steps, you will use a revised spreadsheet for this purpose.
Use the terminal window (and a command shown earlier in this lab) to copy the file ~weinman/courses/CSC105/labs/travel-spreadsheet-v2.ods into your 105 directory. Open it in 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, labelled "All Expenses".
Review the various cells in the spreadsheet, and then answer the following questions.
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. If there are other changes that must be made to ensure all totals and subtotals are correct, make those changes as well.
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 H13 should contain the value $409.09.
Insert the relevant formulae in appropriate cells in Column H, so the four daily subtotals will be computed by the spreadsheet. (For a hint on how to do this, look at the formula in cell I5. Note that it starts with an "=" sign, indicating that it is a formula. It also specifies that the cells to be summed run from C5 through F5. Your formulae should use the same format, with different cell numbers.)
Many spreadsheets allow you to view some or all of the data in a graphical format. In the next few steps, you will create a bar chart displaying the expense subtotals by category.
Highlight the category titles and subtotals as follows. Click on "Travel" in cell C3. Then, holding the shift key down, click on the subtotal for "Other" in cell F4. A block of titles and subtotals should now be highlighted.
Select Chart... under the Insert menu. (It is almost at the bottom of the menu.) In the dialog box that appears, choose these options:
At this point, a chart should appear. You can move the chart to a better location as follows. Click on the anchor icon near the upper left corner of the chart, select Cut under the Edit menu, click in cell L5 to the right of your data, and then select Paste under the Edit menu.
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).
You should endeavor to make the spreadsheet attractive and easily readable.