Calculate GP based on %mark up and discount

Topcat2311

New Member
Joined
Feb 18, 2016
Messages
3
Hi
This has annoying me for days hence the post - i'm almost certain the calculation can be done, but it's eluding me...

Basically I have a price list which gives me the % mark up/discount from trade on a range of products (so no fixed cost "price"). I need to create a formula in the "required discount" column that reacts to a value being placed in the "GP" column (in other words if I want to make 20% on an item it would calculate the discount from trade)

Example -

Range A has a discount from trade of 35%

Item 1 is trade £250
Cost £162.50

Item 2 is trade £350
Cost £227.50

Now if you wanted to sell both of these items and make 20% margin, you’d divide cost by 0.80

Item 1 - £203.12
Item 2 - £284.38

This makes the percentage discount from trade 18.75% to = 20% margin - but try as I might I can't figure out the formula to calculate this (and thus apply to other "ranges".

Can anyone out there put me out of my misery?

TC
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Welcome to MrExcel.

Does this help you?


Excel 2010
ABCD
1TradeCostGP%Discount
2250.00162.5020%18.75%
3350.00227.5020%18.75%
Sheet5
Cell Formulas
RangeFormula
D2=1-((B2/(1-C2)/A2))
 
Upvote 0
ABCDE
1NameTradeDiscountMargin%discFtrade
2Item 175035%12%26.14%
3Item 285035%14%24.42%
4Item 395035%16%22.62%
5Item 4105035%18%20.73%
6Item 5115035%20%18.75%

<colgroup><col><col span="2"><col><col span="2"></colgroup><tbody>
</tbody>



Cell E2 reads: =(B2-((B2*(1-C2))/(1-D2)))/B2
 
Upvote 0
Welcome to MrExcel.

Does this help you?

Excel 2010
ABCD
1TradeCostGP%Discount
2250.00162.5020%18.75%
3350.00227.5020%18.75%

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet5

Worksheet Formulas
CellFormula
D2=1-((B2/(1-C2)/A2))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Hi
Yes and no - in that for the formula i've been asked to create A2 & B2 have no value. In other words, the only data i've been given is the discount from trade for the range?

So for example column A would read "mark up to trade" (in this case 35%) and then i'm left with trying to calculate either C2 or D2.

Does that make sense?!
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

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
Back
Top