Basic Excel Modeling

Created by David Moore, PhD

Reference Material: Wall Street Prep Excel Crash Course

Key Concepts

Modeling Best Practices
Excel Basics and Formatting
TVM review
Excel Functions

Statistical
Logical and Dates
Lookup

Modeling Best Practices

Universal Standards

Color code cells based on content. MOST IMPORTANT TO GET CORRECT

Blue: All hard-coded values. ex. historical data and assumptions
Black: All formulas and links to same sheet
Green: All direct links to other worksheets.
Red: Link to other workbooks (i.e., files) or warnings

Note: Text should be left black.

Non-Universal: Best Practices

Assumptions: Cell background yellow and black border
Currency symbol: Only place at top and bottom of each schedule/column
Accounting format numbers

Decimals

No Decimal: Years or numbers in $Thousands
One Decimal: $Millions, most multiples and percentages
Two Decimals: EPS and Share price
Three Decimals: Shares if in millions

General Principles

Clarity
Efficiency
Consistency

More Guidelines

Text: black; Arial/Calibri; 10–12 pt
Borders: Do not overuse
Elevator Jumps: Leave column A blank
Alignment:left-text;right-numbers
Center across;don't merge
Group;don't hide
Bold: titles, dates, draw attention to certain input/assumption
Italics: percentages or changes (over time)

Even More Guidelines

Know your audience
Comments: important for assumptions

You'll never regret over-commenting

One row; one calculation
Simple better than complicated
Avoid daisy chains, constants in formulas, and linking files
Long sheet better than many sheets

Excel Settings (Alt f t)

Automatic Except for data tables
Enable iterative calculations
Optional:

Disable move down
Set decimal preference
Disable start screen

Excel Formatting Exercise

Notes to Formatting Exercise

Formatting 101

Select column(row): Ctrl(shift)+spacebar
Autofit column: Alt H O I (Manual: Alt H O W)
Enter formatting box: Ctrl+1

Use arrow keys and tab to navigate

Access borders Alt H B (bottom border O)
Go to Special: F5 Alt s

Navigating 101

Select a range Shift+arrow keys (Ctrl+shift+arrow keys to select an array)
Ctrl+arrow key: move to last entry in row/column
Same applies within cells
Ctrl+PgUp(Dn): Move between sheets

Copying 101

Copy: Ctrl+C
Paste: Ctrl+V (select an entire range to paste a formula)
Ctrl+r(d): copies selection to the right (down)
Paste Special: Alt E S (common use is for formats or values)

Editing 101

F2 to enter a cell (if adding to formula need to hit F2 again to select cells)
Ctrl+z=Undo Ctrl+y=Redo
Shift+F11 Add sheet (Alt H O M to move to end)
Add row(column): Alt I R(C)
Delete row(column) Alt H D R(C)
Insert Comment:Shift+F2
Access Clear options: Alt H E

Referencing 101

Excel uses $ to anchor cells
$A1 anchors the column
A$1 anchors the row
$A$1 anchors both (or the cell)
Use F4 to toggle through reference options
Ask "Do I want cell reference to change when I copy formula?"

Grouping 101

Group Rows(columns)

Select rows(columns)
Shift+Alt+right arrow to group
Alt a h to contract
Alt a j to show
Shift+Alt+left arrow to ungroup

Grouping Sheets

Ctrl+Shift+PgDn(Up)
Can enter values into same cell of ALL grouped sheets
Ctrl+Shift+PgUp to ungroup

Auditing 101

F2
Ctrl[(]): Trace precedent(dependent)
Alt M P(D):Trace precedent(dependent)
Alt M A A: Remove trace

Ctrl Shortcuts

CTRL+SHIFT+!: Number format; 2 decimals, comma separator
CTRL+SHIFT+$: Currency; 2 decimals
CTRL+SHIFT+%: Percent; no decimals
CTRL+SHIFT+#: Date Format; D-MMM-YY
CTRL+B: Bold
CTRL+I:Italicize
CTRL+U: Underline
CTRL+SHIFT+_: Remove all borders

Common Cell Problems

#DIV/0!:Divided a number by 0
#REF!: Invalid cell reference
#NUM!: Invalid numerical value in formula/function
#NAME?: Unrecognizable text in a formula
#VALUE!: Incorrect arguments in a function
#####: Column not wide enough (ALT H O I)

TVM Review

Interest Rates and Time Value of Money

A dollar in hand is worth MORE than a dollar in the future.

Suppose you are given the following opportunity: Invest $\$100,000$ today and you will receive $\$105,000$ in one year.
Think of this as depositing money in a bank account paying 5% interest for one year.
We call the difference in value between money today and money in the future the time value of money.

