Invoice Manager Excel Template – User Guide

Posted on

This article provides step by step instructions on how to use the Invoice Manager Excel template.

Product page: https://indzara.com/product/invoice-manager-excel-template/

 

Step by Step Instructions

Enter Invoice data in Invoices sheet

Invoices Sheet
Invoices Sheet

The fields required are

  1. Invoice Number: Unique identification of invoices. The Invoice number should not be repeated.
    1. You can have any method to come up with invoice numbers. For example, you can start with invoice number 10001 and then each new invoice, increment by 1. 10001, 10002, 10003, etc.
  2. Customer: Name of the customer to whom the invoice was sent to. It is important to be consistent with the customer name. When payments are made by customer, we will be entering them based on Customer Name and thus being accurate and consistent with Customer Name is important.
  3. Invoice Date: Date of issue of Invoice
  4. Invoice Amount: Total amount on the invoice that Customer needs to pay
  5. Due in N Days: Type 30 for the due date of invoice in 30 days after Invoice Date. For example, an invoice issued on Jan 1st will become due on Jan 31st, if you put 30 in the DUE IN N DAYS column. Depending on the period for each invoice, you can type the appropriate number.
  6. Category: This field is provided so that you can categorize the different invoices. For example, if you are providing two types of service and you want to be able to track them separately, you can categorize the invoices accordingly. Only one category is chosen for each invoice. This will be used in the Dashboard later.
  7. Notes: Optional field where you can type in any comments or notes about the invoice.
  8. Status: This is an automated field which will change color based on the status of the invoice.
Invoice Status Colors
Invoice Status Colors

The four status values possible are

  • PAID IN FULL: If Invoice has been paid in full by payments made by customer
  • OVERPAID: if paid amount Is greater than invoice amount
  • CURRENT: If it is not paid yet, but the due date is in future – in other words, not due yet.
  • PAST DUE: If it is not paid and the due date has passed.

Now, let’s enter our first invoice data.

Important: Start by typing in cell A4.

If you are new to Excel tables, please read the article on Excel tables before using this template. It is extremely critical that you are entering data correctly in the tables.

Entering First Invoice Data
Entering First Invoice Data

In the above example, I have entered an invoice dated 5th Jan 2020 for an amount of 350 due in 60 days after invoice. Today is March 21st (as of writing this tutorial) and as of today, the invoice is past due date and we have not entered any payment yet for this invoice. Hence the status is Red in color.

To enter a second invoice, you would enter a new invoice number in cell A5.

Entering Second invoice
Entering Second invoice

The fields that are required will have a red border if left empty. This serves as a visual reminder to enter those values.

To enter third invoice, you will enter a new invoice number in cell A6.

This is critical as Excel tables auto expand when we follow this method. If you directly go and enter a new invoice number in cell A10 (for example), that will not be included in calculations.

Pasting Data from Another Source

If you are pasting invoice numbers or data from another spreadsheet, please take extra care in pasting.

  1. Ensure the order of columns of data you are pasting align with the columns in this table. For example, please do not paste Invoice number in Invoice Amount column. The columns cannot be altered like that.
  2. When pasting please paste as values. Do not paste the data with Ctrl+V or paste default.
    1. Right click on cell A4 and choose Paste Values icon as shown below.
paste as values
paste as values

After entering my second invoice, it looks like this.

Second Invoice Data Entry
Second Invoice Data Entry

After third invoice, it will look like this.

Third Invoice Data Entry
Third Invoice Data Entry

You can continue to enter up to 5000 invoices like this in the sheet. It is easy to extend even beyond that. The Excel file may become large and you may prefer to start over for the next 5000 invoices.

Entering payments

In the Payments sheet, we will enter payments made by customers in the table. The steps and guidelines for data entry are like the ones we followed in Invoices sheet. So, we will focus here on types of payments.

Full Payments

If the customer chooses to pay the invoice amount in full, with one single payment, the data could be entered as shown below.

Full payment example
Full payment example

Multiple payments (Partial payments)

As an example, I have entered 2 payments as shown below.

Multiple Payments for one invoice
Multiple Payments for one invoice

In this case, Customer ‘First Customer’ made payment of 300 on 4th Feb and remaining 50 on 10th Feb. Both were for same invoice# 1001.

Bulk Payments

Sometimes, customer may make payments across two invoices. They are referred to as Bulk Payments in this template.

Bulk Payment Example
Bulk Payment Example

Let’s take the same ‘First Customer’. Instead of making the 50 remaining balance on Invoice # 1001, let us assume the customer made a payment of 100 as shown below.

In this case, we don’t enter an Invoice number in the Invoice number column. That’s because it is a bulk payment – a payment that goes against more than one invoice. Here, the payment of 100 should be used against invoices 1001 and 1003.

Bulk Payment vs Invoice Payments

The template will automatically apply the bulk payment against open invoices using a ‘first in first out’ rule. Whichever invoice (for the specific customer) appears first in the Invoices sheet will be closed out before applying against the invoice that appears next. The order of entry in the Invoices sheet dictate the payment distribution.

If you need to specifically control which invoices the payment should be applied against, then enter them as invoice payments instead of bulk payments. For example, the same payment of 100 can be split to two invoice payments of 50 as shown below.

