1. Applying Excel Functions in our Model Part 1
So you've calculated enterprise value, equity value, and equity value per
share using manual discounting. Gonna mix things up a little bit.
We're going to calculate the enterprise value and equity value using actual
dates, both on an end of period basis as well as a mid
period basis. We'll first do that manually, but then we'll use the NPV
function to calculate the end of period enterprise value and then equity
value. And then we'll use XNPV using both end of period and mid
period dates. Again, I've mentioned it before, but it bears repeating,
XNPV does not discount the first cash flow
so we may need to hard code a zero value at the valuation
date to obtain the correct present value and we'll go ahead and show
you that right now. We hard code that as zero. Again,
XNPV does not discount the first cash flow since we have a zero
in there. It doesn't matter. XNPV, assuming you can get the date ranges
correct and the cash flow ranges correct, XNPV will do the work and
will discount everything appropriately. So again, try this out and we will
walk through all of it together. So let's do this one together.
Admittedly, this is the most challenging part of this DCF model,
okay? Since we have actual dates that we want to use.
So for our discounting period, yes, in theory you could take 2023 minus
2022 if you wanted to. Alternatively, again, since we have actual dates,
we can take the dates, one date minus another date. Again, we'll anchor
that date in cell H245 and we'll divide by 365
for days in the year. Again, when Excel sees
dates like this, it doesn't think in terms of dates, it uses serial
numbers, but we can still do math based on it. And again,
yes, I realized that some years have 366 days. It's close enough for
our purposes. Okay. But again, you still get one
out to five years. Okay. Then we can present value the cash flows.
Again, make sure that you anchor your WACC and raise it to the
power of the discounting period. Copy that to the right.
Then we'll calculate the enterprise value. These are unlevered
cash flows, so we'll capture those. And then for equity value,
instead of having net debt and shares outstanding for each one of these
calculations in this section, we'll just do it all
at once. So we'll take our enterprise value
and if you recall, net debt was negative, so we can add it
by adding a negative number. We are of course, subtracting, but we'll anchor
that. And then we need to divide by these shares outstanding,
which we will also anchor. So now we have
$6.19, which is what we had earlier. Now let's apply mid period discounting
as our convention. Again, up until now, we've been using the end of
period or end of year discounting convention. But you'll notice
up here we have cash flow timing, and you can see June 30th,
we have a normal fiscal year of December 31st, so June 30th is
the middle of the year. So we can do some similar math to
what we did in the previous example. We take one date,
subtract a previous date, divide it by 365,
and this will be 0.5. We'll copy that out.
Again, you'll notice everything is 0.5 earlier than the end of period.
That's intentional. We are slightly moving the discounting forward, which
will slightly increase the present value of our cash flows. Again,
the underlying assumption in the mid period discounting is that the cash
flows don't occur at the end of the year, they occur on average
in the middle of the year. Either way though,
take our cash flows divided by one plus our WACC, raise to the
half power in this case. Copy it forward, sum everything up to get
our enterprise value. And we don't need to include that zero in cell
H263. That's really... We really only need to factor that in when we
start using the XNPV and the equity value since we anchored net debt
and shares outstanding, all we have to do is we can literally just
copy that formula and paste it like so. So again,
the present value, the equity value per share is a little bit higher
because we are moving the discounting forward. So our present value increases,
you can see a higher enterprise value than in the end of period.
Therefore, that translates into a higher equity value per share
just through changing your discounting convention. Now, let's go ahead and
use the NPV function to calculate the enterprise value and of course,
the equity value. Again, the end of... The NPV function assumes end of period
discounting. To do equal NPV, we need to reference
our weighted average cost of capital and then we'll capture
the undiscounted cash flows. Press enter. And again, you can see that the
NPV function using end of period discounting is very close to the manual
end of period discounting convention. Once again, to get equity value per
share, we can simply copy the formula again, net debts anchored, shares
outstanding anchored, and we get $6.19.
2. Let's practice!