What's The Big Deal?
Get the view from the inside. Every week, Graham Smith (ex-Ares) and Deborah Taylor (ex-Barclays) take a look at Wall Street’s headline-grabbing deals.
From mega-mergers and hostile takeovers to complex private credit transactions, they break down the why, the how, and the who behind the numbers.
What's The Big Deal?
Can Claude Replace Investment Bankers? We Graded the Output.
Use Left/Right to seek, Home/End to jump to start or end. Hold shift to jump forward or backward.
How good is AI at building a DCF?
In this episode, Debs and Graham continue their Claude for Excel series, this time prompting the tool to construct a full discounted cash flow valuation for Lululemon from a single instruction.
The goal is to test what AI can and cannot do in real valuation workflows, and what that means for analysts working in equity research, investment banking and M&A.
Graham walks through DCF fundamentals from first principles, covering future cash flow projections, WACC, terminal value and the inputs that genuinely drive valuation outcomes.
He then opens Claude for Excel and gives it a structured prompt — anchored to consensus EPS estimates for stage one, with explicit instructions on modelling best practices including no hardcoded inputs in formulas, standard colour coding, and transparent assumption sourcing.
The audit that follows is instructive on both fronts. Claude handles the structural build well — linking assumptions to formulas, applying the Gordon Growth formula correctly for terminal value, and producing a workable enterprise value output.
But the limitations show up in the details that matter most for senior review: the free cash flow build conflates levered and unlevered measures, time period construction is simplistic rather than properly anchored to fiscal year ends and a valuation date, and some formula constructions are opaque enough that auditing them line by line would take longer than rebuilding the section manually.
The verdict: a B-minus output.
Workable as a first pass, but not yet at the level where it can be submitted without significant human review.
The broader question the episode closes on is whether AI tools like Claude for Excel are positioned to replace the analyst role or to elevate it — with Graham making the case that the analyst job as historically defined is exactly the workflow these tools are now competent at, while the judgement-heavy associate role remains some distance from being automated.
Key Discussion Points:
DCF fundamentals: future cash flows, discount rates, terminal value and the inputs that actually drive valuation outcomes.
Prompting strategy: how to structure a Claude for Excel prompt to anchor projections to consensus estimates and enforce modelling best practices.
Where AI delivers: structural build, formula linking, Gordon Growth application, sensitivity analysis output.
Where AI falls short: free cash flow build, time period construction, opaque formulas that resist quick audit.
Sensitivity analysis: long term growth rate versus WACC as the two real swing factors in any DCF.
AI in finance careers: the analyst role versus the associate role and what realistic automation looks like over the next 12 to 24 months.
WTBD Newsletter:
https://webmail.wallstreetprep.com/whats-the-big-deal
Follow Us On Socials:
LinkedIn: https://www.linkedin.com/company/wall-street-prep/
Instagram: https://www.instagram.com/wallstreetprep/
Resources: https://linktr.ee/wallstreetprep
What is the detail this week?
SPEAKER_00We're gonna progress from some of the stuff we started last week. What's the DTF? Why do we do it? Did we give it a little bit more detailed prompting to try to get the result we want?
SPEAKER_01I don't press AI yet.
SPEAKER_00How do we prompt it and say, okay, here's here's how to build the first day's projection period.
SPEAKER_01My starting point is a DTF is always start with consensus numbers.
SPEAKER_00So what is what is a DTF guest?
SPEAKER_01DCF allows us to calculate what counters worth based on its future expected cash flow discounted to today's money.
SPEAKER_00That's definitely a little bit more tricky, I think, to articulate.
SPEAKER_01Ultimately, 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 gonna use my career in investment banking to bring insights to our discussions from a public market perspective.
SPEAKER_00And I'm Graeme Smith, and I'll use my career in investment banking and private credit to bring the private market perspective here.
SPEAKER_01Excellent. Right, Graeme, as usual, my question to you is what is the big deal this week?
SPEAKER_00Big deal. So we are can we're gonna continue on the same thing we started last week, testing out some explainer, explainer topics, and in particular looking at Cloud Code, or not Cloud Code, but Cloud Excel rather, and see how it does building a DCF this week. We're gonna 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 gonna go through and talk about it and hopefully pepper in a little bit of what's a DCF, why do we do it, uh, and some chat about the company we're gonna take a look at today. So great.
SPEAKER_01Yeah, 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 uh at building us a DCF. Um we will, yeah, 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. Um I think we're gonna use Lululemon, aren't we, as our case study for this. Yeah, yeah.
SPEAKER_00So you I I guess the nice thing about Lululemon is everyone knows it to some extent. It's uh that's that's a nice thing about using like 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?
SPEAKER_01That's correct. We love to use Lululemon because, as you say, uh it's well known. Uh it's well, particularly well known if you're sporty type or you're a big fan of Instagram. Um, 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, we'll be using this over the summer, particularly for our intern training.
SPEAKER_00Sounds 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 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. So all right, can you can you see my screen, Debs?
SPEAKER_01I can see your screen, absolutely. It's looking very blank.
SPEAKER_00Okay, we're gonna we're gonna cross our cross our fingers and hope it works. I I like I loathe this laptop. I really do. It's the worst. Uh but we'll see if it we'll see if it plays balls. Oh. Restart the cloud add-in. Okay, here we go. Build a, let's say, build a DCF for Lululemon. Follow financial modeling best practices, including. I think that the issue I was finding last week, uh, and Debs add to this, add to this list. 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 some pretty pretty crazy stuff that you would you would really get, you wouldn't get fired if you were an analyst for doing, but maybe you know put on a probation list. It was building hard code inputs into a formula, you know, in Excel, so you you really had no idea where stuff was coming. So I'm gonna I'm gonna prompt it not to do that. Is there anything on 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?
SPEAKER_01Um I don't think there was anything to say definitely do not. I mean, I always use the caveat when I'm using a building a prompt to say, let me know if there's anything, any information you need from me. But that's just because I'm quite risk-averse. I don't trust AR yet. And that's just my approach.
SPEAKER_00Yeah. Okay, so I'm gonna say follow financial modeling best practices include 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. Pretty did a pretty good job of that last time, but you know, let's just prompt it. Okay, what information do we want to tell to use? So here's here's where I think these tools are incredibly powerful, but we have to give them some guidance. Right? So we want, presumably, we want we want Claude to go to go pull the latest filings and build our build our DCF based on, you know, based on a picture from 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 you know kind of how how the DCF actually works and why that's important. But ultimately, that's what it's all about, right? So how do we how should we prompt it and say, okay, here's here's how to build the first stage projection period.
SPEAKER_01Yeah. So I I mean 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 whether the answer is in aligned with what the market's actually pricing the company at. So um see if it can find consensus numbers. Um I know for you know, if you're in an investment bank, you obviously have access to you know data systems like Bloomberg and Factset that you can extract that information from and provide to Claude.
SPEAKER_00Exactly. And even if you're not, we all have access to you know Yahoo Finance and Google Finance, you know, whatever, whatever platform you want to use, it will usually give you, especially for and 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 slash cash flow from today. Anything else you want to give it?
SPEAKER_01So 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.
SPEAKER_00I wanna see, I wanna see how it does it. Yeah. I want to say calculate the whack for Lulu based on the cost of debt that you'll pull from where are we gonna get now? Do you think Claude is gonna have access to any kind of any kind of current or rather yield to maturity on any Lululemon debt? Or just want to say, hey, use the cost of debt that's in the 10K.
SPEAKER_01Uh so I would personally never use what's in the 10K because it's a historical number. So I would say do your best to find a current number for the cost of debt.
SPEAKER_00I like it. Yeah, we wanted to reflect current risk prices. To find the current market cost of equity debt and then make assumptions for the cost of equity, but make it explicit in the model how you've arrived at that.
SPEAKER_01Brilliant. All right, let's let's take a look at the Gumbas.
SPEAKER_00On that note, let's see. Yeah, let's looks a little bit simpler than the uh than the merger model we did we did last week, which should be 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. Sure, pre-tax cost of debt. Okay, where where are we pulling here? No, no funded long-term debt outstanding, only a revolver, used corporate triple B equivalent. Okay, I mean that doesn't seem like a like a crazy assumption to make. I mean, it's not it's not relevant here in the sense we're just gonna have 100% equity weight anyway, but but okay. Okay, effective tax rate, after tax cost of debt, again, which is not gonna not gonna make a difference here. Okay. All right, so our our whack is just our cost of equity capital in this case. Now you know, you know Lululemon. Is this right about is this right about the debt?
SPEAKER_01Uh what about the weight of debt? Uh it being zero.
SPEAKER_00About the fact that there's no that there's no revolver. Or sorry, there's no, there's no actual debt apart from a revolver.
SPEAKER_01Yeah, I think that sounds right. I mean, they are um they have a big D2C business, so I think that usually makes them a bit more cash generative. Um, 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 the target leverage in the cost of capital. So that's maybe a bit of a punchy one. Um, I would say a whack of I'm already doing what your your uh old manager used to do. I'm looking at the number going, 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.
SPEAKER_00Yeah. Okay, we've got some margin assumptions, DNA as far as revenue, capex as a percentage of revenue. I mean, just looking at these, I've got a feel now for how it's gonna build out its how it's gonna build out its model. Are you seeing 7%? 7% is high here.
SPEAKER_01That's huge.
SPEAKER_00Changing working capital percentage of revenue. What what is it actually ish? Do you think that's the same?
SPEAKER_01So consumer businesses are usually about four or five percent. They have been going through the store expansion, so it might have gone up to about around six percent. Um, but yeah, seven percent seems high.
SPEAKER_00But again, let's go towards Yeah, but I mean to your point when you're looking at we want this to be really a long-term, a long-term estimate. We haven't looked at the at the actual model yet. Maybe you know, if you're doing this for real, you probably and you know Lululemo is going through a growth period, you would build in some explicit growth capex assumptions. And then for the the long tail, for the the long-term growth rate period, you'd want to have some kind of normalized level of maintenance capex. You're not gonna be growing forever. Okay. Terminal growth rate, two and a half percent, terminal year 2030. All right, so we're building a you know, let's call it five-ish year DCF. No what date we're gonna, we're gonna start from here. Okay, so you know, so far it doesn't sound doesn't sound totally, totally insane. All right, let's take a look at what we got. I don't like that the columns here are so are so wide. Let's just make it a bit zoom in a little bit so we can we can see a bit better. Okay, so we've got to look at those Excel skills, Graham.
SPEAKER_01So that's what I'm still needing to have, the ability to resize a column really quickly.
SPEAKER_00Right. You still, yeah, you still need to you still need to know how to how to get your and work your work your way around here. All right, so I said we've got we've got consensus estimates, which is what we gave it, the the assumption to, okay, all right. Now we're growing at one plus. I assume this is like long-term, long-term growth, but we're linking back to our assumptions page instead of instead of building in that two and a half percent right into the formula. So go claude on that. Nice, nice work for for starters here. Uh okay, so we got net income projection, net margin applied revenue growth. All right, simple free cash flow build, net income plus DNA minus cap bucks, and then changed in working capital. And these are all based on our. Wait, what is what is this calculation? Implied revenue, we've got the change in revenue.
SPEAKER_01So working capital. Times change in working. Okay, okay. Okay.
SPEAKER_00It's a it's kind of a weird way to weird way to actually like calculate it, but it doesn't it doesn't not it doesn't not work, doesn't not make sense. Um, and then we've got you know, negative, negative next year less this year, instead of this year, less next year, but you know, fair enough.
SPEAKER_01So it does say free cash flow starting with net income, which worries me. It should be starting with um no pat. So operating profit after tax. So I'm a bit nervous.
SPEAKER_00Well, we're we're not I guess we're not at our DCF because I feel like on the inputs tab we had some operating margin assumptions. So let's 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, let's see, we've actually done summary. Okay, so I assume this fiscal year 2025 is actually January 2026. I don't know, right? But like we can we can kind of make that assumption, I think. Right. So I guess I guess in that case, in that case, we're probably we're probably close enough. Yeah. Right. It's not 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 our time periods and our discount factors uh based on based on some inputs. We've got a pretty simplistic like one through five year period here. And again, not in blue. I'm gonna change that.
SPEAKER_01Yeah, so definitely in research. In rate research, we used to do what we refer to as daily discounting, where you basically say, well, today we're at you know, certain date in May. Uh we've got, you know, effectively just over six months of the year left or seven months of the year left. We're gonna discount the cash flows for that, you know, for that remaining year to today's date and not just kind of have it as kind of let's fudget and assume we're always one year away from the the first cash flow.
SPEAKER_00So Exactly.
SPEAKER_01A little bit more refinement would have been nice, but hey, we'll we'll you know, allow for Claude's first attempt. Yeah, I mean, but I'm more concerned about the free cash flow.
SPEAKER_00Right. I mean the yeah, the big the big miss here is the free cash flow, right? Because when we when we think about when we think about free cash flow for a DCF, we're talking unlevered free cash flow. Now, uh here's the thing. I guess can you can you kind of say on the basis that Lululemon has zero debt, can you kind of can you kind of say they're one of the same? Probably, but in terms of in terms of how you'd actually present this and best practices of like calculating and showing it, it's kind of not, it's not great. I mean, I can read through I could read through these Claude's notes here and see if there's any if there's any assumption about that. So I'm not, I mean, I'm I'm scrolling really quick, but I'm not I'm not seeing an explicit, hey, there's no debt, so I'm not gonna I'm not gonna calculate no pat here. Because usually what we do is we work down to EBIT, tax EBIT, because that's before that's before interest, right? And then and then really reflect the the cost of debt in the weighted average cost of capital, not in the actual cash flows. So we'd start with we'd start with no pat and then we'd add back DNA, subtract capex, and then add or subtract changes in working capital. So pretty, pretty simplistic. And then let's see what else on our 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 EBITA 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, terminal free cash flow. So we're gonna 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. Then our actual DCF, we sum up all of our explicit protection periods, add our terminal value, and then get our enterprise value.
SPEAKER_01And 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%. Um, but you know, ultimately here what you've got is, you know, not you know, maybe 60% of your um enterprise value from your terminal value. And that's good because if you put if you put too much value in a big, a single number with some very big assumptions, it's always a bit concerning. The idea that, you know, maybe you could just build out your cash flow forecast a little bit further and reduce the weighting towards that terminal value. So that 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.
SPEAKER_00Yeah. Yeah. No, 100%.
SPEAKER_01Um I did notice, I mean, the revenue growth assumptions were slightly bizarre. I mean, we had swings up and down in terms of revenue growth. Uh minus 7%.
SPEAKER_00On the basis this, I don't know what the actual consensus estimates are. I mean, if this is actually just pulling from consensus forecasts and is correct, yeah. Then maybe that's right. I mean, it's only it's only really applied uh growth rate assumption to this 20, this 2030 forecast year. So I don't I don't necessarily have the view. I haven't read the equity research for for Lululemon. I don't know what people are expecting. I mean, to your point, if they're going through a a kind of big reorganization period, lack of a better word. I mean, not I I I don't really know, right? You're spending you're spending capex on new stores, that's not going to reflect in your in your earnings necessarily, but is there other stuff going on at Lulemon uh that's driving that's driving next year's next year's earnings forecast down? I'm just looking at this this final this final table here. It's got this summary. Now this this is I I've noticed this before in some some Claude Excel output. Usually I would just have a a data table here, kind of sensitivity analysis. Like just looking at this, at this formula here, like I don't even know how to audit this easily or quickly to see if this is if this is remotely right. I mean, how do you how do you really work through that fundamentally? I mean, 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. I mean, I 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 what is the most straightforward, simple way to get there? And that's kind of the route you should take. This, I mean, I I'm not even gonna try. I'm I'm gonna assume that it's done, that it's done its job right, because like 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. No, no real firm, firm confidence here.
SPEAKER_01But I do give it some brownie points for actually building sensitivity and assist because that for me is totally essential in a DCF. We've got a load of assumptions, and the fact that we then sensitize those most the really significant ones, that's long-term growth and the cost of capital that you sensitize around that is absolutely critical. Um, exactly. Because there's a risk.
SPEAKER_00They're the only two real, real inputs that drive the travel evaluation. I mean, 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 driver output. So yeah, that sensitivity, I agree, is kind of the crucial one. So what do we what do we give Cloud here? We give it like a uh I'm gonna say like a B minus.
SPEAKER_01Yeah, I I think I would give it a C, to be honest. I'm really uh it's lost my confidence that it hasn't calculated free cash flows in the way that I would expect. Yeah. Um 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. But it kind of is, but it also isn't in the sense that it should be neutral. It should be that actually all the, you know, if you have any upside or downside to the current share price, it's all baked into that first five or ten years of cash flows because that's where you've got the most 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. I would then overlay it with my own judgment. And then once I'm happy, that's when I would pass it on for review or share it with a client. So 100% starting point.
SPEAKER_00No, two and a half percent, no, it's definitely it should be four. I don't know.
SPEAKER_01Great. So I think we've shown that it it can make a good start. It's definitely not ready to uh replace analysts yet. And probably, you know, in a sense, I think that we'll never get to a point where AI completely replaces analysts because you're always gonna need that overlay at the end uh and the ability to talk to clients about it.
SPEAKER_00Yeah, well, I'll I'll take the I'll take the contrary view a little bit and say, well, it depends, I guess, what we say when when we think AI is gonna replace analysts. What I do think we're gonna get to the point of is replacing replacing, say, the analyst job that can that will do this as the as the kind of starting starting piece of work. Also, so maybe it's gonna replace the analyst, but not the associate, where you have to, 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. Um 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 mean, I will like I'll I'll make a 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 a just an incredibly well, well thought out DCF analysis that's just really ready for prime time and ready for the next round of review.
SPEAKER_01Great. Well, I think we've we've we've definitely put Claude to the test there. And I hope you all enjoyed listening and watching our deep our deep dive into DCF, but also putting AI to the test for DCF.
SPEAKER_00Yeah, indeed. We'll see everyone same time here next week.
SPEAKER_01Yeah, same time. A new deal, maybe, and some fresh insights. Take care.