Terminology

Present Value (PV): current value of money
Future Value (FV): Value of an investment after one or more periods (hours, day, month, year, etc.)
Interest rate (r or i): The rate at which we can exchange money today for money in the future (the price of money).
AKA discount rate, opportunity cost of capital, cost of capital, cost of debt, cost of equity, required rate of return, rate of return, user cost.

Ways to solve TVM problems

Formula
Financial calculator
Spreadsheet (Excel)

Future Value: formula

$FV=PV(1+r)^t$

FV = Future Value

PV = Present Value

r = Interest rate

t= number of periods

$(1+r)^t$= the future value factor

Present Value

How much do I have to invest today to reach certain amount of money in the future?

$FV = PV(1+r)^t$
Rearrange to solve for $PV = \frac{FV}{(1+r)^t}$

When we talk about discounting, we mean finding the present value of some future amount.
When we talk about the "value" of something, we mean the present value unless we specifically indicate that we are calculating the future value.

Valuing a Stream of Cash Flows

Often, we'll have multiple cash flows on our timeline.

Investment (factory, new store, retirement), debt repayment (mortgage, student loan, interest), salary, bonds, stocks...

We already have the tools to do this.

Compute the PV of each cash flow.
Sum the present values

Present Value of Cash Flows

$PV=C_0+\frac{C_1}{(1+r)}+\frac{C_2}{(1+r)^2}+\frac{C_3}{(1+r)^3}+...+\frac{C_N}{(1+r)^N}$

$FV=C_N+C_{N-1}(1+r)^1+C_{N-2}(1+r)^2+$
$C_{N-3}(1+r)^3+...+C_{N-(N-1)}(1+r)^{N-(N-1)}$

Annuities

A finite series of equal payments that occur at regular intervals

If the first payment occurs at the end of the period, it is called an ordinary annuity.
If the first payment occurs at the beginning of the period, it is called an annuity due

Annuity Formulas

$PV=C[\frac{1-\frac{1}{(1+r)^t}}{r}]$

$FV=C[\frac{(1+r)^t-1}{r}]$

Perpetuities

Infinite series of equal payments.

$PV=\frac{C}{r}$

Sign Reminder

A good way to remember signs:

Cash outflows are negative
Cash inflows are positive

Try:

You borrow 10,000 and make payments of 940 a year for 15 years. What is the rate?
There is an investment that pays 300 a year for 3 years how much are you willing to pay for it today? 5% interest.

Growing Annuity and Perpetuity

Growing Annuity

$PV=\frac{C}{r-g}[1-(\frac{1+g}{1+r})^t]$

Reminder: Cash Flow C occurs at t=1, t=2 cash flow is C(1+g).

Growing Perpetuity

A growing perpetuity is a growing stream of cash flows that lasts forever
$PV=\frac{C}{r-g}$

Important: Growth rate (g) must be less than the interest rate r. If g>r the formula will not work!

Excel

There are two ways to use Excel to calculate TVM.

Code in formulas
Use Excel built in functions:

= FV(rate, nper, pmt, PV)
= PV(rate, nper, pmt, fv)
=RATE(nper,pmt,pv,fv)
=NPER(rate,pmt,pv,fv)
=PMT(rate,nper,pv,fv)

NPV and IRR

NPV Overview

How much value is created from undertaking an investment?

Estimate the expected future cash flows.
Estimate the required return for projects of this risk level.
Find the present value of the cash flows and subtract the initial investment to arrive at the Net Present Value.

NPV Calculation

$$NPV=\sum_{t=0}^n \frac{CF_t}{(1+r)^t}$$

Initial cost is often $CF_0$ and is an outflow.

$$NPV=\sum_{t=1}^n \frac{CF_t}{(1+r)^t}-CF_0$$

Excel: NPV

=NPV(rate,$CF_1-CF_t$)

First parameter = required return entered as a decimal
Second parameter = range of cash flows beginning with year 1
After computing NPV, subtract the initial investment ($CF_0$)

IRR Overview

Most important alternative to NPV
Widely used in practice
Intuitively appealing
Based entirely on the estimated cash flows
Independent of interest rates

IRR Definition

IRR = discount rate that makes the NPV = 0

Enter NPV = 0, solve for IRR:

$$NPV=\sum_{t=0}^n \frac{CF_t}{(1+r)^t}$$

Excel: IRR

=IRR($CF_0-CF_t$, guess)

First parameter = range of cash flows beginning with year 1
Second parameter = Optional guess.

