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

http://www.yacapaca.com

EX3 - Expense Analysis

Lesson

Beginning Data

Enter the following table into Excel

Semiannual Projections

	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

Click 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

Rotate 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

Click 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

In 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

You 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

B19 = 150,000.00

B20 = 4.5%

B21 = 58%

B22 = 8%

B23 = 6.25%

B24 = 5,250,000.00

B25 = 16.5%

Freeze Cells

Sometimes 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

When 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 asthe number of days since December 31, 1899.

Make Reference to Cells both Absolutely and Relatively

On the below formulas, make sure you determine what references are absolute and what are relative prior to using the fill handle.

College Expense Projections

	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

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

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