fbpx
Invoice Report to print

Invoice Template in Excel

Brief

Download Invoice Tool

Please note that the use of the free version for commercial/ corporate purposes violates our terms of use. This version of the tool is only intended for personal purposes. For corporate use of this tool, kindly refer to our commercial version.

              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.

Join thousands who have taken the next step in improving operational efficiency and insight analytics with GPetrium solutions.

100 +
and counting

What is The Excel Invoicer?

              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.

Invoice Tab

Invoice report - explanation on how to use
  1. User needs to manually change this information to conform to their own brand’s name.
  2. Auto-populates [Invoice ID], [Customer ID], [Issue Date] and [Due Date] according to information provided in the ‘Dataset’ tab.
  3. Auto-populates [Name], [Company Name], [Street Address] and [Phone] based on information gathered from the ‘Dashboard’ and ‘Billing Address’ tab.
  4. It auto-populates the Description, Days(Unit), Rate and Amount if the ‘Invoice PDFER’ column in the Dataset table is populated with a ‘Yes’. Note: There is a 11 row limit to the number of items in an auto-populated invoice.
  5. Discount: If discounts can be found within the requested range, it will auto-populate this segment.
  6. Notes: Can be manually changed as needed.
  7. Terms & Conditions: Can be manually changed as needed.
  8. Support Area: Provides a guideline of where the data is being taken from to auto-populate the invoice.

PDF Printer [CTRL+P]

              After the tool is set-up, the PDF Printer preview will look similar to this:

Invoice Report to print

Dataset Tab

              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.

Dashboard Tab

              This tab is used to get a Data Visualization insight into the current status of invoices.

  1. Invoice Status: Provides a quick overview of the amount that is Open, Paid, Partial Payment or Overdue. An example is that users can analyze whether a high percentage of invoices are overdue, by how much (using slicers), whether specific clients are problematic in this regard and whether there are steps that can be taken to decrease the payment cycle.
  2. Total Invoice by Due Date: Quick overview of the amount invoiced (as per due date) throughout the months and years. This can help the firm better understand the revenue/invoice cycles to help define whether there may be an upcoming cash flow downfalls that should be taken into account in the finances. It can also compare different years to understand invoice/revenue trajectories.
  3. Slicers: [Company], [Service/Product Line], [Invoice Status], [Years] and [Due Date’s Months] these slicers can be used to dig into the visualizations to better understand what is happening to the invoices. For the [Company] & [Service/Product Line], there is a drop-down option to the right of the slicer that can be used when the number of items become too large.
  4. Top 5/10 Sales by Service/Product Line: Tied to the Service/Product Line column in the ‘Dataset’ tab, it can help users understand the key revenue drivers within the invoices. This information can help guide decisions such as (E.g. Are there service/product lines that are underperforming? Are there steps that can be done to improve them?)
  5. Invoice Total by Major Clients: Provides a view of the major client invoices. This information can help guide sales efforts (E.g. Should we search for other clients to decrease our dependency on one major client?), better understand whether some clients are often late to pay the invoices, etc.

Billing Address Tab

              ‘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.

  1. Customer ID: A unique number used to link the ‘Dataset’, ‘Invoice’ and ‘Billing Address’.
  2. Customer Name: Client Point of Contact (PoC) for the invoice.
  3. Company: The Company tied to the invoice.
  4. Street Address: Self-explanatory
  5. City: Self-explanatory
  6. State/Province: Self-explanatory
  7. Country: Self-explanatory
  8. Postal Code: Self-explanatory
  9. Phone: Self-explanatory

Conclusion

              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.

FAQ

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.

Adding or deleting cells in excel
Receive a monthly newsletter with updates, insights and solutions from GPetrium!