Financial Forecasting, Analysis and Modelling

Оглавление

Michael Samonas. Financial Forecasting, Analysis and Modelling

Preface

Acknowledgments

About the Author

Part One. Developing Corporate Finance Models

Chapter 1. Introduction

1.1 WHAT IS FINANCIAL MODELLING?

1.2 DEFINING THE INPUTS AND THE OUTPUTS OF A SIMPLE FINANCIAL MODEL

1.3 THE FINANCIAL MODELLING PROCESS OF MORE COMPLEX MODELS

1.3.1 Step 1: Defining the Problem the Model Will Solve: The Fundamental Business Question

1.3.2 Step 2: Specification of the Model

1.3.3 Step 3: Designing and Building the Model

1.3.4 Step 4: Checking the Model's Output

1.4 EXCEL AS A TOOL OF MODELLING: CAPABILITIES AND LIMITATIONS

BIBLIOGRAPHY AND REFERENCES

Chapter 2. A Short Primer in the Accounting of Financial Statements

2.1 THE ACCOUNTING EQUATION

2.2 THE BALANCE SHEET

2.3 THE INCOME STATEMENT

2.3.1 Cash Accounting Versus Accrual Accounting

2.4 THE CASH FLOW STATEMENT

2.4.1 Operating Activities

2.4.2 Investing Activities

2.4.3 Financing Activities

2.4.4 Income Flows and Cash Flows

2.4.5 Preparing the Statement of Cash Flows

2.5 THE ARTICULATION OF INCOME STATEMENT, BALANCE SHEET, AND CASH FLOW STATEMENTS

2.6 FINANCIAL STATEMENT ANALYSIS: RATIO ANALYSIS

2.6.1 Profitability Ratios

2.6.2 Liquidity Ratios

2.6.3 Solvency Ratios

2.6.4 Other Ratios

2.6.5 The Limitations of Financial Ratios

BIBLIOGRAPHY AND REFERENCES

Chapter 3. Financial Statement Modelling

3.1 INTRODUCTION – HOW FINANCIAL MODELS WORK

3.2 COLLECTING AND ANALYZING HISTORICAL DATA

3.3 SELECTING THE KEY FORECAST DRIVERS

3.4 MODELLING THE INCOME STATEMENT

3.5 MODELLING THE BALANCE SHEET

3.6 MODELLING INTEREST AND CIRCULAR REFERENCES

3.7 MODELLING THE CASH FLOW STATEMENT

BIBLIOGRAPHY AND REFERENCES

Chapter 4. Forecasting Performance

4.1 INTRODUCTION: DESIGNING A DASHBOARD-LIKE CONTROL PANEL

4.2 BASIC STATISTICAL METHODS USED FOR FORECASTING

4.3 FORECASTING SALES

4.3.1 Bottom-up Versus Top-down Forecasting

4.3.2 Forecasting Sales of Existing Products or Services

4.4 FORECASTING COSTS

4.5 FORECASTING CAPEX AND DEPRECIATION

4.5.1 Forecasting CAPEX and Depreciation for Existing Companies

4.6 FORECASTING WORKING CAPITAL AND FUNDING NEEDS

4.6.1 Forecasting Funding Needs

BIBLIOGRAPHY AND REFERENCES

Chapter 5. Business Valuation

5.1 VALUATION APPROACHES

5.2 STEPS FOR APPLYING THE DCF METHOD

5.3 REWRITING FINANCIAL STATEMENTS – CALCULATION OF FREE CASH FLOWS

5.4 CALCULATING THE WEIGHTED AVERAGE COST OF CAPITAL

5.4.1 Calculating the Weighted Average Cost of Capital of SteelCo

5.5 ESTIMATING THE TERMINAL VALUE

5.6 DCF SUMMARY – ENTERPRISE VALUE ADJUSTMENTS

BIBLIOGRAPHY AND REFERENCES

Part Two

