The site uses cookies to provide you with a better experience. By using this site you agree to our Privacy policy.

Financial model reviews reveal interesting insights

Colin Prasad

My previous article about the Moore Stephens Victoria Corporate Finance team was on work we had been doing during winter on Initial Public Offerings (IPO), reverse takeovers (RTO) and an expert witness report.  Some of that work carried over into Spring with a successful outcome for a number of those clients. 

In this article I am writing about some of the interesting work we have been doing with financial forecast modelling – primarily for financiers.  I also find this somewhat encouraging that a number of our clients are out there getting finance for expansion and new businesses. 

Recently we have been asked to perform reviews of “3-way financial forecast” models.  A “3-way” model simply means a forecast of the profit and loss, cash flow and balance sheet.  As well as review of models prepared by others, we have also assisted in creating models for some clients. 

Whilst there are a bunch of automated tools around which work with core accounting systems (e.g., Xero) we often find that they fall short when trying to build in the underlying business logic and relationships between operating metrics (e.g., physical quantities of materials, stock, labour hours, etc.) and their financial impact.  Also, financiers tend to be interested in drilling down into key assumptions underpinning sales growth – particularly a “by” customer or “by” product and overall earnings period on period (bridge) analysis.

For example, my colleague Roy Zhang, (somewhat of a guru in excel) recently built several “3-way” models.  One model was for a scaffolding business another for a warehouse development business, and another for a pharmaceutical wholesaler. 

The business logic of the scaffolding business was about utilisation of tonnes of scaffolding equipment with the key decision on when to time CAPEX to support revenue growth.  That model was primarily for the business owners who were looking not just at the overall profitability of the business but more importantly, how much initial (equity) investment they needed to contribute.

The warehouse development model required the building of multiple development timelines as the client intended to develop multiple sites concurrently and each with its own separate costs and debt facilities. That model was used to calculate how much initial equity investment was required, the amount of leverage the business could support and also the internal rate of return on investor funds.

The pharmaceutical model was mainly for a bank proposal (which Sam La from our Debt Advisory team is leading) to fund an acquisition and working capital.  The key areas of this model focused around forecasting of stock levels and timing of cash flows through payments of creditors and collection of debtors.  This model was more complex as typically third parties (such as a bank) will require a more detailed assessment on how much they are willing to lend and what covenants to enforce.

We also assisted one of our Business Services clients in refining a “3-way” model for a bank particularly for assessing trade finance.  This client is a coffee importer and wholesaler.  The problem they were trying to assess was a “doomsday” scenario of a falling $AUD and rising coffee prices (in USD).  I’m not saying this will happen (so don’t panic about the cost of your latte), but the client wanted to understand what its working capital needs would be if it did happen.

Unfortunately, it didn’t look great.  To deliver the same quantity of coffee would require large increases in the value of working capital (stock and debtors).  The tricky part was to model the stock on a genuine FIFO (first-in, first-out) principle as the $A landed cost rises.  We tackled this with a stock “waterfall” in excel – showing the impact of what is nearly a six month lead time (including time on water).

Under some scenarios, stock purchased a few months before being sold “now” is half the price of stock being purchased now.  Strategies being considered for this client are locking in exchange rates and coffee prices earlier and perhaps increasing prices (if the market will bear it) in advance.  It’s actually a pretty difficult problem to solve but at least the client now has the tools to figure out what sort of funds are required, for how long and at what sort of cost or return on those funds.

Another client runs a specialty agricultural property investment fund.  Essentially it allows agricultural producers who undertake all the farm management to sell and lease back their land from the fund.  This frees up capital and comes at a reasonable cost of capital given that rent is deductible to the “farmer”.  The client fund was looking at a particular property and like any finance arrangement, wanted to be confident that the farmer could pay the rent given it’s a 25-year lease.  The farmer provided a “3-way” model which we were asked to assess. 

The focus for us on this sort of review is different from the other examples as in this case, it’s whether our client will get paid.  Therefore, they are less interested in “blue-sky” projections that may boost investor returns.  Our approach was to apply sensitivities to the model to determine what minimum revenue it needs to achieve to still break even (and pay the rent).  As the business was fairly new, the risk period is in the first couple of years, when sales are building on the back of CAPEX.  We identified problems with the model in terms of smoothing of cashflows (instead of actual quarterly payments) which had implication on nadir (lowest) cash balances, as well as some overall comments on the basis of assumptions. 

On this basis the board of our client was able to make further inquiries and satisfy themselves of the risks to the deal. 

Financial model reviews or model creation is somewhat different work from that of written expert reports, but equally fascinating for us to gain an insight into different industries and the risks and returns those businesses face for different providers of funding. 

For more information please contact:

Colin Prasad
Moore Stephens Victoria
Associate Director
P +61 3 9608 0213