Hi all!
Wanted to see whether anyone has toyed around with combining Power Query into a modular workbook.
We've actually delved into it quite a bit, as it provides a lot of power around pulling in operational metrics, even optimising how we run consolidations in a modular workbook.
Will provide an example shortly, just keen to see if anyone has had a play around.
Thanks all!
Very interested to hear how this goes.
I've not played around but this would be fantastic to harness.
I have thought of going the other way - working out a way to connect the modano worksheet via a connection to a Power Query in Power Bi to enable the outputs of the model to be used as the basis of a PowerBi Report.
It would thus allow for the users to see detailed historical information and the budget v actuals in PowerBI.
Hi Blake,
Good timing, I'm actually playing around right now with my personal budget using a Power Query + Modano build.
I did an export of my credit card / bank statements to CSV / Excel, used Power Query to bring this into a Modano file I've created from scratch.
I then did a =match + =index onto the table that I spit out from Power Query, so the referencing auto expands as the model rolls. Just need to make sure that in the Power Query M-code, you do an EOM column for the transactions.
This process flow works quite well as I can see my full historical summary, which allows me to do charting and dashboards at a transaction level granularity to dive into suppliers analysis / volume analysis etc. I can then forecast some of the line items at this level, and then map it back to the rolled-up management / summarised income statement level. It's handy because the banking platform has a transaction categorisation tool, which means everything rolls up into a usable category, and I don't need to go through the CSV / Excel transaction by transaction again. I think the categories are used as a default by banks for mortgage lending purposes, so I just align my reporting to that, so it's ready made if I want to hand this over to the bank.
On a separate note:
We did end up building a Power Query backend into one of our 3-way rolling forecast Dynamic Templates. We coupled this with our Mapping approach and use Power Query to summarise forecast calculations that feed back into the Mapping modules. By doing this, we are able to see the full GLs per Business Unit, as well as summarise the business units to their totals.
Screenshot below of Annual Financial Statements with business unit level summary, as well as General Ledger level summary.
Screencap below is the entity level forecasts, which allows us to see P&L per entity in full.
The screencap below is a centralised Headcount module that allows a centralised calculation. In this case we've kept it simple, but we can do a group level HR forecast, then allocate individuals out to the various business units. This also allows us to allocate out things like Payroll Tax, Leave, etc. to the various business units.
Because we have business unit level P&L, we can also do full budget variance per business unit, as well as at a group level (following screencap).
The benefit of this approach (and I know I keep referring to business units above), is we are actually also able to use an adjusted technique to view a cross of Multi-Business Unit / Multi-Entity combinations, so we can cross business units over multiple entities or Xero / Quickbook / Accounting instances (think Department A, Entity X + Department B, Entity X, Department A, Entity Y, Department B, Entity Y). Doing this, you need to build reconciliations into the model to make sure the total of all your additions reconciles up to all the Xero tracking categories / instances.
The final benefit here is file sizes. Because we are doing a bunch of the calculations in an Excel Data model, and we don't need to repeat information to summarise various dashboards / outputs, we can significantly reduce filesizes. This 4 entity 3 year example that I was taking screenshots from has a system filesize on an .xlsb file of 2.6 mb. The trade-off here is after adjusting assumptions, we need to hit the Refresh All button in the Data tab, but the calculation is quite quick, and we built an error check to remind people to press the button.
Anyway, hope it gives you some ideas.
Jun
Hi Jun,
This stuff looks very interesting. We've done some quite customized PowerQuery integrations for clients but nothing like the example you've discussed here.
Are you able to provide a simple working example for the members of this forum? Images are helpful when explaining concepts but I think the real power of the forum is sharing practical examples so the community can get their hands dirty.
Thanks!
Michael
Hey Mike,
Here's a very basic example of the above that I've pulled together for demonstration purposes only. The only aspect I'm demonstrating here is the Power Query process, and the example hasn't been built to scale (more entities), so it won't work properly if that's attempted, but categories should be able to be added.
A lot needs to be done to the backend (PQ + Modano Structure) to allow that to work, but for those astute individuals, there should be enough clues in here already to work it out from here.
I haven't included the Module structure to allow for historical + forecast, but again the astute individual should be able to glean enough clues from this, to continue the build.
Please note, I have built this from scratch just then, so there very well could be issues / errors in the model that I haven't checked through. The focus is purely the PQ process.
Hope it helps give people some ideas.
Jun
Thanks Jun, will check out this week!
M.
Impeccable timing Jun, I'm just about to start scoping a multi entity (37 to be precise) corporate model sourcing data through PQ so this will be a great example to take ideas from.
Thanks for sharing!