Household Budget Spreadsheet

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. Spreadsheet – A Beginner Lesson Page 3 Yellowknife Catholic Schools 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: Website: Telephone: david_murphy@mail.ycs.nt.ca www.ycs.nt.ca 867-766-7415 Yellowknife Catholic Schools Spreadsheet – A Beginner Lesson Page 21