Inventory Manager for Re-Order Planning Excel Model

Help control and organize inventory re-ordering.

by Jason Varner
smarthelping.com - Financial Modeling for EveryoneFollow 141

466 views|Start the discussion!|Bookmark Add to cart - $40.00

Description
This tool is available in google sheets and excel. The google sheet link is on the 'instructions' tab of the excel download. Hit File>Make a Copy to access your own version.

The idea behind it is to help an inventory manager track when they need to re-order various sku's based on some smart logic. The inputs by the user will define how the logic runs. It can be run once a day, week, month, or whatever is needed based on the specific situation.

Inputs include: inventory level at end of current day, minimum reserve, avg. sales per day, and lead time. Based on these inputs, the logic will display the inventory level that should trigger a re-order and the expected date that will be.

The google sheet version has advanced filtering so you can drop in a number that defines the days until a sku needs re-ordered and based on the database you have populated, the filter will only display items that need to be re-ordered with that 'amount of days' input. This is nice because you can easily see, out of possibly hundreds or thousands of skus, what the skus are that need to be re-ordred in the next x days.

There is conditional formatting to define when a given sku is under its re-order level as well as when it is getting close to re-ordering (red/yellow/green). The video explains exactly what defines the color-coding.

On the excel version, there are 3 filter columns where you can manually choose to only display rows that meet a given amount of days until re-order trigger, but the google sheet version is superior to doing that.

The last piece of logic is to estimate the amount of units you should re-order. I have built this to run off a multiple of the inventory level that should trigger a re-order per the given SKU. Based on the result of that calculation, it will tell you the expected amount of days that should last.

Enjoy!

This Best Practice includes
1 Excel template and 1 tutorial video