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.
Looking for a basic business unit template instead?
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 merge information from multiple spreadsheets but it provides a simple way to track sales and costs for multiple departments in one file.
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 merges this data and displays key metrics such as gross margin, total variable costs, and EBITDA in the dashboard.
How to install the workflow template
Click the blue button below to install the business unit plan template.
Business unit template
Workflow template to monitor income/expenses for different units or projects.
- Login to Sheetgo with your Google, Microsoft, or Dropbox account.
- Click Install template.
- Wait a few moments while Sheetgo creates copies of the files and connects them.
The Business unit performance analysis master sheet opens inside Sheetgo.
To open one of the Business Unit files, click on Show sidebar in the top right-hand corner of the screen and double-click on any of the three files.
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.
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.
Step 3: Transfer the data
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.
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.
Afterward, simply click on Share.
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.
Step 6: Automate the workflow
You can transfer 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 transfers by clicking on the Automate button next to run.
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.
- In Google Drive > Sheetgo Templates > Business Unit Performance Analysis you will find the 3 Business unit spreadsheets that are included in the template.
- To duplicate one of these input sheets, right-click > 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.
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.
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.
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.
Data from the additional Business Unit file is now being merged into the master sheet, along with information from the other 3 Business Unit files.
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.
Share with your manager
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.
