formula to calculate dollar value of discounts on revenue

gsullivan75

New Member
Joined
Sep 10, 2009
Messages
1
Hi all, I am struggling with working out what should be a simple formula in the yellow cell in the pic below:

To try and explain things:
I know the dollar profit I make is $40
I know that this represents a 40% margin on revenue which has had X value of discounts applied to it (thus the 40% is referred to as 'discounted margin')
I know that I can calculate the discounted revenue that drives this 40% by using the formula $40 / 40% = $100
I know that there was 10%pp (percentage points) of discounts applied
Thus I know that the 'undiscounted margin' is 50%

So now what I now need is a formula that will give me the dollar value of the discounts from either the known 50% Undiscounted GP% or the -10%pp of discounts that will be applied.

What I do know from just playing with numbers is that the Undiscounted Rev needs to be $120 and the discounts thus $20 but I am going slowing mad trying to work this simple formula!!!

All help gratefully received!!
Regards
Gerry

1624608234671.png
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,250
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Forum!

You've had no answers yet, which suggests that people don't understand the question.

The things you say you "know" are not consistent. If you know:
a) you made $40 profit, and
b) profit was 40% of discounted price

then I agree with you, discounted price must have been $40/40% = $100.

If that discounted price was based on a 10% discount on the original price, then the original price must have been $100/(1 - 10%) = $111.11.

The 40% applies to $100, and the $10% to $111.11, so you can't add 40% + 10% to get 50%.

And it's not clear why the answer needs to be $120?
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,354
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
I agree the 'known' numbers do not jive.

discount as a percentage 10, discount as dollars 20 would mean a price of $200 was needed.
 

Saba Sabaratnam

Active Member
Joined
May 26, 2018
Messages
397
Office Version
  1. 365
  2. 2010
Your gross profit is $50 per $100 revenue and therefore expense is $50 per $100 revenue excluding 10% discount.

In your example, you made $40 profit after 10% discount and therefore Undisc rev must have been $100 and dic rev $90 ($100 - $10) shown below

1624748850437.png


$120 revenue will generate $48 profit not $40 as shown below.

1624749034791.png


Kind regards

Saba
 

Forum statistics

Threads
1,141,707
Messages
5,707,979
Members
421,539
Latest member
zuniBM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top