sample print materials
TRANSCRIPT
-
7/27/2019 Sample Print Materials
1/15
PART II,DOMAIN A:ANALYZING INFORMATION
2A-16 2013 Association for Financial Professionals. All rights reserved.v1.0
Topic 2: Define Key Inputs and Input to OutputLogic
With an understanding of the desired outputs for the model, the FP&A
professional can then begin to define the inputs and input-to-outputlogic needed to produce the outputs. Inputs come in many forms. These
may include:
Value drivers (or business drivers) and related key performance
indicators (KPIs).
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 A, Chapter 3: Organization. After
introducing some types of inputs, the discussion 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 out all critical factors and then separating them into direct
inputs, contextual inputs, and derived inputs:
Direct inputs are those inputs or drivers entered directly in the
model and are used as inputs to calculations. Direct inputs can be
variables, constants, or semi-variables:
o 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.
-
7/27/2019 Sample Print Materials
2/15
CHAPTER 1:SPECIFYING OUTPUTS AND GETTING INPUTS
2013 Association for Financial Professionals. All rights reserved. 2A-17v1.0
o Constants (givens) 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).
o Semi-variables (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.
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. Models are
more flexible the more they make use of derived inputs because
derived inputs and the calculations they are based upon leverage the
interrelationships between elements. Derived inputs are discussed
further in Chapter 3, Topic 3.
Additional discussion of variables, constants, and semi-variables
follows.
Variables
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 is not available. Even
when historical data is 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.
-
7/27/2019 Sample Print Materials
3/15
PART II,DOMAIN A:ANALYZING INFORMATION
2A-18 2013 Association for Financial Professionals. All rights reserved.v1.0
Constants (givens)
Constants, also called the givens in a model, are inputs that 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, managements attitude toward
taking risk (risk appetite), or managements preference for debt versus
equity.
Semi-variables (step variables)
Semi-variables or step variables require modeling the relevant range or
ranges for the model, perhaps as separate input fields. For example, if
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.
The determination of which inputs should be direct inputs, contextual
inputs, or derived inputs often starts with a study of value drivers and
their related key performance indicators (KPIs). 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
organizations ability to generate economic value. Activities meant to
influence value drivers are often measured using key performance
-
7/27/2019 Sample Print Materials
4/15
CHAPTER 1:SPECIFYING OUTPUTS AND GETTING INPUTS
2013 Association for Financial Professionals. All rights reserved. 2A-19v1.0
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.
As discussed in Part I, Domain A, Chapter 3: Organization, a financial
value metric such as net profit margin is driven by financial value
drivers such as sales revenue, and each of these drivers are 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.
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. 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, as is discussed in Chapter 3, Topic 2.
Value drivers can also be used to generate derived inputs using theknown 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
for the model. Drivers of capital expenditures will give you your capital
expenditures, and so on.
-
7/27/2019 Sample Print Materials
5/15
-
7/27/2019 Sample Print Materials
6/15
CHAPTER 1:SPECIFYING OUTPUTS AND GETTING INPUTS
2013 Association for Financial Professionals. All rights reserved. 2A-21v1.0
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, 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.
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 mall, 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 KPIsInternal value drivers and related key performance indicators (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 organizations business model and
strategy. When these exist, FP&A professionals should select an
-
7/27/2019 Sample Print Materials
7/15
PART II,DOMAIN A:ANALYZING INFORMATION
2A-22 2013 Association for Financial Professionals. All rights reserved.v1.0
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 organizations 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 a set of drivers and metrics 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, walk-ins to sales ratio, 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,
upselling initiatives, availability of ancillary services, etc.
KPIs: Occupancy rate, price realization, ancillary services paid,
customer complaints, satisfaction with complaint resolution, etc.
Differentiating between direct and contextual inputs
The process of selecting which inputs or value drivers will be direct (orderived) inputs and which will be contextual inputs only involves
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. Sufficientis a
-
7/27/2019 Sample Print Materials
8/15
CHAPTER 1:SPECIFYING OUTPUTS AND GETTING INPUTS
2013 Association for Financial Professionals. All rights reserved. 2A-23v1.0
criterion that will help ensure 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, then
get 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 in to play in a complex model.
More detailed flowcharts 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, if it is 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.
-
7/27/2019 Sample Print Materials
9/15
PART II,DOMAIN A:ANALYZING INFORMATION
2A-24 2013 Association for Financial Professionals. All rights reserved.v1.0
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 slideshow.
High-level logic flowchartsHigh-level logic flowcharts are a top-down method of showing all of the
major influences on a given model output. High-level logic flowcharts
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 (see
Part I, Domain A, Chapter 3 for an overview of value driver trees).
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.A.1-7 shows an extract of an example of
the logic for free cash flow (FCF) resulting from a utility companys
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.
-
7/27/2019 Sample Print Materials
10/15
CHAPTER 1:SPECIFYING OUTPUTS AND GETTING INPUTS
2013 Association for Financial Professionals. All rights reserved. 2A-25v1.0
Exhibit II.A.1-7: Illustrative FCF Driver Flowchart for Economic Assistance Customers
Economic AssistanceCustomer (EAC)
Impact on Free Cash
Flow (FCF)
Economic AssistanceCustomer (EAC)
Impact on Free Cash
Flow (FCF)
Change in excess
cost over billed rate
due to funding
timing
EAC Program Costs
Cost of lost
customers who pay
full rate
Collections from
customers who pay
full rate
Timing of payments
from customers who
pay full rate
EAC customer
collections
Timing of EAC
payments
EAC cost of
electricity
EAC collections
Timing of Assistance
Funding
Operating Expenses
CapEx
F(change in EAC
customers)
F(rates and
competitor rates)
Billed amount
Payment amount
Fixed costs
Rate
Usage
Billed amount
Income
Other billsF(payment terms
and ability to pay)
Billed amount
Payment amount
EAC Income
EAC Income
maximum obligation
Billed amount
Income
Other bills
Electricity rate
EAC usage
Number of EACs
EAC income level
EAC Income
maximum obligation
F(payment terms
and ability to pay)
-
7/27/2019 Sample Print Materials
11/15
PART II,DOMAIN A:ANALYZING INFORMATION
2A-26 2013 Association for Financial Professionals. All rights reserved.v1.0
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.
Exhibit II.A.1-8 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).
-
7/27/2019 Sample Print Materials
12/15
CHAPTER 1:SPECIFYING OUTPUTS AND GETTING INPUTS
2013 Association for Financial Professionals. All rights reserved. 2A-27v1.0
Exhibit II.A.1-8: Revenue Projections of a Simple Model
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 this flowchart up to date so that decision makers
can understand conceptually how the model works. When they
understand the models logic and assumptions, only then 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 professionals
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 models process.
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.
-
7/27/2019 Sample Print Materials
13/15
PART II,DOMAIN A:ANALYZING INFORMATION
2A-28 2013 Association for Financial Professionals. All rights reserved.v1.0
Mining Company Case Study
The Inputs
The following direct inputs are planned for the Panama Mine Purchase Analysis Model. The first twofields 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 (MTs or 1,000 Kg) 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
years 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 US$ 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.
(Continued on next page.)
-
7/27/2019 Sample Print Materials
14/15
CHAPTER 1:SPECIFYING OUTPUTS AND GETTING INPUTS
2013 Association for Financial Professionals. All rights reserved. 2A-29v1.0
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 forthe 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 only
deducted in the final year. Note that F() denotes function of in the flowchart.
Detail-Level Flow
The detail-level flow on the next page shows inputs, the derived inputs, the outputs, and
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 are 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 functionof 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.
(Continued on next page.)
-
7/27/2019 Sample Print Materials
15/15
PART II,DOMAIN A:ANALYZING INFORMATION
2A-30 2013 Association for Financial Professionals. All rights reserved.v1 0
On cell A30, the after-tax cash flow per year is added as cumulative sums, A PERCENTRANK.EXC
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 discountedcash 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 expensesand/or capital. NPV divided by the first-year cash expenses equals the profitability index ratio.
Starting in cell H7, 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 years 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 (see Part II, Domain C for more information).