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.9 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 down all the particular formulae and calculations that the model needs to perform.
As a final note to this specific modelling exercise, we mentioned previously that there was no need for any decision making. The model was constructed simply to enhance the business understanding of a particular company policy. Should any decision need to be taken about which credit policy is more efficient, we could model a number of different scenarios each with various credit policies. For example we could examine 3 different policies (2/10 net 30, 2/10 net 45, and 2/10 net 60) in order to choose the most favourable one.
1.3 THE FINANCIAL MODELLING PROCESS OF MORE COMPLEX MODELS
The financial modelling process is comprised of 4 steps as shown in Exhibit 1.1:
Exhibit 1.1 The 4 fundamental steps of the financial modelling process
Let us examine each of the above steps in detail.
1.3.1 Step 1: Defining the Problem the Model Will Solve: The Fundamental Business Question
Financial modelling is used, as we mentioned previously, in order to solve various problems. The first step of the process includes teams or individuals asking the right questions at the start of the problem-solving process. This is sometimes hard to believe as it often seems that people are trying to solve a problem before they have properly defined it. Asking the right questions helps break down the problem into simpler constituents.
For example the commercial manager of the company requests the financial analyst to present the impact on the bottom line results of the company of a New Product Development (NPD). Let us say that the costs of the whole NPD process are available and can be largely funded through government subsidy. In order to tackle the problem the financial analyst needs to ask the following questions:
1 What will be the forecast sales volume of the new product per year?
2 What will be the unit price?
3 What will be the credit terms?
4 What will be the inventory needs of the product?
5 What will be the payment terms of the suppliers of the raw materials?
6 What will be the incremental variable and fixed cost per year for the proposed production?
7 When is it anticipated that the governmental subsidy for the initial investment costs will be received?
The problem, then, can be broken down as per Exhibit 1.2:
Exhibit 1.2 Breaking down a business problem into simpler constituents
1.3.2 Step 2: Specification of the Model
Now we have identified the variables of the problem, we need a solid and thorough specification for a successful financial modelling process. The major assumptions should be documented and organized by category (such as market prices, sales volumes, costs, credit terms, payment terms, capital expenditures, and so on). All assumptions should be placed separately on a single sheet so that we do not have to hunt through formulae to figure out where a number came from.
Moreover, the specification of the model, depending on the problem we have to address, might include the following:
○ To formulate the standard financial statements, including the income statement, balance sheet, and statement of cash flow. For the problem described in Step 1, the balance sheet and cash-flow statements are used to determine the level of additional borrowing, although they are more time consuming than a plain income statement, provided that the new product development will be funded by debt. The interest expense of this borrowing is an expense line in the income statement that we need to forecast in order to answer the original question. In other cases, i.e. where a valuation is required, we would have to derive both the free cash flow and the Weighted Average Cost of Capital schedules as well.
○ To decide the time frame of our forecast and its granularity (time periods). This refers to whether calculations will be done at the monthly level of detail or on a yearly basis. This is important when projecting cash flows in order to ensure enough liquidity to withstand cash-flow spikes due to factors such as inventory replenishment, slow accounts receivable cycles, large quarterly tax payments, major capital purchases, and other events. Output results are normally monthly for the first forecast year, quarterly for the next, and annual for the rest of a full 5-year plan.
○ To group operating expenses by departments as appropriate for the specific industry. Typical departments might be General and Administrative, Sales & Marketing, Research & Development, or Operations. This allows a comparison of departmental expenses as a percentage of total expenses with other companies in the industry.
○ To decide which Key Performance Indicators (KPI) need to be calculated in order to address the problem in question. KPIs expressed as ratios such as revenue EBITDA cover or the quick ratio allow projections to be benchmarked against other companies in the industry.
○ To create various scenarios, in order to assess the impact of different strategies. That is, to evaluate a series of different model output variables given a set of different input variables.
○ To create a sensitivity analysis that shows what will be the impact of changing the major assumptions by equal amounts, in percentage terms. This allows us to determine which assumptions have the greatest impact on our forecast, and must therefore be thought out most carefully. It will also allow us to focus on the important model variables rather than getting lost among all model variables.
