Inventory degradation calculation

[CraigMac]

In order to work out some workload, I need to calculate when inventory will run out. For example:

Inventory = 100 pieces

Week: 45 46 47 48 49 50

Demand: 20 15 10 50 10

This means I need 5 more pieces in week49. How can I calculate this (ie when & how much) automatically using excel ?

Thanks,
Craig

 

[Greg Truby]

book1
	A	B	C	D	E	F	G	H	I
1	StartingBalance	10
2
3		Week
4		1	2	3	4	5	6	7	8
5
6	StartingBalance	10	15	3	7	1	1	5	1
7	Purchases	30	25	10	25	35	45	15	10
8	Demand	25	37	6	31	35	41	19	8
9	EndingBalance	15	3	7	1	1	5	1	3
Sheet1

 

[CraigMac]

Thanks for the hint, I need a little more help ( I wasn't clear enough before)

I need to do this for multiple line items, eg

Part Iventory wk45 wk46 wk47 ...... wk53 Week of first procure
XX 100 10 15 20 10 week48
yy 30 5 17 18 10 week 47

I had a simliar problem in the past, and think the answer is somewhere like :

=INDEX(I$2:V$2,MATCH(TRUE,I3:V3>0,0))

(but this would only work out the cell with the first value in it)

 

[Greg Truby]

Craig,

I tried every way I could think of to get this into a formula. After three failed alternate attacks, I gave up and just wrote a UDF to do it. Here's the spreadsheet:

Failed attempts to use SUM in array function.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M
1				ProjectedDemand(byWeek)
2	SKU	CurrO/H	GoodUntil	11	12	13	14	15	16	17	18	19	20
3	Gadgets	100	Week14	14	44	35	49	20	19	47	28	44	47
4	Gizmos	150	Week16	49	12	17	18	36	38	18	32	16	29
5	Widgets	175	Week18	20	31	16	14	26	11	50	20	23	43
Using UDF

Formula in C3 and copied down:
="Week "&OFFSET($C$2,0,monthssupply(B3,D3:M3))<hr>

The code for the UDF is:<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> MonthsSupply(rngStartInvent<SPAN style="color:#00007F">As</SPAN> Range, rngDemand<SPAN style="color:#00007F">As</SPAN> Range)<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Integer</SPAN><SPAN style="color:#007F00">' Returns period in which accumulated demand will exceed starting inventory</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> dblInvent<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Double</SPAN>
    
    dblInvent = rngStartInvent.Value
    i = 0
    <SPAN style="color:#00007F">Do</SPAN><SPAN style="color:#00007F">Until</SPAN> dblInvent< 0
        i = i + 1
        dblInvent = dblInvent - rngDemand(i)
    <SPAN style="color:#00007F">Loop</SPAN>
    MonthsSupply = i<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Function</SPAN></FONT><hr>
To add a User Defined Function (UDF) to a workbook:

1. Alt+F11 to get to the Visual Basic Editor (VBE).
2. If WB does not contain any Standard Modules then from the VBE menu Insert|Module
3. Add in code (copy and paste).
4. Alt+F11 to jump back to Excel.
5. In the cell enter the formula using the newly created UDF. Example<ul type=square>
6. =myNewUDF()

[/list]

HTH

 

[CraigMac]

Thanks Greg - works great !


Craig

 

[Greg Truby]

Craig,

As I said, I was just sure there had to be some way of doing this without relying on a UDF, I just couldn't suss it out. So I sent a plea for guidance to our #1 array formula guru. Turns out I was in one of those "so close and yet so far" situations. My logic was good, but I was trying to get SUM to do something it won't; whereas SUBTOTAL(9,) will play nice. So, courtesy of Mr. Aladin Akyurek we have another alternative that doesn't require VBA. Here it is.

Failed attempts to use SUM in array function.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M
1				Proj.Demand(byweek)
2	SKU	CurrO/H	GoodUntil	11	12	13	14	15	16	17	18	19	20
3	Gadgets	100	Week14	14	44	35	49	20	19	47	28	44	47
4	Gizmos	150	Week16	49	12	17	18	36	38	18	32	16	29
5	Widgets	175	Week18	20	31	16	14	26	11	50	20	23	43
Using Array Formula

Formula in C3 and copied down:
{="Week " & INDEX($D$2:$M$2,MATCH(TRUE,SUBTOTAL(9,INDIRECT(ADDRESS(ROW($D3),COLUMN($D3))&":"&ADDRESS(ROW($D3),COLUMN(D3:M3))))>=B3,0))}
Note that it's an array formula, so confirm with Ctrl+Shift+Enter.

Thanks, Aladin.

 

[Greg Truby]

FWIW, here is the formula I'd cooked up yesterday, which fails if use SUM, but works if use SUBTOTAL. Again, it's an array formula -

{="Week " & INDEX($D$2:$M$2,MATCH(TRUE,(SUBTOTAL(9,OFFSET(D3,0,0,1,COLUMN(INDIRECT("1:10"))))>$B3),0))}