Thursday, September 22, 2016

Some Retail Formulae


Some Retail Formulae Related to 

1. Booking Revenue
2. Net Revenue
3. Number of Options to Buy
4. Buy Quantity
5. Sales Quantity
6. Sales Quantity ( Net)
7. ASP
8. Average Purchase Price
9. Buy Cost
10. Sales to Stock Ratio
11. Intake Margin ( Rs. Margin)
12. RGM
13. GM%
14. Cost of Goods Sold ( COGS)



Tuesday, April 12, 2016

MRP when Margin ( Net of Taxes is Given)

A vendor gives an item to an ecommerce co at Rs. 100. Freight is 1.5% and CST is 2%. What would be the landed price at the warehouse. If the Retail tax is 5.5%, then what would be the MRP if he wants to get a margin of 40% ( Net of Taxes).

The calculation goes like this:

Warehouse Landed Price= Vendor Cost + Freight + CST= 100+1.5+2.0 = 103.5

Let x be the MRP then Sales Realised with 5.5% retails tax as x/(1.055)

Then Margin ( Net of Taxes) = ( Sales Realised - Warehouse Landed Cost)/ MRP

0.4 = ((x/1.055)-103.5)/x

x = 188.91--> MRP to be kept.

For those who need the formula is:

MRP= (Warehouse landed price)/1-(vat%/(1+vat%))-Targtet Margin ( Net of Taxes)

SALES REALIZED ( NET MRP)= MRP- MRP*(vat%/(1+vat%))

Tuesday, March 29, 2016

Notes on Factors Influencing Ecommerce Industry

Macro Indicators

All Values in USD

Indian GDP- 1890 bn
Private Consumption- 1080 bn
Out of Which Retail- 525 bn
   - Rural: 273 bn (52%), Urban: 252 bn (48%)
   - Traditional Retail:479 bn (91%), Organised Retail:46 bn (9%)
   - Merchandise:488 bn (93%), Service 37 bn (7%)
   - Food and Grocery (350 bn), Apparel (46), Jewellery and Watches (40), Consumer Electronics (26), Pharmacy (15), Furnishings and Furniture (19), Footwear (6), F&B Service (11), Beauty Services (2), Health/Fitness Services (3), Others (9)
Present GMV of Etail: USD 7b
Etail ( % of total Retail): 1.2%
E-tail ( % of organised retail): 13%


Across Cities


  • 12% of the total population contributes of 38% of total spends in top 74 cities
  • Delhi and Mumbai account for 9% of total retail spending.
  • Top 22 cities account for 29% fo the total retail spending
  • North India contributes to 23% of the total retail opportunity
  • Karnataka & AP: Contributes $72 billion 

Success of Apparel and Life Style products is limited by

  1. Limited Penetration of brands in tier 2 and 3
  2. Lack of Standardised Products
  3. Discounted Pricing
Business Model Options for E-commerce

  1. Positioning Focus: 
    1. Pure online retail brand
    2. Online MBOs
    3. Aggregators
    4. Brands going online
  2. Inventory Approach
    1. Own
    2. Third Party
  3. Merchandise Format
    1. Horizontal: Has many categories- Flipkart, Infibeam
    2. Vertical- Limited Category - Exhaustive Range- Health Kart
  4. Inventory Approach
    1. Inventory Led
      1. Outright Purchase
      2. Consignment/SOR
      3. Advantages
        1. Visibility of Stock
        2. Higher Fulfilment Rate
        3. Faster Dispatch Time
        4. Better brand Credibility
        5. Customer Confidence
        6. Higher Margins
      4. Disadvantes
        1. Capital Tie-up
        2. Increased Inventory Mark Down Risk
        3. Building Non selling Inventory
    2. Managed Market Place
      1. Drop Ship
      2. Back to Back
      3. Advantages
        1. No Inventory
        2. Higher Quality Control
        3. Low Margins
        4. Favourable government policies
      4. Disadvantages
        1. Lower Delivery cycle
        2. Higher logistics cost
        3. Multiple Listing
        4. System Integration with Vendor required
        5. Lower Margins
    3. Pure Market Place
      1. Any one can post
      2. Advantages
        1. No Inventory/storage and Handling cost
        2. Wider product offering
        3. Favourable Government policies
      3. Disadvantages
        1. Non standard customer service
        2. Multiple listing
        3. Poor Vendor service
        4. System Integration challenges
E-tailing Participants


1. Web Only (98%), Multiple Channels (2%) ( Note in USA Multiple channels is 40%)

E-tailing Impact Categories

  1. High Impact
    1. Small Electronics
    2. Fashion and Life Style
    3. Why
      1. Greater Standardisation
      2. Low Involvement 
      3. Discretionary
  2. Medium Impact
    1. Home Improvement 
    2. White Goods
    3. Health
    4. Jewellery
    5. Why
      1. High Involvement
      2. Higher Investment 
      3. Lower Product Life
  3. Low Impact
    1. Brown Goods
    2. Food
    3. Why
      1. Need Based
      2. Logistics Issues and Capability
Existing Vendor Universe


1. Authorised Distributors
2. Dealers/Subdistributors
3. Small Brands

Key Need Gaps

  1. Customer Trust
  2. Merchandise and Range Building
  3. Expertise Centred around Technology
  4. Price Based  Positioning
  5. Fulfilment
  6. Challenge of Tapping semi Urban and Rural Population. 90% sale of the retailer is coming from top 100 cities.
