Forward Cover Calculation For Horizontal Data

[RichardHerbageSCSN]

Hi i am trying to calculate forward cover on the correct "countdown method" and have seen various posts that use a combination of MATCH & OFFSET to determine the whole number of Sales Periods a given inventory has - however these seem only to work with Vertically presented data? (example for vertical '=MATCH(TRUE,SUBTOTAL(9,OFFSET(B3:B$11,,,ROW(B3:B$11)-ROW(B3)+1))>C2,0)-1)

- Does anyone know of the right formula for when data is horizontal as below?

Period>> 1 2 3 4 5 6 7 8 9 10
Sales 150 150 150 150 250 200 150 100 100 100
Closing Stock 500 500 700 800 800 700 600 500 500 500
True Cover (Manual) 3.2 2.8 3.7 5.0

[shg]

Can you explain how you got the manual results?

[RichardHerbageSCSN]

Explanation of manual results

Week 1 Closing Stock 500
Week 2 ,3,4 Sales all 150 each; so Stock Cover is 3 & something weeks, balance is 500-450=50 which divided by Week 5 Sales (250) which is 0.2, so total 3.2 weeks

Week 2 Closing Stock 500
Week 3 & 4 Sales 300 but Week 5 Sales is 250; ; so Stock Cover is 2 & something weeks, balance is 500-300=200 which divided by Week 5 Sales (250) which is 0.8, so total 2.8 weeks


etc etc

[JohnTopley]

Try

in B4 ("helper" row)

=MATCH(TRUE,SUBTOTAL(9,OFFSET(C2:$K2,,,,COLUMN(C2:$K2)-COLUMN(C2)+1))>B3,0)-1

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Copy across

in B5

=B4+(B3-SUM(OFFSET(C2,,,,B4)))/OFFSET(B2,,B4+1)

[RichardHerbageSCSN]

John, that's spot on - thank you! Had tried the same but think came unstuck with the control-shift-enter and lost confidence in the ability of OFFSET & COLUMN to replace OFFSET & ROW for Horizontal data.

I did by the way come up with this little one to deal with the problem if i couldn't find a solution - benefits of this approach is it doesn't use a "volatile" function, whilst it is limited in that it gets capped at 7 days.
'=IF(B3-SUM(C2:INDEX(C2:J2,7))>=0,">7 Days",IF(B3-SUM(C2:INDEX(C2:J2,6))>=0,6+(B3-SUM(C2:INDEX(C2:J2,6)))/INDEX(C2:J2,6+1),IF(B3-SUM(C2:INDEX(C2:J2,5))>=0,5+(B3-SUM(C2:INDEX(C2:J2,5)))/INDEX(C2:J2,5+1),IF(B3-SUM(C2:INDEX(C2:J2,4))>=0,4+(B3-SUM(C2:INDEX(C2:J2,4)))/INDEX(C2:J2,4+1),IF(B3-SUM(C2:INDEX(C2:J2,3))>=0,3+(B3-SUM(C2:INDEX(C2:J2,3)))/INDEX(C2:J2,3+1),IF(B3-SUM(C2:INDEX(C2:J2,2))>=0,2+(B3-SUM(C2:INDEX(C2:J2,2)))/INDEX(C2:J2,2+1),IF(B3-SUM(C2:INDEX(C2:J2,1))>=0,1,B3/C2)))))))

Where Sales Starts in B2, and stock in b3 (sorry new to this so couldnt upload a file)

[AliGW]

Yes, you CAN upload a workbook!!!

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.