LITMIR.BIZ

      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.

      ○ Finally, to create a control panel, i.e. a one-page summary where we can change the most important assumptions and see immediately how this impacts on the KPIs of interest.

      The importance of this step is to ensure that the proposed model is easy to read, easy to understand, easy to change, and simply easy to use. The way to make a model useful and readable is to keep it simple. The complexity of the transaction which has to be modelled and the complexity of the model itself are 2 different things.

      1.3.3 Step 3: Designing and Building the Model

      Designing and building the financial model is the next phase of the process. The specification phase (Step 2) should lay out the structure of the model in detail. In this step we first identify the outputs of the model. It is good practice to present the output of the model on a separate sheet. This output sheet is a combination of model inputs and formulae and should read directly from the workings sheet of the model. There may be more than one output sheet in case the resolution of step 1 requires the handling of uncertainty and creating sensitivity analyses. Moreover, the control panel described in the specification part of the modelling process, if any, is part of the output of the model. Next we build the assumptions sheet that forms the inputs to our model. If our model needs past data to build on, i.e. historic financial statements, we collect them and adjust them to the right level of detail. Depending on the problem we have to solve we will not need all of the income statement, balance sheet, and cash flow statement accounts, and thus some will need to be grouped together. Finally we build the workings or calculation sheets and fill their cells with formulae. Thus, the sheets where the calculations are taking place should always be separate from both the input and the output sheets. Also no hard-coded values should be typed directly into the calculations of the workings sheet. In case the purpose of the model is to forecast future financial statements, all the relevant key drivers of the historic financial statements are calculated and forecast into the future. The forecast key drivers will make the building blocks of the future financial statements as we will see in Chapter 3 where we will build a financial model from scratch in order for the reader to grasp all the aforementioned abstract rules.

      The following indicate best practices that will help you build models that are robust, easy to use, easy to understand, and painless to update. Best practices are of greatest concern when documents are used by more than one person:

      ○

Скачать книгу