Revenue Projection Model: The Best Guide

The last time when I shared the super-actionable post, How to Analyze the Financial Health of a Company in 6 Easy Steps, I got emails from readers asking how to build a revenue model.

So, I decided to write not ONE but FOUR approaches to the revenue projection model and take a real case in the retail industry.

Revenue Projection Model in Retail Industry

How The retail business is one of the largest and most diversified sectors covering a very broad range of products from clothing and footwear to appliances/electronics and home-related accessories.

In this article, we will walk through the different terminologies that are used in the financial statements of a retail company, which will help you understand the business performance of the company.

We will then take a case example of a retailer and understand its revenue model to forecast the revenue for future periods.

Terminologies used in Retail companies:

1. EOP Number of Stores: This includes the total number of stores (both company and franchisee-operated). It is a quick indication of the magnitude of the company’s operations.
2. Average Number of Stores: This indicates the average of the number of stores in operation over the course of the given period.
3. Net New Store Opened: This indicates the number of net new stores (store opened – store closed) opened in a period.
4. EOP Selling Area (unit: Square Footage): This refers to the total area (in square footage) at the end of the given period (excludes the square footage used to house inventory).
5. Average Selling Area: This indicates the average square footage area over the course of the given period.
6. Sales per Average Store: This measures the number of sales generated per average store for the given period. It helps determine the productivity of the store.
7. Sales per Average Area: It is calculated by dividing a company’s sales by the average selling area of its stores.
8. Total Sales: This measures total sales made by selling products and services to consumers from all stores in operation.
9. Comparable Store Sales: This is sales from stores that have been in business for more than one year. It is also termed as Same Store Sales or Comp Sales or Comps in the retail industry.
10. New Store Sales: This measures the sales generated by stores that have been opened during the last year.
11. New Store Productivity: This measures the sales generation efficiency of new stores. It is calculated as New Store Sales divided by the increase in Area of New Stores added during the last one year.

To explain the Comparable Store Sales, New Store Sales, and New Store Productivity, let us take an example of a retail firm which had 100 stores at the end of 2013 and opened 10 new stores in 2014.

So, the company had 110 stores at the end of 2014.

Now, let us assume that the total sales of the company were $1000 million in 2013 and $1200 million in 2014.

So the company’s sales increased by $200 million in 2014.

Now, suppose the company generated $160 million (or 16%) from old 100 stores and $40 (or 4%) million from new 10 stores.

So, in the above example, the Comparable Store Sales is $160 million and Comparable Store Sales growth is 16%– generated from stores that have been in business for more than one year.

Remember, that the 10 new stores were opened in 2014 and have not been in business for more than one year.

New Store Sales is $40 million and New Store Sales growth is 4% –generated from stores that were opened in the last one year.

Now, let us assume that the selling area of stores increased by 5% from the addition of 10 new stores.

So, the New Store Productivity is 80%(= 4%/5%), which is calculated as New Store Sales in percentage / Increase in Area of New Stores in percentage).

Now that you have become familiar with the terminologies used in the financial statement of the retail companies, we will discuss four approaches that are generally used to forecast the Revenues of companies in the retail sector.

We will take the case example of ULTA Beauty (NASDAQ: ULTA) to explain the four approaches.

ULTA is the largest beauty retailer in the United States and the premier beauty destination for cosmetics, fragrance, skin, hair care products, and salon services.

In the case example, I have taken the historical numbers from the Annual Reports of the company.

You can download the reports from www.ulta.com -> Investor Relations -> Financial Performance.

Forecasting Methods

1. Revenue Growth

This is the most basic and simple approach to forecast the revenue of a company. In this approach, we make the assumption on:

• Revenues growth: Growth of Revenues for forecasted periods is assumed based on the historical trend and future guidance (qualitative or quantitative) provided by the management of the company.
• In the example, I have made an assumption on Revenue growth (y-o-y) for the forecasted period based on average revenue growth of the last four years (after the recessionary period).

Steps for Revenue Model (please refer to Figure 1):

1. Calculate year-over-year (y-o-y) growth rates of Revenues for historical periods
2. Make assumptions for revenue growth for the forecasted period based on historical trend and management guidance
3. Calculate Revenues for the forecasted periods from assumed revenue growth

Figure 1: Approach 1 – Revenue Growth

2. Sales per Average Store

In this approach, we make the assumption on:

• Numbers of new stores: Number of new stores for forecasted periods is assumed based on management guidance/indication to open new stores in future periods.
• In the example, I have made an assumption on the Number of the net new stores opened in future years based on historical trends and after reading management guidance.

Management guidance for “Number of Net New Stores” is given on Page-4 of Annual Report (refer to Figure 2).

You can refer to other company filings such as quarterly reports and investor presentations to seek management guidance on other items.

Figure 2: Snapshot of Page-4 of 2013Annual Report showing Management Guidance on Number of Stores for future year

• Sales per Average Store: Sales per average store for forecasted periods is assumed based on the historical trend (constant).
• In the example, I have made the assumption on sales per average store for forecasted periods based on the average Sales per Average Store of the last four years.

Steps for Revenue Model (please refer to Figure 3):

