Docstoc

Household Budget Spreadsheet - PDF

Document Sample
Household Budget Spreadsheet - PDF Powered By Docstoc
					       Yellowknife Catholic Schools
                     (www.ycs.nt.ca)




 “A series of tutorials to assist with the implementation of
  the Information and Communication Technology (ICT)
                          curriculum”




Spreadsheet - A Beginner Lesson
Macintosh version (AppleWorks)
Yellowknife Catholic Schools   Spreadsheet – A Beginner Lesson   Page   2
Spreadsheet - An Introduction

What is a spreadsheet...

A spreadsheet is simply rows and columns of data. The term comes from
the field of accounting where at one time accountants kept track of
business activities on large sheets of paper that spreadout to form a
“spreadsheet”. Spreadsheets are record keeping tools that work primarily
with numbers.

Computerized spreadsheets allow the user to perform calculations on
data. The primary advantage of a computerized spreadsheet is its ability
to perform calculations on data and to automatically recalculate when
changes are made.

When to use a spreadsheet ...

Use a spreadsheet when you want to organize text and numbers in rows
and columns. For example you can use a spreadsheet to ...

    • develop a budget.
    • track loans and mortgages
    • maintain a chequebook
    • manage business and personal assets


Elements of a spreadsheet ...

Several terms which you should be familiar with when you create a
spreadsheet are ...

    •   All data is entered in the entry bar at the top of the spreadsheet.
    •   The letters A,B,C, etc at the top of the page are the Column headings.
    •   The numbers 1,2,3 etc at the left of the page are the Row headings.
    •   You place information in a cell.
    •   The lines which mark the boundaries of a cell are called the cell grid.
    •   Clicking on a cell makes that cell an active cell.

Yellowknife Catholic Schools    Spreadsheet – A Beginner Lesson              Page   3
Designing a spreadsheet ...

The first step in creating a spreadsheet is to plan its design. You will have
to decide on the type of information you want to store. Once this is done
you should decide on the tasks you want the spreadsheet to perform. You
should use paper and pencil to put your plan together.


Working with this spreadsheet tutorial ...

As you work through the tutorial you will be presented with information
or asked to perform certain tasks.

    • Important information will be preceded by a dot bullet.

     Tasks which you will be asked to perform will be preceded by an arrow
      bullet.

 (Note: Occasionally you will see information in brackets which will have an
important message or directions for you to follow.)




Yellowknife Catholic Schools    Spreadsheet – A Beginner Lesson                Page   4
Creating a beginner spreadsheet ...

The topic for this spreadsheet is Household Budget. A household budget
spreadsheet can be used as a way for you to keep track of your household
expenses.

AppleWorks 6.2.9 was used as the program to complete the spreadsheet. You
can easily use the ideas from this spreadsheet to create a spreadsheet with
other computer programs.

The tutorial which follows will show you how to create a spreadsheet.
Upon completion of this tutorial you will have an understanding of what
a spreadsheet is and how to create one.



You will be able to ...

        • define a spreadsheet

        • design and create a spreadsheet

        • enter data in the spreadsheet

        • save a spreadsheet

        • format the spreadsheet document

        • enter a formula and function

        • use the sum function

        • select cell ranges

        • make a pie chart of your data




Yellowknife Catholic Schools   Spreadsheet – A Beginner Lesson         Page   5
Getting Started...
Before you begin this tutorial you will have to locate and open the
AppleWorks 6.2.9 program. If you use the Launcher or the Dock, just click on
the AppleWorks icon to open the program.

If you need to locate AppleWorks on the hard drive follow the steps outlined
below …


     Double click on the hard drive icon usually located in the upper right
      hand corner of your screen.

     Find the AppleWorks folder and double click on it to open.

     Locate the AppleWorks icon and double click on it to open
        the AppleWorks program.

     When the New Document screen below appears, choose
      Spreadsheet from the window. Click once on Spreadsheet.




Saving the spreadsheet ...

     When you click on the Spreadsheet button a new spreadsheet document
      will open as shown in the picture on the next page.



Yellowknife Catholic Schools   Spreadsheet – A Beginner Lesson           Page   6
Notice the word untitled at the top. This indicates the document is not saved.
Before you continue beyond this point you should follow these steps to name
your spreadsheet document and save it.

     Go to File in the menu bar at the top of the screen. Scroll down until you
      come to Save as ... A window similar to the one below appears.




     Replace the word untitled with Household Budget by typing over it.


Yellowknife Catholic Schools   Spreadsheet – A Beginner Lesson            Page   7
     Decide where you wish to save your document. Save to the desktop if
      you are not sure where you should save it. Click Save.

(Note: It is a good idea to save the spreadsheet every few minutes so you don’t
accidentally lose your work.)

Format the spreadsheet document...
The spreadsheet which you will create will only contain one page. Therefore
you will find it helpful and perhaps less confusing if you make some
document changes.

     Go to the Format menu. Choose Document as shown below.




Yellowknife Catholic Schools   Spreadsheet – A Beginner Lesson                Page   8
    • A window will appear as shown in the picture below.




     Enter 6 in the Columns across box. Enter 40 in the Rows down box.
      Click OK.

     Go to the Window menu. Choose Page View.

    • Your spreadsheet will now look like the one shown below.

(Note: The spreadsheet below shows 15 rows down. Your actual spreadsheet will have
40 rows down.)




Yellowknife Catholic Schools   Spreadsheet – A Beginner Lesson               Page    9
Entering data...
You are now ready to enter your data and begin to create a spreadsheet.
You will enter three types of data in the spreadsheet - numbers, words,
and formulas. When you are finished, the spreadsheet will show your
income and expenses.

