skip to Main Content

Supply Planning Excel Template

4.75 (8 reviews)

Supply and inventory planner in Excel. Plan and track your orders by SKUs. Fully automated dynamic charts and tables. Ready to use.

Learn more about License Types


Someka refunds icon

30-Days
Return

Someka one time payment icon

One-Time
Payment

Someka direct download icon

Instant
Download

TEMPLATE DESCRIPTION

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:

Supply-Inventory-Planning-Excel-Template-S00

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.

Supply-Inventory-Planning-Excel-Template-S01-1

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

Supply-Inventory-Planning-Excel-Template-S02

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

Search-Product-S03

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:

Planned Orders-S04

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.

Supply-Inventory-Planning-Excel-Template-S05

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.

Supply-Inventory-Planning-Excel-Template-S06

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.

Supply-Inventory-Planning-Excel-Template-S07

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.

Charts-S08

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: 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

Supply Planning Template is a ready-to-use Excel Template and provided as-is. If you need customization on your reports or need more complex templates, please refer to our custom services.

PRODUCT VIDEO

Watch the video below to see the template in action! Presentantation also includes usage notes, explanations and tips & tricks about the template.

FREQUENTLY ASKED QUESTIONS

USER REVIEWS & COMMENTS

8 reviews for Supply Planning Excel Template

  1. Steph

    is it possible if I can directly copy-paste the list of suppliers and Sku’s in the master data rather than creating one by one?

    • Someka

      Hi Steph,

      If you have already a data set for your suppliers or products you may Copy and Paste as Value your data and then Load them by using editing buttons. You do not have to input your products one by one.

      Supply-and-Inventory-Planning-Template_review

  2. Elif Mustaffa

    the template seems good but I cant change the available options in the master data can you help me

    • Someka

      Hello Elif,
      The options and settings in the master data tab are generally locked to prevent changes from the user that may break the template.
      After purchasing, if you need to unlock it, you just have to use the password provided with your purchase.
      Also, you can contact our support team if you need help with self customization.
      Just send an email to contact@someka.net and detail your needs.

  3. Anna D

    This template helped me have me inventory neat and organized it save a lot of time worth the money I paid

  4. Marita M

    very useful for our planning. but want see row number and adding new sheet

  5. Osborn Moore

    Hello, the “add new supplier&product etc ” part is very creative.
    It is motivating to add information such as filling out the form, instead of filling out the cell in Excel.

  6. Mike Schrob 

    Hi. I am interested in your Inventory and Planning worksheet to help me manage my purchase plans.
    I currently manage about 50 different products from 5 suppliers. I maintain purchase orders for each product that extend 7 months.
    On the Planning screen is it possible to view the open PO’s to be received each month?

    • Someka

      Hi Mike,

      Thanks a lot for your interest!

      Our Supply and Inventory Planning template consists of 6 sections. There is a separate section which name is “Purchase Orders” to create a new PO and see what you have created before. That’s why there is not information about PO in the “Planning” sheet. You should track all of the PO from the section we have mentioned in case you needed.

      You can also apply our customization process if you want to add PO information and details on the planning sheet.

      For further questions please do not hesitate to ask!

  7. Dayan

    It so amazing

  8. Amine Kafal

    I don’t leave comments usually but this is worth it . Someka, in my opinion as a business analyst and a developer provide Some of the best excel tools I’ve work with and customize so far. Either you’re a beginner or advanced excel user, you can easily use the tools as is or customize them to your specific needs of you need to. Comments provided really help you navigate and learn the tools but if you need to customize or enhanced any of these tools to your business requirements, it’s pretty straight forward if you have already some excel/vba knowledge – thank you and amazing work Someka.

    • Someka

      Thank you for your kind words Amine! This made our day!

Leave a customer review

Only verified users who have downloaded and used the template may leave a review.

RELATED TEMPLATES