$45.00 USD
The file can be downloaded immediately after purchase. This is included in the inventory template bundle and the Accounting Templates bundle.
User Inputs per SKU (these drive all the calculations):
- Historical sales (or projected if you don't have historical data)
- Expected annual growth rate (enter for year 1 to 6)
- Average Lead Time (months)
- Payment Terms (% paid in up to 3 payments)
- Minimum Inventory Level (safety stock)
- Average Cost per Unit
- Average Sales Price per Unit
- Starting Inventory
- Reorder Amount (dynamic over time per expected sales)
Outputs:
- Monthly and annual summaries for up to 72 months
- Cash payment required for inventory per period
- Running inventory balance (amount and count) per period
- Inventory units arriving per month
- Inventory units purchased per month
- 3-month trailing average inventory value
- Visuals for all of the above
- Running accounts payable balance per period
- Revenue, COGS, Gross Profit
- Easily scale to as many SKUs as you need (compatible with 1,000s)
Expansion:
If you need to account for more than 19 SKUs, the model is easily scalable. All the formulas are setup so the only thing needed is to drag the last row of cells down on each tab as far as you want. It is easy for any spreadsheet skill level. I accomplished this feature by making it so each matrix of calculations is on its own tab, that way when you drag down the logic, you don't have to also move stuff below it down. The spreadsheet can easily handle 1,000s of SKU configurations at one time.
The hardest part of building this model was how to dynamically account for different lead times for different SKU types. I did figure it out so you will see that based on the amount of inventory purchased at each reorder and the number of months it takes for an order to actually reach your inventory, it will show you the timing of when different SKU's need to be purchased and the cash that it takes month to month.
This is important because oftentimes you have to buy ahead of planned demand. The way this inventory forecasting tool works will take your average sales counts during each month of the year and then based on a % increase/decrease you enter, it will give you the forecasted unit sales per SKUs. That logic accounts for seasonality factors that cause more or less sales to happen at different times of the year. You need to know how that impacts cash flow.
The cash management then comes into play as it will show the timing of when inventory needs to be purchased in order for you to have enough supply for the planned demand. That is huge as the assumptions can cause dramatic fluxes in how much cash you need and when. Often times this is not accounted for precisely and shortages can occur that are hard to fix because of various logistical problems.
The model will aggregate all the data into one monthly cash flow line. For added visibility, you can also see the total sales per your forecasted unit sales counts. This is a good tool to implement with your other models that may not take into account the nuances of inventory cash requirements over the course of a year. This tool can plug right into your other models as it shows the data on an aggregate monthly basis.
This is important because oftentimes you have to buy ahead of planned demand. The way this inventory forecasting tool works will take your average sales counts during each month of the year and then based on a % increase/decrease you enter, it will give you the forecasted unit sales per SKUs. That logic accounts for seasonality factors that cause more or less sales to happen at different times of the year. You need to know how that impacts cash flow.
The cash management then comes into play as it will show the timing of when inventory needs to be purchased in order for you to have enough supply for the planned demand. That is huge as the assumptions can cause dramatic fluxes in how much cash you need and when. Often times this is not accounted for precisely and shortages can occur that are hard to fix because of various logistical problems.
The model will aggregate all the data into one monthly cash flow line. For added visibility, you can also see the total sales per your forecasted unit sales counts. This is a good tool to implement with your other models that may not take into account the nuances of inventory cash requirements over the course of a year. This tool can plug right into your other models as it shows the data on an aggregate monthly basis.
You may also be interested in this made-to-order manufacturing financial model.
Questions and Answers From Customers Who Purchased This Template:
Customer Question:
Suffice it to say, I have two questions regarding the template (72 month inventory forecast):
1. Is the recommended order amount per order based on the amount forecasted to be needed exactly (for a period of time) or is it estimated based (averages times days for example)?
2. Does this template work properly on google sheets?
Answer:
1) The order amount is estimated on averages per year, not exact.
2) Yes, you can upload it to Google sheets no problem.
Customer Question:
I’m using your new Inventory Forecasting 72 Months. First off, it’s great! Thank you.
I do have a question though - is there a way for me to plug in ALL of my unit sales projections for 72 months in the Historical Sales Count tab rather than using the annual growth % built into the model? Can I just delete the formulas and plug in my forecast values? (The problem I’m having is that my client will discontinue one of his products in year two.)
Answer:
Yes, you can plug in your expected sales manually for 72 months on the 'Historical Sales Count' tab starting in column AZ for each SKU, thereby overriding the growth %'s built into the model . The depletion logic will run off of that and nothing will be broken other than the fact that the growth %'s will no longer effect the model.
More Inventory Management Spreadsheets:
