Excel Visual Basic for Applications

Excel Visual Basic for Applications (VBA) can reduce data processing time by tens of hours each week and turn conventional spreadsheet analysis into interactive and insightful executive information.   Excel VBA training programmes from BG Consulting  enable delegates to automate conventional data collection procedures and run summary reports with the application of Excel VBA coding procedures.  Delegates learn how to develop interactive scenario analysis and provide an intuitive interaction portal that can be used for strategic business planning and what-if analysis.  Our Excel VBA courses will enable you to turn Excel into a sophisticated executive information system capable of providing interactivity and insight that surpasses that of much more costly financial planning software.  If the provision of your regular financial reporting in your organisation consumes a large proportion of your finance staff resource and you remain unimpressed by the huge pack of data tables each month, your finance team need to attend Excel VBA training arranged by BG Consulting.    

Peer to Peer Lending - How Should Banks Respond?

How Should Banks Respond?

P2P is probably most readily associated with the consumer-focused Zopa, which claims to have created the market almost ten years ago. However, the Chart shows that small businesses are also benefitting from this new technology:

P2P Lending: 2013

There is potential for P2P to change the way that businesses are financed. A key question for banks and other organisations serving small business is:

Is this an Opportunity or a Threat for Banks

The answer to this is, in part, dependent upon whether P2P is here to stay.

P2P has started to do something that banks either cannot or will not – filling the “financing gap”. Bankers have typically complained of the high “costs to serve” and lack of transparent financials associated with small businesses. Have P2P platforms cracked something that the banks have struggled to do for decades or even centuries?

Algorithms Linked to Social Networks

Are the P2P algorithms, with their linkages to social networks, really so much more robust than those developed by leading banks? Does this mean that they are better able to understand risk than the banks?

Traditionally, lending to small business has been relationship driven, which tends to be high cost in terms of both attracting suitable borrowers and in the credit analysis process. It is not difficult to imagine that there can be efficiencies in web-based customer acquisition, but this is available to online lenders without their being P2P. The P2P platforms could really have a sustainable advantage if their credit analysis is capable over the complete cycle of predicting probabilities of default (“PDs”), loss given default (“LGDs”) and the resultant Expected Losses (“ELs”). So, if P2P platforms remain better able to mine useful credit intelligence from social networks, the banks could face increasing competition.

Pricing Decisions

Lenders are probably not being altogether altruistic when they invest surplus funds across a P2P platform. The vast majority will, of course, be seeking to achieve a higher return than they could expect from a bank deposit. It is likely that all, or most, such lenders will appreciate that there is a significantly different risk profile in cutting out the middleman – i.e. a bank with deposit protection. Does the P2P lender: (i) have access to the risk adjusted return models which are standard in the banking industry, and (ii) have the ability to make informed investment decisions based upon these?

Funding Cost Advantage

It is true for the vast majority of P2P lenders that they do not suffer the formal requirement to allocate shareholder capital against their lending as is imposed upon the banks through the Basel regulations. While P2P lenders might consider their opportunity cost of capital to be low, this might be a little misleading relative to the funding advantages (leverage and often zero interest deposits) enjoyed by the banks. Banks have learned over many decades how to price for increasing tenor and this is something that might be new to P2P lenders.

Portfolio Diversification

One of the attractions of P2P lending appears to be that the transaction costs for lender are very low, meaning that it is quite easy to construct a portfolio of loan participations. The ability to diversify across several (or many) borrowers might be a comfort to lenders, but do they have the tools to assess whether they are achieving diversification benefits? How significant is the potential that their portfolios contain unrecognised concentration risks? In banking, there is a saying:

“Concentration kills”…

Investor Protection

Several P2P platforms state that they are able to offer varying levels of investor protection, and if effective this could be a good mitigant for the above objections. For example: Landbay says it offers “protection funds”; Lending Works says it has “insurance”, and Madiston LendLoan Invest advertises its “compensation scheme”. The devil is always in the detail, and it is important to have a concept as to how these mitigants operate and who stands behind them

Attractions to Borrowers from P2P

Just why are small businesses starting to use these platforms? Is it because the P2P process is quicker and less bureaucratic? Are they able to source cheaper, longer term funding from P2P compared to banks? Are P2P lenders financing borrowers who would be turned away by the banks? Or are the P2P platforms cherry-picking the banks’ best small business prospects? Can banks enjoy a better relationship with customers who source their financing from P2P lenders, enabling the banks to cross-sell non-credit products? The answers to these questions are likely to be central to the type of competitive response from the banks.

