Published on February 18, 2014
Financial Modeling Tutorial By EduCBA
What is Financial Modeling ? A financial model represents the financial performance of a company. It represents the financial performance for • Past • Future
Historical input Historical input data should always be taken from the right source. As while making assumption for the forecasted year historical data is one of the criteria needs to be considered.
Assumption Referred to as “drivers”. Some criteria needs to be considered while making an assumption are • No bias should get into the assumptions on the business • Clearly understand the expected changes in future performance • Understand Management expectations • Check out what other analysts think about the company
Color Coding One of the important formatting factor in a financial model. This is done so that a person who has never seen your model comes to know easily whether a certain financial data is a historical input data, calculations or linkages.
Formatting Tip If your year 2009 comes in cell B2 of the income statement then it is advisable that 2009 in other sheets like balance sheet, cash flow statement should also come in cell B2. This formatting helps during linkages.
Preparation of financial modeling To prepare a financial model one needs to understand the company on which the person is preparing the model. The person should also have a thorough knowledge about the industry, its competitors in which his company belong to
Sources In order to analyze a company the different sources that needs to be referred are • Annual report of the company • Transcript • Conference call • Presentations published by the company
5 years of historical data should be used while preparing a financial model You are an “Analyst” not an “Auditor”. Historical data in the annual report published by the company does not compile then don’t sit to tally the financials. For 2011 historical data pick the data from 20112012 and not 2010 -2011.Use the updated annual report Key financial terms like gross profit, EBITDA, EBIT, net profit etc should be available in the income statement Compilation of Historical
BASE Equation For Fixed Asset Equation Items Description B (Beginning) Beginning fixed asset This is the fixed asset which is carried forward from last year A (Addition) Capital expenditure This is the extra asset you bought this financial year S (Subtraction) Depreciation/ sale of asset Depreciation being an expense you need to deduct it Since the assets are sold in this financial year that amount needs to be deducted. E (Ending) Ending fixed asset This fixed asset amount is generated after the addition and subtraction of all the necessary items in this financial year and will be carried forward in the next year
BASE Equation For Shareholders Equity For Long term Debt Equation Items Equation Items B (Beginning) Beginning shareholders fund B (Beginning) Beginning long term Debt A (Addition) Net income / Issuance of equity A (Addition) Issuance of debt S (Subtraction) Repurchase of equity / Dividend paid S (Subtraction) Repayment of Debt E (Ending) Ending equity balance E (Ending) Ending debt balance
Income Statement A financial statement which measures the company’s financial performance over a specific accounting period. It gives a summary of how the business incurs its revenues and expenses through both operating and non-operating activities.
Income Statement Projection Company ABC Income Sheet (Rs m) Net Sales (-)Direct cost Gross profit (-)Selling and admin cost EBITDA (-)Depreciation EBIT (-)Interest expense EBT (-) Income tax Net profit Net Sales Year on year growth CAGR Sales growth percentage provided by the management FY10A FY11A FY12A Historical 2904 (1600) 1304 (350) 954 (101) 853 (44) 809 (175) 634 3408 (2000) 1408 (441) 967 (127) 840 (60) 780 (215) 565 FY13E FY14E FY15E FY16E Forecast 3959 (2500) 1459 (508) 951 (135) 816 (63) 753 (255) 498 Direct Cost & selling & admin cost Percentage on sales Depreciation Percentage on fixed asset
Income Statement Projection Company ABC Income Sheet (Rs m) Net Sales (-)Direct cost Gross profit (-)Selling and admin cost EBITDA (-)Depreciation EBIT (-)Interest expense EBT (-) Income tax Net profit Interest expense Can be calculated by multiplying the interest expense rate and the average closing debt balance FY10A FY11A FY12A Historical 2904 (1600) 1304 (350) 954 (101) 853 (44) 809 (175) 634 3408 (2000) 1408 (441) 967 (127) 840 (60) 780 (215) 565 FY13E FY14E FY15E FY16E Forecast 3959 (2500) 1459 (508) 951 (135) 816 (63) 753 (255) 498 Income tax Calculate the tax rate using the historical and use the same rate in future Profit Calculated by deducting expenses from the revenue
Balance Sheet Projection Company ABC FY10A FY11A FY12A Historical Goodwill Goodwill is not amortized so it needs to be kept constant in the future years. 27 462 87 576 30 473 82 585 40 473 85 598 Fixed Assets PP&E, net 851 950 1100 1671 1671 1671 3,098 3,206 3,369 Current Liabilities & Provisions Accounts Payable Other Current Liabilities Total Current Liabilities 206 248 454 221 223 444 279 257 536 Long Term Debt 1600 1360 875 Shareholder's Funds 1044 1402 1958 3,098 3,206 3,369 Total Assets Total Liabilities & Equity FY14E FY15E Forecast Balance Sheet (Rs m) Current Assets, Loans and Advances Cash Accounts Receivable, net Inventory Total Current Assets Other Intangible Assets Goodwill Fixed asset, long term debt, shareholders fund Calculate using the Base equation and then needs to be linked in the balance sheet FY13E FY16E
Balance Sheet Projection Company ABC FY10A FY11A FY12A Historical 27 462 87 576 30 473 82 585 40 473 85 598 851 950 1100 Other Intangible Assets Goodwill Current assets and current liabilities These items needs to be calculated in the working capital schedule 1671 1671 1671 3,098 3,206 3,369 Current Liabilities & Provisions Accounts Payable Other Current Liabilities Total Current Liabilities 206 248 454 221 223 444 279 257 536 Long Term Debt 1600 1360 875 Shareholder's Funds 1044 1402 1958 3,098 3,206 3,369 Total Assets Total Liabilities & Equity FY14E FY15E Forecast Balance Sheet (Rs m) Current Assets, Loans and Advances Cash Accounts Receivable, net Inventory Total Current Assets Fixed Assets PP&E, net Cash Cash item needs to be linked by the ending cash balance from the cash flow statement FY13E FY16E
Working Capital Projection Current Asset Current Liability Accounts receivable Accounts receivable in days = 365*Average debtor divided by net sales. Using the past historical data assume the number of days for the forecasted years. Accounts Payable Accounts receivable in days = 365*Average Payable divided by net purchases. Using the past historical data assume the number of days for the forecasted years. Inventory Inventory in days = 365*Average inventory divided by net COGS. Using the past historical data assume the number of days for the forecasted years. Other current liability One can calculate the other current liability for the historical as a percentage on the COGS and then you can take an average and assume for the future. After completing this working capital schedule link it to the balance sheet
Cash Flow Statement Assess the amount, timing, and predictability of cash-inflows and cashoutflows, and are used as the basis for budgeting and business-planning. It answers the questions: • Where the money came (will come) from? • Where it went (will go)? The accounting data is presented usually in three main sections: • Operating-activities (sales of goods or services), • Investing-activities (sale or purchase of an asset • Financing-activities (borrowings , or sale of common stock etc)
Cash Flow Projection Company ABC Cash Flow Statement Whatever changes you have incurred in the amount of the item in the income statement , balance sheet in the financial year those changes will be recorded in the cash flow statement according to that items cash inflows and outflows. Operating Activities Net Income Depreciation Change in Working Capital Cash Flow from Operating Activities Investment Activities Capital Expenditures Additions to Intangibles Cash Flow from Investing Activities Financing Activities Proceeds from/ (repayment of) Revolver Issuance / (Repayment) of Long Term Debt Issuance/ (Repurchase of) Equity Dividends Cash Flow from Financing Activities Net Change in Cash Beginning Cash Balance Ending Cash Balance FY10A FY11A Historical FY12A FY13E FY14E FY15E Forecast FY16E
For More Information Knowledge is like a line with no ends so To know more on this topic click on the link below https://www.educorporatebridge.com/financialmodeling/financial-modeling-tutorial/
Be a part of edu CBA Family Visit our website : https://www.educorporatebridge.com/ Free Resources : https://www.educorporatebridge.com/free-courses/ Edu CBA Like us on : https://www.facebook.com/CorporateBridgeGroup Follow us on : https://twitter.com/corporatebridge
If you have found this presentation to be useful, kindly Like Share Follow Follow us! Thank You!!!
Financial Modeling Tutorial. Financial Modeling Tutorial for Beginners. About; Contact; Recommended Books; Categories. Modeling; General; Financial Statements
For full text article go to : https://www.educba.com/financial-modeling-tutorial/ This financial modeling tutorial will help you to create a ...
Financial Model Tutorial ... Financial Modeling Tutorial | What is Financial Modeling - Duration: 1:08:39. edureka! 12,567 views. 1:08:39
Financial modeling is creating a complete program or structure, which helps you in coming to a decision regarding investment in a project or company.
Video Financial Modelling provides low-cost, effective Excel and financial modelling training.
In this free Financial Modeling Training Course, you will learn step by step approach to build Colgate Palmolive Financial Model from scratch.
Financial Models arbeiten mit vereinfachenden und idealisierten Annahmen der Realität, um ein Unternehmen bzw. Projekt quantifiziert abzubilden.
Financial modeling is the task of building an abstract representation (a model) of a real world financial situation. This is a mathematical model designed ...
Financial Data Science Conference; FinTech Forum; GIPS-Tag; Immobilien Forum; Investment Consultant Konferenz; DVFA Kongress; Life Science Conference; Für ...
Financial Modelling View this page in: English. Der Erfolg einer Investition hängt maßgeblich von verlässlichen Finanzmodellen ab. Um Fehlentscheidungen ...