“90% of baseball is mental, the other half is physical”
Using an old Yogiism for applying the time value of money with analytics.
Yogi Berra, Hall of Fame catcher from the New York Yankees has written books full of Yogiisms, witty quotes that are applicable to baseball, life and business.
I often use his quote (the title of this blog) to describe the complexity of building and analyzing cash flow models. Whether it is a bank pricing a loan, credit team forecasting the expected credit loss of a portfolio, a company considering investing in technology or a data scientist calculating the lifetime value of a customer, the concept of the time value of money is at the core of all of it. In fact, in the textbook Fundamentals of Financial Management, written by Eugene Brigham and Joel Houston, they state that “of all financial concepts, time value of money is the single most important concept”.
Hidden Gems:
The hidden gems of the Qlik Analytics Platform are the deep set of formulas (functions) that can be used in either the script or individual charts. This library includes a full set of time value of money functions as depicted in the spectrum below.
“For investors, the Qlik Analytics Platform is invaluable for delivering actionable intelligence. The ability to look at the internal information about a company as well as the external information gives a better sense of confidence as what's the next best action. With the added capability to do complex calculations like Internal Rate of Return (IRR) and Future Value (FV) on the fly as selections are made, the investor is able to reduce hours of excel crunching into seconds of highly visual analysis.”
Keith Carter, Visiting Senior Fellow National University of Singapore, Charter Qlik Luminary and author of Actionable Intelligence: A Guide to Delivering Business Results with Big Data Fast!
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.
RANGEXIRR()
SUM()
COUNT()
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.
Photo credit: Baseball Collection / Foter / CC BY
