What is the big deal this week? We're going to progress from some of the stuff we started last week. What's a DCF? Why do we do it? Maybe give it a little bit more detailed prompting to try to get the result we want.

I don't trust AI yet.

How should we prompt it and say, "Okay, here's how to build the first stage projection period." My starting point with a DCF is always to start with consensus numbers.

So what is a DCF, Debs? DCF allows us to calculate what a company's worth based on its future expected cash flows discounted to today's money.

Cost of equity is a little bit more tricky, I think, to articulate.

Ultimately, you have to build a set of forecasts anyway, even for multiples, and therefore you might as well build a DCF on top of that.

Hello to all of our listeners, and welcome to this week's episode of "What's the Big Deal?" where we take a look under the hood of major deals in the finance industry, and also we look at finance industry developments. My name is Deborah Taylor, and I'm going to use my career in investment banking to bring insights to our discussions from a public markets perspective.

And I'm Graham Smith, and I'll use my career in investment banking and private credit to bring the private market perspective here.

Excellent. Right, Graham, as usual, my question to you is, what is the big deal this week? Big deal. So we're going to continue on the same thing we started last week, testing out some explainer topics, and in particular, looking at Claude Code, or not Claude Code, but Claude in Excel rather, and see how it does building a DCF this week.

We're going to progress from some of the stuff we started last week, maybe give it a little bit more detailed prompting to try to get the result we want.

And then we're going to go through and talk about it and hopefully pepper in a little bit of what's a DCF, why do we do it, and some chat about the company we're going to take a look at today. So how's that sound? I can't wait. Yeah, it sounds brilliant.

I know DCF is kind of loved and loathed in equal measure by analysts, so I'd love to see how AI does at building us a DCF. We will, as you said, we'll start with a short and painless primer on what a DCF is.

Then we'll put AI to the test. When I say we, I mean you, Graham, of course.

I think we're going to use Lululemon, aren't we, as our case study for this- Yeah ... for this test.

Yeah. So you-- I guess the nice thing about Lululemon is everyone knows it to some extent.

That's the nice thing about using a retail brand is everyone's got some kind of baked-in knowledge of the company.

But you're using this for some case studies over some summer instruction.

Is that right? That's correct. We love to use Lululemon because, as you say, it's well known. Well, particularly well known if you're a sporty type or you're a big fan of Instagram.

So yeah, but we use it because it's also quite a simple business model.

It's very easy to kind of learn a DCF for a company which sells to customers in the consumer market.

They make things that can be sold. So it's nice and easy to understand.

So it just makes the mechanics a little bit easier.

So yeah- Yeah ... we'll be using this over the summer, particularly for our intern training.

Sounds good. Well, how about this? Why don't we-- Should we do the prompt and then we'll let Claude build, and then while Claude's building, we can talk about DCF? You know, just- I love that ... we both worked in banking trying to make the best use of time here.

Yeah.

So all right, can you see my screen, Debs? I can see your screen, absolutely.

On my other laptop. All right.

It's looking very blank.

Okay. We're going to cross our fingers and hope it works.

Yeah.

I loathe this laptop. I really do. It's the worst.

But we'll see if it plays ball. Oh.

Restart the Claude add-in.

Okay. Here we go.

Let's say, "Build a DCF for Lululemon.

Follow financial modeling best practices, including..." I think that the issue I was finding last week, and Debs, add to this list- Uh-huh ... one of the main issues I was finding is a lot of Claude was following best practices. Inputs were in blue, links to other sheets were in green, all that kind of stuff. But it was doing some pretty crazy stuff that you would really get-- You wouldn't get fired if you were an analyst for doing, but maybe put on a probation list.

Mm-hmm. It was building hard code inputs into a formula in Excel, so you really had no idea where stuff was coming.

So I'm going to prompt it not to do that.

