How to build a mini ERP in Excel step by step

If you have a small business or are starting a project and you're struggling every month with loose Excel spreadsheets, accounts that don't balance, and scattered data, setting up a A mini ERP in Excel might be just what you need.You don't need to spend a fortune on complex software to start controlling sales, expenses, customers, stock, and payments with some order and a global view.

The idea is to build a lightweight but well-designed system within Excel (o Google Sheets) that centralizes key information and allows you to compare budget with actual results, review basic indicators, and make decisions every week, without waiting 10 or 15 days for the accounting close. Below you'll see how to set it up, what tables and sheets you need, what best practices to follow, and how to take it to a near-professional level using tools like Power Query and user forms.

What is a mini ERP in Excel and what is it used for?

When we talk about a mini ERP in Excel, we are referring to a structured workbook that centralizes the basic management processesIt includes sales, purchases, inventory, treasury, sales targets, accounts receivable tracking, and a small dashboard. It's not a full-fledged ERP like those used by large companies, but it is a system that eliminates the need for countless loose files and forces you to work with data logic.

The main objective is reduce repetitive work and connect information that is normally scatteredInvoices in one place, customer lists in another, stock in another, bank accounts in another, etc. With a single file, or with several well-linked ledgers, you can generate automatic reports, detect deviations, and react in time if sales slow, if certain customers accumulate too much debt, or if you are running out of stock of a key product.

This type of solution fits particularly well in SMEs and micro-enterprises that need organization but cannot afford a large ERP system or those who want to validate their processes before taking the leap. It's also very useful for entrepreneurs who want to understand, in a highly visual way, how money moves in their project: income, direct expenses, fixed expenses, margin, and cash flow.

Before opening Excel: what are you going to measure and with what discipline?

The first thing is not to create spreadsheets or formulas, but decide which indicators you will track and how oftenIf you try to control everything from day one, you'll end up exhausted and abandoning the tool. It's better to start with a few well-chosen KPIs that are aligned with your budget.

Some very practical basic indicators for a mini ERP are sales (units and amount), number of tickets or orders, productive hours, key direct costs (raw materials, direct staff hours, daily or weekly advertising) and a clear separation between variable and fixed costs. As you become more familiar with the system, you can add more dimensions, such as product categories, sales channels, or margins per product.

For each KPI you are going to track, clearly define the level of detail (daily, weekly, or monthly), the unit of measurement, and who is responsible for entering the dataFor example, you might want to see sales by day, productive hours by week, and fixed expenses by month. The important thing is not to mix levels and for the person entering the data to know exactly what, where, and when to enter it.

Furthermore, it is key to agree from the beginning on a data capture routineA specific time each day or week is dedicated to recording POS sales, bank payments, issued invoices, and expenses. For example, at the end of each day for sales and production, and on Monday mornings to record the previous week's bank transactions. Without this discipline, the mini ERP system looks good but is essentially useless.

Basic file structure: budget and reality

A robust and simple way to set up your system is to clearly separate budget information from actual informationThis way you can easily compare what you planned to do with what actually happened and analyze deviations using specific criteria.

On one hand, create a sheet or file that is your budget baseIt's usually a table with columns like: date or week, concept/account/KPI, budgeted amount, and, if you're interested in more detail, some classification by type of expense or income. You can start with a weekly "budget vs. actual" template and expand it with your own categories.

On the other hand, design a base of Royal operationHere, each row represents a specific event: a sale, an expense, a purchase, a production run, a payment, etc. Typical columns would include: date, transaction type (sale, expense, purchase), description or account, amount, customer or supplier, product, payment method, etc. Later, you can use this table as a source for pivot tables or Power Query queries that aggregate by day, week, or month.

The trick is that Ensure the categories in your actual database match those in the budgetIf your budget includes a line item called "Online Advertising," you can't randomly list that expense in your reality table as "Ads," "Sales," or "Digital Marketing." Define a catalog of line items and stick to it, or your budget vs. actual comparisons will be inaccurate. reliability.

Dashboard and navigation sheet

To make the mini ERP easy for the whole team to use, it's very helpful to create a initial page as a dashboard and navigation menuFrom here you can access the other sections—settings, data tables, reports, etc.—using buttons or hyperlinks, and even export the panel to PowerPoint. dynamic links between Excel and PowerPoint.

