Formula to project inventory run out date

[Coach Hager]

I'm trying to project the run out date of my inventory based off of last years history. I have data on sheet1 and sheet2. The first sheet is the Item# (columnA)and the Quantity on Hand (columnB). I want column C to tell me the date I would run out of inventory if it was reduced at the same rate as last year. The 2nd set of data on sheet2 is last years sales history by invoice and has the Item# (columnA) Transaction Date (columnB) and Quantity sold (columnC).

Sheet 2 data looks like this:
Item# Date Quantity
100 12/15/08 -50
100 12/15/08 -25
101 12/15/08 -10
100 12/16/08 -100

Hopefully this makes sense and someone can help.

Thanks in advance!

 

[gardnertoo]

Do you find that your sales are fairly consistent throughout the year, or do you have peak periods and lulls? If peaks and lulls are present, that will be important to consider, because 100 on hand might last for months during a lull, but be sold out in a week during peaks. Does Sheet2 list the data for all of last year, or just a portion of last year?

The approach I would take is to calculate a units-per-day sold for the previous year, or the portion of the year that is relevant. You'll want a way to compare that number to your current-year numbers and pick a good units-per-day figure based on that comparison, then divide your on-hand quantity by that number to determine days-of-inventory on hand. If you show us a little more sample data, I can work up the formulas.

 

[Coach Hager]

Some items are extremely seasonal others are consistent. I want to create a spreadsheet that I can updated any day to show projected run out of all items, if movement is the same as previous 365 days. I would update the sheet2 sales data each time I wanted to see this report to get the last 365 days of sales data. I know that inventory won't move exactly like the previous 365 days, but looking for a general idea as some items have extended lead times to replace.

Thanks for looking into this!

 

[Coach Hager]

Just wanted to bump this up as it already slipped to page 11.

 

[gardnertoo]

OK, I created some fictional data to play with, located on Sheet1 in the range A2:C20000, then came up with some formulas for you. Because it looks like you have multiple transactions per day for any given Item, the SUMPRODUCT formula is heavily used here. (If the list displayed a daily total of xx units of item nn, I might have gone with a LOOKUP or INDEX/MATCH combo instead.) What I've done is create a day by day forecast of inventory based on a starting number in column C. Each column from D all the way to IV then does a SUMPRODUCT against the list of transactions, looking for those which both match the Item in column A, and match the date one year ago plus x days (x is determined by what COLUMN() the formula is in). I perform an INT operation against the list of dates on the off-chance that they are actually in date&time format, like my fictional data is. The SUMPRODUCT adds up those transactions, then the formula subtracts that number from the inventory. At some point, this results in a negative inventory level; that's your depletion date displayed in column B. (Manually highlighted two near-term depletions.) You see at the top something called EAF, entered in cell D1. That's my shorthand for Economy Adjustment Factor. This is where you can tell the spreadsheet that this year is better or worse than last year, and affect the forecast accordingly. If your sales this year are half of last year's, the EAF should be 0.5, if equal then EAF=1.0, if double then EAF=2.0 etc.

Here are the formulas, listed by cell number:
D2, copy across all the way to IV2:
="Proj Inv, close of business "&TEXT($B$1+COLUMN()-3,"mm/dd")

C3, copy down:
=$B$1+COUNTIF(D3:IV3,">"&0)+1

D3, copy this down and across all the way to IV:
=C3-$D$1*SUMPRODUCT(--(INT(Sheet2!$B$2:$B$20000)=Sheet1!$B$1-365+(COLUMN()-3)),--(Sheet2!$A$2:$A$20000=Sheet1!$A3),Sheet2!$C$2:$C$20000)


Here's an excerpt from the sheet:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Date:	12/11/2009	EAF:	1
2	Item	Projected depletion date	On Hand	Proj Inv, close of business 12/12	Proj Inv, close of business 12/13	Proj Inv, close of business 12/14	Proj Inv, close of business 12/15	Proj Inv, close of business 12/16	Proj Inv, close of business 12/17	Proj Inv, close of business 12/18	Proj Inv, close of business 12/19	Proj Inv, close of business 12/20	Proj Inv, close of business 12/21
3	100	12/18/2009	5000	4485	3650	2885	2055	1325	715	-415	-590	-1715	-1775
4	101	2/8/2010	8000	7975	7715	7630	7385	7185	7150	7055	6880	6760	6710
5	102	1/28/2010	7000	6975	6900	6615	6480	6310	6285	6235	6035	5760	5750
6	103	12/21/2009	1000	815	755	735	700	675	640	340	115	90	-5
7	104	2/26/2010	12000	11780	11480	11370	11095	10785	10675	10630	10350	10255	10235
Sheet1

 

[Coach Hager]

Exactly what I was looking for! This works perfectly.


Thanks!