Business unit plan template in Google Sheets

business-unit-analysis-template

Written by Laura Tennyson

Nov 2, 2020

All companies encompass several business units (or departments) such as accounting, sales, marketing, R&D, and production. And in a multidivisional organization, each unit may even function semi-independently as a business-within-a-business (BWB). This is a common business structure for chains, hospitality businesses, and parent companies with multiple subsidiaries.

In organizations of this kind, each business unit is usually responsible for planning and controlling their own budget. As well as analyzing performance internally, department managers are also responsible for reporting their unit’s finances to company directors.

Two widely used indicators of a business unit’s financial health are EBIT (earnings before interest and taxes) or EBITDA (earnings before interest, taxes, depreciation, and amortization). EBIT reflects the amount of income a company is generating, whereas EBITDA approximates a company’s cash flow.

Calculating EBITDA

You can calculate EBIT/EBITDA and analyze the profitability of your company or individual business units with the help of an accountant or accountancy software. But both of these options are expensive and you don’t actually need to invest any more resources into this process.

Most likely, each unit is already measuring fixed and variable costs, income, and expenses. The major challenge is simply collecting and collating that data correctly. With the help of a Google Sheets template, you can set up an automated system that creates a business unit performance report for you.

This business unit plan template can be used to analyze different units, subsidiaries, or projects. Perhaps you’re an investor with multiple projects or startups that you’ve invested in. Or maybe you want to evaluate the profitability of different products that your company is currently selling. Either way, this system is flexible and can be tailored to your business needs.

Why use Google Sheets to analyze business unit performance?

  • Flexible: Google Sheets lets you manage and analyze financial data from multiple departments in the way that suits your business. When you need to modify the system you can simply change or adapt the spreadsheets, without specialist software or IT support.
  • Accessible: Unit managers and administrators can access and update their department’s data at any time, from any location, via laptop or smartphone.
  • Compatible with everything: All software packages integrate with Google Sheets, so if your company has data stored in other programs, you can easily import that information straight into Google Sheets files.
  • Easy to use: everyone on the team will be familiar with spreadsheets and Google Sheets is user-friendly, even for beginners. Sheets are easy to maintain and it’s simple to train your team how to use them.
business-unit-template-dashboard

How to get started

This spreadsheet-based workflow template is a pre-built system that allows you to analyze and compare budgets and performance across multiple business units.

The template can be used by companies of all sizes, with any number of departments or divisions. It provides unit managers with a user-friendly way to collect and analyze income and expenses while generating automated reports, and dashboard for administrators and company directors.

What’s a spreadsheet-based workflow template?

A regular spreadsheet template is a single file with pre-built charts and formulas, but a Sheetgo workflow template is a complete system. It consists of multiple Google Sheets files that transfer data between them. This allows each unit or department to record and share their financial data privately, while enabling managers to collect and analyze metrics from every department.

A spreadsheet-based workflow template gives you:

  • Organization and data privacy: Each file is dedicated to one specific task, for better data protection and accuracy. Instead of working in a shared spreadsheet, each department has their own personal file where they record their financial data. Data from every business unit is exported automatically to the manager’s master sheet and central dashboard.
  • Scalability: Google Sheets collaboration functions are powerful, but when you have too many people working in the same spreadsheet it can become large and heavy. Keeping data in separate spreadsheets ensures that the system functions smoothly. If you want to add another business unit, just copy one of the input spreadsheets, connect it to the workflow, and share it with colleagues from that team.
  • Integration: Google Sheets is compatible with everything, so accountants and managers can easily import and merge data from other software and departments. In a few clicks, you can connect data from other workflows to build a completely integrated financial management system.

What you get with this template:

When you install the Sheetgo business unit performance analysis template, four Google Sheets files with pre-built Sheetgo connections are saved to your Google Drive:

  • Three Business Unit spreadsheets. Share each file with the managers of the different units or departments, and make as many copies as you need for additional divisions. Managers or accountants from that unit use their copy of the spreadsheet to register their forecast or budget as well as their actual income or expenses.
  • One Business unit performance analysis master sheet, for the manager or administrator of the parent company. Sheetgo connections import data from all the Business unit spreadsheets directly into this file. The template automatically consolidates this data and displays key metrics such as gross margin, total variable costs, and EBITDA in the dashboard.
Business Unit Plan Template: Workflow and Connections

Are you running a smaller business?

If you think this automated template could be useful in your company but you’re not a manager, share this post with your administrator so they can set up the system.

Alternatively, if you’re working alone and managing the financial data for multiple units by yourself, try our basic, one-file template.

How to get the workflow template

  1. Click the blue button below to install the business unit plan template.
  2. Log in to Sheetgo with your Google account. As this template is based in Google Sheets, the files will be saved to your Google Drive.
  3. Click Use template.
  4. Wait a few moments while Sheetgo installs the template and creates the connections.
Business unit template

Workflow template to monitor income/expenses for different units or projects.