It's a good idea to include some things in this control panel General adjustments such as the month the fiscal year begins, the main currency, and the payment term range that you want to use to flag late payment alerts. For example, you can decide that if a customer is more than 15 days late, it will be highlighted in the payment terms report.

In addition to the buttons to go to each section (products, customers, sales, purchases, stock, treasury, profitability reports, etc.), you can reserve an area to display Key indicators in a highly visual formatTotal revenue for the period, gross margin, net margin, outstanding customer balances, and a brief cash flow summary for 7, 14, 30, 90, and 180 days. This transforms the mini ERP into a kind of dashboard that provides a quick overview of your business's health.

Configuration section: categories, users, and parameters

Before you start entering data, it's a good idea to prepare a section of configuration where you define the master lists and global parametersThis part is usually a bit more "boring", but it's what makes everything else flow smoothly and coherently.

In the configuration sheet, you can define product categories, sales representatives, payment methods, types of expenses, secondary currencies and any other catalog you might want to use as a dropdown in your tables. Using dropdown lists (data validation) drastically reduces errors and ensures that the concepts are always consistent.

It's also a good place to settle critical thresholdsFor example, the payment term we mentioned earlier, or a default value for the critical stock level, which you can then specify for each product if needed. Similarly, if you work with multiple bank accounts, you can store the short names you'll use when recording receipts and payments here.

Product definition and basic inventory management

A core part of any mini ERP is the product sheetHere you define everything you buy, store, or sell: physical items, services, subscriptions, etc. Each product must have at least one name and a unique code.

In the product table, it is recommended to include fields such as Product name, code, category, unit of measure, critical stock level (in days) And, if it adds value, the brand or your usual supplier. The code and category will make your life much easier when grouping and analyzing later, and the unit (pieces, kg, meters, packages, etc.) prevents confusion when mixing references.

The concept of critical stock level in days It's very powerful: it's not just about how many units you have, but how many days of sales that stock will cover, given your average daily sales. Later, in the stock table, you can calculate a daily consumption rate based on historical sales and use it to estimate how many days the current stock will last. When that value falls below a critical level, the spreadsheet can display a red alert.

If your activity requires it, you can also provide details warehouse location, expiration date, lot number or part number to manage FIFO (first in, first out) methodologies and have better traceability of each batch of product.

Customer database and risk management

Another pillar of an effective mini ERP in Excel is a well-maintained customer list It functions almost like a mini CRM. Here you not only need the client's name, but also contact information and criteria for financial tracking.

A typical customer sheet structure might include Customer code, name, address, phone number, email address, assigned sales representative And, very interestingly, two key parameters: payment term limit and balance risk limit.

El payment deadline Indicate the number of days you consider acceptable for that customer to pay you. If they exceed that number on any invoice, the system may flag it in a payment terms report. balance risk limit This is the maximum amount you are willing to accumulate as debt with a client; if it exceeds this amount without payment and, in addition, goes beyond its term limit, you should see it clearly marked in your reports.

Working with unique customer codes is a good habit, even if you're not using them right now. The codes facilitate filtering, grouping, and data maintenance (For example, if you change the customer's business name, the code remains the same and nothing breaks.) It's also advisable to avoid modifying the table structure once it's running; it's better to use buttons or macros to sort and clean the database.

Sales targets, actual sales, and performance analysis

Beyond simply recording what has happened, a mini ERP gains a lot of strength when you incorporate sales targets and comparisons with actual salesThis allows you to evaluate the performance of the sales team and see if you are getting closer to your monthly goals.

On a goals sheet, you can enter for each product and for each month the sales amount you aspire to achieveIt doesn't need to be perfect; even a reasonable estimate will give you a good reference point. If you work by categories, you can also define aggregate targets by product family or channel.

On a separate sheet, you enter the actual monthly or daily sales (We will now delve deeper into the structure of sales invoices). Then, using pivot tables or formulas, you can generate a "sales performance" report that visually shows you what percentage of the target you have met by product, by category, or by salesperson, highlighting in color the areas that are below or above the forecast.

If you combine this approach with unit cost and sales price information, you will also be able to analyze not only how much you sell, but how profitable each product line is.This sometimes reveals surprises: best-selling products that barely contribute to the margin, or minority references that sustain the overall profitability of the business.

Product costs, selling prices, and fixed costs

