Monday Morning Mulling: January 2020 Challenge

On the final Friday of each month, we set an Excel problem for you to puzzle over for the weekend. On the Monday, we publish a solution. If you think there is an alternative answer, feel free to email us. We’ll feel free to ignore you.

Welcome to this month’s Monday Morning Mulling. Were you able to work around the flexible Income Statement in our last blog?

To recap, last Friday we posted an interesting problem, where I have a Chart of Accounts which is subject to change, such as line items may be added or removed.

I also have the calculation results for each item in the Chart of Accounts, which will be used to build the financial report:

I want to create a financial statement that automatically updated to changes in Chart of Accounts, without having to manually add or remove items when the Chart of Accounts changes so that column E (below) is generated formulaically (i.e. no Power Query or VBA):

Suggested solution:

Firstly, I need to prepare the Chart of Accounts ready for flexible financial statement.  I am going to create a formula-based rank for items in each group, so that they will be sorted accordingly (this means the data should be sorted initially as well).  I add two new columns, Level 2 Rank and IS Rank (“IS” stands for Income Statement).  In the image below, I create a Level 2 Rank as follows:

The formula in cell I11

provides a similar rank for all of the Level 2 items with the same description, assuming the Chart of Accounts has been sorted accordingly.  This ranking is repeated for each time the Level 1 description changes (e.g. from Income to Salary Expenditure).

Then, I concatenate the text of Level 1 and this ranking, which later will be used in my lookup:

Going back to my Income Statement, I create a series and use this to look up the relevant group in the Chart of Accounts based upon the rank:

To look up the line items, I use INDEX and MATCH functions in combination to create the descriptions.  If there is no item match, the formula will return a blank.  For example, the formula in cell E94 is

I can delete rows containing no line items and use a SUMIFS formula to pull the relevant data from the calculation section to fill in the financial statements.

Success! If the Chart of Accounts is changed in the future, I just need to insert or remove rows and copy the formulae across the cells.  The Income Statement is now flexible assuming the Chart of Accounts is sorted!

Do you find this tip useful?  Or have you encountered similar problems?  We’re happy to help.  Otherwise, we’ll see you next month for our next Final Friday Fix.

The Final Friday Fix will return on Friday 28 February with a new Excel Challenge. In the meantime, please look out for the Daily Excel Tip on our home page and watch out for a new blog every business workday.