Basic Excel Modeling

Created by David Moore, PhD

Reference Material: Wall Street Prep Excel Crash Course

Key Concepts

  1. Modeling Best Practices
  2. Excel Basics and Formatting
  3. TVM review
  4. 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

  1. Text: black;ariel/calibri;10-12
  2. Borders: Do not overuse
  3. Elevator Jumps: Leave column A blank
  4. Alignment:left-text;right-numbers
  5. Center across;don't merge
  6. Group;don't hide
  7. Bold: titles, dates, draw attention to certain input/assumption
  8. Italics: percentages or changes (over time)

Even More Guidelines

  1. Know your audience
  2. Comments: important for assumptions
    • Won't regret not commenting
  3. One row; one calculation
  4. Simple better than complicated
  5. Avoid daisy chains, constants in formulas, and linking files
  6. 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
  • IFT+#
  • 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 this as depositing money in a bank account paying 5% interest in 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

  1. Formula
  2. Financial calculator
  3. 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 time we will 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.
    1. Compute the PV of each cash flow.
    2. 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 that interest rate r. If g>r the formula will not work!

Excel

There are two ways to use Excel to calculate TVM.
  1. Code in formulas
  2. 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?

  1. Estimate the expected future cash flows.
  2. Estimate the required return for projects of this risk level.
  3. 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 product of the array
    • 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 one argument are true

Dates

  • =EOMONTH(START_DATE,MONTHS)
    • Used to easily create Data 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))

Will look the a given value in the first row of a given table array and return the value in the specified row. Replace row value with MATCH function to make 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))

Will look the a given value in the first column of a given table array and return the value in the specified column. Replace column value with MATCH function to make 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 Analysis