Supply and Inventory Planning Template
Supply Inventory Planning tool will help you to calculate needs of goods to keep healthy inventory management giving you control on safety stock of your business!
Nowadays, big companies in the FMCG market are always searching for improvements on their supply chain systems investing a lot of money in ERP platforms to get control of the business getting special focus on inventory tracking. This Supply Inventory Planning Template connects all modules into an integrated replenishment planning system working in the same way as an ERP system.
Supply & Inventory Planning Template Features:
Basically, this Excel template consists of 6 main parts:
Using this Supply & Inventory Planning Template, you have the chance to calculate your planned orders, create, edit and release your purchase orders according to your needs. Here is what you can do:
- You can add or create new products and suppliers in a simple Master Data Worksheet with automatic code numbers for each SKU and Supplier.
- According to Master Data input, replenishment planning will be automatically calculated in the 24-month planning horizon.
- In Planned Order Worksheet, you can get the Planned Orders by Supplier and convert them as Purchase Orders.
- You can edit or delete Purchase Orders and replenishment planning will be automatically updated accordingly.
- Save any Purchase Order as PDF format and send to your supplier.
- You can perform Goods Receive Process in order to update your inventory.
- Create Planning Scenario Chart Report and Expected Gross Margin Chart by SKU and save as PDF format.
Now, let’s check the six main sections:
In this section, you can add or create new Suppliers filling the blank fields. An automatic code will be created once you add new suppliers.
**Please, do not input duplicate codes when loading Suppliers massive data from other systems.
2. MASTER DATA
You can add or create new products by Category, Product Description and Suppliers filling the blank fields. In case of loading Products massive data from another system to this Excel Inventory Management Template; suppliers must be matched with Suppliers Data in Section 1.
This section works as a simple Master Data Module setting up an automatic code when you save a new product. Again, do not duplicate codes when loading of your products from another system.
1st. month of Sales Forecast horizon is dynamic. Before selecting the date you would like to set, click “EDIT MASTERDATA & FORECAST” to able to choose from the drop-down list.
The other important indicators of Master Data determines how Supply Inventory Template will calculate Product Replenishment and Gross Margin: Min. Order Quantity, Unit Cost and Sales Price, ABC Classification, and Sales Forecast.
Min. Order Quantity (M.O.Q.): this is the minimum order quantity suppliers will ship to attend your purchase orders. All requirements in this template are calculated as multiple batch quantities according to M.O.Q.
Unit Cost and Sales Price: This is required for calculating Expected Gross Margin by SKU’s on Dashboard & Report Section.
ABC Classification: determines how the template will calculate the safety stock for each SKU.
- For “A” products, safety stock will set on 1-month coverage.
- For “B” products, safety stock will set on 2-months coverage.
- Finally, for “C” products, safety stock will set on 3-months coverage.
Sales Forecast: determines the expected sales for each SKU on a monthly basis.
Here is the engine of this Excel Inventory Monitoring Template. In this section you can check by SKU automatic calculation for replenishment quantities (Planned Orders), Inventory and Stock Coverage according to master data, Sales Forecast, and active Purchase Orders (Transit).
The Planning Scenario is calculated on 24 months planning horizon.
4. PLANNED ORDERS
Here you can check Planned Orders previously calculated on Planning section. You will be able to search Planned Orders by Supplier or get All Planned Orders. Once searching for Planned Orders by Supplier is processed, you are going to see how the Purchase Order preview will look like at the right side:
Moreover, you can set your own company data clicking “EDIT” button and filling the blank fields: Company Address, Company Name, Logo, and Tax Rate % for calculating grand total
Finally, you can save your Purchase Order clicking on SAVE PURCHASE ORDER button.
5. PURCHASE ORDERS
In this section, you can check any purchase order saved before even if the purchase order has been edited, deleted or processed as well received.
- Find your Purchase Order and select Supplier from object list;
- Click on “CONFIRM SUPPLIER” button and choose Purchase Order Nº from the list.
- Click on “FIND.P.O” button and you will get your Purchase Order Bill format at the left side.
From this point, you can save the selected Purchase Order bill as PDF format via “Send to PDF” button. For editing quantities click on “EDIT PURCHASE ORDER” and save your changes. All changes will affect the Planning Section.
When products arrive at your warehouse, you can process your purchase order as goods received clicking on “ADD GOOD RECEIVED” button. Purchase Order status will change to Received status and inventory quantities will be affected on the Planning Section automatically. You can also delete any purchase order in process and Planning Section will be updated accordingly.
6. DASHBOARD & REPORTS
Check planning scenarios in the graphic mode for each SKU according to calculated values on the Planning Section.
After selecting a product you would like to analyze, click on “UPDATE CHART” button to update dynamic charts. You can also save charts and related figures calculated according to Planning Section values by exporting PDF.
Finally, at the right side you can check all purchase orders status saved on this template, even deleted purchase orders.
Supply Inventory Planning Template Features Summary:
- Inventory Management Template in Excel
- Automatic Replenishment Planning by SKU
- Master Data capacity: 200 SKU
- Suppliers Data capacity: 1.500 Suppliers
- Works on Windows
- No installations needed, ready to use
- Dynamic Charts
- Compatible with Excel 2010 and later versions