Skip to content

Order Planning Excel Template

4.75 (4 reviews)

Inventory management tool in Excel. Plan, track & invoice your orders. With printable charts and reports. No installation needed. Ready-to-use.

Download Free Version
One-Time Purchase
Business Subscription

Additional Services



Someka refunds icon

30-Days
Return

Someka one time payment icon

One-Time
Payment

Someka direct download icon

Instant
Download

One-Time Purchase
Business Subscription
SOMEKA BUSINESS 99 $ /m

Redeem 3 Templates

Download 3 premium templates of your choice for free each month.

Continuous Technical Support

We’ll be your one-stop assistant for all your Excel & Google Sheets questions and needs.

Multi-User Environment

You’ll be entitled to multi-user licensing for your templates.

Cancel Anytime | Growing Catalogue | Continuous Updates

Get Inventory Management Bundle
someka-bundle-icon

This template is also included in bundle with special discount! See details >>

TEMPLATE DESCRIPTION

Currently, big companies in the FMCG market are giving importance to focusing on efficiency in their order planning systems. Besides, they are also investing and searching for improvements on their supply management systems with ERP platforms. So, this Order Planning Excel Template aims to connect its modules into an integrated order and invoicing system working.

Order Planning and Invoicing Template for Demand Planning is a solution for your business on Sales and Gross Margin records and guarantee well-functioning inventory management. Not to mention, this tool will give you control over product demand to avoid risks on obsolete stocks or stock-out situations for your business.

Using this Order and Invoicing Template for Demand Planning, you have the chance to create your sales orders by customers, create and save invoices, and release delivery orders according to availability stocks. Here is what you can do:

  • Ability to add or create new products and customers in a simple Master Datasheet with automatic code numbers for each SKU and Customer.
  • According to Master Data input, inventory planning will be automatically calculated in the 24-month planning horizon.
  • In Purchase Order Section, you can import from other systems Purchase Orders by Supplier.
  • You can create Sales Orders by Customers with the automatic Sales Order number.
  • In the Delivery section, you can check and save invoices in PDF format to send to your customers.
  • You can release Deliveries for shipments to automatically update your inventory.
  • Finally, you get many options to check dynamic charts and reports and save them in PDF format.

Order Planning Excel Template Features:

Basically, this Excel template consists of 8 main parts:

Order-Planning-Invoicing-Template-Someka-S00

Now, let’s check the eight main sections of our Order Planning and Invoicing Template:

1. CUSTOMERS

Firstly, you can add or create new Customers by filling the blank fields. Once you do that the template will create an automatic code for new customers only. Here, note that you should not have any duplicate codes when loading Customers’ massive data from other systems.

Order-Planning-Invoicing-Template-Someka-S01

Additionally, if you already have customer data to upload click on “LOAD CUSTOMERS” button to unlock the sheet and then copy your data and paste it as values. Otherwise, you can add a new customer manually by filling in all the blank cells.

For invoice bills, edit and save your Company Data filling the blank fields from Our Company Data table on the right side. This data will appear automatically on all invoice bills you create.

Order-Planning-Invoicing-Template-Someka-S02

2. MASTER DATA for ORDER PLANNING

This is where you input your Master Data for SKUs. You can import your products from another system or create new products by Category and Product Description filling the blank fields. So for massive Product data loading, click on the “LOAD PRODUCTS” button.

Order-Planning-Invoicing-Template-Someka-S03

Moreover, this section works as a simple Master Data module setting up an automatic code when you save a new product. Be careful, not duplicate codes when massive loading of products takes place from another system.

Other important Master Data determines how this template will calculate Product Replenishment and Gross Margin: Min. Order Quantity, Min. Sales Orders, Unit Cost and Sales Price, ABC Classification, and Sales Forecast.

Min. Sales Order

This is the minimum order quantity customers will request in new Sales Orders to release. Usually, this quantity matches with the handle unit for each SKU in your warehouse.

Min. Order Quantity (M.O.Q.)

This is the minimum order quantity that affects all SKU’s requirements in the Planning Section calculated as multiple batch quantities according to M.O.Q.

Unit Cost and Sales Price

