Module 1: Financial calculations: Applications
The basic structure of functions
Time value of money functions
Present value and net current value
The internal rate of return and Multiple IRR (MIRR)
Using NVP, XNPV, and XIRR
Amortization of loan schedule
Effective yields and returns
SUMIFs, COUNTIFs, and SUMPRODUCTs
IF and nested IFs
Conditional formatting
VLookup and HLookup
Data table & sensitivity analysis
Module 2: Corporate financial analysis
Profit and loss versus balance sheet analysis
Comparable company analysis
Building block analysis
Vertical, trend, and horizontal analysis
Liquidity
Current, quick, and cash ratios
Asset management and activity
AR and AP Turnover
Cash conversion cycle
Asset turnover
Solvency, leverage, and gearing
Debt, equity, and times interest earned rates
Assessing profitability management
Profit margin, gross margin, return on assets, return on investment
Market and valuation
Price-earnings and earnings per share ratios
Modeling the DuPont identity
The three-step models
Using keyboard shortcuts
Absolute and relative cell referencing
Module 3: Financial Modeling and Valuation
Overview of Financial Modeling
FAST standards for financial modeling
Structuring the input sheet and structuring time sheet using timing flags
Modeling for assumptions & inputs
Business Structure & revenue modeling
Modeling for & projecting operating expenses
Projecting key schedules including debt/interest, depreciation, working capital and equity
Stepwise projection of income statement, balance sheet, and cash flows
Ratio analysis and common size analysis
Computing cost of equity and WACC
Estimating Terminal Value, DCF Valuation
Trading Comps Valuation (P/E, P/B, EV/EBITDA, etc.)
Scenario analysis
Macros and VBA
Summarization & presentation of the final model
Form controls for scenario analysis
Module 4: Calculating cost of capital and capital structure
The Gordon, dividend model
Supernormal growth
Calculating the cost of equity and value of debt
Computing the expected return on the market
Computing weighted average cost of capital
Module 5: Financial statement modeling
How financial models work
Modeling income statement
Modeling balance sheet
Free Cash Flow measurement (FCF)
Using FCF to value the firm and its equity
Sensitivity analysis
Discounted cash flow analysis
Developing an integrated financial model
Module 6: Model construction techniques
Data tables design
Assumptions and input variable rules
Array functions and formulas
Spinner data modeling
List box data modeling
Option box data modeling
Acquiring and updating data from the text, access database, SQL, and the web
Module 7: Advanced Functions
Working with large datasets (Sorting, Subtotals, Filtering)
Analyzing Data using Pivot Tables
Goal Seek
Charting using Excel
Preparing Final accounts from Trial Balance using Pivot Tables
Financial Forecasting
Income Statement (Revenue, Expenses, etc.)
Balance Sheet (Working Capital, Fixed Asset Schedule, etc.)
Cash Flow Statement