Here's an example:
Traditional cash flow analytics are run by the finance/pricing team and aggregated at a few levels to provide an analysis in a static, pre-aggregated way. This is fine for some high level metrics but does not allow for a full 360 degree view. Additional questions beyond the first pass of aggregated data can only be achieved by going back into the model and creating additional aggregated views, a very time consuming and limiting process.
This spreadsheet example above has 897 cash flows of a portfolio made up of both equipment and real estate, across several regions. Using only three formulas stored in the Qlik Sense master item library, I have provided the ability to dive into any combination of dimensions with the effect of a complete view of the portfolio.
Below is an embedded link to the Dynamic IRR dashboard built in preparation for a recent meeting with a leading global private equity investment firm. The chart objects below are linked to a Qlik Sense application, when you make a selection on one chart, the others will dynamically change. Selections can be made across dimensions such as Region, Investment Type, Deal Size and even down to the detailed Investment ID:
Taking all that into account: let’s revisit Yogi’s great quote and re-purpose it for the application of time value of money in business decisions:
90% of cash flow analysis is communicating a story, the other half is math
Calculating the ‘time value of money’ is the “physical” half and the math is settled. Applications of this math include (but are not limited to) justifying an investment technology, calculating a customer lifetime value to properly price a product or running an IRR on a portfolio of earning assets of portfolio being considered for purchase.
With a user friendly, fully interactive Qlik application, all of the roadblocks have been removed and the complexity of developing a true understanding of the cash flows is unlocked.
I built the application shown above in about an hour, click here to see a video about Qlik’s ROI Calculator. This calculator integrates the concept of building a five year cash flow forecast and calculates the IRR, Net Present Value (NPV) and payback period of an investment in the Qlik Analytics Platform.