Order Planning and Invoicing Template
Order Planning and Invoicing Template is a solution for a well-functioning inventory management. Create sales orders, invoices and release delivery orders!
Currently, big companies in FMCG market is giving importance to focus on efficiency in their order planning systems. They are also investing and searching for improvements on their supply management systems with ERP platforms. This Order Planning and Invoicing Excel Template aims to connect its modules into an integrated order and invoicing system working in the same manner as an ERP system.
Order Planning and Invoicing Template for Demand Planning is a solution for your business on Sales and Gross Margin records and guarantee a well-functioning inventory management. The tool will give you control on products demand to avoid risks on obsolete stocks or stock-out situation 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 Data sheet 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 system 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 as 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 as PDF format.
Order Planning and Invoicing 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:
In this section, you can add or create new Customers filling the blank fields. Once you do that automatic code will be created for new customers only. Here, note that you should not have any duplicate codes when loading Customers massive data from other systems.
If you already have a 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 filling in all the blank cells.
For invoice bill, edit and save your Company Data filling the blank fields from Our Company Data table at the right side. This data will appear automatically on all invoice bills you create.
2. MASTER DATA
This is where you input your Master Data for SKU’s. You can import your products from other system or create new products by Category and Product Description filling the blank fields. So for massive Products data loading, click on “LOAD PRODUCTS” button.
This section works as a simple Master Data module setting up an automatic code when you save a new product. Be careful do not duplicate codes when massive loading of products take place from another system.
Others 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 handle unit for each SKU on your warehouse.
Min. Order Quantity (M.O.Q.)
This is the minimum order quantity which affects all SKU’s requirements in the Planning Section calculated as multiple batch quantities according to M.O.Q.
Unit Cost and Sales Price
Master Data required for calculating Gross Margin by SKU’s 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 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.
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).
All you have to do is choosing 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.
Orders and Invoicing Template will calculate the Planning Scenario on 24 months 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 level 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 “SAVE ORDER” button and create Save Order Number automatically. This number will be priority on next section at the moment to create shipment and invoice deliveries.
6. DELIVERY & INVOICES
This is the section where the sale process ends when delivery is set and saved. Just select Sales Order N° from the object list; all sales orders are going to be listed in the same order they have been created giving priority at the first sales order saved. After that, click on “FIND SALES ORDER” button and data will show up directly in the invoice bill at 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 clicking on the “CONFIRM” button.
In the case of 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 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 the sales process has been closed, 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 product from object list and dynamic chart and figures will update accordingly when chart’s starting date change from object list at the right side. Save report as 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 months planning horizon. Take into account the unit of measure of inventory coverage is set 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
In this section you can check all the charts and reports retrieved from other sections and save as PDF format
Total or Monthly results for Sales and Gross Margin by Category:
Sales Quantity Vs. Forecast by Category:
Total Back-Orders and Back-Orders by Category:
Sales by Customer (Pivot Table and Dynamic Chart):
Order Planning and Invoicing 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