Small Business ERP Template for Trading Companies
Excel Small Business ERP Template for trading companies. Integrate the core business processes of your company within comprehensive Excel Template.
ERP (Enterprise Resource Planning) system is a critical component for businesses to manage their accounts, financials, products, sales, and inventory. Small Business ERP Template will take the necessary steps for your company, instead of using separate databases and spreadsheets that have to generate reports!
Small enterprises also use the Excel ERP system to easily identify efficiency improvements of their performance. It reduces repetitive works and helps their businesses to integrate their important processes.
SMALL BUSINESS ERP TEMPLATE FEATURES:
The template consists of 3 main sections:
1. Planning and Feasibility
2. Data Input and Records
Basically, the flow of the template is:
Below, we will explain these 3 sections in detail. But before that, let’s start with reviewing the Dashboard section of ERP template.
This is the navigation dashboard of the tool. You can reach relevant sections by clicking on the buttons.
In the Dashboard, you will see the gray “Settings” button on the bottom. This is where you define your categories, sales reps and so on. Before making your inputs, make sure you fill these tables below so that you can use this data in the relevant sections.
On the right side of this section, you will see a few more micro-settings explained below:
Start Month: Define when you want to start inputting your data. The template will update all reports according to this.
Currency: All values in Excel ERP Template are formatted as a number. Use this setting to update your report titles.
Payment Term Margin: This setting is important for “Payment Collection Tracking”. If a customer pays its balance after this number, you will see a warning in the Payment Terms report.
Now, we can dive into the 3 main sections of this template.
1. PLANNING AND FEASIBILITY
At the most basic level, you will define your products which you purchase, stock and sell.
Product Name and Product Code columns are free text. If you don’t use codes for your products, we strongly recommend using unique codes for your products. Because that’s the best practice for business management purposes.
Product Category: As you will notice, this column includes drop-downs because you will define your categories at the “Settings” section as mentioned before.
Unit: This is how you sell your products (in packages, in kilograms, inches, pieces etc.).
Critical Stock Level (days): For tracking stock levels, you can see Critical Stock Level’s effect at “Stock Records” section. For example, if a product has the lower number of stock then its critical value, you will see the red warning on Stock Records.
Brand: This is free text and used for reporting (filtering, grouping).
Similar to the Product section, you will define your customers and their information this time. This section is a kind of CRM database of your small business ERP.
Customer Code – Name – Address – Tel – Mail info columns are all free text and you can fill them as you wish. If you don’t use codes for your customers, again we strongly recommend using unique codes for each.
Sales Rep will have a selector in here because you will fill this info at “Settings” section before starting.
Last two columns are unique features of this Excel ERP Template and used for payment tracking of your customers. Even more if used right, this can be a very powerful way to manage your business!
Payment Term Limit: This data is important for “Payment Collection Tracking”. If your customer did not pay its balance after this much days, you will see the warning in the “Payment Terms” in the Reports section.
Balance Risk Limit: ERP Template has the safety limit for your customer balances. If their balance exceeds this limit and still did not pay even after Payment Terms Limit, you will see the warning in the “Payment Terms” report.
Warning!: How to Insert/Delete Rows?
You should not! Changing the table structure is not recommended. For this purpose, notice that there are “Product Sort” and “Customer Sort” buttons on the headers. So you can simply delete your product or customer info without deleting it’s all values on the entire workbook, just the data in these tables.
Sales target is for measuring and comparing your current sales in order to achieve your goal. Here, you simply input your monthly sales targets amount for each product.
After defining your targets, you can input monthly actual sales of your products. Together with Sales Targets, you can analyze your sales at “Sales Realization” report section and it will show the efficiency of your sales team.
In this section, you simply input your unit costs per month. This data is being used at Profitability Reports of Small ERP Template. So, if you can estimate the costs for the upcoming months, this will help with better financial planning for your company.
The sales price of your products or service is being defined in this section. Together with the “Unit Costs”, you can see the result of this data at Profitability Reports.
Fixed costs will remain stable throughout all processes of small business ERP. When you are calculating your business profitability, you can see when the business will reach the break-even point or future profit margins. For the upcoming reports, simply input your fixed costs per month.
**As a general rule, always fill in the white cells. Gray columns contain automatic calculations and would not be overwritten manually. Don’t change the gray columns on the leftmost columns.
2. DATA INPUT AND RECORDS
Sales Invoice Records
For all companies, it is important to keep the record of sales invoice in order to prove an agreement between customer. So, in this ERP Template, you will input your invoices and create sales records here. This is a very important section which includes lots of data that will be used throughout the tool.
Invoice Date: This is for tracking issuing date of the invoice.
Due Date: When will this invoice be paid? This data is important for tracking customer balances.
Invoice No: Important for tracking and grouping same invoice items.
Product Code – Name: As you will notice, this is a drop-down menu and you will select your product which you define at “Products” section.
Unit will be retrieved automatically from the Products section. Please make your changes in the relevant section and do not change gray cells.
Party No: This field is used for “Stock Records” section together with “Purchase Records” for better tracking of separate invoices which are purchased together. You can use this field in order to manage “first in – first out” methodology.
Customer Code – Name: As you will notice, this is a drop-down menu and you will select your product which you define at “Customers” section.
Qty: Number of items in the invoice.
Pricing & VAT: This will be filled according to the info in the invoice.
This section is not crucial for the rest of the calculations but you can use this table to record your purchase info.
Here are the explanations for other columns different from Sales Invoice Records:
HS Code: HS code is an international standardized system of names and numbers for the classification of commodities and you can use for information purposes only.
Expiry Date: Some commodities, especially FMCG goods, may have an expiry date. You can input them here in order to track later on. (This field is optional according to your business.)
Warehouse Location: For information purposes only. Let’s you easily track the placement of newly purchased goods.
Stock Records keeps your stock situation for each product you have purchased. After you choose Products and Party No, all other info (gray columns) will be retrieved automatically from other tables.
Daily Consumption Rate is calculated according to the number of days difference between the First Sales Data on Today’s date.
Estimated Stock Days uses Consumption Rate in order to calculate how many days of stock we have for this product. So if this is lower than the critical stock days, then you will see a red warning here.
Payment Collection Tracking
Once you have received the payment, you have to record it for ongoing evaluation of customers. Here, Excel ERP Template will record the actual payments you have received from customers.
Date is when you receive the actual payment.
Customer Code can be selected from the drop-down.
Payment No is a reference field for you to be able to track the payment numbers. (Using a payment reference number is a good practice when communicating with third parties.)
Payment method can be selected from the drop-down. You can manage these items at the Settings section.
Amount is directly the payment amount you have received. The currency here can be changed at the Settings section
Expected Cash-In Date: Some kind of payments are not directly deposited into your bank account and can have some delay. Customers can make the payment but it can be deposited in after X weeks. For this reason, this is important information for cash flow and accounting.
Bank name and Notes fields are totally optional.
Financial Summary Report
Small Business ERP Template consolidates all your inputs such as sales pricing, fixed costs, COGS and reports in here. Basically, these 3 results will appear:
- Gross Margin
- Net Margin
The further you make your inputs the longer this report will show.
Sales Invoice Records Analysis
This report is merely based on your sales invoice records. If you know a little bit how to use a pivot table, you will really enjoy this report and can create further views for your specific needs.
You can click on “+” / “-” buttons to expand or hide details.
Sales Target Reach Report
Sales Target Realization gives you a clear result in a visual dashboard view to see if you reach your sales targets or not.
You can select multiple product or category with the selector on the right.
Checking Account Analysis
This report is quite important. It simply shows after making the sales how much money does a customer owe you and how much they paid so far.
You can filter out specific customers or select multiple of them at the top of the report and select specific Sales Reps to see how much open balance every sales team member is carrying. Because this is an important indication of payment collection performance.
Product Profitability Report
Product profitability report gives you detailed analysis after accounting for all costs and expenses of your product. Some categories may be more profitable than others. Or maybe your company all depends on one product and all others are making you lose money. That’s why product-based analysis is quite important for this Excel ERP Template.
Again here, you can click on “+” / “-” buttons to expand or hide details.
Payment Terms Report
You may remember, we have defined a risk limit for all our partners at the “Customers” section so this report is totally devoted to customer payments.
The calculations in the background consider quite a lot of factors:
- “How often does this customer pay?”
- “When was the first/last payment?”
- “What is our risk limit for this customer?”
- “What is our Payment Term Margin?”
After making these calculations, the report warns you to push some of your customers or give them some more time.
SMALL BUSINESS ERP TEMPLATE FEATURES SUMMARY:
- Unique ERP Template for SMB
- Works both on Mac and Windows
- No installation needed, ready to use.
- Professional design and suitable for presentations
- Innovative Excel ERP System
- Comprehensive Reports
- On Sheet Instructions
- Compatible with Excel 2010 and later versions.