Displaying Summary Information on Customer Statement

NOTE: This tutorial is for the Customer Statement report included in the default invoice template of Excel Invoice Manager 2.10.1014 and earlier versions. Excel Invoice Manager 2.11.1015 includes a new Customer Statement. See Two Versions of Customer Statement Report for more information.

NOTE: You can download both templates here.

Starting with Version 2.9.1013, the standard template shipped with Excel Invoice Manager adds a summary section to the Customer Statement report. If you are using a template shipped with the previous versions of Excel Invoice Manager, this tutorial tells you how to enable the feature on your system.

Solution 1: Creating the Summary Section from Scratch
Solution 2: Using a Separate Template

Tip: Make sure you have installed the latest version of Excel Invoice Manager before you begin.

Solution 1: Creating the Summary Section from Scratch

If you are familiar with Microsoft Excel, here is how to add the summary section from scratch into your existing Customer Statement report.

1. Exit Microsoft Excel if it is running.
2. Copy the invoice template you are using. Name the new file Invoice-New Statement.xls or something else you like.
3. Open the Invoice-New Statement.xls template.
4. Switch to the Customer Statement worksheet.
5. Display gridlines and row/column headers. (How?)
6. Drag your mouse to select cell L9:N9. Click the Merge and Center button on the Formatting toolbar in Excel 2000-2003, or in Excel 2007 choose the Home ribbon tab, Alignment group, Merge & Center button.

   
   Merge cells (Click to enlarge)

7. Right-click the selected area, choose Format Cells from the menu. The Format Cells dialog box appears. On the Number tab, choose a date format you like. On the Border and Patterns tab (or Fill tab, in Excel 2007), set the border and background color, respectively.

   
   Format cells (Click to enlarge)

8. Enter a formula "=NOW()" (without the quotation marks) for the newly merged cell.
9. Select cell L10:M10, merge the cells. Enter text Account Balance. Set the border for the merged cell.
10. Select cell L11:M11, merge the cells. Enter text Account Credit. Set the border for the merged cell.
11. Select cell N10. Name the cell oknCsHdrAccountBalance. Set the number format (Accounting) and borders for the cell.
12. Select cell N11. Name the cell oknCsHdrCredit. Set the number format (Accounting) and borders for the cell.
13. Now insert 4 new rows above the report header row that displays Invoice #, Date, Total... Format the newly added section as the following figure.

    
    Create formulas (Click to enlarge)

14. Create formulas as shown in the following table.

    Cell Address	Label	Formula
    B21	Current	{=SUM(IF(oknCsDateTo-C24:C1000 <=30,1,0)*J24:J1000)}
    D21	31-60 Days Past Due	{=SUM(IF( INT((oknCsDateTo-1-C24:C1000)/30)=1,1,0)*J24:J1000)}
    J21	61-90 Days	{=SUM(IF(INT((oknCsDateTo-1-C24:C1000)/30)=2,1,0)*J24:J1000)}
    L21	Over 90 Days	{=SUM(IF(oknCsDateTo-C24:C1000>90,1,0)*J24:J1000)}
    N21	Total	=SUM(B21:M21)
    Note: The first 4 formulas, surrounding by a pair of brackets "{}", are called array formula in Excel. To create an array formula, you first enter the formula without "{}", and then press CTRL+SHIFT+ENTER on your keyboard. For example, for the first formula, you first enter =SUM(IF(oknCsDateTo-C24:C1000 <=30,1,0)*J24:J1000), and then press CTRL+SHIFT+ENTER on your keyboard. Excel does not allow you to create array formula for merged cells. This means that, in the above figure B21:C21 are not merged, and the formula is assigned to B21 only. To display the value of B21 in the center of B21:C21, you format the cells in the following way after assigning the formula: Drag your mouse to select B21:C21. Right click the selected area, choose Format Cells. On the Alignment tab, choose the Center Across Selection option in the Horizontal box. Refer to your Excel documents to learn how the above formulas work.

15. Click the New Report button to generate a new report. Test to see if the newly created summary section works or not.
16. Hide gridlines and row/column headers of the Customer Statement report.
17. Save the template.

Solution 2: Using a Separate Template

If you are not familiar with Excel but still need the summary section to be added to your Customer Statement, simply download the new template and have it connect to your database file. Here are the steps.

1. Click the following link to download the new template.

   standard_invoice_template.1014.zip

2. Open the new invoice template.
3. Click the Settings button on the Invoice worksheet. Go to the Database tab.
4. Click Open Database. Browse to and open the database that you are using.
5. Save the template by clicking Excel menu File -> Save in Excel 2000-2003, or click the Office button and then click Save in case of Excel 2007.
6. Use the new template to generate Customer Statement report.