Go Back > Blogs > Hitesh Shah's blog

User login

Frontpage Sponsor


How big is your Baan-DB (just Data AND Indexes)
0 - 200 GB
200 - 500 GB
500 - 800 GB
800 - 1200 GB
1200 - 1500 GB
1500 - 2000 GB
> 2000 GB
Total votes: 54

Baanboard at LinkedIn

Reference Content

Funds Flow statement in Excel Pivot Table directly from Baan
By Hitesh Shah at 30 Jan 2009 - 04:54

Just-do-it-in Excel has been long time successfully deployed strategy deeply embedded in ERPJewels tool of Jewelex. Now  there are many takers and followers for the same. One can vouch this in Just-do-it-in-excel. Reasons for this are not far to seek.

  1. Users need lively interactive vivacious environment (sorting ,filtering , grouping, pivoting, charting, emailing, combining and consolidating,what-if scenario, intuitive WYSYWYG colourful formatting and printing)
  2. Users need freedom from the strictures imposed by the rigid structures of OLTP (even OLAP if there be). And intelligence and creativity do not accept limits very fast. Probably people realize that the heavy strictures of rigid high-cost-high-maintenance-structures cause corporations to fail. This is also a reason why ‘Export to Excel’ is such a popular feature of every tool.
  3. Probably users want to leverage on existing investments and are not too keen in investing in new technology with good recurring maintenance

All this is greatly satisfied by MS Excel (or for that matter other open office spreadsheets also probably!).  No wonder many takers for Just-do-it-in-excel strategy. Now we have  Fund flow statement coming out directly from Baan. I would list here the KPI / KRA / dimensions measures which one can use  in pivot table (drag and drop , slicing and dicing , multi-dimensional modeling etc)

  1. Cash/bank (distinct ledgers in cash transaction type )
  2. Company
  3. Flow descr 1
  4. Flow descr 2
  5. Flow descr 3 (all 3 in a hierarchical fashion)
  6. Year
  7. Half Year
  8. Quarter
  9. Month
  10. Currency

 These KPI’s will be with following 2 measures: 

  1. Amt in FC
  2. Amt in HC

Just to understand the importance of 10 dimensions with respect to pivot table, a pivot table with 10 KPI can give technically  power(2,10) – 1 = 1023  reports along with possible graphical representation and no involvement of dedicated IT personnel in a self serviced manner . This report itself becomes the source of  many reports for fund analysis like  pandora’s box. The challenge is to bring all the related, relevant KPI’s accurately in a tabular manner with 100% accuracy. Which is what this ERPJewels add-on does it. The number of sub-reports from above pivot table could be like

  1. Viewing of fund transactions at each pre-defined logical hierarchical levels
  2. Quarterly fund flow
  3. Half yearly Fund flow
  4. Currency wise fund movements (to study average currency rate for a period/ledgers etc)
  5. Party wise fund movements (Flow descry 1)
  6. Unit-company wise fund flow
  7. Movement of funds as per logical groupings

And all these, finance users do it on it's  own (leaving IT persons  to  concentrate on more important jobs ).


Functional description

  1. Fund flow statement is prepared for all distinct ledger accounts which are used as contra account for cash type transaction type (session tfcmg2100s000 & tfcmg2120s000) .
  2. It checks if from-to period makes it logical for yearly, half yearly or quarterly analyses. If it’s possible then it generates the appropriate time dimension in the base data for pivot table. It assumes 12 month fiscal year.  Though weekly periods can be handled, it’s currently handled mainly because few companies use weekly fiscal periods. Program accepts fiscal periods and generates output in fiscal period OR calender period depending on user request.
  3. The program considers only finalized transactions for it’s analysis. 
  4. It can scan all the financial companies within the group and present logical groupings for fund movement within an enterprise.
  5. For each fund cash/bank account, it calculates opening balance separately and generates the report.
  6. Grand total for any fund a/c is the closing balance for that fund ledger a/c which can be verified with trial balance report (tfgld3402m000) . So the total across periods , kind of trx etc actually reflect the closing balance makeup (e.g opening + receipts - payments = closing balance OR opening + each period change = closing balance.
  7. Hierarchical fund flow description gives one very logical break-up of the actual closing balance makeup.


Other Related Info

It can be scheduled in ERPJewels scheduler using Baan job management. Though it’s possible to get the output in XL using | as separator, it’s not possible to get more flexibility without ERPJewels (for scheduling, calculated items, UDF’s etc).  

One can download the ERPJewels tool freely . This session, and services related to ERPJewels (installation &training) can be had from Jewelex for a consideration. Those interested should get in touch with us for more info (send email / pm through this board).

As reporting structures / finance tables have not been changed later versions, it can be easily used in versions later to Baan IV. Functional descriptions and sample of a report will be attached later on along with separate similar pivot tables for customer, supplier and inventory.

The sample output with sample data can be downloaded from fund flow sample along with functional doc.

No votes yet

All times are GMT +2. The time now is 12:14.

©2001-2018 - -