Final Friday Fix: January 2020 Challenge
31 January 2020
On the final Friday of each month, we’re going to set an Excel challenge for you to puzzle over so that you can get your “Excel fix”. Challenge your office colleagues to see who can solve the puzzle quickest. There are no prizes at this stage, you’re playing for bragging rights only!
Flexible Financial Statement
Welcome to the very first Friday Fix of 2020. This month we are pulling inspiration from our consulting work, with this challenge being related to a recent task.
The problem here relates to creating a flexible financial statement. I have a Chart of Accounts similar to the one below, where current items may be removed or new items may be added:
In the calculation section, I have calculation results for revenue and expense for each line item, which will later be reconciled in the financial statement:
I want to create an Income Statement like the following. The Income Statement line items are based on the Chart of Accounts, which are currently hard-coded. I want to make sure that any changes in the Chart of Accounts in the future, such as removal or addition of groups of accounts, will be reflected in my Profit & Loss account automatically, without having to manually input or delete any line items. This is what I call a flexible financial statement.
In other words, after perhaps the odd row deletion, column E in the image above should be generated formulaically (i.e. this month’s challenge is a formula challenge, rather than seeking a Power Query or VBA solution).
Sound easy? Have a go. We’ll publish what we think is the best solution in Monday’s blog. In the meantime, have a great weekend!
