stern workshop7 excel modeling

8/13/2019 Stern Workshop7 Excel Modeling

1/12

Excel WorkshopPage 1

The Ten Commandments of Excel1 Thou shall not hard code any numbers in a formula

2 Thou shall always reference the source in a formula

3 Thou shall never hide columns/rows, rather group them

4 Thou shall use the mouse as little as possible

5 Thou shall make all worksheets presentable and easy to understand

6 Thou shall be consistent in formatting and coloring

7 Thou shall use relative and absolute references appropriately

8 Thou shall utilize the Help function for it is the Lord and Savior

9 Thou shall never claim to be an expert user less they desire to be smitten

10 Thou shall have no program before Excel


2/12


Commonly Used Shortcut KeysAction KeyStroke Action KeyStrokeAnchoring cells F4 (in edit cell mode ) Ungroup rows or columns Alt D G (or Shift + Alt + )Auditing toolbar Alt T U Edit Cell Mode / Show

dependentsF2 (or Alt T U D)

Auto sum Alt + = Highlight entire column Ctrl + SpacebarBorders - outside area Ctrl + Shift + 7 Highlight entire row Shift + Spacebar

Borders - remove all Ctrl + Underscore (Ctrl + Shift+ -) Insert / Delete Ctrl + + / Ctrl + -

Comment - inserting Shift + F2 (or Alt I M) Move a sheet / Copy a sheet Alt E M / Alt E M, Alt + CComment - deleting Alt E A M Move between toolbars Alt, Ctrl + TabCopy / Cut Ctrl + C / Ctrl + X Move between worksheets Ctrl + Page Up / Ctrl + Page

DownPaste Ctrl + V (or enter) Name a cell Alt I N D (or Ctrl + F3)Paste Special formats Alt E S T Name a worksheet Alt O H R

Paste Special formulas Alt E S F Page setup Alt F UEdit cell F2 Print Ctrl + PFill right / Fill down Ctrl + R / Ctrl + D Print preview Alt F VFont - change size Ctrl + Shift + P Redo Ctrl + YFont - change font Ctrl + Shift + F Repeat F4Function - inserting Shift + F3 (or Alt I F) Find Ctrl + FGo to F5 Replace Ctrl + H

Go to end (contiguousrange)

Ctrl + Arrow Keys Set print area Alt F T S

Highlight Contiguous range Ctrl + Shift + Arrow Keys Undo Ctrl + ZGo to precendent cell(s) /Return