Source



Friday, February 5, 2016

Calculation of Vendor Cost

The problem can be stated like this

 A vendor wants to supply goods to an ecommerce company on a commission. He wants to keep a certain MRP on the website say 1000. The company wants to get a commission of say flat 25% net of taxes on the MRP. The retail tax on the goods are 5.5%. At what price should the vendor supply the goods to the company. At what price should he invoice it, if the input VAT is 4.5%.

In this case the key to understanding is this. The company wants to get commission of 25% net of taxes. Hence the company wants 25%  of 1000= 250 Rupees on the goods net of taxes.

Now 1000 Rs. is the MRP after the taxes of 5.5% are added, hence the realized MRP before taxes is

(1000x100)/105.5]= 947.8 Rs.

So the price that the vendor should supply the goods will be 947.8-250=697.8 Rs. Now input VAT is 5.5%, hence the VAT on purchase price is 697.8 * 5.5%= 38.38 Rs.

So the price the vendor should invoice it is 697.87+38.38= 736.25

However the company needs to pay only 947.8, as the input VAT is offset by the Retail VAT.

See the excel sheet in this regard here





Thursday, December 31, 2015

How to Buy for Ecommerce for Beginners

This is a small step-by-step guide

Before that let me explain a few concepts:

CATEGORY MANAGEMENT

Essentially it is characterised by the following:

1. It is breaking down the range of products into distinct clusters of related products. 

For example one way to break down fashion range is by menswear, womenswear, kidswear etc.

Then further it can be subdivided into western, ethnic, sports etc.

Which can further subdivided into T-shirts, Denims etc.

The aim is to find those clusters which are meaningful and measurable.

2. The aim is to manage each category as a separate business unit

Here manage means to control the buying, logistics and 4Ps of that category- product, price, place and promotion.

3. The goal is to achieve the right stock level vis-a-vis sales to achieve the desired profitability goals. 

GROSS MARGIN

It is calculated in two ways

1. Gross Margin = Operating Expenses + Net Profit Margin

Obviously the NPM has to be greater than the opportunity cost.

2. Gross Margin= Net Sales- Total Cost of the Merchandise (COGS)

DIFFERENCE BETWEEN SELL-THROUGH RATIO AND SALES/STOCK RATIO

STR is sales/stock for a period whereas S/S is sales/average stock for a particular time.

GMROI

It is calculated in two ways

1. GMROI= GM% x S/S Ratio
2. GMROI= GM/Average Inventory

NROI

NROI = Net Profitability/Average Inventory at Cost

HOW TO BUY FOR ECOMMERCE

For startups the following assumptions are taken, whereas for ongoing concerns, previous numbers give these outcomes:

1. MRP Revenue Target- The MRP Revenue that you would like to get if you give no discounts and no returns.
2. TAX- The output tax on Sales, eg. VAT
3. DISCOUNT %
4.  RETURN %
5. DEPTH of the merchandise in the category mix. Which means number of pieces in the SKU. This will be different for different merchandise category
6. BUYING MARGIN % - BM%-This is the margin that we need to have between the buying and the MRP marked
7. SELL-THROUGH

The aim is to find for each of the subcategory grid

a. How much to buy
b. What price point to buy

Step 1. The first step is to make a buying matrix, which means setting up a sheet with MRP price points range and contribution of the total merchandise to the price range. Which means deciding from where your bulk of the sales is coming and at what price point. At the same time percent contribution of the MRP Revenue for that category is plotted.

Step 2. From 1- tweaking price curve and contribution, you can find the average MRP. Please write to me in case you do not understand how to do that ( Step 1 and Step 2)

Step 3. Find the Quantity Sold by MRP Reve Target/Average

Step 4 Find the Net Quantity sold by factoring in the return %: Qty Sold x ( 1-Return %)

Step 5. Find the Qty Bought by the formula=> Net Qty Sold/Sales through

This gives you the qty bought for that particular subcategory cell.

Step 6  Divide Qty bought by depth will give you number of styles to be bought for that particular price point curve

Step 7: Now find the average Purchase price by the following way:

a. Find the Qty Sold by MRP Rev/Average MRP
b. Calculate MRP Revenue Net Tax by MRP Rev x Tax %
c. Factor in the Buying Margin to get the Purchase Cost= MRP Rev Net Tax ( 1-BM%)
d. Find the Average Purchase price by Purchase Cost/Qty Sold

You can also calculate the intake margin as : MRP Revenue Net Tax x BM%


Tuesday, December 15, 2015

Retail Calculations

Inputs

Input Values

1. Average MRP
2. Sell Through
3. Buy Depth
4. Discount %
5. Return %
6. MRP Revenue

Derived Values


Sales Quantity: MRP Revenue/ Average MRP

Buy Quantity: Sales Quantity x (1-Return%)/Sell through

No of Options to Buy:  Buy Quantity/Buy Depth

Booking Revenue: MRP Revenue ( 1-discount%)

ASP: Average MRP ( 1-discoun%)

Discount Value: discount % x MRP Revenue

Some Observations

1. No. of options vary inversely as Average MRP and Buy Depth for a given sell through, disocunt%, return % and MRP Revenue