Free Cash Flow Example | Calculate FCFF in Excel - YouTube

hello friends welcome to this investment-banking tutorial from

wallstreetmojo today we are going to discuss about free cash flow to firm examples

so basically we will try and now solve a couple of examples in Excel

and see what are the practical applications of this so obviously before

you look at this this whole video on FCFF examples I will assume that you know

what is FCFF basically you have an intuitive

understanding of it and in addition to that you know the kind of formulas which

are used in free cash flow to the firm if he don't I would highly recommend

you looking at my previous videos on how to develop intuitive understanding of FCFF

and also on the top three formulas of FCFF which are typically used alright

so let's get started here so let us now look at FCFF example

in Excel so basically I am on the WallStreetmojo website and if you want you

can download this excel example from the website directly from here by clicking

on this link is the example is basically as follows

here we are provided with this balance sheet where in we have the assets and

the liabilities and we are also given the income statement so the objective of

this exercise is to find the free cash flow to the firm for this year of 2008

alright so let's get started so here is the example in the excel sheet now let

me write down the first formula that is used to calculate the free cash flow to

the firm it's basically EBIT into 1 minus tax

rates so I'll write this as 1 minus T plus your non-cash expenses basically

these are the depreciation and amortization plus changes in working

capital minus capex so I hope you remember this is

the first formula for calculating free cash flow to the firm now let's look at

our examples what are the things that we require we require the EBIT so

we have the EBIT in this example so EBIT is nothing but 285 here the depreciation

and amortization if you look at this example again only depreciation is

provided so I will assume that amortization is zero changes in working

capital again it's a very interesting thing if you remember from earlier

videos what as specifically had mentioned that there are certain things in current

assets and current liabilities which we don't include so one of them is

basically the short-term debt when we talk about the the current liabilities

and the other thing that we don't include is cash when you talk about

current assets so please remember that when we are talking about working

capital you should not include cash and you

shouldn't include debt the primary reason we discussed this earlier too is

that we adjust for these items at the end of the valuation exercise so not

when we are calculating the free cash flow to the firm we assume that these

numbers let it be whatever they are and they have to be adjusted at the end when

we get the valuation of the overall firm all right so let's go ahead and now put

in the numbers so EBIT so EBIT here is 285 and what is the tax rate the tax

rate is 30% so EBIT into 1 minus tax this comes out

to be 285 into 1 minus tax rates so this is 199.5 all right now

depreciation and amortization as we said earlier amortization is equal to 0 in

this case so it is just 150 but changes in working capital changes in working

capital so working capital will now consist

primarily of these two these are the account receivables and

inventory in the current assets and in the current liabilities will only

consider accounts payable okay so let me just copy this here all right

so changes in working capital is nothing but whether this change has led to a

cash outflow or a cash inflow all right I understand that many students actually

get confused when they start looking at individual items and when they start

tracking the changes say for example account receivables it

has increased from 45 to 90 all right so whether this is a cash inflow or a cash

outflow I think I'll give you a shortcut to this first before you even think

about any term here just categorize it into assets or liabilities okay so

account receivables is it an asset yes it is inventory is it an asset yes it is

Accounts Payable asset or liability this is a liability okay so now come what may

whatever is the line item here with account receivables inventory or other

line item think of if assets increase what will happen obviously if you have

to buy an asset you will have to spend cash right the cash outflow will happen

so if the asset size increases cash outflow will happen if I have to buy ten

computers which are assets I'll have to give amount of money to buy that

computers right so account receivables and inventory just close your eyes think

whether this is an asset or a liability if it has increased that means

it is a cash outflow so this is 45 minus 9 so essentially from cash flow

point of view this is minus 45 now let's take an example of inventory

right so inventory has increased from 90 to 120 so whether this is a cash inflow

or outflow has the asset size increased yes it did so it must be an outflow so

this will be 19 minus 120 so this was about assets but let's let's talk about

