Basic Excel/Budget. ARTs. Area Research and Tutorials BASIC EXCEL/BUDGET TUTORIAL COURSE. Jumpstart Education.. We get you where you need to be

23  Download (0)

Full text

(1)

Area Research and Tutorials

BASIC EXCEL/BUDGET

TUTORIAL COURSE

ARTs

ARTs

Jumpstart Education …..

We get you where you need

(2)

Area Research and Tutorials

This document should be downloaded and used during the class session. The

instructor lecture should take about 60 to 70 minutes. The course material is

designed to introduce to you the basic must know information and to be used over

and over for references. This document can be used without taking the class but

that will depend on factors such as previous subject matter knowledge. This

course will give you the must know information which you will need to get started

using the essentials

The information contained in this documentation is provided for general purposes

only. Depending on your personal computer and operating system, your screens

and procedures may vary.

For best learning results, please print this document and use it when listening.

Go to: www.artutorials.org, select courses to download course documents.

(3)

Basic Budget w/ Excel Table of Content

Area Research and Tutorials ... i

Basic Budget w/ Excel Table of Content ... 1

Excel Budget Basic Objectives ... 2

Excel Terms and Icons ... 3

Select the Starting Cell ... 4

Auto-Fill Copy ... 5

Entering Expense Titles ... 6

Entering Expense Costs ... 7

Copy and Paste Technique ... 8

Functions ... 9

Equations ... 10

Relative Copying ... 11

Modifying/Updating Cells Entries ... 12

Inserting a New Row ... 13

Copying Columns ... 14

Change the Numbers to Currency Format ... 15

Adding Additional Sections ... 16

Graphing Data ... 17

Basic Microsoft Excel Exercise ... 18

Basic Microsoft Excel Exercise Continue …... 19

Basic Microsoft Excel Exercise Continue …... 20

(4)

Excel Budget Basic Objectives

Select the Starting Cell

Auto-Fill Copy

Entering Expense Titles

Entering Expense Costs

Copy and Paste Technique

Functions

Equations

Relative Copying

Modifying/Updating Cells Entries

Inserting a New Row

Copying Columns

Change the Numbers to Currency Format

Adding Additional Sections

(5)

Excel Terms and Icons

 Rows are the numbers down the left-hand side. Rows numbers range from 1 to 65,536.

 Columns are the letters cross the top. Column letters range from A to IV, after column Z is column AA, after AZ is BA, and so on to IV.

 A Cell is the intersection of a column and a row, example “A1” means column A and row1

 Scissor is used to cut selected data from one location and paste to another area. The original data is removed.

 Clipboard and paper is used to paste data, after cutting or copying data. This icon will be colored in when it is possible to paste data.

 Two sheets icon is used for copying data from one location to another. The original data remains.

 Moving around by using the Directional Arrow and/or the Enter Keys

 There are always several ways to accomplished most tasks

We will use more of the icons from the tool bar and they will be

explained later.

(6)

Select the Starting Cell

To development our budget spreadsheet, let’s start in cell A5 (not so important). Enter the titles Income, and sub-titles Salary1 in cell B6 and Salary2 in cell B7. Type Totals in cell A8 as you see above.

When you are typing in a cell and afterward, the content is shown in the Formula bar fx. Typing a location here will move the cursor.

The current selected cell is called the active cell “A9” and is referenced left of the Formula

Bar.

The column and row containing the active cell are highlighted, making it easier to identify There are 256 columns and 65,536 rows, so a worksheet contains over 16 million cells. The number of cells you can use at any one time is limited by the amount of memory your computer has.

(7)

Auto-Fill Copy

Use the directional arrow keys to move to different cell locations. Enter 01/01/2010 and 02/01/2010 in cells C5 and D5 respectively. Notice the point located at the lower right corner of the cell D5. Drag this point across and to cell N5. The dates will be added continuously, but by days

After dragging, you will see a pop-up box with a + sign. Click the + sign or dropdown arrow, and a menu will appear. This menu has radio buttons with autofill options. Click on Fill Months to change the dates to continuous months.

 Copy Cells

 Fill Series

 Fill Formatting Only

 Fill Days

 Fill Weekdays

 Fill Months  Fill Years

If you highlight C5 and D5, then drag D5 to N5 the dates will continue the series in Months

(8)

Entering Expense Titles

Add the Expenses titles and the Total row. Click on the cells (Totals) then clicking the right-side alignment icon. You can change both Total cells by using the Ctrl-key. We will explain how to insert more rows for additional expenses later.

Letters will always be left-side aligned and numbers will always be right-side aligned by default. However, cell alignments can be changed in using various techniques. Using the technique above is one way to align smaller sections of a worksheet.

 Click on a column or columns (by highlighting multiply columns) and clicking any three alignments (Left, center, right) to align all of the selected cells

 Click on a row or rows (by highlighting multiply rows) and clicking any three alignments (Left, center, right) to align all of the selected cells

