design a formula to calculate the inventory coverage

echo chen

New Member
Joined
Jul 6, 2011
Messages
2
I'm a supply chain planner from China. I need calculate the inventory coverage each month to monitor the inventory trend. Without a good formulation in excel, I have to do this job manually which caused me so much time and I even can't ensure the result is correct since I calculated one by one manually.

I don't know how to attach a file, just can explain my request as below

column A is the product name
column B is ending inventory by last month
column C is where I need put the formulation, it will tell me the inventory coverage ( how long the current inv can be convered --- by month )
column D -- I is the sales forecast

I calculate the coverage based on the sales forecast. For example, C product inventory is 46835kgs, by adding column D-G, it's 32383kgs, but if I add column H, it will be more than the current inv., so, I use 4+((B-D-E-F-G)/H), 4 means the count from D-G, the balance after cover D-G is 46835-32383=14452kgs, it's 71% of H column, so the result in C is 4.71 month. This is the way I'm now using, very stupid but really useful for me to monitor the inventory level !

Could you pls help to design a formulation to solve this problem? I've seek many help on Chinese website but no valid solution.

I'm really looking forward your kind help, of course, if I need pay money on the consultation, pls let me know beforehand....

Appreicated in advance!
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
This might sound a wierd way to do this but it might help.

If your first line of data is in row2, highlight all of your data from cell D2 across and down.

Go to conditional formatting and change to 'formula is':
=SUM($D2:H2)<$C1
and make the fomatting something obvious, like green

This will now change all of schedule numbers to green, when there is enough inventory to cover the stock. you then know that if a schedule number is not changed to green, you need to order more inventory to cover that month's demand.

Not exectly what you were looking for maybe a help.
 
Upvote 0
Do you know how can I attach the excel file in the thread? I'd like to show you the excel file so that you may know what I need indeed.Cheers!
 
Upvote 0

Forum statistics

Threads
1,224,590
Messages
6,179,762
Members
452,940
Latest member
rootytrip

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