Modeling retailers?

beta41's picture
Rank: Chimp | 2

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

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.

    • 1
Nov 30, 2012
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
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

Nov 30, 2012

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

Dec 3, 2012

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).

Nov 12, 2014

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.

Nov 12, 2014

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.

Nov 12, 2014

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

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.

Nov 12, 2014

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

Nov 12, 2014

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?

Nov 12, 2014

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

Nov 12, 2014

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

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

Nov 12, 2014

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

Nov 12, 2014

This isn't too difficult. Have you considered thinking it through and building it yourself? You might actually learn something that way.

Nov 12, 2014
Comment