Free Consolidated P&L Template For Excel & Google Sheets

‍How To Use This Consolidated P&L Template?

Open the Template
Click on "File" -> "Make a copy" - This will create a copy for your own Google Drive.
Connect your Google Sheet to LiveFlow so that you can connect live Profit and Loss reports. If you are already a LiveFlow user, go to "Extensions" -> "LiveFlow." If you're not a user yet, then install LiveFlow first.
Integrate your QuickBooks accounts.
Import a Profit & Loss report using the date range "This Year" from all of the respective entities that you want to consolidate.
Now, copy/paste your Profit and Loss Accounts (chart of accounts) from your parent company into the Dashboard tab.
Replace the values in cells B10 and B11 with the names of each of your P&L sheet tabs.
Your formulas will now automatically be connected, but make sure to drag them down if they're not covering all the cells you're looking to cover.

We HIGHLY recommend watching our step-by-step video guide above that explains all of the steps needed to create your consolidated P&L dashboard. The video takes ~ 10 minutes, and investing these 10 minutes will save you hours of work down the line.

If you would like to receive personal 1:1 help from our team, then book a call completely free of charge.

What's inside the Consolidated P&L Template?

1 - The Dashboard Tab

This tab consolidates your Profit and Loss numbers from all of your various entities.

In column A, you will get an overview of all your Profit and Loss accounts.
In columns B & C, you will list each of your legal entities. If you'd like a specific parent company to go first, feel free to arrange it for your needs.
In cells B10 and B11, you will find two input fields. Here you will add the sheet name for each of your underlying Profit and Loss reports.
In cell D13, you will find a date-picker that allows you to change the reporting month.

Consolidated P&L Template - Google Sheets & Excel — *1) The Consolidated P&L Dashboard Tab*

2 - The P&L - Acme US tab

This tab contains Profit and Loss numbers from our first legal entity, Acme US. When you download the template and connect live data from QuickBooks, you will replace our Acme US tab with your own Profit and Loss report.

Profit & Loss Report — *2) Profit & Loss report from our first entity, Acme US.*

‍

Quick note:

The Profit & Loss numbers are, in this case, automatically connected to Google Sheets with LiveFlow - a tool that helps you to connect your financial reports from your QuickBooks to Google Sheets and automate your whole reporting process.

3 - The P&L - Acme Inc. tab

This tab represents our 2nd legal entity, Acme Inc, and the Google Sheet tab includes a Profit and Loss statement from that entity. Similarly to the above, when you connect your live Profit and Loss report, you will replace this sheet with your own P&L report.

‍

4 - Months

The months listed here are the ones that you'll see in the dropdown menu on cell D13 on the Dashboard tab. If you add additional months, they'll automatically be included.

Profit and Loss Accounts and Months — *4) Months - These goes into our date range picker in the dashboard*

How do the formulas work?

Inside the Consolidated P&L Dashboard, we use an Index/Match formula to pull the underlying values from your Profit and Loss accounts in your P&L reports.

You can find the formula in cell B17 and onwards and it looks like this:

=ifna(INDEX(INDIRECT($B$10&"!$A$10:$O"),MATCH($A17,INDIRECT($B$10&"!$A$10:$A"),0),MATCH($D$13, INDIRECT($B$10&"!$10:$10"),0)))

Yes, that is a complex formula, but it's frankly really simple.

Using Index, we look into your underlying P&L report to index all of its values (P&L accounts, months, revenues and expenses), total revenue, profit margin, and more).
Once all the values are indexed, it matches those on two dimensions:
1) The Profit & Loss account, which can be found in column A. If your account is named "Retail Sales" like in our example here, the formula looks for that name in your Profit & Loss report and tries to find a corresponding match in the Consolidated P&L Dashboard.
2) The date range you specify in cell D13 will determine which month's Profit and Loss report the formula looks at. For example, if you select Jan 2022, the formula will automatically go and look inside that report for the value for Jan 2022 and for the corresponding account name.

If you are not already familiar with the Index/Match formula for Google Sheets and Excel, then we highly recommend learning it. It will save you weeks of work in the long run. Here is an outstanding video that explains Index/Match step-by-step.

Consolidated P&L 101

Introduction

Thanks for taking the first step to learning more about Consolidated Financial Statements and how they play into the core consolidation reports stack.

‍

In this post, we’ll outline all the details you need to know about a Consolidated Income Statement, what to look out for, and how to create your own.

‍

We’ll also give you a few uncovered tips that will make your workflows 10x more efficient.

‍

What is a Consolidated P&L (Profit & Loss)?

A consolidated P&L Statement aggregates multiple Profit & Loss statements from different legal entities or subsidiaries of the same company.

‍

Let’s say that you have the following entity structure of your company group:

You’ve got a US-based holding company
You’ve got a UK-based subsidiary wholly owned by the US holding company
You’ve got a German subsidiary wholly owned by the US holding company

Now, to comply with local financial reporting requirements, you need to report on your P&L separately in both the US, the UK, and Germany - and as a result of that, your Profit & Loss statements will be generated independently and in different currencies.

You also need a Consolidated P&L Statement pulling together all your financial data from all three entities. In fact, you also need a separate Balance Sheet and Cash Flow reports, and a Consolidated Balance Sheet and Cash Flow report as well.

These reports are vital pieces to determine your financial position and help you grow.

How do you create a Consolidated P&L?

Creating a Consolidated P&L can be difficult, time-consuming, and manual - Especially when you’re consolidating numbers from different currencies.

Luckily, we’ve made it faster and easier for you with our Consolidated P&L Statement Template, which gives the core building blocks of a Consolidated Income Statement.

Who should create a Consolidated P&L?

Companies with multiple legal entities should create Consolidated Statements and, therefore, a Consolidated P&L. It doesn’t matter whether your legal entities are all based in the same legal jurisdictions or not; you’ll need consolidated financial statements no matter what.

At FA/FFA level, it is assumed that control exists if the parent company has more than 50% of the ordinary (equity) shares – i.e., giving them more than 50% of the voting power.

Get the template for free

Consolidated P&L without manual consolidation

Consolidated P&L Template For Excel & Google Sheets

Learn how to set up this template in the video below

‍How To Use This Consolidated P&L Template?