Excel Insert Cost (specified in another sheet) after every interval of Specified number of units sold.

[feelsk]

Dear All,

I am struggling with a situation where I developed a feasibility study, and would like to ADD COST (FIXED NUMBER) AT EVERY INTERNAL AFTER ACHIEVING A CERTAIN NUMBER OF UNITS SOLD, for example:

Cost of procuring 10,000 units is USD 2,000.00

Yr0 Yr1 Yr2 Yr3 Yr4
Sales (units) - 5000 7000 9000 1000
Cost (USD) 2000 - 2000 2000 -

Hope I was able to explain as I intended.

Thanks in advance,
feelsk

 

[skorpionkz]

Not sure if I understood ou correctly. Try this formula. In this case you need to achieve 10k units.

Excel 2010
	A	B	C	D	E	F
1		YR0	YR1	YR2	YR3	YR5
2	Sales (Units)	-	5000	7000	9000	10000
3	Cost (USD)					2000
Sheet1

Cell Formulas
Range	Formula
B3	=IF(OR(NOT(ISNUMBER(B2)),B2<10000),"",FLOOR(B2/10000,1)*2000)

 

[feelsk]

skorpionkz, thanks for the prompt reply.

This is very helpful. However, lets say:

1. In the Yr0, I paid USD 2,000.00 for a shipment of 10,000 units, and sold no units. Shipment comes only in consignment of 10,000 units.

2. In the Yr1, I sold 5,000 units, hence left with 5,000 units

3. In the Yr2, I sold 7,000 units, of which I previously had 5,000.00 from 1st shipment of Yr0, balance 2,000 units comes from the 2nd shipment purchased in this year for USD 2,000.00. Therefore, in Yr2 column USD 2,000.00 should also appear.

4. In the Yr3, I sold 9,000 units, similarly, I already had 8,000 units from 2nd shipment, and to make up for the remaining 1,000 units, I bought 3rd shipment for USD 2,000.00. Therefore, in Yr3 also, 2,000. should appear.

5. In the Yr4, I only sold 1,000 units. I already had 9,000 units from the 3rd shipment, therefore no shipment was purchased, consequently USD 2,000.00 should not appear in this year.

I wish if excel could automatically put USD 2,000 under the same year, in which I had to buy shipments.

Hope this is not too overwhelming, and able to communicate more clearly.

Thanks in advance.

 

[skorpionkz]

Sorry, I need to correct the formula, will back soon

 

[skorpionkz]

Sorry I spot that there was mistake in the formula in post above, so I deleted it to do not confuse anyone. Try ths fromula

Excel 2010
	A	B	C	D	E	F	G
1		YR0	YR1	YR2	YR3	YR5	YR6
2	Sales (Units)	0	5000	7000	9000	10000	1000
3	Stock (Units)	10000	5000	8000	9000	9000	8000
4	Cost (USD)	2000		2000	2000	2000
Sheet1

Cell Formulas
Range	Formula
B3	=IF(B2>A3,A3-B2+10000,IF(OR(NOT(ISNUMBER(B2)),B2=0),10000,A3-B2))
B4	=IF(OR(NOT(ISNUMBER(B2)),B2>A3,B2=0),2000,"")

This will not work though if you would sell more than 10000 items

 

[skorpionkz]

I play around with this formulas and it should work now for any order

Excel 2010
	A	B	C	D	E	F
1		YR0	YR1	YR2	YR3	YR5
2	Sales (Units)	0	5000	7000	25000	15000
3	Stock (Units)	10000	5000	8000	3000	8000
4	Cost (USD)	2000		2000	4000	4000
Sheet1

Cell Formulas
Range	Formula
B3	=IF(NOT(ISNUMBER(A3)),10000,IF(B2>A3,CEILING(-(A3-B2)/10000,1)*10000+A3-B2,A3-B2))
B4	=IF(NOT(ISNUMBER(A3)),2000,IF(B2>A3,CEILING(-(A3-B2)/10000,1)*2000,""))

 

[feelsk]

Worked Like a Charm!!! Brilliant! Absolutely Brilliant! A master of Excel indeed!

Sir, skorpionkz, you have saved me a lot of trouble, and indeed awful lot of time. Hats off! Couldn't thank enough.

 

[skorpionkz]

You are welcome!