Export financials in database format table

income statementBalance SheetdatabaseexportCash Flow Statement
10 posts / 0 new
Log in or register to post comments.
Last post
6 years ago
#1
Josh B A+ 6
Export financials in database format table

Hi

I have only just started using Modano and already see massive benefits. I was able to build a 3-way model in hours (that is with me fumbling around) as oppose to weeks or even longer. Even using a standard such as FAST modelling there is really no comparison to this, so thank you Modano team.

My question is how would you create a module that would simply create the financials across P&L, Cash Flow and Balance Sheet into a database friendly table.

E.g. the Fields may look:

Date | Account | Amount

With information running down the page. Historically I have done this using VBA but wary I do not want to break/interfere with the Modano module structure. If using VBA is the solution then I'm ok with that just some pointers so I don't break the workbook. 

The purpose is to use in this with BI reporting software.

Thanks

Josh

6 years ago
Michael Hutchens A+ 189

Hi Josh,

Thanks for the feedback, it's great to hear that we're faster than FAST. ;-)

As you can imagine, there are quite a few ways of doing what you're after here. Personally, I always take it as a challange to do it with minimal or no VBA, and in a way which is scalable as the model evolves over time.

Based on your comment, all you're looking to do here is convert the P&L, BS and CFS - which are structured as categories across multiple time series columns - into a list in a range of rows with 3 columns (Date, Account, Amount). Is this correct? And if so:

  1. Are you happy if the date is always the 1st day of each forecast time series period?
  2. Does the list need to be in date order, or can it by ordered by account then date?

Also, can you provide an example of a standard Excel spreadsheet which contains such a list, so we can check it out.

I'd imagine this is something a lot of people would use as Power BI becomes more commonly used.

6 years ago
Josh B A+ 6

Thanks Michael

I've attached a file I've used for a current client (the modelling was done not using Modano), I've removed the confidential information. It has a few more columns as I have done department budgeting for this organisation (how to do that I realise is a new topic so I'll read the forum and post a separate question if I can't find the solution), but you should see the general flavour of what I am attempting.

Thanks

Josh

Attachments: 
SampleTable.xlsx
6 years ago
Michael Hutchens A+ 189

This is great, thanks for sharing Josh.

So, just to be clear, the order of the data doesn't matter, and it also doesn't matter if the date for each record is always the first (or last) day of its time series period in the forecasts?

Also, a couple more things?

  1. Does the list need to be on an unprotected sheet for use within Power BI, or any other reason?
  2. Does it matter if the list contains formulas, or does it need to all be constants?

Sorry about the large number of questions, I'm just trying to narrow down the limitations as there are a bunch of ways to do this.

Thanks, M.

6 years ago
Josh B A+ 6

Right, some answers:

  • Date shouldn't matter, part of good reporting is to have an adequate date or calendar table (I normally defualt to last day of month), so long as the month is correct
  • Just tested a protected sheet and that is fine, obviously the workbook cannot be protected
  • formulas are fine

Just another point, depending on what business intelligence solution is being used (Power BI, tableau, etc.) I often export the output to csv file for maximum compatible, this can be covered as last step. This can be hanlded via VBA (i know that is a dirty term).

Thanks Josh

6 years ago
Josh B A+ 6

Just wondering where this is at?

Thanks, Josh

6 years ago
Michael Hutchens A+ 189

Hi Josh,

I'd love to show you how I would do this, but I've been sucked into a couple of large projects, so I'm not realistically going to get a chance for the next few weeks.

If this is time-sensitive, purchase a support credit via the Support section of your account (www.modano.com/account/support) and submit a formal support reqeust. The Modano support team will then give you a response within 24 hours.

Cheers, M.

6 years ago
Josh B A+ 6

Thanks Michael

I only just realised you are the CEO, thanks for taking time. It is not a top priority so I will just keep plugging away at it.

Cheers, Josh

1 year ago
Sajid Mafahir X 1
SM

Hello Modano,

 

Is there any update on this front to get Modano data into a database or query to use in power query?

Thanks.

5 months ago
blakem X 1
BM

Just commenting on this - I use a freeform page which lookups up from the IY report outputs. 

This gives me each line of the Business Unit Financials by month.  I can then create keys / helpers to allocate and classes. It is this sheet I then target with Power Query. 

For this to work however I have to:

  • Ensure strict alignment of the forecast rows / lines names - e.g. Salary and Wages must be identical in all BUs. 
  • I have the naming convention / text string for lookups as "BU Name_GL Account Code Name".
    • This lets me slice and dice in Power BI. 
    • Going forward I will probably enter other groupings (Income, COGS, Overheads) 
  • I then run a mapping column with lookups to allocate each line to groups 
  • This creates a Pivot Table style view with Months along the top and all the groupings down the left hand column 
  • I then use Power Query to unpivot
  • Month is then transformed to be the first of the month and this then link to master calendars in Power Query / Data Models

We are implementing NetSuite so what is really cool is I have the data automatically updating for historical and then automated publishing of forecasts for the team to review.

One of my challenges is getting information into a Dollars per Tonnes view. This is very tricky as I need each business unit tonnes to be accurate for this to work.  I'd share an example but as you can appreciate fairly sensitive information