For your mini ERP to have a solid financial dimension, you need to enter three types of economic data: unit product costs, selling prices and fixed costsWith this information, profitability reports make sense.

On the product cost sheet, you can record the unit cost per product and per monthThis is useful because the purchase cost can vary with ThereAnd it's advisable to reflect these variations in margin calculations. If you can estimate future costs, you can even project profitability scenarios several months in advance.

In the sales price sheet, you define the price at which you sell each product or serviceIf you sell at the same price for long periods, you won't have to touch this part very often; if you handle many promotions or rates per customer, then you'll need a somewhat more sophisticated system, but for a mini ERP, the standard price is usually sufficient.

Los fixed costs (Rent, administrative payroll, licenses, insurance, etc.) are typically entered monthly and are considered relatively stable. By inputting this data into the system, your financial reports will be able to show the break-even point, net margin trends, and the proportion of fixed costs relative to sales. It's important to avoid overwriting gray cells or cells containing formulas; generally, you should only fill in white cells, which are designated for data entry.

Sales and purchase invoice record

The heart of the mini ERP is usually in the sales invoice record tableHere you enter information about all your sales, either manually, by importing it with Power Query from a CSV file from the POS system, or from your invoicing program, if you have one.

A standard structure for this table would include fields such as invoice date, expiration date, invoice number, product code and name, customer code and name, quantity, unit, price, VAT And, if you work with batches, a part number to link to the stock. Many of these columns can be automatically populated from the master tables (for example, the unit and product type are retrieved from the product sheet, the customer name is retrieved from the customer sheet, etc.).

In parallel, you can have a sheet of purchase records Use this table to record purchases of goods or services. While some key calculations may not rely on it, it's very useful for traceability and stock control. You can add columns such as tariff code (if needed), expiration date, warehouse location, supplier, and any other information that helps physically locate the goods.

The combination of both tables, sales and purchases, feeds the inventory module and consumption reportsAnd, if you link it well with costs and prices, the gross margin and profitability part per product or per family.

Inventory management and stock alerts

With the recorded purchases and sales, you can build a spreadsheet of stock records that keeps the stock status of each product up to date. Although you can calculate it with formulas, it's usually more convenient to centralize it in a table that displays it clearly.

On this sheet, after selecting the product and, if applicable, the part or batch number, the rest of the data can be automatically retrieved: units in stock, date of first recorded sale, date of last sale, estimated daily consumption rate and estimated days of stock based on that consumption rate. All of this is calculated by cross-referencing historical sales data with warehouse receipts.

Column of estimated stock days This is especially useful because it allows you to see how many days of sales the current inventory covers. If this value falls below the critical level defined in the product details, the system can display a red warning to remind you to place an order. This helps you avoid both stockouts and overstocking your warehouse with slow-moving items.

Monitoring of collections, deadlines and treasury

Selling is great, but if you don't get paid on time, the business suffers. That's why it's essential to include a [missing information - likely a feature or system] in your mini ERP. collections tracking module that records the actual payments you receive from customers and compares them to what they should have paid according to the invoices issued.

You can record on the invoice sheet collection date, customer code, payment number or reference, payment method, amount, expected date of deposit into account, bank and optional notesThe expected deposit date is important because some payment methods (such as POS terminals or online platforms) have a delay between the time the customer is charged and the actual credit to your bank account.

With this data, and by cross-referencing it with the sales invoice table, you can generate current account reports, payment terms and risk alertsFor example, a report that shows you, for each client, how much money they owe you, how much they've paid to date, which invoices are overdue, and how many days late they are. You can also filter by sales representative to see the quality of the portfolio managed by each one.

If you also feed a small leaf of treasury at different horizons (7, 14, 30, 90 and 180 days)This will allow you to anticipate cash flow problems and negotiate more effectively with banks and suppliers. This perspective is complemented by information on operating working capital: accounts receivable, accounts payable, inventory, and prepaid expenses, as well as by tracking capital expenditures (CAPEX), especially if these assets are leased and no longer considered pure operating expenses.

Key reports: financial, profitability and payment terms

Once you have the data well organized, the most rewarding part begins: the reportsIn a well-built Excel mini ERP, it is normal to have at least four blocks of reports: financial summary, sales analysis, product profitability, and payment and risk reports.

