FPAC Part 2 Chapter 06 Specifying Outputs and Getting Inputs.pdf

FPAC Exam Prep Platform FPAC Part 2: Chapter 6 Specifying Outputs and Getting Inputs Introduction to Models and Recommendations……………………………………..…….. 6-2 Topic 1: Understand the End Product and Define the Outputs…………………...…….. 6-7 Topic 2: Define Key Inputs and Input-to-Output Logic………………………………….. 6-18 Topic 3: Gather Data and Assumptions and Identify Gaps..……………..……………. 6-31 Topic 4: Seek Missing Information and/or Use Proxies and Assumptions..………….. 6-38 Topic 5: Sources of Industry Information……………………...…………………………. 6-45 ©2019. Association for Financial Professionals Table of Contents 6-1 FPAC Exam Prep Platform Introduction to Models and Recommendations What Is a Financial Model? The first five chapters of this domain describe the process and best practices of building financial models to produce financial projections or historical analyses. What is a financial model? Financial models must be defined very broadly because they can be built in many different ways to satisfy many different purposes. A financial model is a tool that takes inputs, makes calculations and produces outputs that change flexibly and dynamically in response to changes in inputs. Custom- built financial models should be auditable, meaning that one can objectively determine how their outputs are generated. The power of a financial model comes from the ability to change the inputs and see how the outputs change, called sensitivity or scenario analysis. Modeling thus provides insight into how a situation is likely to be affected by testing sensitivities to certain changes. Models can also generate a scenario that tells a story of what will likely happen if a particular set of assumptions plays out. In addition to their use in financial planning, many financial models can be used for historical analysis such as providing insight into the current state of business performance or performance versus peers. Many financial models are built using worksheets, sometimes also called spreadsheets (e.g., in Excel), but some are built using other tools such as business intelligence products or ERP forecasting modules. Financial models are so commonly built using worksheets that sometimes the terms are used interchangeably, but a worksheet is a broader term. Why Model Building Is Important to the FP&A Profession The ability to build a model is a critical skill for FP&A professionals because they are confronted with a wide range of problems that require a wide range of solutions. Planning and analysis of those problems can take so many forms and use such a diversity of information that only custom- built models will suffice in many cases. FP&A professionals need to develop skills and adopt sound best practices in model building such as following a rigorous and disciplined modeling process. Embracing these practices wholeheartedly allows FP&A professionals to rapidly generate models at the required level of complexity while keeping models: Efficient, meaning that they are straightforward and easy to update or adapt to new requirements Accurate, meaning that inputs and assumptions have been checked for quality and the chances for error are minimized Useful, meaning that the outputs answer the original end product or business question satisfactorily ©2019. Association for Financial Professionals Introduction 6-2 FPAC Exam Prep Platform For example, the analyst must identify which components of the model will be fixed, semi-variable or variable. In this way, the model-building process gives the FP&A professional a thorough understanding of the relationships, dependencies and risks of the business situation. This increased business insight can often be as important to generating conclusions and recommendations as the outputs of the model itself. "An important by-product of model building is that it forces the FP&A professional to identify all of the inputs, variables, indirect consequences and interrelationships of the business opportunity or situation." What Is the Recommendations Process? The final two chapters in this domain include a discussion of the process of using a model to generate conclusions, alternatives and recommendations. This is a process that involves developing support for recommendations, presenting them to decision makers in a way that highlights key outputs and assumptions made, and accepting feedback. For recommendations that are implemented in some way, or alternately for independent financial analysis projects, the FP&A professional may need to assess variances from actual results or perform competitive analysis to put results into perspective. The overall process concludes by improving current decision making and/or the modeling and recommendations processes. Because both modeling and making recommendations are iterative, highly repetitive processes, it is helpful to place them in a continual improvement framework such as the Plan, Do, Check, Act cycle. Plan, Do, Check, Act (PDCA) Cycle The Plan, Do, Check, Act cycle was originally used for quality improvement. It was defined by Shewhart and modified by Deming, both of whom are quality experts. However, it has come to be used as a basic model for developing and continually improving any process or system. Exhibit II.B.6-1 shows the four steps in this cycle. ©2019. Association for Financial Professionals Introduction 6-3 FPAC Exam Prep Platform Exhibit II.B.6-1 – Plan, Do, Check, Act (PDCA) Cycle for Continuous Improvement Plan The plan phase is, of course, where planning occurs. Exhibit II.B.6-2 shows two paths an FP&A professional could take when developing a model or performing another process. Exhibit II.B.6-2 – How Diligent Planning Can Reduce Total Effort Next three Steps: Do, Check and Act The very common first path involves just getting started right away on the work. However, this path results in far more overall effort and more development time because there may be many false starts, omitted elements that are very difficult to add later, or scope creep. Scope creep is where requirements keep getting added because the end product was not defined and agreed to in advance. Unanticipated problems become emergencies. ©2019. Association for Financial Professionals Introduction 6-4 FPAC Exam Prep Platform The second path is the best practice: Plan thoroughly in advance and, in doing so, avoid the problems of the first path and finish the task faster and with more rewarding results that are more likely to satisfy stakeholders. Problems are anticipated because they were planned into the process and planning incorporates lessons learned from prior efforts. Do is the phase in which the primary work is done Check is the process of validating the work, checking for errors, and checking for quality. Act is the process of making necessary changes to plans or repeating steps in the PDCA cycle based on the results from the Check phase. Iteration Is the Key The process of developing a model or making recommendations may involve revisiting many steps. While the chapters in this domain discuss each step in these processes in a particular order, the actual process may involve multiple passes through a particular stage, and the FP&A professional may need to perform some steps such as error checking at multiple points as new information is identified and added. For example, an FP&A professional would check for bias as soon as a new piece of data is received rather than waiting to receive all data. Model building might also begin relatively soon because a model can be used as a place to store gathered data and capture design decisions. Exhibit II.B.6-3 shows that the key concept to take from the PDCA cycle is that many processes—especially modeling—are highly iterative. Exhibit II.B.6-3 – PDCA Cycle Incorporates Iteration ©2019. Association for Financial Professionals Introduction 6-5 FPAC Exam Prep Platform Exhibit II.B.6-4 – Developing Model as PDCA Cycle ©2019. Association for Financial Professionals Introduction 6-6 FPAC Exam Prep Platform Topic 1: Understand the End Product and Define the Outputs Understand the End Product Keeping with the theme of beginning with the end in mind, the outcome of understanding the end product should be that the goals are defined explicitly and are agreed to by relevant parties. The FP&A professional has set the expectations of stakeholders so that they understand what they will be receiving. While the end product may evolve over time as the modeling process gets under way, getting an idea of what is wanted and what is not wanted from the start will minimize the possibility of starting work in areas that are extraneous or unnecessary. Developing an understanding of the end product or business question to be answered often starts when the FP&A professional receives requests for planning or analysis from a decision maker. The FP&A professional’s task is to identify the problem, the goals and the uncertainties. The problem is the business issue that the decision maker wants to resolve. This could be the need to reduce inventories, whether or not it is wise to invest in a project, asset or organization, or the need to increase sales. The goals are methods of understanding or resolving the problem. These goals may not be stated clearly. For example, the goal may be to find a way to reduce inventory, but this may need to be translated into producing better, timely forecasts so that supply will better match demand. The uncertainties are the internal and external forces that must be taken into account as well as the unintended consequences that may result from a business decision. This may involve cataloging related risks and opportunities. Understanding the business question may involve breaking it down to make the influencing factors or available options explicit. For example, if a Web retailer has a problem of shrinking net incomes, the goal of increasing net income could be expressed as reducing costs, increasing advertising to generate more sales, increasing prices for goods, changing sales mix, increasing advertising space fees or doing some combination of these choices. The FP&A professional might review these options with the decision maker to clarify which he or she is willing to consider. The end product will then be how to optimize revenues using the remaining available choices. For each component, the FP&A professional considers the uncertainties. For example, raising prices can be done only to the point that it continues to add incremental revenue. Determining how best to discover the optimum price point may be part of the end product. Defining the problem may become quite detailed, as the FP&A professional may need to know specifics rather than just generalities. For the inventory reduction example, this could include determining what is stored in each specific location, production times, shipping times and so on. ©2019. Association for Financial Professionals Topic 1 6-7 FPAC Exam Prep Platform In some cases, the desired end product will go beyond presenting the results of requested analysis and move into the realm of presenting viable solutions to problems. For example, when analyzing a new deal with a customer, some decision makers may welcome FP&A creativity in examining alternatives or suggesting different terms or structures for the deal that could improve its financial impact. Other stakeholders may only want basic analysis, so part of understanding the end product requires developing an understanding of the stakeholders. Stakeholders The primary stakeholder for the model is the decision maker who needs the results of the planning or analysis to make a decision. This is usually an internal customer such as an executive, but it could also be an external customer such as a banker. The primary question to ask oneself when meeting with a decision maker is, “What is relevant to this person in this situation?” Relevance relates not only to what is pertinent to be examined or highlighted in the analysis but also how detailed or summarized the outputs should be. Relevance requires understanding the perspectives of various job roles in an organization, as discussed in Part I, Domain B. This allows the FP&A professional to frame output in terms of the value drivers and KPIs relevant to that area of business. Note that it may be necessary to understand a particular job role rather than making assumptions based on the person’s department (such as finance). Individuals can be quite specialized at large organizations. Conversations with the person can help determine what is relevant. Coming to an agreement regarding what is needed may involve some amount of negotiation to ensure that the request can be completed within the available time frame. It is also important to consider what the person is requesting and, if appropriate, suggest better ways of getting to the same result based on experience. Decision makers rely on FP&A professionals to provide guidance and add value to the process. The degree of this reliance and the amount of added value that they are comfortable receiving from an FP&A professional depends in part on relationship building and trust. A prior track record of providing timely, accurate, objective and actionable information can lead to situations where the decision makers invite the analyst to provide creative solutions to problems. However, at some organizations, this level of advice may be restricted to an FP&A management position. In some cases, a model’s output will be used by multiple departments or business units for different purposes and each area may require different standard reports. In such cases, all parties that must approve the outputs are decision makers and the model should be designed to satisfy each party, possibly by creating different standardized output sheets tailored to each department or business unit. Understanding the evaluation criteria and information sensitivity issues of each area will help such reports meet their approval. ©2019. Association for Financial Professionals Topic 1 6-8 FPAC Exam Prep Platform It is also important to understand other direct stakeholders who may be involved in providing information or using the outputs. This may involve spending time talking to persons who will need to provide data or assumptions, persons who maintain databases or other systems, or end users if the model or its outputs are to be used by many persons. This will provide participating stakeholders an idea of their necessary time commitments and it will give the FP&A professional a better idea of people’s schedules and what to produce to satisfy possibly conflicting needs. Internal auditors are stakeholders in that they enforce compliance with policies and procedures. Auditing may check for errors or provide recommendations to superiors for process improvements. This means that the FP&A professional must be prepared to be audited frequently and must produce models using best practices so that the models stand up to scrutiny. Other direct stakeholders will be external to the organization, such as auditors, bankers, unions or regulators. For example, bankers might require review of financial projections annually as a project goes on. Another category of stakeholders is indirect stakeholders. Indirect stakeholders may be peripheral to the planning or analysis, such as shareholders who will see a final report, customers who may balk at paying higher prices, or business analysts who will assess for themselves the wisdom of business decisions. Such stakeholders may sometimes play a role in shaping an understanding of the end product or the constraints. This may involve talking to experts, such as salespersons who best understand their customers, or gathering information directly, for example, using a customer survey related to proposed price changes. Complex projects may require meetings between relevant stakeholders to arrive at a consensus on what the end product will be and the amount of reliance that can be placed on the results. It is important to explicitly set expectations in advance, especially as they relate to the accuracy or precision of the outputs. Managing expectations is a critical step in ensuring later satisfaction with results. Strategic Context (if Applicable) Strategic plans and planning were introduced in Part I, Domain A. FP&A professionals should have an understanding of organizational strategy and should understand the organization and its industry. A deep understanding of the organization helps the FP&A professional to provide insight and value to decision makers when discussing what the end product should look like. In addition, knowing when and how an end product can contribute to organizational strategy will help the FP&A professional to frame the outputs to highlight this relationship when presenting conclusions and recommendations. For example, if the statement of corporate objectives is to achieve a 40 percent market share and the outputs measure increase in market share, this should be expressed as relevant to strategy. Similarly, whenever key business drivers (e.g., profitability, ROI, ROA, economic profit) are used as outputs, these metrics drive the business and can be linked to their strategic relevance. ©2019. Association for Financial Professionals Topic 1 6-9 FPAC Exam Prep Platform FP&A professionals who are consultants will need to spend time getting up to speed on the organization and its industry by talking to people, reading corporate annual reports and trade publications and so on. Some types of financial planning and analysis are clearly linked to strategy, such as producing the organizational budget based on strategic and operational plans. Another type of analysis that is often more strategic in nature is non-financial analysis. This type of analysis might involve the FP&A professional addressing questions such as whether a business opportunity being analyzed is consistent with the organization’s mission or strategy or if it is more of a distraction from the organization’s core strategy. Non-financial analysis might also assess how a decision might impact other internal business units, for example, whether the opportunity creates capacity issues for other units or if it might generate synergies that could be harnessed. In other cases, the link between a given request and a strategy may not be as apparent. FP&A professionals are service providers after all, and they must provide services as requested by their internal customers. However, it is still important to know the context for the financial projection or analysis. The question to ask is, “How is the end product relevant?” The FP&A professional’s role in this case is to ensure that the project aligns with management’s direction. However, FP&A professionals may be able to prioritize modeling projects by relative strategic importance when several tasks are pending. Business Unit Operating Plan Context (if Applicable) Overall organizational strategy is distilled down to business units in the form of operating plans. When providing planning and analysis for specific business units, it is important to understand how an end product will fit into those operating plans. Directly linking results and recommendations to these plans will show that the FP&A professional understands the business unit’s needs and is adding value. It enhances the professional’s credibility and recommendations will be more likely to be given full weight in considerations. An understanding of the business units’ perspective also helps when defining the end product. For example, from the perspective of the entire organization, how overhead costs are allocated to various business units is not relevant, since the organization is looking at total overhead. However, if a new asset can reduce the amount of overhead allocated to a department, the information will be quite relevant to that department. Whether the asset can reduce labor costs will be relevant to both audiences. ©2019. Association for Financial Professionals Topic 1 6-10 FPAC Exam Prep Platform Scope and Deadline It may not be possible to develop a complete understanding of the scope of a modeling effort until some data gathering and model building has taken place. At this point, the scope may involve the following decisions: What level of detail is feasible by the deadline? How long is the time period being studied? Will this be a one-off model or is it intended to be reused? Will a worksheet be used or should other software or databases be considered? Who will use the model—just yourself, other FP&A professionals, a decision maker or many end users? Who will use the outputs? Will the modeling process require project management and multiple team members? Often one of the first things that a decision maker will tell an FP&A professional is when the information is needed. Information must be supplied in a timely manner to be useful in business decisions. When deadlines are short, the deadline is the primary determinant of the scope of the effort. This can sometimes be a problem if time does not allow for an adequate scope to answer the business question. When sufficient or excess time is available, the desired end product is the primary determinant of the scope. Allowing scope to fill the available time could result in overdesign or scope creep, which might add needless complexity and thus reduce overall quality and usefulness. The time period being studied will also have an effect on the scope of the effort. Planning for multiple future periods or analysis of multiple past periods will require more effort than for fewer periods. A consideration is whether there is an end state, such as for a project or an asset with a terminal value, or if it is projected to continue indefinitely, such as a business. It may be necessary to provide projections for a certain number of periods and then define a point where there is an assumed steady state. When this occurs should be specified. FP&A professionals very frequently get information requests that are needed in a very short time frame, and they produce a basic model and provide an answer within an hour or so. These are likely to be one-off models, but even these models will benefit from a few minutes of planning and a discussion with the decision maker to clarify goals. A model that must be developed for end user use or input must have protections and be user- friendly. The outputs may need to be less technical and more visual. The user interface may need to go beyond just protecting cells and include entry fields, drop-downs, etc. It may take significant effort to produce a model that guides input and makes users comfortable using the tool. Often it may be wise to consider using a dedicated input tool when many people must contribute information such as for a budget, for example, using a data consolidation tool such as Hyperion rather than just protecting a worksheet. Such systems can improve data validation and consolidate data automatically. Other modeling tasks could require building a database or using business intelligence software. ©2019. Association for Financial Professionals Topic 1 6-11 FPAC Exam Prep Platform Define the Required Outputs for the End Product Defining the required outputs helps to refine one’s understanding of the end product. If the end product has been clearly stated, such as “Should we launch the new product X?” or “Should we target Chinese markets?” or “What is the most we should pay to acquire Company Y?” then the FP&A professional can define the outputs that will be used to answer the business question. The outputs must be specific, measurable, achievable, relevant and time-based, or SMART (discussed in Part I, Domain A). The outputs could be the net present value (NPV) of future cash flows and the internal rate of return (IRR). These outputs are specific, so the decision maker can express whether they will be sufficient information for decision making, they are measurable since they are discrete calculations, their relevance can be tested against the end product, and they should be time-based, or able to be calculated within the available deadline. The outputs must also be clearly stated and communicated effectively. Often, in addition to the primary outputs, decision makers may have some additional side goals or constraints that need to be included in the outputs. For example, in addition to calculating the NPV of several options, the effect on each option on operating cash flow may need to be considered. The timing of cash payments and receipts may be an additional output. In many cases, the outputs may start out being defined in one way or only loosely and then are refined over time as the data-gathering and model- building processes provide more information on what is feasible. If sufficient data are not available to use a particular output, proxies and assumptions may need to be made to provide the required outputs, or different outputs sometimes could be selected that can also answer the business question (if acceptable alternatives exist). Sometimes, the FP&A professional may already have an applicable model and the process of defining outputs may be to validate that the outputs available from that model will suffice. ©2019. Association for Financial Professionals Topic 1 6-12 FPAC Exam Prep Platform Basic Output Constraints Output constraints are the voluntary or mandatory limits placed on how outputs will be presented. Voluntary constraints include selecting the smallest time period being studied (weeks, months, quarters, years) and the smallest organizational level to study (e.g., division, business unit, region, corporation). Selecting the minimum time period or organizational level to be studied is important because it is simple to aggregate smaller time periods or organizational levels to arrive at summary levels, but it is difficult to split a year into months or split corporate data into divisions if the month or unit data are not captured in the model from the start. However, building a model at levels that will never be studied will introduce unnecessary complexity. Other voluntary constraints include: Whether to use fiscal or calendar years Whether to use real dollars or adjust for inflation What currency to use Whether to provide outputs as percentages or numeric values Whether to use full values or incremental/marginal values How units should be consistently presented (e.g., in thousands) Whether the model will automatically round numbers and how Deciding on what voluntary constraints to select involves referring to what would be relevant to decision makers and best suit the purposes of the end product, but the FP&A professional should also consider what may be relevant in the future and plan ahead to be able to provide that information, even if it is not initially requested, such as being able to drill down to specific months on request or being able to view results by specific region or business unit. This decision will be based partly on past experience as well as available development time. Mandatory constraints include contractual, legal or regulatory constraints on business activities. Examples of contractual constraints include agreed-upon prices for items sold to major customers or debt ratios to maintain for creditor contracts. Examples of legal or regulatory constraints include rent-controlled apartments or the maximum price that can be charged to Medicare patients. These will be specific to each organization. ©2019. Association for Financial Professionals Topic 1 6-13 FPAC Exam Prep Platform Documentation of Outputs It is a best practice at this point to get organized and create a summary workbook with one or more worksheet tabs listing all outputs, including units of measure and periods for which data will be created in the columns. Creating more than one output worksheet allows FP&A professionals to organize the output in layers, for example, a one-page executive summary sheet with the key outputs followed by one or more high-level sheets with the supporting details and metrics. A simple executive summary facilitates: A quick review of the key outputs of the model that can be used during model development and use Communication at the appropriate level of detail to decision makers The workbook will contain no data at this time, just row and column headers. It is also a good idea to block out space for charts and other elements. If the workbook will be used to create the model itself, the FP&A professional can also create the other tabs as needed. Decision makers or other stakeholders will not usually specify how they want the outputs to appear. It is the FP&A professional’s responsibility to decide. Decision makers will likely not have time to consult on every detail, so ask yourself, “How would I like this information to be presented to me?” Other questions to ask are whether the information will need to be presented to other parties such as shareholders or whether the charts or summary reports will need to be presented in a slide show format. Thinking through these issues as much as possible from the start and documenting them in the worksheet can reduce the need to rework model layout (and affected formulas and functions) later. ©2019. Association for Financial Professionals Topic 1 6-14 FPAC Exam Prep Platform Case Study Mining Company Case Study: Introduction Copper Mines Company is a U.S.-based, publicly traded company with $576 million in 2012 revenue from three mines in the U.S. The organization has a strong reputation for environmental sustainability and regularly reinvests in new technology to reduce environmental impact and improve efficiency. It is a pure player in the copper mining industry, historically having avoided upstream exploration in favor of purchasing rights to proven reserves and also having avoided downstream investing in copper smelting, paying fees for this service and selling the copper after it has been smelted into pure copper cathodes. The organization trades in the spot market and does not hedge since the CEO believes investors invest to be exposed to commodity prices. The CEO also wants to avoid issuing new equity, instead using debt as needed. Gold and silver byproducts the smelter extracts are used to partly offset smelting fees. The organization’s three copper mines—A, B and C—have the following data: In 2012, the three mines together extracted and processed ore into copper cathodes as follows: ©2019. Association for Financial Professionals Topic 1 6-15 FPAC Exam Prep Platform Mining Company Case Study: Introduction (continued) The average selling price for copper in 2012 was about $8,000/MT ($8,000/MT × 72,000 MT = $576 million). After-tax net income was $86 million. The Problem, Goals and Uncertainties Copper Mines Company needs to purchase or lease new mines and get them up and running before its existing mines become depleted. It is also at risk due to its lack of geographic diversification. At the most recent board meeting, a board member discussed a potential copper mine purchase in Panama that could help diversify the organization geographically and get a new mine up and running soon. The government in Panama recently revised its mining laws, and the time seems ripe for investment. The Panama mine in question is nearing the end of its five-year exploration phase but has one year remaining to complete the survey. The site has rail access nearby, but roads, electricity and other infrastructure would need to be built. The organization has not operated in this region before. Current reserve estimates are that the site has 540,000 MT of copper reserves, but this could be revised upward or downward. The board member noted that the exploration company has become cash-strapped and is looking to make an early sale. The price they could negotiate for the mining rights seems promising, but the risk of the reserves proving less than expected is higher than the organization has traditionally accepted. An FP&A professional is given six weeks to build a model that can produce the end product. The End Product The end product can be expressed as: Should we invest in the Panama mine, and, if so, what is the maximum price we should offer? Underlying questions include: By how much will this project increase profitability in the first, second and third full year? How much could the reserve estimates vary? How long will it take and how much will it cost to close the deal and get the site operational? How many MT of copper can we reasonably expect to extract per year at full capacity? How much will ongoing operating costs be? What is the retirement obligation in the terminal year (cleanup cost)? What will the growth rate of copper prices be and how will it affect our decision? What are our foreign exchange or risk exposures? How much external financing is needed and how will the financing cost affect the decision? ©2019. Association for Financial Professionals Topic 1 6-16 FPAC Exam Prep Platform The Outputs The outputs so far include the following: NPV IRR Profitability index Payback period Discounted payback period Expected life of mine Mining Company Case Study: Introduction (continued) These outputs were selected for the model because NPV greater than zero is a key project acceptance hurdle and IRR is the discount rate at which an investment’s NPV equals zero and is thus another way of understanding the project’s minimum acceptance criteria, especially as it regards the cost of project funding. Similarly, the profitability index is attractive when it exceeds a ratio of 1.0. The payback period and discounted payback period show how long it takes to recoup the initial investment in terms of actual dollars and present value dollars respectively. Together, these measures are often used to evaluate capital budgeting projects because each provides a different piece of information for making accept or reject decisions. Since each method has its own strengths and weaknesses, using the outputs together can provide the best information in total. However, the mining company considers NPV to be the metric of most value because it shows by how much the project will increase shareholder wealth. Each of these metrics is discussed in more detail in Part II, Domain B. The final model output, the expected life of the mine, is a key output critical for understanding whether the mine will have sufficient resources to be a profitable long-term investment. It is calculated using the reserve estimates and the amount extracted per year. Other important outputs to be produced using a set of pro forma financial statements (not shown in this case study) for the entire organization include the following: For the financing question, a key output is the external financing required; interest expense will be an input to the profitability analysis so this will require an iterative model. (See Part II, Domain C, for an example unrelated to this case study.) Another output is the marginal (additional) profit to the organization generated by the mine for each of the first three years of the project. (See Part II, Domain A, for an example of financial statement projections unrelated to this case study.) Finally, while also not specifically addressed in this case study, often an output of a potential investment analysis is the comparison of a project against competing alternatives for organizational funds. The outputs of the financial model could be directly compared to other potential projects of similar risk or they could be risk-adjusted so as to compare apples to apples. ©2019. Association for Financial Professionals Topic 1 6-17 FPAC Exam Prep Platform Topic 2: Define Key Inputs and Input-to- Output Logic Outputs and Inputs: Introduction With an understanding of the desired outputs for the model, the FP&A professional can then begin to define the inputs and the input-to-output logic needed to produce the outputs. Inputs come in many forms. These may include: Value drivers (or business drivers) and related key performance indicators Historical data or seed data that can help establish trends Proxies and assumptions that take the place of missing data or predict what conditions will be like during the analysis period (Note that proxies and assumptions are discussed in later topics.) Specifying inputs and input-to-output logic often revolves around a study of value drivers related to the end product. Value drivers are discussed in Part I, Domain C, Chapter 11, “Organization.” After introducing some types of inputs, the discussion here therefore addresses how value drivers and KPIs are used to help define both inputs and the logical flow of inputs to outputs. Afterward, there is a discussion of how to construct high-level flowcharts and flowcharts of more detailed calculation processes for models. Specify Inputs When deciding what inputs to specify in the model, the first question to ask is, “What critical factors do we need to know about the situation and the future that will drive the outputs?” This is a brainstorming process of listing all critical factors and then separating them into direct inputs, contextual inputs and derived inputs. Direct Inputs Direct inputs are those inputs or drivers entered directly in the model and used as inputs to calculations. Direct inputs can be variables, constants or semi-variables. Variables are data that can assume any one of a set of values as needed or expected in the model. Variables can be based on value drivers, the most up-to-date historical data or assumptions. Variables will form the majority of direct inputs to a model, especially when generating projections into the future. Variables are used as inputs for assumptions, for example, when historical data are not available. ©2019. Association for Financial Professionals Topic 2 6-18 FPAC Exam Prep Platform Even when historical data are available, how the input will behave going into the future could vary and so a variable direct input is usually necessary. FP&A professionals could start with a long list of potential variables, which can then be reviewed to determine which are really constants or semi-variables and also which are better as contextual or derived inputs (all discussed below). Constants, also called the “givens” in a model, are values that are not expected to change in the model and are used for stable relationships. Constants could be assumptions, historical data, internal policies or facts (e.g., five workdays per week). Constants are typically easy to collect because by definition they do not change frequently, if at all. Constants are still modeled as direct inputs because calculations should not contain hard- coded values and the values might also differ the next time the model is used. One example of constants are internal policies. Internal policies are those strategic or operational values set by organizational policy. Examples include minimum cash balances, working capital, weighted average cost of capital, depreciation schedules, capital expenditures, dividend payout policy, operating and financial leverage, relevant marketing or manufacturing decisions, management’s attitude toward taking risk (risk appetite), or management’s preference for debt versus equity. Semi-variables (or step variables) are inputs that are used for relationships that are stable over a given relative range and then step up or down to a new stable level once the range is exceeded. Semi-variables require modeling the relevant range or ranges for the model, perhaps as separate input fields. For example, say that one employee can produce between a relevant range of 0 and 100 parts per week. If 150 parts are needed per week, an additional employee would be needed and number of employees might be an input field. Note that if the relevant range will not be exceeded within the model (e.g., that range is considered reasonable), these inputs can be treated as constants, but some data validation might be needed so the range is not inadvertently exceeded. Contextual inputs (contextual drivers or indirect inputs) are those inputs or drivers that are not used in the model directly but may help determine model logic or may be used in descriptive summaries to provide support for scenarios or conclusions and recommendations. Contextual inputs should be removed from the list of direct inputs and put on the assumptions tab or elsewhere. Derived inputs are the outputs of calculations in a model that are used as inputs to different calculations in the model. The more models make use of derived inputs, the more flexible they are, because derived inputs and the calculations they are based upon leverage the interrelationships between elements. ©2019. Association for Financial Professionals Topic 2 6-19 FPAC Exam Prep Platform The determination of which inputs should be direct, contextual or derived often starts with a study of value drivers and their related key performance indicators. In this way, the inputs and the input- to-output logic are often developed simultaneously. Specify Value Drivers and Related KPIs Value drivers, or business drivers, are factors that affect the organization’s ability to generate economic value. Activities meant to influence value drivers are often measured using key performance indicators. A key performance indicator (KPI) is a metric that indicates the level of performance required to achieve a defined objective in a certain activity. A financial value metric such as net profit margin is driven by financial value drivers such as sales revenue, and each of these drivers is driven in turn by a number of operational value drivers, one of which might be direct sales volume. Direct sales volume is in turn driven by one or more tactics such as new account development, which might be measured by the KPI “number of new accounts.” Purpose of Identifying Value Drivers and KPIs Identifying value drivers and KPIs is a high-level, top-down effort that can help frame the big picture and clarify the purpose of the end product prior to getting into the details of the model. Studying value drivers helps the FP&A professional to understand the financial and economic relationships between inputs and helps to construct the logical flows and high-level model process flowchart logic. Another benefit of studying value drivers is that it helps to identify potential project or operational risks and opportunities. These risks and opportunities can be listed in a risks and opportunities (R&O) analysis for possible inclusion in scenarios. Value drivers can also be used to generate derived inputs using the known relationships between the drivers and other information that is unknown when making a projection. For example, when building a set of pro forma financial statements, drivers of revenue will give you your revenue for the model, drivers of expenses will give you your expenses, drivers of capital expenditures will give you your capital expenditures, and so on. Value drivers that cannot be used as direct or derived inputs are often still valuable as contextual inputs. These drivers might be addressed as considerations or constraints within the overall logic of the model "Understanding the key value drivers for a particular end product and how the business opportunity impacts the drivers will help the FP&A professional and decision makers understand how a project will maximize a business opportunity or improve business operations so that recommendations will have relevance for decision makers." ©2019. Association for Financial Professionals Topic 2 6-20 FPAC Exam Prep Platform External Value Drivers and Related KPIs External value drivers and their related KPIs are factors outside the control of the organization that are likely to have an influence on the end product. As defined and described in Part I, Domain A, Chapter 6, external drivers are sometimes considered in the following macro environment categories, which is sometimes called a PESTLE analysis: Political Economic Social Technological Legal Environmental Considering each of these areas in turn can ensure that no important external factors are ignored in the model. See Part I, Domain A, for specific examples of macro environment drivers in each of these categories. External drivers can also be specific to an organization, business unit or product decision: Market growth Overall market size Customer needs and expectations Loan covenants Competitor actions such as their offerings, marketplace positioning strategies and anticipated responses to the organization’s actions Organization-specific external drivers can help provide subtle distinctions in how variable interrelationships should be interpreted. For example, understanding where a company or product is at in terms of its life cycle will influence how its free cash flow should be interpreted. A new company or one experiencing strong growth may have significant negative free cash flow, and this is a normal consequence of its life cycle stage. External drivers specific to a particular business question could include impact on the environment or a local community, the availability of suitable land and infrastructure, useful life or maintenance costs for technology and equipment, or local market costs for services such as construction. Also, some macro environment factors can be drilled down to relevant specifics, such as the impact of a specific regulatory approval process on a product release or the inflation rate of the raw materials used in a product. ©2019. Association for Financial Professionals Topic 2 6-21 FPAC Exam Prep Platform Selected external drivers and KPIs are therefore specific to the organization and the end product. Take, for example, a chain of mall-based retail clothing stores that markets to a niche market segment: End product. Increase mall retail space foot traffic. External drivers of foot traffic. Unemployment levels and disposable income for niche demographic, mall foot traffic, ratio of vacant to occupied spaces in malls, seasonality, rate of change in clothing trends, etc. KPIs. Number of persons entering store, ratio of walk-ins to sales. Another example is for a hotel chain: End product. Maximize hotel revenues. External drivers of hotel revenue. Unemployment rates, disposable income, travel budgets for organizations, gas prices, regional events, regional situation (e.g., political or social strife), etc. KPIs. Occupancy rate, price realization, etc. Internal Value Drivers and Related KPIs Internal value drivers and related KPIs are drivers and metrics that the organization can influence or control. Many internal value drivers and KPIs will be ones that the organization has previously determined are vital to the organization’s business model and strategy. When these exist, FP&A professionals should select an appropriate subset that relates to the end product or business question. When decision makers are already measuring and managing success using these drivers and KPIs, it will be straightforward to show how the planning or analysis results are pertinent to the audience and how they impact the organization’s strategic or tactical goals. Newer organizations or organizations that have not engaged in formal strategic planning may not have a set of clearly identified drivers and KPIs, in which case the FP&A professional may need to consult with internal experts to develop them for the end product. Like external value drivers, internal value drivers and related KPIs will be specific to the organization and end product. The prior example of a mall retail clothing store is continued for internal drivers: End product. Increase mall retail space foot traffic. Internal drivers of foot traffic. Choice of malls for stores, store location in mall, store layout, shelf layout, product mix, advertising, number of salespersons, training of salespersons, etc. KPIs. Number of persons entering store, ratio of walk-ins to sales, etc. Internal drivers for the hotel chain example follow: End product. Maximize hotel revenues. Internal drivers of hotel revenue. Location, customer experience, frequent stay programs, coordination with convention space, up-selling initiatives, availability of ancillary services, etc. KPIs. Occupancy rate, price realization, ancillary services paid, customer complaints, satisfaction with complaint resolution, etc. ©2019. Association for Financial Professionals Topic 2 6-22 FPAC Exam Prep Platform Differentiating Between Direct and Contextual Inputs The process of selecting which inputs or value drivers will be direct (or derived) inputs and which will be contextual inputs involves only deciding what is necessary and sufficient to produce the end product. Necessary is a criterion that will help restrict key inputs to what is feasible to model within scope and deadline constraints. Sufficient is a criterion that will help ensure that the set of inputs as a whole can answer the business question, including any scenarios that need to be developed. Other selection criteria include driver volatility and prediction usefulness. Volatility relates to the frequency and size of swings in variations. Volatile drivers often still need to be explicitly included in the model if they are critical to understanding the issue. They may require more assumptions, and you should understand that these assumptions become quickly less reliable the farther into the future they are projected. Prediction usefulness refers to how predictable a driver has been in the past in forecasting correlated events. Drivers with poor correlation might be excluded. The variables selected may start out broadly in the first iterations and then may be narrowed or changed as the understanding of the end product and available information evolves. Document the Logical Flow of Inputs to Outputs The logical flow of inputs to outputs uses logical arguments to show the overall factors and drivers that come into play in a complex model. More detailed flow charts show how the inputs lead to calculations and the results of those calculations become inputs to other calculations, and so on, until the final outputs are generated. The purposes of producing a logical flow of inputs to outputs are to: Ensure that all relevant considerations and major components of the model are accounted for Show cause and effect Make the design and documentation transparent Provide a method of checking for logic errors or model auditing Enable presentations of high-level model logic to interested parties For example, for a revenue projection model the purpose of the flowchart is to show where the money is coming in, how things tie together and what factors influence each revenue stream. Flowcharts can be created using an automated flowcharting tool such as Microsoft Visio, but they can also be created manually in an Excel worksheet tab or in a PowerPoint slide show. ©2019. Association for Financial Professionals Topic 2 6-23 FPAC Exam Prep Platform High-Level Logic Flowcharts High-level logic flowcharts are a top-down method of showing all of the major influences on a given model output. They are especially vital for complex models with many drivers and influencing factors. These flowcharts are top-down because they start from a major output and branch into more and more specific drivers. A common way of presenting this information is a value driver tree. These high-level flowcharts do not indicate the specific calculations but instead provide a way to check that all considerations and constraints are accounted for.Exhibit II.B.6-2 on the next page shows an extract of an example of the logic for free cash flow (FCF) resulting from a utility company’s economic assistance customers (EACs). Note that some of the specific drivers or inputs on the right could be further broken down into additional levels of detail. Note also that F( ) denotes “function of” in the chart. Detail-Level Process Flows For specific portions of a complex model or for a simple model, an additional level of detail can be mapped out to help with design. At this higher level of detail the inputs, calculations and outputs (ICO) of a model can be shown. This type of process flow should visually differentiate between the inputs, derived inputs, calculations and outputs. Detail-level model process flows may be constructed in many ways. A simple model may require only simple mathematical arguments such as plus, minus, multiply and divide, or it could list financial functions to perform such as Excel worksheet functions. Other detail-level model flowcharts will use standard flowchart methodology (i.e., symbols such as boxes for processes and diamonds for decision points with arrows between processes). In this case, the mathematical operators and calculations to perform might be specified within the flowchart boxes, or they might be omitted to keep the chart simple, as is often needed when a detail-level flowchart must still show many complex interactions between elements. ©2019. Association for Financial Professionals Topic 2 6-24 FPAC Exam Prep Platform Exhibit II.B.6-5 – Illustrative FCF Driver Flowchart for Economic Assistance Customers ©2019. Association for Financial Professionals Topic 2 6-25 FPAC Exam Prep Platform Exhibit II.B.6-6 – Revenue Projections of Simple Model Exhibit II.B.6-3 shows a process flow produced on a worksheet tab, which ensures that it is easily accessible within the model. The chart shows how an organization selling products for families with newborns estimates their revenue based on the new customers gained and the number of existing customers retained after accounting for churn (lost customers). Note that the exhibit differentiates between direct inputs, derived inputs and outputs, while the calculations are all shown using operator symbols (plus, times, and equals). Transparency and Continued Relevance Model logic and flow development throughout this iterative process should be very open and transparent. Documenting as you go is the only way to keep this process transparent. Clearly documenting the logical flow using a basic flowchart is a best practice. It is important to keep the flowchart up-to-date so that decision makers can understand conceptually how the model works. Only when they understand the model’s logic and assumptions will they be in a position to apply their expertise rather than blindly relying on a black box (or rejecting the model outright). From the FP&A professional’s perspective, a useful flowchart will improve formal presentations and make justifying the methods used to arrive at the results more obvious and visual. Flowcharts will also be valuable for future model users and auditors so they can trace the model’s process. ©2019. Association for Financial Professionals Topic 2 6-26 FPAC Exam Prep Platform Formal Review of Inputs and Logical Flows Large, complex projects may have a formal review step to validate the inputs to be used and the logic of the model for quality assurance purposes. Such reviews may occur at various development stages. Case Study Mining Company Case Study The Inputs The following direct inputs are planned for the Panama mine purchase analysis model. The first two fields sum to the initial year investment cost. The next field is the discount rate, or the cost of funds used for present value calculations. The copper reserves and extraction per year fields are listed in metric tons, and together they dictate the expected life of the mine. The copper base price field is the average historical price of copper in the initial year. The copper price growth rate is a compounded growth rate to apply to the copper base price in the first year and to the prior year’s calculated copper price for subsequent years. The retirement obligation is the cost of closing the mine and any necessary environmental remediation. The cash expenses field is an assumption that the expenses can be estimated as a certain percentage of revenue. Depreciable assets, asset salvage value and depreciation period are to be used to estimate straight-line depreciation for the model as a simplifying assumption since the real assets will be depreciated at different rates. The income tax rate is also a simplifying assumption because it omits consideration of tax deductions, deferred taxes and so on that would be used to arrive at a more realistic effective tax rate. Note that all amounts listed in millions are entered in whole dollars but are formatted to display in millions. Finally, note that the values entered in the input fields so far could be test data or early assumptions at this point. ©2019. Association for Financial Professionals Topic 2 6-27 FPAC Exam Prep Platform Mining Company Case Study (continued) High-Level Flow The following flowchart shows a value driver tree for calculating the net present value as of the terminal year of the Panama mine. This flowchart shows that NPV requires knowing the terminal year of the mine, which is variable based on the amount of copper reserves and the extraction per year. Therefore, the model will need to calculate NPV for each year and then look up the NPV for the terminal year. The NPV calculation is primarily determined using the present value of the after-tax cash flows based on the discount rate, plus the initial cost. The retirement obligation is deducted only in the final year. Note that F() denotes “function of” in the flowchart. ©2019. Association for Financial Professionals Topic 2 6-28 FPAC Exam Prep Platform Detail-Level Flow The detail-level flow below shows the direct inputs, the derived inputs, the outputs and the calculations (math operators and Excel worksheet functions, e.g., PV, IRR) the analyst plans on using. Starting from the top, the copper base price is used as the Year 0 copper price. Thereafter, the prior- year copper price times one plus the growth rate is used to create compounding growth in average annual copper prices. The copper price per MT for the given year is multiplied by the amount of MTs of copper extracted per year to find the base revenue per year. Then the cash expenses assumption is used to calculate the cash expenses for the given year. The third item required to calculate the income before tax per year is the depreciation per year, which is a function of the depreciable assets, salvage value and depreciation period. Income before tax per year is multiplied by the tax rate to calculate the income taxes, which are subtracted from the income before tax to find the income after tax. Depreciation is added back at this point to find the after-tax cash flow per year, and this amount is used to calculate the IRR for each year of the mine. In cell A30, the after-tax cash flow per year is added as cumulative sums. An IF Statement and MAX function in Excel is used to find the payback period. The discounted payback period is also calculated starting with after-tax cash flow, but a present value (PV) function is used to calculate the cumulative discounted cash flow for each year. Net present value is calculated using an NPV function, which should equal the discounted cash flow per year less the first-year cash expenses and/or capital. NPV divided by the first-year cash expenses equals the profitability index ratio. Starting in cell H6, the copper reserves divided by the extraction per year equals the estimated life of mine. Since the final year of the project is variable, this model calculates the other outputs for each potential terminal year. Therefore, a LOOKUP function will be used to match the terminal year of the mine to a project year field within the calculations and then return that year’s outputs for IRR, NPV and profitability index. The payback period outputs are found using an array function to return the minimum payback period that is greater than zero. ©2019. Association for Financial Professionals Topic 2 6-29 FPAC Exam Prep Platform ©2019. Association for Financial Professionals Topic 2 6-30 FPAC Exam Prep Platform Topic 3: Gather Data and Assumptions and Identify Gaps Gather Data and Assumptions and Identify Gaps: Overview The blank or test data inputs that the FP&A professional has assembled so far need to be filled in with data from source systems and persons, possibly using estimates. The inputs may also require making assumptions regarding variables or constants to use in the model, for example, how a variable will behave over the modeling period. Data and assumptions are gathered for financial projections and for historical or current state analysis. Gathering data and assumptions for financial projections can be challenging, especially for a new endeavor in which there is little or no direct historical data for comparison. Gathering data for financial analysis is often more straightforward than for projections because, by definition, financial analysis uses actual results. However, even in this case, the necessary information may be located in multiple places or it may contain accounting estimates such as reserves for sales returns. Data, Assumptions and Estimates Data, assumptions and estimates are often used as the values entered into the input fields of a model or are used to generate the model logic and calculations. Data are objective and verifiable facts that are often expressed in numerical form. Assumptions are (a) axioms, hypotheses or projections about past, current or future conditions, data, or business decisions that are taken for granted in a model, or (b) simplifying steps used in models to approximate more complex real-world relationships that should not be modeled due to purpose and scope constraints or that cannot be modeled due to time, tool or user limitations. An example of the former definition is an assumption about the percentage of lost customers in a period. An example of the latter is the assumption that all cash flows occur at the end of the year because even though untrue, the difference may be immaterial. Estimates are casual or methodical assumptions made about the value of data. ©2019. Association for Financial Professionals Topic 3 6-31 FPAC Exam Prep Platform The difference between data and assumptions or estimates is that data are objective and verifiable, while assumptions and estimates are subject to challenge. Assumptions must therefore be substantiated with reason and logic or corroborated in some other way. Corroboration is supporting something with evidence or authority, with more weight being given to independent sources. This text uses the blanket term “information” for data, estimates or assumptions. The following are some examples of types of assumptions: Time-series assumptions. A time-series assumption is a projection of how a variable will behave over the time period being studied. This could be modeled as a trend, such as a constant, compounding or exponential rate of growth or decline, or the model could contain separate input fields to allow customization within each period. Value assumptions. A value assumption is an estimate about the value of data, for example, the useful life of equipment or interest rate. Event assumptions. An event assumption is an assumption that an event will or will not occur during the analysis period, along with what is projected to occur, such as winning or losing a court case. Developing an understanding of the types of assumptions to be made can help the FP&A professional plan the model logic. For example, a constant growth rate could be assumed for all periods in the model, or each period could contain a separate input field for that period’s growth. The latter functionality is straightforward to include in the model during initial development but could be difficult to add to an established model. Gathering FP&A Data and Assumptions Once you have identified the information you need, the next step is to create a data acquisition plan. This can be a simple list or a detailed set of tasks depending on the scope of the project. The process of gathering and validating data and assumptions is highly iterative and is typically a very time-consuming part of an FP&A professional’s duties, so a plan can help manage this task. The plan may allow for concurrently performing some data-gathering and model-building tasks to achieve results within time constraints. Once a plan is in place, the FP&A professional gathers the easiest data and assumptions to collect and also starts the process of gathering more challenging data. As each piece of data is collected, the FP&A professional validates this information and checks for outliers or bias (see the next chapter) and documents the data and their sources. This process is repeated as needed once the more challenging data become available or when a newly discovered data requirement is determined. ©2019. Association for Financial Professionals Topic 3 6-32 FPAC Exam Prep Platform Initial data validation occurs as the data are collected. This step is necessary when gathering data and assumptions from others to ensure that you understand exactly what you are getting. This includes knowing how the information is categorized, how it is counted, units of measure, and so on. FP&A professionals should take the time to understand that the information is what they think it is and to check that the information is on an apples-to-apples basis with other information being collected. For example, in the mine case study, perhaps the mine is internally measuring gross copper weight, but the open market uses a weight that factors in a purity measure. Understanding these nuances would help properly model the copper volume. Specialized data collection and consolidation systems such as Hyperion may serve as a collection point for both system- and user-provided data, for example, to build out revenues and expenses for a budget using a standardized input template. These systems contain powerful consolidation tools to allow various business units to each enter data, and the system automatically consolidates it. Once data are extracted from various information systems and persons, the FP&A professional places the data in a repository. This could be worksheets for various categories of data or a database. When collecting data in worksheets, keep the data in a simple rectangular format with column headers, but avoid using row headers or summary rows because this can prevent the data from being manipulated easily such as in a pivot table in Excel. Data from Information Systems Some data will be available on demand from an organization’s information systems, including its enterprise resources planning (ERP)/ general ledger (GL) systems, data warehouses or other sources. This may involve an automated tool that can produce standardized or configurable reports with the click of a button, or the professional may need to produce SQL queries to pull just relevant data. ERP/GL, data warehouses and SQL are discussed later in Part II, Domain B. For either planning or analysis, it is critical that the FP&A professional develops a detailed understanding of the various sources of data at an organization, both what is stored where and in what format the data are stored. Data and Assumptions from Persons Equally important to knowing what systems contain what data is to know who to talk to when gathering expert opinions. Providing estimates or assumptions is a skill that requires experience, but it also requires specialized expertise in the area being estimated. Therefore, best practice is to contact the individual closest to the process. This may be a process expert, a person who will be involved in doing the work (if applicable) or a person who will be held accountable for the results. Accountability structures often dictate that these persons must provide the estimates and assumptions within their purview. This person’s educated guesses will be better than guesses you could make. For example, marketing professionals have expertise in price elasticity, impact of advertising, consumer trends and competitor reactions, so they are an obvious choice to forecast unit sales and sales prices. ©2019. Association for Financial Professionals Topic 3 6-33 FPAC Exam Prep Platform Developing relationships with other professionals in advance of an urgent need will make them more likely to be helpful. FP&A professionals need to contact such persons as soon as possible during a modeling effort to determine their availability, capability, and willingness to provide information, and then schedule a time convenient to gather the information if it cannot be collected immediately. This may involve interviews, e-mail or other media. FP&A professionals can provide appropriate guidance to help improve the estimation and assumption-making skills of experts. For example, it may be necessary to help information providers establish a shared set of economic assumptions prior to asking for estimates from multiple persons. The FP&A professional will need to decide when it is necessary to get them to come to a consensus or if he or she will independently combine the information in some way. Time permitting, when multiple estimators meet to share their rationales, the process can result in capturing more relevant factors and can make the consensus value more likely to be accurate. External information providers that could be queried include professional colleagues outside the organization, professors, economists, consultants or even competitors. Data and Assumptions from External Sources Data and assumptions from the Internet or other external sources may be collected after exhausting internal sources. A vast amount of useful and reliable information is available from government sources such as census data or macroeconomic forecasts, paid sources such as stockbrokers or research analysts, and from general sources such as data on competitors. However, much data on the Internet will require corroboration, and factors unknown to the FP&A professional could make the data difficult to compare (i.e., may be unknowingly comparing apples to oranges). See Part II, Domain B, for more details and other caveats on gathering information from the internet. Establish Collection Policies and Procedures The accuracy of the outputs that a model produces is directly related to the accuracy of the estimates and assumptions used in inputs and calculations. Estimation and assumptions policies set rules for how to make estimates and assumptions and how to document them. Documentation should at minimum include the following information: The estimate or assumption The name of the person who provided the estimate/assumption The date that the estimate or assumption was made Notes regarding assumptions the provider made when generating an estimate so that estimates from other sources are comparable Notes that you could record might include the level of confidence that the provider has in his or her estimate or critical assumptions that, if unrealized, would require revising the estimate. A consistent methodology for estimation and assumption making can be produced for the FP&A area and communicated to internal clients. Because estimates or assumptions can vary widely, ©2019. Association for Financial Professionals Topic 3 6-34 FPAC Exam Prep Platform when estimates must be provided by many persons, a prescriptive estimation policy can be followed and enforced in part using protected forms/worksheets. Elements that can be specified include: Unit of measure and how to round (e.g., to the nearest day or $100) Key assumptions (e.g., organization increases maintenance spend) Source, relevance and “freshness” of historical data (e.g., from Project ABC two years ago) Risks that could change the estimate (e.g., fuel prices) Tolerance range (e.g., plus or minus 10 percent for a loss contingency) Uncertainty level (e.g., refine this ballpark estimate later) Length of time the estimate or assumption will remain valid Knowing When Estimates/Assumptions Are Needed Estimates and assumptions are required whenever historical data and facts are not available, such as when projecting values for data in the future. Estimates and assumptions that can be validated in some way are especially needed when no base or seed data are available at all, such as for a new product line or area of business. Performing planning for such greenfield projects is a quite common task for FP&A professionals. However, simple guessing is not a sufficient method for making critical estimates or assumptions. Acceptable methods include finding a similar situation, product or project, finding an empirical basis to justify the estimate or assumption, or using triangulation, which involves extrapolating information based on known data and known interrelationships between data. (Triangulation is discussed more in a later topic.) For example, it may be necessary to corroborate the advice of internal experts with advice from trusted colleagues, find white papers or case studies, or research what competitors did. Start an Information Gap Analysis An information gap analysis is a type of variance analysis in which the current set of information is compared to the desired set of information and shortfalls between the current and desired state are identified. Information gaps may include the following types of information: No data are available on the subject at all. Data are not available for the time period in question (i.e., either a future period or a gap in a historical time sequence). Data are unavailable for the business unit, product, customer segment or geographic region in question. Data are noisy (outliers or corrupted data). Data are incomplete, for example, not all costs are accounted for. Data are considered too unreliable to use without corroboration. Information providers have yet to provide requested estimates or assumptions. ©2019. Association for Financial Professionals Topic 3 6-35 FPAC Exam Prep Platform List All Data Gaps During the first pass or two at data and assumptions gathering, the first step of an information gap analysis is performed: listing all known data gaps. Listing out all identified gaps in data and assumptions is performed concurrently with data gathering because whenever data or an assumption cannot be gathered from a source system or person, it is placed on a list of data requirements along with notes on the reasons why the data were not able to be collected. The purpose of building this list of gaps is to identify areas where planning or analysis can begin and areas where the gaps are still too large and additional data gathering is necessary. For example, you cannot do a cost analysis until you have all of your cost data. The next topic discusses some further analysis steps performed to determine how to respond to specific data gaps: performing a preliminary data review, identifying which of these gaps is relevant enough to spend additional time gathering data, and determining which gaps can or must be filled using proxies or assumptions. Case Study Mining Company Case Study There is no historical data for the Panama mine projection as this is a new project in a different region and costs will differ. However, for the setup expenses input, much of the same equipment will be used as at the North American mines, so the FP&A professional gathers statistics on the equipment from information systems. For the extraction per year input, the professional gathers information and assumptions on equipment productivity (a value driver) and maintenance from the firm’s engineers. The FP&A professional gets the assistance of an in-house researcher, who gathers geography, political situation, climate and labor statistics on Panama as well as specifics on the distances to the nearest smelters, the cost of various products and services including smelting, and maps and specifics of the mine site. The FP&A professional studies the report and provides it to the chief engineers for each mine, scheduling time to meet with them once they have read the information. Also, a health and safety committee board member discusses the need for additional employee health and safety measures due to mosquito-borne ailments. These sources of information will help determine the proper percentage to apply to the cash expenses input and also contribute to cost estimates for the setup expenses input. The information on local conditions also influences the asset salvage value input. Another source of information is the preliminary report provided by the site exploration company looking to sell the rights to the mine. The report makes it clear that the results are not official and concludes that the reserve estimates could differ by up to +/− 20 percent. The FP&A professional notes this as a potential range for the copper reserves input in a later sensitivity analysis. ©2019. Association for Financial Professionals Topic 3 6-36 FPAC Exam Prep Platform The FP&A professional also talks to a board member who has had bad experiences with getting involved in exploration in the past and notes the various risks that she discusses. While this information will not be directly included in the model inputs, the professional includes it as a contextual input that might influence accept/reject decisions if the projections are borderline. While the copper base price input is as an average of the historical prices for the prior year, the copper price growth rate input assumption is formed partly by analyzing the 20-year copper price trends and is corroborated by having discussions with salespersons. These discussions yield good information on expectations for demand for copper and the likely prices and volatility to be expected over the long term. Looking for further corroboration, the professional also reads external analyst projections that indicate that the demand for copper is projected to increase significantly in the future. Despite the volatility in copper prices on a short-term basis, the analyst feels confident that using the conservative long-term upward trend the salespersons projected is appropriate for the base case of this long-term project. While gathering information, the FP&A professional compiles a list of the data that is still missing: Purchase price for the base case Discount rate to apply given added project risks Cost of retirement obligation Further corroboration for setup expenses and cash expenses inputs Validation that the sum of purchase price and setup expenses can be used as the depreciable assets base case input as a simplifying assumption ©2019. Association for Financial Professionals Topic 3 6-37 FPAC Exam Prep Platform Topic 4: Seek Missing Information and/or Use Proxies and Assumptions Continue the Information Gap Analysis Once information gaps are listed, the next step in an information gap analysis may involve a preliminary data review to look for less obvious information gaps, determine why gaps exist and point out the root causes if possible, and determine which known gaps are critical or material to the analysis. These steps are discussed next. Perform a Preliminary Data Review A preliminary data review is a process of reviewing the data collected so far to determine if there are gaps within individual data sets, if the information appears to be reasonable and useful, and if existing data could be used to extrapolate values for some of the information gaps on the list. When looking at a set of data for less obvious gaps, various types of data reviews can be performed to get a general sense of patterns or trends in the data and to make other general observations such as the strength of correlation between related data. These steps may help the professional to identify gaps in a time sequence as well as identify outliers or a new required type of information. Tools may include statistical analysis, logic and reasonableness assessments, modeling or discussions with process experts. Preliminary review for gaps in received data may involve producing graphs of the data for visual analysis, calculating statistics such as mean, standard deviation, minimum and maximum, finding the mode and frequency of categorical variables, or removing seasonality from sales data. Statistics are discussed primarily in Part II, Domain B. Logic and reasonableness assessments are methods of evaluating data against expectations based upon education and experience regarding what should logically or reasonably be expected. This is a process of looking for red flags to investigate further. Another similar method is to populate an existing model or an early version of a new model with the data collected so far to determine if the results match expectations. Finally, reviewing your data inputs with process experts can reveal when not all necessary information was accounted for and new inputs or modifications to existing inputs are required. In the case of determining operational costs, the following are examples of common omissions: Materials costs may not include the cost of scrap or rework. Labor costs may omit recruitment, benefits, taxes and sick leave. Distribution costs could omit packaging, damage or theft. ©2019. Association for Financial Professionals Topic 4 6-38 FPAC Exam Prep Platform While explicitly including the multiple considerations that affect a real- world situation could needlessly complicate a model, identifying such data gaps at this point can help the FP&A professional devise proxies and assumptions to ensure that inputs account for these complexities. Determine Why Data Are Missing or Incorrect The main reason that data are sometimes missing is that they do not exist because they are for a new project or product. However, when data should be present in a source but are not, it is important to discover why they are missing or incorrect and determine the root cause of the issue if possible. This may include data in databases that should have been entered but was not or data that were deleted even though they should have been maintained. Another reason is data that were saved under an incorrect name or other identifier code, such as a variant on a vendor’s name (two records may exist, both with incomplete data). If data gaps are found where none are expected, this is a red flag that should trigger a review. Possible root causes for missing or incorrect data include the following: Poor database controls Worksheet errors Miscommunications among departments or individuals Data entry errors If the gaps are found in the data in a database, for example, the database controls, such as its methods of data validation and enforcement of business rules, should be reviewed. It may turn out that there are other flaws in the database such as missing key fields that designate which database tables are related (called primary and foreign keys). Such structural errors can result in a query not finding the requested data. The best solution to these problems is to fix the problems at their root, which is an organization-level task of cleansing and normalizing databases and improving input controls. Applying work-arounds could fix a problem temporarily, but it will also allow the problems to persist and grow worse. Often the real root cause is that no one has ownership and accountability for organizational data quality. Another major source of missing or incorrect data is worksheet errors. It is very easy to create errors in worksheets, for example, a row could be added with additional data but the sum calculation does not sum the new row. The calculations themselves may also contain errors. Worksheet errors are discussed more in Part I, Domain B. Human errors related to miscommunications can take many forms. One common source of error is the misuse of terminology in different parts of an organization. Organizations may have agreed- upon vocabularies, but if they are not consistently followed or there is no shared understanding of how a term should be defined, it can create subtle differences in what data are being submitted. These differences can become significant when viewed at the aggregate level. ©2019. Association for Financial Professionals Topic 4 6-39 FPAC Exam Prep Platform Data entry errors are another source of human errors. This could result in multiple data values stored in the same cell or incomprehensible data entries. Lack of data validation rules is a common source of these problems, but even with proper data validation, some data entry errors can still occur. Some fields, such as name fields, cannot be restricted very much due to the variable nature of names or other text fields. This can lead to duplicate records with slightly different names. Identify Which Information Gaps Are Critical and Material The last step in an information gap analysis is to determine which missing data or variables are the most relevant and need additional efforts to gather, which could be replaced with proxies or assumptions, and which could be omitted entirely. Factors to consider include criticality and materiality. Criticality relates to how the data will be used in the model; materiality relates to the level of impact that having better precision will provide. To assess criticality, it may be necessary to perform an informal cost/benefit analysis for each gap to determine whether the time and cost of collecting the information will provide sufficient improvements in model relevance. In general, highly critical variables are going to require more time spent on gathering data, estimates or assumptions. Materiality relates to how much an error in an estimate can affect the final outputs. The level of precision that is needed is related to the magnitude of the change in outputs relative to the change in input. For example, if interest expense is just one percent of sales, a small amount of error in this estimate will have little effect on the model’s output, so perhaps an assumption can be made. However, if the organization is highly leveraged, it could be important to determine interest expense more precisely. Identify and Contact Owners of Required Information If the FP&A professional has established clear channels of communication with other professionals and knows the availability and schedules of these persons, this past networking should give the FP&A professional an idea of who to contact and when to contact the person. The FP&A professional should be in a position to know who will have the required information, if there is a viable back-up person to ask for the information or for corroboration, or at least who can suggest a good person to talk to. For example, if you need to know what the organization’s WACC will be for a future period, you would start by discussing the matter with treasury professionals because they are the primary source for this information. A treasurer may also refer you to an investment banker, who can discuss prospective interest rates. ©2019. Association for Financial Professionals Topic 4 6-40 FPAC Exam Prep Platform However, if an internal client is responsible under organizational policy for providing specific information but has not yet done so or attempts to pass you off to someone else, you need to own the process and get the information from this person despite his or her resistance. The information provider may not realize any direct benefit from providing the information and may simply be too busy or have a specific reason to resist providing the information. This could be because the information is considered sensitive and permission to release the data may be needed, or it could be that the person has provided estimates in the past and was later held accountable for providing poor information. Overcoming motivation issues of information providers is discussed more in the next chapter when discussing bias. Getting information from persons is often a huge source of delay for planning and analysis, so FP&A professionals need to be polite but persistent. Showing providers that you are documenting their uncertainties and assumptions in addition to the actual inputs can help establish the necessary trust that you will represent their estimate or assumption realistically to decision makers. Seek Alternative Sources of Information If critical information is not available from the first points of contact, it may be necessary to seek the information from alternative sources. Part I, Domain A, discusses sources of information in detail, such as using corporate annual reports or external sources such as suppliers or corporate customers. That part also discusses using surveys, which could be used to gather opinions from a large group of persons on subjects related to consumer behavior, perceptions or other non- financial information. FP&A professionals also gather information from the Internet, but it bears repeating that this information should not be taken at face value and should be verified or corroborated. Use Proxies and Assumptions to Fill Remaining Gaps FP&A professionals use proxies and assumptions when it is not feasible to collect all key data within the analysis period or the data do not exist. A proxy is a substitute for missing data or an alternative path that can be taken to get to the same result. Assumptions are used to adapt substitute data to the needs of the end product or to replace missing values with educated guesses. For example, if salary data are not available for customers, sometimes modelers use level of education as a proxy. Or if data are not available on a particular age group, an FP&A professional could base the model on a different age group and correlate it to what is needed. It may be necessary to purchase demographic data to get such proxies. Note that it is usually not possible to redesign the model outputs when critical data are not available. On occasion, you may be able to select a different calculation or financial function that requires different inputs (ones you have), but it must still satisfy the end product that the decision maker has requested. The end product is the end product, and there may not be any substitute calculation. Therefore, abandoning the model or using proxies and assumptions are often the only possible ways forward. ©2019. Association for Financial Professionals Topic 4 6-41 FPAC Exam Prep Platform Methods of Arriving at Proxies and Assumptions When arriving at proxies and assumptions, FP&A professionals return to the experts. They are still closer to the information than you are. It is their responsibility to provide appropriate proxies and assumptions or to validate any proxies you suggest. The FP&A professional’s role is to solicit and prominently document these proxies and assumptions and to document in the executive summary the level of confidence that should be placed in the model. For example, when using information from a similar project or product as a proxy for a greenfield project or product, the FP&A professional may be able to gather assumptions from persons who were involved in the other projects. Another method of arriving at proxies and assumptions is to use a process of triangulation. For example, if you know the inventory at the beginning and end of the year, you can calculate the average inventory by summing and dividing by two. Averages of any number of other things can be generalized in this way. In another type of triangulation using something like an XY scatter diagram, if you know three pieces of interrelated data, sometimes you can derive a fourth piece of data by extrapolation. The first two points can provide a straight line, and the third known data point indicates the range in multiple dimensions. Solving an equation for a different variable or disaggregating data and looking at specifics might also help. For example, if you don’t know the market share of a privately held competitor but do know that your own market share is five percent at $100 million in revenue and can estimate the competitor’s market share to be about $30 million, then you can derive the market size and the competitor’s market share. Market size = $100M/0.05 = $2.0B Competitor’s market share = $30M / $2.0B = 0.015 x 100 = 1.5% While this is an assumption that could vary, the research done to arrive at the answer shows you have done your due diligence. In all cases, it is important to consider the materiality of the variable. If the rate can be between three and five percent but neither rate will make much difference, then select the more conservative estimate and move on. In another example, it may not be possible to know the cost of some factors of labor costs, such as recruitment, taxes, benefits and sick time. Unless the model is for the HR area, explicitly modeling each factor may be unnecessary. Some organizations instead use an assumption of doubling all salaries to capture the additional employment costs. Including such add-ons is sometimes called a “fully burdened cost.” ©2019. Association for Financial Professionals Topic 4 6-42 FPAC Exam Prep Platform Questioning Proxies and Assumptions The role of the FP&A professional at this stage is to validate that you can give the decision maker what was requested. In other words, you need to assess the impact and implications of the proxies and assumptions on the end product to determine an appropriate course of action. In some cases, there will be no suitable proxies or assumptions that can be made. Producing a model in this situation could be more harmful to decision making than not producing a model at all, and, in such cases, it is your responsibility to say so. After all, you can be held accountable for providing poor plans or analysis. Internal clients are asking FP&A professionals for an end product, but they are also asking for expertise. FP&A professionals need to know the implications of what they are doing and provide the necessary guidance. Decision makers may not know the business drivers or data as well as you do. It is your job to understand them and explain them in a way that highlights the key points and assumptions that are being made. FP&A professionals must continually evaluate how good the answer is going to be and know when they will likely need to provide a rationale for proxies and assumptions. If an answer is required despite your belief that the data are inadequate, then lower your confidence level in the estimate and set expectations in advance with decision makers. For example, you could say, “I can get you an answer, but we may be 20 percent off.” Ongoing Data and Assumptions Collection After iteration, the FP&A professional should have gathered sufficient data and assumptions or proxies to answer the business question. Additional iteration may be needed when late submissions come in from persons or someone has reconsidered an estimate and wants to revise it. Therefore, this cycle of getting data, validating it and so on can continue on until very late in the process. ©2019. Association for Financial Professionals Topic 4 6-43 FPAC Exam Prep Platform Case Study Mining Company Case Study The FP&A professional reviews the list of data that is still missing and determines the following courses of action for each of these sources of information: Purchase price for the base case: Discuss with the executive in contact with the exploration firm looking for an early sale, discuss with the Panama mine expert consultant, and look at historical sale prices of similar mines in Panama. Arrive at a base case price of $400M. Discount rate to apply given added project risks: Discuss with treasury to arrive at a risk- adjusted discount rate of 10 percent for the base case. Cost of retirement obligation: Review inflation-adjusted historical mine obligations and discuss Panama environmental law with Panama mine consultant, resulting in base case of $25M. Further corroboration for setup expenses and cash expenses inputs: See below. Validation that the sum of purchase price and setup expenses can be used as the depreciable assets base case input as a simplifying assumption: Discussions with other finance colleagues lead to the opinion that the sum of base case purchase price ($400M) and setup expenses ($200M) is a valid simplifying assumption, but if the purchase price is bid up higher than this estimate of reasonable market value, the excess should be treated as goodwill and not depreciated. Therefore the FP&A professional leaves this as a separate variable input but sets the initial base case value at $600M. The FP&A professional decides to use data from the organization’s three existing mines as a proxy for the new mine’s setup expenses and cash expenses inputs, pulling data from the organization’s information systems and recent financial statements regarding its copper mines. The information includes the organization’s capabilities for ore extraction (how much copper can reasonably be mined per year), maintenance costs on

FPAC Part 2 Chapter 06 Specifying Outputs and Getting Inputs.pdf

Document Details

Tags

Related

Full Transcript

Upgrade to continue