Microsoft Office/Create a Personal Budget with functions

Vocabulary

Function - pre-written formula built into Excel that takes different values and performs an operation: SUM, AVERAGE, MAX, MIN

Order Of Operations - PMDAS (Please Excuse My Dear Aunt Sally)

P - parentheses
E - exponents
M & D - Multiplication and Division taken left to right
A & S - Addition and Subtraction taken left to right

Point Mode - selecting cells for a formula by clicking on them with the mouse

Smart Tags - Dates, financial symbols, people's names, indicated by a small purple triangle in the cell. Clicking on this purple triangle gives a list of actions you can perform for that cell

Smart Tag Indicator - the small purple triangle itself is the indicator that a smart tag is available

Arguments - The values that you plug into a function are called the arguments. In a SUM function it would be the list of cells you want to add together: =SUM(C2:C15) This means to add the values in the cells from C2 down to C15.

Average - Sums the numbers in a range and then divides by the number of cells in that range.

Max - A function that will display the highest value in a range of cells

Min - A function that will display the lowest value in a range of cells

Range Finder - A helping function in Excel that checks which cells are referenced in a formula. Very convenient if you have accidentally clicked on the wrong row or column for one of the values.

Theme - a set of predefined colors, fonts, chart styles, cell styles,a nd fill effects in an Excel Workbook.

Accounting Number Format - displays the numbers as a dollar value with the dollar sign to the left of the numbers, a comma every three digits and rounded to the nearest hundredth.

fixed dollar sign - the dollar sign in a cell is to the far left

floating dollar sign - the dollar sign is just to the left of the numbers

Comma Style Format - uses commas but no dollar signs in the numbers

Conditional Formatting - Formatting that changes depending on the value in the cell, or what condition the value meets. You could change the color of numbers to blue for any number over 1,000,000 for example.

Condition - A relation between at least two values. If a number is less than 7 then ..., this is a condition.

Pixel - The smallest element of a graphic. It represents on dot of color on a screen. The size of the dot is based on your screen resolution. A resolution of 1024X768 means that you have 1024 pixels across and 768 pixels down the screen.

Spell Checker - An operation in Excel that looks for possible spelling errors in a worksheet, comparing the words against a standard dictionary of words.

Page Layout - a view of the worksheet that lets you edit the worksheet while seeing how it will look in printed form.

Normal View - the default view of Excel allowing you to adjust the margins, header or footer.

Previewing the Worksheet - When you print an Excel Worksheet you should ALWAYS print preview before printing. This allows you to check to see what is being cut off and placed on a separate page. It also lets you see if you have a stray character that will cause you to print 100 pages instead of just 1.

Values Version - this is the default view and lets you see the results of the formulas and functions in each cell.

Formulas Version - this lets you see the functions and formulas in each cell instead of the value from the computation.

Debugging - the process of checking your formulas or functions to find errors.

In-cell editing - Double clicking on the cell and making the changes in the cell itself.

Best fit - Double clicking between two rows or columns and letting Excel choose the best width or height depending on the largest contents of a cell in that row or column.

Vocabulary Quiz

http://www.yacapaca.com

EX2 - Personal Budget

Lesson

Double click on Sheet1 Tab and change the name to Portfolio Summary

Type in the following Table into Excel

All data was retrieved from: Nasdaq.com/

Stock Analysis as of 10/20/09
	A	B	C	D	E	F	G	H	I	J
1	Personal Stock Anaylsis
2	Portfolio Summary
3	Stock	Symbol	Date Acquired	Shares	Initial $/Share	Initial Cost	Current Price/Share	Current Value	Gain/Loss	Percent Gain/Loss
4	Apple Inc	aapl	10/22/07	400	$174.36		$198.76
5	Hasbro Inc	has	11/04/05	1515	19.21		28.11
6	Texas Instruments Inc	txn	03/29/00	400	85.25		23.66
7	Coca-Cola Co	ko	10/01/03	600	43.89		54.07
8	Yahoo! Inc	yhoo	12/09/03	200	20.785		17.17
9	Google	goog	11/10/05	540	391.10		551.72
10	eBay Inc	ebay	07/03/03	1150	27.5325		25.06
11	Sprint Nextel	s	02/14/05	652	21.075824		3.29
12	McDonald's Corp	mcd	10/27/99	700	39.50		58.92
13	Totals
14	Average
15	Highest
16	Lowest