the liabilities now so in this case there has been no change in the assets

in the liability side but let's say for argument's sake if this was 80 so what

would have been the case what happens if liability increases if

liability increases this think of this as you have taken a loan from a friend

or a bank right so if you have taken a loan your liability increases right but

what happens you have now money in your pocket so there's a cash inflow that is

that's happening with the increase in the liability okay so if the accounts

payable was 80 and initially it was 60 this would have been an inflow okay of

20 but in our example I just switch it back to 60 because it was then change

during these two years so this is what is the situation so this is the changes

in working capital the sum total of this this the overall change okay so then

next line item that we need to find is the capital expenditure the capex okay

in order to find a capital expenditure you can find this number in the cash

flow statement unfortunately we don't have the cash flow statement we are only

provided with the balance sheet and the income statement here but obviously

there is a way out in which we can find this easily we have been given this gross

capex number the capital expenditure the PPE is nothing but property plant and

equipment this is the capital expenditure earlier it was 900 the total

amount of assets that they had now it is 1200 so obviously in order to

make it to 1,200 they would have been a capital expenditure or the investment in

the company so it's easy to see that the capex is nothing but the

difference between the two so capital expenditure is to an extent of 300 and

please do remember this is a cash outflow all right so it is outflow that

means it has to be accompanied with the minus sign so let me now go back and

write this formula here and let's do the sum total EBIT into 1 minus T that's

199.5 plus depreciation and amortization that's 150 we have the

changes in working capital that is minus 75 and this is the capex so obviously

this is the cash outflow so I am putting it with a minus sign so what answer

do we get it's minus 26 if we round it off otherwise it's minus 25 point five

so FCFF when we use the EBIT approach comes out to be minus 25 point five in

our example so let us now use the other two set of formulas to find the FCFF

obviously the answer should be same but just for the practice purpose let's do

that right now so the first thing that we'll do is FCFF using the net income

approach okay so I'll write down the formula its net income plus your

depreciation and amortization alright plus interest to one minus tax rate plus

your changes in working capital minus capex so the last two items are

obviously the same the only thing that changes is is the first three items okay

so let me populate that net income depreciation and amortization and

interest one minus tax okay so net income is here

we have 168 for 2008 depreciation and amortization is 150 which we already

know and interest into one minus tax rate so here do we have the interest

expense yes do we have it's 45 into bracket 1 minus tax rates so tax rate

here is 30% that's provided in the example so that's thirty one point five

changes in working capital this we have already calculated earlier so this was minus

75 and capital expenditure capex this is 300 okay

so let's find out this FCFF this is net income plus depreciation on amortization

plus interest into one minus tax plus changes in working capital minus your capex

so what do we have in, in the final answer that's equal to twenty five point

five so it's essentially the same result that we get so even if you start with

net income which you find it easier you can do that so let's move on up to the

third approach of free cash flow to the firm

the third formula for FCFF uses the EBITDA , FCFF using EBITDA okay so

the formula goes like this EBITDA into 1 minus tax rate plus your

depreciation and amortization multiplied by tax rate plus your changes in working

capital minus capex so essentially all these three

terms we already know changes in working capital capex depreciation amortization

we have the only information that we don't have is the EBITDA into

one minus T so I'll put this in in a standard format EBITDA

into one minus tax rate depreciation and amortization into tax rate sorry tax

rate changes in working capital and capex so let us link it from the top so

let's link this from the example above EBITDA we have 435 as EBITDA into one

minus tax rate that is 30% okay depreciation and amortization into

tax rates depreciation amortization here is 150 into tax rate that is 30% okay

changes in working capital we already have calculated that's minus 75 and capital

expenditure is 300 so let's do the sum total three hundred three hundred five

plus 45 minus 75 minus the capital expenditure so this comes out to be twenty five

point five so as you see whichever approach that you use even the net

income approach or the EBITDA approach or the EBITDA approach for calculating

FCFF the answer remains the same