Is there anything on- Mm-hmm ... your list that you remember from last week where we want to say, "Hey, do this, definitely do this, or definitely do not do this"? I don't think there was anything to say definitely do not.

I always use the caveat when I'm building a prompt to say, "Let me know if there's any information you need from me." But that's just because I'm quite risk-averse.

I don't trust AI yet.

Fair. And that is my approach.

Yeah. Okay. So I'm going to say, "Follow financial modeling best practices, including the following. Don't embed inputs into formulas.

Always link a hard-coded input into its own cell." And then say, "Standard financial modeling formatting, such as hard codes in blue, formulas in black, and links to other sheets in green." Did a pretty good job of that last time, but let's just- Yeah ... prompt it. Okay. What information do we want to tell it to use? So here's where I think these tools are incredibly powerful, but we have to give them some guidance, right? Mm-hmm.

So we want Claude to go pull the latest filings and build our DCF based on a picture from today. But I guess more importantly, how do we want to instruct it to build the forecast? And while it's building, we can talk about how the DCF actually works and why that's- Mm-hmm ... important. But ultimately, that's what it's all about, right? So how should we prompt it and say, "Okay, here's how to build the first stage projection period." Yeah. I would just see if it can access any consensus numbers, because my starting point with the DCF is always to start with consensus numbers and then see- Yeah ... whether the answer is aligned with what the market's actually pricing the company at.

Yeah.

See if it can find consensus numbers.

I know if you're in an investment bank, you obviously have access to data systems like Bloomberg and Fact Set that you can extract that information from and provide to Claude.

Exactly. And even if you're not, we all have access to Yahoo Finance and Google Finance, whatever- Yeah ... platform you want to use. It will usually give you-- A nice thing about using a big company like Lululemon is you'll undoubtedly be able to find at least consensus EPS estimates.

That should be relatively easy to find. Okay.

So I say do the following: build stage one based on consensus EPS estimates, pull the latest filings for Lululemon, and project earnings/cash flow from today.

Yeah.

Anything else you want to give it? So I would be tempted to provide a cost of capital, but I think you're a bit more gung-ho. Let's see what it uses.

I want to see how it does it. Yeah. I want to say calculate the WACC for Lulu based on cost of debt that you'll pull from-- Where are we going to get-- Now, do we think Claude is going to have access to any kind of current, or rather yield to maturity on any Lululemon debt? Or do you just want to say, "Hey, use the cost of debt that's in the 10-K?" So I would personally never use what's in the 10-K because it's a historical number. So I would say do your best to find a current number for the cost of debt.

Let's say pull from...

Cost of debt, let's say, yeah. I like it. Do your best.

Because really we want it to reflect current risk pricing. Do your best to find the current market cost of debt, and then make assumptions for the cost of equity, but make it explicit in the model how you've arrived at that.

Okay? Brilliant.

All right. Let's see.

Put to the test. Let's take a look at the numbers then.

Okay. On that note, let's see what we got.

Yeah, let's see how good we are with that.

This looks a little bit simpler than the merger model we did last week- Mm ... which should be good here. Okay.

Current share price, diluted shares outstanding, market cap, risk-free rate, equity risk premium. Let's just see how it's calculating these.

Sure. Standard equity risk premium.

Okay, pulling US Treasury rate.

Mm-hmm.

Sure. Pre-tax cost of debt. Okay. Where are we pulling here? No funded long-term debt outstanding, only a revolver.

Used corporate BBB equivalent.

Okay. That doesn't- Okay. Yeah ... seem like a crazy assumption to make.

It's not relevant here in the sense we're just going to have 100% equity weight anyway. But okay.

Okay. Effective tax rate, after-tax cost of debt, again, which is not going to make a difference here.

Okay. All right. So our WACC is just our cost of equity capital in this case. Now, you know Lululemon.

Is this right about the debt? What, about the weight of debt? It being zero? About the fact that there's no revolver.

