Spreadsheet Skills For Planning, Forecasting and Budgeting

Introduction

A thorough grasp of the principles of financial analysis is a key competency needed for effective management of a business. This 10 Module interactive program goes far beyond the typical financial analysis program. It uses both the principles of analysis and the power of Excel® to identify the strategic drivers that can dramatically enhance the shareholder value of your organisation. Using Excel®, delegates will learn how to master the tools and techniques it provides to apply the latest financial analysis techniques, understand what the numbers are revealing, and be better able to make strategic and tactical decisions and predict the outcome of these decisions.

Using Excel®, delegates will develop from the basics of strategic financial planning to using the tools of finance such as the time value of money and determining key performance indicators in order to manage the drivers of business success. The program proceeds from basic financial analysis to more advanced techniques for determining the cost of capital, methods of projecting the benefit of capital projects, Also, using Excel®, delegates will learn how to evaluate organizations with which delegates are planning to do business; determine if they are able to provide strategic strength to your company. Participants will be provided Excel® templates for use during this session, and when they return to their organizations. The power of Excel enables learning to be taken to a considerably higher level than that which would be possible using only manual methods. They will thus be enriched with the ability to use spreadsheet techniques for both analysis and financial modeling.

Objectives

On completion of this module delegates will be able to:

• Benchmark corporate performance against peer groups and industry statistics.
• Analyze a company’s annual report identifying key performance indicators in order to understand what the numbers are really saying and make projections of future performance.
• Understand the theory of interest and the time value of money and its applications.
• Use the powerful tools in Excel® such as the Financial and Statistical functions (¦), the Data Analysis toolbox, and sensitivity analysis tools such as the Goal Seek command.
• Develop an Economic Value-Added model to see the organization from the shareholders’ perspective.
• Project the benefit of capital investment.
• Analyze the risks and benefits of proposed acquisitions, valuation of target companies, and issues relating to post-acquisition integration.
• Measure management's ability to convert the company’s investment in assets into shareholder return.
• Learn how to use Excel® tools such as Solver, Goal Seeker, Scenario, and Spreadsheet Auditor
• Use Pivot Tables to quickly develop reports.
• Analyze time-series data and develop relationships using exponential smoothing and regression analysis techniques.
• Use Excel® with more confidence and flexibility, thus saving large amounts of time and enhancing productivity.

Training Methodology

This is an interactive course. There will be open question and answer sessions, regular group exercises and activities, videos, case studies, and presentations on best practices. Participants will have the opportunity to share with the facilitator and other participants on what works well and not so well for them, as well as work on issues from their own organizations. The online course is conducted online using MS-Teams/ClickMeeting.

Who Should Attend?

All financial professionals, financial management, department heads, internal auditors, project managers, and those responsible for ensuring the effective use and management of corporate resources. Also, sales and marketing professionals, R&D professionals, and anyone who needs to understand the short and long-term financial impact of key decisions. Participants should have a basic understanding of financial statements and may bring their own laptop computer with a full install of Excel®, including the complete “Toolbox.” Access to a desktop PC will be made available to all participants.

Course Outline

Module 1 - The International Business Environment

• Overview of the strategic and operational aspects of a global business entity
• The Organisational Planning Model
• The Product/Decision/Information Cycle
• The Objectives of Financial Analysis
• Creating wealth by adding value
• Basic metrics of wealth creation and financial performance

Module 2 - The Financial Reporting Process and the Organisational Planning Cycle

• Review of financial statements using International GAAP
• Different asset concentration in industries and the relative impact on Return
• The picture presented by the financial statements relative to past operations
• The inter-relationship among financial statements, strategic decisions, and operations
• GAAP vs. Cash – How to spot accounting irregularities in financial statements
• Focus provided by NOPAT and ROIC

Module 3 - Analyzing the Annual Report

• Ratio Analysis – The heart of Financial Analysis
• Use Excel® templates to calculate and interpret liquidity, leverage, and profitability ratios
• Interpret the results of ratio analysis from an accrual accounting perspective
• The inter-relationship between the DuPont Formula and EVA®
• How to use the Altman Z-Score
• Use various investment surveys to benchmark the results of financial analysis

Module 4 - Creating Shareholder Value

• What constitutes “Shareholder Value?”
• Finding the cost of equity
• Determine the Cost of Debt, Preferred Equity, and Common Equity
• Calculate Weighted Average Cost of Capital (WACC)
• Develop the EVA© Model
• Impact of Acquisitions on WACC

Module 5 - The Time Value of Money

• The impact time has on the value of money.
• Understand the various interest calculations
• Using WACC and ROIC as benchmarks
• Use Excel®, to determine Present Value, Future Value, Net Present Value, Internal Rate of Return, Modified Internal Rate of Return
• Using IRR as a basis for capital project evaluation

Module 6 - Steps in Building Financial Models

• Situations that require models
• Models and Shareholder Value (EVA®)
• Identification of Forecast Validation Criterion
• Determination of Model and Forecast Horizons
• The recognition of risk in forecasts
• The Role of Assumptions in Financial Forecasting

Module 7 - Evaluating Capital Project Proposals

• Identify the various types of capital projects
• Discuss the capital project evaluation process
• Determining the initial and subsequent capital project cash flows
• Development of the “Hurdle Rate” for capital projects
• Discuss the use of “Terminal Value” in evaluating capital projects
• Use Excel® to evaluate capital projects by applying NPV, IRR, and Discounted Payback models

Module 8 - Effective Management of Historical Data Using Excel®

• Understanding the Approaches Used to Build Financial Forecasting Models
• Recognizing the Basic Patterns Inherent in Historical Data
• Using the Exploratory Data Analysis Tools Available in Excel®
• Key Factors in Determining the Proper Time Horizon to Choose for Your Model
• Determining Degrees of Reliability in Model Projections
• Selecting the Degree of Robustness and Sensitivity of the Model
• Understanding and Applying Selected Modeling Techniques

Module 9 - Use of Time Series Analysis

• Development of Time Series Models using histograms, moving averages, exponential smoothing, and regression analysis
• Using Regression as a predictor and estimator
• Mastering the use of Exponential Smoothing as a Data Analysis tool
• Validation of Time Series Analysis
• Appreciate the Meaning and Importance of Sensitivity Analysis
• Developing “What-if” Scenarios in Your Financial or Operational Models
• Using the Excel tools “Scenario and Goal Seek”

Module 10: Workshops & Cases