Creating a Budget using Microsoft Excel 2000



Creating a Simple Budget Spreadsheet Using Microsoft Excel

Objectives

Student will be able to:

• Enter and edit text and numbers in an Excel spreadsheet.

• Use Excel to create, enter, and calculate a formula on a spreadsheet.

• Resize and format columns and rows of an Excel spreadsheet.

• Save and print an Excel spreadsheet.

Getting Started with Excel

Estimated time: 5 minutes

Open Microsoft Excel.

Microsoft Excel is a spreadsheet program you can use to create budgets, schedules, and simple databases, such as an address book. The Excel screen is arranged in rows and columns. Cells hold the text and numbers you type. You can use the mouse, the arrow keys, and the Tab key to move from cell-to-cell.

If you are creating a document that is largely text, such as a letter or a resume, a word processing program is used (e.g. Word or Word Perfect). Excel is used to create spreadsheets – documents used to organize numbers and data.

Features Unique to Excel

Excel has several unique features. These features include the spreadsheet, cells, the name box, row and column headings, and the formula bar. The following section details these unique Excel features.

The Spreadsheet

A spreadsheet is an application commonly used for budgets, forecasting, and other finance-related tasks that organizes data values using cells, where the relationships between cells are defined by formulas. A change to one cell can produce changes to related cells. Each workbook (file) contains three spreadsheets when you begin. Workbooks can contain numerous spreadsheets, thus you can organize many types of information in one file. Spreadsheets store data, charts and macros. Spreadsheets are made of 256 lettered columns and 65,536 numbered rows.

Cells

A cell is an addressable (named and numbered) storage unit for information. Each row and column in an Excel spreadsheet is unique, so each cell is uniquely identified—for example, cell B17, at the intersection of column B and row 17. Each cell is displayed as a rectangular space that can hold text, a value, or a formula. You can type 255 characters in each cell.

Active Cell

The highlighted cell on the spreadsheet that displays the current focus of operation is the active cell. The active cell is surrounded by a heavy border and is identified by the cell address, which is where the column and row intersect.

[pic]

The Name Box

Located in the top left corner, the name box displays the address of the selected cell. If you type a cell address in the name box and press ENTER you will go to that cell.

[pic]

Row Headings

Row headings label the rows. Rows are a series of cells arranged horizontally. Numbers identify rows. Clicking a row heading selects an entire row.

Column Headings

Columns headings label the columns. Columns are a series of cells arranged vertically. Letters identify columns. Clicking a column heading selects an entire column.

Formula Bar

Located on the top of the spreadsheet, below the toolbars, the formula bar displays text from the selected cell. If there is a formula in the cell, the formula, not the answer to the formula, is displayed in the formula bar.

Activity: Entering Text, Editing Text, and Moving Around

1. Click cell A1 to select it.

2. Type your first and last name in cell A1. Notice how the text appears in both the cell and the Formula Bar.

3. Use the arrow keys to move to cell D1 and type today’s date.

4. Move row 1 down three rows to row 4. To move row 1:

a. Select row 1 by clicking the number 1 on the row heading on the left edge (the row will change color).

b. Move the mouse pointer to any part of the row’s horizontal edge. The mouse pointer changes to a diagonally pointing arrow.

c. Click and drag row 1 to row 4.

Hint: The cell must be active to move it.

5. Click the cell with your name (cell A4).

6. Click and drag cell A4 back to cell A1.

7. Select the date and move it back to the original position in row 1.

8. Double-click cell A1. Notice the blinking vertical insertion point in the cell. You are now in edit mode.

9. While in edit mode, add your middle name to cell A1.

Activity: Calculating a Sum

1. Click cell A2.

2. Type the number 1 in A2, 2 in A3, and 3 in A4.

3. In cell A5 type a formula that will instruct Excel to automatically sum the values of cells A2, A3, and A4.

a. Type the formula =A2 + A3 + A4 in cell A5.

b. Press Enter.

Hint: When entering formulas, after you type an equals sign, instead of typing the cell names (i.e. A3, A4) click the column you want to sum. Excel will put that cell reference in your formula.

Another way to calculate a sum in Excel is to use the AutoSum button. Select cells A3, A4 and A5 by clicking on A3 and dragging the mouse pointer to A5 (the cells will be grayed). Now click the AutoSum button,. [pic]

Unless a range of cells is selected, AutoSum will by default, sum the cells above the active cell, if no data is there, it will sum the cells to the left of the active cell. If no range is selected, AutoSum only sums cells that continuously have data (i.e. if there is data in cells A1 and A3-A5, only the data in cells A3 through A5 will be summed).

4. Close the spreadsheet and start with a clean sheet – no need to save your work yet.

a. Click File and choose Close.

b. When asked if you would like to save the spreadsheet click No.

c. Click [pic] on the Standard toolbar to open a new workbook.

Review Questions

1. What are some of the similarities between Microsoft Word and Microsoft Excel?

2. What are two ways to calculate a sum?

3. How do you change the contents of a cell?

Creating a Monthly Budget Using Excel

Estimated Time: 35 Minutes

Those were the basics. As you move through this lesson you will pick up more skills, as well as practice the ones you have gone over.

In the next activity you will create a monthly budget. An electronic budget is a great tool for keeping yourself organized and current. With an electronic budget you can easily update information as well as communicate changes (e-mail, disk, etc.).

Activity: Creating a Monthly Budget

Follow the directions on the Creating a Monthly Budget handout to create your own personal budget.

