Excel

Formulas & Formula Inputs

Functions
Formula Elements
Excel Math Symbols
Order of Precedents
Exponents
Number 1
Number Formatting
Data in Excel
Percent & Percent Change
Cell References
Stylistic Formatting
 Add
4 5 3 Calculation Alt + =
What is Excel? ==> Calculations Min
Data Analysis 0.15 0.12 0.09 Calculation & Data Analysis
Columns Store raw data Minus
Rows 100 75
Cells
Range of cells
Worksheet
Sheet Tab
Workbook
Ctrl + Page Up &
Ctrl + Page Down
Ribbons
QAT
Scroll Bars
Formula & Functions
Formula Bar
Name Box
& Data Analysis
 finance

Add
4 5 3 12 Calculation Alt + =
What is Excel? ==> Calculations Min
Data Analysis 0.15 0.12 0.09 0.09 Calculation & Data Analysis
Columns Store raw data Minus
Rows 100 75 25
Cells
Range of cells
Worksheet
Sheet Tab
Workbook
Ctrl + Page Up &
Ctrl + Page Down
Ribbons
QAT
Scroll Bars
Formula & Functions
Formula Bar
Name Box
& Data Analysis
Loan 50000 Total Revenue 65000
Annual Rate 0.0795 Total Expenses 53210
Periods per Year 2 Net Income
Period Rate
Never: Effective Rate

Equal sign starts all formulas

Formula inputs are items the formula uses like, annual rate,
periods per year, revenue, expenses
If a formula input can change: 1) Put it in a cell, 2) Label the cell,
3) refer to the formula input with a cell reference.
If a formula input can change: DO NOT TYPE THE NUMBER INTO
THE FORMULA.
Entering cell references into formulas: 1) If the cell references
are close to the formula, use the arrow keys, 2) If the cell
references are NOT close to the formula, use the mouse
In this class, I will color all formula cells green and most labels
will be dark blue
Loan 50000 Total Revenue 65000
Annual Rate 0.0795 Total Expenses 53210
Periods per Year 2 Net Income 11790
Period Rate 0.03975
Never: 0.03875 Effective Rate
0.0810800625

Equal sign starts all formulas

Formula inputs are items the formula uses like, annual rate,
periods per year, revenue, expenses
If a formula input can change: 1) Put it in a cell, 2) Label the cell,
3) refer to the formula input with a cell reference.
If a formula input can change: DO NOT TYPE THE NUMBER INTO
THE FORMULA.
Entering cell references into formulas: 1) If the cell references
are close to the formula, use the arrow keys, 2) If the cell
references are NOT close to the formula, use the mouse
In this class, I will color all formula cells green and most labels
will be dark blue
 A Function is built-in code that makes a certain calculation or does a certain
task. For example, the SUM function will add values from a range of cells, the
AVERAGE will calculate the average (arithmetic mean), and the PMT function
can calculate a loan payment
If you have a choice between a formula like =SUM(B2:B5) and =B2+B3+B4+B5,
choose =SUM(B2:B5) because it is faster to create and it can handle structural
updates like inserting a row.
Screen Tips help greatly with entering function arguments.

You can search for functions if you click the fx button on the formula bar.

Cash Flows Cash Flows

-1000 -1000
500 500
500 500
500 500
Total Cash Flow
Over 3 years Never:
Keyboard for SUM: Alt + =

Returns
-0.015
-0.225 Loan 50000
0.025 Annual Rate 0.0875
0.0365 Years 10
Average (Mean) Yearly Payment Ctrl + 1 opens Format Cells Dialog box

Shift + F3 opens Insert Function dialog box

 A Function is built-in code that makes a certain calculation or does a certain
task. For example, the SUM function will add values from a range of cells, the
AVERAGE will calculate the average (arithmetic mean), and the PMT function
can calculate a loan payment
If you have a choice between a formula like =SUM(B2:B5) and =B2+B3+B4+B5,
choose =SUM(B2:B5) because it is faster to create and it can handle structural
updates like inserting a row.
Screen Tips help greatly with entering function arguments.
You can search for functions if you click the fx button on the formula bar.

