Consolidated P&L without manual consolidation

Consolidated P&L Template For Excel & Google Sheets

Create a live Consolidated P&L Dashboard, that updates while you sleep.

Compatible with

Excel

Google Sheets

Integrations

QuickBooks

Live data connector

LiveFlow

How to duplicate this template?

1. Go to File

2. Click on Make a copy

Consolidated P&L Template For Excel & Google Sheets
LIVEFLOW ON AIR
Live Icon

Learn how to set up this template in the video below

Arrow down

How To Use This Consolidated P&L Template?

  1. Open the Template
  2. Click on "File" -> "Make a copy" - This will create a copy for your own Google Drive.
  3. 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.
  4. Integrate your QuickBooks accounts.
  5. Import a Profit & Loss report using the date range "This Year" from all of the respective entities that you want to consolidate.
  6. Now, copy/paste your Profit and Loss Accounts (chart of accounts) from your parent company into the Dashboard tab.
  7. Replace the values in cells B10 and B11 with the names of each of your P&L sheet tabs.
  8. 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.

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.

Profit & Loss Report
3) Profit & Loss report from our second entity, Acme Inc.


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.

  1. 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).
  2. Once all the values are indexed, it matches those on two dimensions:
  3. 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.
  4. 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.


Get started in 3 simple steps

Google Sheets Logo

1. Copy the template

Open the template. Click on File, then click Make a copy and save it to your drive.

Open the template
LiveFlow Logo Icon

2. Connect LiveFlow

Connect your spreadsheet to LiveFlow, so you can import live data to your template.

Connect to LiveFlow
Google Sheets Icon

3. Make it truly yours

Personalize the template and connect live data so you don't have to update it manually.

See how

Wasting time on manually importing data?

Join hundreds of accountants and finance teams automating their workflows with LiveFlow and saving hours of work every week

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.


Frequently Asked Questions
Is the template free?
FAQ ICON
Who built this template?
FAQ ICON
Can I share this template?
FAQ ICON
Can I get help if I need it?
FAQ ICON