Most ERP and accounting software solutions out there can generate decent standard reports. However, we often need more than that.

One way to approach the preparation of more specific statements is to do it in Excel. We can export the data from our systems and prepare a template for the Excel statement, where we will aggregate this data.

This is a common approach amongst many accountants. But it’s a time-consuming exercise, prone to errors. Once we prepare our statements manually, it’s hard to make corrections, and almost impossible to reuse the work for the next periodic report.

The good news is that with a few tricks in Excel, we can automate a large part of this process. And the truth is, it’s relatively easy once we get over setting up our initial structure.

The Trial Balance Mapping Process

We can use any structured source data to prepare a mapping table for any report. In terms of mapping our Trial Balance to the financial statements, we can follow this overall guideline.

1. Build out our statement structure in Excel;
2. Export the relevant data from our system and get it to Excel;
3. Prepare our mapping of the data to respective line items in our report;
4. Use aggregation formulas like SUMIF/SUMIFS to consolidate source data into the relevant statement lines;
5. Add cross-checks to our Notes and validation checks for our mapping.

Multi-level Mapping

When we prepare our reports mapping, we usually have more than one level of mapping. For example, we can have one map for our Trial Balance to our Financial Statements (Income Statement, Balance Sheet), and then another for our accompanying detailed Notes, where we present data in a more granular fashion.

Benefits of Mapping our Data

Following this approach to our reports preparation, we can eliminate a few of the inherent problems of manually aggregating our statements.

Reports usually present more aggregated data, and we can have many data lines corresponding to a single statement line, which will require complex and hard-to-read SUM functions.

Let’s say our Cash in Bank account in the trial balance is on row 44 in this period, but on row 47 the next period, due to adding new accounts. If we follow the traditional approach to aggregate the amounts by addition, our formula will point to the wrong cell address in the next period.

Names of accounts in the trial balance can be different from those in our reports, which will make it harder to match them manually when we prepare each period’s statement. This can lead to inconsistencies between statements.

Data Validation

When we are developing our mapping model in Excel, we can use the built-in Data Validation functionality. By doing so, we can ensure proper mapping and avoid typos.

One of the data validation options in Excel is to match the cell content to a list of items. This is useful when we prepare more specific statements that our ERP or accounting software cannot automate on itself.

Using Tables

We can also convert our mapping range to a table and give it a name. That way, we will make it more robust for adding new items to our statements. Using the Table functionality within Excel helps us with the following:

• Excel Tables auto-expand when we add new items at the bottom;
• We can give tables specific names;
• When we reference appropriately named Tables, our formulas become more readable and easy to understand.

Prepare Trial Balance Mapping in Excel

Let us illustrate the process with the following detailed example.

Create the Report Structure

First, we start by preparing our statements’ structure. We will be preparing an Income Statement and a Balance sheet based on the company’s Trial Balance.

We open Excel and prepare our structure, adding all the necessary sub-totals.

Here’s our Income Statement report:

And here’s the structure for our Balance Sheet:

Source Data

The next step is to get our source data. In this case, this will be our accumulated trial balance for the period we are reporting. Here’s our 2020 trial balance:

Let’s also create another sheet to keep our mapping table and some report settings, like the statements’ date.

We will also have two options for our mapping validation. An account on our trial balance can belong to either the Income Statement or the Balance Sheet. Therefore, we will prepare a table listing those options.

As discussed above, we can convert it to an Excel Table to ensure it will auto-expand if we add new items. However, in this example, we will have a slightly more elegant approach, which will enable us to have conditional data validation. But more on this a bit later.

Remember that if we rely on the Table functionality, it’s good practice to give our table a more meaningful name. While we have a cell within the table selected, go to the Table tab and type in your identifier in the Table Name field.

Below that, let’s add all our various line items and identify them as part of either the Income Statement or Balance Sheet. You can select the cells with your line items from the report sheets and copy-paste them on the Settings sheet.

Data Validation and Mapping

We then move to work on our Trial Balance, adding two columns for the mapping options.

We will use the Data Validation functionality (Data Tab) and start with cell D8 (the first line under the statement heading). Let’s use Allow > List.

