Microsoft Excel & Budgets

Objective: To practice using Excel by creating a sample budget workbook and charts to display the data.

Exercise: Create a Budget Workbook

1. What’s Your Objective?

To create a spreadsheet into which we will enter and calculate budget information.

2. Type the Labels

Account Number, Description, Last Year Budget, Last Year Actual, This Year Budget

3. Fill-in the Numbers

· Account Numbers (use Autofill), descriptions, budget numbers.

· Don’t enter a number for Current Property Tax (2002 Budget). We’ll come back to it later.

4. Write the Formulas

· Total the columns (type formula or use AutoSum)

5. Format the Worksheet

a. Make labels bold

b. Put in currency format

c. Adjust width of columns

d. Insert a row at the top

i) Type “Revenue” (without quotes)

ii) Merge cells A1 & B1

iii) Format new cell A1 (bold, larger text, etc.)

e. Bold totals row. Change the text color, if you like.

f. Put a line above the totals row

g. Put a line below the top labels row

h. Add background color wherever you like.

6. Rename this worksheet “Revenue”

7. Create a General expenditure table in another worksheet.

a. Click on the tab for Sheet 2.

b. Type descriptions and numbers for 2001 Budget, 2001 Actual, and 2002 Budget.

c. Use Autofill to insert account numbers.

d. Type the formula for 2001 Diff. (=C3-B3) and copy it to the rest of the column.

e. Use Autosum to calculate totals.

f. Format the worksheet same as Revenues worksheet.

g. Adjust column widths, if necessary.

h. Double click on the tab that says “Sheet 2” and type the new name: “General”.

8. Create a Highway expenditures table in another worksheet (“Sheet 3”).

a. Copy “General Expenditures” worksheet data into this worksheet.

b. Replace descriptions and numbers for 2001 Budget, 2001 Actual, and 2002 Budget.

c. Add or delete rows, as necessary.

d. Use Autofill to insert account numbers.

e. Make sure calculations are still correct.

f. Adjust column widths, if necessary.

g. Double click on the tab that says “Sheet 3” and type the new name: “Highway”.

9. Insert another worksheet (INSERT/WORKSHEET). This is where we’ll put the budget summary information.

a. Type the labels.

b. Since the total highway expenditures, general expenditures, and revenues have already been calculated in the other worksheets, we won’t need to re-create the same formulas. Instead, we’ll just refer to the cells that contain them.

c. In the column labeled “2001 Budget”, insert the following formula in the Highway Expenditure row:

=Highway!C8

If your worksheet has the total 2001 highway budget in another cell, then substitute that cell reference for the “C8”.

d. Enter similar cell references for the 2001 Budget General Expenditure, the 2001 Actual expenditures, the 2002 Budget, and the Total Revenue row.

e. After the “2002 Budget” column, type the following label (without the quotes): “% Change (2001-2002)”.

f. In this column, we want Excel to calculate the percent change from the 2001 budgeted amount to the 2002 budgeted amount. To do this, enter the following formula in cell F3:

=(E3-B3)/B3.

g. Copy this formula to cells F4 and F5.

h. Under the “Total Revenue” row, in cell A7, type the following label: Revenue-Expense

i. In B7, type the following formula: =B6-B5

j. Copy the formula to cells C7, D7, and E7.

k. The amount in cell E7 will tell us how much we need to plug into “Current Property Tax” on the Revenue worksheet in order to have a balanced budget in 2002. Note this number, return to the “Revenue” worksheet, and enter the number in the blank cell in the “2002 Budget” column. [The number should be $204,000.00.]

l. After you do this, go back to the “Summary” worksheet and note how the numbers have changed. Cell E7 should be zero, and the percent change in revenue should have changed dramatically.

m. Format the worksheet so it looks nice. Consider using Text Wrap for one or more of the columns to keep the columns from becoming too wide.

n. Move the worksheets to put them into an order that makes sense to you.

10. PAGE SETUP

Now it’s time to format the page using “Page Setup.” Click on FILE, then PAGE SETUP. There will be several tabs: PAGE, MARGINS, HEADER/FOOTER, SHEET.

NOTE: You must set-up each worksheet in your workbook separately. This gives you additional flexibility in printing and formatting, since you can do each page differently depending on your needs.

a. On the PAGE tab, make sure that the orientation is set to “Portrait” and that we’re printing 100% of normal size.

b. On the MARGINS tab, set the margins to whatever you prefer.

c. On the HEADER/FOOTER tab, lets add a custom footer.

i) Click on the “Custom Footer” tab.

