Assignment 3: Spreadsheets

LBSC 690 Section 0101: Spring98
Assignment 3: Spreadsheets

Part 1: Creating and Using Models

Instructions...

The following instructions are written for Microsoft Excel 5.0, available in the computer lab. You may find that newer versions of Excel and other spreadsheet programs have similar commands. Due date: March 11.

Part 1. Creating and Using Models

In Part 1 you will create a spreadsheet model for planning a library budget, enter last year’s expenditures and this year’s allocation, and make three different projections for the next year. Make sure to save your work - you will use the spreadsheet you create in Part 2.

1. Double click on the Excel icon. Create the personal budget request spreadsheet by typing in the words and numbers as pictured below. Enter all the data except cells C11 and D11 (benefits) and C13 and D13 (subtotal). [Hint: To enter the data -- both labels and figures -- you may either move downward in a column or across in a row. For example, after inputting the heading "Personnel Budget Request" in row one (cell C1), move the cursor to cell C3 and enter the first part of the label (Expanded) for the first column. Continue entering the column labels either by moving across to cell D3 and entering the first word of the next column (Allocated), or by moving down to cell C4 and entering the second part of the label for the first column (Last Year).]

	A	B	C	D
1			Personnel Budget Request
2
3			Expended	Allocated
4	Line Item		Last Year	This Year
5
6	Salaries & Wages
7	Professional		20000	24000
8	LTA		12000	21000
9	Clerical		15000	18000
10	Hourly		2000	5000
11	Benefits (18%)		=SUM(C7:C9)*0.18
12
13	Subtotal
14
15

2. For the "Benefits" row enter the formula =SUM(C7:C9)*0.18 in cell C11. (Hint: If you used a different row and/or different cells, you need to adjust the formula.) What is the value that you get in C11?________________

3. Copy this formula to cell D11 by using the copy function. There are several ways to do this; all of them require the cell to be copied (C11, in this case) to be "selected" (the cell has a thick outline around it).

a. Edit | Copy from the menu. A dotted outline appears around the target cell (C11). Select the target cell (D11). Edit | Paste

b. Click on the Copy button (with an image of two overlapping documents) from the tool bar. A dotted outline appears around the target cell (C11). Select the target cell (D11) and click on the Paste button (with a document on a clipboard icon).

c. Hold down the Control key (Ctrl) while pressing the C-key once. Let go of the Ctrl-key. A dotted outline appears around the target cell (C11). Select the target cell (D11) and hold down the Ctrl-key while pressing the V-key once.

What is the value you get in cell D11?________________ What is the formula in cell D11?_______________

(Hint: to see the formula, highlight the cell but look at the formula bar towards the top of the window.)

4. For the "Subtotals" row select cell C13. Click on the AutoSum button from the tool bar (a S symbol on it). Dotted lines will appear around the cells which are to be added. In this case, you want to add the following line items from the "Expended Last Year" column: Professional, LTA, Clerical, Hourly, and Benefits. You will notice that an equation appears in the cell. What is the formula? __________________ Press the Enter-key to confirm the equation. What is the value? __________________

5. Either repeat step 4 above for D13 or copy and paste the formula from C13 to cell D13.

6. Move the cursor around the spreadsheet, especially over the cells with entered and calculated values. Note that although both appear as numbers in all of the cells, when a calculated cell has been selected, it’s formula is displayed in the Formula Bar (at the top of the window underneath the tool bar).

7. Formatting. You may want to change the width of some or all of the columns. There are several ways to do this (search for "column heading" in the Excel HELP function (press F1)). You can have Excel automatically set a row or column size in a given column (or row, but re-sizing columns is more common). Note: to use this method, all data in the spreadsheet must first be entered.

a. Click on the header of the column you wish to resize (the gray boxes with column letters).

b. Move the cursor to the line to the left of the selected column, between the selected column and the column to the right. (The cursor should turn into a double-headed arrow with a vertical line in the middle aligned with the line separating the columns.)

