Order Planning Excel Template
Inventory management tool in Excel. Plan, track & invoice your orders. With printable charts and reports. No installation needed. Ready-to-use.
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:
Now, let’s check the eight main sections of our Order Planning and Invoicing Template:
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.
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.
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.
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.
- For “A” products, safety stock will be set on 1-month coverage.
- B”, safety stock will set on 2-months coverage.
- 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).
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.
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.
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
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.
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:
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.
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.
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.
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:
Secondly, sales Quantity Vs. Forecast by Category:
Thirdly, total Back-Orders and Back-Orders by Category:
Lastly, Sales by Customer (Pivot Table and Dynamic Chart):
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