Published on February 24, 2014
Refining Financial Modeling A Refresher and Continuous Improvement February, 2014
Contents Section Context What is a Financial Model? Need for Dynamic Financial Model Shortcoming of Static Financial Model and introduction of Dynamic financial model framework Significance of Scenario Planning Scenario Planning parameters and linkage with Dynamic financial modeling Example - Financial Model 2 A Refresher Example to illustrate impact of various scenarios © 2014 Deloitte Touche Tohmatsu India Private Limited
Understanding Finance • Financial Measurement • • • • Reading Income statements Balance Sheets Cash flows And more… • Art of Finance – separating hard data from assumptions and estimates • Mechanics of Analysis – Calculating ratios, ROI, Working capital • Separating Cash and Profit • Financial literacy and transparency – recognising how they can boost performance 3 © 2014 Deloitte Touche Tohmatsu India Private Limited
What do you need to do? • Speak the language • Ask questions • Use the information 4 © 2014 Deloitte Touche Tohmatsu India Private Limited
What is a Financial Model?
What is a Financial Model? Definition A Financial Model constructs a financial representation of some, or all, aspects of a firm, a project, or a decision. It is the task of building an abstract representation of a financial decision making situation It is designed to represent the performance of: a financial asset or a portfolio A financial model is a tool for valuation of your business/ project/ financial decision The valuation is an estimate based on assumptions for future growth, cash flows etc., based on past experience and future outlook The simpler and flexible a financial model, the better it is a business a project Any other form of a financial investment v/s Important Considerations v/s It is a set of assumptions about future business conditions that drive projections of a company’s revenue, earnings, cash flows and balance sheet accounts Grounded assumptions, flexible structure and scenario analysis is the key to a successful financial model Define your objectives and intended audience before finalizing your financial model © 2014 Deloitte Touche Tohmatsu India Private Limited
Financial Model vs. Financial Statements vs. Business Plan Financial Model Financial Statements Business Plan • Past and future performance based on sensitivities and represents of some, or all, aspects of the firm. • Previous performance and provides the records that outline the financial activities of a business • • The model is usually characterized by performing calculations, and makes recommendations based on that information. • It is a standard practice for businesses to present financial statements that adhere to generally accepted accounting principles (GAAP), to maintain continuity of information and presentation across international borders Future performance based on key assumptions typically derived from Financial model & in-line with key trends from financial statement. • It provides set of business goals, the reasons they are believed attainable, and the plan for reaching those goals. It may also contain background information about the organization or team attempting to reach those goals. • It may be externally focused, targeting investors or customers. OR Internally focused to frame the strategy of the firm to reach business goals. • Typical uses: o Calculating returns from capital investments o Analyzing a potential merger / acquisition o Forecasting financial performance • Includes: o Profit and Loss account o Balance Sheet o Cash flow statement • Tool for evaluating a business plan, i.e. efficacy of a BP 7 Tool to assess past and existing business health Tool for executing a business idea © 2014 Deloitte Touche Tohmatsu India Private Limited
What is a Financial Model? Example Here’s a basic example of a financial model to ascertain the present value of projected net sales. The main components of this financial model are: 1. Projected costs and sales – While Year 0 is actuals but year 1 to 5 are forecasted based on certain assumptions of future micro and macro economic conditions 2. Discounting factor – this represents the time value of money. “Money in hand is more valuable than possible cash flow in the future” 3. Net Present Value, IRR, Payback period – These are the outcomes of a financial model that can allow to value your business, take financial decisions etc. Illustrative Particulars Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 (INR) (current yr.) Cost of Equipment 1,000,000 Installation Cost 100,000 Maintenance and other 50,000 55,000 60,500 66,550 73,205 Financial running costs Data, Cost 1,100,000 50,000 55,000 60,500 66,550 73,205 including assumptions Sales Net Sales/ Cash flow Valuation assumptions Output 8 Discounting Factor Net Present Value IRR Payback period (in Years) 200,000 220,000 242,000 266,200 292,820 322,102 (900,000) 170,000 187,000 205,700 226,270 248,897 1 0.9091 0.8264 0.7513 0.6830 0.6209 (127,273) 4.64% 4.81 Note: 1. Cost and Sales are assumed to increase by 10% y-o-y 2. Discounting factor is assumed at 10% © 2014 Deloitte Touche Tohmatsu India Private Limited
How to do Financial Modeling? The Financial Modeling Framework • Should explain how business model works, and encapsulate the main drivers of your business • Consists of identifying the list of input variables – internal and external – and determining the interlinkages between them Define Assumptions • Once the inputs for the financial model are finalized, the next step is to collect data • Data should be gathered from reputable sources such as historical financial statements and published reports and statistics • Any judgment-based data points should be backed by sound rationale Collect Data • Start with the ultimate business users in mind, and accordingly define KPIs and supporting analytics for reporting the model • Any feedback from stakeholder reviews should feed back into the model assumptions Report and Review Build Model Test and Validate • The most basic way to test the model is to play with the inputs, i.e., testing every input variable with values that reflect all possibilities • Validation of the model takes two forms: i) audit or diagnostic review of the model by a credible third-party, and ii) continued validation wherein the results are continuously tested against actual performance 9 • Spreadsheet-based modeling remains the most popular method for building financial models • Some key guiding principles for a spreadsheet model are: ─ Planning the layout of the model in advance ─ Breaking up different modules of the model (assumptions, calculations, financial statements, etc.) ─ Planning out scenarios to analyze key drivers of the model that have the most impact on the projections © 2014 Deloitte Touche Tohmatsu India Private Limited
Need for Dynamic Financial Model
Evolution of Dynamic Financial Modeling Types of Financial Models Conventional financial forecasting and Dynamic financial analysis are worlds apart, even though dynamic models have evolved from more traditional models Conventional Financial Model Dynamic Financial Model Drawbacks of a Conventional Model What is a Dynamic Financial Model • In a conventional financial model, the output is based on 2-3 business scenarios only (expected, best case, worst case) • Starting with a conventional financial model, ranges are defined for key parameters, and values within ranges are associated with a probability distribution • Variability of possible outcomes cannot be quantified • The model is then recalculated multiple times, generating a range of results • Multiple variable scenario simulation and risk mitigation design are not possible • Since the full depth and breadth of possible outcomes is not captured, it is not a reliable standalone tool for strategic decision making 11 • Feedback loops and “management intervention decisions” are incorporated into the model • Differences in financial results arising from alternative strategic decisions can be evaluated © 2014 Deloitte Touche Tohmatsu India Private Limited
Dynamic Financial Modeling framework Components of dynamic financial modeling Framework and Components of Dynamic Financial Model 5 4 Analysis/Presentation Interpretation/Optimization Significance of components Readjust strategy for optimization of target values of the company 6 5 Identify variables that need to be modified to optimize results Output variables 6 3 Company model Risk factors 2 Scenario Generator Strategy Evaluation Identify and study input scenarios giving rise to particularly bad results Modeling reaction of company on the behaviour of risk suggested by various scenarios Dynamic models for risk factors affecting the company e.g. economic/business risk 4 3 2 1 1 Calibration Process of finding suitable parameters for the model to produce scenarios Dynamic Financial Model provides business with: ─ Quantitative look at the risk-and-return trade-offs inherent in emerging Strategic Opportunities ─ Structured process for evaluating alternative operating plans ─ Solid information about the interaction of decisions from all areas of company operations 12 © 2014 Deloitte Touche Tohmatsu India Private Limited
Scenario Planning Parameters of Scenario planning A Scenario is a collection of sensitivity changes resulting in changes to financial model e.g. • High inflation and high input cost • Unexpected competition so lower price and volume, together with higher labour costs • Financial risks – e.g. higher interest rates (where these are not hedged), currency fluctuations • Sales risk – e.g. will the price and / or volume be achieved Typical Parameters Considered for Dynamic Financial Modeling Input Parameters/variables in Dynamic Financial Modeling Macro Economic Interest Rates Inflation Exchange Rate Fluctuation Taxation and regulatory structure Cost Cost for major raw materials/Salaries Sales Pricing for finished goods/services Capital Structure 14 External/Internal Influencers External Significance in Dynamic modeling (H/M/L) H External H/M Internal M Internal M © 2014 Deloitte Touche Tohmatsu India Private Limited
Business Value measurement Parameters and linked financial metrics Key lever • • • • Volume/ Price Cost of RM Conversion Cost Overheads • Inventory • Receivables and payables • Margin • Cost of Debt • Amount of leverage • Stock adjustments / times inventory • CAGR • Inflation and expected Margins • Future leverage 15 Impacted parameters Performance Business Indicators • profitability • performance Financial Metrics/Ratios • ROCE (Return on Capital Employed) • PBDIT • Operational leverage • Gross Margin • Accounts payable days and Accounts receivable days Risk Perception • Business stability • Financial leverage • Current ratio and Quick ratio • Interest turnover ratio Expectation • Future growth • Future profitability • Estimated Market Share • Future earnings/cash flow projections Efficiency © 2014 Deloitte Touche Tohmatsu India Private Limited
Identifying the key financial drivers through Financial Modeling… Assumption Sales Volume Selling Price Raw Material Conversion costs Distribution costs Selling & Admin Exp CAPEX Debt portion Equity portion Cost of equity Cost of Debt Receivables Inventory Payables Units Tons INR / ton INR / ton INR / ton INR / ton INR 000s INR 000s % % % % Months Months Months Base Case Scenario 1 Scenario 2 (+)Inc / Year 1 (-)Dec (+)Inc (-)Dec 1,500 10% 15% 5% 2,000 0% 2% -2% 1,350 5% 8% 5% 300 5% 10% 5% 105 5% 8% 5% 440 5% 8% 2% 2,500 3,000 2,500 70% 70% 30% 30% 30% 70% 10% 10% 10% 15% 15% 15% 1 2 2 1 2 1 1 1 1 Base Case Results Revenue (in '000) COGS (in '000) EBIDTA EBITDA % Net profit % ROI % ROE % Fixed Asset turnover (times) Net cash flow (in '000) WACC NPV (in '000) IRR Pay back period (years) 16 Year 1 3,000 1,958 693 23% 2% 15% 10% 1.41 -185.07 Year 5 4,392 3,088 1,304 30% 14% 124% 84% 7.03 608.06 10.09% 403 19% 4 Which is a better business scenario….? • Volume increase as planned AND the price increases by atleast 2% on y-o-y basis • To meet the new scenario: ‒ Purchase more RM & thus cost increases to 8% ‒ Aggressive manufacturing thus conversion cost increase by 10% ‒ Other cost inc. by 8% • Increase CAPEX to meet higher demand • Carry higher inventory to meet orders and avoid stock-out • Provide credit hence receivable increase to 2 month Scenario 1: (+) Inc Scenario 2: (-) Dec Year 1 3,000 1,958 603 20% -2% 6% -7% 1.18 -674.91 Year 1 3,000 1,958 783 26% 6% 18% 14% 1.41 -241.24 Year 5 5,680 4,117 1,563 28% 13% 124% 84% 7.57 602.68 10.09% -538 1% 5 Year 5 3,363 2,426 938 28% 11% 45% 30% 5.38 481.73 10.07% -74 8% 5 • Volume increase by only 5% AND price decrease by atleast 2% on y-o-y basis • To meet the new scenario: ‒ Raw material, conversion distribution remain same ‒ Overhead remains same • Have same CAPEX as no urgency • Borrowings becomes limited hence infuse more Equity • Provide credit hence receivable increase to 2 month • Control inventory to similar levels Though scenario 2 looks grim… but • key business performance indicators are better in 2nd scenario. Higher EBITDA, Net Profit Margin, ROI & ROE in the initial years • The cash outflow from business is better managed and you incur lesser loss in the initial years • On overall basis 2nd scenario has: ‒ Better NPV (though still negative); ‒ Positive IRR ‒ Marginally lower WACC © 2014 Deloitte Touche Tohmatsu India Private Limited
Key sensitivities to guard upon …(illustrative) Impact of Volume & Price (+)10% increase in Volume At same Volume Results Revenue (in '000) COGS (in '000) EBIDTA EBITDA % Net profit % NPV (in '000) IRR Pay back period (years) Year 1 Year 5 3,000 3,000 1,958 2,085 693 915 23% 31% 4% 12% 75 12% 5 (-) 10% decrease in Volume Results Revenue (in '000) COGS (in '000) EBIDTA EBITDA % Net profit % NPV (in '000) IRR Pay back period (years) Results Revenue (in '000) COGS (in '000) EBIDTA EBITDA % Net profit % NPV (in '000) IRR Pay back period (years) Year 1 Year 5 3,000 4,392 1,958 3,088 693 1,304 23% 30% 4% 14% 365 18% 4 Year 1 Year 5 3,000 1,968 1,958 1,394 693 574 23% 29% 4% 7% -218 5% 5 Sales volume has high sensitivity to the NPV and IRR of the investment, even though ratios remain same (+)10% increase in Price At same Price Results Revenue (in '000) COGS (in '000) EBIDTA EBITDA % Net profit % NPV (in '000) IRR Pay back period (years) Year 1 Year 5 3,000 3,000 1,958 2,085 693 915 23% 31% 4% 12% 75 12% 5 (-) 10% decrease in Price Results Revenue (in '000) COGS (in '000) EBIDTA EBITDA % Net profit % NPV (in '000) IRR Pay back period (years) Results Revenue (in '000) COGS (in '000) EBIDTA EBITDA % Net profit % NPV (in '000) IRR Pay back period (years) Year 1 Year 5 3,000 4,392 1,958 2,155 693 2,238 23% 51% 4% 29% 1,446 37% 4 Year 1 Year 5 3,000 1,968 1,958 2,033 693 -65 23% -3% 4% -15% -1,064 -25% >5 Price has the HIGHEST sensitivity to the NPV & IRR and also greatly impacts the performance ratios 17 © 2014 Deloitte Touche Tohmatsu India Private Limited
Key sensitivities to guard upon …(illustrative) Impact of Borrowings & Working Capital Debt to Equity Ratio: 30:70 Debt to Equity Ratio: 70:30 Cash flow Statement EBITDA Interest Tax Change in Working Capital Loan raised / (repaid) Equity raised CAPEX Net cash flow Opening Balance Closing Balance Year 1 Year 2 693 (210) (35) (283) 1,400 750 (2,500) (185) 0 (185) 800 (158) (87) (38) (350) 167 (185) (18) Year 3 Year 5 1,001 1,304 (105) (169) (301) (36) (45) (350) (350) 341 608 (18) 784 323 1,393 Cash flow Statement EBITDA Interest Tax Change in Working Capital Loan raised / (repaid) Equity raised CAPEX Net cash flow Opening Balance Closing Balance Year 1 Year 2 693 (90) (74) (283) 600 1,750 (2,500) 96 0 96 800 (68) (116) (38) (150) 428 96 524 Year 3 Year 5 1,001 1,304 (45) (188) (301) (36) (45) (150) (150) 582 808 524 1,787 1,106 2,595 Cash flow positive from year 1, thus helping in reducing dependence of external funding & business insolvency One month of Payable, Receivable and Inventory Increase in Receivables to 2 & Inventory to 3 month Working Capital Receivables Inventory Payables Working Capital Receivables Inventory Payables Cash flow Statement Net cash flow Opening Balance Closing Balance NPV IRR Unit Months Months Months Year 1 Value 1 1 1 Year 2 (45) 0 (45) 365 18% Year 3 298 (45) 253 Year 5 461 253 715 708 1,286 1,994 Cash flow Statement Net cash flow Opening Balance Closing Balance NPV IRR Unit Months Months Months Year 1 Value 2 3 1 Year 2 (520) 0 (520) -225 6% Year 3 244 (520) (276) Year 5 415 (276) 139 645 653 1,297 Working capital gets adversely impacted due to lower cash flow, thus significantly reducing NPV and IRR 18 © 2014 Deloitte Touche Tohmatsu India Private Limited
Summary & Wrap-up • Financial models can investigate a range of decisions, but Super Models create additional value through advanced analytical techniques and dynamic reporting • A range of scenarios and simulation tools can highlight the economics / impact of financial decisions on financial and operating metrics – The right tool should be selected based on client / project needs and requirements, and financial model objectives and complexity • Auditing a model and validating assumptions is a critical step – one wrong number can destroy a client’s trust in the results and underlying analyses • "A picture is worth a thousand words" - displaying information is crucial to information processing and decision-making 19 © 2014 Deloitte Touche Tohmatsu India Private Limited
Deloitte refers to one or more of Deloitte Touche Tohmatsu Limited, a UK Private Company limited by guarantee, and its network of member firms, each of which is a legally separate and independent entity. Please see www.deloitte.com/about for a detailed description of the legal structure of Deloitte Touche Tohmatsu Limited and its member firms. This material and the information contained herein prepared by Deloitte Touche Tohmatsu India Private Limited (DTTIPL) is intended to provide general information on a particular subject or subjects and is not an exhaustive treatment of such subject(s) and accordingly is not intended to constitute professional advice or services. The information is not intended to be relied upon as the sole basis for any decision which may affect you or your business. Before making any decision or taking any action that might affect your personal finances or business, you should consult a qualified professional adviser. None of DTTIPL, Deloitte Touche Tohmatsu Limited , its member firms, or its and their affiliates shall be responsible for any loss whatsoever sustained by any person who relies on this material. ©2014 Deloitte Touche Tohmatsu India Private Limited Member of Deloitte Touche Tohmatsu Limited
SixThirty is a financial services startup accelerator. ... Analysis, Financial Management, Financial Modeling ... SixThirty - FinTech Accelerator on ...
Startupbootcamp is a global network of ... to your startup! This month, Startupbootcamp ... Startupbootcamp started in 2010 and now operates globally with ...
Apply to a Techstars accelerator today. Meet our Mentors. ... UP Global has joined Techstars to strengthen and support startup communities.
Entrepreneur in Residence; Dell ... for VC and Angel-backed startups to provide financial and scalable ... 2014 financial, and projections for 2014.
... lean startup sessions and our flagship Entrepreneurship 101 series. MaRS hosts a range of events, including hackathons, industry conferences, ...