Financial modeling Project Finance & Infrastructure 2018 4.4 (468 ratings) Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
Many people do not realize that spreadsheets can be used for more than just creating tables and charts and summarizing data. Although spreadsheets were originally designed to function as electronic ledgers and accounting worksheets, they are now commonly used for creating complex mathematical models. Instead of just storing, summarizing, and formatting data, a financial model is used for analysis.
What is a Model? Generally, a financial model consists of one or more input parameters along with data and formulas that are used to perform calculations or make predictions. By changing the values of the input parameters, you can do 'what-if' studies to see what happens when the inputs change. Some examples of financial models are the financial calculators that can be downloaded from Vertex42.com.
Financial Templates and Spreadsheets for Excel
- Excel Business Templates - vertex42.com - Check out our collection of business templates for Excel, including financial statements, budgeting, loan calculators, time sheets, project management, and more.
- Free Financial Spreadsheets - www.exinfm.com - This page by Matt H. Evans on his site, Creating Value through Excellence in Financial Management (exinfm.com), lists over 75 free spreadsheets related to accounting, personal and corporate finance, project management, and cash flow valuation.
- Excel Financial Templates - www.ozgrid.com - If you are looking to buy a collection of Excel financial templates, this package comes with a '... bonus pack of 30 additional excel templates providing 'what-if' financial modelling, bond valuations, balanced scorecard templates, NPV tables, financial functions for Excel, financial ratios and many more.'
Excel Finance Books and Articles
A number of books on finance, financial modelling, and building models using Excel and VBA are listed on the right.
After you have a model built in Excel, one advanced method used in forecasting and decision making is Monte Carlo Simulation. For a basic introduction, take a look at the article, 'Monte Carlo Simulation in Excel: A Practical Guide'.
One of the attractions of using Excel for financial modeling is the variety of financial formulas at your disposal. For a list of the financial functions available in Excel, organized by category, see the article, 'Excel Financial Functions and Formulas'
Financial Modeling in Excel for Dummies - by Danielle Stein Fairhurst
I've read many 'for dummies' books because they typically do a good job of presenting basic information in a practical way. That describes this book very well. It focuses on how to use Excel for financial modeling, with an emphasis on modeling.
Chapters 3-5 contain many important modeling concepts that are also applicable to engineering models. The author doesn't talk about engineering - I just bring that up to emphasize that many of the concepts are universal. The author does a great job of explaining how to apply these fundamental concepts in Excel, such as documenting assumptions, error-checking, designing a model to be used by others, and how to review a spreadsheet created by somebody else. Even advanced Excel users could probably learn some things from chapters 3-5.
Chapters 6-9 cover mostly Excel basics with a few intermediate Excel concepts (like named ranges, goal seek, scenarios, and dynamic links in chart objects). All the information about Excel is very targeted for use in financial modeling, such as using goal seek in a break-even analysis.
Chapters 10-12 are a good representation of the financial topics covered in the book. Chapter 10 describes in detail how to build a model that integrates an income statement, cash flow statement, and balance sheet. Chapter 11 explains how to build a basic discounted cash flow valuation. Chapter 12 shows how to model depreciation and create a budget model.
Some of the screenshots of dialog boxes, contextual menus and sidebar panels are too small to read, but the text within the book usually gives you enough step-by-step detail to figure out how to get the job done.
Financial Modeling Software for Excel
I'm not going to list all of the financial modeling software that is available for Excel, but I will offer a few suggestions:
- Take a look at the financial functions that are already available in Excel.
- Look for add-ins that provide additional financial functions. Many add-ins include specialized functions that you can use to create your own models.
- Look for add-ins that allow you to analyze a financial model that you have created. Risk Solver, Crystal Ball and @Risk are probably the best that I know of for Monte Carlo simulation, decision, and risk analysis.
You can find a number of different add-ins in our directory of Excel Links.
A financial model is a mathematical representation of the financial operations and financial statements of a company. It is used to forecast future financial performance of the company by making relevant assumptions of how the company would fair in the coming financial years. It is also a risk management tool for analyzing various financial and economic scenarios and also provided valuations of assets. These models involve calculations, analyzing them and then provide recommendations based on the information gathered. A financial model generally includes projecting the financial statements such as the income statement, balance sheet and cash flow statement with the help of building schedules such as the depreciation schedule, amortization schedule, working capital management, debt schedule etc. It encompasses the company’s policies and restrictions imposed by lenders that would impact the financial position.
Investopedia definition of Financial modeling.
Download Corporate Valuation, Investment Banking, Accounting, CFA Calculator & others
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 and provide direction regarding possible actions or alternatives
Why Financial Models are prepared?
Financial models helps in conducting historical analysis of a company, projecting a company’s financial performance used in various fields such as Project Finance, Real estate, Personal finances, Non-profit organizations, Banks, Oil and Gas projects, Financial institutions, Government, Investment banking, Equity research etc.
These professional models are predominantly used by the financial analyst and are constructed for many purposes, such as valuation of a company/security, determining the benefits/demerits of a takeover or merger, judging an Initial Public Offer (IPO), forecasting future raw materials needs for a corporation etc.
Types of Financial Models
There are various kinds of financial models that are used according to the purpose and 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 following are the different types of financial models:
1) Discounted Cash Flow model
Among different types of Financial model, DCF Model is the most important. It is based upon the theory that the value of a business is the sum of its expected future free cash flows, discounted at an appropriate rate. In simple words this is a valuation method uses projected free cash flow and discounts them to arrive at a present value which helps in evaluating the potential of an investment. Investors particularly use this method in order to estimate the absolute value of a company. If may want to learn more about Financial Modeling here
2) Comparative Company Analysis model
Also referred to as the “Comparable” or “Comps”, it is the one of the major company valuation analyses that is used in the investment banking industry. In this method we undertake a peer group analysis under which we compare the financial metrics of a company against similar firms in industry. It is based on an assumption that similar companies would have similar valuations multiples, such as EV/EBITDA. The process would involve selecting the peer group of companies, compiling statistics on the company under review, calculation of valuation multiples and then comparing them with the peer group.
4.6 (1,674 ratings)
3) Sum-of-the-parts model
It is also referred to as the break-up analysis. This modeling involves valuation of a company by determining the value of its divisions if they were broken down and spun off or they were acquired by another company.
4) Leveraged Buy Out (LBO) model
Included in the types of Financial model is the LBO Model. It involves acquiring another company using a significant amount of borrowed funds to meet the acquisition cost. This kind of model is being used majorly in leveraged finance at bulge-bracket investment banks and sponsors like the Private Equity firms who want to acquire companies with an objective of selling them in the future at a profit. Hence it helps in determining if the sponsor can afford to shell out the huge chunk of money and still get back an adequate return on its investment.
5) Merger & Acquisition (M&A) model
Merger & Acquisitions type of financial Model includes the accretion and dilution analysis. The entire objective of merger modeling is to show clients the impact of an acquisition 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 transaction will be called “accretive” while the opposite would be called “dilutive.”
6) Option pricing model
As it is defined “Options are Derivative contracts that give the holder the right, but not the obligation, to buy or sell the underlying instrument at a specified price on or before a specified future date”. Option traders tend to utilize different option price models to set a current theoretical value. Option Price Models use certain fixed knowns in the present (factors such as underlying price, strike and days till expiration) and also forecasts (or assumptions) for factors like implied volatility, to compute the theoretical value for a specific 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.
If you want to learn Financial modeling like a professional analyst, then you can take advantage of our Financial Modeling Courses here
Recommended Articles
Financial Analyst Course- All in One Bundle
250+ Online Courses
1000+ Hours
Verifiable Certificates
Lifetime Access