How to count the number of periods I have the inventory for

[Aldek]

Hi, as in the subject, I'm trying to figure out how to compute / construct formulas in Google Spreadsheet, which dynamically shows the count of months I have enough inventory for.

My data is:

Let say the numbers represent units of volume.
So I finish my first period with 5562 units. I need to compute for how many future months I have stock (i guess it is something around 1.3 month), of course having some assumption of future sales and new production (I need to optimise my production as it seems it is not necessary to produce so much).

Much appreciate for you r help.
Best
Aldek

 

[KRice]

Hi Aldek,
Welcome to the Board! What can you say about your assumptions? In your example, you have opening balance inventories that increase substantially over time. Do you have some target opening balance inventory that you would like to maintain from one period to the next? And then are you primarily interested in balancing the "produced" values to keep up with the "sold volume", assuming the most recent rates for each, so that you can maintain the target inventory level?

 

[KRice]

Here is an idea that might help...
The user inputs are in blue-shaded cells. You would establish some target inventory level indirectly by specifying how many periods of inventory buffer you want to maintain based on the most recent production and sales volumes (for the past one month). That target level is used to estimate the production volume for the upcoming month. But there may be some reasons why it is not practical to jump directly to the targeted inventory level: 1) perhaps the current inventory level is so large and sales volumes are so low that it would take several months to reach the target level with no production whatsoever; and 2) to maintain production facilities some minimal level of production will probably be necessary, due to either equipment startup/maintenance issues or workforce volume matters. For that reason, a minimum production level per period is introduced as a user input. Another user input pertains to Returned items. If the reason for returning an item is due to a defect, then it probably cannot be sold again, so an input for "Reutilization of Returned" reflects the fraction of returned items that are okay to resell again. With this approach, the Inventory Utilization Time (expressed in periods), eventually levels out at the target inventory level, assuming there are not large deviations in the inputs.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Target Inventory Level (pds)	1.5
2	Reutilization of Returned	0.5
3	Minimum production/pd	650
4
5	Period	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16
6	Inventory - Opening Balance	3433	5562	4212	3250	3386	2972	2558	2144	1730	1596	1596	1596	1596	1596	1596	1596
7	Sold Volume	1064	2000	1800	1064	1064	1064	1064	1064	1064	1064	1064	1064	1064	1064	1064	1064
8	Returned	1	1	100	1	1	1	1	1	1	1	1	1	1	1	1	1
9	Produced	3193	650	788	1200	650	650	650	650	930	1064	1064	1064	1064	1064	1064	1064
10	Inventory - Closing Balance	5562	4212	3250	3386	2972	2558	2144	1730	1596	1596	1596	1596	1596	1596	1596	1596
11
12	Inventory Utilization Time (pds)	7.23	1.43	1.27	3.31	2.40	2.02	1.63	1.24	1.37	1.50	1.50	1.50	1.50	1.50	1.50	1.50
Sheet4

Cell Formulas
Range	Formula
C6:Q6	C6	=B10
C9:Q9	C9	=ROUNDUP(MAX(($B$1+1)*B7-($B$2*B8)-B10,$B$3),0)
B10:Q10	B10	=ROUNDDOWN(B6+$B$2*B8+B9-B7,0)
B12:Q12	B12	=(B10+B9+$B$2*B8-B7)/B7