1. Calculate Average Stores and Sales per Average Store for historical periods
2. Make assumption on the number of New Stores for forecasted periods based on historical trend and management guidance
3. Calculate End of Period and Average Stores for forecasted periods from assumed New Stores
4. Make assumption on Sales per Average Store for forecasted periods based on the historical trend (keep constant)
5. Calculate Revenue for forecasted period from assumed Sales per Average Store and calculated Average Stores

Figure 3: Approach 2 – Sales per Average Store

3. Sales per Average Area (Square Footage)

In this approach, we make the assumption on:

• Numbers of new stores: Number of new stores for forecasted periods is assumed based on management guidance/indication to open new stores in future periods.
• In the example, I have made an assumption on the Number of the net new stores opened in future years based on historical trends and after reading management guidance.
• Average Area per store: The average area per store for forecasted periods is assumed based on the historical trends (constant).
• In the example, I have made an assumption on Average Area per Store for forecasted periods based on average of Average Area per Store for the last four years.
• Sales per Average Area: Sales per average area for forecasted periods is assumed based on the historical trend (constant).
• In the example, I have made the assumption on Sales per Average Area for forecasted periods based on average of Sales per Average Area of the last four years.

Steps for Revenue Model (please refer to Figure 4):

1. Calculate Average Area for historical periods
2. Calculate Average Area per store and Sales per Average Area for historical periods
3. Make assumption on Number of new stores for the forecasted period based on historical trend and management guidance
4. Calculate End of Period Stores for forecasted periods from assumed New Stores
5. Make assumption on Average Area per store for forecasted periods based on the historical trend (assume constant)
6. Calculate Average Area for forecasted periods from assumed Average Area per store and calculated End of Period Stores
7. Make assumption on Sales per Average Area for forecasted periods based on the historical trend (assume constant)
8. Calculate Revenue for forecasted period from assumed Sales per Average Area and calculated Average Area

Figure 4: Approach 3 – Sales per Average Area

4. Comparable Store Sales growth and New Store productivity

The benefit of this approach is that the revenue is broken into its subparts: Comp Store Sales and New Store Sales.

The Comparable Store Sales, which is the majority portion (80-90%) of the total revenues, is forecasted on the basis of how the sales will grow if there were no new store addition in the company business.

This growth would generally be in line with historical comparable store sales growth and economic growth.

The New Store Sales is forecasted on the basis of how much productivity a new store generates in its first year of opening.

The productivity for the forecasted period is assumed to be in line with the historical trend/constant for future periods.

Please note that the sales of a store are counted in the comparable store sales in its first year of operation.

In this approach, we make the assumption on:

• Numbers of new stores: Number of new stores for forecasted periods is assumed based on management guidance/indication to open new stores in future periods.
• In the example, I have made an assumption on the Number of the net new stores opened in future years based on historical trends and after reading management guidance.
• Average Area per store: The average area per store for forecasted periods is assumed based on the historical trends (constant).
• In the example, I have made an assumption on Average Area per Store for forecasted periods based on average of Average Area per Store for the last four years.
• Comparable Store Sales growth: Comparable Store Sale growth for forecasted periods is assumed based on historical trend and management guidance/economic scenario for the future.
• In the example, I have made the assumption on Comparable Store Sales growth for forecasted periods based on average of Comparable Store Sales growth of the last four years.
• New Store Productivity: New Store productivity for forecasted periods is assumed based on historical trends (constant).
• In the example, I have made an assumption on New Store Productivity for forecasted periods based on the average of New Store Productivity of the last four years.

Steps for Revenue Model (please refer to Figure 5):

1. Calculate New Store sales growth from Total sales growth and ComparableStore Sales growth (Total sales growth – Comparable store sales growth) for historical periods
2. Calculate Average Area and its y-o-y growth from the EOP Area
3. Calculate New Store productivity from New Store sales growth and Average Area growth (New Store sales growth/Average Area growth)
4. Make assumption on Number of new stores to be opened based on historical trend and management guidance
5. Calculate End of Period and Average Stores for forecasted periods from assumed New Stores
6. Make assumption on Average Area per store for forecasted periods based on the historical trend (assume constant)
7. Calculate Average Area and its y-o-y growth for forecasted periods from assumed Average Area per store and calculated End of Period Stores
8. Make assumption on Comparable Store Sales growth for future periods based on historical trend and management guidance and industry growth
9. Make assumption on New Store productivity for the future period based on the historical trend (assume constant)
10. Calculate New Store sales growth for forecasted periods from assumed New Store productivity and calculated Average area growth (New Store productivity X Average area growth)
11. Calculate Total Sales growth for forecasted periods from assumed ComparableStore Sales growth and calculated New Store Sales growth
12. Calculate Revenues from calculated Total sales growth rate

Figure 5: Approach 4 – Comparable Store Sales growth and New Store productivity

5. Putting It All together

Now It’s Your Turn

Now it’s time to actually put these powerful approaches into practice.

Learn more about financial modeling jobs.

To make the process easy for you, I made the comparison above that outlines the EXACT approach you need to select.

Do your revenue projection and share your findings in the comments below:

• Download BIWS Course sample videos here.
• Read Students’ Testimonials here.