***Note, There are three versions for this template (all go out for 36 months now instead of 12 months). (two new versions have been recently added)
- Original – the original base template
- Version 2 – has variables for payment terms as well as minimum inventory level requirements
- Version 2 for startups – allows for a dynamic start date for the first batch of inventory arrival.
Trying to first figure out what your quantity demanded might look like over the next 12 months might be difficult. However, it is important to know how much cash you need in order to buy the supplies and/or inventory that will meet this demand. This model starts with a forecast that uses up to 3 years’ worth of historical monthly sales count data. Based on the average units sold per month per SKU/ID over that time, you will apply a % growth for each of the 12 future months.
The forecast was done in a way that lets seasonality effects show. You may have more sales or supply requirements in February or in November. Based on those varying rises and falls in inventory needs your going to have to know how much cash you need and when so that there is no problem being ready for the projections.
One of the most complex and difficult things to build in this model was a dynamic lead time assumption for each SKU. It allows you to enter a lead time count in days. Based on that number, the model will show the purchase happening that many days before the runout day of a given inventory SKU.
So, the model will track inventory levels based on starting supply and projected sales. Whenever the running supply goes negative, that triggers a re-order and it happens ‘x’ days before the depletion day per what is put in the lead time. There is a 365 days schedule so that the model is able to be precise enough on the lead time logic.
Finally, there is a monthly aggregate that will show the total cost of these re-purchases, when the re-purchases happen, the sales count and sales $, and the count of how much inventory arrives for each SKU per month. This is where you could plug in the cash requirements per month into a more broad financial model that needs to know how much cash is needed for inventory and when based on some predicted sales count.
With that above data, you will see a matrix of month-ending inventory balances per sku over the next 12 months. It will give you a sense of what type of reserves you need and when.
Plenty of charts and visuals are also available.
Similar customers where also interested in...
Multi-Entity Group Generic Monthly 5-Year 3 Statement Rolling Financial Projection Model
User-friendly 3 statement 5 year rolling financial projection Excel model for a Multi-entity Group (... Read more