Automatic Selling price calculator

[nic140274]

Hello,

could someone help me with a formula to calculate the selling price of an item given some specific guidelines?

Basically I sell products on a website which I buy from a supplier,

The website where I sell charges me 1 euro per sale plus 15% of the selling price I choose.

The constant is that I like to have a 6% profit in my pocket for each sale and this value needs to have its own cell so that if I like one day to up the margin I can just change this number.

Ultimatelly I should just input the price I buy the item for and the formula should give me the selling price of the item.

Just as a check, if we have any accountants, is my profit % formula correct?

Many Thanks

Regards

Nic

[#2]

@nic140274 Have added the formula you need. You can now change the variables in the shaded cells in rows 4 and 5, and the selling price will be calculated for you.

The formula (in words) is as follows:

((Distributor Price + Fixed Website Fee) X (1+ Desired Margin %) ) / (1 - Website Commission % - (Website Commission % X Desired Margin %)

I suspect that a mathematician can make it nicer and remove some brackets, but that's not me right now.

[#3]

@Riny_van_Eekelen 

Hi, looks like it works, I am going to play with it today and do a few more tests, but thank you so much.

When I was trying my way I kept getting some type of loop error.

What is the info I see further down the file?

0.841S = PPx(1+pm)

S = ((PP+1)x(1+pm))/0.841

[#4]

@nic140274 Ooops! You may disregard the bits at the bottom. That was me trying to figure out the math needed to write the formula in I4. Forgot to delete it before uploading.

[#5]

@Riny_van_Eekelen 

I think that I may or may not made a mistake. When I provided the info I provided everything without tax, but when I tried to apply this formula to my real world I realized that the selling platform applies the 15% to the selling price inclusive of TAX.

When I buy my products I buy them with 21% tax, which I then get back fully every 3 months when I submit my taxes. When I sell a product the price has to be indicated including taxes, and that is where the 15% is applied. Out of the 15% + the fixed 1 Euro I can claim back 21%

So now if I get the selling price in the file you shared, I cannot just add 21% taxes because then the 15% fee should also increase which changes again the selling price and I find myself in the loop again.

How can I get the same logic you made but including buying taxes and selling taxes plus the 21% taxes I get back from the 2 fees?

I am not sure I have been really clear, I have been at this for weeks now :(

[#6]

@nic140274 Clear! The tax you pay upon purchasing is irrelevant. It's not a cost (you pay and you get it back later). Tax charged to your customers is irrelevant also. You probably pay it at the end of the month and hopefully collect it from your customers later. So, these taxes only affect your cash-flow, not profits. The only impact the sales tax has it that the 15% platform fee (which is a real cost) gets higher. Have now worked this into the formula in J4. In cell B4, you enter the purchase price excluding tax. Cell K4 now has the selling price including tax. Profit = Selling price (excl. tax) minus total cost. Profit % = profit / total cost.

[#7]

@Riny_van_Eekelen 

I don't understand the formula behind L4 that uses a 25% revenue,

also, is the fact that I can claim 21% back from the 1 Euro plus 15% selling price important or required?

Thanks

Nic

[#8]

@Riny_van_Eekelen 

Hello Sir,

Can you please help me with the below detailed issue; Can we have a formula for this.

Distributor Product Price is normally printed on the boxes and the MRP is inclusive of taxes. 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. Finally, the tax paid to the Government by the agent/re-seller is after claiming input credit, that is, the tax paid to the distributor by the agent /re-seller will be deducted from the tax received from customer and the balance will be remitted to the Government by the agent /re-seller.

Example:

MRP on the box is – Rs. 1000

Dealer Price Rs. 800

Re-seller sold at: Rs. 900 after giving discount of Rs. 100 on MRP

The unseen breakup: product cost Rs. 847.457 + Tax @ 18% Rs = 152.542 (Roundup Rs. 1000)

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

                                      Selling price Rs. Rs. 762.711 + Tax @ 185 = 137.288 ( (roundup Rs. 900)

Tax Payable to Government by re-seller:

                                 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

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

Sir, how can we have a formula for the above, So that we can decide on what percentage we can offer to customers and then how much profit will remain.

[#9]

hello Ronald1969,
can you please create your own post, this one is now dealing with my issue, I do not like to mix things.

[#10 · Accepted solution]

@nic140274 Not sure I follow. If you refer to the 25% in cell K4, that is not a revenue. I just entered the tax% there that you charge to the customer. PErhaps I should have removed your column header. Then I don't have to type "25%" in every formula where it is used. Just a habit.

The tax that you pay on you purchases are not costs and the tax that you charge to your customers is not revenue. I doubt that the 1 euro and the 15% are inclusive of taxes, but if you are certain that this is really the case, enter:

=1/1.21

 in C4, and

=15%/1.21

in D5. 

Have attached the schedule with these formulae.

[#11]

@Riny_van_Eekelen 

I think this is it, the 25% confused me, because we charge 21% tax to customers here in the Netherlands,

so I manually changed it to 21%. I hope I did not missundertood the use for it and mess the whole logic.

For now thanks so much and and I will keep working on it.

Regards

Nic

[#12]

Mijn fout. Weet niet waarom ik 25% gebruikte. Prettig weekend.

[#13]

@Riny_van_Eekelen 

OK I spent all day on this yesterday, I did lots of tests and matched them to manual calculations and looks really good, I am very happy and I consider this now resolved.

Sorry while I live here in the Netherlands my Dutch is still really bad :)

Bedankt voor al je harde werk om me te helpen

Regards

Nic from Huizen