SOLVED

Formula for finilazing resale value of product and to know the profit after Tax dues

Brass Contributor

Hello,

Can someone please help me with the below detailed issue; I need a formula for this calculation.

 

I am a re-seller and I purchase from the Distributor. The MRP (Maximum Retail Price)  is  printed on the boxes and the MRP is inclusive of 18% tax. Suppose the MRP is Rs. 10000

 

The Distributor sells it to the re-seller at Dealers Price and not at MRP and the re-seller further sells it to his customer at MRP rate.

 

I pay tax to the Government by claiming input credit, that is, the tax paid to the distributor when I bought the product from him for onward sell.

 

Example:

MRP printed on the on the product is – Rs. 1000

Dealer sells it to me for - Rs. 800

I sell it to my customer with 10% discount on MRP, that is, for Rs. 900

 

The unseen breakup of rates:

a) Product cost Rs. 847.457 + Tax @ 18% Rs = 152.542 (Roundup Rs. 1000)

b) Dealer Price Rs. 677.966 + Tax @18% Rs = 122.033 (Roundup Rs. 800)

c) Sold at Rs. Rs. 762.711 + Tax @ 185 = 137.288 ( (roundup Rs. 900)

 

Tax due to the Government from me:

                                 Tax Charged to customer by re seller Rs. 137.288

                                 Tax already paid through the distributor Rs. 122.033

                                 Tax Due to pay    137-122= Rs. 15

How we arrive at profit :

 

Collected from resale =                                        Rs. 900

Paid to Distributor  =                                           Rs. 800

Balance Tax Due to Government                          Rs.  15

Total Cost to me =                                     (Rs. 800+Rs. 15)

Profit in the above sell is : (9.44%) Rs.85  (Rs.900-Rs. 800-Rs.15=Rs 85)

 

In view of above, I am seeking help to do a formula, which may be like this;

 

I should be able to choose option to give discount on MRP and then I should know how much profit I will earn on the product. or any other format to know my profit while fixing a price for resale.

 

 

 
 
 
Highlighted
 
 

 

6 Replies
best response confirmed by Ronald1969 (Brass Contributor)
Solution

@Ronald1969 Had seen your question pop-up in another unrelated thread yesterday. Thanks for re-posting it in a new one. The attached workbook may have the formulae you are asking for. I kept your example on the side, so that it was easier to compare your request with the outcome of the formulae.

Thanks for your help. In fact, I am feeling very happy to use Excel. Earlier I was not aware that such dynamic personalities like you are always available to help people like me. You have assisted me even earlier. Thank you.

@Riny_van_Eekelen 

 

Hi!

I am Sorry, to intercept again on an identical issue, you have already helped me. However, as I am trying to use it practically, I am facing few hurdles. Here I am trying to figure out if is there is any possibility of making the placement of formula in fewer cells. To seek one common answer, can we manage by utilizing fewer cells? This Issue may not be identical, but very closer to my issue in question.

I have attached an excel sheet, for perusal.

Please check, and, if possible, help me to make the working more dynamic or compact.

Regards,

 

Ronald Pinto

@Ronald1969 Not sure I follow the logic of your price setting. You buy for 1000 plus 18% tax. Then you add 5% for profit to the purchase price including tax. And then again 18% tax to arrive at the selling price. Your profit % will then be 18%+(5% x 1.18) = 23.9%. In numbers, 239 profit on 1000.

 

In my world, taxes on purchases and sales are not part of your cost and revenue and, thus, do not affect profit. In your example, you initially pay tax 180 and you collect tax 223. You end up paying 43 in taxes, but these are not costs for you! Your customer paid the tax, not you. You are just acting as a tax collector for the government.

 

I tried to demonstrate that in your file in column A and B. Compact enough and dynamic. Just change the parameters in B4:B5, and the rest goes automatic.

 

@Riny_van_Eekelen 

 

You are correct, we must collect tax on behalf of the Government from the customer and pay it to the Government. However, the method is slightly different.

 

In India, now its Goods and Service Tax (GST) regime. The GST is charged on sales and service. The procedure is as follows.

 

Manufacture while dispatching the product to the Distributor, at first instance, pays GST to the Government on the product rate charged to distributor. And thereafter collects the same from the Distributor.

 

The Distributor when sells the products to the re-sellers, he sells it at dealer price which will be slightly higher than his purchase rate and collects GST on the total price, for Distributor, his total cost is Original cost plus Tax paid on the product. Now, the Distributor keeps back the tax amount already paid to the manufacturer on each product and remits balance to the Government.

 

The re-seller sells the product to his end customer at a slightly higher rate than his purchase rate and collects Tax on that amount and thereafter he keeps back the tax already paid to the Distributor and remits the balance to the Government.

 

In this way, Government ensures that they get the Tax in full on the total value in three parts.  And in this way the keep a track of product from the manufacture to the end customer. Since the tax is already paid in advance - scope of manipulation is curtailed.

 

In this situation, the tax is paid only on the excess earned by the Distributor and the re-seller, because the manufacture has already paid tax on the original cost sold to the distributor.

 

This procedure in called claiming “Input Tax Credit”.

 

I hope, I have clarified, why we consider Unit price plus tax paid to the distributor as our cost. The law mandates, that we must specify cost of the unit and taxes separately when we raise tax invoice.

1 best response

Accepted Solutions
best response confirmed by Ronald1969 (Brass Contributor)
Solution

@Ronald1969 Had seen your question pop-up in another unrelated thread yesterday. Thanks for re-posting it in a new one. The attached workbook may have the formulae you are asking for. I kept your example on the side, so that it was easier to compare your request with the outcome of the formulae.

View solution in original post