(9)

Entering Expense Costs

Enter the estimated amounts for the salaries for only the first month. Also enter the costs for the expenses for only the first month, as you see above in the left sheet. These costs can be easily adjusted at anytime in the future. These figures are only used as a place-holder and should be replaced with the correct values when they are known. This will be explained.

To copy these first month figures to the other months, start by:

 Select all the first month entries (by dragging the mouse from the first to the last cell) which identifies what to copy

 Start the copy command by clicking the copy icon (double pages) on the task bar or by clicking the right mouse button and clicking copy. This tells Excel what you what to do with the selected cells.

(10)

Copy and Paste Technique

Continued …..

 Tell the copy command where you want the data, by clicking in cell D6 and dragging the mouse to the last month N6 (N6 should be for 12/1/2010)

 To place the selected cells, click the Paste icon on the task bar (clipboard & paper), or right click the mouse button and then click Paste.

 Press the Esc key to terminate the copy command and the selected cells, because you can paste the selected data in more than one place.

After pasting the data from column C to the other columns, ranging from D to N, all the rows are relatively pasted in position. In other words, the placement of the selected cells are determined by the top row.

(11)

Functions

Click in cells C8 and C20 to select where you want the answer to the Sum function placed. Then click the Sum icon on the task bar and all of the connecting cells will be selected to be added. The selected cells will be displayed in the formula bar and have a blinking border. Press Enter to complete the task.

Performing these steps in cell C8 will select C6 and C7 to be added. Clicking in cell C20 will select C17 to C19 only. The sum selection will stop when an empty cell is encountered. Press Enter to accept these answers for now.

To have cells C11 to C19 added and have the sum place in C20, Click in cell C20 to display that function in the formula bar, then edit the formula listed to be “=SUM(C11..C19)” or you can just type this in cell C20.

(12)

Equations

In cell A22, enter Saving and in cell B22, enter 5000. This will represent your previous available funds

The equation we want is to add all the available incomes and subtract all the expenses from that total. In B22 has the available Saving and C8 has the total income for the first month which is B22+C8. C20 has the total expense so this must be subtracted from the total available income. Equations must start with an “=” sign. This means you are not typing in a letters but you want the contents of the cell, so =B22+C8-C20 will equate to what you will have left at the end of the first month.

(13)

Relative Copying

Use Auto-Fill to copy the Totals from C8 across the other months (D8 to N8), Totals from C20 across the other months (D20 to N20), then do the same Auto-Fill to copy the Saving totals from C22 across the other months (D22 to N22).

Did it work correctly? Let’s examine - get your calculator: C8 = C6+C7 => 5000 + 4000 = 9000

C20 = C11 to C19 => 2000 + 200 + 100 + 100 + 400 + 500 + 100 + 100 = 3500 C22 = B22 + C8 – C20 => 5000 + 9000 – 3500 = 10500

Now:

click in cells D8, E8, to N8 to verify the sums includes the correct cells ( via formula bar) click in cells D20, E20, to N80 to verify the sums are correct ( via formula bar)

click in cells C22, D22, E22, these figure are the total incomes minus the total expenses for a month, plus the saving from the previous month.

C22 = “=B22+C8-C20” D22 = “=C22+D8-D20” E22 = “=D22+E8-E20”

(14)

Modifying/Updating Cells Entries

Left Screen

Right Screen

Rule# 1: Always save your worksheet before making change changes. This allows you to close without saving your new changes and returns to where you started.

The Left Screen shows 500 added for insurance 3/1/2010, in C16. This increase the total expenses from 3500 to 4000, and the total saving is decrease from 21500 to 21000. The totals are always automatically re-calculated.

The Right Screen shows the misc changed to church in B18. This was done by over typing in the cell or in the formula bar. To have church expense in cell C18 equal to 10 percent of your total income for that month, enter “=C8*0.1” => 900. Copy this C18 to D18,, N18 using Auto-Fill. The totals will be calculated using the correct cell for that month income.

Therefore, we start this budget using some estimated costs, but as the actual costs are known, they should be changed by typing over what’s there.

(15)

Inserting a New Row

Left Screen Right Screen

To insert a row, click Insert on the Task Bar, and then click Rows on the menu. Rows are always inserted above the selected row or cell.

Left Screen:

The selected row was 20 (or any cell could have been selected on row 20), see the previous page, C20 became C21 and a new row 20 was inserted. The sum formula does not include the new row C20.

Right Screen:

The selected row was 19 (or any cell could have been selected on row 19), see the previous page, C19 became C20 and a new row 19 was inserted. The sum formula does include the new row C19.

Always insert a row within the sum range, to have the new row included. Hint: I always keep an extra misc expense just for this reason.

(16)

Copying Columns

To modify your budget worksheet to match your date in time, you will need to add and delete columns. Place the cursor in the column-row, then select the desired dates, by pressing and dragging. In this example, we included only 3 months, then click the copy icon. The selected columns will have a blinking border.

