Microsoft Office/Create a Personal Budget with functions
Vocabulary
[edit | edit source]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
[edit | edit source]EX2 - Personal Budget
Lesson
[edit | edit source]Double click on Sheet1 Tab and change the name to Portfolio Summary
Type in the following Table into Excel
[edit | edit source]All data was retrieved from: Nasdaq.com/
| 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 | 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
[edit | edit source]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
[edit | edit source]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
[edit | edit source]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
[edit | edit source]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
[edit | edit source]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
[edit | edit source]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
[edit | edit source]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
[edit | edit source]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
[edit | edit 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
[edit | edit source]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
[edit | edit source]| 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. |