Ctrl + [ (or F5, Enter) Workbooks - toggle Ctrl + Tab

Group rows or columns Alt D G G (or Shift + Alt + ) Zoom - sizing Alt V Z

Row Height Alt O R E Column Width Alt O C W


3/12


Commonly Used FunctionsType of Function Common Use Formula

Absolute function Parity check =ABS(number)

And function If statement logic =AND(logical argument 1, logical argument 2)

Choose function Scenarios =CHOOSE(index number, value 1, value 2.)

Columns function To count N periods (e.g., rate function) =COLUMNS(reference range)

Comp. annual growth rate IRR and growth over a period (1) =RATE(periods, payment, -PV, FV, type)

Count function Data Mining =COUNT(value 1, value 2, value 3)

Hlookup function Used to reference inputs on an output page =HLOOKUP(lookup, array, row, index, range lookup)

If statement Error handling and switches =IF(logical test, value if true, value if false)

Net present value Discounted cash flow analysis (2) =NPV(rate, vlaue 1, value 2, value 3)

Offset function Scenarios =OFFSET(reference, rows, columns, height, width)

Or function If statement logic =OR(logical argument 1, logical argument 2)

Sum-of-years digits depreciation Modeling depreciation =SYD(cost, salvage, life, period)

Text function Footnotes, titles, and subtitles =TEXT(value, "format of text")

Vlookup function Used to reference inputs on an output page =VLOOKUP(lookup, array, column index, range lookup)

(1) Compound annual growth rate formula: [(FV/PV)^(1/N)] - 1(2) Net present value formula: CF1/(1+r)1 + CF2/(1+r)2 + CFN/(1+r)N


4/12


Basic Excel Uses What is a spreadsheet? What are the advantages of using a spreadsheet?

o A spreadsheet is a ledger composed of rows and columns where information can be easily stored, viewedand modified with extreme efficiency.

o An advantage is that it easy to experiment with numbers without having to redo all of the calculations.

HELP! =your new best friend! HitF1 UNDO = CTRL + Z ALTbrings you to the menu bar.

o Hit the corresponding letter that is underlined

Rows, Columns & Cellso Selecting rows & columns:

Use mouse to manually select

Select a row: Shift + Spacebar Select a column: Ctrl + Spacebar Select cells: Ctrl Shift + arrow keys Select all = Ctrl + A

Types of data: Format CellsorCtrl + 1

o Text: Add a before typingo Numbero Currencyo Date

Inserting/Deleting Rows & Columnso Right click insert/deleteo Menu bar Insert Row/Columno Rows:Alt+I+ R; Columns :Alt+ I+ C


5/12


o Delete: Ctrl -

Entering Datao Click on the cello Type in the formula baro Hit F2when in the cell you want to edit

Manipulating Data - Fill down, Copy, Paste, Pastespecial

o

Copy = Ctrl + Co Paste = Ctrl + Vo Paste Special = after copying, right click paste special orALT + E + Sand the letter corresponding to

what you want to doo Fill down : select the cell that has the original formula , hold the shift key down and click on the last cell

(in the series that needs the formula), Menu Bar Edit FillDown or Ctrl+ D

Formatting Cells and Spreadsheetso Format Format cells or Ctrl+ 1o Hover your mouse between a column/row until the pointer turns into an arrow that points in two opposing

directions, double click to make the column Auto fito Format Conditional Formatting

Auto-filter, Sort data: Another advantage of excel is being able to sift through large amounts of data veryquickly.

o Menu barData Sort or Alt + D + So To Auto filter, select the row that you want filtered then : Menu bar Data Filter Auto filter orAlt

+ D + F + F

Basic operations - Referencing cells (see above figure)o The column comes first then the row number


6/12


o Sometimes it is necessary to keep a certain position that is not relative to the new cell location (ex: whenyou use fill down) This is possible by inserting a $before the Column letter or a $before the Row number

(or both). This is called Absolute Positioning/referencing

FormulasWhen selecting cells you can go ahead and either click on the cells you want to have the formula use tocalculate using your mouse, or you can use shift + arrow keys to select the cellso Sumwill add together the specified cells

=SUM (first value, second value) or Alt = =o Averagewill return the average of the specified cells (simplifies having to add the cells together andthen divide by the number of cells)

= average (first value, second value )o Min/maxwill return the smallest (Min) value in the selected range of cells

= min (first value, second value) = max (first value, second value)

o Countfunction returns the number of entries (actually counts

each cell that contains number data) in the selected range ofcells

= count (first value, second value)o Iffunction will check the logical condition of a statement and

return one value if true and a different value if false =IF (condition, value-if-true, value-if-false)

Charts

To insert a chart, select the data you want in your chart then goto the Menu bar Insert Chart orAlt I HThen select the type of chart you want from the left hand sidemenu.o Pie charto Bar chartso Line chartso And more!


7/12


Web Queryo Data>import external data > new web query

Reminder: ALT brings you to the top menu bar, then use the underlined letters to get where you want to goo Go to finance.yahoo.com and type in a ticker of your choiceo Click on the yellow arrow next to last trade (it should turn green)

note that in excel you can download any of the tables that have a yellow arrow next to themo Hit import and OK

Calculating Betao

Open a web browser and go to finance.yahoo.como Type in the same ticker as beforeo On the left hand side menu, click on historical priceso Change the start date to November 29

th2001 Monthlyo Hit get priceso Scroll to the bottom of the page and hit download to spreadsheeto Open with excelo Right click on the bottom worksheet tab and go to move or copy

o Under to book go to Book 1 and hit OKo Delete columns B through F by:

Hovering your mouse over the column letter Shortcut: CTRL spacebar to select a column, CTRL to delete

o Go back to finance.yahoo.com and type in ^SPX (S&P) and repeat steps 3 to 10o Copy data next to first companys historical priceso Calculate monthly return for both MSFT and ^SPX (this month last month divided by last month) and copy down

Calculate 5 year monthly Beta

=SLOPE( known ys, known xs) known Ys = returns for MSFT

known Xs = returns for SPX =LINEST(known ys, known xs) Tools> Data Analysis > regression


8/12


Calculating WACCo Link risk free rate to web query worksheet by:

hitting = and clicking on the web query tab and then the cell you want to link it to hitting = then Ctrl page up to get to the web query worksheet and then using the arrow keys to move around

o Enter 1.5% as the spread and 35% as the tax rateo Cost of Debt = pre-tax rate * (1-tax rate)o Link the risk free rate and betao calculate cost of equity = risk free + beta * risk premium

Calculating PV, FVo = 1/(1+ Discount Rate)^Nr of yearso PV of terminal growth = PV factor * Terminal FCF /(WACC terminal growth rate)o = PV(rate, nper, pmt, [fv], {type])

rateis the interest rate per period. If you borrow money at 6 percent per year and the period is a year,rateequals 0.06. If the period is a month,rateequals 0.06/12, or 0.005.

#peris the number of periods in the annuity. In our copier example,#perequals 5. If payments for the copierare made each month for 5 years, #perequals 60. Your rate must be consistent with#per, of course. In other

words, if #perimplies that a period is a month, you need to use a monthly interest rate. If#perimplies that aperiod is a year, use an annual interest rate.

pmtis the payment made each period. In our example,pmtis $5,000. A payment is a positive number, whilemoney received is a negative number.

fvis the cash balance (or future value) that you want to have after the last payment is made. In our copierexample,fvequals 0. (If we want a $5,000 cash balance after the last payment,fvwould equal $5,000.) Iffvisomitted, it's assumed to equal 0.

typeis either 0 or 1. Type indicates when payments are made. Iftypeis omitted or equal to 0, payments are

made at the end of each period. When typeequals 1, payments are made at the beginning of each period.


9/12


Financial functions

Function Description

ACCRINT Returns the accrued interest for a security that pays periodic interest

ACCRINTM Returns the accrued interest for a security that pays interest at maturity

AMORDEGRC Returns the depreciation for each accounting period by using a depreciation coefficient

AMORLINC Returns the depreciation for each accounting period

COUPDAYBS Returns the number of days from the beginning of the coupon period to the settlement dateCOUPDAYS Returns the number of days in the coupon period that contains the settlement date

COUPDAYSNC Returns the number of days from the settlement date to the next coupon date

COUPNCD Returns the next coupon date after the settlement date

COUPNUM Returns the number of coupons payable between the settlement date and maturity date

COUPPCD Returns the previous coupon date before the settlement date

CUMIPMT Returns the cumulative interest paid between two periods

CUMPRINC Returns the cumulative principal paid on a loan between two periods

DB Returns the depreciation of an asset for a specified period by using the fixed-declining balance method

DDBReturns the depreciation of an asset for a specified period by using the double-declining balance methodor some other method that you specify

DISC Returns the discount rate for a security

DOLLARDE Converts a dollar price, expressed as a fraction, into a dollar price, expressed as a decimal number

DOLLARFR Converts a dollar price, expressed as a decimal number, into a dollar price, expressed as a fractionDURATION Returns the annual duration of a security with periodic interest payments

EFFECT Returns the effective annual interest rate

FV Returns the future value of an investment

FVSCHEDULE Returns the future value of an initial principal after applying a series of compound interest rates

INTRATE Returns the interest rate for a fully invested security

IPMT Returns the interest payment for an investment for a given period


10/12


IRR Returns the internal rate of return for a series of cash flows

ISPMT Calculates the interest paid during a specific period of an investment

MDURATION Returns the Macauley modified duration for a security with an assumed par value of $100

MIRR Returns the internal rate of return where positive and negative cash flows are financed at different rates

NOMINAL Returns the annual nominal interest rate

NPER Returns the number of periods for an investment

NPVReturns the net present value of an investment based on a series of periodic cash flows and a discountrate

ODDFPRICE Returns the price per $100 face value of a security with an odd first periodODDFYIELD Returns the yield of a security with an odd first period

ODDLPRICE Returns the price per $100 face value of a security with an odd last period

ODDLYIELD Returns the yield of a security with an odd last period

PMT Returns the periodic payment for an annuity

PPMT Returns the payment on the principal for an investment for a given period

PRICE Returns the price per $100 face value of a security that pays periodic interestPRICEDISC Returns the price per $100 face value of a discounted security

PRICEMAT Returns the price per $100 face value of a security that pays interest at maturity

PV Returns the present value of an investment

RATE Returns the interest rate per period of an annuity

RECEIVED Returns the amount received at maturity for a fully invested security

SLN Returns the straight-line depreciation of an asset for one period

SYD Returns the sum-of-years' digits depreciation of an asset for a specified period

TBILLEQ Returns the bond-equivalent yield for a Treasury bill

TBILLPRICE Returns the price per $100 face value for a Treasury bill

TBILLYIELD Returns the yield for a Treasury bill

VDB Returns the depreciation of an asset for a specified or partial period by using a declining balance method

XIRR Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic


11/12


XNPV Returns the net present value for a schedule of cash flows that is not necessarily periodic

YIELD Returns the yield on a security that pays periodic interest

YIELDDISC Returns the annual yield for a discounted security; for example, a Treasury bill

YIELDMAT Returns the annual yield of a security that pays interest at maturity


12/12


Error Message Explanations

If a formula cannot properly evaluate a result, Microsoft Excel will display an error value. Each error type has differentcauses and different solutions.

Error Meaning

##### The column is not wide enough to display the contents of the cell. Increase column width, shrink the contents tofit the column, or apply a different number format.

#Value This error message is returned by a function when you are in inputting arguments of the wrong type i.e. if youwere trying to sum Mark + 1

#REF! A cell reference is not valid. Cells may have been deleted or pasted over.

#NUM! The logical value of your inputs is incorrect even if the type and number of arguments are correct i.e. youcannot have an IRR with only positive cash flows

#NAME? You may have misspelled a function name or used a name that Excel does not recognize.

#DIV/0! You are trying to divide by zero

#N/A No value is available

Troubleshooting Formula Errorso Auditing Toolbar (Alt T U)

Trace Precedents Trace Dependents

IF(ISERROR)o

For example, if you enter the following in your worksheet:A1: =B1/C1 B1: 5 C1: 0o You would get #DIV/0! Returned to cell A1. You can change the formula in cell A1 to hide the error value

to the following:o =IF(ISERROR(B1/C1),"",B1/C1)o With the example values given above, this formula returns the empty string ("").

stern workshop7 excel modeling

Documents