Preparing a Budget (Lesson)
- "in class" work to prepare for doing a budget as an Assignment

Set up the Worksheet (page 138)
Open a new Excel worksheet file
To set up the page, click file, page setup and portrait (if not already selected)
Click OK
Click in Cell A1, hold the shift key and click in cell I 1
Release the shift key
Click on the Merge and Centre Icon (page 161)

Type the title, Monthly Budget and tap “enter”
Bold and underline the title

Resize Row Height and Column Width for Item Description and Amounts (page 126)
Click A2 and hold the shift key then click in cell D2 and release the shift key
Click on the Merge and Center icon
Right click in the merged cell (A2 to D2) and a menu will appear click on Copy
Click in Cell A3 Hold the shift key and click in Cell D17 – release the shift key
Tap Enter key
Label this (in Cell A2) Item

Click E2 and hold the shift key then click in cell F2 and release the shift key
Click on the Merge and Center Icon
Right click in the merged cell (E2 to F2) and a menu will appear – click on copy
Click in Cell E3 Hold the shift key and click in Cell F17 – release the shift key
Tap Enter key
Label this (in Cell E2) Amount

Click G2 and hold the shift key then click in cell I-2 and release the shift key
Click on the Merge and Center Icon
Right click in the merged cell (G2 to I-2) and a menu will appear – click on copy
Click in Cell G3 Hold the shift key and click in Cell I-17 – release the shift key
Tap Enter key
Label this (in Cell G2) Comments
 

Resizing row height
Click on the 2 that identifies Row 2
Hold the shift key
Click on Row 17
Release the shift key
Go to tool bar and click on format
Click on Row Click on Height and a window will appear
In number field (box) type the number 22 and click OK

Format Cells for Currency Amounts (page 186)
Click in Cell E3
Hold the shift key
Click in Cell E17
Release the shift key
Right click in the highlighted area and a menu will appear
Click on Format Cells
Click on the Number tab
Click on Currency
You will see this:

Look to the right to see if decimals are set at two and the dollar sign is set
(If not, click on the down arrows to set these items)
Click OK
Click on the right justify icon on the tool bar

Formatting Items Column
Click in Cell A3
Hold the shift key and click in A17
Release the shift key
Click the left justify icon from the tool bar

Adjusting automatic curser movement (page 127)
Click in Cell A3
Click Tools Click Options
Click on the Edit Tab
You will see this:

Near the middle left you will see a box beside “move selection after enter”
If there is not a check mark in this box, click on it to show the check mark
Click on the down arrow beside it and click on “down”
Click OK

Enter Budget Items
Click in Cell A4 and type Rent – tap enter
In Cell A5, type Utilities – tap enter
In Cell A6 type Transportation - tap enter
In Cell A7 type Food – tap enter
In Cell A8 type Medical Expenses – tap enter
In Cell A9 type Clothing – tap enter
In Cell A10 type Leisure Activities – tap enter
In Cell A11 type Miscellaneous – tap enter
In Cell A12 Type TOTAL – tap enter
Tap Enter to be in Cell A14
In Cell A14 type INCOME
In Cell A15 type EXPENSES
In Cell A16 type SAVINGS
 

Bolding multiple items (page 3)
Click in Cell A12
Hold down the Control key (Ctrl)
Click in Cell A14
Click in Cell A15
Click in Cell A16
Release the Control key
Click on the Bold icon on the toolbar
(All items selected will now be bold)

Enter Amount Items
Click in Cell E4
Type 250 - tap Enter
In Cell E5 type 50 – tap enter
In Cell E6 type 220 – tap enter
In Cell E7 type 200 – tap enter
In Cell E8 type 35 – tap enter
In Cell E9 type 75 – tap enter
In Cell E10 type 80 – tap enter
In Cell E11 type 50 – while still in this cell click on the underline icon

Enter Comments
Click in Cell G3
Hold the shift key
Click in Cell G17
Release the shift key
Click the left justify icon from the tool bar
Tap enter
Click in Cell G5 type Phone and Electric – tap enter
In Cell G6, type Car payment, insurance, gas – tap enter
Tap enter again
In Cell G8 type Insurance contribution – tap enter
Tap enter again
In Cell G10 type movies, dining out, etc.

Create Total of Expenses Using AutoSum (page 157)
Click in Cell E12
Click on the AutoSum icon on the toolbar
The numbers in this column are now outlined – Excel is asking if these are the numbers you want added – they are
Tap enter and the total amount will appear in Cell E12
 

Enter Income, Expenses and Savings
Click in Cell E14 to enter income figure – in this example, $1,225.00
Type 1225 – tap enter
In Cell E15 – tap the equal (=) sign
Click on Cell E12 – tap enter
The expense total will now appear in the summary section of the sheet

Create Subtraction Formula (page 150)
Click in Cell E16 – tap the equal sign
Click in Cell E14 - tap the minus (-) sign on the top right of number pad
Click in Cell E15 – tap enter
The Difference will appear in the Savings cell E16

Bar Chart to Compare Income and Expenses
Using the calculations you have just compiled, click on the data (information) on the spreadsheet (click anywhere in the amount column)

Then Click on chart wizard icon (on the toolbar near the middle) (page 163)

Select bar chart and click Next

You will see the chart previewed and the data in the box where it says data range
In the data range box, change $A$4 to $F$11 (to capture the figures you want on the chart)  -- This will show the amount of each budget item.

Click the columns button if it is not already selected and click Next

Enter an appropriate title for your chart

Enter the Y-Axis title of “Dollars”

Click Next

Choose location of chart on spreadsheet (what sheet)

Click on Finish --- move the chart to a blank area of the worksheet if you placed the chart in the same worksheet as the data.

If the X-axis only shows some of your budget items, then right click on the axis (ie. right-click on any of the budget item names on the chart) and select Format Axis.  Click on the Scale tab and change the Number of Categories between Tick-mark Labels to “1” (middle box).  Then Click OK.

Default placement of the Legend is to the right – you can change this by right-clicking on the Legend and selecting “Format Legend” from the pop-up menu – then click on the “Placement” tab.  For this chart, the legend is not needed, so delete the legend.

Right click on chart area and select Format Chart Area
To select colour of background of chart, on the right side under Area click on the Automatic button, then click the colour that you want for the background

Under border heading on the left side, choose the colour and thickness of the border of the chart line with style colour and weight selections

 

Click OK when you have finished formatting the chart area.

For the lines (bars) in the graph showing the data, double click on a line to get the options to format these lines as to thickness, style and colour. (Format data series)

Solution will look like this

Learning Outcomes for Project 2: Budget

How to use Page Setup (page 138)
How to Merge and Centre (page 161)
How to How to resize row height and column width (page 126)
How to format cells for currency (page 186)
How to adjust automatic cursor movement (page 127)
How to bold multiple items (page 3)
How to use AutoSum (page 157)
How to create a subtraction formula (page 150)
How to create a bar chart using the chart wizard (page 163 to 171)
How to select and edit different areas of a bar chart (e.g. lines, background, borders etc.) (page 163 to 171)
 
 
 
 

witiger.com	CONTACT I MAIN PAGE I NEWS GALLERY I E-BIZ SHORTCUTS I INT'L BIZ SHORTCUTS I MKTG&BUSINESS SHORTCUTS I TEACHING SCHEDULE
	.
	MISTAKES ITEXTS USED I IMAGES I RANK IDISCLAIMER I STUDENT CONTRIBUTORS I FORMER STUDENTS I PUBLICATIONS I
	.

.

Prof. W. Tim G. Richardson © www.witiger.com