Some Key Challenges for P2P Business Models

Incentive Problems

This is essentially a broker model, wherein the provider of the P2P platform takes little or no financing risk. A root cause of asset bubble driven banking crises has often been a skewed reward structure wherein relationship managers have been incentivised to generate asset growth with little or no regard for the risks that they have piled onto their employers’ balance sheets.

If the P2P platforms have no risk of capital loss, could this mean that credit quality within the system might deteriorate and simply be passed around many P2P lenders rather than (as is currently the case) remaining in the hands of the originating bank?

Monitoring and the Identification of Early Warning Signals

Through the inclusion in their credit algorithms of behavioural factors (often drawn from social media), it is conceivable that the P2P platforms will do a better job of identifying developing problems than incumbent lenders (who have struggled with this since the beginning of time!).

However, it will be important to see how much attention the platforms will pay to advising their lenders of changes in the borrowers’ credit quality.

Without question, the earlier that problems are identified, the better the lender’s prospects of making a full recovery.

Problem Loans 

Who will take responsibility for negotiating restructurings when problems develop? This is a highly specialised, time-intensive and costly process. Loan workout and restructuring skills and cultures vary widely between existing lenders. How will P2P platforms perform?

Education of Lenders

Banks have invested huge sums in training their staff in credit analysis and associated topics. Will all (or even a reasonable percentage) of P2P lenders have the same or better understanding of these principles and techniques? And if the P2P platforms are to educate/ train potential lenders, is there not a significant conflict of interest?

There are, of course, myriad other issues for P2P platforms to manage, not least of which are potential fraud, anti-money laundering and KYC. However, within this article I have sought to identify some of the strategic questions and challenges with a view to providing bankers with a starting point for framing their strategic responses.

For assistance with developing your strategic response, training relevant staff or to provide feedback/comments on this article, please email me at:

adrian.grant@bgconsulting.com

READ MORE 

Financial Modelling: Model Design

Best Practice

Ultimately, the best practice is to invest time, thinking about a model before it is built, focusing on what it is needed for and what is the best way of structuring the model in order to achieve its objectives. The more time spent thinking about a model at the outset will save time later in the production process; remember that computers do not think, so we have to do the thinking for them. Once the objectives of the model have been thought about and the structure of the model set, the next most important rule to bear in mind when building a model is to keep it as simple as possible! Ask the following questions constantly when building any financial model:

Although a model will always reflect the way that the designer thinks, there are a few steps that can be taken to ensure that somebody else could use, and improve the understanding of, any model. Utilising these steps will also help to minimise errors:

Financial Forecasting & Modelling

Introduction

In order to value a company or understand its credit position, it is necessary to have a view about its future profitability, financial position and growth.

The assessment of future profitability, financial position and growth is based on assumptions about how fast a company will increase its sales, how much it will need to invest in order to be able to produce its goods, and how efficiently the company will be at turning sales into profits. 

Making assumptions is not an exact science: assumptions are reasonable or unreasonable, aggressive or conservative. On the other hand, producing an integrated set of financial statements based on agreed assumptions should only give one potential outcome: the answer is either right or wrong.

In building any financial forecast model, it is easy to become too engrossed in the details and lose focus on the aggregate result. It is often the case that once a model is built, it is better to improve a valuation by matching a decline in future ROCE (Return On Capital Employed) against a reduction in competitive advantage, rather than precisely modelling accounts receivable in 20 years’ time. There is value in thinking about how to go about forecasting and what level of detail is required in the model.

This manual covers how to build a fully integrated Excel model to forecast the financial performance of a company, based on reasonable assumptions and on the correct mechanics. It discusses how to achieve realistic forecasts and provides guidance in terms of model design, structure and formatting best practices.

Types of Valuation

Standalone Valuation

As discussed above, there are two main types of valuation used to value a company, either an absolute valuation tool which attempts to arrive at a nominal valuation, or a relative valuation tool, which uses the value of other companies in the market to determine the value of the company being valued. The two types of tool and approaches have been neatly summarised by the following quotes by two successful investors:

An absolute valuation approach produces a nominal valuation, i.e. the value in pounds, dollars or euros that the company’s shares should trade at if fairly valued. This type of valuation is often called an “intrinsic value”, because it is highly unlikely that the company’s shares do trade at this value. An absolute valuation approach assumes that the market is inefficient at valuing companies. 

