Hey guys, I have a question on modeling retailers.

Say you're looking at a company that is expected to generate a 5% same store sales number next year.

Would Year T+1 Revenue = [(Stores open at T - Expected Closures)*(Sales/Store)*(1 + Expected Same Store Sales change)] + [Store Closures * Partial Year Sales/Store] + [Store openings * Partial Year Sales/Store]?

Thanks.

Most retailers give guidance re: projected store openings. With that you should be able to take an average of beginning and ending stores. Then multiply avg stores time revenue per store. Historically, you would have that number, so simply apply the same store sales growth rate.

Take this simple example. 100 stores currently open and mgmt guided to 110 stores at the end of the year. Avg stores for the year is 105. You know last year that 100 stores produced \$1,000 in revenue, thus \$10 per store. Take your same store sales assumption to the \$10 per store, so if we estimate 10% growth, thats \$11 per store. Now we take the \$11 x 105 stores = \$1,155 in revenue for the year.

Not going to double check that math, but I think it makes sense.

I think what you are trying to do is too complicated. Keep it simple, and beyond that you probably dont have the information to do so.

FYI that is how most of it is done in the retail space; i spent a year as a specialty retail analyst.

iRX:
Most retailers give guidance re: projected store openings. With that you should be able to take an average of beginning and ending stores. Then multiply avg stores time revenue per store. Historically, you would have that number, so simply apply the same store sales growth rate.

Take this simple example. 100 stores currently open and mgmt guided to 110 stores at the end of the year. Avg stores for the year is 105. You know last year that 100 stores produced \$1,000 in revenue, thus \$10 per store. Take your same store sales assumption to the \$10 per store, so if we estimate 10% growth, thats \$11 per store. Now we take the \$11 x 105 stores = \$1,155 in revenue for the year.

Not going to double check that math, but I think it makes sense.

I think what you are trying to do is too complicated. Keep it simple, and beyond that you probably dont have the information to do so.

FYI that is how most of it is done in the retail space; i spent a year as a specialty retail analyst.

Thats right if: i) all stores have the same size; ii) same profitability (revenues/sqm)

Monkey Junior Banker:
iRX:
Most retailers give guidance re: projected store openings. With that you should be able to take an average of beginning and ending stores. Then multiply avg stores time revenue per store. Historically, you would have that number, so simply apply the same store sales growth rate.

Take this simple example. 100 stores currently open and mgmt guided to 110 stores at the end of the year. Avg stores for the year is 105. You know last year that 100 stores produced \$1,000 in revenue, thus \$10 per store. Take your same store sales assumption to the \$10 per store, so if we estimate 10% growth, thats \$11 per store. Now we take the \$11 x 105 stores = \$1,155 in revenue for the year.

Not going to double check that math, but I think it makes sense.

I think what you are trying to do is too complicated. Keep it simple, and beyond that you probably dont have the information to do so.

FYI that is how most of it is done in the retail space; i spent a year as a specialty retail analyst.

Thats right if: i) all stores have the same size; ii) same profitability (revenues/sqm)

Correct. That's usually a safe assumption, given that the historicals are based on hundreds of stores, if not more. But if trending toward larger stores or suburban mall locations vs. city-center open-air locations, obv that would need to be taken into consideration. Good comment, braahhhhh

its all about LFL growth and EBITDAR in retail, but the formula looks good at first sight

For new stores, we run a store opening schedule and forecast the sales based off of the gross sales/GFA average of the region (city or province).

- V

Hi beta41,
I am currently working on modeling retailer as well. Came across your post. Do you mind sharing a excel model, with comp sales driver included to forecast the sales. it will be very much appreciated.

Best Response

Retail Models (Originally Posted: 02/18/2015)

Hi guys,

I've seen people ask about models pretty frequently in this forum and thought I'd help out. I have some models that I've built on my site that you can grab at www.treetis.com. Right now I only have a BBBY model on there, and I've had to take down the other models for now to tweak them a bit but they'll be going back up hopefully within the next week or two. They're catered a little bit more to casual investors, but there is an advanced tab that will give you a sense of what the models might look like at a bank.

You can check out some of my blog posts as well to see how I go about analyzing some companies. They're written more for a casual audience, but thought they might be helpful anyway. Note that the BBBY model doesn't forecast every line item on CF and BS -- every team I've been on doesn't really care to do that for retail. My AMZN model will have each line item forecasted out, for those who would like to find out more about that.

Just some background -- spent 5 years in equity research at two different banks covering retail. Recently left the industry but thought I'd stay in touch by keeping a blog and providing some models for people.

• 3

Thank you so much! I'm still about halfway through college, but I've been trying to get familiar with modeling in my spare time.

This looks great! Excuse me if this is a dumb question as I'm in undergrad and pretty new at this, but I am a bit confused about how you derived your target price (BBBY) via EV/EBITDA.

I've always learned that to get enterprise value, you have to sum up all the discounted cash flows along with the terminal value * exit multiple. But it looks like you just took terminal ebitda and multiplied it by your EV/EBITDA multiple, then that was it. You had that EV, less net debt, and got a target price. Isn't this missing the other years? Or is it just because you're literally only forecasting out one year?

Marty - there's a couple different valuation methods. One method is multiples, which is where you pick a key financial metric in an out year and apply an appropriate multiple to it. So you could use a PE multiple on EPS, a EV/EBITDA multiple on EBITDA, or a EV/Sales multiple on your sales. The latter two calculate an enterprise value, but then you have to back out the equity value from that enterprise value. That's what I did, which is why I took the calculated EV and subtracted net debt (and then divided by share count).

Another method is a DCF, which is the sum of future cash flows discounted to the present. That method requires forecasting the out years and a terminal value, but it's not used much in equity research because there's too much uncertainty in the future to forecast it out that way. It kind of depends on the industry as well as preference though. It might make more sense in other industries where the future cash flows are fairly predictable.

For a retailer, most banks will forecast out one year and stick a multiple on it, which is supposed to be a catch all for the company's future prospects.

• 1

Very clear. Thanks for the help, and the site looks great.

few questions.

for your BBBY, how did you estimate the 2015E multiples.

Also, IF you were to to a DCF, how far out would you have forecasted the cash flows? would you have discounted them on a quarterly bases or on a yearly?

Really like the style of the blog, very clean and professional.

"After you work on Wall Street it’s a choice, would you rather work at McDonalds or on the sell-side? I would choose McDonalds over the sell-side.” - David Tepper

Thanks guys. I'm amazed at how easy it is to make sites nowadays. If anyone has any other questions, feel free to ask.

FYI I've added an AMZN model on there, if anyone is interested.