financial modelling workshop

8/19/2019 Financial Modelling Workshop

1/57

FINSOC Presents: CFA Financial Modelling Workshop

ASB G24 !h"rsda# $4th Ma#% $&2p'

2($) Se'ester $ *a'es Beckett


2/57


3/57


4/57

2($) Se'ester $ *a'es Beckett


5/57


6/57


7/57

What is FINSOC

FINSOC 5ni1ersities

O"r Sponsors

FINSOC is a st"dent societ# 6hose 'ission is to help st"dents nd the right careaid the' in p"rsing opport"nities in the nancial ser1ices ind"str#7

What does FINSOC do

• A society for Australian university students studyingFinance & Banking. We help students to find the rightcareer path and.make valuable collections to allow them toprosper

• We operate across three universities: ...!"W# A! and"ydney niversity

• At !"W we are an Arc affiliated ...organisation that willbe running events ...throughout $%' ( so keep an eye outfor ...us)


8/57

er path and to


9/57

Welco'e to Microso,t ./cel If this is the rst time you have used Excel, this workshop will get you up and runni

!preadsheets are incredi"ly versatile and widely used "y companies, organisations,

What is a spreadsheet

Wh# are spreadsheets "se,"l

$ength %&

'idth (

)rea

• ) spreadsheet is essentially an enormous sophisticated calculator which can help

• Each little rectangle is called a *cell* which can "e used to store num"ers, text, dat

• Each cell has a name for it+ "y calling it the column letter rst, then the row num"

!his cell is called B$87

• he entire sheet you see is called a worksheet -the name of this 'orksheet is * Int

• he entire le consists of many worksheets and is called a work"ook -this name of

• he power of a spreadsheet is that it automatically calculates things for you

• )s an example, if we have the dimensions of a rectangle and wish to work out the


10/57

g

universities and #ust a"out everyone

ou manage lots of data

es, times and much, much more

r

od"ction to ./cel*.

this work"ook is *Financial Modelling Workshop*.

rea we can work out its area+


11/57


12/57

General !ips ,or Financial Modelling in

9egend

Formulas 1ardcodes

$inks to other worksheets 2ey inputs

So'e Os and O NO!s o, .;C.9

• 2eep everything neat and tidy 3 use a sensi"le font such as )rial or Cali"ri

• 4ake sure your model is well formatted

• Consider using a cover page for the model

< !r# to learn and "se ke#+oard shortc"ts in ./cel= In partic"lar% learn th

• Consider "sing colo"r&coding and having a legend in the model his will makeis auditing it ) commonly used colour coded system is+

O NO! O !?IS O !?IS

O NO! put hardcodes -ie specicamounts. in cells

O NO! have long formulas in cells5reak up formulas into managea"lepieces

O turn o6 gridlines in "eforesu"mitting models for review he


13/57

icroso,t ./cel

cell na1igation ke#+oard shortc"ts= !his

your model easier to follow for anyone who


14/57

./cel Shortc"ts hroughout this spreadsheet, key"oard shortcuts will "e expressed in two di6eren

his means that "oth the control "utton and the :C: "utton are pressed simultaneo

5se,"l ./cel Shortc"ts

For'atting shortc"ts !a+ na1igation5old C;$ < 5 4ove Copy a ta"

Italic C;$ < I Change ta" name

>nderline C;$ < > 4oving to leftright ta"s

Format 5ox C;$ < % ?ew ta"

Clear cell )$ < E < ) < )

@utline "order !1IF < C;$ < A 0o6@col"'n shortc"

;emove "order !1IF < C;$ < B !elect columnInsert a comment !1IF < F0 !elect row

Copy C;$ < C )dd row

/aste C;$ < )dd column

/aste !pecial )$, E, ! Fit column width

Cut C;$ < 7

%. *2ey % < 2ey 0*

For example, the key"oard shortcut to :copy: something is *Ctrl < C*

0. *2ey %, 2ey 0, 2ey 8, *

For example, to use paste special, we can press *)lt, E, !, * his means that thefollowed "y the :!: key, followed "y the :: key

1ere is a list of 4icrosoft Excel shortcuts /articularly important shortcuts are in re


15/57

ways

usly

)$, 1, @, 4

)$, 1, @, ;

C;$ < /g >pDown

!1IF < F%%

s

C;$ < !pace!hift < !pace

)lt, I, ;

)lt, I, C

)$ < 1 < @ < I

lt key is pressed, followed "y the :E: key,

d


16/57


17/57


18/57

A+sol"te 0e,erencing his is a simple example demonstrating a"soluterelative referencing

Forecasted 0e1en"e