Setting Up Budget Categories

Step 1: Start Microsoft Excel and open a new spreadsheet.

Step 2: Click on A1 cell and enter Categories. Press the Enter key.

Step 3: Enter the word Income in cells A2 and A3.

Step 4: Enter the word Living in cells A4 through A10.

Step 5: Enter the word Other in cells A11 through A15.

Step 6: Enter the word Description in cell B1. Press the Enter key.

Step 7: Enter, in cells C1 through H1, the months January through June.

Step 8: Drag over the first row.

Step 9: Click B (boldface icon) on the toolbar.

Step 10: Drag over the first column.

Step 11: Click B (boldface icon) on the toolbar.

Step 12: Drag over the cells B2 and B3.

Step 13: Enter the name(s) of people earning income in your household.

Step 14: Drag over the cells B4 and B10. This highlights the B cells for the Living categories.

Step 15: Enter Rent, Food, Water, Trash, Gas, Electric, and Telephone in these cells. (Substitute other descriptions depending on your needs.) Press the Enter key after each entry.

Step 16: Drag over the cells B11 and B15. This highlights the B cells for the Other categories.

Step 17: Enter Books, Tuition, Transportation, Medical, and Housekeeping in these cells. (Substitute other descriptions depending on your needs.)

Entering the Numbers

Step 1: Drag over the cells C2 through C15.

Step 2: Enter your budget amounts (the amount of money you anticipate spending). For example, under Rent, enter 1200.

Step 3: Drag over the cells C2 through C15.

Step 4: Open the Edit menu and select Copy.

Step 5: Drag over the cells D2 through H2.

Step 6: Open the Edit menu and select Paste.

Step 7: Modify your new entries according to your actual monthly expense amounts, if available. For example, Tuition for the months of February through June could be 0.

Totaling Your Budget

Step 1: Drag over the cells that contain your budget and the labels.

Step 2: Open the Data menu and select Subtotals.

Step 3: In the Subtotal dialog box, ensure Categories is selected in the "At each change in" option.

Step 4: Select January, February, March, April, May and June in the "Add subtotal to" option.

Step 5: Click OK.

Step 6: Select the Grand Total row.

Step 7: Open the Edit menu and select Delete.

Step 8: In the box that appears, select Entire Row, then click OK.

Step 9: Enter the label Monthly Savings in the cell where the title Grand Total was located.

Step 10: Click in the cell in the Monthly Savings row and the January (C) column.

Step 11: Enter + and the cell name of the Income total. For example, +C4.

Step 12: Enter -. For example, +C4-

Step 13: Enter ( and the cell name of the Living total. For example, +C4-(C12

Step 14: Enter + and the cell name of the Other total. For example, +C4-(C12+C17

Step 15: Enter ) and press the Enter key. For example, +C4-(C12+C17)

Step 16: Repeat for each remaining month.

Review Questions

4. How do you move the data from one cell to another?

5. What is the AutoSum button [pic] used for?

6. How do you select an entire row?

7. What are you telling Excel when you type a equal sign (=) into a cell?

If You Have Time

The next activities examine further formatting functions of Microsoft Excel. For these activities you will need a new Excel worksheet.

Activity: Using Autofill and Freeze Pane

AutoFill — Fill in a series of numbers, formulas, dates, or other items

The AutoFill function of Excel is a quick and handy function for “finishing what you started.” For example, if you are typing in the months of the year, beginning with January, you can use the AutoFill to automatically finish the list. This function works for numbers and formulas as well.

1. In cell A6 type January. You will now automatically fill in the preceding months below January.

2. Select cell A6 (the first cell in the range you want to fill) drag the fill handles over the range you want to fill.

Hint: To acquire the fill handle, select the beginning cell in the range (in this case A6) and move the mouse pointer to the black square in the lower right corner of the active cell. The mouse pointer changes to a +. Drag the mouse down (or to the right) to fill in increasing order. To fill in decreasing order, drag up or to the left.

3. In cell B6 type ‘2001. You will now autofill the dates

Hint: When typing numbers as values (numbers that will not be in a formula; a label or year for example) proceed the number with an apostrophe (‘).

4. Autofill 2001 in the B row to cell BH. Cell BH should be 2007.

Freeze Pane – Keep row and column labels visible as you scroll

There will be times when your spreadsheet expands beyond the view of your desktop. For spreadsheets like these, Excel enables you to “freeze” your column and/or row headings – allowing you to always know what column or row you are in. This feature is essential when working with numerous headings.

• To freeze the top horizontal pane, select the row below where you want the split to appear.

• To freeze the left vertical pane, select the column to the right of where you want the split to appear.

• To freeze both the upper and left panes, click the cell below and to the right of where you want the split to appear.

After you have selected the row and/or column you want to freeze, on the Window menu, click Freeze Panes.

If You Remember Nothing Else

Microsoft Excel is a spreadsheet program you can use to create budgets, schedules, and simple databases, such as address books.

The Excel screen is a spreadsheet made of cells that hold text and numbers arranged in rows and columns.

Review Answers

1. Toolbars, menus, print, save, spell check, help, etc.

2. You can type the cell locations separated by plus (+) signs. You can also use the AutoSum function.

3. Double-click the cell to be edited to enter the edit mode. You can edit in the cell directly or from the formula bar.

4. Click and drag, or cut and paste.

5. To quickly add a series of numbers.

6. Click a row heading (a number).

7. You are telling Excel to act on what you enter, rather than display what you type.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download