A relative valuation approach produces a valuation based on how other similar companies are valued by the market and assumes that the market, in aggregate, is correctly valued but that inefficiencies in valuation exist across the market or individual sectors. Both absolute and relative valuation tools are generally used to value a company on what is called a standalone basis, i.e. the company being valued is being valued in isolation.

Market Practice and Combination Valuation

In practice, a combination of absolute and relative valuation tools would be used to value a business. Often an absolute valuation tool such as DCF would be used as the first pass at valuation and then a cross-check would be performed using relative valuation tools such as multiples to gauge how the market was valuing similar companies. Other absolute valuation methodologies would be Economic Value Added (EVA) or Adjusted Present Value (APV).
In addition to valuing companies on a standalone basis, in practice valuation tools are often used that assume an element of corporate activity will take place. This can take the form of a comparable transaction (comptrans) valuation, where recent corporate transactions are used as a benchmark to determine the value of the company being valued. The assumption made is that because other companies have been acquired at certain valuation levels that these transaction values are relevant to the value of the company being valued. Corporate activity can also be incorporated by way of a combination valuation, where the target company’s value in a transaction will incorporate elements of value that would only arise as the result of a transaction, such as the synergy benefits from combining two companies.
Other specialised valuation tools include Leveraged BuyOut analysis (LBO) where a valuation based on the restructuring of a company’s capital structure in the form of a private equity, venture capital or Management BuyOut (MBO) transaction is carried out.

Key learning points:

1. Standalone valuation approaches include absolute valuation and relative valuation. An absolute valuation approach assumes that the market is inefficient and a relative valuation approach assumes that, although the market in aggregate is efficient, inefficiencies exist across the market.
2. Other forms of valuation include comparable transaction valuation, combination valuation and leveraged buyout valuation.

Cleaning the Numbers – Arriving at Operating, Financing and Non-core Items

Unless privileged access to inside information on a company has been given, the information available to produce a valuation will be limited to public data, which primarily means the historic report and accounts plus other regulatory filings. Historic information is often used to forecast forward trends in profitability and cash flow that form the basis for a valuation or to compare market valuations across sectors. However, before the information in historic accounts can be used to compare or value companies, it is necessary to ensure that the information being used is relevant for the valuation being produced.

Cleaning the Numbers

Historic accounts are produced for a wide variety of interested parties, from employees of the company through to investors and industry regulators. Accounts therefore include a lot of “noise”, items that are not strictly relevant for valuation or analysis. Accounts are also required by law to include all the transactions that have taken place during the period, whether they relate to the underlying activities of the company or not. It is therefore necessary to decide which items included in the accounts relate to the underlying performance of the company (operational items), which items are being used to finance the company (financing items) and which items do not relate to the company’s underlying activities (non-core items). This exercise must be performed to get to any starting point for comparison or valuation.

What is required is an exercise to “clean up” the accounts, using knowledge of the company to identify the underlying profitability and cash flow of the business. A relative valuation, such as a comparative company (“comps”) analysis where comparisons across a sector are made, will allow us to produce an analysis of the group of companies where the accounting data is truly comparable. In an absolute valuation exercise, the clean ongoing profitability and cash flow calculated can be used as the starting point or “base camp” for a forecast that will allow the extrapolation of historic trends to produce an income and cash flow statement if there are no detailed forecasts.

As well as the annual report and accounts, data from stock market filings such as the 10K or 20F (US filings) are available. A 10K filing is filed by quoted US companies and is similar to an annual report, but covers much more detail about the business. As such, it is a very good source of information for making adjustments. A 20F filing is completed by a non-US company that has securities listed in the US (say through an ADR, an American Depository Receipt) and the 20F covers much the same information as a 10K. Broker forecasts can be used for adjustments if the broker has a particular insight into the way a company operates and Bloomberg is generally used as a source of market information.

Income Statement Adjustments

There are three main types of adjustment to the income statement:

Adjustments to Operating Income

Fortunately, under IFRS there is now complete disclosure of the continuing and discontinuing operations of the company. The decision to sell is the determinant of the split between the two, so as soon as management decides to sell part of a company its results must be reported separately. Discontinued business performance can therefore be excluded when making adjustments. 