Our example has 1/1/2010 to 12/1/2010 in columns C to N respectively. So to continue a new year with 1/1/2011, we must copy any previous months after the last month (12/1/2010), so click on column O and click the Paste icon.

The dates will not be correct but the figures will, so perform an Auto-Fill Copy for the new dates. Select columns “M and N” and drag the bottom right corner of the cell over all the other cells. If previous months have passed and you want to remove them, you can select those months (columns), right click for the menu, and select Delete or press the Delete key.

You can also hide these columns by selecting the months (with the passed months), Click Format on the task bar, click columns, click Hide. Click Unhide to later view the columns.

(17)

Change the Numbers to Currency Format

Highlight the cells you want to change to currency format and click the “$” on

the task bar.

Change the saving by selecting B22 (cell) and then clicking the “$”

(18)

Adding Additional Sections

This can be accomplished so many ways.

1. Select Row 22 ( by clicking on the row column), click cut ( scissor icon) and pasta at row 28. 2. Type in cells:

a. A22, enter Donations b. B23, enter Red Cross c. B24, enter Churches d. B25, enter School

e. A26 enter Totals, right justify/Align this cell f. C23 and C25 enter 100

g. C24, select it, enter =C8*.10

h. C26, click to sum icon and then enter.

3. Copy Cells C23 to C26, to Columns D to Q, This can be done by using Auto Fill. 4. While these cell are highlighted (selected) click the “$” for currency format

5. Delete row C18 (church expense) from the Expense Section: Click on Row 18, right click mouse and click Delete

6. Budget Result Total = Saving + Expense Totals – Expense Totals – Donation Totals a. =B27+C8-C19-C25

(19)

Graphing Data

Graphing your data can be easily accomplished using the Chart Wizard. Charts consist of figures and labels. Select the data and the representing labels, we used B11 to C18. Afterward click the Chart Wizard icon on the Task Bar. It will ask:

 Chart type

 Select Pie

 Select which type of Pie Chart via the pictures

 Click Next on the bottom to proceed

 Click in the name field and type January

 Click Finish to complete a simple chart

 This Chart will represent the expenses for the Month of January

Click on this Chart and drag it where you like it to be located. To Chart the month of February, simply hint column C and repeat the steps again.

Please Fill out the course evaluation, go on-line to:

Start your Web-browser and go to address:

www.artutorials.org

(20)

Basic Microsoft Excel Exercise

Lab Assignment is to create a Weekly Budget just like our Monthly above:

1. Select the Starting Cell and auto-fill copy

Use the directional arrow keys to move to different cell locations. Enter 01/07/2011 and 01/14/2010 in cells C5 and D5 respectively. Notice the point located at the lower right corner of the cell D5. Highlight cell C5 and D5, then drag this point across and to cell N5. The dates will be added continuously by weeks.

 Cursor Movement: Press the tab key, Shift-Tab keys, and the Enter key

2. Entering Income and Expense Titles

Let’s start in cell A5 (not so important). Enter the titles Income, and sub-titles Salary1 in cell B6 and Salary2 in cell B7. Type Totals in cell A8.

Add the Expenses title in cell A10, the expense subtitles in B11, B12, and so on. Enter the Total title in cell A19. Example below:

3. Entering Expense Costs

Enter the estimated amounts for the salaries for only the first week. Also enter the costs for the expenses for only the first week, be as accurate as possible. Example Below:

(21)

Basic Microsoft Excel Exercise

Continue

….

4. Copy and Paste Technique

Because some months have more weeks than others, I recommend copying one week at a time. Copy the first week 01/07/2011, and paste this to 02/04/2011, 03/04/2011, etc

Highlight cells C6 to C18, click right mouse button, then select copy. Go to G6, right click right mouse button, then select paste. Repeat this copy and paste for each week to the other months.

5. Functions

Use the summation function to add the total incomes and expenses. In cell C8 type “=SUM(C6:C7) and in cell C19 type “=SUM(C11:C18)” .

(22)

Basic Microsoft Excel Exercise

Continue

….

6. Equations (

Balance = starting funds + income – expenses

)

We need an equation to figure the balance at the end of each week. Enter the word ”Balance” in A21. Assuming you have $1000 starting, enter 1000 in B21. Enter in C21 the starting fund (B21) + Total Income (C8) – Total Expenses (C19). Equations must start with an “=” sign ( =B21+C8-C19).

(23)

Basic Microsoft Excel Exercise

Continue

….

7. Relative Copying

Copy the Total Income, Total Expenses and Balances from week 01/07/2011 to the other weeks. Click cell C8, click the right mouse button and select Copy. Click in cell D8 and drag across the other weeks (E8,F8,…), then press Enter. Repeat these steps for Total Expense C19 and Balance in C21.

Figure

Updating...

References

Related subjects :