Excel Overview

Microsoft Excel is the spreadsheet software available on computers in our labs.

Spreadsheets are arguably THE software application that made desktop computers popular. Spreadsheets directly extend the ideas behind doing calculations on paper. Spreadsheet shine for manipulation and analysis of data. Simple summaries such as summations to more complex statistical and database manipulations are possible through an intuitive interface. Reporting of results through tables and charts is straightforward. Best of all, calculations can be set up (should be set up!) to be reusable so that "What if?" evaluations are expedited.

Building a level of comfort in basic usage of spreadsheets will be indispensable for a number of classes you take in CNR (as well as life after you graduate). As is usually the case with software, to build a comfort level requires that you put in your time sitting down and using the software. We suggest everyone go through the on-line tutorial available here while duplicating steps in a copy of Excel running in another window. At a minimum you should understand:

• Workbook/worksheet organization and structure
• Row/column labeling, selection, addition
• Cell definition and referencing
• Moving around a worksheet
• Entering/editing "data" in cells
• Defining/working with ranges of cells
• Constructing formulas with operators, constants, cell references and functions
• Copying formulas and the implications of relative/absolute referencing
• Formatting cells and ranges of cells
• Using chart wizard to quickly create a graph
• Formatting options for charts
• Getting help within Excel

Formal Assignment

This assignment is worth 15% of your course grade (to be submitted by 4:00PM Friday Oct 19 on diskette in the box labeled "Orientation Assignments" in 115 Green Hall).

WARNING - This is not a trivial assignment. Do not attempt to start it until you are comfortable using Excel to the level described above. Then THOROUGHLY study the examples provided below including what is a datum entry and what is a formula (e.g. print the images out). Then assemble the course information PARTICULAR to your program. Then, and only then, begin to assemble the worksheets as described below. The minimum required for the assignment is simply typing in your program for three semesters in a particular format on two worksheets; start there and work your way forward according to the additional credit you'll receive on the assignment (detailed under "Grading" below). Beyond the minimum you may put in as little or much effort as you wish; the consequences in terms of grade are explicit.

Problem Description

Construct an Excel workbook that lays out your program of study beginning with this semester (exceptions to this will be necessary for students that will not be in residence in CNR three or more semesters; those students will have to include past course work to obtain credit as described below). The workbook should consist of three worksheets:

1. Program worksheet with your classes identified semester by semester
2. Courses worksheet with a listing of courses you have taken or will take, by requirement category
3. Credit Summary worksheet with a tabular and chart summary of your progress toward completing your degree

The following image shows part of the Program worksheet for a student in the Forest Resources - Management Track option (developed using 1999 information). Your worksheet should be laid out in an IDENTICAL fashion. The Credits summary (e.g. cell C10) should be computed with the SUM function. The fields to the right of the course listing contain information for computing tuition cost (update these to current rates - the information is available on the Web!). The "Annual Inflation Factor" is the rate at which tuition is assumed to increase annually; you may use your best guess for this. Tuition cost is computed in each cell it appears in, e.g. cell C11, by a formula "like"

=IF(C10<=H3,C10*H2,H3*H2+(C10-H3)*H4)*K1^(D10-H1)

Note that the worksheet is setup so classes can be substituted, deleted, or added without needing to update any formulas.

The following image shows the end of the Program worksheet which is seen to display total credits and total tuition cost (again, these are computed using formulas, not typed in!).

The following image shows the top of a Courses worksheet. The sheet is seen to be an implementation of a Curriculum Guide sheet with specific options selected. Columns are provided for identifying the year and semester when the course has been/will be taken and completed credits upon taking the course (you should assume, at a minimum, that you have completed the courses from the present semester). Totals are again computed with the SUM function. Total credits and total credits completed should be summarized at the end of the Courses worksheet (not shown) by adding across categories.

The following image shows a Credit Summary worksheet. The tabular summary contains REFERENCES to corresponding totals on the Courses worksheet. The chart was easily created with Chart Wizard.

A Comma Separated Value file listing many of the courses common to CNR curricula is available to download and import into Excel to eliminate some of the drudgery associated with typing course titles in.

Images of worksheet examples for the NRES Environmental Assessment and Monitoring concentration (again developed using 1999 information) are also available.

Label the diskette you turn in with:

• Your name
• Your class (FR or NRES) and lab time
• The version of Excel used and platform (Mac vs. IBM-compatible)
• The name of the Excel file containing your completed workbook

Grading:

Fill out Courses sheet for your program plus                   Max Grade = 75%
   fill out Program sheet for three semesters WITHOUT
   tuition calculation

Above plus Credit Summary sheet                                Max Grade = 85%

Above plus Program sheet for full program or six               Max Grade = 100%
   semesters (whichever is less) and tuition formula

1. Satisfy yourself that you are proficient using Excel to the degree identified by the bulleted list above.
2. Study the example solutions carefully.
3. Start a new Excel workbook and label necessary worksheets.
4. Use the appropriate curriculum guide to layout your courses to be taken (include this semester) in the format of the Courses worksheet (i.e. by Category). Where there are options for courses, pick one that seems best now. Your categories may differ from those of the examples depending upon your major/concentration/track. Be sure to use the SUM function when adding credits.
5. Layout a schedule of courses for this semester and the following two semesters in the format of the Program worksheet. Add the Total Credits line to the worksheet. Be sure to use the SUM function when adding credits.
6. Construct the table on the Credit Summary worksheet. Be sure table cells are references to cells in your Courses worksheet. Use Chart Wizard to construct an in-line chart of your Progress Toward Degree completion.
7. Expand your Program worksheet to include your full program (or six semesters, whichever is less). Include the tuition cost calculation inputs (use current data) and formulas and total on your Program worksheet.

You would stop at step 5. if you are satisfied with a maximum grade of 75% and at step 6. if you are satisfied with a maximum grade of 85%.

Back to lab listing