0&%( 0&%K 0&%L 0&%M

)=(( %,&&& $%()(

S!.P $

S!.P 2

;evenuegrowth rate

• ake a look at the formula in cell .$(, it says *=D%&N-%


19/57

9

(


20/57

0&%P 0&0&

ou are a"le to edit the formula

should see dollar signs appearing in front of

sJuare "ox If you hover over this with your

y a cell, the formula that is copied depends

s to cell I$( ?otice how the formula didn:t

erence with a dollar sign in front of it won:t


21/57


22/57

Basic F"nctions EIF% CO5N!% S5M% CO5N$et:s look at the example "elow 'e want to identify how many technology stock

Co'pan# Ind"str# IF

)pple echnology

;io into ;esources

E"ay echnology

otal Q

'e can also count the num"er of technology companies using the !>4IF and C@

>sing the C@>?IF function+ 0 Counts the number of cells that cont

>sing the !>4IF function+ &

?ow, go to cell F%0, and press *)lt < =* his is the autosum function It guess

column a"ove it /ress enter 'e can see that there are 0 technology companies

?ow let:s count how many companies we surveyed 1ere, we use the C@>?)

cell R%0 a"ove his can "e used to count cells with text in them

?ote that the answer is ( and not K, and it didn:t count the "lank cells o count cells with num"ers in them, use the *C@>?* function instead of the

Here, we are looking at cells that consumming the cells that correspond to


23/57

IF% S5MIF there are+

f companies surveyed

>?IF functions+

in the word "Technology"

es that you want to sum the num"ers in the

function ype *=C@>?)-DM+D%8.* into

@>?) function

tain the word apple (i.e. Range E!E# $these (in the range %!%#


24/57

Na1igating the Spread Sheet

Go to cell C%0 1old down *!hift < ;ight)rrow2ey* and stop at around cell I%0

?ow go to cell C%0 1old down *!hift < Down)rrow2ey* and stop around cell C0K

% 0

0

8

9

(

K

LM

P

%&

%%

%0

%8

%9%(

?ow go to cell )%

Go "ack to cell C%0 again

?ow press *Ctrl < ;* he formula from Cell C%0 copies 0ight across the highlig

his time, press *Ctrl < D* he formula from cell C%0 copies o6n the highlight

1old down *Ctrl* A press the updown arrow keys ou Juickly move to the top A


25/57

ted cells

ed cells

"ottom of the column of data


26/57

Charts

Interest ?/ ear Interest ;ate

8S %%K9L 0&&& 0S

9S 3090 0&&% %&S

(S 3%%L%K 0&&8 LS

KS 300LP9 0&&9 8S

LS 3889P9 0&&K KS

S!.P $

$et:s plot the ?/ against interest rates 1ere:s a Juick way to plot the da


27/57

1ighlight cells EL+G%0, then click on :!catter: or *)lt, ?, D, Enter* Choose

In this new chart, we can see that the temperature data is skewing the gr

!elect the graph, then click on the :Format: ta"

>nder :Current !election:, click on the dropdown menu, and select :!eries

>nder :!eries @ptions:, click :!econdary )xis:, then :Close:


28/57

emperature

8&

8%

0M

0P0(

ta+ ry selecting cells CM+C%0 and pressing *)lt, ?, C, Enter*


29/57

the second option in the rst row

aph, so we:ll plot it on a secondary axis

*emperature*:, then :Format !election:


30/57

A"tolter his is a simple example demonstrating autoltering and sorting

!elect cells 5%8+F0(

?ow we can lter out various pieces of information

?et Income -U. Company Industry 4arket Cap -Um.

M9%,MLP Ruan in a 4illion Food 80

99M,L9% 'ok and ;oll Food 99

(M8,&K8 @ooh Girl !alon 1air and 5eauty 8((K8,LLM ) !alt and 5attery Food 80

(M(,&M% 1airanoia 1air and 5eauty 9M

M00,M8& /ho 2ing 'ay Food 0(

8LL,89M Frisky 5usiness 1air and 5eauty 0P

LLK,9K8 Curl >p A Dye 1air and 5eauty 80

L9L,%ML 'ok his 'ay Food %0

M%P,LLK 1air 'e )re 1air and 5eauty 8KMP&,8LM Git3o Chicken Food 88

K9M,MP% !moothies Food 9&

For example, lets look at only 1air and 5eauty companies

First, try to calculate the /E ratio for this list of companies in column F -F%(+F0K. usincolumnT

/ress *)lt, D, F, F* to activate the autolter -It can also "e found in the menus Chec

In cell 4%, press *)lt < Down)rrow2ey* his opens the autolter menu


31/57

/E

g what was taught in prior worksheets ;emem"er to lock the entries in this

k the help le.


32/57

Inde/ and MatchGiven an array of data, the index function can "e used to return the data at a certain

ear A Vuarter ear Vuarter ;evenue -Um.

0&&& % 0&&& % 800&&& 0 0&&& 0 8(

0&&& 8 0&&& 8 8&

0&&& 9 0&&& 9 0L

0&&% % 0&&% % 89

0&&% 0 0&&% 0 0M

0&&% 8 0&&% 8 0M

0&&% 9 0&&% 9 800&&0 % 0&&0 % 8&

0&&0 0 0&&0 0 0&

0&&0 8 0&&0 8 88

0&&0 9 0&&0 9 0&

0&&8 % 0&&8 % 8&

0&&8 0 0&&8 0 8%

0&&8 8 0&&8 8 0K0&&8 9 0&&8 9 0(

!uppose we have the following Juarterly earnings data ry to write a formula to lookfunctions


33/57

row and column reference

up revenue for V8 0&&8 using the Index and 4atch


34/57

39ook"p

he lookup function has 9 inputs

he rst is the data you want to look up his should "e a uniJue identier -eg !t

he second input is the range you want to search and the third is the column of th

ry to write a lookup function using lookup to try and nd the revenue for V9 in th

ear A Vuarter ear Vuarter ;evenue -Um.

0&&& % 0&&& % 80

0&&& 0 0&&& 0 8( 0&&% 90&&& 8 0&&& 8 8&

0&&& 9 0&&& 9 0L

0&&% % 0&&% % 89

0&&% 0 0&&% 0 0M

0&&% 8 0&&% 8 0M

0&&% 9 0&&% 9 80

0&&0 % 0&&0 % 8&0&&0 0 0&&0 0 0&

0&&0 8 0&&0 8 88

0&&0 9 0&&0 9 0&

0&&8 % 0&&8 % 8&

0&&8 0 0&&8 0 8%

lookup is another way to lookup data in a ta"le hough lookup seems like an eaWexi"le tool than Index and match

he fourth input is whether you want the match to "e exact or not 'hen comparian exact match


35/57


36/57

dent num"er as in the example a"ove.

range that you want to return

e year 0&&%

sier lookup function to master, it is a less

g data from year to year, you generally want


37/57


38/57

Pi1ot !a+lesIn Excel &L or later, create a pivot ta"le "y selecting the data -columns 5+G., the

/ivota"le: on the :Insert: ta"

@nce you:ve done that, select the location that you want the pivot ta"le to appea

either on this sheet or a new worksheet, and click :@k:

?ote+ If you:re opening a spreadsheet that you:ve downloaded from the internet,

make sure that you choose to save it somewhere rather than #ust :opening: it

ID $ength Expenditure )ge Income /eople Expday

(M 0 9(9 0( & 9 00L

%&& 0 989 0( & 9 0%L

L&% 0 8L( 0K & 0 %ML(

8( 0 (%9 0K & 9 0(LLK 0 9&L 0L & 0 0&8(

KM% 0 90( 0M & 0 0%0(

(& 0 8M9 0P & 0 %P0

PP 0 9&L 0P & 8 0&8(

L%P 0 9(8 0P & 8 00K(

%9K 0 9LP 8& % 0 08P(

LKK 0 990 8& % 0 00%%M0 0 8PK 8& & 9 %PM

M9P L %9M& 8& & 0 0%%90M(L%98

KP( 0 8%( 8% & 0 %(L(

%%% 8 L8M 8% & 9 09K

M0( 0 998 80 & 8 00%(


39/57

L8% 0 8M% 8( & 9 %P&(

(9& 0 (%L 8( % 9 0(M(

%0K% 0 (LL 8( % 9 0MM(

9P( L %0(9 8( % 0 %LP%90M(L%9

9(L L %K%& 8( % 9 08&

MP& L 0&00 8( % 9 0MMM(L%90MK

9%0 L 0(&0 8( % K 8(L90M(L%98

8&K L %L%9 8K % 0 099M(L%90MK

0KK L %M%( 8K % 9 0(P0M(L%90P

89M L 0&%L 8K % 9 0MM%90M(L%9

K00 L %K(P 8K % 9 08L

L&L 0 8K8 8L & 0 %M%((%0 0 (8P 8L % ( 0KP(

%(8 0 (LM 8L & K 0MP

%&PK L %9L9 8L % 8 0%&(L%90M(L

98& L 0&K& 8L % ( 0P90M(L%90P

%&(( L 00ML 8L % ( 80KL%90M(L%

((9 0 (%9 8M % 8 0(L

L(9 0 90P 8M % 8 0%9(M L %K09 8M % 0 080

%&8% L %8&L 8M % 0 %MKL%90M(L%

P&L L %9KM 8M % 9 0&PL%90M(L%

09& L 0&M0 8M % K 0PL90M(L%98

809 L %MP& 8M % K 0L&

%L& 0 90L 8P & 9 0%8(

9&K L %L0P 8P % 0 09L%%%9 L %9L( 8P % 0 0%&L%90M(L%

8K( L %9KM 8P % 9 0&PL%90M(L%

99M L 0&88 8P % 9 0P&90M(L%98

(L% 9 %&%& 9& % 0 0(0(

8&& L %%M& 9& % 0 %KM(L%90M(L


40/57

P98 L %%P8 90 % 0 %L&90M(L%98

0LL L %KML 90 % 9 09%

08( L 0%(0 90 % K 8&L90M(L%98

%%9P 0 (&P 98 % 9 0(9(

%K9 0 8P% 98 & ( %P((

0%% 8 (L9 98 & 8 %P%88888888

%08 K %8%( 98 % 0 0%P%KKKKKKL

K L %KLK 98 % 0 08P90M(L%98

8%M L %K%8 98 % 0 08&90M(L%98

9&& L %9M9 98 % 0 0%0

8K% L %(8& 98 % 9 0%M(L%90M(L

990 L 0%&% 98 % 9 8&&%90M(L%9(8K 0 9(& 99 % 9 00(

(K( 8 (0L 99 % 0 %L(KKKKKKKL

9LM L %8(K 99 % 0 %P8L%90M(L%

0(8 L 0%9K 99 % ( 8&K(L%90M(L

MLM L 08KM 99 % ( 88M0M(L%90P

K(9 0 88% 9( & 0 %K((

M&L 0 99K 9( & 9 0080P9 L %8K& 9( % 8 %P90M(L%90P

8LL L %(90 9( % 8 00&0M(L%90P

%&&0 L %9&K 9( % 8 0&&M(L%90MK

K&L L %LMM 9( % 9 0((90M(L%98

88( L %PL0 9( % ( 0M%L%90M(L%

P(L L 00&0 9( % ( 8%9(L%90M(L

80 0 8K0 9K & 0 %M%L& 0 89K 9K & 0 %L8

(90 0 K&8 9K % 9 8&%(

%0KK 0 9(( 9K % 9 00L(

L8K 8 KM% 9K & 9 00L

(M8 ( PLL 9K % 0 %P(9


41/57

K&% 9 MK0 9P % 0 0%((

8L% L 0%8& 9P % 9 8&90M(L%90P

P(( L 0(&K 9P % K 8(M

0M 0 8L( (& & 0 %ML(

2$ K PK9 (& & 0 %K&KKKKKKKL

$$2( L %L%M (& % 0 09(90M(L%98

$H 0 9%( (% & 9 0&L(

0 (80 (% & K 0KK

)H 9 M%8 (% % 0 0&80(

H$ L %(P% (% % 0 00L0M(L%90P

$($4 L %L(M (% % 0 0(%%90M(L%9

H2 L %(&9 (% % 9 0%9M(L%90MK99 0 88L (0 & 0 %KM(

KKK 0 8&( (0 & 0 %(0(

%PM 0 9(9 (0 & 9 00L

%%8L 0 K&9 (0 % ( 8&0

8P& K %8K0 (0 % 0 00L

9L% L %P9& (0 % 8 0LL%90M(L%9

MKK L %MP9 (0 % K 0L&(L%90M(LM0 0 8P9 (8 & 0 %PL

(P( 8 KL& (8 % 0 00888888888

K8K L %9PM (8 % 0 0%9

0M9 L %K0M (8 % 9 080(L%90M(L

PP& L %KL9 (8 % 9 08P%90M(L%9

P9P L 0(M( (8 % K 8KP0M(L%90P

%8& 0 9L& (9 % 8 08(KM8 0 (&( (9 & 9 0(0(

L0( 0 9M9 (9 & 9 090

LP( 0 9LM (9 & 9 08P

%0&& 0 (P9 (9 % 9 0PL

0%0 9 KKK (9 & 0 %KK(


42/57

going to

r in


43/57


44/57


45/57


46/57

Sensiti1it# Anal#sis'e are going to do this from scratch in the workshop


47/57


48/57


49/57


50/57

G8 Education Discounted Cash Flow Model(in thousands, fiscal year ending June 31)

Key assumptionsWACC assumptions Key Assumptions Sensitivity Analysis

*ebt + total capital: ,%- erminal growth rate: $.'%- Terminal growth/re0ta1 cost of debt: 2.'%- 3.,, 2.2!/ost0ta1 cost of debt: 4.''- 5arginal ta1 rate: ,%.%%- ".#$!

".%8!

6isk free rate: ,.74- Case names "."$!5arket risk premium: 2.%- 8ase no. *ownside &'.&8!9: .4% 8ase no. $ Base &'.#$!8ost of euity: $.$4- 8ase no. , Aggressive

WA88 "."$! Selected case( $

Discounted Cash Flow Model)isto*ical +e*iod Fo*ecast pe*iod

F;% F;,> F;4>"ales $,2%% $',#&2 $$,2& %,2#" &$#,&- &--,-&- 2$#,%- 2##,'2%?ther sales %' 2$ &% -"% %,8#& ' ' 'otal revenue $,$2% $',"$ $$,$8' %%,'# ',""- &--,-&- 2$#,%- 2##,'2%?perating e1p. $,$2"/ $&,$2%/ $2,'%2/ -,$"#/ &&",#-/ 0&8,2# 02'8,8%% 02&-,28">B@*A 0# 0$"& &,$&8 8,%2 2&,#' &",#-2 2,-'& 2%,-$8*epreciation 0%- 0'8 0"$ 0""$ 0&,-&& 02,2%% 02,""& 0$,&&2 Amortisation 0- 0&"2 0&8# 0# ' ' ' '

>B@ 0% 0&,'"& #& -,& &",82" &-,2'% 22,-&' 2$,%2%a1 ,%- ## $2- 0&%2 022 0"#" 0&%2 0%8&$ 0-'88!?/A &'2/ -%$/ $-8 ,2%& &$,88& &2,'## &,8"- &%,$8 Add depreciation %- '8 "$ ""$ &,-&& 2,2%% 2,""& $,&&2 Add amortisation - &"2 &8# # ' ' ' '8ape1: &,%'8/ %%%/ -28/ "&$/ 2,2#"/ $,%%"/ #,'8$/ #,2-$/!et Ac. ' &&,"/ $8#/ &&,-&$/ $,8'$/ %2,8''/ ,'''/ '@ncrease !W8 ' 2,"". $,-&2." &',%#8.$ ,'8#.- %#8 &2$# &8#$F8FF 0&,%8 0",%#8 $,-% #,#2' 08,$-% 0$-,&' 2,'$" $&,22'

/ of cash flows % % % % % 0,4$.%4'4, $%uity value

WACC

"hares outstanding

"hare price


51/57

"hare price

Ma*1in and 1*owth *ates analysis

YOY historical growth rates

F;% F;,> F;4>"ales growth C-D: nm 7,- =- =2- %2- ,$- ,$- 4-?ther sales growth C-D: nm


52/57

Model assumptionsDownside case

F;% F;,> F;4>"ales ,#$22 ,%#4$ ,,#$' 2'#$4= ,4#'<


53/57

# # # # # # # #/ of cash flows 0,4#$ $%# F;4>

"ales $,2%% $',#&2 $$,2& %,2#" &$#,&- &",#88 28,'2# 2%8,#2"?ther sales %' 2$ &% -"% %,8#& % % %otal revenue $,$2% $',"$ $$,$8' %%,'# ',""- ='#477 $'7#%$4 $27#4$=?perating e1p. $,$2"/ $&,$2%/ $2,'%2/ -,$"#/ &&",#-/ C


54/57

rate

2.'! 2.-!

F;'> F;2> 2$,8%- 2%#,&' '

' ' '

2$,8%- 2%#,&' '

022%,'& 02$,&%- '

2-,8&% 28,"$8 '

0$,2$- 0$,$%8 '

' ' '2#,-" 2,-' '

0-$-# 0-%-& '

&-,2' &-,8"" '

$,2$- $,$%8 '

' ' '

#,2-$/ #,2-$/ '

' ' '

&%#-- &-&$% '

$2,%#% $#,&$' #--,2"$$,'$.,,=' $$'2.4$2$' $=


55/57

F;'> F;2> 4- 4-nm nm4- 4-4- 4-4- 4-4- 4-nm nm4- 4-4- 4-%- %-nm nm

4- 4-

F;'> F;2> $1.33! $1.33!

.&! .&!1.31! 1.31!#.##! #.##!.3! .3!'.1&! '.1&!1.3! 1.&&!#.##! #.##!

&.&&! &.&'!


56/57

F;'> F;2> $4


57/57

$$#,'$ $#$'2 $= F;2> 2-",2# 2"',&

% %$

financial modelling workshop

Documents