Or sorry, there's no actual debt apart from a revolver. Did Claude get that right? Yeah, that sounds right. They have a big D2C business, so I think that usually makes them a bit more cash generative.

But I think the one challenge I would have here already is that we are therefore assuming that that is a permanent state of affairs, that they're never going to be leveraged. We usually try to use- Yeah ... the target leverage in the cost of capital.

So that's maybe a bit of a punchy one.

I would say a WACC of-- I'm already doing what your old manager used to do.

I'm looking at the number going, "Hmm, that sounds a little bit high to me." I'd probably be erring on somewhere around 10, 11%.

But let's see where we get to in terms of the numbers.

Yeah. Okay, we got some margin assumptions, DNA in terms of revenue, CapEx as a percentage of revenue.

Just looking at these, I've got a feel now for how it's going to build out- That's high ... its model. Are you seeing 7%? 7% is high here.

That's huge.

Change in working capital as a percentage of revenue. What is it actually, ish? Do you remember? So consumer businesses are usually about 4% or 5%.

They have been going through a store expansion, so it might have gone up to around 6%. But yeah- Yeah ... 7% seems high. But again, let's- But again, and to your point, when you're- Just go with Claude.

Yeah. But- Go on ... to your point, when you're looking at, we want this to be really a long-term estimate. We haven't looked at the actual model yet.

Maybe, if you're doing this for real, you'd probably, and you know Lululemon was going through a growth period, you would build in some explicit growth CapEx assumptions, and then for the long tail, for the long-term growth rate period, you'd want to have some kind of normalized level of maintenance CapEx.

You're not going to be growing- Yeah ... forever.

Okay. Terminal growth rate, 2.5%.

Terminal year, 2030. All right. So we're building a, let's call it five-ish year DCF, knowing what date we're going to start from here.

Okay. So far, it doesn't sound totally insane.

All right. Let's take a look at what we got.

I don't like that the columns here are so wide.

Hmm.

Let's just make it a bit-Zoom in a little bit so we can see it better.

Okay, so we've got what I assume- Look at those Excel skills, Graham.

See, that's what analysts still need to get help, the ability to resize a column really quickly.

Right. You still need to know how to work your way around here. All right.

So I said we've got consensus estimates, which is what we gave it, the assumption to... Okay. All right, now we're growing at one plus.

I assume this is long-term growth, but we're linking back to our assumptions page instead of building in that 2.5% right into the formula. So go Claude on that.

Nice work for starters here.

Mm-hmm.

Just a... Okay, so we got net income projection, net margin, implied revenue growth.

All right. Simple free cash flow build, net income plus DNA minus CapEx, and then change in working capital, and these are all based on our... Wait, what is this calculation? Implied revenue.

We've got the change in revenue- So working capital- ... times change in- ... forecast is potentially- Okay ... change in revenue. Okay It's kind of a weird way to- Yeah ... weird way to actually calculate it, but it doesn't not work. Does not make sense.

And then we've got- So it's saying that free cash- ... negative next year less this year instead of this year less next year, but fair enough.

So it does say free cash flow starting with net income, which worries me.

It should be starting with NOPAT, so operating profit after tax. So- Yeah ... I'm a bit nervous.

Well, we're not at- So I would want to check that ... our DCF because I feel like on the Inputs tab, we had some operating margin assumptions. So let's see what it actually did on DCF.

I guess this is just literally just net income and free cash flow to 2030. So let's see- Okay ... what we've actually done. Summary. Okay.

So I assume this fiscal year 2025 is actually January 2026.

I don't know, right? But we can kind of make that assumption- Yeah ... I think.

Yep.

Right. So I guess in that case, we're probably close enough.

Yeah.

Right. It's not crazy. But what I was getting at is what I'd like to see really is a bit more sophistication built in, in terms of, okay, here's our valuation date, here's our fiscal year ends. Let's build out our time periods and our discount factors based on some inputs. We've got a pretty simplistic one through five-year period here. And again- Yes ... not in blue. I'm going to change that.

