Cost plus margin calculator

[mikeniedert]

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
	C	D	E	F	G
2	Amount Spent:
3
4		Spend Thresholds		Cost Plus Margin	Cost
5		$ 0.01	$ 1,500.00	2.50%
6		$ 1,500.01	$ 3,000.00	2.00%
7		$ 3,000.01	$ 5,000.00	1.50%
8		$ 5,000.01	$ 99,900.00	0.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!

 

[Zot]

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]

NVM. I did not read carefully to understand it

 

[mikeniedert]

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]

Hi mikeniedert, see the formula:

Book1.xlsx
	C	D	E	F	G
2	Amount Spent:	$ 4,000.00
3
4		Spend Thresholds		Cost Plus Margin	Cost
5		$ 0.01	$ 1,500.00	2.50%	$ 37.50
6		$ 1,500.01	$ 3,000.00	2.00%	$ 30.00
7		$ 3,000.01	$ 5,000.00	1.50%	$ 15.00
8		$ 5,000.01	$ 99,900.00	0.50%	$ -
Sheet1

Cell Formulas
Range	Formula
G5:G8	G5	=MAX((MIN($D$2,E5)-E4),0)*F5

 

[AhoyNC]

This link may be of help.

McGimpsey & Associates : Excel : Using SUMPRODUCT() with variable rates

 

[mikeniedert]

Hi mikeniedert, see the formula:

Book1.xlsx
	C	D	E	F	G
2	Amount Spent:	$ 4,000.00
3
4		Spend Thresholds		Cost Plus Margin	Cost
5		$ 0.01	$ 1,500.00	2.50%	$ 37.50
6		$ 1,500.01	$ 3,000.00	2.00%	$ 30.00
7		$ 3,000.01	$ 5,000.00	1.50%	$ 15.00
8		$ 5,000.01	$ 99,900.00	0.50%	$ -
Sheet1

Cell Formulas
Range	Formula
G5:G8	G5	=MAX((MIN($D$2,E5)-E4),0)*F5

Thanks! That did the trick. I'm grateful for geniuses

 

[Habtest]

No problem, happy to help, thanks for the feedback.