El summary financial report This report consolidates your revenue, gross margin (sales less cost of goods sold), and net margin (after fixed costs and other expenses) into a single view. As you add data month by month, this report expands, allowing you to identify trends: strong and weak months, the impact of price changes, the weight of variable expenses, and more.

El analysis of sales invoice records It draws on the sales table and is typically built using pivot tables. You can group by product, category, customer, sales representative, geographic area, or channel, and quickly see where your sales are concentrated. With even a basic understanding of pivot tables, you can create highly flexible views without altering the underlying structure.

El sales target achievement report It focuses on comparing targets with actual sales. It's usually presented as a graphical dashboard, showing the percentage of achievement per product or category. It's especially useful for monthly reviews with the sales team, as it provides a quick overview of who is meeting targets and who needs support.

El product profitability report It's probably one of the most revealing. By cross-referencing selling prices, unit costs, and fixed costs, it shows you which products or business lines are actually generating profit and which might be dragging down your results. Sometimes you discover that a low-volume product is a real gem in terms of margin, or that an entire product range should be reviewed or repositioned.

Finally, the payment terms report It uses the risk and payment term limits you defined in the customer profile to evaluate their payment behavior. The system can answer questions such as: “How often does this customer pay?”, “When was their first and last payment?”, “Are they within the agreed payment term?” and, based on this, issue warnings to those who exceed their risk limit and accumulate significant late payments.

Automation with Power Query, UserForms, and macros

If you want to take a leap in quality and reduce manual tasks, it's well worth using Power Query for structuring and relating tables and user forms (UserForms in VBA) for data entry. It's not mandatory, but it's a natural evolution as the mini ERP starts to grow.

With Power Query you can Import data from CSV files, point-of-sale systems, bank statements, or even other Excel workbooksTransform them (clean columns, standardize concepts, combine tables) and load them into your model without having to copy and paste each time, and, for presentations, you can also take advantage of OLE and DDE dynamic links between Excel and PowerPoint. In addition, you can define relationships between tables (for example, between invoices and customers, or between sales and products) in a very similar way to how you would do it in a lightweight database.

UserForms and macros allow you to build more user-friendly and guided data entry screensInstead of the user directly editing the table, they fill out a form with required fields, dropdowns, and validations, and the system populates the corresponding table with the information. This reduces errors, prevents structural errors, and allows for better control over who accesses what.

Of course, it is important to remember that It is not advisable to modify table structures that are already being used by formulas or queries.If you need to change designs, it's best to do so carefully, documenting existing dependencies, or working with copies until you're sure. You should also consider macro and VBA compatibility, especially if you're going to share the file with users of Mac or older versions of Excel.

Review routine and work with the accountant

A mini ERP in Excel only provides value if it is used consistently and reviewed frequently. Therefore, establishing a short weekly meeting to review the dashboard and make concrete decisions.

In that meeting, instead of getting lost in details, you can focus on Three key points: largest deviations between budget and reality, root cause, and corrective actionFor example, if sales are 20% below projections in a category, you analyze whether the problem is related to pricing, promotion, stock, or competition, and define specific measures. It's very helpful to document these actions in an "action" column and a "responsible party" column within the spreadsheet itself, so that the measurement is directly linked to management.

It's also a good idea to check with your accountant or advisor how shorten the deadlines for delivering financial informationMany firms operate with a delay of 10 days or more in closing their accounts, which is practically useless for making operational decisions. You can implement automation processes and pre-filtering of key accounts ("before adjustments") to have up-to-date data on sales, direct, variable, and fixed expenses, cash flow, and working capital.

If you integrate the information your accountant gives you with your mini ERP, you'll have a a much more complete view: accounting, operational and cash flowThis allows you to control both the short term (treasury and inventory) and the medium term (profitability and growth) with a tool that, in essence, is still just a well-structured Excel spreadsheet.

Setting up a mini ERP in Excel isn't about building a monstrous macro or creating spectacular charts, but about Think carefully about what data is critical to your business, organize it in well-designed tables, automate what is necessary, and review it with discipline.If you structure products, customers, sales, purchases, costs, stock, collections, and reports using the logic we've seen, use tools like Power Query and forms to reduce errors, and get into the habit of reviewing deviations and cash flow weekly over different periods, you'll have a surprisingly powerful system for the size of your company without having invested in a traditional ERP.