What is a Financial Model?
A financial model is a mathematical tool that is built-in spreadsheet software such as MS Excel to represent the performance of a financial asset, portfolio or a business. This is done by forecasting financial statements of the company like income statement balance sheet and cash flow statements, To begin with historical data is inputted from the financials of the company and assumptions and drivers of the business are identified which are used to forecast the line items in the forecasting period.
Once the forecast is complete, advance models of valuation are made on top of these models. Valuation Models that can be made include Discounted Cash flow(DCF) Models that involve forecasting the free cash flows of the business and estimating Weighted Average Cost of Capital (WACC). In addition PE multiple or Dividend Discount and Valuation can be undertaken. Comparable Company Analysis, Comparable Transaction Analysis can be carried upon. Financial Modeling can also be used in the analysis of standalone projects and the final result of the analysis is used for decision making by the prospective investors as to whether to invest in the company or not or when to exit from the company. At the corporate level the decision could be whether to go ahead with a project or not or at what price one company should acquire another company. Complex Transactions like leveraged buy outs can be analyzed with the help of a Financial model. While working in the company the financial model is used by Analysts/ Modelers to help the management take important decisions about acquisitions, divestitures and capital allocations. Financial modeling is especially useful in Investment Banking, Equity Research & Analysis, Corporate Strategy and Development and Accounting.
How to create Financial model using Excel?
If you want to create a financial model using excel, the knowledge of equity research is very important. The best way is to take an established company's historical financials, built a flat-line model into the future, and calculate the present value per share. By this, you can compare closely to the current share price or the target price of equity research. You should also read a Company's Cost of Equity Analysis and Sensitivity/ Scenario analysis. You can also use free templates available online to kickstart your learning.
Professionals who use financial modeling, use simple tricks in excel to work much faster such as limit the use of a mouse. The keyboard shortcuts are much faster. Since Financial Model built by you is going to be used by other users, the look and feel of the model is important and hence formatting is very important for a financial model, there are some industry norms on formatting like You can use blue fonts to highlight hard codes, the rest can stay black. To format it neatly, you can also use shading cells and using borders. Financial models are used in the income statement, balance sheet, cash flow statement, valuation, and supporting schedules. It is essential to structure a financial model in an easy to follow the design. It means that you should build a model in such a way that it does not create confusion.