Modeling retailers?

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.

Comments (19)

 
Nov 30, 2012 - 4:01pm

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.

 
Nov 30, 2012 - 8:27pm

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)

 
Dec 1, 2012 - 12:23pm

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

 
Dec 3, 2012 - 10:56am

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
 
Best Response
Nov 12, 2014 - 3:42pm

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
 
Nov 12, 2014 - 3:44pm

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?

 
Nov 12, 2014 - 3:45pm

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
 
Nov 12, 2014 - 3:47pm

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
 
Nov 12, 2014 - 3:48pm

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

 
Nov 12, 2014 - 3:49pm

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

 
Nov 12, 2014 - 3:50pm

Thanks man. I'm enjoying your stuff on your website...

 
Nov 12, 2014 - 3:52pm

Equity Model for Retailers with Comp Sales Driver (Originally Posted: 11/08/2014)

Was wondering if anyone could share valuation model with comp sales driver built in. I am aware of the mechanics on how comp sales and store growth effect revenue growth , but need a model to start practicing

Start Discussion

Total Avg Compensation

January 2021 Investment Banking

  • Director/MD (9) $911
  • Vice President (31) $349
  • Associates (141) $232
  • 2nd Year Analyst (88) $152
  • 3rd+ Year Analyst (19) $150
  • Intern/Summer Associate (90) $144
  • 1st Year Analyst (349) $132
  • Intern/Summer Analyst (299) $82

Leaderboard See all

1
LonLonMilk's picture
LonLonMilk
98.5
2
Jamoldo's picture
Jamoldo
98.4
3
Secyh62's picture
Secyh62
98.3
4
CompBanker's picture
CompBanker
97.9
5
redever's picture
redever
97.7
6
frgna's picture
frgna
97.6
7
Edifice's picture
Edifice
97.5
8
NuckFuts's picture
NuckFuts
97.5
9
Addinator's picture
Addinator
97.5
10
bolo up's picture
bolo up
97.5