Excel Functions:Statistical

The Basics

=SUM(Range) (ALT+=)
=AVERAGE(RANGE) (Alt H U A)
=MAX(RANGE) (ALT H U M)
=MIN(RANGE) (ALT H U I)

COUNT

=COUNT(RANGE)

Counts cells that contain numbers

=COUNTA(RANGE)

Counts cells that are not empty

=COUNTIF(RANGE,CRITERIA)

Counts cells that meet the criteria
COUNTIFS for multiple

Basics with Logical

=SUMIF(CRITERIARANGE,CRITERIA,SUMRANGE)
SUMIFS(SUMRANGE,CRITERIARANGE1,CRITERIA,...)
=AVERAGEIF(CRITERIARANGE,CRITERIA,AVERAGERANGE)
AVERAGEIFS(AVERAGERANGE,CRITERIARANGE1,CRITERIA,...)

Can embed IF statement in other basic statistical as well.

Other

=SUMPRODUCT(array1,array2...)

Returns the sum of the products of the arrays
Useful for weighted averages

Excel Functions:Logical and Dates

IF

=IF(LOGICAL_TEST,VALUE_IF_TRUE,VALUE_IF_FALSE)

Can nest IF statements
Quotes needed for text outputs

=IFERROR(VALUE,VALUE_IF_ERROR)

Used for error trapping

Alternative to Nested IF

=AND(LOGICAL1,LOGICAL2,...)

Evaluates to TRUE if ALL arguments are true

=OR(LOGICAL1,LOGICAL2,...)

Evaluates to TRUE if any argument is true

Dates

=EOMONTH(START_DATE,MONTHS)

Used to easily create date headers

=YEARFRAC(START_DATE,END_DATE,BASIS)
=DATE(YEAR,MONTH,DAY)
=DAY(DATE), =MONTH(DATE), =YEAR(DATE)

Excel Functions:LookUp

Basics

=HLOOKUP(LOOKUP_VALUE,TABLE_ARRAY,ROW_INDEX_NUMBER,(RANGE_LOOKUP))
=VLOOKUP(LOOKUP_VALUE,TABLE_ARRAY,COL_INDEX_NUMBER,(RANGE_LOOKUP))
=CHOOSE(INDEX_NUM,VALUE1,VALUE2,...)
=OFFSET(REFERENCE,ROWS,COLS)
=INDEX(ARRAY,ROW_NUM,[COL_NUM])
=MATCH(LOOKUP_VALUE,LOOKUP_ARRAY,MATCH_TYPE)

HLOOKUP

=HLOOKUP(LOOKUP_VALUE,TABLE_ARRAY,ROW_INDEX_NUMBER,(RANGE_LOOKUP))

Looks up a given value in the first row of a table array and returns the value in the specified row. Replace the row value with a MATCH function to make it fully dynamic. If looking up text, set RANGE_LOOKUP to FALSE for an exact match.

VLOOKUP

=VLOOKUP(LOOKUP_VALUE,TABLE_ARRAY,COL_INDEX_NUMBER,(RANGE_LOOKUP))

Looks up a given value in the first column of a table array and returns the value in the specified column. Replace the column value with a MATCH function to make it fully dynamic. If looking up text, set RANGE_LOOKUP to FALSE for an exact match.

OFFSET

=OFFSET(REFERENCE,ROWS,COLS)

Given a location (reference) will count x rows down and y columns to the right. Set reference to top left corner of the table and use MATCH to output ROWS and COLS (will need to subtract 1)

INDEX

=INDEX(ARRAY,ROW_NUM,[COL_NUM])

Given an array will return the value at the location of the given row and column. Use MATCH to output ROW_NUM and COL_NUM

MATCH

=MATCH(LOOKUP_VALUE,LOOKUP_ARRAY,MATCH_TYPE)

Returns the row (column) location of a given value in an array. Set MATCH_TYPE to 0 for an exact match.

Custom Number Formatting

Format Multiples

_(#,###0.0x_);(##,##0.0x)

Format Normal with special value for zero

#,##0.00_);(#,##0.00);"Balance"

Format Number with any word

0 "days"

Details on Formatting

#,## is used to for comma separator
_( or _) creates a space the size of ( or )

Used for alignment

Number of 0 after 0. is for number of decimals
First argument is for positive numbers
Second argument is for negative numbers
Third argument is to format 0
Fourth argument formats text
Arguments separated by ;

Key Learning Outcomes

Excel, Excel, Excel!
Basic modeling formatting
TVM!
Basic logical and dates, statistical, and lookup functions

Next time

Financial Statement Review