Yeah. So definitely- Bad job, Claude ... in research, we used to do what we refer to as daily discounting, where you basically say, well, today we're at a certain date in May. We've got effectively just over six months of the year left, or seven months of the year left.

We're going to discount the cash flows for that remaining year to today's date, and not just have it as kind of let's fudge it and assume we're always one year away from the first cash flow, so- Exactly ... a little bit more refinement would've been nice, but hey, we'll- Yeah, but- ... allow for Claude's first attempt.

Yeah. I mean, but the- I'm more concerned about the free cash flow.

Right. I mean, yeah, the big miss here is the free cash flow, right? Yeah.

Because when we think about free cash flow for a DCF, we're talking unlevered free cash flow.

Now, here's the thing. I guess, can you kind of say on the basis that Lululemon has zero debt, can you kind of say they're one and the same? Probably, but in terms of how you'd actually present this and best practices of calculating and showing it, it's not great. I mean, I could read through these Claude's notes here and see if there's any assumption about that.

I mean, I'm scrolling really quick, but I'm not seeing an explicit, "Hey, there's no debt, so I'm not going to calculate NOPAT here." Because usually what we do is we work down to EBIT, tax EBIT, because that's before interest, right? And then really reflect the cost of debt in the way to average cost of capital, not in the actual cash flows.

So we'd start with NOPAT, and then we'd add back DNA, subtract CapEx, and then add or subtract changes in working capital.

So pretty simplistic.

Mm-hmm.

And then let's see, what else? On our terminal value.

So also, usually, and I don't know in the equity research world if you would do this. A lot of times when teaching in the classroom, kind of teach about calculating terminal value both ways, one using long-term growth, the other introducing a market valuation and applying usually an EV to EBITDA multiple to the final year EBITDA, and then discounting that back to present value. We're saying, okay, we've got Gordon growth formula, so terminal free cash flow.

So we're going to grow the year five cash flow at one plus the growth rate, apply the Gordon growth formula. That gets us our terminal value as of year five, and then discount that back to today.

That looks good enough. In our actual DCF, we sum up all of our explicit projection periods, add our terminal value, and then get our enterprise value.

And actually, that's an interesting one because yes, often as analysts, we're quite concerned about the proportion of your EV, your enterprise value, that is represented by the present value of your terminal value.

And I know some analysts use a rule of thumb that it should never be more than 75%.

But ultimately here what you've got is maybe 60% of your enterprise value from your terminal value.

And that's good because if you put too much value in a single number with some very big assumptions, it's always a bit concerning. The idea that maybe you could just build out your cash flow forecast a little bit further and reduce the weighting towards that terminal value.

So that looks quite good.I think my sense when I look at the final number there, it's got to an implied share price of $125 compared to the current share price of $125.

They've done maybe what every analyst starts with, which is trying to work to the answer.

Yeah.

That's not necessarily best practice, but it's a good way to start your DCF, which is what is the market actually pricing in? Yeah.

No, 100%.

I did notice, the revenue growth assumptions were slightly bizarre.

We had swings up and down in terms of revenue growth.

Minus 7%.

On the basis, I don't know what the actual consensus estimates are.

If this is actually just pulling from consensus forecasts and is correct- Yeah ... then maybe that's right. It's only really applied a growth rate assumption to this 2030 forecast year.

So I haven't read the equity research for Lululemon.

I don't know what people are expecting.

To your point, if they're going through a kind of big reorganization period, for lack of a better word, I don't really know, right? You're spending CapEx on new stores.

That's not going to reflect in your earnings necessarily.

But is there other stuff going on at Lululemon that's driving next year's earnings forecast down? I'm just looking at this final table here.

It's got this summary. Now, I've noticed this before in some Claude Excel output. Usually, I would just have a data table here, kind of sensitivity analysis.

Yeah.

