How to Manage a Project P&L with Pivot Tables

A few years ago, I started a new job managing a P&L, and immediately faced a challenge in the shape of the Excel file for the P&L. It was a mess of tabs, with huge numbers of formulas running between them, poor notes, unexplained parts and errors in forgotten corners. It was slow and painful to work with, and we were facing a lot of questions from our execs about the program. We needed insight, lots of it, and fast, and all I had was Excel, reports from various sources, and very little time. Here’s how I left the mess behind and moved to a system based on pivot tables.

This article is for anyone facing a similar challenge and assumes a basic knowledge of Excel, but nothing too crazy. If you come across something you are unfamiliar with, Google is your friend, and a great way to learn!

Why Pivot Tables?

The existing file was messy, but it worked. Why change it? Well, zooming in on particular years, quarters or services meant hopping between tabs, replicating formulae and making tables ad hoc, copy-pasting numbers and writing formulas on the fly. This is slow, and not a good look when trying to answer a simple question from the VP. It’s also error-prone, especially when moving quickly under pressure, leading to embarrassing corrections later. Pivot tables speed everything up and remove much of the room for error. They are more intuitive, needing less effort to use and understand, and let you make charts and graphs more easily.

Data Quality and Excel Hygiene

Before continuing, we should consider two fundamental requirements. Firstly, data quality: if the data isn’t any good, no tables will help, pivot or otherwise. Make sure you have good data first! Secondly, hygiene: your Excel workbooks should be spotless, like your bedroom when your parents are going to see it. What you want to avoid is opening the file after a long weekend and not knowing what something is because you neglected to label it, or you put it in some unusual part of the workbook. Organise, label and document your workbook as if you will need to hand it over to a colleague for a few weeks without being able to explain anything first, and your life will be much easier.

Structuring the data

Sadly, we cannot select any set of data, hit “Create pivot table” and expect the results needed. In my case, the months of the service were arranged as columns, like a project plan. That may work for a monthly review, but when pivoted I got a separate filter for each month, instead of one from I could select the months needed. Adding months was not possible within the pivot table.

In addition, Revenues and Costs were laid out as separate sets of rows, making them impossible to distinguish from each other. They also need to be two separate columns.

This gives us a goal: one row for each discrete element of the P&L, per month, with its respective Revenues and Costs on that row.

To restructure the data, I used the transpose option to rearrange the data. It is critical with such a step to keep an eye on the numbers as you go, checking for errors with spot checks and also checking the totals of each set of data after moving it, basically doing a rudimentary checksum and comparing it with the original data.

Following that, we can expand the data to include any additional features needed, e.g., columns for Fiscal Quarter and Fiscal Year (based on the months), or for groups of elements, e.g. for Business Unit or Technology Groups like (in my case) Services and Hardware. We can also add a Scenario column to distinguish alternative versions of the P&L, such as Actual performance vs. the original Plan. A basic table might look something like this:

Tip One: Grouping Data

For any groups, like both Deployment and Support being included in the wider Services group in the example above, or the months being grouped into quarters and years, we can add a mapping to a hidden tab, so those columns can be automatically populated with a Vlookup when you enter the Item or Month. This speeds up data entry and prevents mistakes.

Tip Two: Format as a Table

After making the basic data table, use Excel’s “Format as Table” option. This means the table and its associated pivot tables will adjust automatically when you later add or remove rows or columns.

When ready, go ahead and make your pivot table!

Calculated Fields and the Pivot Cache

You might wonder why we don’t have a profit margin calculation in the data. That’s because we can calculate margins directly within the Pivot Table using a Calculated Field, simplifying the data and allowing us e.g. to display margins as percentages rather than whole numbers within the pivot table, as needed.

You might also wonder why we use a separate tab to map data, rather than selecting and grouping items directly in the Pivot Table. That could work, but in practice this approach tends to break down over time, with every update needing a time-consuming ungrouping/regrouping, carefully selecting the components each time. This is much easier and faster to manage in a separate tab where you can just add or change a line as needed.

Calculated fields and Groups are both important when considering the pivot cache—a hidden arrangement of the data within Excel which is shared across pivot tables when they are copy-pasted. This means if you have nine pivot tables sharing a pivot cache, and add a calculated field to one of them, it will become available to all of them.

Tables and Charts

Now for the fun part!

After making the first P&L pivot table, for example a high-level overview, we can make alternative views by copy-pasting the pivot table and adjusting it as needed. Of course, we could keep one table and adjust that, but it will save time to have ready any views that will be used frequently. Of course, we need to tailor the approach to our audience: for Service Managers on monthly reviews we can make views with the details for their services in those months, while for execs we can make more high-level overviews of the whole project over a longer term.

As per the example, one useful way of showing P&L data is by displaying Revenues and Costs on an overlapping charge, giving an indication of profitability in each period and highlighting any notable peaks or troughs. Of course, the table can be shown alongside so everyone still has the actual numbers. We can even overlay a line showing the Margin percentage on the other axis, though as I have prepared this example in LibreOffice instead of Excel I have foregone that, since Calculated Fields have yet to be implemented there. Working with pivot tables and charts is notably more clunky in LibreOffice than in Excel so I would not recommend it, though as you can see it is possible.

Reviews and Updates

Be sure to run the tables and charts you intend to use by your business partners and stakeholders, so they can choose what views will help them run the business. After that, you will just need to update your data each month with the actual numbers as they become available (from four different sources, in my case), as well as with any updated forecasts received from your business partners. Don’t forget to hit “Refresh All Connections” to update your pivot tables and charts after each change to the data!

Tip Three: Use a Changelog

After each update it’s a good idea to save a new copy of the P&L file, keeping the history. I’d also recommend having a tab for the Changelog, adding a note whenever you update the file to say what was changed and when. This will help you trace any issues and developments over the years and will help anyone unfamiliar with the file to understand recent changes.

And that’s it! I hope this guide was useful, give me a shout out in the comments if so! And if you have any tips or suggestions, feel free to put those in the comments likewise.