Creating Creditor ageing report using Pivot Table sample 1 pg 88 89 90

Creating 'Creditors Ageing' Report Using PivotTable Operation

An alternative and easier way to create a Creditors Ageing report is by using Pivot Table function.

Sample 1

	A						B
1
2
3	Sum of RM–CREDIT 
4	CREDIT AGE GROUP		Total
5	0-30					(1,057,766.61)
6	30-10					[1,243,035.98)
7	60-90					(2,603,497.82)
8	90-120					(1,147,774.63)
9	NR						(71,195,278.65)
10	Grand Total				(77,287,353.70)
11

The sample shows the total values of each credit-age group. Here are the steps to produce the above report:

1. Click any non-empty cell in the data area in the sheet DATA. (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

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

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

6. Click Next.

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

88

8. Excel will automatically identify the data area for Range) and till the address in the box on the right of the Range label. If your data structure follow 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 proceed to the 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 Bee worksheet.

12. Click Layout.

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

14. Drag the button CREDIT-AGE GROUP (partly hidden) into the ROW box and drog it there. Drag the button RM-CREDIT 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. You should see a Creditors Ageing report as above in a new sheet.

Effectively, this operation takes the credit age group in the cobus CREDAT MIG GROUP and gets its total from column RN-CREDIT)

89