Need help in creating a fake discount calculator

I'm very new to excel, my boss request me to create a discount calculator. A cost of price is given. We have a fix discount rate of 5% of that cost value. Some new customer want more percent of discount to the item they purchase. To deal with that, a markup price will be quoted then we will make a discount according to what the customer prefer but the final discounted value will be always be the discounted 5% of our cost value. I've problem creating it because as example; the customer request for 15% discount, when I add the cost + 10% then minus it back to 15%, the value I get will no be the same of the fixed reduced 5% value of my cost value. Please have a look on the image and help me with the formula. Thank you.

ac16xpZ.jpg

	A	B	C
1	List Price	$ 1,000.00	B1: Input
2	Discount	5%	B2: Input
3	Net Price	$ 950.00	B3: =B1*(1-B2)
4	Customer wants	20%	B4: Input
5	His list price	$ 1,187.50	B5: =B3/(1-B4)
6	His Net Price	$ 950.00	B6: =B5*(1-B4)

Oh dear, I don't think I want to buy anything from your company.

But, just to do the maths:

discounted price:

Formula:

Please Login or Register  to view this content.

1000 * (1-5%) = 950

inflated price:

Formula:

Please Login or Register  to view this content.

950 * (1 + 5% + 20%) = 1187.50

customer quote:

Formula:

Please Login or Register  to view this content.

1187.50 * (1 - 20%) = 950


But don't the customers see a list price before they ask for a discount? Surely they know that you have inflated the price so that you can give them a bigger discount?



Regards, TMS

You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

Thanks for the rep