Fill in the cells with formulas

Fill in the empty columns

Initial cost = (shares) * (Initial price per share)
- Go to cell F4
- Type and click on: =D4*E4
- Use the fill handle to fill down the column
Current value = (Shares) * (Current Price/Share)
- Go to cell H4
- Type and click on: =D4*G4
- Use the fill handle to fill down the column
Gain/Loss = (Current Value) - (Initial Cost)
- Go to cell I4
- Type and click on: =H4-F4
- Use the fill handle to fill down the column
Percent Gain/Loss = (Gain/Loss) / (Initial Cost)
- Go to cell J4
- Type and click on: =I4/F4
- Use the fill handle to fill down the column

Fill Cells in with Functions

Fill in the rows 13 - 16

Total =SUM(D4:D12)
- Go to cell D13
- Click on the Sigma (AutoSum)
- Check that the correct range is chosen (D4:D12) and press enter
- Use the fill handle to fill across the row
Average =AVERAGE(D4:D12)
- Go to cell D14
- Click on the drop down menu with the Sigma (AutoSum)and choose AVERAGE
- Check that the correct range is chosen (D4:D12) and press enter
- Use the fill handle to fill across the row
Highest = MAX(D4:D12)
- Go to cell D15
- Click on the drop down menu with the Sigma (AutoSum)and choose MAX
- Check that the correct range is chosen (D4:D12) and press enter
- Use the fill handle to fill across the row
Lowest = MIN(D4:D12)
- Go to cell D16
- Click on the drop down menu with the Sigma (AutoSum)and choose MIN
- Check that the correct range is chosen (D4:D12) and press enter
- Use the fill handle to fill across the row

Verify a formula with the range finder

Once you have your formulas into excel you might need to go back and verify that you have entered them correctly. If you single click on a cell that has a formula then you will see the formula in the formula bar and the value from the formula in the cell.

If you double click on the cell with a formula the formula will show up in the cell and the RANGE FINDER feature will be turned on. This will outline the different cells that are used in the formula to make it easier to verify that you have entered the formula correctly.

This is very handy in the case where you accidentally click on a cell in a row lower than you meant to.

Apply a Theme to a workbook

So far the spreadsheet is considered to be mostly unformatted. An easy way to format a worksheet is to apply a THEME. You can change the theme of a worksheet from the Page Layout tab. In the far left there is a themes button. Play with the themes for the worksheet until you find one that you like.

Format the headings using the Cell Styles button from the HOME tab. The options here are dependent on the THEME you chose earlier. You should format each heading (row A and B) separately to give the main heading more emphasis than the sub heading.

Change the background color by selecting the cells you want to change and then clicking on the Fill color button from the HOME tab (it looks like a paint bucket).

Add a border around the headings from the BORDERS button in the HOME tab. Chose both headings at one time, then click on the Thick Box Border.

Put a bottom border under row 3 using the border button.

Put a total Style on the total row.

Bold the row headings: Totals, Average, Highest, Lowest

Number Styles

Accounting Number Format - When working with number such as stocks it is good to use Accounting Number Format. This will place the dollar sign to the left of the number (as a fixed dollar sign). It will also insert commas every third position to the left of the decimal, and put the decimals to two places (hundredths). A negative number will be displayed in parenthesis.

Comma Style Format - This will place a comma every third position to the left of the decimal.

Percents - Will move the decimal two places and place a percent sign at the end.

If you want to change the number of decimals then click on the increase or decrease decimal buttons in the NUMBER group.

Apply conditional formatting

Conditional formatting lets you change how a cell is formatted based on the values in the cell. In this case we will use the example of changing negative percent gain/loss to red so we can see them more easily.

Click and drag over the range J4:J12
From the HOME tab click on CONDITIONAL FORMATTING
Click on NEW RULE
Click on FORMAT ONLY CELLS THAT CONTAIN
In the edit rule section at the bottom set FORMAT ONLY CELLS WITH: to Cell Value
change the next drop down to LESS THAN
type in the value 0 in the last field.
Click on the FORMAT button
Go to the FILL tab
Click on a color for a background color
Go to the FONT tab
Change the color of the font so it will show up with your background color
Click OK

