Creating Profit & Loss report using Pivot Table pg 69 70

Creating 'Profit & Loss' Report Using PivotTable Operation

An alternative way to create a Profit & Loss items report is by using the PivotTable operation.

We will use data in sheet TB to build a Profit & Loss report using the Pivot Table operation. More specifically, we will use 2 columns i.e. PL-CATEGORY and RM-NET. The PivotTable function will summarise each unique item (category) in the column PL-CATEGORY with its corresponding totals.

Below are the steps to create the Profit & Loss report using PivotTable operation:

1. Select any filled cell in the sheet TB.

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 What kind of report do you want to create, 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 address) in the box on the right of Range label. If your data structure follows the rules, the data area identified by Excel should be correct (but you can check the range anyway). If the range is incorrect, click Cancel, correct your data area and start again frm step 1 above.

69

9. Click Next to go on to the next step.

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

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

12. Click Layout.

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

14. Drag the button PL-CATEGORY (partly not visible) into the ROW box and drop it there. Drag the button RM-NET 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 Profit & Loss report in a new sheet.

Here is a sample of the report:

	A							B
1
2
3  	Sum of RM-NET
4 	PL CATEGORY					Total
5 	ADMIN EXPENSE				542,007.89
6 	COMPANY TAX					4,637 08
7 	COST OF SALE				5,840,222 18
8 	DIRECT EXPENSE				289,831 31
9 	INCOME						(13,491,982.03)
10 	LABOUR COST					693,216 96
11 	NR							19,159,807 06	
12 	OTHER INCOME/EXPENSE 		(957,274.79)	
13 	UNAPPROPRIATED PROFIT C/F	(11,960,539.28)
14	Grand Total					119,926.38
15

70