The Business unit performance analysis master sheet opens inside Sheetgo.

To open one of the Business Unit files, click on Workflow in the side-bar on the right-hand side of the screen and double-click on any of the three files.

business-unit-template-opens

How to use the business unit template

Step 1: Select a time period for analysis

In the Business unit analysis master sheet, go to the Instructions tab, and enter inputs in the boxes on the right.

Choose which year you want to analyze, which month, and if you want an Accumulated or Month-only report.

business-unit-template-inputs

Step 2: Enter account names

Now go to the Configuration tab. In Column A (Account Name) enter the list of categories that you want each business unit to provide data on.

The template contains examples (Sales, Direct Cost, Commissions, etc). You can use these or add your own categories.

business-unit-template-configuration

Step 3: Update the workflow

The template contains pre-built connections. Updating the connections will send this list of Account names to each of the Business Unit spreadsheets.

To do this, click on Workflow in the sidebar to the right of the screen and click Run on the menu bar.

business-unit-template-run

Step 4: Share the files

Now that each Business Unit file contains the correct list of Account categories, your colleagues from each department can start using their spreadsheet.

Click on Workflow or Files, then double-click on one of the Business Unit files.
Go to Open in new to open the file in your browser.

business-unit-template-open-in-new

Now rename the file for the appropriate business unit or department.

Click on the green Google Sheets Share button and give edit access to the colleague(s) who will manage this spreadsheet.

Step 5: Enter data

Once they have received their Business Unit spreadsheet, the managers or accountants from that department should start entering data into the Registers tab.

business-unit-enter-registers

Step 6: Automate the workflow

You can update the data in the master sheet at any time by clicking Run, but you can also automate another step of the business reporting process.

To ensure you’ve always got the latest data flowing into your dashboard from each department, schedule automatic updates via the menu bar.

business-unit-template-automate

How to expand the workflow

Once your automated business unit reporting system is up and running, it’s easy to expand and customize the workflow.

You can add more Business Unit files with new connections.

  1. In Google Drive > Sheetgo Templates > Business Unit Performance Analysis you will find the 3 Business unit spreadsheets that are included in the template.
  2. To duplicate one of these input sheets, right-click > Make a copy.
business-unit-template-make-a-copy

3. Open Sheetgo and go to your Business unit performance analysis workflow.

4. Go to Connections. You will see that there are currently 4 connections.

The first connection in the list, Consolidated business registers, combines data from the Business Unit spreadsheets and pulls that information into the master sheet.

The following 3 Configuration connections send the list of account names to the Business Unit spreadsheets. We want to copy this connection for the new (duplicated) spreadsheet that you’ve just created.

business-unit-template-4-connections

1. Next to one of the existing Configuration connections, go to the three dots (⋮) menu and select Make a copy.

As you can see, the source data is the Business unit performance analysis master sheet > Configuration tab. Don’t make any changes here.

2. Scroll down to Destination > Google Sheets.

3. Click on File > Change file.

4. Select the new (duplicated) Google Sheets file from your Google Drive. You will find it inside Sheetgo Templates > Business Unit Performance.

5. Under Tab name, enter the name “Configuration”.

6. Click Finish and save to create the connection.

In the Connections list to the right of the screen, you will see that there are now 4 Configuration connections.

The Configuration tab in the new destination sheet (Business unit 4) is hidden (click on the All Sheets icon in the bottom left of the spreadsheet to view it) but it is now connected to the master sheet, pulling the list of Account categories from the master sheet.

business-unit-extra-sheet-new-connection-one

If you click on Workflow, you can see there is now a connection going in one direction, from the master sheet to the new file, Business unit 4.

business-unit-extra-sheet-one-direction

Now we need to send data back in the other direction.

To do this, return to the Connections list.

Nex to the connection Consolidated business registers click on the three-dots menu () and then Edit connection.

Under Source data > Multiple files you will see the Business unit files that are already connected.

Click +Add another source file > +Select file and select the new spreadsheet.

Under File tab, make sure to select the correct source tab. In this case, that’s Registers.

business-unit-extra-consolidated-connection

Click Finish and Save and the connection is complete.

Data from the additional Business Unit file is now being consolidated into the master sheet, along with information from the other 3 Business Unit files.

business-unit-extra-connections-complete

Create a custom business intelligence solution

Sheetgo makes Google Sheets even more flexible, allowing you to design and implement your own custom data management solutions.

Various steps can be automated further, by connecting other inputs such as Google Forms, or by connecting other spreadsheets with Sheetgo. If you need assistance or you’d like a more complex system, book a help session with our automation experts.

Looking for a basic template?

If you’re working alone and want to manage the financial data for multiple business units in one single file, try our basic, one-file template. This template does not consolidate information from multiple spreadsheets but it provides a simple way to track sales and costs for multiple departments in one file. 

Did you like this post?

If you found this post useful, please share it with your colleagues and friends via the social media buttons on the left!

You may also like…

Share This