In many cases, these units are structured as a business-within-a-business because they operate with a high level of independence. As such, companies with separate business units are often found in parent companies with subsidiaries and hospitality businesses.
Usually, companies with several business units plan and control their own budgets. Consequently, managers are in charge of analyzing performance and preparing reports of their own unit’s financial situation.
The two most common indicators are EBIT (earnings before interest and taxes) or EBITDA (earnings before interest, taxes, depreciation, and amortization). More specifically, EBIT refers to the amount of income a company is generating, whereas EBITDA approximates a company’s cash flow.
Calculating EBITDA
To calculate EBIT/EBITDA, companies can opt to buy accountancy software or simply hire an accountant. However, these options are expensive, especially when considering the alternative.
In fact, no specialist software is needed because each business unit already measures its own costs, income, and expenses. Indeed, Sheetgo’s business unit and EBITDA template in Excel can analyze each business unit’s performance in a reliable way. Moreover, automated and cloud-based templates generate reports for managers using real-time data.
Why use Microsoft Excel to analyze EBITDA and business unit performance?
- Flexibility: Microsoft Excel is the ideal tool to manage and analyze financial data according to your company’s requirements. Additionally, it’s easy to modify and adapt spreadsheets because it requires no previous coding experience.
- Accessibility: Unit managers and administrators are able to access and transfer their department’s data at any time and from any location. For instance, they can use their laptop, smartphone, or other devices.
- Compatibility: Microsoft Excel is compatible with other software that your company uses. As a result, data is easily imported into Excel spreadsheets.
- User friendly: Many of your employees are probably familiar with spreadsheets. If they are not, it is easy to train them. Indeed, it’s ideal for beginners and advanced users to work with an Excel-based template.
How to get started
This spreadsheet-based workflow template from Sheetgo is a pre-built system. With the template, you 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. On top of that, it automatically generates dashboards 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. A Sheetgo workflow template, however, is a complete system.
To be precise, it consists of multiple Excel 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 dedicates to one specific task only, 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 then exports automatically to the manager’s master sheet and central dashboard.
- Scalability: Collaboration on Excel is very powerful. But when you have too many people working in the same spreadsheet it becomes large and slow. Instead, keep data in separate spreadsheets to ensure that the system functions smoothly.
- Integration: Excel is compatible with almost everything. This means that accountants and managers easily import and merge data from other software and departments. As a result, you can connect data from other workflows and build a completely integrated financial management system.
What you get with this EBITDA template in Excel
When you install the Sheetgo Business unit performance template in Excel, 4 Microsoft Excel files are saved to your preferred cloud-based account:
- Three Business Unit spreadsheets. Firstly, share each file with the managers of the different units or departments. Additionally, make as many copies as you need for extra divisions. Managers from each unit use their copy of the spreadsheet to register their forecasts and actual income or expenses.
- One Business unit performance analysis master sheet, for the manager or administrator. Sheetgo connections import data from all the Business unit spreadsheets directly into this file. The template then automatically merges this data. Moreover, it displays key metrics such as gross margin, total variable costs, and EBITDA in the dashboard.
How to install the Business unit performance template in Excel
Click on the blue button below to install Sheetgo’s Business unit performance and EBITDA template in Excel.
Business unit template
Workflow template to monitor income/expenses for different units or projects.
How to use the template
- Login to Sheetgo with your Google, Microsoft, or Dropbox account.
- Next, click Install on Onedrive or choose your preferred platform from the dropdown list.
- Please allow a few moments as Sheetgo creates the connections and saves a copy of the template files to your chosen account.
Step 1: Select a time period for analysis
First, double click the Business unit performance analysis master sheet in the workflow view. The spreadsheet now opens in a new tab.
If you prefer working in the Excel desktop application, simply click Open in Desktop App.
Next, go to the Instructions tab and enter inputs in the boxes on the right. Choose which year and month you want to analyze. Finally, select if you want an Accumulated or Month-only report.
Step 2: Enter account names
In the same spreadsheet, go to the Configuration tab. Now enter the list of categories each business unit provides data on in Column A (Account name).
Please note that the template contains sample data to show you how it works. Simply delete this data or overwrite it with your own.
Step 3: Update the workflow
The template contains pre-built connections that send data back and forth between the spreadsheets. Update these connections to have the correct data.
To do this, go back to Sheetgo with the main workflow view. Click Run located in the left bottom corner of the screen. As a results, it transfers all your data and sends the correct account names to the different Business Unit files.
Step 4: Share the files
Once you’ve ran your workflow, each Business Unit contains the correct list of Account names. Now, your colleagues from each department or unit can start using their spreadsheets.
Open each of the Business Unit sheets and select Share at the top right of your screen.
Step 5: Enter data
After sharing the files with your colleagues, they should enter their data in the Registers tab of their designated spreadsheet.
Step 6: Automate the workflow
Instead of manually updating the workflow, you can also choose to schedule automatic transfers. Automation ensures you always have the latest data. Moreover, automatic transfers save you valuable time.
First, go back to Sheetgo and select Automate located next to the run button. Then select Update automatically and choose your preferred automation schedule. You can also find an approximate usage summary at the bottom of the Automation settings window.
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.
This means that you can add more Business unit files with new connections.
Step 1: Duplicate a spreadsheet
1. In your Drive storage > 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 > Copy to.
Step 2: Create an extra connection
3. After that, open Sheetgo and go to your Business unit performance analysis workflow.
4. Navigate 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, however, 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.
Next to one of the existing Configuration connections, go to the three dots (⋮) menu and select Duplicate. As you can see, the source data is the master sheet > Configuration tab. Don’t make any changes here.
6. Click on File > Edit.
7. Select the new (duplicated) Excel file from your Drive storage. You will find it inside Sheetgo Templates > Business Unit Performance.
8. Under Tab name, enter the name “Configuration”.
9. Finally, click Finish and save to create the connection. In the Connections list, you will see that there are now 4 Configuration connections.The Configuration tab in the new destination sheet (Business unit 4) is now connected to the master sheet, pulling the list of Account names from the master sheet.
Step 3: Create opposite connection too
Now we need to send data back in the other direction.
To do this, return to the Connections list.
Next to the connection Consolidated business registers click on the three-dots menu (⋮) and then Edit.
Under Source > 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.
All done!
Congratulations on setting up the Business unit performance and EBITDA template in Excel!
Looking for more finance-related workflow templates?
Check out this post on Sheetgo’s automated expense tracker template and discover more finance templates in Excel!
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!
