Detailed Course Curriculum
Financial modelling is one of the most powerful and widely used concepts available to senior level management for improving the odds of making good business decisions. Further, the increase of computational power of personal computers in the recent few years has brought the advantage of fairly sophisticated financial models into the reach of most businesses. Unfortunately the low cost and ready availability software has made it all too easy to incorrectly apply sophisticated techniques. This seminar provides business practitioners an overview of some major issues in financial modeling using Excel. For in-depth understanding of the applicable concepts and tools, and for further enhancement of their professional skills the participants are encouraged to attend the more advanced Data & Decision Modeling courses.
Focus 1: Financial Statements and Valuation Modeling
Modeling allows senior management to visualize the effect of strategic decisions on cash flow and the financial statements. This is as an effective tool for:
The emphasis of the seminar falls on the following topics:
• evaluating investment alternatives;
The seminar will provide detailed examples of using Excel in a variety of real business situations.
• identifying the sustainable growth rate;
• identifying the amount of outside capital needed for implementing the strategic plan;
• providing a standard for evaluating company performance;
• providing a benchmark for analyzing opportunities as they arise.
• Create pro forma financial statements and cash budgets
Focus 2: Linear Programming and Integer Programming Optimization Methods
• Conduct scenario and sensitivity analysis - including the use of Monte Carlo simulation
• Employ Fundamental (DCF) Valuation and Relative Valuation Approaches for Company Analysis
• Capital Budgeting - including purchase vs. lease decision
Using Excel and the Solver add-in it is possible to create solutions for a wide range of business problems. These solutions illustrate the best way to allocate scarce resources. The resources may be raw materials, machine time or people time, money, or anything else in limited supply. By creating an optimization model and using Solver to find optimal solutions and generate reports, the analyst can discover ways to reduce costs, increase profits, or improve quality or response time.
The course is designed to address the following issues:
• What is Solver good for?
Linear Programming Optimization
• What kind of solution can I expect?
• How can I model a real business problem? How can I reach optimal decision with the help of user-friendly application of Linear Programming and Integer Programming techniques on Excel?
• How do I formalize the problem and set an appropriate model?
• How do I interpret the results of model outputs?
• Linear Programming Optimization - Fundamentals: Simple Optimization Problem. Objective Function, Constraints and Analytical Statement. Graphical Solution.
• Linear Programming Optimization
(1) "Cash Management - Portfolio Selection" - a small case. Computer Solution and interpretation of the computer output. Sensitivity Analysis: Slack and Surplus Variables. Sensitivity Analysis: Right-hand Sides, Shadow Prices and Dual Values, Reduced Cost, Region of Feasibility and Region of Optimality.
(2) "Production scheduling-based Financial Mix Selection" - the alternate optima case
• Integer Programming fundamentals. Types of Integer Programming Models
Focus 3: Financial forecasting with EXCEL
• Solutions of the home assignment examples. Discussion.
• Integer Programming - continue. Capital Budgeting -small portfolio NPV maximization example.
Problem setting and graphical solution. Computer solution - variations and interpretations.
Fundamentals of Multiple (Linear) Regression Analysis
• Statistic concepts for applied econometrics and forecasting. Underlying distribution of a data set and the choice of appropriate test for hypothesis testing.
Multiple Regression: Limitations, Problems and Remedies:
• Limitations of Regression Analysis. Multicollinearity, Heteroskedasticity and Serial Correlation: identification, consequences and remedies.
Time Series Analysis
• Fundamentals in Time Series Analysis: Univariate Linear Models (Trend, Seasonal lags, AR, MA and ARMA models).
Elements of Forecasting
• A survey of modern business and economics forecasting methods.
• Multiple regression fundamentals - a summary. ANOVA and stepwise regression analysis.
• Forecasts distributions and tests for accuracy of the forecasts.
• Forecast comparisons. Extensions.