Supply Planning Excel Template

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 a special focus on inventory tracking. This Supply Planning Excel Template connects all modules into an integrated replenishment planning system working in the same way as an ERP system.

Supply Planning Excel Template Features:

Basically, this Excel template consists of 6 main parts:

First of all, you have the chance to calculate your planned orders and create, edit and release your purchase orders according to your needs. Here is what you can do with this Supply & Inventory Planning Template:

• 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 in PDF format and send it to your supplier.
• You can perform the 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:

1. SUPPLIERS

Basically, you can add or create new Suppliers by filling the blank fields in this section. Moreover, the template will create an automatic code once you add new suppliers.

**Please, do not input duplicate codes when loading Supplier’s 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 your products from another system.

To emphasize, the 1st. the month of Sales Forecast horizon is dynamic. Before selecting the date you would like to set, click “EDIT MASTERDATA & FORECAST” to be able to choose from the drop-down list.

The other important indicators of Master Data determine 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 to your purchase orders. This template calculates all requirements as multiple batch quantities according to M.O.Q.

Unit Cost and Sales Price: This is required for calculating Expected Gross Margin by SKUs in Dashboard & Report Section.

ABC Classification determines how the template will calculate the safety stock for each SKU.

1. For “A” products, safety stock will set on 1-month coverage.
2. For “B” products, safety stock will set on 2-months coverage.
3. 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.

3. SUPPLY PLANNING IN THE TEMPLATE

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).

Lastly, the Planning Scenario is calculated on 24 monthly planning horizon.

4. PLANNED ORDERS

Here you can check Planned Orders previously calculated in the Planning section. Furthermore, 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 on the right side:

Moreover, you can set your own company data by clicking the “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 by clicking on the SAVE PURCHASE ORDER button.

5. PURCHASE ORDERS

Then, 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 the 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 on the left side.

From this point, you can save the selected Purchase Order bill in PDF format via the “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 by clicking on the “ADD GOOD RECEIVED” button. Then, the purchase order status will change to Received status and inventory quantities will be affected in the Planning Section automatically. Lastly, you can also delete any purchase order in process and the template will update the Planning Section accordingly.

6. DASHBOARD & REPORTS

Firstly, check planning scenarios in the graphic mode for each SKU according to calculated values in the Planning Section.

After selecting a product you would like to analyze, click on the “UPDATE CHART” button to update dynamic charts. You can also save charts and related figures calculated according to Planning Section values by exporting a PDF.

Finally, on the right side you can check all purchase orders status saved on this template, even deleted purchase orders.

Supply Planning Excel Template Features Summary:

• Inventory Management Template in Excel
• Automatic Replenishment Planning by SKU
• Master Data capacity: 1000 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