Stock cover days with variable sales and delivery quantities

[Marco_3]

Hi,

I am looking for a formula able to work out the number of days that the sum of initial stock and incoming deliveries can cover based on variable sales and deliveries, in the below example this is under the heading of Stock Cover Days.

I have had to work it out manually, but I would be really glad if you could advise a non-VBA solution.

The below example is available as attached Excel file.

Thank you

Days	Sales	Stock	Deliveries	Stock Cover Days
18/08/2017	0	800	0	3.6
19/08/2017	525	0	200	2
20/08/2017	0	0	100	2.8
21/08/2017	475	0	200	2.4
22/08/2017	120	0	0	1.4
23/08/2017	100	0	0	0.4
24/08/2017	200	0	0	0

[Logit]

.

Here is one solution :


Paste in C3 and drag down as far as required :

Please Login or Register  to view this content.

Paste in E2 and drag down as far as required :

Please Login or Register  to view this content.

[Marco_3]

Hi Logit,

thank you for getting the ball rolling.

However, dividing the total stock by 222 is a little too approximate, I need the exact count of days my stock can cover based on variable sales.

[oeldere]

maybe you can add the manual expected result in your file.

can you please explain how you manual counted the expected result.

[Marco_3]

Hi oeldere,

my attachment already includes the expected result under the heading of Stock Cover Days.

I have attached another file with an example on how I calculated it for 20/08.

Very briefly, I have manually summed the initial stock to all deliveries up until that day, then deducted all sales values going forward and counted the days it takes for the total stock to go below zero.

At that point I simply worked out the % of sales value that can be covered on the last day.

[oeldere]

I get diferant values in the purple column.

So probably I don't understand how you counted the values.

[Marco_3]

Attached a file showing how I worked out the Stock Cover Days.

Please let me know should you have a formula that does all that.

[oeldere]

I have no idea how to make that formula.

Maybe other forummembers step in on this one.