Just looking at this formula here, I don't even know how to audit this easily or quickly to see if this is remotely right. How do you really work through that fundamentally? You could take a few minutes and do it, sure.

But this is one of the things that I do think is a bit dangerous about some of these tools is it doesn't always take the simplest approach, and usually the simplest approach is the best one.

Mm-hmm.

I tell people in Excel all the time, there are a bunch of different ways to get to the same answer and use the one that is most comfortable for you, the easiest, the simplest. But generally, it's what is the most straightforward, simple way to get there, and that's kind of the route you should take.

This, I'm not even going to try.

I'm going to assume that it's done its job right because our growth rate is going up, so our share price is going up.

Our discount rate increases, our share price is going down.

So I'm like, okay, directionally correct. Do I know if this is 100% correct? Not at all.

Right.

No real firm confidence here.

But I do give it some brownie points for actually building sensitivity analysis, because that, for me- Yeah ... is totally essential in a DCF. We've got a load of assumptions, and the fact that we then sensitize those, the really significant ones, those long-term growth and the cost of capital, that you sensitize around that is absolutely critical.

Exactly, because they're- There's a risk ... the only two real inputs that drive all that.

Obviously, you have all the inputs that go into your projection model.

If you're an analyst, you're doing a lot more than just this high-level analysis here, right? But in terms of the actual DCF drivers, it's really what's your long-term growth rate and what's your discount rate, and those two things really drive your output. So yeah, that sensitivity, I agree, is kind of the crucial one.

So what do we give Claude here? We give it a, I'm going to say a B-minus.

Yeah. I think I would give it a C, to be honest.

C, yeah. Okay.

It's lost my confidence that it hasn't calculated free cash flows in the way that I would expect.

Yeah.

Definitely also the growth profile for me is very concerning because for me, those explicit forecast cash flows should be what underpin your valuation. You know what you're saying about long-term growth being a really important input. Well, it kind of is- Mm ... but it also isn't in the sense that it should be neutral.

It should be that actually all the, if you have any upside or downside to the current share price, it's all baked into that first five or 10 years of cash flows because that's where you've got the most- Yeah ... visibility.

So for me, there's a few things here which have lost my confidence slightly. But like any analyst, I'd take what it's done, and I would improve it.

Yeah.

I would then overlay it with my own judgment.

Yep.

And then once I'm happy, that's when I would pass it on for review or share it with a client. So- 100%. And they're going to say- ... it's a starting point ... "No, 2.5%, no, it should be four." I don't know.

Yeah. Great.

All right.

So I think we've shown that it can make a good start.

It's definitely not ready to replace analysts yet, and probably, in a sense, I think that we'll never get to a point where AI completely replaces analysts because you're always going to need that overlay at the end.

And the ability to talk to clients about it.

Yeah. Well, I'll take the contrary view a little bit and say...

Well, it depends, I guess, what we say when we think AI is going to replace analysts. What I do think we're getting to the point of is replacing, say, the analyst job that will do this as the kind of starting piece of work. Also, so maybe it's going to replace the analyst but not the associate, where you have to take a look at something and take a look at the themes, decide if it makes sense, and decide what to do about it.

Mm.

What I do think we should do, just as an interesting exercise, is because these tools are obviously improving so quickly, is kind of pick a time period and say, all right, let's come back in six months, go back to our old prompts, say give it the same prompt, and see what it comes up with then. And I do think, I'll make a bet about this one. I do think we'll get to a point at some point in the next probably few years where that same prompt will get you just an incredibly well-thought-out DCF analysis that's just really ready for prime time and ready for the next round of review.

Great. Well, I think we've definitely put Claude to the test there, and I hope you all enjoyed listening and watching our deep dive into DCF, but also putting AI to the test for DCF.

Yeah, indeed. We'll see everyone same time here next week.

Yeah, same time. A new deal maybe, and some fresh insights. Take care.