Managing Invoices
Help for managing invoices in the customer invoicing template for Excel
The 'Invoice' sheet acts as an Invoice template which can be modified either manually or, in most cases, using the 'Invoice Control' functions to the right. Sections of the invoice template are:
- Invoice Number. This is automatically generated for new invoices as 1 number above the maximum invoice number already saved into the system. It can also be overwritten; however when saving the invoice it will update any order using the same number.
- Invoice Date. The date is set automatically to the current date for new invoices and represents the original date of any loaded invoices. The date can be modified manually.
- FROM. This section loads the information from the 'Company Details' section of the 'Control' sheet. This information can be changed in the 'Control' sheet and cannot be overwritten in the 'Invoice' sheet.
- TO. This section represents the customer details or the invoice recipient. This information can be loaded automatically from any saved customers by choosing the 'Load Customer Details' button to the right, or entered manually. These details can then be saved to the system by choosing the 'Save Customer Details' button to the right.
- ITEMS. This section provides the means to enter product details to the invoice. Product spaces can be added, removed and populated using the option buttons to the right. Product detail can also be entered manually for each item. The shipping method can be selected from the drop down menu which is populated from the 'Shipping Methods' section of the 'Control' sheet. The shipping cost is then automatically populated into the invoice; however this can be overwritten in the 'Custom Shipping Cost' input cell to the right. The amount already paid by the customer can be entered in the 'Invoice Amount Paid' input cell to the right. This is useful for loaded invoices and serves for analysis on unpaid revenue in the reports. Checking the 'All Paid' box populates this cell with the invoice total.
- TERMS. 3 terms for the invoice are presented here. New invoice terms are populated from the default terms specified in the 'Invoice Details' section of the 'Control' sheet. Terms can be added, editing and saved to the system and invoice using the 'Load Invoice Terms & Notes' button to the right.
- NOTES. A note for the invoice is presented here. New invoice notes are populated from the default note specified in the 'Invoice Details' section of the 'Control' sheet. A note can be added, editing and saved to the system and invoice using the 'Load Invoice Terms & Notes' button to the right.
- CUSTOMER APPROVAL. A section is presented at the bottom of the invoice for the date and signature for customer approval. This is provided for printed copies of the invoice.
- SHIPPING SLIP. A separate shipping slip is provided with item details from the invoice for optional shipping and packaging purposes. Data can be manually entered into the shipping slip such as purchase order number, alternative shipping address and transport details.
The 'Invoice Control' box to the right of the invoice provides a menu of automated processes for managing invoices in the system. The options provided here are:
- Create New Invoice. This clears out any invoice information, populates the default information and sets a new invoice number. A check is made to verify whether the existing invoice was saved. If the existing invoice has not been saved, and option is presented to save or not to save the existing invoice before clearing out the information for the new invoice.
- Load Existing Invoice. This option launches a form in order to locate and load an already saved invoice into the template. The form displays two sections: 'Invoice Search Criteria' to narrow the range of invoices and 'Invoices Matched' initially loaded with all invoices and updated by the search criteria. The search criteria presents options to narrow the set of invoices matched based on ranges of invoice number, invoice date, customers, and products.
- Save This Invoice. This option saves the current invoice details into the system. If the invoice number is found in the existing saved invoices, then the invoice is updated. If it is a new invoice number, then the invoice is added to the system. When an invoice is saved product units in stock quantities are reduced accordingly. For updating invoices by this procedure, the product units in stock quantities are modified based on the difference between the new and previously saved invoice details.
- Delete This Invoice. This removes the invoice from the system. If the invoice number cannot be found in the system (for example a new invoice), then a message appears indicating as such.
- Export to New Workbook. This exports the invoice to a blank workbook and displays a message with the new workbook name. The new workbook is left open and unsaved and can be located from the 'Window' menu item in Excel.
- Export to InfoPath XML. This exports the invoice to a Microsoft InfoPath 1.0 XML file. This enables import of the invoice details to InfoPath compatible applications for both the invoicing organization and the recipient.
- Load Customer Details. This option can be used to load an already saved customers details to the invoice. Choosing this option presents a form with a drop down menu of customers and corresponding organizations ordered alphabetically.
- Save Customer Details. This option allows the adding and saving of a new customer into the system. A form is presented with information preloaded from the existing customer details in the template. A check is made whether the same contact name and organization exists and provides an option to update the existing record if this is the case.
- Store New Product Details. This option allows the adding and saving of a new product into the system. A form is launched and pre-populated with the information existing in the first item of the invoice. This enables the saving of manually entered product details in the first item field. The new product can then be selected when populating the invoice item fields. A check is made whether the same inventory code already exists and provides an option to update the existing record if this is the case.
- Add Space for New Item. This appends a new item field to the end of the 'ITEMS' section of the invoice which can then be populated. Product attributes can be optionally added to product descriptions to specify modifications and characteristics of the sale item. Product attribute types and associated values need to be first defined in the area provided at the right in the Products sheet. These can be anything such as size, color, model and serve as an alternative to creating separate product entries for standard product options.
- Populate with Product Detail. This launches a form in order to populate the invoice item fields with saved product information. The form prompts for the item number to populate and the product to use with two drop down select menus.
- Delete Last Invoice Item. This removes the last item from the 'ITEMS' section of the invoice. Keep Only First Invoice Item. This removes all items from the 'ITEMS' section of the invoice except for the first one as each invoice must have at least one item.
- Custom Shipping Cost. This input cell allows the overwriting of the automatically generated shipping cost generated by the 'Shipping Method' drop down menu.
- Invoice Amount Paid. The amount already paid by the customer can be entered in this input cell. This is useful for loaded invoices and serves for analysis on unpaid revenue in the reports. Checking the 'All Paid' box populates this cell with the invoice total. Removing the check from this box replaces the amount paid cell with the value that existed before checking it.
- Load Invoice Terms & Notes. This option launches a form to populate the invoice with saved or custom invoice terms and notes. Invoice terms can be applied to any of the three fields provided in the invoice for terms of sale. Terms and Notes can be selected from saved ones then customized, or entered manually, with an option to save as predefined for later use. The system automatically detects whether the term or note content is new and checks the box to prompt for saving as a new predefined term or note.
- Hide/Show Invoice Sections. Sections at the bottom of the invoice can be hidden or displayed for presentation purposes. Invoice terms, notes, customer approval and shipping slip can be toggled as hidden or displayed using the form provided.
Screenshot: Managing Invoices
){kind=link}