By Rob Trippe, MBA, Financial Modelling Veteran
Many sophisticated business environments use budgets as a bridge between actual and forecast. Within the corporate finance domain, budgets are used to allocate resources and provide a starting point for current and future period estimates. Budgets and revised budgets are often communicated to shareholders and the investor community in numerous ways such as earnings estimates and road show presentations in the form of a “current estimate”. The budget becomes a foundation for expectation.
Here are five key approaches to a sound budget model:
Budgeting and forecasting methods can be divided into two broad categories: qualitative and quantitative. Listed below are common quantitative forecast techniques for financial statement modeling.
Qualitative techniques may be used to adjust quantitative forecast output, based upon subject matter expertise.
A time series is simply a set of observations measured at successive points in time or over successive periods of time. Time series uses past trends of a variable.
With causal relationships, the forecaster examines the cause-and-effect relationships of a variable with other relevant variables such as the level of consumer confidence, an income statement or balance sheet item. Below are examples of common causal relation calculations:
1. Position calculations represent a company's financial position regarding earnings, cash flow, assets or capitalization. Calculations can be expressed as a dollar amount, a percentage, or a comparison. Position calculations are often referred to as “common-sized” when it is uniformly applied to a whole statement.
2. Metric calculations assess financial position relative to a non-financial figure such as days, transactions or number of customers.
A widely-known causal method is regression analysis, a technique used to develop a mathematical model showing how a set of variables are related. Regression analysis that employs one dependent variable and approximates the relationship between these two by a straight line is called a simple linear regression. Regression analysis that uses two or more independent variables to forecast values of the dependent variable is called a multiple regression analysis.
Smoothing methods are appropriate when a time series displays no significant effects of trend, cyclical, or seasonal components (often called a stable time series). In such a case, the goal is to smooth out the irregular component of the time series by using an averaging process.
This relates closely to valuation’s use of “normalized” statements. The purpose of normalizing financial statements is to adjust the financial statements of a business to more closely reflect its true economic financial position and value conclusions of operation on a historical and current basis.
The term "moving" refers to the way averages are calculated—the forecaster moves up or down the time series to pick observations to calculate using an average of a fixed number of observations.
In calculating moving averages to generate forecasts, the forecaster may experiment with different-length moving averages. The forecaster will choose the length that yields the highest accuracy for the forecasts generated. Weights may be assigned to time periods.
Stochastic modeling is a statistical process incorporating a random element to a figure’s composition, based upon pre-determined parameter estimates. It can be used to determine revenue and expense items. This is one technique coined “predictive analytics”.
Integrated statements are a fundamental requirement for forecast DCF and the budget plays a part in that development process. Both conceptually and mechanically, integrated statements, by acknowledging the balance sheet-income statement-cash flow interdependency, demonstrate a core understanding of corporate finance synthesis and provide a foundation for both accounting and modeling structure. This is why the investment banks and Big Four consulting firms go to great lengths to develop this skill in their incoming analysts. Without integrated statements forecast, DCFs are likely to not hold upon to scrutiny.
Integrated statements can create circular references such as this:
Though conceptually correct, both modeling organizations FAST and Best Practice Modeling recommend avoiding circularity. This can be solved by using a goal seek macro, for instance, maintaining a debt to equity level through all model periods. However this is handled, the best integrating approach will reflect the actual management approach employed in the organization.
The mechanics of the budget as the bridge between actual and future estimate are straight forward.
Therefore; actual, budget, current estimate and longer range plans are each dependent upon a respective upstream model. Any change to a prior period assumption or data may impact all future periods and models thereafter.
The granularity of the budget and a resulting downstream longer term plan is dependent upon need and ability. It is easier to begin by developing greater granularity in a model and rolling up into longer periods than it is to break apart a period into smaller segments, such as going from quarterly to monthly. Pay attention to this during development and attempt to anticipate future requirements.
“Return on Capital (ROC), Return on Invested Capital (ROIC) and Return on Equity (ROE): Measurement and Implications” by Aswath Damodaran, illustrates well the power behind distinguishing between assets in place and growth assets. Separating the two allows management to more accurately gauge the following (quoting Damodaran):
How good are the firm’s existing investments? In other words, do they generate returns that exceed the cost of funding them?
This approach has numerous advantages. First, it eliminates noise and confusion. Second, it sets FP&A up for developing appropriate discount rates by project and growth opportunity, thus avoiding a one size fits all weighted cost of capital approach. And third, it acknowledges that future returns may not be the same as prior returns – discount rates change through time.
Though asset capacity and elasticity constraints (utilization rates) usually prevent the symmetrical distribution of outcomes from occurring in real life, as a planning and forecast tool, budgets most often assume a bell-shaped curve. In such a symmetrical distribution, the most likely outcome is equal to the probability weighted outcome. This will provide compatibility to most downstream models such as valuation models which most often are built upon the assumption of expected, as in valuation.
There is much discussion centered on the development, use, and validity of budgets. Viewing the budget as a bridge from past to future should help eliminate doubts, will provide commonality between FP&A models and synthesize FP&A responsibilities.
It all works together.
Rob Trippe is a financial modelling veteran. With over fifteen years’ experience, Rob has developed corporate finance models for valuation, M&A, forecasting and performance monitoring. He is widely respected for his deep understanding of corporate finance theory, lectures at university and has worked with some of the world’s largest and most respected firms. His research while at the investment bank Houlihan Lokey Howard & Zukin has been published in the Wall Street Journal and USA Today. His cash flow model work has been published in SEC and quarterly press release filings. Rob was accredited in valuation in 2008 and holds an MBA ,Finance from Boston College.
By Rob Trippe, MBA, Financial Modelling Veteran
Through the financial crisis, the advent of drill down database capabilities and with direction from the Federal government, financial modeling is evolving into both a defined art and science. The idea that financial modeling means one knows Excel and work with numbers has been superseded by a financial model governance framework which requires the proper employment of academic theory, collaborative development, identification and management of risks and controls, and verification of final model output through validation techniques and ongoing monitoring practices.
Since the great recession, the Federal Reserve has issued “SR 11-7” which defines what a model is, how a model is developed and implemented and how a model should be proven accurate and effective. The audience for SR 11-7 are financial institution’s model developers, owners and users. But much can be learned from the document to serve the far broader corporate modeling world and is invaluable in pursuing the ill-used and often undefined wording of “best practice” in everyday corporate life.
SR 11-7 begins by eliminating the idea that models can pretty much mean anything to anyone. By definition, a model employs:
A few other model characteristics which may help model identification and rank of importance are:
SR 11-7 sets forth general guidelines to ensure the model development approach is disciplined, knowledge-based and properly implemented.
Banks employ a dedicated development team and resources for key models. With this approach, models operate under the concept of leverage. Just as with operating leverage, substantial upfront time and effort can lead to losses but also extraordinary gain through the quality of output and confidence in that output. Consider taking the time and expense of developing your model upfront, research methodologies, and calculation techniques, give design and structure a top billing and take the time to validate it through back-testing and benchmarking. Model development utilizing lower leverage will witness a more limited reward, but with reduced risk. Models developed and improved on an “as needed” basis over time can result in confusion, key person risk, extraneous bulk, and circuitous audit trails.
Thought to form and structure is critical for all models. From experience, we know that such thought is simply not always the case. All financial models follow the same logical order. They:
Use this commonality to develop discipline in how models are built and structured among and across users and functional areas. Create an intuitive and easy to follow workflow, such as using tabs left-to-right in Excel. Models in MatLab code can leverage replicable building blocks.
I am a fan of the FAST standards which can be found at the link below. The FAST standards view Excel financial models not simply as mathematical calculation tools, but communication tools. I find that powerful, and think any veteran financial modeler will ultimately gravitate towards models which possess well defined structures, clarity in logic, and brief audit trails. FAST views financial models as narratives; with sentences, paragraphs, and chapters. Here is the link to the FAST organization: www.fast-standard.org.
Flow charting financial statement builds provide the user a quick gauge on what data is at hand versus what is needed to complete a model. A flow chart will help identify the required builds and environments from which data will be acquired. That’s helpful in managing large projects. In M&A, a banker or seller’s rep will often provide information which does not align to a valuation model builds. With a flow chart, it is easier to identify and manage what is required versus what is available and provide focus on the point where most models breakdown – the middle. Flowcharting will help visualize core components across models and those which are “plug ‘n play”, meaning unique to the particular model at hand. A good example of this would be a set of financial statements (balance sheet, income statement, and cash flow) in consolidated formats. These are applicable to a range of models; capital allocations, valuation, forecasting, just with each model tailored to provide additional output.
Though your model output is needed urgently, things change, and what you solve for today might not be the same as what is required tomorrow. Solid component piece model building provides built-in flexibility and adaptation. Such models can shed one-time output with reduced risk and react to changing sources and systems more easily.
In financial modelling, no one functional area reigns supreme. Example: a financial model solves for a balance sheet answer. That means keeping accountants involved in the model process, wing-to-wing. Statistical methodologies and techniques may be employed in data transformation, but stats are just part of a broader model framework. At every possible step speak in terms of the language used in a model’s final output environment (finance, accounting, etc.). Functional areas will often use the same terminology to mean different things. Be aware of this and agree on definitions first. When a term is used for the first time, stop and define it for all involved. One company I worked with made investments in equity securities using a portion of equity. Imagine the potential for confusion as model output is passed from entity to entity to entity! “Corporate”, owners and business units do not always coordinate and come to terms on basic terminologies and build and often work under differing constraints.
Below is a link covering Excel design content and protocol (Wall Street Prep). Every workplace across functional areas should be having conversations on modeling topics such as these: www.wallstreetprep.com/knowledge/financial-modeling-best-practices-and-conventions/
Good documentation serves a few powerful purposes; it allows one to communicate a model’s structure, design and output across functions and academic disciplines with confidence. It provides comfort that a model was accurately developed, and it forces the model developer to think longer and harder about both academic standard and quality of design. A common approach to model documentation would include the three primary components of SR 11-7:
SR 11-7 discusses documentation as a critical component of both development and validation. Documentation ensures smoother use of a model as owners and users change over time.
Flow chart documents are powerful tools. Flow charts can come in many forms and there is no one exact manner to flow a model. From my experience, two flow charts stand out as invaluable;
A system flow chart will show, left to right, a model’s data inputs and IT/business unit environments, calculation processes, and model output and IT/business unit environments. A development flow chart will visualize the exact mathematical methodologies and techniques employed to transform input data into useful business information and will generally focus on only one business environment. Flow charts will dramatically improve model buy-in and provide a path for the solid structure. Dead ends, duplicity and unmanageable audit trails now become visual.
Here is a document link to PwC which shows the documentation of a cash flow model with integrating statements.
Model validation is a set of processes and activities intended to verify that a model performs as intended and as expected. All components of a model (inputs, calculation processes, outputs) should be subject to this verification. Validation should be commensurate with the potential risk in the model’s use. Validation does not end once a model is implemented. The same validation tools which are used during development can be employed on an ongoing manner. SR 11-7 recommends established periodic review (seldom seen in the corporate finance world) and the establishment of thresholds and tolerance levels when model output deviates from expectation or actual.
The tools SR 11-7 suggests for validations are back-testing and benchmarking.
Both require a degree of independence from the model developers and owners, though that would vary case by case in a non-regulatory environment. Seminars, workshops, and certification are available for model validation.
The “Use Test” adds qualitative validity and is mentioned in Basel II. Model validation through sensitivity and scenario analysis rounds out the validation process. Sensitivity analysis tests for the impact of a change to an input relative to the change in output. Scenario analysis would involve multiple changes to inputs to reflect a given set of circumstances.
The Global Association of Risk Professional has numerous articles on model risk and validation. The link is https://www.garp.org.
For starters, know exactly what your model solves for. Define your final model output upfront and in painstaking detail. What is the answer conceptually and how will it be expressed? What environment will the final model output be in and will it affect downstream models? Example: if you are developing a net cash flow model, make sure your model solves for a net figure (all economic benefits less all economic detriments resulting from the implementation of the planned activity). Another example is fair value versus fair market value. Fair value is a recently conceived accounting measure for balance sheet reporting. Fair market value is an age-old valuation concept. Lawyers go to remarkable lengths to define financial statement items and their calculations for the purposes of debt covenants in securities documentation. Model developers should as well. It’s arduous, but an impressive example.
Acknowledging conceptual limitations is critical to model integrity. All theory is limited and so too are models which depend upon it. A classic example is CAPM. Simple, intuitive, widely adopted, uncanny in accuracy and flawed in its claim to measure expected returns. Another example is IRR. Solving for IRR is extremely useful when comparing similar projects and investments, but IRR also comes equipped with built-in pitfalls. If you know your model’s limitations upfront and have articulated these limitations comprehensively, you will have a far greater chance of model acceptance and adoption than if you are broadsided during the challenge and cross-examination.
The Fed asks that model developers give thought to various theories and approaches (for example DCF versus normalized earnings, or market multiples versus income approach). Documentation provides a platform to communicate conceptual soundness and academic theory and empirical evidence should be cited, as should alternative approaches. Qualitative judgment should be challenged and put to test to ensure that subjective adjustments to the model are not simply compensating for an equal but opposite model error.
Once, I opened a weekly forecast model to find it lacking any standard professional structure and protocol. Fair enough, this had been someone’s individual work assignment and they knew it well. The harder challenge to the model was the conceptually incorrect methodology it employed in attempting to forecast EBITDA. The model extrapolated a small percentage of monthly revenue into a full month’s figure. The population base (i.e. a number of customers) of the revenue streams was small (about 40) and dissimilar. This is not right (just because it rained the first two days of the month does not mean it will rain all month). Adjustments required to reach an accurate estimate were cumbersome and undermined model credibility. So, seek advice from other business units and corporate for model approaches, if needed. Coordination from corporate to business units is not always as strong as one might hope. In the absence of methodological guidance, the business plan should be the starting position for any forecast in corporate finance. This will also serve a second use in keeping your business plan development in check.
Another example of aligning to academic standard is financial statement structure and terminology. Over time companies will have developed statements and statement terminology meaningful internally. Taking the time to document and align to common statement structure and terminology will dramatically improve a model’s adoption by future users and outside parties. EBIT, EBI and EBT for example, should be clearly shown as such. “Cash flow” is a generic term. So, define your cash flow as you would read from a textbook, such as “net debt free cash flow to equity holders”.
Ongoing monitoring is highlighted in SR 11-7 to ensure a model continues to function as intended and to evaluate whether any external changes require model alteration. Ongoing monitoring will also ensure that changes by a model user separate from the developer do not affect the model’s intended output. These would include overrides, partial formulae, etc.
Sensitivity analysis and benchmarking are specifically cited for monitoring purposes, as is outcomes analysis. Outcomes analysis is the comparison of model output to actual outcomes. Back-testing, which was previously mentioned, is a type of outcomes analysis and may serve as an excellent model development approach as well.
Policy and procedure formalize risk management activities for implementation. SR 11-7 recommends an emphasis be placed on testing and analysis with a key goal of promoting accuracy. These roles and responsibilities can be divided among ownership, control, and compliance.
Strong governance coordinates processes and model output across functional areas and validates the final model output. It provides a venue for sharing ideas across areas and business units. Governance activities may include:
Become an informed, insightful and invaluable employee by utilizing its guidance, even in non-regulatory environments. Excellent executive decision-making demands excellent modelling and analysis.
SR 11-7: www.federalreserve.gov/bankinforeg/srletters/sr1107.htm
Rob Trippe is a financial modelling veteran. With over fifteen years’ experience, Rob has developed corporate finance models for valuation, M&A, forecasting and performance monitoring. He is widely respected for his deep understanding of corporate finance theory, lectures at university and has worked with some of the world’s largest and most respected firms. His research while at the investment bank Houlihan Lokey Howard & Zukin was published in the Wall Street Journal and USA Today. His cash flow model while at the Hertz Corp. was published in SEC and quarterly press release filings. Rob was accredited in valuation in 2008 and holds an MBA, Finance from Boston College.
By Richard Reinderhoff, Freelance Consultant Strategy & Operations
A rolling forecast is not only about seeing the future unravel, but a constant evaluation of the management team to see if they are able to adjust their operations on time. Without it, any form of strategic planning becomes useless. Below you find a real-life case. Step-by-step each question will be briefly discussed. It is about a foreign business unit, which was part of a large European corporation, on the brink of a crisis.
You want the rolling forecast to have the basics. This means there should be an overview of the budget: Budget (n), where “(n)” is the actual year, next to the actuals of previous years, Year (n-1) and (n-2).
In this overview, you see that the “year-to-date” numbers by management are optimistic. The plan was approved (sales target 43,0 million), meaning that the executive team knows how the management team will realise this growth scenario, in the last 2 quarters of the year.
Next, you have the “year-to-date actuals” forecast. The local management team might want to see month-by-month numbers, to manage the sales force/sales division. As an executive team, you don’t want to start micro-managing a local business (you hired a country manager, remember). That’s why the budget consists of YTD numbers.
The first month was better than budget, pushing the YE (December) up to 43,4 million. Yet, the following months the business turned sour. What has been happening?
You want to know if the management team is focussed and if the quality of the forecast is adequate, to achieve the quarterly results.
You see repeatedly the first month being overly optimistic forecasted by the management team: YTD 6,2 expected in February, YTD 5,3 realised; YTD 8,9 expected in March, YTD 7,1 realised; YTD 11,1 expected in April, YTD 10,1 realised. Is this just a bad quarter and what are their plans to recover lost sales? Or are they ‘wishing’ things will turn out for the best?
A strategist looks just a little bit further. With a 13 months(!) rolling forecast you can get the next month projected twice! Near the monthly close, the management team has to forecast coming month revenues, based on their order book or some kind of sales projection. In addition, the same people should forecast the same coming month, but 1 year ahead. “Business-as-usual” or is there a something on the horizon?
YTD Februari, March and April of the actual year are the same as the YTD months of the forecasted year, 6,2, 8,9 and 11,1 million. The management team is thinking “business-as-usual”.
(Note: The YTD Actual of January (3,4), changed in the forecasted year to 2,5 million. This was an unwitting mistake, yet explained because actual YTD sales had dropped 0,9 million in February (from 6,2 to 5,3 million). This kind of planning should actually always occur, but some executives don’t want to see reality, that quickly.)
Any trend should appear here, the forecasted 12 months (FC12). It shows the expectations the management team has about the evolution of the industry and/or the commercial impact of operational problems, eg. out-of-stock, recall, strikes. It presents the foundation for the next business plan, hence no surprises anymore.
Each month the business is loosing a million or more in sales and the local management team isn’t seeing any improvement, thus not acting. This confirms that the management team is ‘wishing’ for a better future. Is the business loosing market share? Or is there another crisis?
The rolling forecast gives the executive team the opportunity to discuss with the management team what is happening and to decide on the best way forward. They can coach the management team through strategic choices and financial decision making.
In this case, there was another crisis and the executive team intervened. The country manager was effectively ousted and the thirty-something finance director and sales manager were put in charge. The executive team (approving their monthly purchase orders, of course) accepted the turnaround plan writing overnight by the finance director, and their re-forecast of May to YTD (Decembre) 21,2, down from 39,7 million.
The decisions of the executive team and the actions of the management team will appear in the rolling forecast. Again, short term predictions, YE (December) stability, and solid long term outlook (FC12).
The YTD monthly sales now are higher than forecasted, several months in a row. The YE improved too. Also the FC12 in August seemed more realistic (22,7, from 16,9 million), supported with 1 year ahead forecasts justifiably being lower. This gave the executive team the option to sell the business.
Lessons learned: Look outside the reporting deck!
The local finance director foresaw the downturn. He had been looking at the local accounting numbers, without all the reporting contingencies and reserves. In addition, he saw that inventory of their (worldwide) suppliers was growing fast, according to Bloomberg. This indicated a general slowdown in the segment. Fueled with ‘bad’ management, it was a crisis in the making. The turnaround plan focussed on expanding into another segment: fewer volume sales, yet solid profits.
Above were the key-questions related to Sales. You should also have a rolling forecast of the Operational Profit (OP). This allows the executive team to monitor what management is doing to improve operations (from COGS to overhead). Depending on the industry, add an Order book rolling forecast. To complete the monthly forecast executive deck, add a quarterly overview. In this way, you can have OP/Sales (%), which is relevant to all publicly held companies.
Even with the best forecast at hand, always look outside the reporting deck. Each step generates different questions. Talk to the management team. Remember, a rolling forecast means continually reviewing the (non-)actions of the management team and adjust operations in accordance with the business focus. A rolling forecast is one of the best first steps towards having an agile business culture.
by Dr. Amarendra Kumar, General Manager of FP&A at Pyramid Consulting
There is evidence that FP&A interest is growing fast. Each and every day, CFOs feel the pressure building on the finance function to contribute more to business success. Within the CFO’s organization, the responsibility for tracking, assessing and reporting corporate performance normally falls to the Financial Planning and Analysis (FP&A) group.
In reality, FP&A specializes in analyzing and planning for the future, wearing multiple hats and identifying various improvement strategies. A valued FP&A specialist is someone who has the ability to engage with and influence the full breadth of top management – not just CFO – ensuring they have the necessary information. The specialist will explain why the business needs to go towards x, y, z markets and not the a, b, c direction they were planning.
Members of FP&A are the Finance “ambassadors” to business leaders. Embedded across the business, they are a crucial part of decision making in areas such as planning, making resourcing decisions, measuring success, approving investments, and more. Roles include working with the marketing, sales, product, and engineering departments, as well as corporate (which touches on everything, and interacts directly with the CFO). A strong FP&A individual will have the ear of the sales director and can talk to the commercial director and operations director. He/she can sit down with the managing director and also be the right-hand man for the CFO.
FP&A is historically seen as strictly a financial function. There is often confusion regarding the roles of Accounting and FP&A and their differing objectives. Accounting, on the other hand, is very much a science, focused on meeting GAAP standards, instituting controls and shortening the close process. As was previously quoted by Mark Gandy, G3CFO, "The financial controller typically looks backward, the FP&A professional looks forward and sideways, diagonally, upward, downward, multi-dimensionally, and so on".
The role of an FP&A professional is largely a new and evolving one—to be truly great s/he has to be flexible, quick and adaptive. As the primary driver for financial planning, forecasting, reporting, and business analysis, FP&A plays a critical role in the organization and with their business partners.
FP&A moves beyond the traditional budgeting process to link strategic and operational planning. It must focus on high-quality analytics and predictive planning to analyze multiple scenarios and make smart decisions more quickly than ever before. Information delivered quickly, flexibly, in a format most relevant to the issues at hand, is more important than ever. FP&A also has the ability to measure how well Accounting and Operations are collaborating and supporting the company’s long-term goals. An optimized FP&A group, with the direction of executive leadership, has close ties with Accounting and Operations and applies their expertise to facilitate a collaborative business environment.
The definition of FP&A
FP&A generally includes several discrete processes. While these systems can be managed separately, their ownership requires a common skill set. This includes an understanding of accounting, finance theory, data sources and definitions, modeling, creative problem solving and the economics of the business. The processes typically owned by FP&A include: – Budgeting – Forecasting – Strategic Planning – Management Reporting – Financial Analysis – Capital Planning – Business Modeling (e.g., new ventures and investments)
The Skill Sets of FP&A
Ability to communicate with and gather information from business partners - ability to coordinate FP&A tasks with the corporate calendar or the assigned deadline - Ability to prepare reports and/or make presentations - Ability to build budgets, forecasts, annual plans and so on - Ability to receive, analyze, integrate and consolidate assumptions and data from business units - The knowledge of finance principles and processes - Ability to synthesize information to create conclusions, alternatives and recommendations - Technical aptitude - Candidates should have the ability to solve problems utilizing technology- Knowledge of spreadsheet and database structures and functions - ability to perform variance analysis and reporting - Ability to define, incorporate and report on financial and/or non-financial key performance indicators - Intelligence, natural curiosity, and a desire to learn.
Dr Amarendra Kumar is currently working in Pyramid IT Consulting Inc, as a General Manager (Finance) and heading profile of Financial Planning, Analysis & Management Reporting.
By James Myers, Global Finance Executive and FInance Transformation Consultant
Copyright, ©2017 fpa-trends.com. All rights reserved.