Page Layout View

The current view you are in is NORMAL VIEW. Change to page layout view by clicking the page layout button in the lower right of the window. Notice what is going to be cut off when you print and placed on a separate page.

I usually do my page layout from the print preview screen.

Go to the Office Button
Click on PRINT
Click on PRINT PREVIEW
Click on Page Setup
Click on Landscape because this table is wider than it is tall
Click on Fit to -1- page wide by -1- page tall
Click on OK

I then usually print from the Print Preview screen because it is a WYSIWYG (What you see is what you get).

NOTE - If you go to the SHEET tab from the Page Set up dialog box you can turn on grid lines to make your spreadsheet easier to read.

Rename a sheet tab

Go to Sheet 2

Double click on the sheet tab and rename it to real-time Stock Quote

Make cell A1 the active cell

Import stocks from a Web Source

Go to the DATA Tab

Click on Existing Connections in the Get External Data group

Click on MSN MoneyCentral Investor Stock Quotes

Click on Existing Worksheet

Click on OK

check the box for Use this value/reference for future refreshes

click OK

be patient as the spread sheet is created for you with all the current values of your stock.

Project

Create a personal budget for the next year. Pick one of the occupations that you have been looking at, or if you have a job currently use that.

Include rows for:

Income
auto gas
groceries
Eating out
going to games
going to movies
movie rental
music downloading
other expenses
totals

Include columns for each month in the year.

Fill in the data as best you can, know that you do more of certain things in the winter versus the summer and certain things in the summer more than the winter.

Add columns at the right for Total, Average, Highest, Lowest

For the Total at the bottom be aware that income is money coming in and all the rest is money going out - hint hint hint

Format the spreadsheet

Create a Pie Chart to show the expenses you incur FOR THE WHOLE YEAR only.

Use Microsoft HELP to add a title to the chart.

Rubric

Personal Budget Rubric
Standards	1 point	2 points	3 points	4 points	5 points
Chooses a Career and find salary per month	Chooses a career - can't find salary	Chooses a career with a salary	Chooses a career with an annual salary	Chooses a career with an annual salary and figures salary per month	Chooses a career with an annual salary and correctly figures salary per month
Fills in budget items for each month	Misses 4	Misses 3	Misses 2	Misses 1	Includes information for all budget items lists in directions
Includes total money at the end of each month	Inclues column	Includes column with values	Includes column and gets formula wrong	Includes column and gets formula mostly correct	Includes column and computes formula correctly
Includes row for total at the bottom	Creates row for total	Creates row for total and uses values	Creates row for total and uses function	Creates row for total and uses function correctly	Creates row for total and uses function correctly for all applicable columns
Includes row for average at the bottom	Creates row for total	Creates row for total and uses values	Creates row for total and uses function	Creates row for total and uses function correctly	Creates row for average and uses function correctly for all applicable columns
Includes row for Highest at the bottom	Creates row for total	Creates row for total and uses values	Creates row for total and uses function	Creates row for total and uses function correctly	Creates row for total and uses function correctly for all applicable columns
Includes row for Lowest at the bottom	Creates row for total	Creates row for total and uses values	Creates row for total and uses function	Creates row for total and uses function correctly	Creates row for total and uses function correctly for all applicable columns
Includes a conditional formatting on the total for each month	Does not understand conditional formatting	Attempts to use conditional formatting	Uses conditional formatting	Uses conditional formatting on the total for each month to change formatting	Uses conditional formatting on the total for each month to make negative numbers a different format.
Formats the spreadsheet to look professional	Missing 4 or more items	Missing 3 items	Missing 2 items	Missing 1 item	Follows standard layout and formatting for an accounting style spreadsheet: titles, borders, fonts, number styles
Creates a Pie chart to represent expense for the year	Pie chart created for something	Pie chart created for the data	Pie chart created for the expenses	Pie chart created for the entire year for expense	Pie chart created for just the total expense for the entire year by expense.
Prints 2 copies of spreadsheet correctly to one page each	Missing one view	Prints more than one page for both views	Prints more than one page for a view	Prints one page for each view	Uses Print Preview and page set up to format the document to fit on one page each. Prints the formula view and value view.