The management of a company will often try to massage various items to improve the reported profitability of the company. Losses, which reduce profitability, will be treated as exceptional and excluded from highlighted performance lines or ratios (such as net income, EPS, etc.). Gains will be classified as part of the normal operations and therefore boost profitability. Adjustments must be made for this manipulation to get back to core, repeatable profitability. 

Goodwill is another item for which an adjustment must be made. Goodwill impairment now reflects over payment for an acquisition/asset and does not reflect the economic wasting of an intangible asset (which is now separately reported under IFRS). If goodwill impairment has been included in EBIT, it must then be removed.

Adjustments to Financing Items

There are various accounting issues that may have to be adjusted for in financing items, but the main items requiring adjustment would be pension costs and leases. Pension costs as reported will sometimes include the interest and investment return element of the pension cost, which are financing items. As pension costs are often reported in the EBIT number (i.e. pre-financing costs), if the pension cost includes an interest/investment return element, this should be removed from EBIT line and included in financing costs line. Operating lease expenses not capitalised may also be included in EBIT. These will include the interest portion of the lease payment, which again should be separated out of operating costs and included in financing costs.

Taxation

If any of the items that have been included as an adjustment have a tax effect, this must be adjusted for. One of the key problems with reported numbers is that sometimes pre-tax items have a tax element to them. For example, gains or losses on sale or restructuring costs may be included net of tax, but above the tax charge line in the income statement. Unless the amount of tax is disclosed in the notes to the accounts, it is often impossible to figure out what the tax charge would have been, so it is best not to make any adjustments. This will lead to a slightly incorrect EBIT number if the item is included above EBIT, but the amounts involved are usually immaterial. If the amount of tax is disclosed in the accounts, then both the EBIT, tax charge and the net income figure can be adjusted.

What type of item is taxation? The answer is a bit of operating and a bit of financing, as tax has to be paid on profits, but the financing charge relating to interest is deducted from profits to calculate the income statement tax charge. In practice, tax is treated as an operating item when producing a valuation, but the tax charge in the income statement is re-calculated to strip out the effect of interest. Operating returns are calculated as EBIT − Marginal tax (calculated as EBIT × Marginal tax rate), the marginal tax cash paid for each £1 or $1 of operating profit earned. In terms of the balance sheet, the current tax asset/liability is treated as an operating item but the deferred tax asset or liability, being an estimate and a provision which may or may not arise, is treated as a financing item.

Summary

Key Adjustments Summary – Income Statement

There are three main areas where adjustments may be made to an income statement, the main one being one-off items. This will include restructuring costs, that will not be part of operating revenues/costs going forward, impairment of goodwill, which reflects overpayment for goodwill and is therefore not really an operating expense (more of a financing cost) and finally profit/loss on sales of assets, which again is not an operating income/cost (as depreciation should reflect the effect of changes in value of assets).

Adjustments for accounting issues mainly relate to pension and lease accounting. Finally, adjustments must be made for results from discontinued operations which, by their very nature, are not part of the underlying operations of the business.

Cash Flow Statement Adjustments

The cash flow statement will not be a focus for adjustment as a historic cash flow statement is not included in a financial forecast. If the adjustment is part of a comps analysis, then the adjusted profit for the period from the income statement would be the starting point for the operating cash in the cash flow statement. This way, the operating cash flow incorporates all income statement adjustments. The only other adjustment that may be required is the removal of any cash flow from non-core assets, which would be included in financial income.

Balance Sheet Adjustments

In the balance sheet, the key adjustments to be made are the separation of non-core assets from underlying capital. The three main non-core items are associates, asset/liabilities held for sale (now separated out under IFRS) and investments, in effect anything that is not consolidated or that is identified as for sale. Joint ventures are difficult to judge and should probably be included in noncore assets, but this will depend on the terms of the joint venture (buyout clauses, etc.).

Generally, associates (or any investment that is not consolidated) would be treated as a

non-operating item. As such, any income or cash flow from associates is not included in underlying returns and the investment in associates is excluded from the core assets in the balance sheet. If associates are included in the core operations of the business (e.g. if there is a very strong trading relationship with the associate), then associate income would be incorporated in operating income and the investment in an enterprise value calculation. Depending on the degree of disclosure (it helps if the associate is quoted), the associate results can be incorporated to varying degrees of detail.

Key learning points:

1. Historic accounts include various items that do not relate to the underlying performance of a company. Historic accounts must therefore be “cleaned” up before they are used to compare companies or forecast forward returns.
2. The main adjustments made to the income statement ensure only continuing items are included, that one-off items are excluded and that accounting anomalies such as pension costs are adjusted for.
3. The main adjustments to the balance sheet relate to non-core assets being removed.

