Financial Forecasting, Analysis and Modelling. Michael Samonas

flows and thus determine the degree of risk associated with the firm.

      Furthermore, for the small business owner and entrepreneur who would like to project future financial figures of his business, financial modelling will enable him to prepare so-called proforma financial statements, which in turn will help him forecast future levels of profits as well as anticipated borrowing.

      Finally, as more and more companies become global through the acquisition/establishment of international operations, there is an imminent requirement for sophisticated financial models. These models can assist the business/financial analyst in evaluating the performance of each country's operations, standardize financial reporting, and analyze complex information according to the various industry demand–supply patterns.

      Financial modelling, unlike other areas of accounting and finance, is unregulated and lacks generally accepted practice guidelines, which means that model risk is a very real concept. Only recently certain accounting bodies, such as the Institute of Chartered Accountants in England and Wales (ICAEW), published principles for good spreadsheet practice based on the FAST Standard which is one of the first standards for financial modelling to be officially recognized.⁴ The FAST (Flexible Appropriate Structured Transparent) Standard is a set of rules on the structure and detailed design of spreadsheet-based models and provides both a clear route to good model design for the individual modeller and a common style platform upon which modellers and reviewers can rely when sharing models amongst themselves.⁵ Other standards include SMART, developed by Corality, which provides guidance on how to create spreadsheets with consistency, transparency, and flexibility⁶ and the Best Practice Modelling (BPM)⁷ published by the Spreadsheet Standards Review Board (SSRB).⁸ Nevertheless the above standards have not yet been widely adopted and the reader should be aware of the scope, benefits, and limitations of financial modelling. Always apply the “Garbage in Garbage out” principle.

      1.2 DEFINING THE INPUTS AND THE OUTPUTS OF A SIMPLE FINANCIAL MODEL

      A good model is easily recognizable. It has clearly identifiable outputs based on clearly defined inputs and the relationship between them can be tracked through a logical audit trail. Consider the following situation. Think of a wholesale company that wants to use a financial model to assess the financial implications of its credit policy. Let us say that the company has a 2-term trade credit agreement. In this agreement it offers a discount to its buyers if payment is made within a certain period, which is typically shorter than the net payment period. For example, a “2/10 net 30” agreement would give the buyer a discount of 2 % if payment is realized by the 10th day following delivery. If the buyer fails to take advantage of the discount, there are still 20 additional days in which to pay the full price of the goods without being in default, that is, the net period has a total duration of 30 days. Finally, as with net terms, the company could charge penalties if the buyer still fails to meet the payment after the net term has expired. It is expected that 30 % of the company's buyers would adopt the discount. Trade credit can be an attractive source of funds due to its simplicity and convenience. However, trade credit is like a loan by the company to its customer. There are 2 issues associated with loans: (a) what is the necessary amount of the loan and (b) what is the cost of it?

      Therefore, the company needs to build a model in order to estimate:

      a. the cost of the trade credit it provides to its customers, and

      b. the funding impact of it, on the basis that 70 % of the company's customers will not adopt the discount, given that it has an annual turnover of €10,000,000.

      So the model outputs should look like this:

      The Effective Annual Rate (EAR) is the cost of the discount offered by the company to encourage buyers to pay early and is given by the following formula:

      As far as the above situation is concerned:

      This cost is really high and means that the company offering the discount is short of cash. Under normal circumstances it could get a bank loan much more cheaply than this. On the buyer side, as long as they can obtain a bank loan at a lower interest rate, they would be better off borrowing at the lower rate and using the cash proceeds of the loan to take advantage of the discount offered by the company. Moreover, the amount of the discount also represents a cost to the company because it does not receive the full selling price for the product. In our case this cost is:

      Apart from the above cost, if we assume that the company's customers would wait until the last day of the discount period to pay, i.e. the 10th day, then the company should fund 10 days of receivables for turnover equal to:

      The factor (1–2 %) takes into account the discount. These 10 days of receivables, assuming a 360-day financial year, are equal to the following amount (as we will see in Chapter 2):

      If the €81,667 are financed by debt and the cost of debt is 8 % per year, then the company will bear interest of:

      That is, the company will bear a cost of €60,000 per year arising from the discount of 2 % plus a further cost of €6,533 as interest arising from the funding needs of the 10-day credit period.

      Concerning the 70 % of the company's customers that prefer the credit period of 30 days, this is equivalent to turnover of:

      This turnover, if funded for 30 days, gives rise to receivables equal to:

      Again, if the €583,333 are financed by debt and the cost of debt is 8 % per year, then the company will bear interest of:

      To summarize: the company will bear a cost of €60,000 per year arising from the discount of 2 % plus a further cost of €6,533 as interest arising from the funding needs of the 10-day credit period plus another cost of €46,667 as interest arising from the funding needs of the 30-day period.

      All the numbers that feed into the above formulae should form the inputs of the model and all the formulae will be part of the workings of the model as we discussed in the previous paragraph.

      Then, the inputs of the model should look like this:

      and the outputs of the model will look like this:

      where the funding impact of €665,000 is the sum of both the 10-day discount period and the 30 credit days (€81,667 + €583,333) and €53,200 is the cost of these funds per year at 8 %.

      So far you may have the impression that financial modelling is purely maths and finance. However, for a model to be effective, precise financial calculations are not enough and are only part of the equation. The second and equally important part is the appropriate application of subjectivity. Financial models that combine both maths and art become the models that are relevant and are actually used in business.⁹ In this direction we have used a common style for the headings of both the inputs and the outputs. Moreover we could have used blue colour for the inputs. We have used 3 columns to separate the particular inputs from their relevant unit of measure (UOM) and their proposed value. We started by defining first the outputs of the model that will answer the business question the model will need to address. Then we identified any additional information required in order to complete the model (i.e. the cost of funds/debt for the company). Only then did we write