Converting bulk payment to invoice payments
Converting bulk payment to invoice payments

Invoice Overpayment

If the customer makes a payment greater than the invoice amount, it will appear as Overpayment in status.

In this case, the payment was 400 was made for invoice # 1001.

Overpaid Amount for Invoice
Overpaid Amount for Invoice

Since the invoice amount was only 350, the status is blue in color indicating overpayment.

Invoice showing overpaid status
Invoice showing overpaid status

The additional amount of 50 will not be used to pay off other invoices.

It will be listed as overpaid in the customer report.

Overpaid amount in Customer Report
Overpaid amount in Customer Report

It is recommended that you update the Payments table to reflect as shown below. 350 for invoice #1001 and 50 as bulk payment.

Correcting overpayment
Correcting overpayment

This would ensure that there is no overpayment for the customer in the report.

Updated Customer report
Updated Customer report

Overpayment of Bulk Payments

In the above case, the customer had another invoice to which the bulk payment was applied. What if the customer didn’t have another open invoice?

In such cases, the customer report will show the extra amount in the top right section as shown below.

Extra Bulk Payments in Customer Report
Extra Bulk Payments in Customer Report

This is the amount you will owe to the customer, since the customer has made extra amount as payment. If a new invoice is issued to customer, then this extra bulk payment amount will be used to pay off the new invoice.

Customer Name validation

If the Customer Name entered for an invoice payment does not match the name in the Invoices sheet, a red border will appear on the payment record.

Invalid Customer Name Entry
Invalid Customer Name Entry

Refresh Calculations

The template uses pivot tables and hence, after entering invoice or payment data, we need to refresh calculations.

In the Data ribbon, click on Refresh All.

Refresh Calculations
Refresh Calculations

In the Payments sheet, you will see a Payment trend by Month.

Payment Trend by Month
Payment Trend by Month

DASHBOARD

In the Dashboard sheet also, the data gets refreshed due to us refreshing in previous step.

The dashboard is designed to give you insights about customers and revenue for your business.

Invoices Dashboard
Invoices Dashboard

The dashboard shows the following visuals:

  1. Invoice Amount by Invoice Month – Displays revenue trend
  2. Balance Amount by Due Month – Displays when balance amount will be due (future revenue projection)
  3. Invoice Amount by Invoice Category – Identifies categories that bring most revenue to your business
  4. Top 10 Customers by Invoice Amount – Customers that bring most revenue to your business.
  5. Top 10 Customers by Balance Amount – Customers who owe the most to your business now

The dashboard can be filtered by Invoice Date slicer.

UNPAID INVOICES REPORT

The report shows all unpaid invoices – broken down into Current and Past Due invoices. You can use this report as one place to see all invoices past due and take necessary actions to follow up with those customers.

Unpaid Invoices Report - Aging and Invoices due today
Unpaid Invoices Report – Aging and Invoices due today
  • It gets updated automatically as soon as you enter/update data in Invoices and Payments sheets.
  • The Aging of invoices
    • For Current Invoices: Number of days from today to Due Date
      • 0 to 30 days, 31 to 60 days, 61 to 90 days and > 90 days
    • For Past Due Invoices: Number of days from Due Date to Today
      • 1 to 30 days, 31 to 60 days, 61 to 90 days and > 90 days
    • 20 current and 20 past due invoices can be shown at a time. To see the next set of invoices, just type in 21 to see invoices 21 to 40.
See next 20 invoices
See next 20 invoices

CUSTOMER REPORT

Customer Report is designed to be a one page view of all invoices for a customer.

Customer Report example
Customer Report example

You can type a Customer name in the customer input field at the top left.

The first section shows the total invoice amount, paid amount and balance amount.

You can choose to see all invoices or only past due.

Customer Report All invoices vs Past Due
Customer Report All invoices vs Past Due

The report shows 30 invoices at a time. But if customer has more than 30 invoices, you can type 31 instead of 1 to see the invoices 31 to 60.

Next 30 invoices
Next 30 invoices

The report is prepared to be print-friendly and you can print in one page.

 

FAQ (Frequently Asked Questions)

Currency formatting

The template has been designed not to display any one specific currency format, as audience all over the world will be downloading this template.

However, if you need to apply your specific currency format, it is easy.

In any sheet, Press Ctrl+G.

Choose currency cells
Choose currency cells

Select CURR and Click OK

This selects all the cells that are currency cells.

Press Control+1 (control and 1 keys together)

Change Currency Format
Change Currency Format

Select Currency Category. Choose the specific Symbol of your choice and click OK

Now, the currency format would be applied automatically.

Repeat this process within each sheet.

 

How to unprotect?

Dashboard, Unpaid Invoices and Customer Report sheets are protected to prevent unintentional editing of formulas, as that would impact the accuracy of the template. Password is not needed to operate the template with all functionalities as described in the product page.

If you are very familiar with Excel formulas, and want to modify the functionality of the template, you can. Please unprotect the sheets using indzara as password and make changes. Before making changes, please save a back-up copy of the file.

More on how to unprotect in Excel

 

How many invoices can be entered?

The template is designed to support 5000 invoices. If you need to increase it to more, please reach out to support@indzara.com

Leave a Reply

Your email address will not be published. Required fields are marked *