Microsoft Excel 2013 - Introdu
Why Excel?
• Used extensively for a variety of requests
• Increases Efficiency, Saves Time
• Better Data Presentation
Excel Interface
Quick
Access
Toolbar
Me
nu
Bar
Na
me
Box
Working in Excel
Cell Navigation & Data Manipulation
Inserting/Deleting Rows/Columns
To Insert Alt+I+C/Alt+I+R or cntrl+ Selecting a row Shift+Space
Delete Cntrl- Selecting a column cntrl+space
Hiding/Unhiding Rows & Columns
Columns Rows
Hiding Cntrl + 0 Hiding Cntrl + 9
Un-hiding Cntrl + Shift + 0 (Does not work on 2010/ Un-hiding Cntrl + Shift + 9
Grouping/Ungrouping
To Un-group
To Group Alt+Shift+Right Arrow Alt+Shift+Left Arrow
To Freeze/Un-Freeze Alt+W+F+F
Merging/Un-merging cellsCntrl+1 Alignment
Edit a cell F2
Copying/Pasting
Paste Special Alt+E+S >>
Creating Hyperlinks Cntrl+K Top Text_to_Column worksheet
Cell Referencing Relative/Absolute >>
Sort & Filter >>
Text to Columns >>
Naming a Cell/Range Cntrl+F3 25%
Data Validation >>
Formatting
Conditional Formatting Alt+H+L >>
Column Adjust Height Alt+H+O+W
Row Adjust Width Alt+H+O+H
Insert/edit comment Shift+F2
Delete a comment Alt+R+D
Indentation Alt+H+6 Alt+H+5
Custom Formatting Cntrl+1 Number Custom 2013A 2014E
Print Settings Page Layout/Orientation
Calculation Options Should always be set at automatic Alt+T+O Formulas Tab
Password Protection Alt+R+P+S
Shift+F10 To open right-click menu
cntrl+shift+* To select the entire data range
5000
4500
3000
2500
2015E
Absolute/Relative Cell Referencing
Quantity Sold (Tickets)
T 100 200 300
i 10
c 15
k 20
e 25
t 30
P 35
r 45
i
c
e
Paste Special
1/1/2012 ----> Copy date
Dell ----> Copy Text
23.456 ----> Copy Comment
Sales Profit Profit Margin
5000 3156 63%
1000 350 ----> Copy Formula
Using Paste spec
----> Copy First 10 country names horizontally
Data Validation
Enter Company Name Satyam
Share Price: INR 600
Return
Return
Return
Formula
tally
Sort & Filter Return
Shares Outstanding Market Cap
Company Sector Price (USD) (in million) (in INR Million)
3M Industrials 150 640.8 96,123
Abbott Labs Healthcare 42 1503.7 63,155
Accenture Business Support 80 660.1 52,805
Actavis Healthcare 240 660.1 158,416
Apple Technology 105 5866.2 615,947
AT&T Telecommunication 34 5185.0 176,290
Bank of America Finance 17 10515.9 178,770
Boeing Industrials 125 712.9 89,116
Caterpillar Industrials 100 627.8 62,785
Dow Chemical Basic Material 48 1194.1 57,316
Eastman Chemical Basic Material 77 149.1 11,479
Facebook Technology 80 2600.1 208,004
Hospira Healthcare 52 168.7 8,774
To Sort Alt+A+S+S To open sort drop-box using keyboard Alt+ Down arrow ke
To Filter Alt+A+T To check/un check data Space
Custom Filter Companies that end with 'L'
Sort by Sector, followed by Market capitalization
Exercise:
Sort the data alphabetically from Z-A
Filter by North American countries
Using custom sort, calculate as of year 2000, how many countries had a population above 50 million?
Company Share Price
Reliance INR 2,500
Tata Steel INR 900
SBI INR 1,800
Satyam INR 600
Infosys INR 1,500
ICICI Bank INR 1,100
Wipro INR 500
Reliance Energy INR 1,400
Grasim INR 2,200
Bharti Airtel INR 850
NTPC INR 200
arrow key
Segregate into 2 columns
Name
ronnie kinder
fola mayer
baylee pinkston
ryan deluca
isabis grigsby
cristobal moe
quintin rocha
tasha pond
oran clarke
sage mckinnon
rory vang
bertha etheridge
lucile parkinson
eloisa vogt
landon palacios
pete hanlon
travis bruno
sullivan humphries
oneal brewster
johnnie mclean
fawn george
joye gooden
lottie speight
Return
Flash Fill - New feature introduced in 2013
Email First Name
Nancy.Freehafer@fourthcoffee.com
Andrew.Cencini@northwindtraders.com
Jan.Kotas@litwareinc.com
Mariya.Sergienko@graphicdesigninstitute.com
Steven.Thorpe@northwindtraders.com
Michael.Neipper@northwindtraders.com
Robert.Zare@northwindtraders.com
Laura.Giussani@adventure-works.com
Anne.HL@northwindtraders.com
Alexander.David@contoso.com
Kim.Shane@northwindtraders.com
Manish.Chopra@northwindtraders.com
Gerwald.Oberleitner@northwindtraders.com
Amr.Zaki@northwindtraders.com
Yvnonne.McKay@northwindtraders.com
Amanda.Pinto@northwindtraders.com
# Exercise on Practice sheet
Conditional Formatting
Revenue: $421,849 $446,950 $464,812 $480,632 $500,487 $550,789 $587,124
Revenue Growth: 6.0% 4.0% 3.4% 4.1% 10.1% 3.0%
COGS: $314,946 $335,127 $334,251 $287,000 $360,450 $314,800 $600,000
Gross Profit $106,903 $111,823 $130,561 $193,632 $140,037 $235,989 -$12,876
Gross Profit Margin 25.3% 25.0% 28.1% 40.3% 28.0% 42.8% (2.2%)
Return
Highlight cells where revenue growth is more than 4%
Highlight negative numbers in red
On practise sheet, highlight cells in red where
1. population is above 50 million
2. population between 5 million to 10 million
To highlight punched numbers F5 Alt+S+O+X (using Go To)
To highlight formulas F5 Alt+S+F+X
Function Syntax
Arithmetic Functions
ABS ABS(number)
PRODUCT PRODUCT(number1,number2,...)
ROUND ROUND(number,num_digits)
SUM SUM(number1,number2, ...)
SUMIF/SUMIFS SUMIF(range,criteria,sum_range)
SUMPRODUCT SUMPRODUCT(array1,array2,array3, ...)
Statistical Functions
AVERAGE AVERAGE(number1,number2,...)
COUNT COUNT(value1,value2,...)
COUNTA COUNTA(value1,value2,...)
COUNTIF COUNTIF(range,criteria)
COUNTIFS COUNTIFS(criteria_range1, criteria1, [criteri
LARGE LARGE(array,k)
SMALL SMALL(array,k)
MIN MIN(number1,number2,...)
MAX MAX(number1,number2,...)
Logical Functions
AND AND(logical1,logical2, ...)
IF IF(logical_test,value_if_true,value_if_false)
NESTED IF
NOT NOT(logical)
OR OR(logical1,logical2,...)
Lookup and Reference Functi
CHOOSE CHOOSE(index_num,value1,value2,...)
LOOKUP LOOKUP(lookup_value,lookup_vector,result_
HLOOKUP HLOOKUP(lookup_value,table_array,row_in
VLOOKUP VLOOKUP(lookup_value,table_array,col_ind
INDEX INDEX(array,row_num,column_num)
MATCH MATCH(lookup_value,lookup_array,match_ty
OFFSET OFFSET(reference,rows,cols,height,width)
INDIRECT INDIRECT(ref_text,A1)
Text and Data Functions
CONCATENATE CONCATENATE (text1,text2,...)
LEFT LEFT(text,num_chars)
LEN LEN(text)
LOWER LOWER(text)
MID MID(text,start_num,num_chars)
RIGHT RIGHT(text,num_chars)
TRIM TRIM(text)
UPPER UPPER(text)
LOWER LOWER(text)
PROPER PROPER (text)
SUBSTITUTE SUBSTITUTE(text, old_text, new_text, inst
REPLACE REPLACE(old_text, start_num, num_chars, n
Date & Time Functions
TODAY Today()
NOW Now()
Information Category Functi
ISERROR ISERROR
IFERROR IFERROR
Excel Functions
What it does?
Arithmetic Functions
Returns the absolute value of a number i.e. value of a number is the number without its sign.
Multiplies all the numbers given as arguments and returns the product.
Rounds a number to a specified number of digits.
Adds all the numbers in a range of cells.
Adds the cells specified by a given criteria.
Multiplies corresponding components in the given arrays, and returns the sum of those products.
Statistical Functions
Returns the average (arithmetic mean) of the arguments.
Counts the number of cells that contain numbers
Counts the number of cells that contain text or numbers or both
Counts the number of cells within a range that meet the given criteria.
Applies criteria to cells across multiple ranges and counts the number of times all criteria are met
Returns the k-th largest value in a data set. You can use this function to select a value based on
its relative standing. For example, you can use LARGE to return the highest, runner-up, or third-
place score.
Returns the k-th smallest value in a data set. Use this function to return values with a particular
relative standing in a data set.
Returns the smallest number in a set of values.
Returns the largest value in a set of values.
Logical Functions
Returns TRUE if all its arguments are TRUE; returns FALSE if one or more argument is FALSE.
Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FA
A Nested IF function is when a second IF function is placed inside the first in order to test additional c
Reverses the value of its argument. Use NOT when you want to make sure a value is not equal to one
Returns TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE.
Lookup and Reference Functions
Uses index_num to return a value from the list of value arguments. Use CHOOSE to select one of
up to 29 values based on the index number. For example, if value1 through value7 are the days of
the week, CHOOSE returns one of the days when a number between 1 and 7 is used as
index_num.
looks in a one-row or one-column range for a value and returns a value from the same position in
a second one-row or one-column range
Searches for a value in the top row of a table of values, and then returns a value in the same
column from a row you specify in the table or array
Searches for a value in the leftmost column of a table, and then returns a value in the same row
from a column you specify in the table
VLOOKUP in excel stands for vertical lookup. This simple function is extremely useful if you have
large lists of data. With this function, you can easily search through a list of data and match
specific criteria.
Returns the value of an element in a table or an array, selected by the row and column number
indexes
Returns the relative position of an item in an array that matches a specified value in a specified
order
Returns a reference to a range that is a specified number of rows and columns from a cell or range
of cells.
Returns a reference indicated by a text value. Mostly used to refer a cell in another worksheet.
Text and Data Functions
Joins several text strings into one text string.
When data is imported or copied into a spreadsheet unwanted characters or words can sometimes
be included with the new data.
Excel has several functions that can be used to remove these unwanted characters and allow the
data to be used in calculations.
The LEFT function is one of these. It allows you to specify how many characters in a cell you wish
to keep. These characters are then copied to a new cell for you to work with.
LEN returns the number of characters in a text string.
Converts all uppercase letters in a text string to lowercase.
MID returns a specific number of characters from a text string, starting at the position you specify,
based on the number of characters you specify.
RIGHT returns the last character or characters in a text string, based on the number of characters
you specify.
Removes Extra spaces
Capitalizes all text in the cell
Converts all text to small case
Capitalizes first alphabet in each word
Replaces existing text with new text in a text string.
Replaces part of a text string, based on the number of characters you specify, with a different text
string
Date & Time Functions
Returns the serial number of today’s date.
Returns the serial number of the current date and time.
Information Category Functions
Returns TRUE if the value is any error value.
Returns value if there's an error, or alternate value if there is no error
uates to FALSE.
additional conditions
equal to one particular value.
Company Sector Price (USD)
3M Industrials 150
Abbott Labs Healthcare 42
Accenture Business Support 80
Actavis Healthcare 240
Apple Technology 105
AT&T Telecommunication 34
Bank of America Finance 17
Boeing Industrials 125
Caterpillar Industrials 100
Dow Chemical Basic Material 48
Eastman Chemical Basic Material 77
Facebook Technology 80
Hospira Healthcare 52
Total market cap
Average Share Price
Maximum Share Price
Minimum share Price
2nd Highest shares outstanding
2nd Smallest shares outstanding
Total Number of Companies
Total market cap of
companies whose
share price is greater
than $100
Total number of
companies with share
price between 50 to
100
Total market cap of
companies in
"Healthcare" and
"Technology" sector
Total market cap of
companies with share
price greater than 100
Shares Outstanding Market Cap
(in million) (in INR Million) * AverageIF/AverageIFS
640.8 96,123 ** Difference between Average and AverageA
1503.7 63,155
660.1 52,805
660.1 158,416
5866.2 615,947
5185.0 176,290
10515.9 178,770
712.9 89,116
627.8 62,785
1194.1 57,316
149.1 11,479
2600.1 208,004
168.7 8,774
erage and AverageA
Shares Outstanding Market Cap Weightage in
Company Sector Reco Price (INR)
(in million) (in INR billion) Index
Reliance Petrochemicals Buy 2,500 410.0 1,025,000 19%
Tata Steel Metals Sell 900 655.5 589,950 11%
SBI Banking Hold 1,100 355.3 390,775 7%
Satyam Technology Buy 600 181.3 108,752 2%
Infosys Technology Sell 900 175.0 157,500 3%
ICICI Bank Banking Buy 500 391.0 195,500 4%
Wipro Technology Sell 500 500.0 250,000 5%
Reliance Energy Power Hold 1,400 350.6 490,784 9%
Grasim Capital Goods Sell 2,200 200.2 440,539 8%
Bharti Airtel Telecom Hold 850 455.0 386,750 7%
NTPC Power Buy 200 962.4 192,488 4%
Maruti Suzuki Indi Automobiles Buy 688 288.9 198,770 4%
HDFC Bank Banking Buy 798 425.1 339,262 6%
TCS Technology Buy 462 978.6 452,118 9%
Tata Motors Automobiles Hold 136 449.8 61,177 1%
Total Index Cap 5,279,365
Total weightage in index of companies with Buy Reco
Total number of companies having price below INR 500
Total market capitalization of companies in Technology
Classify as Small Cap, Mid
Cap & Range of Weightage
Large Cap based on
Using in Index, if
Avg. 1 yr. Where to "INVEST" Weightage in Index
Nested IF /AND <5% Small Cap
return or "NOT INVEST"
Function
32% 5-10% Mid Cap
25% >10% Large Cap
31%
-5%
19%
21%
23%
5%
29%
36%
31%
42%
28%
24%
21%
<< Back <<
Shares
Company Sector Recommendation Price (INR) Outstanding
(in million)
Reliance Petrochemicals Buy 2,500 410.0
Tata Steel Metals Sell 900 655.5
SBI Banking Hold 1,100 355.3
Satyam Technology Buy 600 181.3
Infosys Technology Sell 900 175.0
ICICI Bank Banking Buy 500 391.0
Wipro Technology Sell 500 500.0
Reliance Energy Power Hold 1,400 350.6
Grasim Capital Goods Sell 2,200 200.2
Bharti Airtel Telecom Hold 850 455.0
NTPC Power Buy 200 962.4
Maruti Suzuki Ind Automobiles Buy 688 288.9
HDFC Bank Banking Buy 798 425.1
TCS Technology Buy 462 978.6
Tata Motors Automobiles Hold 136 449.8
## Using VLOOKUP Function
Price Recommendation
Bharti Airtel 850
HDFC Bank
Reliance
Satyam
## Using HLOOKUP Function
A B C
Maths 75 89 95
English 85 85 92
Science 90 45 90
Commerce 45 79 89
OFFSET Function
Month East West
Jan 510 1010
Feb 605 1467
Mar 648 1034 1034
Apr 155 1030 605
May 691 588
Jun 861 694
Jul 379 1219
Aug 317 610
Sep 928 1159
Oct 340 746
Nov 443 1213
Dec 934 1209
Index/Match/Choose
INDEX Function Uses an index to choose value from a reference or array
MATCH Function Looks up values in a reference or array
1 2 3 4
1 Q1'13 Q2'13 Q3'13
2 Sales 450 400 500
3 EBIT 160 150 145
4 EBITDA 180 175 166
5 Net Income 50 75 65
Enter Metric Enter Period
EBIT FY'13
Row # Column #
6
Value
Using VLOOKUP with TRUE
Market Cap
(in INR billion)
A 40% Below Average
1,025,000 B 77% Average
589,950 C 66% Below Average
390,775 D 95% Outstanding
108,752
157,500
195,500 0% Fail
250,000 40% Below Average
490,784 70% Average
440,539 80% Above Average
386,750 90% Excellent
192,488 95% Outstanding
198,770
339,262
452,118
61,177
Data must be in ascending order
What are A's marks in Science?
C Maths 2
95
1467
ference or array
5
Q4'13 FY'13
350 1700
120 575
145 666
55 245
Practical use: To calculate average prices for the lastest week in a data
1-Jan-15 0.5
2-Jan-15 1.2 5.385714 8.5
3-Jan-15 2.5 6.9
4-Jan-15 3.5 7
5-Jan-15 4.5 0.8
6-Jan-15 8.5 6.5
7-Jan-15 5 3.9
8-Jan-15 6 4.1
9-Jan-15 3.5 5.385714
10-Jan-15 9.5
11-Jan-15 3.4
12-Jan-15 3.9
13-Jan-15 7.5
14-Jan-15 3.8
15-Jan-15 8.5
16-Jan-15 6.9
17-Jan-15 7
18-Jan-15 0.8
19-Jan-15 6.5
20-Jan-15 3.9
21-Jan-15 4.1
OFFSET function in financial modelling
Scenario Weak Case 3
Income Statement Assumptions
2008 2009 2010 2011 2012
Revenue Growth (%)
Operating Scenario - Sensitivity Analysis
Revenue Growth (%)
Strong Case 1 7.0% 7.0% 7.0% 7.0% 7.0%
Base Case 2 6.0% 6.0% 6.0% 6.0% 6.0%
Weak Case 3 5.0% 5.0% 5.0% 5.0% 5.0%
Sensitivity Analysis/what-if Analysis using Data Tables
Controllable Input Units Sold
Unit Price 29
Uncontrollable Inputs 500
Units Sold 700 550
Unit Variable Cos 8 600
Fixed Costs 12000 650
Performance Measure 700
Net Cash Flow 2700 750
800
850
900
Units Sold
2700 500 550 600 650 700
$6.00
$6.50
$7.00
$7.50
$8.00
$8.50
$9.00
$9.50
$10.00
$10.50
$11.00
Goal Seek Analysis
Calculation of Loan Repayment
Rate: 6% Assuming you have a loan for $225,000 at 6%
# of Payments 60
Principal 225,000
Monthly Payment ($4,349.88)
Altering the monthly payment using Goal Seek
Change monthly payment to $5000 and check the value for number of payments
Another Example:
Current Sales
Units sold 35
Price 18
Sales 630
What should be the number of units sold if sales should be 1,000?
Net Cash Flow
2700
Example of a one-
variable data table
750 800 850 900
Example of a two-
variable data table
loan for $225,000 at 6% p.a for 5 years.
umber of payments
<< Back <<
LEFT returns the first character or characters in a text string, based on the number of characters yo
RIGHT returns the last character or characters in a text string, based on the number of characters
MID returns a specific number of characters from a text string, starting at the position you specify,
characters you specify.
LEN returns the number of characters in a text string.
January Janua =LEFT(B4,5)
March rch =RIGHT(B6,3)
April Marril =CONCATENATE (LEFT(B6,3), RIGHT(B8,3)) or
Decembe cem =MID (B10,3,3)
Jan25uar 9 =LEN (B12)
Febru ar 10 =LEN (B14)
M25 ch 6 =LEN (B16)
january has 31 days January Has 31 Days =PROPER (B20)
JANUARY HAS 31 DAYS =UPPER (B20)
=SUB
Excel 2010 and Word 2010 Excel 2013 and Word 2013 text to
Excel 2010 and Word 2013 Text is
QXZ650 C650 =REPLACE (B20,"january","March",1).
position of the text to be replaced, use the
** TRIM Function - Removes extra spaces between words
We are one of
We are one of the world's leading franchisors of quick service restaurants se
e number of characters you specify.
the number of characters you specify.
t the position you specify, based on the number of
Marril
=SUBSTITUTE (B20,"january","March",1). If you know the
text to be replaced, use the SUBSTITUTE function
Text is case-sensitive
PLACE (B20,"january","March",1). If you know the
on of the text to be replaced, use the REPLACE function
uick service restaurants serving hot and cold coffee and baked goods.
TODAY 2/20/2019 =TODAY () Displays current date
2/27/2019 Displays date as of 5 days from today's date (all days)
DAY 20 Returns current day (from 1-31)
MONTH 2 Returns current month (from 1-12)
YEAR 2019 Returns current year
NOW 2/20/2019 3:39
11/25/2014 3/2/2017
= WORKDAY((start_date,days,holidays) Returns a number that
WORKDAY
represents a date that is the indicated number of working days
before or after a date (the starting date
Example:
Start date: 10/1/2008
Days to completi 151
Holiday 11/26/2008
Holiday 12/4/2008
Holiday 1/21/2009
4/30/2009 Date 151 workdays from the start date
5/5/2009 Date 151 workdays from the start date, excluding holidays
To calculate number of working days between 2 dates. =NETWORKDAYS()
• Also referred to as Cntrl+Shift+Enter or CSE Formulas
• Two types of array formulas
- Array formulas that perform several calculations to generate a single result
- Array formulas that perform multiple results
Creating Array Formulas that calculate a single result
GOOG MSFT
Shares 200 500
Price 550 40
130000
Total Value
1.Click the cell in which you want to enter the array formula
2.Enter the formula that you want to use.
3.Press Ctrl+Shift+Enter
Creating Array Formulas that calculate multiple results
Hour/Wages
Hours worked Wages Earned
Hourly Rate Jan Feb Mar Jan Feb March
A $100.00 30 40 60 3000 4000 6000
B $125.00 25 29 80 3125 3625 10000
c $250.00 40 35 54 10000 8750 13500
D $90.00 35 45 65 3150 4050 5850
Using SUM IF In Arrays
Company Sector Weightage 1-yr Return
Reliance Petrochemicals 19% 32% 1 # To calculate instances of re
Tata Steel Metals 11% 25% 1 Avg 1 year return is greater t
SBI Banking 7% 31% 1
Satyam Technology 2% -5% 13
Infosys Technology 3% 19%
ICICI Bank Banking 4% 21% 10
Wipro Technology 5% 23%
Reliance Energy Power 9% 5% 1
Grasim Capital Goods 8% 29% 1
Bharti Airtel Telecom 7% 36% 1
NTPC Power 4% 31% 1
Maruti Suzuki India Automobiles 4% 42% 1
HDFC Bank Banking 6% 28% 1
TCS Technology 9% 24% 1
Tata Motors Automobiles 1% 21%
COUNTIFS calculates the number of instances where all conditions are met, SUM IF array is useful to calculate
Using MINIF Array function
Q. Calculate minimum returns given by any company in the Technology sector
-5%
Transpose Using Array Function
## OFFSET Function with Arrays
Note Manually typing braces around a formula will not convert it into an array formula — you must press Ctrl+
Important Any time you edit the array formula, the braces ({ }) disappear from the array formula, and you m
es Earned
# To calculate instances of recommendation to invest
Avg 1 year return is greater than 30%
IF array is useful to calculate instances when either of the condition is met
rmula — you must press Ctrl+Shift+Enter to create an array formula.
m the array formula, and you must press Ctrl+Shift+Enter again to incorporate the changes into an array formula and to a
to an array formula and to add the braces.
IRR Effective compounded interest rate on an investment
Example: Initial investment in a security is $1000. Return after 5 years is $2000. What is the IRR?
Year 0 Year 1 Year 2 Year 3 Year 4 Year 5
$ (1,000) $ - $ - $ - $ - $ 2,000
IRR 14.9%
Year 0 Year 1 Year 2 Year 3 Year 4 Year 5
How IRR Works: $ 1,000 $ 1,149 $ 1,320 $ 1,516 $ 1,741 $ 2,000
XIRR Used if cash flows are irregular in occurrence
12/31/2020 6/30/2021 9/30/2022 3/30/2023 1/31/2024 12/31/2024
$ (1,000) $ - $ - $ - $ - $ 2,000
18.9% <--- Correct here
14.9% <--- Incorrect - time not factored in
Practical appliction of IRR Used by PE firms, hedge funds to measure the attractiveness, profitablity of a project.
NPV The difference between the present value of cash inflows and the present value of cash outflows.
How much upfront investment needed if we wish to have $100 in annual cash flows and $2000 as the return. Expected rate of
Year 0 Year 1 Year 2 Year 3 Year 4 Year 5
($1,279.85) $ 100 $ 100 $ 100 $ 100 $ 2,000
NPV $1,279.85 15%
XNPV For irregular project cash flows
Practical appliction of NPV: Calculate the amount of upfront investment needed, with a certain expected rate of retu
How Might a Company Use NPV in Real Life?
Let's say a Company can estimate their cash flows from a potential expansion project for 10
years into the future.
Internally, they are targeting returns of at least 12% on all expansions and acquisitions,
in order to meet shareholder expectations and grow revenue and profits.
The NPV of those cash flows over 10 years, at the 12% discount rate, is $100 million.
But the project, even with very optimistic assumptions, will cost at least $130 million.
Therefore, it's highly unlikely that the company will pursue this project because the
numbers don't work - if they pay $130 million upfront, it will be impossible to achieve the
12% targeted IRR over 10 years.
Example:
Spec, Inc. is considering the purchase of a machine that costs $60,000 with an estimated salvage value of $12,000.
Operating cash flows are estimated to be $15,000 in year 1, $18,000 in year 2 and $20,000 in year 3.
The company's expected return on investment is 6.5%
Year 0 Year 1 Year 2 Year 3
Operating Cash Flows 15,000.00 18,000.00 20,000.00
Investing Cash Flows (60,000.00) 12,000.00
Total Cash Flows (60,000.00) 15,000.00 18,000.00 32,000.00
IRR 3.62%
IRR of the Machine: Hence, project is not viable
NPV: $56,445.55
($3,554.45)
ess, profitablity of a project.
e of cash outflows.
s the return. Expected rate of return of 15%
a certain expected rate of return and knowledge of cash flows from investment
value of $12,000.
Types of Errors
Error What It Means
#DIV/0! Divide by zero error (undefined value)
Non-valid cell or range reference (usually due to
#DIV/0! deleting rows / columns)
#DIV/0! Intersecting range that doesn't intersect
#DIV/0! Function not recognized by Excel
#DIV/0! Non-valid number in calculation formula
#DIV/0! Wrong type of input to function
Cannot find match for value with lookup function;
also with array functions if referenced ranges are
#DIV/0! not same size
Tools to check Errors
Checks sheet for Errors. Points out formulas
Alt + M + K + K that are different from the ones around that
formula in the worksheet.
Trace Precedents/Dependents
Cntrl+{ Goes to precedent cells
Formatting Check Points
Spell Check
External Links
Remove Circular References
Before closing file, cursor should be at Cell A1 cover page/output tab
Data Format should be consistent across sheets
Example
'=12/0
='Valuation xx'!C12*5
'=A1:F1 B2:B10
'=MAXIF(5, 3)
'=DATE(-1,1,30)
'="Test"*5
'=VLOOKUP("NOVAL",B2:M18,2,FALSE)
. Points out formulas
the ones around that
et.
F7 A MUST!!
Check and break all the external links before
Alt+A+O
sending final Deliv
Should be removed from all the sheets
Should be removed from all the sheets
Cell A1 cover page/output tab Cntrl+Home,
Cntrl+Pg Up/Down
ss sheets
Revenue: $421,849 $446,950 $464,812 $480,632 $500,487 $550,789 $587,124
Revenue Growth: 6.0% 4.0% 3.4% 4.1% 10.1% 3.0%
COGS: $314,946 $335,127 $334,251 $287,000 $360,450 $314,800 $426,000
Gross Profit $106,903 $111,823 $130,561 $193,632 $140,037 $235,989 $161,124
Gross Profit Margin 25.3% 25.0% 28.1% 40.3% 28.0% 42.8% 27.4%
= IFERROR (J9/J6,"NA"). Returns
"NA" as value if the formula
returns an error.
###
trl+Home,
trl+Pg Up/Down