Matching Returns and Capital – Operating, Financing and Non-core Items

As discussed above, returns and capital are used in various valuation approaches to produce either an equity or enterprise valuation. Depending on the approach used and the valuation produced, there will be a number of adjustments to be made to the valuation to arrive at the estimated value of the share price of a company. Assuming that an operating enterprise value is produced, this would have to be adjusted as follows:

In calculating an operating enterprise value, only the operating returns and capital would be used.The value for non-equity finance would be calculated using the financing returns and capital and the value of the non-core assets would be derived from the non-core returns and capital. What is crucially important is that the correct returns and capital items are used in each part of a valuation, e.g. returns from non-core assets should not be used to calculate the value of operating assets, etc.

When producing a valuation therefore, the elements of a company’s returns and capital must be broken down into three key areas:

A calculation of enterprise and equity values will start by analysing the operating and financial items in a set of accounts. Operating items relate to enterprise value, the value of the entirety of the operations of the business, while financing items are deducted from enterprise value to get to equity value. Non-core items are extracted from the accounts and valued separately. As discussed, an example of non-core items would be Nestlé’s investment in L’Oréal, which is a non-core investment (food vs. cosmetics).

Operating Items

Within a set of accounts, operating items in the income statement include EBIT (earnings before interest and tax) and any items above EBIT (profit before interest, which is paid away to non-equity financiers). In the cash flow statement, the operating items are detailed in cash flow from operations (again before financing payments/receipts) and in the balance sheet, operating items are the net operating assets of the company.

Looking in a bit more detail, this means the return items that are operating items include revenue, EBITDA (Earnings Before Interest, Tax, Depreciation and Amortisation) and EBIT, and the capital items include operating assets such as fixed and intangible assets and working capital (current assets less current liabilities).

Financing Items

In a set of accounts, financing items in the income statement include items such as interest in the income statement (to get to profit after interest, which is available to equity holders), cash flow from financing and investment in the cash flow statement and non-equity finance in the balance sheet.

This means financing items will include interest payable and receivable and other financial

income/expense from the income statement. These would be deducted from operating returns to get to equity returns. In the balance sheet, financing items include net financial debt, provisions and minority interests.

Financing items in total make up non-equity finance (as detailed above) and in each area would be defined as follows:

It is worth commenting at this point on the definition of net financial debt. The simplifying

assumption made above is that any cash (or marketable securities) held by a company is not required to finance the operations of the company. However, there will be occasions when a company does require cash to finance its operations, e.g. a retailer that requires a cash float for its tills or a travel company that is required to post a bond as part of its ABTA membership. If cash is identified that is not surplus but an integral part of the operations of the company, then it should not be deducted from financial debt but instead included in working capital as part of the operational assets of the company.

Non-core Assets and Liabilities

Non-operating or non-core items relate to unusual items or investments that the company has made that are not part of the underlying operations of the business. They include assets that are no longer part of the business (discontinued operations, disposed assets) or investments that do not relate to the company’s ongoing operations, e.g. Nestlé’s investment in L’Oréal.

In the accounts of a company, non-core assets relate to anything that is not part of the underlying returns or capital of the company. In the income statement, this would generally include restructuring charges (unless recurring), impairments and profits/losses on disposal of assets/businesses. Disposal profits or losses may be operational for some types of company, e.g. for a car rental business (constantly turning over its vehicle fleet). Under IFRS, assets and liabilities held for sale are separated out in the balance sheet, so these are specifically identified as being non-core. For the purpose of this manual, it is assumed that associates (or any investment that is not consolidated) are non-core. Investments in unrelated businesses are always non-core and joint ventures are generally treated as non core, unless the joint venture is proportionately consolidated. 

The main problem with non-operating or non-core items is the amount of judgement that has to be applied to decide what is non-core, which can be difficult to determine. The key issue is consistency. If, for example, associates are treated as core, then any associate income would be included in operating returns and the value of the associate would be included in operating capital.

Key learning points:

1. Returns and capital can be broken down into operating, financing and noncore items.
2. Operating items relate to enterprise value and include returns such as EBITDA and capital.
3. Financing items relate to non-equity finance and if deducted from operating items, produce equity returns or capital.
4. Non-core items do not relate to the core activities of the business and should be extracted.