Cash Flows Cash Flows

-1000 -1000
500 500
400 400
500 500
500 500
Total Cash Flow
Over 3 years 900 Never: 500
Keyboard for SUM: Alt + =

Returns
-0.015
-0.225 Loan 50000
0.025 Annual Rate 0.0875
0.0365 Years 10
Average (Mean) -0.044625 Yearly Payment -$7,705.48 Ctrl + 1 opens Format Cells Dialog box
($7,705.48)
Shift + F3 opens Insert Function dialog box
1) Equal sign (starts all formulas).
2) Cell references (also: Defined Names, sheet references, workbook references).
3) Math operators (plus, subtract, multiply, etc.).
4) Built-in Functions (AVERAGE, SUM, PMT, etc.)
5) Numbers (If the number will not change, like 12 months, 24 hours).
6) Comparative operators (=, >, >=, <, <=, <>)
7) The join symbol, ampersand, “&” (Shift + 7)
8) Text that is in quotes (example: “For The Month Ended”)
9) Arrays constant (example: {1,2,3})
 You must learn Excel's Operation Symbols
Parenthesis () Shift + 9 and Shift + 0 Add Subtract Divide Divide
Exponents ^ Shift + 6 5 2 5 2
Multiply * Number pad 6 5 2 5
Divide / Number pad
Add + Number pad
Subtract - Number pad Alt + =
Equal = Left of Backspace
Greater than > Shift + .
Greater than or equal to >= 2 characters next to each other
Less than < Shift + ,
Less than or equal to <= 2 characters next to each other
Not <> 2 characters next to each other
Join & Shift + 7
 These are called Logical Formulas (can come out to be TRUE or FALSE
Multiply Exponent Equal? Greater than?
6 6 10.01 12
6 2 10.00 11

because
of
decimals
not
showing
 You must learn Excel's Operation Symbols
Parenthesis () Shift + 9 and Shift + 0 Add Subtract Divide Divide
Exponents ^ Shift + 6 5 2 5 2
Multiply * Number pad 6 5 2 5
Divide / Number pad 11 -3 2.5 0.4
Add + Number pad
Subtract - Number pad Alt + =
Equal = Left of Backspace
Greater than > Shift + .
Greater than or equal to >= 2 characters next to each other
Less than < Shift + ,
Less than or equal to <= 2 characters next to each other
Not <> 2 characters next to each other
Join & Shift + 7
 These are called Logical Formulas (can come out to be TRUE or FALSE
Multiply Exponent Equal? Greater than?
6 6 10.01 12
6 2 10.00 11
36 36 0 1
36

because
of
decimals
not
showing
 Order of Operations
1 Please Parenthesis ()
2 Excuse Exponents ^
3 My Dear Multiply & Divide (Left to Right) *,/
4 Aunt Sally Adding & Suntracting (Left To Right) + , -

2+2*3^2 = 144? 2+2*3^2 = 20?

Excel's Order of Operations:

Parenthesis ( )
Ranges use of colon symbol ":"
Example: =SUM(A1:A4)
Evaluate intersections with spaces
Example: =E12:G12 F10:F15 (retrieve what is in F12)
Evaluate unions (,)
Example: =SUM(E10:G10,E14:G14)
Negation (-)
Example: =-2^4 è 16
Example: =-(2^4) è -16
Converts % (1% è .01)
Exponents (^)
Example: 4^(1/2) = 2
Example: 3^2 = 9
Multiplication (*) and division (/), left to right
Adding (+) and subtracting (-), left to right
Ampersand (&)
Comparative symbols: =, <>, >=, <=, <, >
If anything is still left, then left to right
 Order of Operations
1 Please Parenthesis ()
2 Excuse Exponents ^
3 My Dear Multiply & Divide (Left to Right) *,/
4 Aunt Sally Adding & Suntracting (Left To Right) + , -

2+2*3^2 = 144? 2+2*3^2 = 20?

144 20

Excel's Order of Operations:

Parenthesis ( )
Ranges use of colon symbol ":"
Example: =SUM(A1:A4)
Evaluate intersections with spaces
Example: =E12:G12 F10:F15 (retrieve what is in F12)
Evaluate unions (,)
Example: =SUM(E10:G10,E14:G14)
Negation (-)
Example: =-2^4 è 16
Example: =-(2^4) è -16
Converts % (1% è .01)
Exponents (^)
Example: 4^(1/2) = 2
Example: 3^2 = 9
Multiplication (*) and division (/), left to right
Adding (+) and subtracting (-), left to right
Ampersand (&)
Comparative symbols: =, <>, >=, <=, <, >
If anything is still left, then left to right
Base
Exponent
2^6 = ?

Annual Rate
Periods per year
Years
Future Value Factor

*Square means 2
Number
Square root

n root of 8, where n = 3
n root means "what times itself n
times equals the number?"
Number (radicand)
n root
3rd root of 8 = ?
8^3
 2
6

0.1
12
30

16

8
3

because 2*2*2 = 8
Base
Exponent
2^6 = ?

Annual Rate
Periods per year
Years
Future Value Factor

*Square means 2
Number
Square root

n root of 8, where n = 3
n root means "what times itself n
times equals the number?"
Number (radicand)
n root
3rd root of 8 = ?
8^3
 2
6
64

0.1
12
30
19.8374

16
4 4

8
3
2
because 2*2*2 = 8
See pdf
Ctrl + 1 opens Format Cells Dialog box Keyboard for today's date = Ctrl + ;

General Change Currency

(Ctrl + Shift + ~) Decimals Comma (Ctrl + Shift + 4) Accounting Date
$55.54 10 1000 1000 1000
$55.54 10 1000 1000 1000
$55.54 10 1000 1000 1000

Total 30.03 Loan Due 8/1/2010

Today 9/15/2010
Days Past Due
board for today's date = Ctrl + ;

Date Date Date

1 2 40436
1 2 40436
1 2 40436

Loan Amount $50,000.00

Loan Issue Date 9/15/2010
Loan Length in days 45
Maturity Date
Ctrl + 1 opens Format Cells Dialog box Keyboard for today's date = Ctrl + ;

General Change Currency

(Ctrl + Shift + ~) Decimals Comma (Ctrl + Shift + 4) Accounting Date
55.5421122 10.01 1,000.00 $1,000.00000 $ 1,000.00 40436
55.5421122 10.01 1,000.00 $1,000.00000 $ 1,000.00 40466
55.5421122 10.01 1,000.00 $1,000.00000 $ 1,000.00 40497

Total 30.03 Loan Due 8/1/2010

Today 9/15/2010
Days Past Due 45
board for today's date = Ctrl + ;

Date Date Date

### Monday, January 01, 1900 9/15/2010
### Monday, January 01, 1900 9/15/2010
### Monday, January 01, 1900 9/15/2010

Loan Amount $50,000.00

Loan Issue Date 9/15/2010
Loan Length in days 45
Maturity Date 10/30/2010
10/30/2010
Dates Sales Sales Rep Product Sold
9/15/2010 $557.00 Joe Product 1
9/16/2010 $1,067.00 Sioux Product 3
9/17/2010 $784.00 Chin Product 1
9/18/2010 $694.00 Phil Product 2
9/19/2010 $1,068.00 Mo Product 3
9/20/2010 $1,125.00 Tina Product 1
9/21/2010 $557.00 Luong Product 2

By default, numbers aligned right By default, text aligned left

9/15/2010 Apostrophe as lead character

9/15/2010 Cell Formatted with Text Numner format
 Percent Number Format
Number First, Then Format 0.02 2 0.0144
Format First, Then Type No.
Format as you Type

Percent = Part/Total
Total Revenue 1000
COGS expense 455
COGS expense as a percent of
Total Revenue

Percent Change = (End - Beg)/Beg = End/Beg-1

Stock value at beginning of year 95
Stock value at end of year 98.75
% change
% change

Percent Increase
Sales New Sales
$100.00
Increase Sales by 10% 1.1
100 * 10% = 10
100 + 10 = 110
100 + 100 * 10%
100*1 + 100 * 10%
100*(1 + 10%)
100*(1 + 0.1)
100*(1.1)
100*1.1

Percent Decrease
Sales New Sales
$100.00
Decrease Sales by 10% 0.9
 Percent Number Format
Number First, Then Format 2% 200% 1.440%
Format First, Then Type No. 2.00% 2.00%
Format as you Type 2.00% 1.596%

Percent = Part/Total
Total Revenue 1000
COGS expense 455
COGS expense as a percent of
Total Revenue 45.50%

Percent Change = (End - Beg)/Beg = End/Beg-1

Stock value at beginning of year 95
Stock value at end of year 98.75
% change 3.9474%
% change 3.9474%

Percent Increase
Sales New Sales
$100.00 $110.00
Increase Sales by 10% 1.1

100 * 10% = 10
100 + 10 = 110
100 + 100 * 10%

100*1 + 100 * 10%

100*(1 + 10%)
100*(1 + 0.1)
100*(1.1)
100*1.1

Percent Decrease
Sales New Sales
$100.00 $90.00
Decrease Sales by 10% 0.9
 We use cell references in formulas that point to formula inputs. This way if
we want to change the formula inputs, it is easy to do.
Relative cell references mean that the cell references moves relatively
throughout the formula copy action.
Absolute cell references mean that the cell references is locked throughout
the formula copy action.
To lock a cell reference, use the F4 key to add dollar signs to convert the
relative cell references to an absolute cell reference.

Day Cash In Cash Out Net Cash Flow For Day

Day1 $14,995.00 $19,353.00
Day2 $11,846.00 $7,698.00
Day3 $12,035.00 $17,226.00
Day4 $5,513.00 $13,434.00
Day5 $14,333.00 $12,108.00
Day6 $19,413.00 $13,347.00
Day7 $17,860.00 $22,132.00

Annual Rate 8.75%

Loan Amount Interest
Loan Option5 $125,000.00
Loan Option4 $90,000.00
Loan Option3 $75,000.00
Loan Option6 $68,500.00
Loan Option1 $50,500.00
Loan Option2 $15,000.00
 We use cell references in formulas that point to formula inputs. This way if
we want to change the formula inputs, it is easy to do.
Relative cell references mean that the cell references moves relatively
throughout the formula copy action.
Absolute cell references mean that the cell references is locked throughout
the formula copy action.
To lock a cell reference, use the F4 key to add dollar signs to convert the
relative cell references to an absolute cell reference.

Day Cash In Cash Out Net Cash Flow For Day

Day1 $14,995.00 $19,353.00 -$4,358.00
Day2 $11,846.00 $7,698.00 $4,148.00
Day3 $12,035.00 $17,226.00 -$5,191.00
Day4 $5,513.00 $13,434.00 -$7,921.00
Day5 $14,333.00 $12,108.00 $2,225.00
Day6 $19,413.00 $13,347.00 $6,066.00
Day7 $17,860.00 $22,132.00 -$4,272.00

Annual Rate 8.75%

Loan Amount Interest
Loan Option5 $125,000.00 $10,937.50
Loan Option4 $90,000.00 $7,875.00
Loan Option3 $75,000.00 $6,562.50
Loan Option6 $68,500.00 $5,993.75
Loan Option1 $50,500.00 $4,418.75
Loan Option2 $15,000.00 $1,312.50
Loan 50000
Annual Rate 0.0775
Periods per Year 2
Period Rate 0.03875
Never: 0.03875
Loan $50,000.00
Annual Rate 7.750%
Periods per Year 2
Period Rate 3.875%
Never: 3.875%
 In cell A4 type the label "Revenue". In cell A5 type the label
"Total Expenses". In cell A6 type the label "Net Income". In
cell B4 type the number 500. In cell B5 type the number 400.
In cell B6 create the formula for calculating Net Income.
Finally, format your mini Income Statement with Number
formatting and Stylistic formatting.
 In cell A4 type the label "Revenue". In cell A5 type the
label "Total Expenses". In cell A6 type the label "Net
Income". In cell B4 type the number 500. In cell B5 type
the number 400. In cell B6 create the formula for
calculating Net Income. Finally, format your mini Income
Statement with Number formatting and Stylistic
formatting.

Revenue $500.00
Total Expenses $400.00
Net Income $100.00
In cell A4 create the label "math expression: 9+2+1*8^2". In cell B4 create
an Excel formula that will force the adding to be done first, then the
multiplyinh, then the exponent. The formula answer must be 9216. You
do not have to use cell references, although you could.
In cell A4 create the label "math expression: 9+2+1*8^2". In cell B4 create
an Excel formula that will force the adding to be done first, then the
multiplyinh, then the exponent. The formula answer must be 9216. You
do not have to use cell references, although you could.

math expression: 9+2+1*8^2 9216

In cell B6, create a formula that calcualtes the period rate.

Annual Rate 11.25%

Periods per Year 2
Period Rate
In cell B6, create a formula that calculates the period rate.

Annual Rate 11.25%

Periods per Year 2
Period Rate 0.05625
 In D5, create the formula Sales minus COGS, and then copy it
down through the range D5:D14. Then format the numbers with
Accounting Number Format.

Week Sales COGS Profit

Week 1 92808 81230.8227540488
Week 2 103740 32966.5302445238
Week 3 92828 10560.3870016106
Week 4 56032 51268.6702732669
Week 5 83685 33203.6565221954
Week 6 89271 26838.6767996522
Week 7 120530 79725.7783228259
Week 8 81764 61115.5607341919
Week 9 101728 3733.3456027696
Week 10 79359 50690.0578094836
In D5, create the formula Sales minus COGS, and then copy it down
through the range D5:D14. Then format the numbers with
Accounting Number Format.

Week Sales COGS Profit

Week 1 $ 92,808.00 $ 81,230.82 $ 11,577.18
Week 2 $ 103,740.00 $ 32,966.53 $ 70,773.47
Week 3 $ 92,828.00 $ 10,560.39 $ 82,267.61
Week 4 $ 56,032.00 $ 51,268.67 $ 4,763.33
Week 5 $ 83,685.00 $ 33,203.66 $ 50,481.34
Week 6 $ 89,271.00 $ 26,838.68 $ 62,432.32
Week 7 $ 120,530.00 $ 79,725.78 $ 40,804.22
Week 8 $ 81,764.00 $ 61,115.56 $ 20,648.44
Week 9 $ 101,728.00 $ 3,733.35 $ 97,994.65
Week 10 $ 79,359.00 $ 50,690.06 $ 28,668.94
 In E5, create the formula "Profit Before Tax times Tax Rate" , and then copy it down
through the range E5:E14. Then format the numbers with Accounting Number Format.
Be sure to use the correct Relative and Absolute Cell References.

Tax Rate
Week Sales Total Expenses Before Tax Profit Before Tax Tax Expense 22.50%
Week 1 $ 169,864.00 $ 17,641.00 $ 152,223.00
Week 2 $ 112,663.00 $ 81,793.00 $ 30,870.00
Week 3 $ 151,992.00 $ 131,215.00 $ 20,777.00
Week 4 $ 156,866.00 $ 130,607.00 $ 26,259.00
Week 5 $ 108,855.00 $ 9,203.00 $ 99,652.00
Week 6 $ 145,067.00 $ 96,964.00 $ 48,103.00
Week 7 $ 195,791.00 $ 105,986.00 $ 89,805.00
Week 8 $ 144,664.00 $ 72,052.00 $ 72,612.00
Week 9 $ 169,962.00 $ 19,366.00 $ 150,596.00
Week 10 $ 168,508.00 $ 162,750.00 $ 5,758.00
 In E5, create the formula "Profit Before Tax times Tax Rate" , and then copy it down
through the range E5:E14. Then format the numbers with Accounting Number Format.
Be sure to use the correct Relative and Absolute Cell References.

Tax Rate
Week Sales Total Expenses Before Tax Profit Before Tax Tax Expense 22.50%
Week 1 $ 169,864.00 $ 17,641.00 $ 152,223.00 $ 34,250.18
Week 2 $ 112,663.00 $ 81,793.00 $ 30,870.00 $ 6,945.75
Week 3 $ 151,992.00 $ 131,215.00 $ 20,777.00 $ 4,674.83
Week 4 $ 156,866.00 $ 130,607.00 $ 26,259.00 $ 5,908.28
Week 5 $ 108,855.00 $ 9,203.00 $ 99,652.00 $ 22,421.70
Week 6 $ 145,067.00 $ 96,964.00 $ 48,103.00 $ 10,823.18
Week 7 $ 195,791.00 $ 105,986.00 $ 89,805.00 $ 20,206.13
Week 8 $ 144,664.00 $ 72,052.00 $ 72,612.00 $ 16,337.70
Week 9 $ 169,962.00 $ 19,366.00 $ 150,596.00 $ 33,884.10
Week 10 $ 168,508.00 $ 162,750.00 $ 5,758.00 $ 1,295.55
 In math we can write this math expression on the board: (1+.01)12*5 . In cell A4 create a label. In
cell B4 create the Excel formula to calculate (1+.01)12*5 . You must use cell references and label the
cell references: .01 = Monthly Rate, 12 = Periods per Year, 5 = Years.

check: 1
In math we can write this math expression on the board: (1+.01)12*5 . In cell A4 create a label. In
cell B4 create the Excel formula to calculate (1+.01)12*5 . You must use cell references and label
the cell references: .01 = Monthly Rate, 12 = Periods per Year, 5 = Years.

Formula for (1+.01)12*5 1.8166966986

Monthly Rate 0.01

Periods Per Year 12
Years 5

check: 1.8166966986
 In cell B5, calculate the Maturity Date for the loan (when loan must be paid)

Loan Issue Date 9/16/2010

Length of Loan in Days 120
Maturity Date
 In cell B5, calculate the Maturity Date for the loan (when loan must be paid)

Loan Issue Date 9/16/2010

Length of Loan in Days 120
Maturity Date 1/14/2011
In cell B5 calculate the percentage change for the stock between Jan 1 and Dec 31. Format the dollar
amounts to show 2 decimals and the % change amount to show 3 decimals (like 1.232%).

Value of Stock on Jan 1 98

Value of Stock on Dec 31 102.5
% Change
In cell B5 calculate the percentage change for the stock between Jan 1 and Dec 31. Format the dollar
amounts to show 2 decimals and the % change amount to show 3 decimals (like 1.232%).

Value of Stock on Jan 1 $98.00

Value of Stock on Dec 31 $102.50
% Change 4.592%
 In cell B5 calcualte the percentage change for the stock between the begin date and end date. Format the dollar
amounts to show 2 decimals and the % change amount to show 3 decimals (like 1.232%).

Value of Stock AIG stock on Jan 1, 2007 1423.6

Value of Stock AIG stock on Dec 31, 2009 29.98
% Change
ate. Format the dollar
e 1.232%).
 In cell B5 calcualte the percentage change for the stock between the begin date and end date. Format the dollar
amounts to show 2 decimals and the % change amount to show 3 decimals (like 1.232%).

Value of Stock AIG stock on Jan 1, 2007 $1,423.60

Value of Stock AIG stock on Dec 31, 2009 $29.98
% Change -97.894%
ate. Format the dollar
e 1.232%).