excel product selling price prediction based on commission and tax percentage

0
votes

please help me with a formula which can do the following.

supplier gives us product on Transfer Price-TP which is to be paid to them upon products sale. we charge commission and tax on the selling price-SP. here is an example of price breakup.

TP - $100
SP - $120 
Commission - $18 which is 15% on SP
Tax - $2.2 which is 12.36% on Commission

I should be able to add just the transfer price-TP in A2, commission percent in B2 and Tax percent in C2 and the formula must populate a selling price in D2 from which the commission and tax amount is removed and we are left with the TP.

if this can be achieved it will be of great help.

Thanks in advance!

1
excel-formula

1 Answers

0
votes

This is more of mathematics than excel formula, in cell D2 use the following formula to get the SP value =A2/(100%-B2-(B2*C2))

Based on your Commission% and Tax%, I have derived a constant using following equation

TP = SP(1-0.15-0.01854)

So SP = TP/0.83146