Future Days Cover

[bluetaurean]

Need help in calculating future days cover


Period Opening Purchase Consumption Closing DaysCover

Jan 110 60 40 130 60

Feb 130 40 75 95 45

Mar 95 60 85 70 40

Apr 70 20 50 40 etc

May 40 20 60 0 etc

etc

Each period is a month - assumed 30days. I need to calculate the days cover at the end of the period based on future consumption. Note it is not a weighted average calcuation but based on forecasted consumption in future periods.

 

[Faseeh]

Hi Blue Tauream,


I place your data in between cells A1:F6 like bellow:

[pre]

Period	Opening	Purchase Consumption	Closing	DaysCover
Jan	110	60	 40	        130	60
Feb	130	40	 75	        95	45
Mar	95	60	 85	        70	40
Apr	70	20	 50	        40
May	40	20	 60	        0[/pre]

I added a scatter chart with Consumption on X Axis and Days Over on Y-Axis, enabled Trend Line (Linear) and Check-marked Display Equation on Chart, this shows the following equation on chart:


Y = -0.44 X + 77.68

You can try with this equation for forecasting purpose as it shows relation between the two variables..


Faseeh

 

[bluetaurean]

Hello,


Thanks for the feedback. I am looking for a formula to calculate the days cover given that actual future consumption and closing inventory data is already available. for a given closing inventory and future consumption data, the inventory days cover is to be calculated.