Setting Up an Invoice with Excelby Keith Evans
Design The Invoice
Before setting up an invoice in Excel, it is first necessary to create a sketch--or, at least, develop a rough idea--of what the finished invoice should look like. Invoices vary considerably from industry to industry and even business to business, but common fields include a company logo, company contact information (address, phone number, website, and email address), customer name and contact information, several lines of product or service descriptions and prices, a subtotal field, a taxes (or fees) field, a total amount due field and a fine-print area where information on warranties, liability waivers and other incidentals can be found. The attached example, courtesy of business consulting company The New Management Group, shows a typical arrangement of these fields.
Separate Each Description Line
In the area of the invoice where products and services, their quantities, prices and costs are detailed, it is a good idea to shade every other row so users can more easily read the invoice. To shade the row, first highlight the cells to be shaded by clicking on the left-most cell, holding the mouse button, and dragging to the right-most cell. When the row is selected, click the "Fill" icon in the tool bar (or click "Format," then "Cells," then "Pattern") and select a light-gray color. Repeat this process for every other row in the details portion of the invoice.
Add a Total Per Item
For each product or service, designate one column to indicate the quantity of items purchased and label this column in the topmost row of the details section; in the example invoice above, column "D" was used for this purpose and the title was placed in cell "D11." Next, designate a column where the price of the product or service will be listed and place a corresponding title at the top of the column (in the example, this information is contained in column "E" and the title is located in cell "E11"). Finally, designate the last column as the "Total" and label it accordingly (the example shows the total in column "F" with the title in cell "F11"). Automate the calculation of the title by using the Excel formula to multiple the price per item by the quantity. Using the example invoice provided, the cell "F12" would contain the formula "=D12*E12" to display the total price of each item. This automation is useful if a customer purchases multiple items or services at the same price; for example, a customer who purchases ten cans of cat food at $1.00 each would see "$1.00" in the "Price" column, "10" in the "Quantity" column, and the total of "$10.00" would be automatically calculated by the Excel formula in the "Total" column. Repeat this process for each line in the details section.
Add a Subtotal Section
At the bottom of the product or service detail section, designate a cell--usually the leftmost cell near the bottom of the section--to display the customer's subtotal (the subtotal is the total purchase price before any taxes, fees or discounts are applied). To have Excel automatically calculate the subtotal, type "=SUM(" into the subtotal cell, then click and drag the mouse to highlight all "Total" cells created in "Add a Total Per Itam" above. When all "Total" cells have been highlighted, press "Enter" and the subtotal will be displayed (the subtotal will automatically update as items are entered in the future).
Add a Taxes Section
In the cells just below the "Subtotal," add (and, as appropriate, label) a cell where taxes will be calculated. To calculate local taxes, first contact the local tax collector to identify the local sales tax rate (for this example, the tax amount of 7.5% will be used). In the "Taxes" cell of the Excel spreadsheet, enter the Excel formula to multiply the "Subtotal" by the decimal equivalent of the tax rate. For this example, assuming the sales tax rate is 7.5% and the "Subtotal" is stored in cell F27, the formula in the "Taxes" cell would be entered as "=F27*.075" After it is entered for the first time, this formula will automatically update with any changes when the spreadsheet is used.
Add a Total Section
To display the total amount due, it is necessary to add the taxes and subtotal calculated in the steps above. Identify a cell where the total will be displayed (in the attached example, the total is displayed in cell "F28"). In this cell, enter the Excel "SUM" formula to add the subtotal and taxes together. If, as the example above shows, the subtotal is stored in cell "F26" and the tax amount is stored in cell "F27," the formula to calculate the total amount due would read "=SUM(F26+F27)" As with the "Subtotal" and "Taxes" cells, the "Total" will automatically update after it is initially created.
Add Notes and Other Information as Necessary
With the financial information added, use the remaining space on the invoice to display information such as return policies, contact information, coupons, customer loyalty messages or any other information that may need to be conveyed to customers. With this information added and the invoice properly formatted and saved, the Excel invoice is complete.