Chapter 6. Using Sensitivity Analysis

6.1 INTRODUCTION

6.2 ONE-DIMENSIONAL AND 2-DIMENSIONAL SENSITIVITY ANALYSIS

6.3 CHOOSING THE VARIABLES TO CHANGE

6.4 MODELLING EXAMPLE

6.4.1 Selecting the Variables to Change

6.4.2 Assigning a Range of Values

6.4.3 Constructing the 2-dimensional Sensitivity Analysis Table

6.4.4 Interpreting the Results

BIBLIOGRAPHY AND REFERENCES

Chapter 7. Using Scenarios

7.1 INTRODUCTION

7.2 USING SCENARIO ANALYSIS WITH EXCEL'S SCENARIO MANAGER

7.2.1 Adding 2 More Scenarios

7.3 ALTERNATIVE WAYS TO CREATE SCENARIOS IN EXCEL

7.4 APPLYING SCENARIOS TO STEELCO'S CASE

7.4.1 Deciding on the Number of Scenarios and Input Variables under each Scenario

7.4.2 Deciding on the Output Variables

7.4.3 Assigning Values to the Input Variables under Each Scenario

7.4.4 Building the Scenarios in Excel's Scenario Manager

7.4.5 Interpreting the Results

BIBLIOGRAPHY AND REFERENCES

Chapter 8. Using Monte Carlo Simulation

8.1 INTRODUCTION

8.2 BUILDING UNCERTAINTY DIRECTLY INTO THE MODELLING PROCESS

8.3 PROBABILITIES, CUMULATIVE PROBABILITIES, AND FREQUENCY DISTRIBUTION CHARTS

8.4 MODELLING EXAMPLE

8.4.1 Identifying the Key Risk Variables

8.4.2 Choosing a Probability Distribution for Each Input Variable

8.4.3 Performing the Simulation Runs

8.4.3.1 The Simple VBA CODE

8.4.4 Creating a Histogram (Frequency Distribution Chart) in Excel

8.4.5 Interpreting the Results

8.4.6 Some Issues of Concern

REFERENCES AND BIBLIOGRAPHY

Appendix

1. WALKING THROUGH THE EXCEL MODEL PROVIDED WITH THE BOOK (STEELCO SA 4YR BUSINESS PLAN)

2. OTHER EXCEL FILES PROVIDED WITH THE BOOK

WILEY END USER LICENSE AGREEMENT

Отрывок из книги

Over the past several years, spreadsheet models have been the dominant vehicles for finance professionals to implement their financial knowledge. Moreover, in the aftermath of the recent financial crisis the need for experienced Financial Modelling professionals has steadily increased as organizations need to plan and adjust to the economic volatility and uncertainty. The level of risk in taking certain decisions needs to be projected using proper financial models and the alternative possible outcomes need to be analyzed. One benefit of this type of analysis is that it helps companies to be proactive instead of reactive. They can avoid or at least mitigate potential negative results that may stem from influences in their industry or within the business itself.

This book provides a step-by-step guide that takes the reader through the entire process of developing long-term projection plans using Excel. In addition, by making use of various tools (Excel's Scenario Manager, sensitivity analysis, and Monte Carlo simulation) it provides practical examples on how to apply risk and uncertainty to these projection plans. Although these projections are not guarantees they can help organizations to be better informed, and thereby provide peace of mind.

.....

Financial modelling is used, as we mentioned previously, in order to solve various problems. The first step of the process includes teams or individuals asking the right questions at the start of the problem-solving process. This is sometimes hard to believe as it often seems that people are trying to solve a problem before they have properly defined it. Asking the right questions helps break down the problem into simpler constituents.

For example the commercial manager of the company requests the financial analyst to present the impact on the bottom line results of the company of a New Product Development (NPD). Let us say that the costs of the whole NPD process are available and can be largely funded through government subsidy. In order to tackle the problem the financial analyst needs to ask the following questions:

.....