LBSC 690 Section 0101: Spring 98
Assignment 3: Spreadsheets

Part 2: Advance Spreadsheet Functions

 

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 2. Advance Spreadsheet Functions

In Part 2 you will extend the budget model you created in Part 1, graph the resulting data, and create a spreadsheet for calculating grades.

1 Adding to the Personnel Budget Model

1.1 Double click on the Excel icon. Open the personnel budget spreadsheet that you created in Part 1. Use File | Open to retrieve the file (remember to specify the correct disk drive).

1.2 Use the personnel budget spreadsheet to create three new different budget requests for next year (hint: use 3 separate columns and base the models on the data from the "Allocated This Year" column). The three requests and the assumptions upon which they are based are:

a. Maintenance of Effort -- Salaries and wages will increase at 3%, the rate of inflation.

b. Decrease in Program -- Salaries and wages will decrease as the library loses staff; the budget cut will be 5% of this year’s allocation.

c. Increase in Program -- Salaries and wages will increase as the library adds staff; the budget increase will be 8%.

1.3 Set up three new columns for these three budget models. User formulas to set the values for each model.

1.4 Print both the values (surface level) and formulas (deep level) of the spreadsheet for submission.

a. Click File | Print... to print the values.

b. Click Tools | Options... When the Options dialog appears, click on the View tab and select the Formulas check box. Click OK. Notice that the underlying formulas in different cells are now visible. Print this view of the spreadsheet using File | Print. Examine the differences between the two printouts.

1.5 Spreadsheets can be used to create graphic representations of the data. Try Excel’s graphics functions by creating a pie chart of the expenditures for various categories of personnel expenses for the current year. (Do not include the subtotal cell.)

a. Use the mouse to highlight the cells which contain the salaries & wages component data for the Allocated This Year column.

b. Hold down the Ctrl-key and highlight the corresponding Line Item column cells, from "Professional" to "Benefits (18%)". (Hint: A total of 10 cells -- 2 columns and 5 rows -- should be highlighted.)

c. Click on the ChartWizard button in the toolbar (it has a picture of a bar graph with a wand -- as in "magic wand" next to it). Move the cursor over the spreadsheet and notice that it has changed from an arrow to a plus sign (+) with a small bar graph symbol.

d. Pick an empty part of the worksheet (with lots of space to the right and below), hold down the left mouse button, and drag to the right and down (you will see a box marking the size of the graphic as you drag). Once the box is the size that you want your graphic to be, let go of the mouse button.

e. In the Step 1 of 5 dialog box, notice that the range of your selected cells are shown in the text field (e.g., =$A$7:$B$11). Check to make sure that this corresponds to the cells you highlighted. Click on Next.

f. Select the Pie picture in Step 2 of 5 and click on Next.

g. Select style 7 (i.e., a pie chart with both labels and percentages) in Step 3 of 5 and click Next.

h. In Step 4 of 5, look at the sample chart to make sure it looks right. Note the two controls, "Use First ___ Column(s) For Pie Slice Labels" and the "Use First ___ Row(s) For Chart Title." Try changing the numbers and watch the chart change.

What does the First Columns control do? _____________________________________________
What does the First Row(s) control do? ___________________________________________________

Click Next.

i. In Step 5 of 5, type the following in the text field under Chart Title: Salaries and Wages Components, 1998. Click Finish.

j. The complete chart will appear on your spreadsheet.

k. To print the chart (and not the underlying spreadsheet), double click on the chart so that the outline of the box around the pie is highlighted and surrounded by a thick line. Select File | Print Preview to see what the printout will look like. If you like what you see, click on the Print... button at the top of the window. Submit the printout of the chart.

1.6 Create other graphs if you wish to try other features of the graphing function. You can add titles and other text to the graph through the ChartWizard.

Hand in the following printouts: (1) spreadsheet - values only; (2) spreadsheet - values and formulas; and (3) Pie chart.

 

2 Creating an Electronic Grade Book

This spreadsheet will be used to keep a record of the grades of ten students on six assignments or exams and will calculate final grades.

2.1 Open a new Excel file by clicking on File | New.

2.2 Enter the following data:

a. Students: Holly Jones, Patrick McGee, Mary Morrison, Brenda Karns, Robert Anders, Zoe McNamara, Joyce Jensen, Thomas Best, Paul Robertson, and Karen Warner. Enter the names in that order. You should enter the names last name first; the last and first names can either be in different columns or in the same column, but separated by a comma.

b. Assignments: Book Report (10% of the final grade); Oral Report (15%); Test (20%); Project (20%); Group Work (10%); and Final Exam (25%).

c. Enter grades for each student on each assignment or test. Give two students the same grade on the Final Exam.

2.3 Calculate final grades for each student using the appropriate formula(s).

2.4 Print the spreadsheet two ways: with just the values and with the underlying formula(s).

2.5 The student records are apparently in a random order. Use the sort function to sort the records in an order that is more useful (Hint: there are two buttons in the tool bar -- both with pictures of the letters A and Z -- that may be useful for this job. Use Help to read up on how to use them. Be careful not to sort only the names and not their corresponding assignment/exam grades, an easy mistake to make). Print the sorted spreadsheet.

2.6 Now sort the records by final grade so that the student with the highest Final Grade is the first record. (Hint: highlight the relevant cells and click on Data | Sort.) Print this version of the spreadsheet.

2.7 Finally, sort the records first by Final Exam score (highest score first) and then by the Test score (also highest score first). Check the order of the two students who had the same grade on the Final Exam -- are they sorted correctly by their Test scores? Print this version of the spreadsheet.

Hand in the following printouts: (1) grade book spreadsheet - values only; (2) grade book spreadsheet - values and formulas; (3) Spreadsheet sorted in a useful order; (4) Spreadsheet sorted by Final Grade; and (5) Spreadsheet sorted by Final Exam and Test.