We need Master Data to calculate the Gross Margin by SKUs on Reports (Section 7 and 8).

While grouping the Products as A, B or C please note that it is going to make the coverage plans in the Planning section for 1,2, or 3 months accordingly. ABC Classification determines how the template will calculate the safety stock for each SKU.

  1. For “A” products, safety stock will be set on 1-month coverage.
  2. B”, safety stock will set on 2-months coverage.
  3. C”, safety stock will set on 3-months coverage.

Sales Forecast Horizon on 1st month is dynamic. Set the date you would like to set by pressing on “EDIT FORECAST” and then once you click on the first date, you will be able to choose the accurate date from the drop-down list. Sales Forecast determines the expected sales for each SKU on monthly basis.

3. ORDER PLANNING in EXCEL

Here you can check the SKU automatic calculation for replenishment quantities (Planned Orders), Inventory, and Stock Coverage according to master data, Real Sales Vs. Forecast, and active Purchase Orders (Transit).

Order-Planning-Invoicing-Template-Someka-S05

In summary, all you have to do is choose the product from the drop-down button to find it from the list. According to the category you choose in the master data (A, B, and C) the coverage ratio can’t be below 1,2, and 3 accordingly.

Then Orders Planning Template will calculate the Planning Scenario on a 24-month planning horizon.

4. PURCHASE ORDERS

In this section, you can import Purchase Order data from another system. Take in mind, this data will update product inventory as Good Received when Status (Last Column) is set as “Received”. If Status is set as “Active”, purchase order quantity will appear in Planning Section as “Transit”. You should use the same Product Code between both systems, otherwise, Inventory and Transit will not be updated accordingly.

Order-Planning-Invoicing-Template-Someka-S06

It is highly recommended our Second Module “Supply & Inventory Template for Demand Planning” to calculate and import your purchase order data in this Excel template.

5. SALES ORDERS

Create your Sales Orders by Customers selecting the customer from the object list and clicking on “SET NEW ORDER” button. You are able to set up to 20 products by Sales Order according to Minimum Sales Order Quantity, and you can set Discounts % in case of promotions.

Order-Planning-Invoicing-Template-Someka-S07

Current inventory quantity by-product will show up and warn you about any Back-Order situation in case of inventory levels are less than requested customer quantity. In this case, you are able to inform this situation to your customer getting in contact with them

Click on the “SAVE ORDER” button and create Save Order Number automatically. This number will be a priority in the next section at the moment to create shipment and invoice deliveries.

6. DELIVERY & INVOICES

The sale process ends in this section with delivery setting and savings. Just select Sales Order N° from the object list; the template lists all sales orders in the same order of creation, giving priority at the first sales order saved. After that, click on the “FIND SALES ORDER” button and data will show up directly in the invoice bill on the left side

Delivery-Invoice-Someka-S08

All data like Our Company and Logo, Customer Data, Sales Orders Product’s and Quantities and Sales Prices will be loaded automatically in the invoice bill.  Note that other data like Credit Notes and Tax Rates % can be set at the right side filling blank fields. For Credit Notes, value has to be set by clicking on the “CONFIRM” button.

Order-Planning-Invoicing-Template-Someka-S09

In the case of a back-order situation, invoice bill will update according to available stock for each SKU. Otherwise, invoice quantity must be zero in stock out situation and it won’t affect total invoice value.

Moreover, you can save your invoice as PDF format by clicking on “SEND INVOICE TO PDF” and send it via email to your customer. You can also print your invoice for your record documents on shipment or transportation.

The last very important step in the sales process is saving your delivery. Click on “RELEASE DELIVERY” button and the tool will automatically update inventory levels for each product in Planning Section.

When you close the sales process, you can also check any invoice you like from the Consult Invoice Section:

Consult-Invoice-Someka-S10

7. PRODUCT REPORT

In this section, you can check Planning Scenario by SKU. Select a product from the object list. Then the chart and figures will update accordingly when chart’s starting date change. Save the report in PDF format clicking on “SEND TO PDF” button.

Product-Report-Someka-S11

