Observations using RitchViewer
The images below are the result of using RitchViewer to see visual stories come to life in a spreadsheet. The authors are reputable financial model training firms, wall street analysts, and university professors.
This exercise is not a criticism of the individuals or institutions. As such we are not showing the underlying spreadsheets; what we are demonstrating is the power of visualisation and animation. Again, keep in mind these people or institutions are very good at their trade, but despite this within seconds of using RitchViewer on their spreadsheets we were able to identify issues that might be anomalies or might represent structural changes to underlying company or data, that represent opportunities.
Decline in forecast Fixed Assets
As an equity portfolio manager when evaluating financial models I have been supplied, one of my favourite places to look at is how the analyst is forecasting future capex spend. Having run my own global business, my rule of thumb is that for every $1.00 of capex spend missed there is another $1.25 of expenses that need to impact the profit & loss, so understated or overstated capex can have a significant impact on earnings and valuation. Typically I look at Capex/Sales or Capex/Depreciation as measures, but for this demonstration and evaluation I will be looking at Fixed Assets (PP&E) on the Balance Sheet of several supplied models.
For the following extracts from models, you can see the dilemma of the forecast period indicating a decline in Fixed Assets where their is no historic evidence of the company no longer growing its asset base.
Financial Model Training firm
The images below were generated using RitchViewer over a financial model supplied by a firm that specialises in Financial Model Training in Microsoft Excel. Their website shows training locations across North America, Europe, and Asia; with corporate clients such as JP Morgan, Bank of America, Mizuho Bank, and E&Y; and training in Financial Modelling, Business Analytics, and external exam preparation.
The model was for a listed company with only a short amount of available history. The first chart on the left shows two years of historic and six years of forecast Fixed Assets (Property Plant & Equipment) numbers. The charts' right axis relates to the line chart which is Fixed Assets as a percentage of Total Assets. So you can see that the Fixed Assets are not only being predicted to fall in absolute dollars, but they also fall relative to Total Assets.
With the swipe of a mouse RitchViewer allows us to see the data in a different form. The second chart on the right goes further giving us just a view of the second-order data of "Fixed Assets/Total Assets". This chart includes the horizontal lines that show historically "Fixed Assets/Total Assets" were an average of18% and now on average the analyst is forecasting "Fixed Assets/Total Assets" at 6%; with the final forecast year being as low as 1%. So as an observer you have one of two choices; the analyst is missing something, or the company is going through a structural change.
Investment Bank Financial Model
Looking at the same topic, the images below were generated using RitchViewer Finance Pack over a financial model from an Investment Bank. This model has been passed down from analyst to analyst. The first chart (left most) shows a growth in "Property, Plant & Equipment" (PP&E) during the historic years and into the first forecast year. After that, we can see a decline; Why?
The PP&E formula in this model is increased with growth of Fixed Assets (Total PPE), and decreased by Depreciating previously purchased assets. If we use PathFinder to drilldown further, then we see a steady increase in Total PP&E (assets are growing), but an acceleration in Depreciation is causing the apparent decline in Net PP&E. Why is the analyst predicting accelerated depreciation of assets? This Insight gives you the ability to engage with the analyst at a detailed level and as such a competitive advantage over your peers.
Although the charts are depicting Annual data, the model was built up Quarterly making data exploration and visualisation by other products very time consuming to the point it is practically impossible. With RitchViewer we can choose just to see the Annual data, and we can choose to see data in hidden areas of the spreadsheet.
Again, theses are not inexperienced financial analysts, just people without the tools to quickly visualise their own models.
Investment Bank - Discounted Cash Flow Model
"Working Capital" is the capital that is needed to be set aside for the increases in Accounts Payable, Accounts Receivable, and Inventory that goes with any increase in Revenue. In the charts below the Analyst has rising Revenue, yet their data for "Increase in working capital" falls to Zero in the second forecast year. A quick calculation using the analysts own model showed a 6% impact on valuation by having this number missing. Now 6% may not appear much, but Finance Theory tells us that extracting true Alpha for the stock in question is difficult due to the breadth of analyst’s coverage. Using public data from a group of 28 Analysts in the Market that covered this stock we find that the Coefficient of Variation in EPS is only 2.7%. This means the 6% impact on valuation is around double one standard deviation from the mean of Analysts Estimates.
Had the analyst been able to easily visualise the Intrinsic Value Statement in their financial model, then this anomaly would not have been missed.
Toll Road Infrastructure Project Financial Model
A Toll Road Financial Model was downloaded from the web. When using RitchViewer to walk through the "Projected Profit and Loss Statement" a noticeable jump occurred in Interest between Year 11 & 15. The same data flowed through to other line items, such as Tax where you can see the decline matching that period. Is this real? There are 32 years of data in this model. Without having RitchViewer to quickly walk down the rows of 32 columns of years it is unlikely this characteristic would have ever been found.
Drilling down with PathFinder, we were able to establish that these are payments for Zero Coupon bonds. Given the project impact at the time of payment, either the model is wrong, or the owner needs to re-evaluate their project.
Viewing Educational Data
The following charts have been extracted from higrowth.xls found at Stern School of Business at New York University
In this case we are looking at columns of Ratios on the "Industry Averages" sheet and what they might mean. We have set RitchViewer to sort the Y-Axis Descending so we can see the spread of each ratio as we walk across the columns. Each row represents an aggregated sector. If we look at "Effective Tax Rate", we see a normal spread of rates with much of the curve well below the USA corporate tax rate. The chart of more interest is the one of "Pre-tax Operating Margin" where REIT's are above 100%. Given Real Estate Investment Trusts distribute their earnings, we understand this will impact other columns in the sheet, but this implies Pre-tax is greater than Sales?
As a Hedge Fund Manager, the cynic in me believes their is bad input to the sector data; but then again this may be real. Either way, I would firstly hide the row when looking across the various columns in the model so that I get a clear picture. Secondly, I would want to determine why REITs in this model have this characteristic.
Another sheet on the model is "DCFValuation". Walking down the rows, we can view financial drivers of the model such as revenues, margins, tax, depreciation, capex, change in working capital. When I reach Free Cash Flow to the Firm (FCFF) I note an anomaly at year 4. With the swipe of a mouse I can look at "Change in FCFF" which highlights year 5 as the possible issue. Walking back up through the data, given the shape of the chart, I can postulate this has something to do with the Tax structure in place. Is this real?
We can drilldown on year 4 & year 5 on the "Taxes" chart and see the underlying formulas for Taxes. PathFinder is showing in Year 5, there are no Net Operating Losses (NOL) to carry forward, where as year 4 still has a residual NOL of $615m against and EBIT of $625.
This has just been some Observations of Financial Models using RitchViewer. Again, where we have found potential issues, these have not come from inexperienced financial analysts, just people without the tools to quickly visualise their own models. RitchViewer is not an audit tool but what it does do is supplies visualisation through animation and allows the human brain to do what it does best; making us both the audit tool and more importantly the source of Insight.