ii) With the cursor in the “Left” section, click on the “Date” button to insert the date. This will automatically print the current date whenever you print the worksheet.

iii) With the cursor in the “Center” section, click on the “File” button to insert the filename. Type a comma and a space. Then click on the “Tab” button to include the worksheet name.

iv) With the cursor in the “Right” section, click the “Page #” button to insert the page number. Then insert a space and type the word “of”. Then insert another space and click on the “Total Pages” button to insert the total number of pages.

v) Click OK. You can then see an example of what your footer will look like.

d. On the SHEET tab, you can do a number of things:

i) If you only want to print a portion of the worksheet, this is where you set the print range.

ii) Choose “Rows to Repeat at Top” to choose the “title” rows that will automatically print at the top of each page in the event that your worksheet extends for more than one page. This eliminates the need to manually insert the title rows wherever you think the page will break.

iii) Choose “Columns to Repeat at Left” to determine which columns will repeat on the left of every page.

iv) You can also choose to print the gridlines to make the worksheet easier to read, to print row and column headings, etc.

11. PRINTING

a. Click on FILE, then PRINT.

b. You can print certain pages, a selection, only the active sheet, or the entire workbook.

___________________________________________________

BONUS – CHARTS

Sometimes a chart is the best way to show data, and they’re fairly simple to create in Excel. For example, let’s create a pie chart showing each budget category as a percentage of the total budget. When you create a chart, you will need to select one set of numbers and one set of labels to identify the numbers. Both the numbers and the labels need to be present in your worksheet, although they do not need to be in adjacent columns or rows. You should select the same number of labels as you have numbers. It’s usually not a good idea to include totals in the graph.

1. Select the Numbers and Labels

a. Go to the “Summary” worksheet and select cells A3 and A4 (the labels).

b. Hold down the CTRL key and select cells E3 and E4 (pressing the CTRL key allows you to select additional cells without de-selecting the cells you selected previously). The numbers in cells E3 and E4 contain the total 2002 budgeted general expenditures and highway expenditures.

c. Click on INSERT, then CHART. This will start the Chart Wizard.

2. Chart Wizard

a. Click the tab that says “Standard Types” in the top left corner to see a list of common chart types. We want to do a pie chart, so select the type of pie chart that appeals to you. Press and hold the button that says “Press and hold to view sample.” This will let you see a preview of your chart. After you choose the type of chart you want, click on the NEXT button to go to the next step.

b. Click on the “Data Range” tab. Excel enters the data range that you selected. Decide whether the “Rows” or “Columns” looks best. In our case, columns look best, so choose that one. Click the NEXT button to go to the next step.

c. On the “Titles” tab, enter the title for your chart. For example, “2002 Budget by Type of Expenditure.” On the “Legend” tab, choose a location for your legend. On the “Data Labels” tab, let’s choose “Show Label and Percent.” This will label each piece of the pie and show us the percentage for each piece. Click on NEXT to move to the next step.

d. Let’s place the chart as a new sheet in our workbook. Choose that item and type a name for the new sheet. Let’s call it “Budget Pie Chart.” Click on FINISH. Excel will now create the chart for you.

3. Customizing Chart Colors

a. If you’d like to change the color scheme, just double-click the part you want to change.

i) You can change colors, patterns, and fonts.

ii) You can do “fill effects” by clicking on the “Patterns” tab, then clicking the “Fill Effects” button.

b. Double-click the background to change the background color.

c. Use your creativity and imagination to create a beautiful chart!

4. It’s Alive!

If any of the numbers in your workbook change, Excel will automatically update the chart to reflect the changes. This will save you the trouble of having to re-create the chart every time your data change.

5. Changing Chart Type

After you create a chart, you might change your mind and decide to switch from a pie chart to a bar or line chart. You don’t need to start over. While the chart sheet is on your screen, click on CHART and choose CHART TYPE. After you click CHART TYPE, a dialog box should appear, allowing you to select another chart type.

6. Changing the Titles

a. If you’d like to change the wording in one or more of your titles, click once on the title. A box will form around the title to indicate that it’s selected. Click once in the middle of the box, and a blinking cursor should appear in the box. Just edit the title as you see fit. Click your mouse on another part of the chart to de-select it.

b. If you’d like to change the size, color, alignment, or appearance of one of your chart titles, point to it and right-click. A menu containing the words “Format Chart Title” or “Format Axis Title” should pop-up. Left-click on the option you want. A “Format” dialog box will appear. Now just change whatever you want.

This is just a basic introduction to charts. Generally, you should experiment with what you can do to a chart by pointing at the part you would like to change and either right-clicking or double-clicking on it. Have fun exploring your options!