We can then move to the Source field and select the relevant cells from our Settings table.

Click OK, and here we have it – a drop-down menu allowing us to pick the statement for each trial balance account. Transfer the Data Validation by copying the formatting down to all the rows.

We want the available options for each account’s Class to be shown based on the selected Statement option. Let’s add a no-selection option in our Settings sheet that we can refer to.

Next, we follow the same approach as with the Statement Data Validation, but for the first line in the Class column.

We will use a more complex Source, incorporating the following formula:

=IF(D8=Settings!$B$10,Settings!$C$16:$C$24,IF(D8=Settings!$B$11,Settings!$C$25:$C$37,Settings!$D$13))

We use two nested IF statements. The first checks if the selected statement is equal to our Settings sheet’s first option – Income Statement. If it is, we return a selection of all possible account Class mappings that match this Statement option. If the IF statement is FALSE, we then check for the second Statement option. And, if this returns FALSE again, we give our No-selection option.

If we try the drop-down on the Class column when we have nothing in statement, we only see the Select Statement.

However, if we select Income Statement, then the Class drop-down shows us all line items within this section from our Settings sheet.

And if we switch to the Balance Sheet, we get the other list of suitable options.

Data Validation ensures we avoid typos or use a line item that doesn’t exist in our report structures. If we try to enter something not part of the list, the validation throws up an error.

Copy down the validation formatting for all rows in the Class column.

The next step is to go ahead and employ our knowledge of each account’s contents to map them to our statements. This requires a deep understanding of the business processes and how accounting data reflects these.

Aggregate Data on the Statements

Let’s also give our Report Date cell a name for easier reference.

We can then link the Income Statement and Balance Sheet directly to this date by using the identifier.

All that’s left to do is employ Excel’s SUMIF formula to aggregate our source data based on the mapping. Let’s start with the Income Statement.

The first parameter of the formula is the range where we will match our criteria. This will be the Class column.

The next parameter is our Criteria, which will be the current line item in our report.

And the third parameter is the sum range. For Revenue and Income, we usually select the Credit Turnover of accounts.

The formula essentially aggregates the source data based on the line item on the same row in our report.

For Expenses, we need to switch to Debit Turnover. We present Debits as positive values in the trial balance, but they represent expenses in the reports, shown as negative values. Therefore, we put a minus sign in front of the SUMIF formula.

However, if we take a close look at our trial balance, we notice that account 704 PPE Sales Revenue has both Debit and Credit turnovers. This is because we use the same account to book revenue and the cost of the asset we are selling.

To account for such cases, we can add a column with Net Turnover and use it for our SUMIF functions. We can take the net value, as PPE sales are not material and not part of the standard operations. Usually, we can present them net in our financial reports.

We can then adjust all our SUMIF formulas to use the Net T/O column. We will add a negative sign initially, as in the TB, we will have Debits (expenses) as positives and Credits (revenues) as negatives.

Copying the formula to all our line items, we end up with our finished Income Statement.

We will follow the same approach for the Balance Sheet. However, we will need to add the Net Closing balance to our Trial Balance sheet. We also added the Net Opening Balance in case we decide to prepare comparative data for our statement.

Using the same mapping process, we can aggregate the numbers for our Balance Sheet line items.

Remember that we need to put a minus sign in front of our SUMIF formula for Equity and Liabilities because Credit balances will appear negative in our Net Closing Balance column.

However, we notice that our check on the Balance Sheet doesn’t zero out. This is because our Trial Balance is not yet closed, and the Income Statement result is not carried over to Retained Earnings.

To account for that, let’s first create an Adjustment column for our Trial Balance.

We can then link our adjustment to Retained Earnings to the year’s net result in our Income Statement. And this evens out our Balance Sheet check.

Conclusion

As accounting and reporting professionals, we often have to prepare various management reports and financial statements. Designing a structured model and employing Excel’s capabilities to automate the process can significantly impact our work.

It will ensure we avoid repetitive rudimentary tasks and focus more on analyzing the figures and implementing a more robust reporting process.

If you enjoyed the article, show your support by sharing it with colleagues and friends.

Also, don’t forget to download the Excel file below.