INTRODUCTION
Financial modeling is the task of building an abstract representation (a model) of a real world financial situation.
DEFINITION OF MODELING AND 'FINANCIAL MODELING’
MODELING is designing and analyzing a mathematical representation of an economic system to study the effect of changes to system variables.
Modeling can be:
1. The act, art, or profession of a person who models.
2. The process of producing sculptured form with some plastic material, as clay.
3. The treatment of volume, as the turning of a form, in sculpture.
4. The representation, often mathematical, of a process, concept, or operation of a system, often implemented by a computer program.
5. The technique of rendering the illusion of volume on a two-dimensional surface by shading
'FINANCIAL MODELING'
MEANING-
Financial modeling is a general term that means different things to different users; the reference usually relates either to accounting and corporate finance applications, or to quantitative finance applications.
It is a process by which a firm constructs a financial representation of some, or all, aspects of the firm or given security.
While there has been some debate in the industry as to the nature of financial modeling - whether it is a tradecraft, such as welding, or a science - the task of financial modeling has been gaining acceptance and rigor over the years.
Typically, financial modelling is understood to mean an exercise in either asset pricing or corporate finance, of a quantitative nature. In other words, financial modelling is about translating a set of hypotheses about the behavior of markets or agents into numerical predictions; for example, a firm's decisions about investments (the firm will invest 20% of assets), or investment returns (returns on "stock A" will, on average, be 10% higher than the market's returns).
The financial 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.
2. Create a own reliable model to identify weaknesses and predict future performance.
3. Gain an in-depth understanding of how to build a business case.
4. Learn how to enhance the decision-making process.
5. Translate business concepts into logically structured models and formats.
6. Learn to prevent incorrect use of your model by protecting worksheets
7. Learn to validate data entry by setting data entry parameters.
8. To create drop-down boxes which enable a model to produce a series of results depending on scenario variable selected.
Investopedia explains 'Financial Modeling'
Financial models can be constructed in many ways, either by the use of computer software, or with a pen and paper. What's most important, however, is not the kind of user interface used, but the underlying logic that encompasses the model.
A model, for example, can summarize investment management returns, such as the Sortino ratio, or it may help estimate market direction, such as the Fed model.
DEFINITION-
Wikipedia defines financial modeling as the task of building an abstract representation (a model) of a financial decision making situation.
SELECTED AREAS OF FINANCIAL MODELING APPLICATION:
•Business valuation, especially discounted cash flow.
•Cost of capital or WACC.
•Financial statement analysis.
•Modeling and analysis of financial markets.
•Modeling the term structure of interest rate and credit spread.
•Portfolio problems.
•Project finance.
•Real options.
•Risk modeling.
•Valuation (finance).
•Option pricing.
EXAMPLE-FINANCIAL MODELING:
FINANCIAL MODELING USING EXCEL:
Microsoft Excel is a spreadsheet application developed by Microsoft for Microsoft Windows and Mac OS X. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications. It has been a very widely applied spreadsheet for these platforms, especially since version 5 in 1993, and it has replaced Lotus 1-2-3 as the industry standard for spreadsheets. Excel forms part of Microsoft Office.
Financial modeling using excel requires a person to know:
Basics- of finance and Excel
and in additional they need to determine-
· The basic building blocks of a good financial model.
· The objective of and inputs for the model.
· Various types of excel models.
· Know the useful shortcuts, functions and conventions of excel.
OBJECTIVES OF FINANCIAL MODELING USING EXCEL:
The objective of a financial model in excel is to easy the task of financial managers by:
1. Discovering ways to measure and interpret the performance of your company using Excel modeling in seconds.
2. Create a own reliable model to identify weaknesses and predict future performance.
3. Gain an in-depth understanding of how to build a business case.
4. Learn how to enhance the decision-making process.
5. Translate business concepts into logically structured models and formats.
6. Learn to prevent incorrect use of your model by protecting worksheets
7. Learn to validate data entry by setting data entry parameters.
8. To create drop-down boxes which enable a model to produce a series of results depending on scenario variable selected.
1. Excel Fundamentals for the Finance Professional to:
- Learn basic features of Excel and how to properly navigate and format Excel files and worksheets
- Learn basic functions and creating calculations in cells and linking between tabs (worksheets)
- Basic data manipulation and realizing the power and capabilities of Excel
- Learn relevant financial formulas and functions and how to begin maximizing Excel’s abilities
2. Advanced Excel for Data Analysis:
- To minimize as much manual labor as possible in data analysis
- Learn to use the most overlooked Excel formulas that will make your life easier
- Learn powerful functions built in Excel that streamline your analysis
- Learn how to build macros to automate common tasks
3. Excel Charting & Graphing Techniques & PowerPoint Integration to:
- Translate Excel analysis into meaningful charts and graphs to visually present your work
- Master the skills necessary to create robust dynamic charts easily and effortlessly
- Learn different techniques and best practices of integrating charts into PowerPoint
- Advance beyond simple charting functions to create multi-layered graphs that combine and display multiple data sets and ideas simultaneously.
PURPOSE OF FINANCIAL MODELING USING EXCEL:
1. FOR FINANCE MANAGERS :
It is customized for Finance Managers to build user-friendly financial model for better decision making in:
It empowers with the skills required to use Excel as a better planning tool for various marketing activities.
Customized for HR professionals to simplify their manual HR tasks and automate the reports using Excel.
EXAMPLES: FINANCIAL FUNCTIONS IN EXCEL.
1. FOR FINANCE MANAGERS :
It is customized for Finance Managers to build user-friendly financial model for better decision making in:
- Capital Structures
- Company Valuation
- Forecasting
- Discounted cash flow modeling
It is specifically for FINANCIAL CALCULATIONS that are interconnected. At one point you change the FIGURE so as to see immediate changes in RESULT accordingly.
2. FOR MARKETING PROFESSIONALS:
- Marketing Plan
- Sales Pipe Analysis
- Sales Promotion Effectiveness
- Marketing Dashboard, etc
Customized for HR professionals to simplify their manual HR tasks and automate the reports using Excel.
- Compensation Planning
- Manpower Planning
- Payroll
- HR Dashboard, etc
EXAMPLES: FINANCIAL FUNCTIONS IN EXCEL.
Function
|
Description
|
ACCRINT function
|
Returns the accrued interest for a security that pays periodic interest
|
ACCRINTM
Function
|
Returns the accrued interest for a security that pays interest at maturity
|
AMORDEGRC function
|
Returns the depreciation for each accounting period by using a depreciation coefficient
|
AMORLINC function
|
Returns the depreciation for each accounting period
|
COUPDAYBS function
|
Returns the number of days from the beginning of the coupon period to the settlement date
|
COUPDAYS function
|
Returns the number of days in the coupon period that contains the settlement date
|
COUPDAYSNC function
|
Returns the number of days from the settlement date to the next coupon date
|
COUPNCD function
|
Returns the next coupon date after the settlement date
|
COUPNUM function
|
Returns the number of coupons payable between the settlement date and maturity date
|
COUPPCD function
|
Returns the previous coupon date before the settlement date
|
CUMIPMT function
|
Returns the cumulative interest paid between two periods
|
CUMPRIN
Function
|
Returns the cumulative principal paid on a loan between two periods
|
DB function
|
Returns the depreciation of an asset for a specified period by using the fixed-declining balance method
|
DDB function
|
Returns the depreciation of an asset for a specified period by using the double-declining balance method or some other method that you specify
|
DISC function
|
Returns the discount rate for a security
|
DOLLARDE
Function
|
Converts a dollar price, expressed as a fraction, into a dollar price, expressed as a decimal number
|
DOLLARFR function
|
Converts a dollar price, expressed as a decimal number, into a dollar price, expressed as a fraction
|
DURATION function
|
Returns the annual duration of a security with periodic interest payments
|
EFFECT function
|
Returns the effective annual interest rate
|
FV function
|
Returns the future value of an investment
|
FVSCHEDULE function
|
Returns the future value of an initial principal after applying a series of compound interest rates
|
INTRATE function
|
Returns the interest rate for a fully invested security
|
IPMT function
|
Returns the interest payment for an investment for a given period
|
IRR function
|
Returns the internal rate of return for a series of cash flows
|
ISPMT function
|
Calculates the interest paid during a specific period of an investment
|
MDURATION function
|
Returns the Macauley modified duration for a security with an assumed par value of $100
|
MIRR function
|
Returns the internal rate of return where positive and negative cash flows are financed at different rates
|
NOMINAL function
|
Returns the annual nominal interest rate
|
NPER function
|
Returns the number of periods for an investment
|
NPV function
|
Returns the net present value of an investment based on a series of periodic cash flows and a discount rate
|
ODDFPRICE function
|
Returns the price per $100 face value of a security with an odd first period
|
ODDFYIELD function
|
Returns the yield of a security with an odd first period
|
ODDLPRICE function
|
Returns the price per $100 face value of a security with an odd last period
|
ODDLYIELD function
|
Returns the yield of a security with an odd last period
|
PMT function
|
Returns the periodic payment for an annuity
|
PPMT function
|
Returns the payment on the principal for an investment for a given period
|
PRICE function
|
Returns the price per $100 face value of a security that pays periodic interest
|
PRICEDISC function
|
Returns the price per $100 face value of a discounted security
|
PRICEMAT function
|
Returns the price per $100 face value of a security that pays interest at maturity
|
PV function
|
Returns the present value of an investment
|
RATE function
|
Returns the interest rate per period of an annuity
|
RECEIVED function
|
Returns the amount received at maturity for a fully invested security
|
SLN function
|
Returns the straight-line depreciation of an asset for one period
|
SYD function
|
Returns the sum-of-years' digits depreciation of an asset for a specified period
|
TBILLEQ function
|
Returns the bond-equivalent yield for a Treasury bill
|
TBILLPRICE function
|
Returns the price per $100 face value for a Treasury bill
|
TBILLYIELD function
|
Returns the yield for a Treasury bill
|
VDB function
|
Returns the depreciation of an asset for a specified or partial period by using a declining balance method
|
XIRR function
|
Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic
|
XNPV function
|
Returns the net present value for a schedule of cash flows that is not necessarily periodic
|
YIELD function
|
Returns the yield on a security that pays periodic interest
|
YIELDDISC function
|
Returns the annual yield for a discounted security; for example, a Treasury bill
|
YIELDMAT function
|
Returns the annual yield of a security that pays interest at maturity
|