Chapter 12: Case Study 1 – Building a Model to Extract a Balance Sheet and Profit and Loss from a Trial Balance

The task of extracting financial statements from a trial balance can be taxing and prone to error, especially when you then have to incorporate journals and repeat the whole process in subsequent years.

This case study will teach you how to create an integrated template that seamlessly updates the financial statements with audit journals and other adjustments to the original data.

By the end of the chapter, you will have learned how to integrate a trial balance into the three-statement model of the balance sheet, profit and loss account, and cash flow statements.

In this chapter, we will cover the following steps:

1. Introduction – Case study and requirements
2. Preparing a working trial balance (WTB)
3. Extracting groupings from the WTB
4. Preparing a model template for the balance sheet (BS) and profit and loss (P&L), and relevant extracts...

Scenario for the case study

Your client, Wazobia Company Limited, commenced business on 1 January, 2021, and has asked you to look at their first set of accounts made up to 31 December, 2021. Wazobia has a competent bookkeeper who prepared a trial balance on 31 December, 2021, and has given you a copy in an Excel file. You are required to perform the following tasks:

1. Create a model template to extract the BS and PNL and relevant notes and schedules from the trial balance. The model should be integrated so that adjustments and updates can be affected in just a few actions.
2. Populate the model from the trial balance making sure that the BS is in balance.
3. Update your model with the following adjustments:
   • It was discovered that the closing inventory had been understated by N10,000,000.
   • You are to make a provision of N10,512,000 for taxation.

Let's begin by preparing a WTB.

Preparing a WTB

You have been given the following trial balance:

Figure 12.1 – Client's trial balance

Here are the steps to prepare a WTB:

1. Open a new Excel workbook and copy this worksheet to a worksheet in the new workbook. Rename the worksheet CTB.

You will notice that the trial balance has separate columns for DR and CR. You will need to combine these into one column, with CR balances shown as negative (in brackets).

To do this, you will create another column of figures using the IF formula:

Figure 12.2 – Syntax for the IF formula

As you can see from Figure 12.2, the IF formula has three arguments. The first argument is logical_test, which is a statement that must evaluate as TRUE or FALSE. In this example, we are using C6 (the DR column or field) > 0.

This DR column contains either positive amounts for DR accounts or blanks for CR accounts. Therefore, our logical test will result in TRUE...

Extracting groupings from the WTB

The next step is to convert the WTB into an Excel table and then prepare a pivot table to summarize the account descriptions:

1. To convert to a table, place the cursor anywhere inside WTB, then press Ctrl + T.
2. The range is highlighted and the Convert to Table dialog box is launched. Check the My Table Has Headers checkbox, confirm that the correct range is covered, and then click Ok.

Figure 12.7 – The WTB formatted as a table

3. With the Table Design ribbon selected, go to the Table Name option at the beginning of the ribbon icons and change the name of the table to WTB.

Formatting the range as a table has several advantages:

• Type in a formula at the beginning of any field and when you press Enter, the formula is filled down the rest of the records of that field.
• You can include the range in formulas simply by typing the WTB table name.
• You can add new records to the bottom of the...

Preparing a model template for BS and PNL, and relevant extracts from notes and schedules

In order to create the template, rename a new worksheet, BS PL CF Ns. Then, each schedule you prepare will have a banner of light blue with white characters from column A to column E, with the schedule title written in column A. The schedule itself will start with the name of the company from column B with the amounts in column E.

The schedules will be prepared one on top of the other, with each schedule grouped by selecting from the row above the company name to the row after the end of the schedule, and pressing Shift + Alt + Right Arrow simultaneously.

When all the groups are collapsed, you get the following effect:

Figure 12.13 – Collapsed grouped schedules

The schedules can be expanded individually by clicking on the plus sign below and to the left of each heading, or all at once by clicking the 2 to the left of the column header A.

Next, rename a...

Populating BS, PNL, notes, and schedules from groupings

You will now populate the statements from the groupings using the XLOOKUP function. In this function, you will need to specify Lookup Array and Return Array, so to simplify our formula, we will name those ranges as well:

1. On the Groupings worksheet, select ACC GRP (Column B) from the first item beneath the header (Row 4) to five rows below the grand total (Row 29). In other words, B4:B29:

Figure 12.15 – XLOOKUP lookup array

2. Name this range LARR. This is the lookup array. The reason we extend it by five rows is in case we are required to expand our WTB with additional accounts.
3. Do the same for the return array, C4:C29, and name this range RARR:

Figure 12.16 – XLOOKUP return array

We can now populate the statements using XLOOKUP, starting from the schedules to the accounts:

Figure 12.17 – XLOOKUP syntax

When you...

Preparing journal adjustments and linking to the WTB

You now need to address the adjustments in Part 3 of the requirements:

1. Create a new worksheet and rename it JNLs. Prepare your journal adjustments in the following manner:

Figure 12.22 – Template for journals

2. Enter the DR amount, then for the CR value, enter – (minus) in the cell with the DR value. This way, you reduce the exposure to typographic errors and ensure that your journals balance.

Note that the account name you use is from ACC DESCRIPTION and not ACC GRP.

Now, go to the AUDIT JOURNAL column of the WTB and enter the following formula in the first record in a cell:

Figure 12.23 – Syntax for SUMIF

The SUMIF function has three arguments:

• Range – This refers to the range Excel will search for records matching the criteria. In this case, all of column C on the JNLs worksheet.
• Criteria – All occurrences...

Updating financial statements and troubleshooting to correct errors

In order to update the financial statements, we just need to do the following:

1. Refresh the pivot table, and the values will be updated accordingly.

Figure 12.28 – Refreshing the pivot table

2. With the cursor anywhere within the pivot table, right-click on the mouse and select Refresh from the drop-down menu. The pivot table is then updated for any adjustment made to the WTB.

Figure 12.29 – Pivot table updated with new information

The new account is incorporated into the pivot table. We now turn to the BS to confirm that the financial statements have been updated correctly.

The PNL has been updated correctly for Cost of sales and Taxation. The BS has updated Inventory, but is now out of balance.

In this exercise, it is easy to see where the difference is coming from; however, these are the steps you would normally take when the...

Summary

In this chapter, you have prepared a model to extract financial statements from a trial balance. You have learned how to construct your template so that the model can be updated with a few keystrokes for any adjustments or updates. You have also learned how to systematically approach the task of troubleshooting in the event of an error.

In the next chapter, we will look at capital budgeting with the help of another case study.