Explanation for creating Profit & Loss report using formulas pg 67 68 69

Notice the following:

In cell A1: Write the name of the business.

In cell A3: Write the title of the statement.

In cell A5: Write ACCOUNTS or some other term you like. In the cells below it is a list of the Profit & Loss categories and their accounts. Arrange the items accordingly. Make sure the spelling for each category and account is the same as that used in the TB sheet because we want to extract values from accounts and categories in this sheet.

67
In cell B5: Write NOTES or some other term of your choice. In the cells below it, you can write any notes against each item in the list of category and accounts.

In cell C5: Write YEAR TO DATE or whatever you like.

In cell C6: Write the cut-off date.

In cell A9: Starting from this cell downwards, write the items that you want to appear. Make sure the spelling is the same as that used in column E in sheet TB (unless it is a title).

In cell C8: Write this formula:
=SUMIF(INDIRECT("TB!E5:E2000"), A9, INDIRECT("TB!HS:H2000"))
This formula assumes that there is a account written in cell A9. This formula will compute the total amount of the Profit & Loss account stated in cell A9 wherever its amount is available in sheet TB (Remember that in sheet TB, the subcategory is in column E while the amount is in column H).

Copy this formula down to each cell which has an account stated in column A. The total of each Profit & Loss account in each cell will be computed. (Understanding the SUMIF and INDIRECT functions will enable you to design the Profit & Loss statement differently).

You can use SUM function to get totals for categories in cells C11, C19, C25 etc.

You can copy and paste values in the statement to other sheet for comparison purpose, either to compare with previous month or previous year figures (Formulas cannot produce comparative figures because of the structure of the file).

TIP

Use menu Edit/Copy to copy and then menu Edit/Paste Specials/Values to paste the values only (without the formula).

68



Formatting Certain columns need to be formatted with appropriate format to accommodate proper type of data. Usually, data can be a 'text' type, 'date' type or 'number type Creating 'Profit & Loss' Report Using PivotTable