You are safe to download the resources. We've added our digital signatures to the files.
Download this if you have already installed the Microsoft Store edition of Invoice Manager for Excel, and are looking for additional customized templates.
Download this if you want this design to be set as the default template by the installer program.
Screenshots and Images
The printed version.
Open the template in Excel.
The default product report does not include sales representative name. This simple tutorial on customization demonstrates how to add the sales rep name to the product report by using custom fields.
By default, the main contents of the "Product" report come from "Invoice Body" database table, including fields such as Product#, Product Description, invoice#, invoice date, quantity, unit price, line total. It also includes the cost field for each invoiced item.
Note that on the invoicing templates we're publishing here on InvoicingTemplate.com, the term "price" is the money the client / customer pay you for each unit of product / service they purchase; on the other hand, we use the term "cost" for the money you pay to your vendor. In case of selling of goods / materials, "price - cost" results in the gross profit of each unit you sell out. On most of our invoicing formats, you see only the "Price" or "Unit Price" column, because the "Cost" fields are put into a hidden column.
However if you are a business manager you may also find it is helpful to include the sales representative name on the product report, so that you have an easy way to know who sold out what products in the specified date range. So in this simple invoice template, we'll see the detailed procedure on customizing an existing invoice template to add this feature. We'll be using the default sales invoice template shipped with Invoice Manager for Excel setup program as our base invoice template and starting point of the customization.
- Before doing any modifications to an existing template, as always you should first back up it. In this simple sample, we'll modify the database structure too. So you'll also have to make a backup copy of the database file. To do this, in Windows Explorer simply copy the template and database files.
- Open the invoice template.
- Click the Design Mode button on the "Invoice" ribbon tab.
- Choose a blank (not used) area. In this sample, we choose the range at O22:O33 and assign it a slightly different background color.
- Name the cells "oknItemSalesRep_1", "oknItemSalesRep_2", "oknItemSalesRep_3" ... "oknItemSalesRep_12", from top to bottom. That is, O22 is named "oknItemSalesRep_1", O23 is named "oknItemSalesRep_2" and so on.
- Create a formula for each of the cells. For the "oknItemSalesRep_1" cell, the formula should be "=IF(oknProductName_1="","",oknSalesRepName)", and for the "oknItemSalesRep_2" cell, the formula should be "=IF(oknProductName_2="","",oknSalesRepName)", and so on. These formulas check if the product name (the "Description" column) cells are blank, and if not, the formulas retrieve the content of the "oknSalesRepName" cell and fill it into the current cell. "oknSalesRepName" cell is an manually editable cell that supports the on-sheet picker feature supported by Invoice Manager for Excel. When creating an invoice (see tutorial), if you click to select "oknSalesRepName" (at $F$19 in this sample) cell, an on-sheet button appears. Click this button to choose a sales representative name from the list.
- Click the "Design Mode" button on the "Invoice" ribbon tab to exit design mode. The invoice template spreadsheet is now in design mode that allows you to modify the locked contents.
- Click the "Invoices" button on the "Invoice" ribbon tab.
- Click "Custom Fields".
- On the "Custom Field Manager" dialog box, choose "Invoice Body" from the "Database table" list.
- Click "Add Field".
- On the "Add New Custom Field" dialog box, enter the new field name "ItemSalesRep", leave" the "Type" field as the default value "Text". In the "Size" box, fill in the max text length 50. Click OK to create the new field.
- Click "Close" to close the "Custom Field Manager" dialog box.
- Now switch to the "Product" worksheet. By default, it has the following columns: Product ID (cell name: oknRpProductID), Date (cell name: oknRpInvoiceDate), Invoice # (cell name: oknRpInvoiceID), Description (cell name: oknRpProductName), Quantity (cell name: oknRpQuantity), Price (cell name: oknRpPrice), Line Total (cell name: oknRpLineTotal), Unit Cost (cell name: oknRpCost).
- Click to select the "Unit Cost" cell.
- On Excel "Home" ribbon tab, click "Format Painter".
- Click the cell next to "Unit Cost" on the Product worksheet. This copies the format of the "Unit Cost" cell this the new (empty) cell ($J$10).
- Enter the column heading into $J$10 - in this case, "Sales Rep".
- Name the cell "oknRpItemSalesRep". Note in this name, "oknRp" is the name prefix that is used in each name on the "Product" report worksheet; and "ItemSalesRep" is the column / field name in the database definition.
- Save the template by clicking the "Save" button on Excel quick access toolbar.
Now when you create an invoice (see tutorial movie), the sales representative name will be saved along with each item. And when you generate a product report, the sales representative name will also be shown to tell you who sold out each item.
Format and Specification
|Name||Simple Invoice Sample - Sales Rep Name on Product Report|
|Category||Sales Invoice Template|
|Release Date||Saturday, June 25, 2016|
|Format (XLS or XLSX)||.xlsx|
|Line Height (Points)||18.00|
|Papaer Size / Orientation||Portrait|
|Default Margins (Points)|
|System||Windows 7 and later, and Excel 2007 and later.|
April 24, 2020
- Optimized form layout.