Bill Tracking Spreadsheet Excel - DOC by xuy16262


More Info
									                                               Project 3:
                                           Excel Spreadsheet
                                             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
  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
  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

  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
         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

   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.
          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
Part I grade
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
Part I grade
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
Part I grade
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
                              not complete        spreadsheet      spreadsheet    spreadsheet expert.
                              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
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
                                                                                                   Total: 0 out of

To top