Microsoft Office/Create a College Expense Analysis Workbook
Vocabulary
edit#REF! - An error message stating that there is an error with the formula, this can occur if you have a formula and delete one of the cells that the formula referred to.
Format Symbol - Symbols that are entered with numbers such as $, %, or a comma. When you type one of those in with a number Excel will automatically assume the formatting that goes with it.
Freeze the Titles - Taking a row or column and having Excel display it on the screen, no matter how far down or to the right you scroll.
Date Stamp - Shows the date a file was created. This is very important in Business Financial Reports.
NOW Function - This will always display today's date, or the date the computer holds as the system date. The date is actually stored as a number representing a day starting January 1, 1900
Absolute Cell Reference - Keeping a cell constant when copying it. This is done by adding $s. $C$15 is an absolute cell reference and when you copy a formula that contains it, =$C$15 +C16, down a column the $C$15 will stay the same and the C16 will change with the row.
Relative Cell Reference - A cell reference that changes as the formula is copied. In the equation =$C$15 + C16, the C16 is the relative cell reference.
Mixed Cell Reference - You can tell Excel to keep part of the cell reference from changing by only using one $. C$15 and $C15 are both called mixed cell references. In C$15 the row will stay the same but the column letter can change. In $C15 the Column letter will stay the same but the row number can change.
IF Function - A condition that allows you to assign a value to a cell based on a test. =IF(B7>$B$2, $C$2, 0) This means that if the value in cell B7 is greater than the value in Cell B2 then keep the value in cell C2. If it is equal to or less than the value in B2 then keep the value 0.
Logical Test - The test in an IF statement such as B7>$B$2.
Value If True - This is the value that is displayed if the condition tested is true. =IF(B7>$B$2, $C$2, 0) in this example it is the $C$2.
Value If False - This is the value that is displayed if the condition tested is false. =IF(B7>$B$2, $C$2, 0) in this example it is the 0.
Comparison Operators - There are six tests you can perform:
- < is the first one less than the second
- > Is the first one greater than the second
- >= Is the first one greater to or equal to the second
- <= Is the first one less than or equal to the second
- = Are the two values equal to each other
- <> Are the two values NOT equal to each other
Nested IF function - You can place one IF statement inside another IF statement. The second if statement would be in the area for value if true, or value if false.
Chart Sheet - A sheet in Excel that contains only a chart
Pie Chart - A graphical representation of part of a whole. You would use a pie chart only if you want to show how the percentage of something is made up. Each pie wedge shows a percentage of the whole.
Exploded Pie Chart - A pie chart where one or more wedges is pulled out from the rest.
Offsetting - The piece of the pie chart that is pulled out is called offsetting. Sometimes you only want one piece pulled out to show the area you are discussing.
What-If Analysis - This is sensitivity analysis, this just means that Excel will recalculate every formula and redraw every chart for any cell that you change the data in.
Goal Seeking - If you want to find what has to happen to give your desired results in a cell you can use this function to determine the values of a cell on which the formula depends.
Vocabulary Quiz
editEX3 - Expense Analysis
Lesson
editBeginning Data
editEnter the following table into Excel
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Pizza R Us | |||||||
| 2 | Semiannual Projected Gross Margin, Expenses, and Operating Income | |||||||
| 3 | January | Total | ||||||
| 4 | Sales | $2,212,105.98 | $4,845,562.56 | $6,721,100.32 | $3,025,430.35 | $3,001,272.68 | $5,987,384.14 | |
| 5 | Cost of Goods Sold | |||||||
| 6 | Gross Margin | |||||||
| 7 | ||||||||
| 8 | Expenses | |||||||
| 9 | Bonus | |||||||
| 10 | Commission | |||||||
| 11 | Marketing | |||||||
| 12 | Research and Development | |||||||
| 13 | Support, General and Administrative | |||||||
| 14 | Total Expenses | |||||||
| 15 | ||||||||
| 16 | Operating Income | |||||||
| 17 | ||||||||
| 18 | What-if Assumptions | |||||||
Save the Workbook
editClick the Office Button to save the workbook
Click the Office Button - Prepare to add document properties for your name and other relevant information
Pick a Theme for the worksheet
Format by Rotating Text
editRotate the names of the month so that they are at a 45° angle
Click on the Row Heading 3 to select the entire row.
Click on the HOME tab to make it active
Click on Alignment Group Dialog Box Launcher
Change the Orientation to 45°
Click OK
Create a Series with the Fill Handle
editClick on cell B3, the month JANUARY
Click and drag the fill handle to the right, to cell G3
Notice that Excel automatically updates the month to the next on in the series.
Also notice the new options button that appeared - Auto Fill Options Menu
- Copy Cells
- Fill Series
- Fill Formatting Only
- Fill Without Formatting
- Fill Months
Copy and Paste
editIn Excel there are many different ways to copy and paste.
- Copy and Paste button in the HOME tab in the CLIPBOARD group
- Right click and copy and paste
- CTRL+C to copy and CTRL+V to paste
- Another option is to cut and paste if you want to move the data
Use one of these methods to Copy the range A9:A13 to A19:A23.
Just like with the Fill Options Menu, there is a Paste Options menu that offers the same options.
Insert and Delete Cells
editYou can insert a single cell, range of cells, or whole rows and columns. The different methods are:
- Right click on a row header and click INSERT to insert a Row
- Right click on a column header and click INSERT to insert a column
- Click on a row header and click in the HOME tab in tyhe CELLS group on INSERT
- Click on a column header and click in the HONME tab in the CELLS group on INSERT
- You can do the same for just one cell
You can also delete a cell, range of cells, or whole rows and columns. When you delete a cell you are asked if you want to shift cells up, or over.
Insert cell A21 and place the text Margin in the new cell A21.
Insert cell A24 and place the text Revenue for Bonus in the new cell A24.
NOTE: if you delete a cell and it is used in a formula the formula result will show as REF! and you will need to fix the formula.
Type in the What-If Assumptions Data
editB19 = 150,000.00
B20 = 4.5%
B21 = 58%
B22 = 8%
B23 = 6.25%
B24 = 5,250,000.00
B25 = 16.5%
Freeze Cells
editSometimes when you are working with a large spreadsheet it would be nice to see the column and row headings. There is an option in Excel that allows you to Freeze rows or columns on your screen. This will allow you to still see the headings, and scroll to see the rest of the data.
To freeze a cell
- Click in the cell that boarders the areas you want to freeze. In this example that would be cell B4
- Click the VIEW tab
- Click the FREEZE PANES button
- Click Freeze Panes, this will freeze column A and row 1 - 3
Display the System Date
editWhen working with data it is very important to know how recent the data is. For this reason we place the date in our table.
- Go to cell H2
- Click the Insert Function box in the formula bar
- Select Date & Time
- Click on NOW in the Select a Function box.
- Click OK
- Right Click cell H2
- Click on Format Cell
- Click on the Number tab
- Click on DATE
- Click on the format that you like (I will choose 10/27/09)
- Click OK
This date is actually stored as the number of days since December 31, 1899.
Make Reference to Cells both Absolutely and Relatively
editOn the below formulas, make sure you determine what references are absolute and what are relative prior to using the fill handle.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Cell | Row Title | Formula | Comment |
| 2 | B5 | Cots of Goods Sold | =B4*(1-B21) | This is how much it costs for all the items that you sell. |
| 3 | B6 | Gross Margin | =B4-B5 | The profit from the Sales without any additional expenses figured in. |
| 4 | B9 | Bonus | =IF(B4>=B24, B19, 0) | If your sales(B4) are greater than or equal to the Revenue for Bonus (B24) amount then give the Bonus(B19), otherwise give them nothing. |
| 5 | B10 | Commission | =B4*B20 | Figure the percent commission (B20) of the sales (B4). |
| 6 | B11 | Marketing | =B4*B22 | Figure the percent spent on Marketing (B22) of the sales (B4). |
| 7 | B12 | Research and Development | =B4*B23 | Figure the percent spent on Research and Development (B23) of the sales (B4). |
| 8 | B13 | Support, General, and Administrative | =B4*B25 | Figure the percent spent on Support, General and administrative(B25) of the sales (B4). |
| 9 | B24 | Total Expenses | =SUM(B9:B13) | Total of all expenses for January |
| 10 | B16 | Operating Income | =B6-B14 | This is your actual profit after you subtract out all your expenses. |
When you enter in a formula you have three options for how the formula will interact with the cells you have referenced:
- Relative Cell Reference - This is what you have been working with. When you use the fill handle the cell reference changes. If you use the fill handle on a cell with the formula =A3+A5 and drag right the new formula would be =B3+B5. If you use the fill handle and drag down the new formula would be =A4+A6. Depending on what direction you drag would change either the row number or the column letter.
- Absolute Cell Reference - Sometimes you would want the cell in the formula that you referenced to not change when you drag the fill handle. (Like in the formulas above.) If you Take the formula for cell B5, =B4*(1-B21). The B4 is the sales for that month. When you drag right you would want that to change so that each column refers to the month it is in. But the cell B21 does not have anything next to it. So you would want that cell to be absolute. To do this place a $ in front of the B and the 21 so it would be $B$21, or the formula would read =B4*(1-$B$21)
- Mixed Cell Reference - If you do not need to block a cell from changing references in both the column and row direction, like in this case you could just put the $ on the part that you don't want to change. $B21 so the formula would read =B4*(1-$B21)Then the reference would always be to the B column and when you drag right the 21 would not change anyway.
Create Conditional IF Function Statements
editThe format of an IF statement:
= if (B4>=$B$24,$B$19,0)
This is read - If the contents of B4 is greater than or equal to the contents of B24 then use the value in B19, otherwise use 0.
or
IF(logical_test, Value_if_True, Value_if_False)
You can test for:
- = Equal to
- < Less than
- <= Less than or equal to
- > Greater than
- >= Greater than or equal to
- <> Not equal to
Total on Non-adjacent Cells
editChoose the cells in Column H that should have a total. If the cells are not next to one another then use CTRL.
Click on the AutoSum button to get totals.
Use the Format Painter
editThe format painter works the same here as it did in Microsoft Word. Click on a cell with the formatting you want to copy, then click on the format painter (paintbrush tool) then click on the cell you want formatted the same.
Create a Pie Chart
editSelect B3:G3 and B16:G16
Insert Tab
Pie Chart - 3-D Pie
Click on the Move Chart button in the Chart Tools - Design Tab
Click on the NEW SHEET radio button
Type "Semiannual Financial Projector"
Click OK
Notice the new chart tab at the bottom of the window.
Add a chart title - "Semiannual Financial Projector"
Underline it
Turn the Legend off - From the LAYOUT tab click on LEGEND and click on NONE
Add data labels - From the LAYOUT tab click on Data Labels - Check Category Name and Percentage
Rotate the Chart - From the LAYOUT tab click the 3-D rotate button - Choose the degrees
Change the Format of the Chart - Right click on the pie chart and go to Format Data Series - Explore and make sure you visit the 3-D Format to add contour, surface or material.
Try to change the colors of the pie wedges manually from the Format Data Series window.
Rename and Change Color of Sheet Tabs
editRight Click on the sheet tab and click on RENAME to change the name from SHEET 1 to "SemiAnnual Financial Projection"
Right Click on the sheet tab and click on TAB COLOR to change the color of the tab, change active tabs to see the color change.
Click and drag a sheet tab to the right or left to change the order they are listed at the bottom.
Previewing
editALWAYS PRINT PREVIEW BEFORE YOU PRINT!!!
Office button - print - print preview
I use the Page Setup button to format and then I print from the preview to ensure I will get what I want.
From the PAGE tab you can change from landscape to portrait, and fit the document to one page.
From the MARGINS tab you can change the margins of the page and center horizontally and vertically.
From the HEADER/FOOTER tab you can add either a header or a footer to the page.
From the SHEET tab you can set a print area, set the row or columns that will print at the top or side of every sheet, turn on gridlines for printing, print only in black and white, or print in a draft quality that takes less in.
Printing a WorkBook
editIf you have a multiple sheet workbook, you can print the entire workbook at one time. Choose all the sheet tabs that contain data or charts, then print preview and print as normal. In the print preview you will need to use the scroll bar to see the other pages.
Using What-If Analysis
editSince all of your data is linked to the What-If Assumptions section you can change your assumptions and watch the changes through out the entire workbook, chart and all.
Zoom your worksheet so you can see the entire data table.
Enter 72,000 in cell B19 - What does this change, and why? What does it mean?
Enter 3.0 in cell B20 - What does this change, and why? What does it mean?
Enter 15.25 in cell B25 - What does this change, and why? What does it mean?
What happens in cell H16 and why?
Goal Seeking
editThis is very useful if you know the results you want but are unsure how to get there. This feature of Excel allows you to put in the end result and it will help calculate the dependent variables.
click on the Virtical Split box and split the screen after column E.
Adjust the right side to show column H
Click cell H16
Click the DATA Tab
Click the What-If Analysis button
Click Goal Seek
Fill in the box as follows:
- Set Cell - H16
- To Value - 11,000,000
- By Changing Cell - $B$25
Click OK
What happened?
Play with the values
When you are done, click Cancel
Project
editCollege Expense Projections Attending college in today's economy is tough. Planning ahead is the key to relieve the stress of finding the finances needed for this large expense. Create the following table:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | University of Northern Colorado | |||||
| 2 | Expense Projections | |||||
| 3 | Expenses | Freshman | Sophomore | Junior | Senior | Total |
| 4 | Tuition | Formula #1 | Formula #1 | Formula #1 | ||
| 5 | General Fees | Formula #1 | Formula #1 | Formula #1 | ||
| 6 | Medical | Formula #1 | Formula #1 | Formula #1 | ||
| 7 | Book Allowance | Formula #1 | Formula #1 | Formula #1 | ||
| 8 | Room/Board | Formula #1 | Formula #1 | Formula #1 | ||
| 9 | Personal/Misc | Formula #1 | Formula #1 | Formula #1 | ||
| 10 | Transportation | Formula #1 | Formula #1 | Formula #1 | ||
| 11 | Total Expenses | |||||
| 12 | ||||||
| 13 | Resources | Freshman | Sophomore | Junior | Senior | Total |
| 14 | Savings | Formula #2 | Formula #2 | Formula #2 | Formula #2 | |
| 15 | Parents | Formula #2 | Formula #2 | Formula #2 | Formula #2 | |
| 16 | Job | Formula #2 | Formula #2 | Formula #2 | Formula #2 | |
| 17 | Loans | Formula #2 | Formula #2 | Formula #2 | Formula #2 | |
| 18 | Scholarships | Formula #2 | Formula #2 | Formula #2 | Formula #2 | |
| 19 | Total Resources | |||||
| 20 | ||||||
| 21 | Assumptions | |||||
| 22 | Savings | |||||
| 23 | Parents | |||||
| 24 | Job | |||||
| 25 | Loans | |||||
| 26 | Scholarships | |||||
| 27 | Annual Rate Increase | 5.3% | ||||
Formula #1 = Prior Year's Expense * (1+Annual Rate Increase)
Formula #2 = Total Expenses for Year * Corresponding Assumption
The Assumptions are your best guess on what percent you will need from each category to pay for college
Create a Pie chart of total expenses.
Create a Pie chart of total resources.
EXTRA CREDIT - Use an if statement for notifying you if the total savings is higher than you know you have saved.
Rubric
edit| Standards | 1 point | 2 points | 3 points | 4 points | 5 points |
|---|---|---|---|---|---|
| Accurately creates the College Expense Table | Missing 4 item | Missing 3 item | Missing 2 item | Missing 1 item | 100% accurate - nothing missing |
| Fills in Assumptions with logical Guesses | Missing 4 item | Missing 3 item | Missing 2 item | Missing 1 item | 100% accurate - nothing missing |
| Uses Formula #1 accurately | 4 mistakes | 3 mistakes | 2 mistakes | 1 mistake | No mistakes |
| Uses Formula #2 accurately | 4 mistakes | 3 mistakes | 2 mistakes | 1 mistake | No mistakes |
| Fills in the totals accurately | 4 mistakes | 3 mistakes | 2 mistakes | 1 mistake | No mistakes |
| Creates a Pie Chart of total Expenses | 4 mistakes | 3 mistakes | 2 mistakes | 1 mistake | No mistakes - Includes data labels, and title |
| Creates a Pie Chart of Total Resources | 4 mistakes | 3 mistakes | 2 mistakes | 1 mistake | No mistakes - Includes data labels, and title |