Bill Tracking Spreadsheet Excel - DOC by xuy16262

VIEWS: 151 PAGES: 5

• pg 1
```									                                               Project 3:
Excel – 20 points

Part I – Excel Grade book – 10 points
1. Excel is an important program for performing math and accounting functions, and it
is an excellent tool for teaching. As such, it is important that you know how to
create and work with an Excel spreadsheet. Follow the directions below to finish this
project.
2. Open Microsoft Excel and create the spreadsheet that has been presented at the
end of Part I in this document. Replace “Jane Doe’s 5th Grade Class” with your name
and the grade level you hope to teach. Type the rest exactly as you see it.
3. Enter all of the numerical values as they appear in the table at the end of this
document.
4. In Cell G3 enter the equation “=SUM(B3:F3)” in the function field.

a. The “=” sign means that an equation is entered in the function field. Without
the equals sign the Excel spreadsheet assumes the value there is simply text.
The SUM is the function and B3:F3 means do the function from Cell B3 to F3.
5. Place your cursor in the field G3 and highlight it. A box will appear around it with a
black square in the lower right corner.

a. Doing this replicates the function “=SUM(B3:F3)” to each row, but changes
the row value, in this case “3”, to the appropriate value for each row.
6. Move the cursor over the black square, click on it, and drag the square down to the
end of Cell G17, below Ophelia Orvis. This will place the equation in all of the cells in
the “G” column. You should see the totals of all the students in these cells.
7. In Cell H3 enter the equation “=G3/5” which means take the value in Cell G3 and
divide by the value 5 (a forward slash stands for divide). Pressing the enter key will
then calculate the value.
8. Highlight Cell H3 and drag the black square past to Cell H17 to copy the equation
into the column of cells from H3 to H17. This step repeats what you did for Step 6
but for the H column.
9. Now, highlight column B from Cell B3 to B19 and stop. Highlighting requires clicking
with the left mouse button (and holding) and dragging the mouse until you have
reached Cell B19 and then let go of the left mouse button. You should see the
column highlighted in color from B3 to B19.
10. With the Home Tab selected in Excel 2007, press the AutoSum tool in the upper-
right corner or the toolbar at the top.

a. This will place the equation “=SUM(B3:B18)” into the Cell B19. This adds all
of the values from Cell B3 to B18 and places the value in B19.
11. Highlight the Cell B19. Place your cursor in the Function field at the top of the
spreadsheet. Alter the equation to “=SUM(B3:B17)/15”. The last cell was changed
from B18 to B17 and the “/” sign divides the sum of the values by the value 15. This
will produce the Class Average for the B column.
12. Highlight the B19 cell and drag the black box to the right to Cell H19. This will copy
the equation presented in Cell B19 to the other cells.
13. Highlight the G19 cell and then place your cursor in the function field. Delete the
equation from the field. Alternatively, highlighting the Cell G19 and pressing the
Delete Key will eliminate the equation.
14. Now, highlight Row 19 from B19 to H19 and right-click in the highlighted row.
Select Format Cells from the list of menu options. Under the Number Tab select
Number in the Category field and alter the Decimal Places to a value of 1. This will
reduce the decimal places reported to only 1. Press the OK button to affect the
change.

15. Save the file with the first letter of your first name, your entire last name, and the
title, “Project_3_Excel” as the file name for this file. (ex. J_Doe_Project_3_Excel). DO
NOT Submit this yet. You will add the Part II of this assignment to this file PRIOR to
submitting the file.

Jane Doe's 5th Grade Class
Student              Book Report Vocabulary Math    Reading Art            Total     Average
Abigal Adams                  90         95      85      97           83
Brian Blakely                 99         91      92      98           95
Chris Cooper                 88          84        82        87       84
David Dalton                 75          90        92        88       80
Eartha Evans                 78          79        83        78       82
Francesca Fox                70          71        72        67       70
Greg Gilmour                 79          70        76        70       80
Hanna Huntington             85          85        80        75       75
Ivor Ireland                 70          70        70        70       70
Jake Judd                    71          73        79        72       80
Kerry Kipp                  100          98        75        97       80
Liam Lovel                   85          80        90        80       90
Melissa Mary                 90          96       100        97       92
Neil Nickels                 85          88        89        81       82
Ophelia Orvis                83          90        83        97       97

Class Average

Part II – Excel Project of your choice – 10 points
1. You will generate your own spreadsheet for Part II of this Excel Project. In chapter 3
of the textbook, 5th edition, page 181 there is a Fund-Raiser Spreadsheet example.
You could use this idea for your project. Alternatively, you can do any project of your
choice. The goal is to produce a spreadsheet tracking expenses or costs. You can do
something personal or something fictional. Below is a list of ideas to get you
creative ideas flowing. Use an Internet search to come up with relevant classroom
projects that you could use as a teacher. Once you have your idea, follow the
instructions below to complete this part of the project.

a. Track your school supply expenses for your child, classroom, or college
expenses.
b. Track your annual expenses for a hobby
c. Track your monthly grocery bill
d. Track your recent vacation expenses
e. Track your car mileage, gas prices, and driving distance over a period of time
2. You will use Sheet2 for this, Part II. You should have placed your grade book on
Sheet1. Click on the tab labeled Sheet2 at the bottom-left of the spreadsheet
window.

3. You must have between 10 and 20 rows labeled and 10 to 20 columns labeled for
your spreadsheet. The rows and columns that are labeled count. If you go over 20 in
rows, columns, or both, that is fine. The minimum is 10 rows and 10 columns to
complete Part II successfully.
4. You need to have calculations for some of your columns and rows. It is hard to give
you a number because this is dependent upon your choice of graphic. In the grade
book there were three calculated regions, 2 columns and 1 row. Ultimately, your
instructor will determine if you have an appropriate number.
a. The point is to not have cells filled with values only. In the grade book the
grades you inputted were values and the equations placed in the function
field or copied over were calculations. If you have only entered values and
not calculations in the whole spreadsheet, you are not completing the
assignment correctly.
5. Check to make certain the file is saved with the first letter of your first name, your
entire last name, and the title, “Project_3_Excel” as the file name for this file. (ex.
J_Doe_Project_3_Excel).
a. Submit the project using the Assignment Tool and attach the file to Project 3
Excel, or
b. Submit the file to your instructor according to their instructions
6. Both Part I and Part II will be graded with the following rubric:
Complete Grading Form of Demo Student for Project 03 - Excel

Objective/    Performance Indicators
Criteria      No Credit       Need        Good                 Well Done Excellent       Exemplary
Improvement
book values      (0 points)       (1.8 points)     (2.1            (2.4         (2.7         (3 points)
and labels   No Credit        Grade book has points)           points)      points)      Grade book
three or more    Grade book      Grade book   Grade book   appears as
errors.          has two         has an       has no       designated by
errors.         error.       errors but   the project.
does not
match
project
book             (0 points)       (1.8 points)     (2.1            (2.4         (2.7         (3 points)
functions    No Credit        Grade book has points)           points)      points)      Grade book
applied                       three or more    Grade book      Grade book   Grade book   appears as
correctly to                  errors.          has two         has an       has no       designated by
each cell                                      errors.         error.       errors but   the project.
does not
match
project
book             (0 points)        (2.4 points)      (2.8          (3.2         (3.6         (4 points)
instructions No Credit        The instructions points)         points)      points)      Grade book
followed                      were not          The            There is one Instructions appears as
followed with     instructions   error in     are followed designated by
three or more       were not      following     correctly          the project.
errors.             followed with instructions. with no
two errors.                 deviations.
Part II
instructions     (0 points)        (1.8 points)        (2.1             (2.4          (2.7        (3 points)
followed     No Credit        The                 points)          points)        points)     The spreadsheet
spreadsheet is      The              The            The         was done by an
and the effort is   is complete      is complete    is well
not sufficient      but the effort   but the        conceived
for completion      is not           instructions   and
according to the    indicative of    were not       executed.
instructions.       following the    completely
instructions.    followed.
Part II
labels and       (0 points)       (1.8 points)        (2.1             (2.4           (2.7           (3 points)
values       No Credit        Labels or values    points)          points)        points)        Labels and
are missing,        Labels or        Labels or      Labels and     values are
inaccurate, and     values are       values are     values are     accurate and
unrealistic to      missing or       not            present but    complete.
the project.        inaccurate.      complete or    not
accurate.      necessarily
accurate or
complete.
Part II
calculations     (0 points)        (2.4 points)       (2.8             (3.2           (3.6           (4 points)
and          No Credit        Calculations        points)          points)        points)        Calculations and
functions                     and functions       Calculations     Calculations   Calculations   functions are
are missing and     and functions    and            and            present and
those present       are present,     functions      functions      appropriate and
are not entirely    but some are     are mostly     are present    nothing is
appropriate for     missing or not   present and    and            missing.
the information.    appropriate.     appropriate    appropriate.
with some
missing.
Total: 0 out of
20