A well-structured invoice process often leads to a decrease in labor costs, better invoice management, lower error thresholds and even insights on the business itself. Unfortunately, a good structure often means thousands of dollars spent in process development, tools and labor that sometimes provide limited value to the organization and still fails to, for example, ensure that clients are paying within the appropriate time.
Concur estimates that it costs on average $12.90 per invoice with error reduction and automation potentially helping decrease costs by 29%. In this article, we will look at the free invoice template for excel called ‘Invoicer’, a relatively simple solution that GPetrium has created to help businesses gain control of their invoices and to automate some of the steps to cut down invoicing cost.
Excel Invoicer is a free excel invoice tool created by GPetrium to help facilitate invoice management for businesses, consultants and freelancers. The tool is designed to track information to support the rapid creation of invoices. The tool also features a handy data visualization segment that helps guide decision making. The tool is divided into 4 key segments, the ‘Invoice’, ‘Dashboard’, ‘Dataset’ and ‘Billing Address’. The ‘Invoice’ segment is a pre-built, auto-populated Invoice that can be easily printed into PDF or other forms. The ‘Dashboard’ segment provides insight on key variables impacting the overall invoices of the company. The ‘Dataset’ segment keeps an itemized control of all the invoices that have been or will be processed. The ‘Billing Address’ segment compartmentalizes a portion of the invoice information that is used to pre-populate customer information.
After the tool is set-up, the PDF Printer preview will look similar to this:
Although the ‘dataset’ tab may look overwhelming at first, the information required in it is quite intuitive and remains the backbone of the Excel Invoicer.
At the top of the tab, there are 5 slices to help the user dig into different segments of the data at hand. Right below it, there are 18 columns in a table that covers a variety of items that are often needed in the Invoice and Invoice Data Visualization.
1. Slicers: 5 Slicers tied to the table underneath to easily assist the user to filter the data. It covers [Company], [Customer Name], [Service/Product Line], [Invoice Status] and [Invoice PDFER] column.
2. Invoice #: Unique invoice identifier to help keep control of invoices that are created and sent to clients.
3. Customer ID: Unique customer identifier used to connect to other data sources such as the ‘Billing Address’ tab and potentially other external datasets. It can be considered key to maintaining client data privacy (E.g. GDPR) and cybersecurity within the firm. This information is then transferred into the ‘Invoice’ tab if the right conditions apply [Right conditions is defined by a ‘Yes’ on the Invoice PDFER column].
4. Customer Name: Client Point of Contact (PoC) for the invoice. This information is automatically populated by searching for the Customer ID in the ‘Billing Address’, and then transferred into the ‘Invoice’ tab if the right conditions apply.
5. Company: The Company tied to the invoice. This information is automatically populated by searching for the Customer ID in the ‘Billing Address’, it is then transferred into the ‘Invoice’ tab if the right conditions apply.
6. Service/Product Line: An internal identifier of goods/services to help keep track of amounts sold.
7. Item: Description of a good or service to be paid for by the client. It shows as ‘Description’ within the ‘Invoice’ tab if the right conditions apply.
Days/Hours/Units: a drop-down header of a column used to define the number of units that have been sold for the described item. Both the header and the number are auto-populated into the ‘Invoice’ tab if the right conditions apply
9. Rate: The per unit value of the item in that row. It auto-populates the rate in the ‘Invoice’ tab if the right conditions apply.
10. Issue Date: The date in which the invoice is expected to be issued to the client, it is then transferred into the ‘Invoice’ tab if the right conditions apply.
11. Invoice: Auto-populated formula to total the amount in Units * Rates.
12. Discount: This segment is meant to be populated whenever that line item has a discount that will be provided to the client. The discount is auto-populated into the last row of table in the ‘Invoice’ tab if the right conditions apply.
13. Late Fee: An internal control of fees in case the invoice receiver fails to pay on time.
14. Tax: Auto-populated formula that considers the ([Invoice]+[Discount]+[Late Fee])*Tax Rate, where Tax Rate is determined by the Config Tax Table in the ‘Input’ tab. This version of the tool considers only one tax rate. Some clients have requested for a multi-country/state/province tax system. Reach out to us for any inquiry.
15. Due Date: The date in which the client is expected to pay for the invoice. The due date is auto-populated into the last row of table in the ‘Invoice’ tab if the right conditions apply.
16. Total Paid: Auto-populated formula that totals the row amount from [@Invoice]+[@Discount]+[@[Late Fee]]+[@Tax] columns.
17. Invoice Status: A drop-down line containing Open, Partial Payment, Paid, and Overdue. It helps to maintain control of the invoice status.
18. Invoice PDFER: This column is very important whenever an Invoice is expected to be processed. Every row that contains a ‘Yes’ is considered part of the Invoice. The current version does not stipulate errors (added an extra Yes), so make sure that only the right items are marked as ‘Yes’. The slicer at the top can help the user keep track of that.
19. Comments: A cell used to write any relevant comments regarding that line item.
This tab is used to get a Data Visualization insight into the current status of invoices.
‘Billing Address’ is used to keep track of the billing information for each client. This information is then used to connect the [Customer ID] into the ‘Dataset’, which then helps to auto-populate the ‘Invoice’ tab.
A good invoice process and solutions can be detrimental to a smooth invoicing system. Excel Invoicer provides a baseline for clients of all sizes to move in the right direction by automating segments of the Invoice process, gain insight on invoice data all while using a single, widely available tool. There is plenty of space to improve the tool by automating e-mailing, providing error handlings, keeping track of the number of times a client has been reached for payment and others.
Q: I have cleared all the data from the ‘Dataset’, but unfortunately the formulas in the pink columns disappeared. What do I do?
A: The best option is to Right Click->delete all the table rows with the exception of the first row on the table, then repopulate the first row with your data. This is step is only required once.