Microsoft Office/Create a College Expense Analysis Workbook

VocabularyEdit

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

http://www.yacapaca.com

EX3 - Expense Analysis

LessonEdit

Beginning DataEdit

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 WorkbookEdit

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 TextEdit

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

Format Cells Orientation.jpg

Create a Series with the Fill HandleEdit

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 PasteEdit

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 CellsEdit

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 DataEdit

B19 = 150,000.00

B20 = 4.5%

B21 = 58%

B22 = 8%

B23 = 6.25%

B24 = 5,250,000.00

B25 = 16.5%

Freeze CellsEdit

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 DateEdit

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 RelativelyEdit

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

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 CellsEdit

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 PainterEdit

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 ChartEdit

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 TabsEdit

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.

PreviewingEdit

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 WorkBookEdit

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 AnalysisEdit

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 SeekingEdit

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.

Virtical and Horzontal Split box.jpg

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

ProjectEdit

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.

RubricEdit

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
Last modified on 27 September 2010, at 20:47