# Microsoft Office/Create a College Expense Analysis Workbook

## Vocabulary

#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 - 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 The 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 Choose 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 The 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 Select 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 Right 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 ALWAYS 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 If 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 Since 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 This 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

College 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:

College Expense Projections
A B C D E F
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

College Expense Rubric
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