Here you can check inventory levels, planned orders, purchase orders, sales quantities, forecast and stock coverage in 12-month planning horizon. Take into account that the unit of measure of inventory coverage is on monthly basis.

Order-Planning-Invoicing-Template-Someka-S12

The other report in this section lists all portfolio products sorted according to Gross Margin. The main purpose of sorting Gross Margin is to check Absolute Frequency % in order to suggest whether is necessary to change ABC Classification on Product Master Data taking into account the following rules:

  • Absolute Frequency < 80%, ABC Classification will be set as “A” product.
  • 80% < Absolute Frequency < 95%, ABC Classification will be set as “B” product.
  • Absolute Frequency > 95%, ABC Classification will be set as “C” product.

Order-Planning-Invoicing-Template-Someka-S13

8. CHARTS & REPORTS

After all, in this section, you can check all the charts and reports retrieved from other sections and save in PDF format

Firstly, total or Monthly results for Sales and Gross Margin by Category:

Charts-Someka-S14

Secondly, sales Quantity Vs. Forecast by Category:

Order-Planning-Invoicing-Template-Someka-S16

Thirdly, total Back-Orders and Back-Orders by Category:

Charts-Someka-S17

Lastly, Sales by Customer (Pivot Table and Dynamic Chart):

Charts-Someka-S18

Order Planning Excel Template Features Summary:

  • Excel template to perform Sales Orders, Deliveries and Invoices for Demand Planning
  • Inventory Replenishment System in Excel
  • Master Data capacity: 200 SKU and 14 Categories.
  • Customers Data capacity: 1.500 Customers
  • Works on Windows
  • No installations needed, ready to use
  • Dynamic Charts and Pivot Tables
  • Compatible with Excel 2010 and later versions

Order 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! Presentation also includes usage notes, explanations and tips & tricks about the template.

FREQUENTLY ASKED QUESTIONS

USER REVIEWS & COMMENTS

4 reviews for Order Planning Excel Template

  1. Ankica B

    Nice template but I can’t upload my list of purchase orders, after I upload all the info the sheet won’t show it, can you help me??

    • Someka

      Hi Ankica,
      Thank you for your review.
      If you have uploaded all your info to the correct sheet, you just have to click the “import data” button. Have a look at this image for reference:

      Order-Planning-and-Invoicing-Template-review1

  2. Marko

    I love the template it saved me a lot of time at work, thank you

  3. Salman

    I saw your template while I was looking for a template to monitor the purchasing cycle. Also, to have a track of each MR -material request- unless it’s received in the warehouse, in the process, there are few hands involved, from the point of creating requisition until approval and then collection from the vendor and shipping all the way to the warehouse.
    I have purchased this template already because it is almost the one that I am looking for. But, I would like to remove and add some parts of the template. How could I do this?

    • Someka

      Hi Salman,

      Thank you so much for sharing all the details you expect to find in a template. While our development team creates this template, they try to include all necessary steps that the user would like to see. But, of course, it can be customized according to your needs. Please have a look at our customization process: Hire an Excel Expert

      Our team has already contacted you about the parts you would like to add/remove, please check your mailbox.
      Kindly regards,

  4. Tano Stoyanov

    I am interested in the Order planning and invoice template. I have a few questions about it. Firstly is it a regular excel file, which I can copy and use for my different business – trading, product and restaurants (for example)? What is meant by “single user” and “multiple users” – is there some login to it? Lastly, we have many products – around 1500 positions. Is there a limit? If there is one, can’t I just use one excel file for one category and so on?

    • Someka

      Hi Tano,

      First of all, thanks a lot for your interest!

      Yes! We are creating Excel templates. You are free to copy and paste your data into the template. You just have to be careful when pasting the data that is not in an incompatible format.

      The license types are for specifying the user number. If you have already Microsoft Excel on your computer, you don’t have to log in to anything. Please visit this page to learn more about the difference between license types: License Types.

      Once you download the template, you can add rows manually for your products as well. So there is not a certain limit of the products that you can add. You can enter the categories from the “Master Data” section so you don’t have to create another Excel file for each category.

      Hope our explanation helps you! For further questions, you can contact us anytime you needed.

Leave a customer review

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

RELATED TEMPLATES

Search