I need to calculate the days coverage i have for my inventory. I start with
90660 units on 2-23 and then subtract my sales from my inventory. How many
days do i have before my inventory will go negative. I need to calulate this
daily over a period of time. Is there a formula i can use?
Date Days Cov Sales Iventory
FRI 02/23/07 90660
SAT 02/24/07 5D 10044 80616
SUN 02/25/07 4D 6696 73920
MON 02/26/07 3D 14580 59340
TUE 02/27/07 2D 18624 40716
WED 02/28/07 1D 18624 22092
THU 03/01/07 22656 -564

Assuming that your table here starts with "Date" in A1, over in cell E3 (same
row with 10044 and 80616 entries), put this formula
=INT(D3/AVERAGE(C$3:C3))
That will give you the number of days of inventory remaining at that point
based on average average sales to date as you extend the formula down the
sheet. The results I get are 8, 8, 5, 3 and 1 for the values you have in the
chart (and then of course it goes negative).

If you wanted the same thing just based on the last day's sales,
disregarding earlier sales amounts just make it =INT(D3/C3)

If you want to show partial days, drop the INT() from the formula, as:
=D3/AVERAGE(C$3:C3) and =D3/C3


"bjw" wrote:

I need to calculate the days coverage i have for my inventory. I start with
90660 units on 2-23 and then subtract my sales from my inventory. How many
days do i have before my inventory will go negative. I need to calulate this
daily over a period of time. Is there a formula i can use?
Date Days Cov Sales Iventory
FRI 02/23/07 90660
SAT 02/24/07 5D 10044 80616
SUN 02/25/07 4D 6696 73920
MON 02/26/07 3D 14580 59340
TUE 02/27/07 2D 18624 40716
WED 02/28/07 1D 18624 22092
THU 03/01/07 22656 -564

I really don't want an average though...I need the literal numbers to come
out of the formulas. Is there a way to do that?

"JLatham" wrote:

Assuming that your table here starts with "Date" in A1, over in cell E3 (same
row with 10044 and 80616 entries), put this formula
=INT(D3/AVERAGE(C$3:C3))
That will give you the number of days of inventory remaining at that point
based on average average sales to date as you extend the formula down the
sheet. The results I get are 8, 8, 5, 3 and 1 for the values you have in the
chart (and then of course it goes negative).

If you wanted the same thing just based on the last day's sales,
disregarding earlier sales amounts just make it =INT(D3/C3)

If you want to show partial days, drop the INT() from the formula, as:
=D3/AVERAGE(C$3:C3) and =D3/C3


"bjw" wrote:

I need to calculate the days coverage i have for my inventory. I start with
90660 units on 2-23 and then subtract my sales from my inventory. How many
days do i have before my inventory will go negative. I need to calulate this
daily over a period of time. Is there a formula i can use?
Date Days Cov Sales Iventory
FRI 02/23/07 90660
SAT 02/24/07 5D 10044 80616
SUN 02/25/07 4D 6696 73920
MON 02/26/07 3D 14580 59340
TUE 02/27/07 2D 18624 40716
WED 02/28/07 1D 18624 22092
THU 03/01/07 22656 -564

Try this
=D3/C3
in cell E3. That will give you estimated days remaining, including
fraction, based on that day's sales and the remaining inventory.


"bjw" wrote:

I really don't want an average though...I need the literal numbers to come
out of the formulas. Is there a way to do that?

"JLatham" wrote:

Assuming that your table here starts with "Date" in A1, over in cell E3 (same
row with 10044 and 80616 entries), put this formula
=INT(D3/AVERAGE(C$3:C3))
That will give you the number of days of inventory remaining at that point
based on average average sales to date as you extend the formula down the
sheet. The results I get are 8, 8, 5, 3 and 1 for the values you have in the
chart (and then of course it goes negative).

If you wanted the same thing just based on the last day's sales,
disregarding earlier sales amounts just make it =INT(D3/C3)

If you want to show partial days, drop the INT() from the formula, as:
=D3/AVERAGE(C$3:C3) and =D3/C3


"bjw" wrote:

I need to calculate the days coverage i have for my inventory. I start with
90660 units on 2-23 and then subtract my sales from my inventory. How many
days do i have before my inventory will go negative. I need to calulate this
daily over a period of time. Is there a formula i can use?
Date Days Cov Sales Iventory
FRI 02/23/07 90660
SAT 02/24/07 5D 10044 80616
SUN 02/25/07 4D 6696 73920
MON 02/26/07 3D 14580 59340
TUE 02/27/07 2D 18624 40716
WED 02/28/07 1D 18624 22092
THU 03/01/07 22656 -564