Transcription
LECTURE NOTESONFINANCIAL MODELINGMBA IV semester (IARE-R16)Ms. P.BINDU MADHAVIAssistant ProfessorDEPARTMENT OF MASTER OF BUSINESS ADMINISTRATIONINSTITUTE OF AERONAUTICAL ENGINEERING(Autonomous)DUNDIGAL, HYDERABAD - 500 043
FINANCIAL MODELLING SYLLABUSUNDERSTANDING THE BASIC FEATURES OF EXCELHours: 09Introduction to modeling, introduction to excel, understanding advanced features of exceldatabase functions in excel, creating charts using forms and control toolbox, understandingfinance functions present in excel, creating dynamic models.UNIT-IUNIT-II SENSITIVITY ANALYSIS USING EXCELHours: 09Scenario manager, other sensitivity analysis features, simulation using excel different statisticaldistributions used in simulation generating random numbers that follow a particular distribution,building models in finance using simulation.UNIT-III EXCEL IN ACCOUNTINGHours: 09Preparing common size statements directly from trial balance, forecasting financial statementsusing excel, analyzing financial statements by using spreadsheet model, excel in projectappraisal, determining project viability.Risk analysis in project appraisal, simulation in project appraisal, excel in valuation,determination of value drivers, discontinued cash flow valuation, risk analysis in valuation.UNIT-IV EXCEL IN PORTFOLIO THEORYHours: 09Determining efficient portfolio, creating dynamic portfolios, portfolio insurance, fixed incomeportfolio management using excel, excel in derivatives black and schools model in excel, Greeksin excel, real options valuation, building a mega model.UNDERSTANDING SUBROUTINES AND FUNCTIONS AND BUILDINGSIMPLE FINANCIAL MODELS USING SUBROUTINES AND FUNCTIONHours: 09Recording and editing macros, subroutines and functions, decision rules, message box and inputbox, debugging, designing advanced financial models using visual basic application user forms,other advanced features, actual model building.UNIT-V
UNIT-1UNDERSTANDING THE BASIC FEATURES OF EXCELIntroduction to modeling, introduction to excel, understanding advanced features of exceldatabase functions in excel, creating charts using forms and control toolbox, understandingfinance functions present in excel, creating dynamic models.INTRODUCTION TO MODELING:Financial modeling is the construction of spreadsheet models that illustrate a company'slikely financial results in quantitative terms. Financial models can simulate the effect of specificvariables so that the company can plan a course of action should they occur.Financial modelling is the process by which a firm constructs a financial representationof some, or all, aspects of the firm or given security. The model is usually characterized byperforming calculations and makes recommendations based on that information. The model mayalso summarize particular events for the end user such as investment management returns orthe Sortino ratio, or it may help estimate market direction, such as the Fed model.A financial model is a mathematical representation of the financial operations and financialstatements of a company. It is used to forecast future financial performance of the companyby making relevant assumptions of how the company would fair in the coming financialyears.It is also a risk management tool for analyzing various financial and economicscenarios and also provided valuations of assets. These models involve calculations,analyzing them and then provide recommendations based on the information gathered. Afinancial model generally includes projecting the financial statements such as the incomestatement, balance sheet and cash flow statement with the help of building schedules such asthe depreciation schedule, amortization schedule, working capital management, debtschedule etc. It encompasses the company’s policies and restrictions imposed by lendersthat would impact the financial position.DEFINITION :“The process by which a firm constructs a financial representation of some, or all,aspects of the firm or given security. The model is usually characterized by performingcalculations, and makes recommendations based on that information. The model may alsosummarize particular events for the end user and provide direction regarding possibleactions or alternatives.”TYPES OF FINANCIAL MODEL:There are various kinds of financial models that are used according to the purposeand need of doing it. Different financial models solve different problems. While majority of
the financial models concentrate on valuation, some are created to calculate and predict risk,performance of portfolio, or economic trends within an industry or a region. The followingare the different types of financial models :DISCOUNTED CASH FLOW MODEL:Among different types of Financial model, DCF Model is the most important. It isbased upon the theory that the value of a business is the sum of its expected future free cashflows, discounted at an appropriate rate. In simple words this is a valuation method usesprojected free cash flow and discounts them to arrive at a present value which helps inevaluating the potential of an investment. Investors particularly use this method i n order toestimate the absolute value of a company.COMPARATIVE COMPANY ANALYSIS MODEL:Also referred to as the “Comparable” or “Comps”, it is the one of the major companyvaluation analyses that is used in the investment banking industry. In this method weundertake a peer group analysis under which we compare the financial metrics of a companyagainst similar firms in industry. It is based on an assumption that similar companies wouldhave similar valuations multiples, such as EV/EBITDA. The process would involveselecting the peer group of companies, compiling statistics on the company under review,calculation of valuation multiples and then comparing them with the peer group.SUM-OF-THE-PARTS MODEL:It is also referred to as the break-up analysis. This modeling involves valuation of acompany by determining the value of its divisions if they were broken down and spun off orthey were acquired by another company.LEVERAGED BUY OUT (LBO) MODEL:It involves acquiring another company using a significant amount of borrowed fundsto meet the acquisition cost. This kind of model is being used majorly in leveraged financeat bulge-bracket investment banks and sponsors like the Private Equity firms who want toacquire companies with an objective of selling them in the future at a profit. Hence it helpsin determining if the sponsor can afford to shell out the huge chunk of money and still getback an adequate return on its investment.MERGER & ACQUISITION (M&A) MODEL:Merger & Acquisitions type of financial Model includes the accretion and dilutionanalysis. The entire objective of merger modeling is to show clients the impact of anacquisition to the acquirer’s EPS and how the new EPS compares with the status quo. In
simple words we could say that in the scenario of the new EPS being higher, the transactionwill be called “accretive” while the opposite would be called “dilutive.”OPTION PRICING MODEL:On, to buy or sell the underlying instrument at a specified price on or before aspecified future date”. Option traders tend to utilize different option price models to set acurrent theoretical value. Option Price Models use certain fixed knowns in the present(factors such as underlying price, strike and days till expiration) and also forecast s (orassumptions) for factors like implied volatility, to compute the theoretical value for aspecific option at a certain point in time. Variables will fluctuate over the life of the option,and the option position’s theoretical value will adapt to reflect these changes.INTRODUCTION TO EXCEL:Excel is a spreadsheet program that is used to record and analyze numerical data. Thinkof a spreadsheet as a collection of columns and rows that form a table. Alphabetical letters areusually assigned to columns and numbers are usually assigned to rows. The point where acolumn and a row meet is called a cell. The address of a cell is given by the letter representingthe column and the number representing a row. Let's illustrate this using the following image.We all deal with numbers in one way or the other. We all have daily expenses which wepay for from the monthly income that we earn. For one to spend wisely, they will need to knowtheir income vs. expenditure. Microsoft Excel comes in handy when we want to record, analyzeand store such numeric data.Running Excel is not different from running any other Windows program. If you are runningWindows with a GUI like (Windows XP, Vista, and 7) follow the following steps. Click on start menuPoint to all programsPoint to Microsoft ExcelClick on Microsoft ExcelAlternatively, you can also open it from the start menu if it has been added there. You canalso open it from the desktop shortcut if you have created one.For this tutorial, we will be working with Windows 8.1 and Microsoft Excel 2013. Followthe following steps to run Excel on Windows versions Click on start menuSearch for Excel N.B. even before you even typing, all programs starting with what youhave typed will be listed.Click on Microsoft Excel
The following image shows you how to do this:UNDERSTANDING THE RIBBON:The ribbon provides shortcuts to commands in Excel. A command is an action that theuser performs. An example of a command is creating a new document, printing a documenting,etc. The image below shows the ribbon used in Excel.RIBBON COMPONENTS EXPLAINED:Ribbon start button - it is used to access commands i.e. creating new documents, savingexisting work, printing, accessing the options for customizing Excel, etc.
Ribbon tabs – the tabs are used to group similar commands together. The home tab is used forbasic commands such as formatting the data to make it more presentable, sorting and findingspecific data within the spreadsheet.Ribbon bar – the bars are used to group similar commands together. As an example, theAlignment ribbon bar is used to group all the commands that are used to align data together.UNDERSTANDING THESHEETS, WORKBOOKS):WORKSHEET (ROWSANDCOLUMNS,A worksheet is a collection of rows and columns. When a row and a column meet, they form acell. Cells are used to record data. Each cell is uniquely identified using a cell address. Columnsare usually labelled with letters while rows are usually numbers.A workbook is a collection of worksheets. By default, a workbook has three cells in Excel.You can delete or add more sheets to suit your requirements. By default, the sheets are namedSheet1, Sheet2 and so on and so forth. You can rename the sheet names to more meaningfulnames i.e. Daily Expenses, Monthly Budget, etc.CUSTOMIZATION MICROSOFT EXCEL ENVIRONMENT:Personally I like the black colour, so my excel theme looks blackish. Your favouritecolour could be blue, and you too can make your theme coulor look blue-like. If you are not aprogrammer, you may not want to include ribbon tabs i.e., developer. All this is made possiblevia customizations. In this sub-section, we are going to look at Customization the ribbon Setting the color theme Proofing settings Save settings
CUSTOMIZATION OF RIBBON:The above image shows the default ribbon in Excel 2013. Let’s start with customizationthe ribbon, suppose you do not wish to see some of the tabs on the ribbon, or you would like toadd some tabs that are missing such as the developer tab. You can use the options window toachieve this Click on the ribbon start button Select options from the drop down menu. You should be able to see an Excel Optionsdialog window. Select the customize ribbon option from the left-hand side panel as shown below. On your right-hand side, remove the check marks from the tabs that you do not wish tosee on the ribbon, For this example, we have removed Page Layout, Review, and Viewtab.Click on the “OK” button when you are doneYour ribbon will look as follows
ADDING CUSTOM TABS TO THE RIBBONYou can also add your own tab, give it a custom name and assign commands to it. Let’sadd a tab to the ribbon with the text Guru991. Right click on the ribbon and select Customize the Ribbon. The dialogue window showabove will appear.2. Click on new tab button as illustrated in the animated image below3. Select the newly created tab4. Click on Rename button5. Give it a name of Guru996. Select the New Group (Custom) under Guru99 tab as shown in the image below7. Click on Rename button and give it a name of My Commands8. Let’s now add commands to my ribbon bar9. Select all chart types command and click on Add button
10. Click on OKYour ribbon will look as follows:SETTING THE COLOUR THEME:To set the color-theme for your Excel sheet you have to go to excel ribbon, and click on aFile Option command. I will Open a window where you have to follow the following steps.
1.2.3.4.The general tab on the left-hand panel will be selected by defaultLook for colour scheme under General Options for working with ExcelClick on the colour scheme drop-down list and select the desired colourClick on OK buttonSETTING FOR FORMULASThis option allows you to define how Excel behaves when you are working with formulas. Youcan use it to set options i.e. auto complete when entering formulas, change the cell referencingstyle and use numbers for both columns and rows and other options.If you want to activate an option, click on its check box. If you want to deactivate an option,remove the mark from the checkbox. You can use this option from the Options dialoque windowunder formulas tab from the left-hand side panel.
PROOFING SETTINGS:This option manipulates the entered text entered into excel. It allows setting options suchas the dictionary language that should be used when checking for wrong spellings, suggestionsfrom the dictionary, etc., You can use this option from the options dialogue window under theproofing tab from the left-hand side panel.
SAVE SETTINGS:This option allows you to define the default file format when sa
Financial modelling is the process by which a firm constructs a financial representation of some, or all, aspects of the firm or given security. The model is usually characterized by performing calculations and makes recommendations based on that information. The model may also summarize particular events for the end user such as investment management returns or the Sortino ratio, or it may .
