# Cost plus margin calculator

#### mikeniedert

##### New Member
This one is perplexing me. I am trying to create a calculator that will help me run various "what if" scenarios in a cost plus model. We have a vendor who charges us using a cost plus model, but the margin gets better as we spend more through the year. So let's say I spend a total \$6000 through the year, I'd pay 2.5% on the first 1500, 2% on the second 1500, and etc. If I spend \$2000, then I'd pay the 2.5% on the first \$1500, and 2.0% on the last \$500.

Book2 (version 1).xlsb
CDEFG
2Amount Spent:
3
4Spend ThresholdsCost Plus MarginCost
5\$ 0.01\$ 1,500.002.50%
6\$ 1,500.01\$ 3,000.002.00%
7\$ 3,000.01\$ 5,000.001.50%
8\$ 5,000.01\$ 99,900.000.50%
Sheet2

I need a set of formulas in the Cost Column that would calculate the costs based on whatever number I plug into the Amount Spend cell.

Thanks!

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### Zot

##### Well-known Member
I did not get your idea. Maybe because not having business mind

You want to put amount spent but what is the relation to amount in column E?

#### Zot

##### Well-known Member
NVM. I did not read carefully to understand it

#### mikeniedert

##### New Member
I did not get your idea. Maybe because not having business mind

You want to put amount spent but what is the relation to amount in column E?
Sorry I wasn't more clear. The Amount Spent in cell D2 is an annual number. I want to be able to plug in different amounts in that field and have the formulas in G5-G8 calculate out the costs. So if I spend \$600 during the year, they charge me 2.5%, so so the result would be \$15 in G5 and all the rest would be zero. If I spend \$4000, G5 should show \$37.50 (2.5% of \$1500), G6 should show \$30 (2.0% of \$1500), G7 should show \$15 (1.5% of the remaining \$1000).

#### Habtest

##### Board Regular

Hi mikeniedert, see the formula:

Book1.xlsx
CDEFG
2Amount Spent:\$ 4,000.00
3
4Spend ThresholdsCost Plus MarginCost
5\$ 0.01\$ 1,500.002.50%\$ 37.50
6\$ 1,500.01\$ 3,000.002.00%\$ 30.00
7\$ 3,000.01\$ 5,000.001.50%\$ 15.00
8\$ 5,000.01\$ 99,900.000.50%\$ -
Sheet1
Cell Formulas
RangeFormula
G5:G8G5=MAX((MIN(\$D\$2,E5)-E4),0)*F5

#### AhoyNC

##### Well-known Member
This link may be of help.

#### mikeniedert

##### New Member
Hi mikeniedert, see the formula:

Book1.xlsx
CDEFG
2Amount Spent:\$ 4,000.00
3
4Spend ThresholdsCost Plus MarginCost
5\$ 0.01\$ 1,500.002.50%\$ 37.50
6\$ 1,500.01\$ 3,000.002.00%\$ 30.00
7\$ 3,000.01\$ 5,000.001.50%\$ 15.00
8\$ 5,000.01\$ 99,900.000.50%\$ -
Sheet1
Cell Formulas
RangeFormula
G5:G8G5=MAX((MIN(\$D\$2,E5)-E4),0)*F5
Thanks! That did the trick. I'm grateful for geniuses

#### Habtest

##### Board Regular
No problem, happy to help, thanks for the feedback.

Replies
2
Views
855
Replies
6
Views
2K
Replies
1
Views
231
Replies
15
Views
2K
Replies
3
Views
748

1,130,118
Messages
5,640,216
Members
417,131
Latest member
Seanr19871

### 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.

### Which adblocker are you using?

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

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