Cost profit Margin Calculator Help

Fixer32

New Member
Joined
Apr 8, 2017
Messages
7
I have made a calculator to calculate selling price from cost but I need help to make it tiered so the things I buy for a smaller price arer calculated higher than the more expensive ones.
I am not a big wiz kid In excel but know some basic stuff this is what i have made so far....

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>
Parts Gross Profit Margin Calculator
Only "Blue" cells can be modified.
Profit Margin=0.2575%
ItemsDescriptionCostSelling Price
Item 1$100.00 $ 133.33
Item 2 $ -
Item 3 $ -
Item 4 $ -
Item 5 $ -
Item 6 $ -
Item 7 $ -
Item 8 $ -
Item 9 $ -
Item 10 $ -
Item 11 $ -
Item 12 $ -
Item 13 $ -
Item 14 $ -
Item 15 $ -
Item 16 $ -
Item 17 $ -
Item 18 $ -
Item 19 $ -
Item 20 $ -

<colgroup><col style="width: 60px"><col width="133"><col width="24"><col width="72"><col width="15"><col width="83"></colgroup><tbody>
</tbody>

Cell E5 Has the formula =1-$D$5 but I need it to change depending on the cost like this.
I want to have this formula run if I put cost into the "blue" field E4.
  1. $.01 to $1.00 75% GP and 300% markup
  2. $1.01 to $5.00 71% GP and 245% markup
  3. $5.01 to $12.00 65% GP and 185% markup
  4. $12.01 to $26.00 60% GP and 150% markup
  5. $26.01 to $38.00 55% GP and 124% markup
  6. $38.01 to $50.00 50% GP and 100% markup
  7. $50.01 to $62.00 45% GP and 82% markup
  8. $.6201 to $74.00 40% GP and 67% markup
  9. $74.01 to $86.00 35% GP and 55% markup
  10. $86.01 and up 30% GP and 43% markup
  11. $.01 to $1.00 75% GP and 300% markup

Any help with this would be greatly appreciated
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hi,

Your Selling price is equal to Cost divided by (1 - markup )

Hope this will help
 

Fixer32

New Member
Joined
Apr 8, 2017
Messages
7
I guess what I need is a formula that calculates depending on what the input is for the cost depending on what it costs the margin will change automatically in the background. There would be no user input other than description and cost. the calculations would be hidden so only I can change them. So the blue cell would go away for the user input.
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,173
Office Version
  1. 2013
Platform
  1. Windows
Fixer32,

You could have a couple of lookup tables somewhere so that you can easily edit values if required or you can incorporate directly in your formula.
Not sure how you are wishing to calculate with both markup and Gp percentages?

Below shows a formula that is just adding a gross profit.
Sheet5

*ABCDEF
6ItemsDescription*Cost*Selling Price
7Item 1**£34.00*£52.70
8Item 2**£1.00*£1.75
9Item 3**£90.00*£117.00
10Item 4**£39.00*£58.50
11Item 5*****
12Item 6*****

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:103px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:86px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
F7=IFERROR(D7*(1+LOOKUP(D7,{0.01,1.01,5.01,12.01,26.01,38.01,50.01,62.01,74.01,86.01},{0.75,0.71,0.65,0.6,0.55,50,0.45,0.4,0.35,0.3})),"")

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4


Hope that helps.
 
Last edited:

Fixer32

New Member
Joined
Apr 8, 2017
Messages
7

ADVERTISEMENT

So would I just carry that same formula to all the cells in column"D", but change D7 to E7 E8 E9 and so forth till I get to D12?
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,173
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Yes, copy my formula into F7 and then drag it down as far as you need.
 

Fixer32

New Member
Joined
Apr 8, 2017
Messages
7
If I input $100.00 in cell D7 I should get a selling price of $143.86. With your formula I get $130.00. I want to use profit margin not markup so 30% profit margin uses a multiplayer of 1.43 am I correct. I hope I'm explaining it correctly%(&%&(%%
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,173
Office Version
  1. 2013
Platform
  1. Windows
Then change F7 to....
=IFERROR(D7/(1-LOOKUP(D7,{0.01,1.01,5.01,12.01,26.01,38.01,50.01,62.01,74.01,86.01},{0.75,0.71,0.65,0.6,0.55,0.5,0.45,0.4,0.35,0.3})),"")
 

Fixer32

New Member
Joined
Apr 8, 2017
Messages
7
I think it has to be like this, your thoughts...

=
IFERROR(D7*(1+LOOKUP(D7,{0.01,1.01,5.01,12.01,26.01,38.01,50.01,62.01,74.01,86.01},{3,2.45,1.85,1.5,1.24,1,0.82,0.67,0.55,0.43})),"")
 

Watch MrExcel Video

Forum statistics

Threads
1,130,163
Messages
5,640,520
Members
417,150
Latest member
cdguinn

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