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 =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Welcome to MrExcel.

Does this help you?

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">Trade</td><td style="text-align: right;;">Cost</td><td style="text-align: right;;">GP%</td><td style="text-align: right;;">Discount</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">250.00</td><td style="text-align: right;;">162.50</td><td style="text-align: right;;">20%</td><td style="text-align: right;;">18.75%</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">350.00</td><td style="text-align: right;;">227.50</td><td style="text-align: right;;">20%</td><td style="text-align: right;;">18.75%</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet5</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D2</th><td style="text-align:left">=1-(<font color="Blue">(<font color="Red">B2/(<font color="Green">1-C2</font>)/A2</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

W8253

Board Regular
Joined
Oct 7, 2015
Messages
64
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
 

Topcat2311

New Member
Joined
Feb 18, 2016
Messages
3
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?!
 

Forum statistics

Threads
1,136,790
Messages
5,677,745
Members
419,718
Latest member
ALWP

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