Data analysis tools & spreadsheets
A selection of the spreadsheets, dashboards and applications I have built and use within my business:
Portfolio Construction & Risk Analysis:
Economic & Market Analysis
Other
Portfolio Construction & Risk Analysis:
- TSA SAA Calculator
- TSA Discounted Cash Flow Analysis
- Risk adjusted cash flow analysis for long term investors.
- Monte Carlo Simulation (risk, cash flow & performance analysis)
- Portfolio Optimisation (non-Markowitz)
- Retirement, Savings & Quality of Life
- Volatility-Adjusted Returns (explaining the impact of time, leverage and cash flows on final wealth)
Economic & Market Analysis
- The Case for Cash (market valuation)
- Schiller CAPE Analysis (R, Shiny)
- Mean Reversion or Momentum: what works? (market research using algorithms in Python)
- Effects of Monetary Policy and Risk Premiums on Asset Price Returns (asset pricing)
- Property vs Shares (Simulator)
Other
- 4Sight (trading simulator)
- Superannuation Fund Review 2017 (analysis of all major superannuation funds, approximately $1 trillion AUM)
- Investing in Small Business: Analysis of cash flow and capital structure
- Life Expectancy Calculator
- Income Tax Calculator
- Recommended Reading
- NAPLAN Analysis
Portfolio Construction & Risk Analysis
TSA SAA Calculator
|
Designed for financial advisers, this simple-to-use Strategic Asset Allocation Calculator allows the user to set not only return and volatility estimates, but also to specify more detailed characteristics of major asset classes, including correlation, cycle length, current valuation (relative to fair value or mean), and fees and taxes. We even provide the ability to tailor math methods, price velocity, leverage, short positions and allocation methods (high or low diversification).
Once these variables have been set, the user needs only to complete their "current" portfolio and cash flows (contributions and withdrawals), and the calculator will recommend a portfolio that provides their optimal risk-adjusted return. Although designed to be incredibly user-friendly, there are several methods used in our allocation process that make it more robust than traditional MPT methods (such as Markowitz optimisation). For example, our model factors in the "shape" of volatility and prices over time, as well as sequence of cash flows. We also include something that I call Shock Sensitivity. This indicator uses data about pricing anomalies, cycles and Price Velocity to estimate the impact from a normal market "shock" (normal being an event expected every 7 - 12 years, on average). We also reframe this risk as a real return-risk ratio. This should be a focal point for advisers as it indicates the level of loss (drawdown) investors should expect to encounter over a normal market cycle, given their current and recommended asset allocations. Although users should be aware of the key concepts underlying the allocation strategy, they do not need to have a full understanding of the math in order to make use of this tool. |
TSA Discounted Cash Flow Analysis
|
For the most part, our Discounted Cash Flow (DCF) analysis follows the traditional model for projecting P&L line items. However, in an effort to create a more robust, yet flexible model, I have incorporated a number of additional features, such as:
Example: Baby Bunting (ASX: BBN): 2017 has been a tough year for Baby Bunting shareholders, with the company's share price falling by over 35% as investors consider the effect of lower margins and the much anticipated (dreaded) arrival of Amazon. Though this makes for tougher trading conditions - at least in the short-term - our analysis suggests the market has overreacted. Starting with margin compression: As Australia's dominant player in baby goods retailing, Baby Bunting's continues to hold the most attractive operating margin and to a large degree has been the instigator of lower margins. This, in turn, has contributed to the failure of several of BBN's competitors. As we should expect, the liquidation of their rivals has caused somewhat of a surplus of at-or-below-cost stock to flood the market, and in doing so causes some weakness in BBN's short-term profitability. Overall, however, I believe this is a net positive for the company: They have clearly indicated their desire to grow, and with a very strong balance sheet they might decide this is an opportune time to accelerate their expansion. I estimate that an investment of between $15 - $25 million could bring their plans forward approximately four years, and contribute up to $10 million per annum of free cash flow. Regardless, we expect to see margins rise again in coming years, and earnings growth to outpace that of the sector as they mop up the gap left by their competitors. The second issue worrying investors is Amazon. If we look to the experience of other developed markets, within baby-goods Amazon has been able to capture around 5% of total segment sales. We will have to see how products, prices and warranties compare, but intuitively, on the whole we should see online retailers more adversely affected than BBN's "bricks and mortar" oriented business; this should help insulate earnings. We have determined a Fair Value for BBN of $2.76 ($1.93 net MOS), approximately 78% (25% net MOS) above the current price of $1.54. Review date: 14 January 2018 [ref: BBN.140118] |
Risk Adjusted Cash Flow Analysis for long term investors
|
This model allows the user to analyse valuation anomalies by categorising returns by earnings yield (or FCF), earnings growth, inflation and price change. Scenario analysis incorporates cash flows (sequencing risks), providing a better comparison of the investment outcomes from different portfolio allocations.
This type of approach is ideal for investors with very long investment horizons, who are making "lumpy" contributions or large withdrawals from their portfolio. For example, Not for Profit foundations and recent retirees. |
Monte Carlo Simulation - Portfolio modelling
This Monte Carlo simulator provides a comparison of three portfolios (plus Risk Free Rate), with ability to incorporate the impact of tax, fees, composition of returns, portfolio turnover, return objectives and inflation. Simulations incorporate client cash flows (contributions/withdrawals).
Analysis includes the following:
We also incorporate several qualitative factors, such as the client's personality and the importance of reaching certain goals as a way to gauge a client's risk tolerance and, specifically, the influence of behavioural bias (eg: loss aversion, mental accounting, framing). |
Portfolio Optimisation (non Markowitz)
|
This was one of our first Portfolio Optimisation tools to incorporate valuation anomalies and asset cycle length in our estimate of the asset return trajectory. It uses a normal distribution to factor in mean reversion (in a manner consistent with Time Decay) and uses this to predict future returns.
Though the math behind this approach is solid, we have since moved to a model that accounts for Price Velocity and "fat tailed" distributions, such as Cauchy or Laplace. We believe this better reflects "real world" market behaviour. As with our other models that incorporate asset cycles and pricing anomalies, as time moves forward investment allocations automatically adjust to reduce risk at the short end of the portfolio, while reacting to changes in the relative value of assets. This has the effect of reducing exposure to assets as they become overvalued, and increasing exposure as they become cheap. |
Retirement, Savings & Quality of Life
|
This is one of my favourite calculators. It was built to help illustrate the effect that small changes to savings and expenses can have on our clients' working life and retirement.
Here we pay particular attention to the "first phase" of retirement, which is the time between first retiring and the point at which most clients become less physically active (normally from about 80 years of age). I was inspired to build this calculator from my many conversations over the past decade with retirees lamenting the fact they left their retirement "too late". Having focused too much on their financial position they had deferred retirement, and in doing so greatly shortened the period of time they were physically able to pursue their retirement goals, such as travel, active family time, and involvement in their local communities. As advisers, I believe it is our duty to provide our clients with advice that properly considers both the financial and social/health aspects of our clients lives. For example: In the example to the right, this person's goal of retiring at 62 years of age is achievable, and from their current age and position results in about 37% of their future life being spent saving (working), 35% in active retirement (Phase 1), and 28% in the second phase of their retirement. If, however, they reduced their household expenses by about 6% they could change these proportions to 22%, 50% and 28%; a 40% increase in their active retirement years. |
Volatility-adjusted returns: adjusting for time, cash flows and leverage
|
A simple model to help clients better understand the difference between volatility and risk. We illustrate that even for a risk-averse investor, many assets exhibit non-linear mean reversion properties that, over time, can present an excellent opportunity to enhance returns without increasing the likelihood (or severity of) permanent capital loss.
We also include examples showing the difference in outcomes if their "exit" is brought forward or pushed back 10 years (this was made for aspiring retirees, or investors with a specific investment horizon). By educating our clients on the impact that volatility has on investor behaviour and expectations, we find that many investors come much more accepting of volatility and comfortable taking a long-term view of their investment journey. |
Economic & Market Analysis
The Case for Cash
|
Consider a scenario where cash is yielding 1%, and equities, although looking incredibly expensive, are still offering a yield of about 5%. Your client is fairly risk averse, yet requires 6% per annum to meet their long-term objectives. What do you do?
This model is designed to help answer this question. Here we compare the cash flows of an asset (such as equities, infrastructure) against a the yield curve of a risk-free benchmark. We then calculate how much our risky asset's price must fall to eliminate the return advantage, and convert this into a probability based on the asset's valuation relative to fair value, and the cycle length of that asset. This model does not tell us by how much an asset might under or out-perform, but rather the probability it will happen (a model that includes the magnitude of change is included in our "H-Minus" model). We also include a snapshot of the return profile from both Put and Call ATM options (priced using BSM). This is particularly useful for investors with large unrealised capital gains and/or highly variable tax rates who might wish to defer the sale of assets. For example, if we believed an asset to be grossly overvalued, it might be more cost-effective for a highly taxed investor to defer the sale of the asset and instead reduce risk by selling call options and using the premium income to buy Put options. |
Schiller CAPE Analysis (R, Shiny)
|
An interactive dashboard illustrating the distribution of prospective returns relative to the Cyclically Adjusted Price-Earnings Ratio (Case-Shiller PE, or CAPE).
This application was built using R (code), with graphics interface loaded through Shiny App. Data sourced from Professor Shiller (Yale University), with CSV files uploaded from Quandl. Current to July 2017. |
Mean Reversion or Momentum: what works?
(market research using algorithms in Python) |
What can past returns tell us about future risk and returns?
Our analysis seeks to help determine whether there is a connection between recent returns (over varying look-back periods), and future returns. If there is, a positive connection may suggest that long-term alpha can be generated through momentum-oriented strategies, while a negative connection might suggest that mean-reversion is the dominant force. To speed up our analysis and reduce human error I constructed an algorithm (written in Python, deployed through Quantopian) to sort, rank and measure 208 pieces of data over ten years to 31 January 2018. Final analysis of the results was run through Excel, allowing the user to specify factor weights and rules to help select portfolios (Qualifying Portfolios). I have also experimented modelling the results through MATLAB. This research uncovered some surprising results on the impact that liquidity may have on prospective returns from both mean-reversion and momentum oriented approaches to algorithmic trading. We find that over the sample period an investor could have earned superior risk-adjusted returns by combining both approaches. More information can be found here. |
Effects of Monetary Policy and Risk Premiums on Asset Price Returns
|
I built this spreadsheet as a way to quickly and easily illustrate the effect of changes to monetary policy and market risk premiums for publicly listed companies with different capital structures.
To do this, we approach the problem from an accounting perspective; starting with our RoA and analysing the effects of leverage on earnings, then letting the market how it prices the risk associated with the different rates of leverage. From there I allow the user to make changes to estimates of interest costs, earnings growth rates and risk premia, plus the time we expect it to take for our forecasts to persist before returning to more stable market conditions. Also included is inflation data, which is used to predict changes to interest rates using a version of the Taylor Rule. Results are provided in a separate three-dimensional wireframe. |
Property vs Shares (Simulator)
|
This simple calculator was built to accompany a series of reports I presented over the 2011 to 2014 period focusing on property economics and the factors influencing supply, demand, and prices of Australian residential property (particular focus on Melbourne).
Using price data from residential property prices, the All Ordinaries, plus interest rate and inflation, we provide users the opportunity to model a wide range of "what if" scenarios, with starting dates as far back as the mid-70s. Crucially, we also allow the user to specify ongoing costs for each asset class, plus leverage. This provides a more accurate "side by side" comparison of results. |
Other
4Sight (trading simulator)
|
4Sight is a trading simulator designed to help advisers test, learn from, and improve their investment decision-making processes.
This simulator uses an series of algorithms to model real-world (non-parametric) returns while incorporating exogenous variables, such as political, economic and market events. Importantly, while the data driving the simulator is real, to encourage investors to look at the data supporting their investment decisions (and reduce time series bias), the names of countries have been changed. Investment costs are also included, as are alternative assets such as residential real estate and commodities. To test the investor's skill level, 4Sight also pits the investor against other investors, each of which is programmed to implement their own unique investment style. Background The ideas behind 4Sight not only come from observing the behaviour of investors (and investment advisers) but also from the growing body of research into neurofinance which finds that investors' decision-making process and abilities are often influenced by the body's physiological responses to stimuli (in particular the cognitive response to fluctuations in testosterone and cortisol). Learning to compensate for, if not control, one's biases (emotional, cognitive and chemical) sets the framework for a more rational, robust and consistent approach to investment and risk management. In Development: 4Sight+ 4Sight+ will allow the user to run their portfolio (in the simulation environment) as a professional investment fund. This includes modeling fund flows based on both relative and absolute performance (after-fees), as well as business and staff costs. The idea to include these features comes from the observation that many investors are willing to underperform, and pay higher fees, so long as returns were not negative, while many investors feel compelled to withdraw from financial investments or managers if their performance is negative, even returns were better than their peers. |
Superannuation Fund Review (2017)
|
For many people, choosing a superannuation fund can be a daunting task. This calculator was prepared to help simplify the process.
Here we compare Australia's largest 49 public offer funds which together represent over 21 million individual accounts, with a total value of approximately $1 trillion, or 79% of the total assets managed by APRA regulated funds. In addition to analysing fund performance and fees, our analysis considers client cash flow (contributions and withdrawals), investment strategy, taxation, and member engagement in order to identify the funds most likely to meet their requirements. |
Investing in Small Business: Analysis of cash flow and capital structure
|
A simple to use calculator illustrating the effect of leverage, risk, and changes in the P&L on shareholder wealth.
This spreadsheet isn't intended to replace a business plan, rather it is designed to be used as a "sanity check" for prospective purchasers, allowing them to quickly and easily see the effect of different funding structures and changes in business performance measures. This model contains a number of particularly useful features. For example, we provide the ability to model a two scenarios ("High" and "Low"). This is to encourage prospective investors to think about what might happen if business conditions deteriorate. We also include options to adjust earnings for both extraordinary and recurring items. An example here might be an allowance for a "Management Fee" by the new owner, or a large capital expense to launch a new product line or service. Example The example to the left is for a suburban tyre fitting franchise. The purchase was to be funded 60% bank, 23% from private funding sources (including vendor finance), and 17% ($83,000) by the investor. Included in this purchase price is $92,000 to purchase a new vehicle and specialist equipment to provide a "mobile tyre fitting" service. We have also allowed for a very aggressive marketing strategy, with a 50% increase in marketing allowance in year one, plus a substantial pay rise for existing staff; our objective here is to ensure key staff are retained, and to attract high performers from local competitors. Although he is not intending to be involved in the day-to-day operation of the business, the investor has also included Management Fees of $37,000 per annum. From this we see that if the business meets it's revenue targets the investor's 10-year IRR is approximately 36% p.a., or a return of about $1.7 million on his $83,000 investment (this is in addition to Management Fees received). We also see that our low-range estimates result in a 10-year loss of about $121,000 (representing his $83,000 investment, and cash losses of about $38,000). Armed with this information, the investor may reconsider his growth strategy and capital structure. |
Recommended Reading
|
I and am often asked by clients and other advisers which books I might recommend on different subjects.
To help answer this question, I recently started tracking my reading list; ranking each book by a range of factors, such as readability, innovation and content subject. This way I can easily supply a "Top 10" list that will suit the person's topic interest and level of knowledge. It is also helpful for me, as it identifies which books might be worth re-reading. As I only recently started recording my reading list, there are only around 70 books loaded. Over the next 3 - 5 years I hope to round this out to 200 - 300. |
NAPLAN Analysis (School Performance)
|
Choosing a school is difficult. As is often the case, most people only have anecdotal evidence to support their claims on which school is best, or most suitable for their child.
Fortunately, with the introduction of NAPLAN several years ago, we now have quantitative data to help us compare schools objectively. This simple calculator allows the user to compare school performance across a range of factors, weights and time, as well as providing an indication of how each school compares on cost, and relative to the national average. Though not work related, it provided some interesting results that have now been used across some of our local schools in presentations to teachers and the families of prospective students. |