Using Excel to Solve Financial ProblemsThis one-day course shows students how Excel’s™ advanced capabilities are used by financial institutions to solve a wide variety of problems. Students learn how Excel's™ powerful features - including goal seek, solver, specialized financial functions and statistical functions, charts, macros, scenario manager, data tables and pivot tables – greatly facilitate the solution of many financial problems, such as discounted cash flow (DCF) evaluation, portfolio optimization, pro-forma projections, stock screening, regression hedging, sensitivity analysis, scenario analysis and simulation-based forecasting. Students learn by building spreadsheets that use Excel’s™ advanced features to solve real-world financial problems. Students are also introduced to third-party add-ins, such as TreePlan and Crystal Ball, which further enhance Excel’s™ problem-solving abilities. This is not a course on Excel™ tips, tricks and shortcuts. |
|
 |
|
 |
|
 |
| Anyone in the financial industry who uses Excel™. |
 |
| No advance preparation required. |
 |
Students will be able to:- Apply various Excel™ tools, including: goal seek, solver, specialized financial and statistical functions, charts, macros, scenario manager, data tables, and pivot tables
- Create spreadsheets that solve these financial problems: discounted cash flow (DCF) evaluation of bonds’ present value and yield, portfolio optimization, pro-forma projections, stock screening, regression hedging, sensitivity analysis and scenario analysis, and simulation-based forecasting
|
 |
| Attendees must have working knowledge of Excel™, and an elementary understanding of statistics, accounting and finance. |
 |
| Financial Modeling for Corporate Finance - EveningIntroduction to Simulation for Financial Professionals Advanced Excel Charting & Graphing Techniques & PowerPoint IntegrationAdvanced Valuation Modeling - Fundamental & Relative Valuation |
 |
Introduction to Excel Functions, Add-ins and Macros- Distinctions between functions, add-ins and macros
- When to use each feature
- Best practices for creating financial models with Excel
Model I: Evaluating Individual Bonds and Stocks- Using IRR, NPV, and Yield to evaluate bonds and corporate projects
- Using Excel’s statistical functions to compute stock returns, correlations, and betas
- Creating regression hedges with Excel’s multiple regression add-in
Model II: Portfolio Optimization- Using Goal Seek and Solver to build optimal portfolios for known risk tolerances
- Creating an efficient frontier array and chart with Excel’s data tables
Model III: Stock Screening- Analyzing relationships among historical stock prices, earnings and growth rates
- Using pivot tables to indentify undervalued stocks, as measured by PE and PEG ratios
Model IV: Pro-Forma Projections- Creating pro-forma projections of balance sheets and income statements
- Using Excel’s Match function and Solver add-in to build accurate projections
Model V: Pro-Forma Projection Analysis- Using Scenario Manager to perform sensitivity analysis, create Tornado charts and perform scenario analysis
- Create simulation-based projections with Excel’s probability functions and macros
|
|
 |
| Clients who register for this course will receive a complimentary 4-month subscription to FT.com. The Financial Times is the world's most respected financial newspaper, providing a broad assessment on finance, business and the industrial sector. The move to the electronic version follows an ongoing review of our environmental responsibilities as a global business and as part of the Pearson group. FT.com also has features that are not available in hard copy, such as: Special Reports, Alphaville, editor blogs, education sections and much more! Subscriptions will start within 6-8 weeks of the start of class and are limited to one subscription per client. (Please note: as of May 1, 2011, the electronic subscription replaces the hard-copy 3-month Financial Times subscription.) |
Lunch is included for all students taking day classes. |
|
|
]
