Creating Debt Ageing report using Pivot Table sample 2 pg 77 78 79

Creating 'Debt Ageing' Report Using PivotTable Operation

We can easily create a sales debt ageing report (debtor ageing report) using the Pivot Table function.

Sample 1

AGE GROUP

Total

10-30

2,889,964.40

30-60

1,955,195 68

60-90

3,148,617 09

90-120

89.296 20

NR

48,187,089.96

Grand Total

56,270,163 33

Follow these steps to build the above report:

1. Select any non-empty cell in the data area in the sheet DATA (because we want to use data in this sheet). Make sure the data is not filtered.

2. Click menu Data/PivotTable and PivotChart Report.

3. A dialog titled PivotTable and PivotChart Wizard Step 1 of 3 will appear.

77

4. Under the Where is the data that you want to analyze label, select Microsoft Excel list or database.

5. Under the label What kind of report do you want to make, select PivotTable.

6. Click Next.

7. A dialog titled PivotTable and PivotChart Wizard Step 2 of 3 will appear.

8. Excel will automatically identify the data area (or Range) and fill the box on the right of the label Range. If your data structure follows the rules, the data area identified by Excel should be correct (but check the range anyway). If the range is incorrect, click Cancel, correct your data area and start again.

9. Click Next to go to next step.

10. A dialog titled PivotTable and PivotChart Wizard Step 3 of 3 will appear.

11. Under the Where do you want to put the PivotTable label, select New worksheet.

12. Click Layout button.

13. A dialog titled PivotTable and PivotChart Wizard Layout will appear.

14. Drag the button DEBT-AGE GROUP (partly hidden) into the ROW box and drop it there. Drag the button RM-DEBIT into the DATA box and drop it there.

15. Click OK.

16. You will return to the previous dialog (Step 3 of 3).

17. In this previous dialog, click Finish.

18. You should see a debt age report in a new sheet.
Effectively, this operation takes the debt age group in the column DEBT AGE GROUP and gets its respective total value from the column RM-DEBIT.

78

In the above operation, the item NR is included because it is an item in the column DEBT AGE GROUP However, we want to eliminate this item. Follow these steps:

1. In the above report, click on the small downward arrow-head on the right of DEBT AGE GROUP.
A list will appear.

2. Remove the check mark on the left of item NR. 3.

4. Click OK.

5. A refreshed report (without item NR) reappear.

6. The Grand Total also changes. Now it shows the correct figure of all debts amounts (without amounts for item NR).

In the above report, group >120 does not appear because its total value is zero. We can force an item with a zero total to appear. Here are the steps:

1. Right-click on any cell in the report.

2. A floating menu will appear.

3. Select Field Settings.

4. A dialog titled PivotTable Field will appear.

5. Mark the box on the left of Show item with no data.

6. Click OK.

7 The report will refresh with the group >120 (with zero amount) now appearing.

79