Cost plus margin calculator

mikeniedert

New Member
Joined
Aug 4, 2017
Messages
5
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
Joined
Nov 26, 2020
Messages
1,174
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Nov 26, 2020
Messages
1,174
Office Version
  1. 2016
Platform
  1. Windows
NVM. I did not read carefully to understand it
 

mikeniedert

New Member
Joined
Aug 4, 2017
Messages
5
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
Joined
Jul 30, 2020
Messages
158
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

mikeniedert

New Member
Joined
Aug 4, 2017
Messages
5
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
Joined
Jul 30, 2020
Messages
158
Office Version
  1. 365
Platform
  1. Windows
No problem, happy to help, thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

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