North Star NP was recently visited by Rudy Brynolfson, an independent consultant to nonprofits.  Following a great discussion about Excel Pivot sheets, he agreed to write up a couple of points that can be of benefit to nonprofits.  Check out what he has to say about this under used function in Excel.

Budgets

Pivot Tables let you enter your nonprofit’s budget information on one worksheet, allowing for convenient documentation, ability to import into your accounting system, and flexibility in outputting summaries for Board Member review.

Begin by setting up your data in an Excel table structure that includes columns for;

• Each account segment and program or project (class) code;
• The account description;
• The type of account such as income or expense;
• The report group such as contributions or salaries;
• The current year budget so you can do comparisons;
• The total and monthly amounts to be budgeted in the new year;
• An explanation for each item being budgeted.

Next, create one or more rows for each combination of account number and project (class) code to be budgeted.  Having more than one row per account is good for documenting different items going into it; later you will be able to combine them using the Excel Subtotal function.

Once you have populated all the rows in your table:

1. Highlight the entire data table;
2. Create a pivot table on a new worksheet by clicking on Insert > Pivot Table;
3. Drag the account type, report group, and other desired breakdowns into the row area;
4. Drag the restriction or class code (as desired) into the column area;
5. Drag the budget amount into the data area.

Sample pivot table field list

Sample pivot result

As changes are made in the master data table, clicking ‘Refresh’ on the pivot table will update the summary.  Copies of the pivot table on other worksheets can display the same data broken out different ways, such as by program.

Analyzing General Ledger Data

Using pivot tables to process exported General Ledger detail can provide a flexible way to analyze relationships and trends, such as between different programs or time periods.

Analysis

Start by exporting the General Ledger detail for the desired range of dates and account numbers.

1. Edit the output to repeat the account number and account description in each row;
2. Sort by transaction amount, and delete all non-detail rows;
3. Highlight the entire data table and click on Insert > Pivot Table to create a pivot table on a new worksheet;
4. Drag the account number and account description into the row area;
5. Drag the date or other field (as desired) into the column area;
6. Drag the net debit or credit amount into the data area;
7. Format the pivot table as desired (see general reference on Excel pivot tables);

The above steps can be automated using an Excel macro (obtained free or at minimal cost from the source below) so you can go directly from the exported General Ledger listing to the pivot table without manual processing.  Once you have created the pivot table, you can double-click on any number in the pivot table to produce a new sheet listing the General Ledger transactions making up that amount.

Rudy Brynolfson is an independent consultant to nonprofits and guest writer for North Star NP.