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/

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 <li>Type and click on: =D4*G4 <li>Use the fill handle to fill down the column </ul> <li>Gain/Loss = (Current Value) - (Initial Cost) <ul> <li>Go to cell I4 <li>Type and click on: =H4-F4 <li>Use the fill handle to fill down the column </ul> <li>Percent Gain/Loss = (Gain/Loss) / (Initial Cost) <ul> <li>Go to cell J4 <li>Type and click on: =I4/F4 <li>Use the fill handle to fill down the column </ul> </ul>

Fill Cells in with Functions
Fill in the rows 13 - 16 <ul> <li>Total =SUM(D4:D12) <ul> <li>Go to cell D13 <li>Click on the Sigma (AutoSum) <li>Check that the correct range is chosen (D4:D12) and press enter <li>Use the fill handle to fill across the row </ul> <li>Average =AVERAGE(D4:D12) <ul> <li>Go to cell D14 <li>Click on the drop down menu with the Sigma (AutoSum)and choose AVERAGE <li>Check that the correct range is chosen (D4:D12) and press enter <li>Use the fill handle to fill across the row </ul> <li>Highest = MAX(D4:D12) <ul> <li>Go to cell D15 <li>Click on the drop down menu with the Sigma (AutoSum)and choose MAX <li>Check that the correct range is chosen (D4:D12) and press enter <li>Use the fill handle to fill across the row </ul> <li>Lowest = MIN(D4:D12) <ul> <li>Go to cell D16 <li>Click on the drop down menu with the Sigma (AutoSum)and choose MIN <li>Check that the correct range is chosen (D4:D12) and press enter <li>Use the fill handle to fill across the row </ul> </ul>

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

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. <ul> <li>Go to the Office Button <li>Click on PRINT <li>Click on PRINT PREVIEW <li>Click on Page Setup <li>Click on Landscape because this table is wider than it is tall <li>Click on Fit to -1- page wide by -1- page tall <li>Click on OK </ul>

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: <ul> <li>Income <li>auto gas <li>groceries <li>Eating out <li>going to games <li>going to movies <li>movie rental <li>music downloading <li>other expenses <li>totals </ul>

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.