Formula to find months inventory coverage

jolin78

New Member
Joined
Mar 8, 2020
Messages
1
Office Version
  1. 2019
Platform
  1. Windows
Hi,

Need help to find out the formulation to use to calculate the months of inventory coverage base on the Stock on hand ( Column B ) verus the monthly demand ( Column D to O )

1583676600930.png
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
There are a lot of things to take into consideration with such formulas, the simplest way would be to divide the stock on hand by the average demand of the last 12 months

=B2/AVERAGE(D2:O2)

Note that this method is simple, but far from accurate.

Entering 'inventory coverage' into google will provide you with a significant amount of information on the matter, the first couple of results list a number of factors that should be considered.
 
Upvote 0
divide the stock on hand by the average demand of the last 12 months

=B2/AVERAGE(D2:O2)
From the dates in the headings, D2:O2 looks like the next 12 months?

@jolin78
Welcome to the MrExcel board!

I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with. ;)

Is this what you are after? The formula in C2 is an array formula so needs to be confirmed with Ctrl+Shift+Enter. You can then copy/drag it down.
Also note that OFFSET is a volatile function so might impact your sheet's performance if you have a very large number of rows with this formula.

20 03 09.xlsm
BCDEFGHIJKLMNO
1SOHMonths CoverageMar-20Apr-20May-20Jun-20Jul-20Aug-20Sep-20Oct-20Nov-20Dec-20Jan-21Feb-21
24257233969549477219064969486
370103957245011108649446140
4289435924749085108436556299
55001190171873504666319243
Sheet2
Cell Formulas
RangeFormula
C2:C5C2=IFERROR(MATCH(B2,SUBTOTAL(9,OFFSET(D2,,,,COLUMN(D2:O2)-COLUMN(D2)+1))),0)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
 
Upvote 0
Or try this in C2:

=IFERROR(MATCH(B2,SUMIF($D$1:$O$1,"<="&$D$1:$O$1,D2:O2)),0)

Enter with Ctrl+Shift+Enter.
 
Upvote 0
hi i need 3 months stock cover formula for excel
sales, open stock closing stock new order 3 months stock cover
using above tab please assist
 
Upvote 0
this works but is there a way for it show to one decimal place?
Yes. I like to split it into several rows to not mash several formulas into one.

First, you have the full month coverage as specified above.

1689756196977.png


Then, you calculate how much stock you have covered, through a sum indirect address function. This function sums up the total stock x months forward, and refers to the full month coverage. In this case, it takes the next month's COGS and sums it with the following six months (as we have a full coverage of 7 months).

1689756279235.png


Next, you calculate how much of the month after the nth month you have covered.

1689756424023.png


And finally, sum that with the full month inventory coverage and you have the coverage to how many decimal points you want!

1689756483429.png


Hope that helps!
 

Attachments

  • 1689756259050.png
    1689756259050.png
    35.6 KB · Views: 69
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top