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 run out 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.
|Industry||Distribution, Financial Model, General Excel Financial Models|
|Summary||A 12-month inventory cash requirement forecast based on varying lead times and re-purchase rates.|
|Screenshots / Pictures||
|Use Cases||Budgeting, Cash Flow Analysis, Cash Flow Projections, CFO, Controlling, Cost Projections, Demand, Financial Projections, Forecast, Forecasting, Inventory, Management, Revenue Projections, Sensitivity Analysis|
You must log in and be a buyer of this download to submit a review.