P&L Reporting using Pivot Table

Method to create P&L-type Report(s)

I use this simple and straightforward data collection and reporting system with Tiller which replaces my prior go-to accounting system using Quicken. It is personalized, so I’ll give you my parameters, and if you’re in a similar situation, this may help you.

I want to track:

  1. Household finances
  2. My small business enterprises. I have 3.
  3. Transfers
  4. Uncategorized transactions

Data Collection

To do this, I created a Group for each situation listed. The Groups are

  1. Household
  2. The 3 businesses
    a. EUR
    b. M4SD
    c. SD
  3. Transfers
  4. Uncategorized

The accounts, therefore, are setup as follows

  1. Household. No prefix, just the name of the income and expense accounts. Sub-accounts are separated with colons. Each will report as a single line item. Types are set either as Income or Expense. Examples – There are many more
    a. Soc Sec {myname}
    b. Soc Sec {wife’sname}
    c. Auto: Gas
    d. Auto: Repair/Mainten
    e. Bank Expense
    f. Insurance: Auto
    g. Insurance: Household
    h. Insurance: Health
    i. Insurance: Life
    j. Utilities: Gas/Electric
    k. Utilities: Home Security
    l. Utilities: Pest Control
    m. Utilities: Sewer
    n. Utilities: Water
  2. EUR Accounts
    a. EUR sales
    b. EUR production
    c. EUR Refunds
    d. EUR Website Mgmt
  3. SD Accounts
    a. SD {income type}: XYZ
    b. SD {income type}: Other
    c. SD Interest Income
    d. SD Advertising
    e. SD Event Inc: Pre-reg
    f. SD Event Inc: Donations
    g. SD Event Exp: Refreshments
    h. SD Event Exp: Refunds
    i. SD Event Exp: Professional Staff
    j. SD Event Exp: Accommodations
    k. SD Office Exp
    l. SD Postal Exp
  4. Transfers – all Transfers are setup as Group = Transfers, Type=Transfer
  5. Uncategorized – setup as a Group – Uncategorized, Type = Expense. True, we can have uncategorized income as well. In reality, these transactions will not be set in AutoCat and will need my attention when they arrive. I can categorize them at that time. So, this category may be totally redundant and you can skip it. I put it there because I was unsure how Tiller would handle uncategorized transactions.

With Accounts, Groups and Types set up this way, AutCat transactions are setup to minimize editing Transactions when they download to the Transactions sheet. Tiller instructions will illustrate how to do this.

Reporting

Reporting is done via a Pivot Table. The Pivot Table draws from both the Categories and Transactions sheets. The setup looks like this:

  1. Create a new sheet to hold the Pivot Table. This option is selected when initiating the Pivot Table buildup.
  2. Select the Catagories and Transactions sheets as data source
  3. Only 3 fields of the Catagories sheet are used, Category, Group and Type. From the Transaction sheet, select Amount and Date (Month). We also need ‘Date (year), but it does not display. To get it to display, go to the date label field (Jan, Feb, Mar, etc.) in the report, right-click on the date label, which is any of the months across the top row, select ‘Group’, and select both month and year. When finished, the ‘Date (year) field will appear at the bottom of the Transactions field listing. I renamed it to ‘Select Report Year’ and dragged it into the Filters area of the PivotTable Fields setup, as shown.

Tiller PT Setup1
Tiller PT Setup2

The result is a report that expands monthly as new monthly transactions are added. 3 filters allow you to select the year of the report, the Group included in the report, and the individual month(s) to display. The report looks like this:

This is how I am using Tiller. The reporting is critical for me and is needed to track progress every month and for tax preparation purposes. I don’t know if there is a better way to achieve this, but this particular functionality is something I haven’t seen yet. It may be something for you to consider.

The yearly filtering is neat. It allows me to keep all my transactions from year to year in the same file on the same Transactions sheet. The Account Catagories filtering allows me to view and print only the specific business or Household report I need to view or print. This is very useful to me.

Kipmeister

Hi @kipmeister I moved your topic to the Show & Tell category as I think it’s an interesting solution that others may find useful!

Thanks, Heather. Still learning the ropes on this end.