(Note: - All information will appear in the Entry Bar. If the information is correct press
return to enter the information in the appropriate spreadsheet cell.)




     The active cell is cell A1. If not, click on A1. Type Household Budget in the Entry Bar as
      shown below.




     Press return. Your spreadsheet will look like the one on the next page.




Yellowknife Catholic Schools        Spreadsheet – A Beginner Lesson                       Page 10
     Complete your spreadsheet by filling in the cells with the data as shown
      below.




Entering a function...
Once the words and numbers are entered you are ready to enter a formula
to add the total for Income and Expenses. Notice Salary and Other are
listed under the Income column. Several expenses are listed under the
Expenses column. The total in these columns can be found by using the
sum function.


Yellowknife Catholic Schools   Spreadsheet – A Beginner Lesson           Page 11
(Note: A function is a predefined formula you can either type or paste in a spreadsheet
to perform a calculation. Functions can simplify formulas that would otherwise be
complex.)

     Click cell B9 to make it active.

     Go to the Edit menu. Choose Insert Function as shown below.




    • When the Insert Function window appears you will see a list of
      functions .

     Scroll down to choose SUM as shown in the picture below. Click OK




Yellowknife Catholic Schools     Spreadsheet – A Beginner Lesson                  Page 12
     When you choose SUM the function is automatically pasted into the
      entry bar as shown below.




     Delete number1,number2,... inside the brackets.

(Note - The cursor should remain inside the brackets when you delete
number1,number2.... If you have moved the cursor, you can click between the brackets to
reinsert it.)




     Click on B6. Click on B7. The formula will be entered in the Entry Bar as
      shown below.




Yellowknife Catholic Schools    Spreadsheet – A Beginner Lesson                 Page 13
     If your formula is correct click on the checkmark in the Entry Bar or
      press return.

    • The sum of cells B6 and B7 is placed in cell B9 as shown below.




Follow these directions to calculate the sum of the items under the
Expenses column.


     Click on E16.

     Go to the Edit menu. Choose Insert Function.

     Choose SUM from the Paste Function window. Click OK. Delete
      number1,number2,... from inside the brackets of the function when it
      appears in the Entry Bar.

(Note - The cursor should remain inside the brackets when you delete
number1,number2.... If you have moved the cursor, you can click between the
brackets to reinsert it.)

     Click E6. Keep the mouse clicked and drag to Click E14. Press return.

(Note - Clicking on E6 and dragging to E14 has the same effect as clicking on E6,
typing .. and clicking on E14.)

            • The total of the Expense column will appear as shown below.


Yellowknife Catholic Schools     Spreadsheet – A Beginner Lesson                    Page 14
You have created a spreadsheet which calculates your income and expenses.
Follow the directions below to setup the spreadsheet to subtract the total
expenses from the total income.

(Note: You will enter a formula in cell D19 which will subtract the total Expenses from
the Total Income. You will use the SUM formula as you did in the previous section.
However, instead of using Paste Function as in the previous section you will simply
type the formula as in the directions below.)

     Click on cell B19 to make it active. Type Balance. Press return.

     B20 should be the active cell. If not, click on cell B20 to make it active.
      Type (Income-Expenses). Press return.

     Click on cell D19 to make it active.

     Type =SUM(B9-E16). Press return.

(Note - Entering the formula directly into the Entry Bar has the same result as using the
Paste Function method to enter the formula. Remember if you enter formulas directly
into the Entry Bar you must always type = at the beginning of the formula.)



Yellowknife Catholic Schools     Spreadsheet – A Beginner Lesson                    Page 15
                      • Your screen will appear as in the picture below.




Graphing your data...

You can easily make a graph of your data by following these directions...

     Select D6 and drag the cursor diagonally to E14 to highlight the block of
      cells to be graphed.




Yellowknife Catholic Schools        Spreadsheet – A Beginner Lesson         Page 16
     Go to the Options menu. Choose Make Chart.

     Click on Pie as shown below. Click OK.




    • When you click OK the graph below appears.




Yellowknife Catholic Schools   Spreadsheet – A Beginner Lesson   Page 17
(Note: A message as shown below will appear if you have not highlighted enough data to
make a chart. In this case, highlight the data required for the chart and repeat the Make
Chart steps above.)




     Drag the graph below the spreadsheet data. Your document will look
      like the picture on the next page.




Yellowknife Catholic Schools    Spreadsheet – A Beginner Lesson                  Page 18
Yellowknife Catholic Schools   Spreadsheet – A Beginner Lesson   Page 19
As you can see from working through this beginner lesson a spreadsheet
can prove to be a useful tool.

There are other lessons to help you gain a better understanding of a
spreadsheet. Before you attempt them you may find it useful to go through
this tutorial again especially if this is your first attempt at creating a
spreadsheet. Remember to save this document for use with the Intermediate
Spreadsheet tutorial.




Yellowknife Catholic Schools   Spreadsheet – A Beginner Lesson        Page 20
                               Technology Curriculum Project

The Technology Curriculum Project was a joint initiative of Yellowknife
Education District No. 1, Education, Culture and Employment and
Yellowknife Catholic Schools. The scope of the project was to assist teachers
integrate technology into the curriculum.
This tutorial is a modified and updated version of the original tutorial created
as part of the Technology Curriculum Project.

I would appreciate any comments from you regarding this word
processing tutorial. Please provide comments to …



David Murphy
Technology Curriculum Coordinator
Yellowknife Catholic Schools
5124-49 Street
PO Box 1830
Yellowknife, NT X1A 2P4

Email:                    david_murphy@mail.ycs.nt.ca
Website:                  www.ycs.nt.ca
Telephone:                867-766-7415




Yellowknife Catholic Schools        Spreadsheet – A Beginner Lesson        Page 21

				
DOCUMENT INFO
Shared By:
Stats:
views:451
posted:6/29/2009
language:English
pages:21