c. Double click the right mouse button to "optimize" the width of the column (i.e., the column will resize to accommodate the longest entry in that column) or just hold down the right mouse key and drag the double-headed arrow to increase or decrease the column size.

To modify the column width manually:

a. Click on the header of the column you wish to resize (the gray boxes with column letters).

b. Place the cursor on the header and press the RIGHT mouse button.

c. Select Column Width... in the menu box.

d. Type in a number for the desired column width in the Column Width filed of the window. (The number must range between 0 and 255 and represents the number of characters displayed using a "standard" font.)

8. Create a column that shows the percentage of the total Salaries and Wages budget allocated this year for each category.

a. Choose a column for this data and enter an appropriate label (e.g., "Percentage" in F3 and "Spent" in F4).

b. Enter the formula in one cell: divide the expenditure in the category by the total expenditure. Use cell addresses not numbers for this function and use / to signify division. (Hint: When entering a formula, use the equal sign (=) as the first element then a cell address. For example, =d20 - f13 will subtract the value in cell f13 from the value in cell d20.)

c. Copy this formula to the other cells. When copying the formula, you will want the cell address for the subtotal (D13) to remain constant or absolute. (Hint: To indicate the absolute value in the formula, place dollar signs before each part, that is, before the column letter and the row number -- e.g., $D$13).

d. Select the column after all of the formulas have been copied. Right click on the mouse and select Format Cells... from the properties menu. Click on the "Number" tab and highlight the Percentage entry in the Category list. Select 0.00% in the Format Codes list to the right. This ensures two decimal places will be shown for each percentage. Click OK.

e. What do you expect the resulting value to be if you sum up the percentages? ___________% To test this, select the next unused cell in the Percentage column and click the AutoSum (S ). Was your prediction correct? ________

9. Put your floppy disk in the drive. Click on File | Save to save your spreadsheet onto the floppy disk. You will use the spreadsheet you just created in Part 2. (Hint: In the Save File dialog box change the drive to "A:") Enter a file name of your choosing in the File Name fill-in field.

10. File | Exit to exit from Excel.

Other Features and Functions

Now explore some other spreadsheet features and functions.

Data Alignment -- Examine the display of data in cells. Numerical data is aligned to the right (right justified); text is left justified. You can change this with the Alignment function. After selecting a cell or cells (hold down the right mouse button and drag the cursor across the desired cells), either click on one of the three Alignment buttons (Alight Right, Center, Alight Left) in the tool bar or click the right mouse button once with the cursor over the highlighted cell(s). Select Format Cells... and click on the Alignment tab in the Format Cells window. Notice that there are a number of formatting features that can be adjusted to satisfy your needs through this particular dialog box. Try a different setting and press OK to implement your changes. Once you’ve confirmed that the new settings have taken effect, click on Edit | Undo Format Cells to return to the original format.

Move Rows and Columns -- An important feature of spreadsheets is the ability to move entire columns or rows. This is important because spreadsheets are frequently revised to create new models of the data. Move the Expended Last Year column to the B column. Select the entire C column by clicking on the field title box (the gray box labeled C). Note: The entire column will be highlighted. Click on Edit | Cut. Click on the B column title box and then Edit | Paste. After confirming that the column was moved, click Edit | Undo Paste

Another helpful function in editing a spreadsheet is the ability to insert a column or row into the table. For more information on this procedure access the HELP topics "Inserting Columns" and "Inserting Rows".

Functions and Formulas -- You have already entered a function that is common to all spreadsheets, the ability to sum the values in two or more cells. Try another function. (Access the HELP topic "About Worksheet Functions" for information on the possibilities.)

Try doing some calculations using the spreadsheet values. For example, subtract one number from another or add numbers together.

----------

NOTE: You do not have to turn in anything (electronic or otherwise) other than these instructions with the blanks filled in with your answers.