Feb 22 2020 11:15 PM
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.
Feb 22 2020 11:20 PM
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.
Feb 23 2020 12:36 AM
Feb 23 2020 01:02 AM
@Ronald1969 You are most welcome!
Feb 23 2020 04:46 AM
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
Feb 23 2020 05:40 AM
@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.
Feb 23 2020 09:07 AM
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.
Feb 22 2020 11:20 PM
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.