EBOOK - Financial Forecasting Analysis and Modelling - A Framework for Long-Term Forecasting (Michael Samonas)

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 Forecasting, Analysis and Modelling: A Framework for Long-Term Forecasting covers financial models in the area of financial statement simulation. It provides clear and concise explanations in each case for the implementation of the models using Excel. It is relevant to a variety of situations. At the most fundamental level, it can help:


PART ONE
Developing Corporate Finance Models  1
Chapter 1
Introduction 3
1.1  What is Financial Modelling?  3
1.2  Defining the Inputs and the Outputs of a Simple
Financial Model  6
1.3  The Financial Modelling Process of More Complex Models  9
1.3.1  Step 1: Defining the Problem the Model Will Solve:
The Fundamental Business Question  9
1.3.2  Step 2: Specification of the Model  10
1.3.3  Step 3: Designing and Building the Model  11
1.3.4  Step 4: Checking the Model’s Output  13
1.4  Excel as a Tool of Modelling: Capabilities and Limitations  13
Chapter 2
A Short Primer in the Accounting of Financial Statements  17
2.1 The Accounting Equation  17
2.2  The Balance Sheet  20
2.3 The Income Statement  23
2.3.1  Cash Accounting Versus Accrual Accounting  26
2.4  The Cash Flow Statement  26
2.4.1 Operating Activities  27
2.4.2 Investing Activities  27
Table of Contents
viii  TABLE OF CONTENTS
2.4.3 Financing Activities  27
2.4.4  Income Flows and Cash Flows  27
2.4.5  Preparing the Statement of Cash Flows  28
2.5  The Articulation of Income Statement, Balance
Sheet, and Cash Flow Statements  29
2.6  Financial Statement Analysis: Ratio Analysis  32
2.6.1 Profitability Ratios  35
2.6.2 Liquidity Ratios  37
2.6.3 Solvency Ratios  38
2.6.4 Other Ratios  39
2.6.5  The Limitations of Financial Ratios  40
Chapter 3
Financial Statement Modelling  43
3.1  Introduction – How Financial Models Work  43
3.2  Collecting and Analyzing Historical Data  47
3.3  Selecting the Key Forecast Drivers  53
3.4  Modelling the Income Statement  59
3.5  Modelling the Balance Sheet  63
3.6  Modelling Interest and Circular References  66
3.7  Modelling the Cash Flow Statement  69
Chapter 4
Forecasting Performance  75
4.1  Introduction: Designing a Dashboard-like Control Panel  75
4.2  Basic Statistical Methods Used for Forecasting  88
4.3  Forecasting Sales  93
4.3.1 Bottom-up Versus Top-down Forecasting  97
4.3.2  Forecasting Sales of Existing Products or Services  97
4.4  Forecasting Costs  99
4.5  Forecasting CAPEX and Depreciation  103
4.5.1  Forecasting CAPEX and Depreciation for Existing Companies  108
4.6  Forecasting Working Capital and Funding Needs  110
4.6.1  Forecasting Funding Needs  113
Chapter 5
Business Valuation  115
5.1 Valuation Approaches  115
5.2  Steps for Applying the DCF Method  120
5.3  Rewriting Financial Statements – Calculation of
Free Cash Flows  121
5.4  Calculating the Weighted Average Cost of Capital  124
5.4.1  Calculating the Weighted Average Cost of Capital of SteelCo  128
Table of Contents  ix
5.5 Estimating the Terminal Value  131
5.6  DCF Summary – Enterprise Value Adjustments  132
PART TWO
Planning for Uncertainty  137
Chapter 6
Using Sensitivity Analysis  139
6.1 Introduction  139
6.2  One-Dimensional and 2-Dimensional Sensitivity Analysis  140
6.3  Choosing the Variables to Change  145
6.4  Modelling Example  149
6.4.1  Selecting the Variables to Change  149
6.4.2  Assigning a Range of Values  149
6.4.3  Constructing the 2-dimensional Sensitivity Analysis Table  151
6.4.4  Interpreting the Results  153
Chapter 7
Using Scenarios  157
7.1 Introduction  157
7.2  Using Scenario Analysis with Excel’s Scenario Manager  158
7.2.1  Adding 2 More Scenarios  159
7.3  Alternative Ways to Create Scenarios in Excel  163
7.4  Applying Scenarios to SteelCo’s Case  167
7.4.1  Deciding on the Number of Scenarios and Input Variables
under each Scenario  167
7.4.2  Deciding on the Output Variables  168
7.4.3  Assigning Values to the Input Variables under Each Scenario  170
7.4.4  Building the Scenarios in Excel’s Scenario Manager  173
7.4.5  Interpreting the Results  176
Chapter 8
Using Monte Carlo Simulation  179
8.1 Introduction  179
8.2  Building Uncertainty Directly Into the Modelling Process  180
8.3  Probabilities, Cumulative Probabilities, and Frequency Distribution Charts  182
8.4  Modelling Example  187
8.4.1  Identifying the Key Risk Variables  188
8.4.2  Choosing a Probability Distribution for Each Input Variable  188
8.4.3  Performing the Simulation Runs  190
8.4.3.1  The Simple VBA CODE  192
8.4.4  Creating a Histogram (Frequency Distribution Chart) in Excel  195
8.4.5  Interpreting the Results  200
8.4.6  Some Issues of Concern  201
x  TABLE OF CONTENTS
Appendix  203
1.  Walking through the Excel Model Provided with the Book
(SteelCo SA 4yr Business Plan)  203
Introduction 203
Structure of the Model  204
2.  Other Excel Files Provided with the Book  205

LINK DOWNLOAD

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 Forecasting, Analysis and Modelling: A Framework for Long-Term Forecasting covers financial models in the area of financial statement simulation. It provides clear and concise explanations in each case for the implementation of the models using Excel. It is relevant to a variety of situations. At the most fundamental level, it can help:


PART ONE
Developing Corporate Finance Models  1
Chapter 1
Introduction 3
1.1  What is Financial Modelling?  3
1.2  Defining the Inputs and the Outputs of a Simple
Financial Model  6
1.3  The Financial Modelling Process of More Complex Models  9
1.3.1  Step 1: Defining the Problem the Model Will Solve:
The Fundamental Business Question  9
1.3.2  Step 2: Specification of the Model  10
1.3.3  Step 3: Designing and Building the Model  11
1.3.4  Step 4: Checking the Model’s Output  13
1.4  Excel as a Tool of Modelling: Capabilities and Limitations  13
Chapter 2
A Short Primer in the Accounting of Financial Statements  17
2.1 The Accounting Equation  17
2.2  The Balance Sheet  20
2.3 The Income Statement  23
2.3.1  Cash Accounting Versus Accrual Accounting  26
2.4  The Cash Flow Statement  26
2.4.1 Operating Activities  27
2.4.2 Investing Activities  27
Table of Contents
viii  TABLE OF CONTENTS
2.4.3 Financing Activities  27
2.4.4  Income Flows and Cash Flows  27
2.4.5  Preparing the Statement of Cash Flows  28
2.5  The Articulation of Income Statement, Balance
Sheet, and Cash Flow Statements  29
2.6  Financial Statement Analysis: Ratio Analysis  32
2.6.1 Profitability Ratios  35
2.6.2 Liquidity Ratios  37
2.6.3 Solvency Ratios  38
2.6.4 Other Ratios  39
2.6.5  The Limitations of Financial Ratios  40
Chapter 3
Financial Statement Modelling  43
3.1  Introduction – How Financial Models Work  43
3.2  Collecting and Analyzing Historical Data  47
3.3  Selecting the Key Forecast Drivers  53
3.4  Modelling the Income Statement  59
3.5  Modelling the Balance Sheet  63
3.6  Modelling Interest and Circular References  66
3.7  Modelling the Cash Flow Statement  69
Chapter 4
Forecasting Performance  75
4.1  Introduction: Designing a Dashboard-like Control Panel  75
4.2  Basic Statistical Methods Used for Forecasting  88
4.3  Forecasting Sales  93
4.3.1 Bottom-up Versus Top-down Forecasting  97
4.3.2  Forecasting Sales of Existing Products or Services  97
4.4  Forecasting Costs  99
4.5  Forecasting CAPEX and Depreciation  103
4.5.1  Forecasting CAPEX and Depreciation for Existing Companies  108
4.6  Forecasting Working Capital and Funding Needs  110
4.6.1  Forecasting Funding Needs  113
Chapter 5
Business Valuation  115
5.1 Valuation Approaches  115
5.2  Steps for Applying the DCF Method  120
5.3  Rewriting Financial Statements – Calculation of
Free Cash Flows  121
5.4  Calculating the Weighted Average Cost of Capital  124
5.4.1  Calculating the Weighted Average Cost of Capital of SteelCo  128
Table of Contents  ix
5.5 Estimating the Terminal Value  131
5.6  DCF Summary – Enterprise Value Adjustments  132
PART TWO
Planning for Uncertainty  137
Chapter 6
Using Sensitivity Analysis  139
6.1 Introduction  139
6.2  One-Dimensional and 2-Dimensional Sensitivity Analysis  140
6.3  Choosing the Variables to Change  145
6.4  Modelling Example  149
6.4.1  Selecting the Variables to Change  149
6.4.2  Assigning a Range of Values  149
6.4.3  Constructing the 2-dimensional Sensitivity Analysis Table  151
6.4.4  Interpreting the Results  153
Chapter 7
Using Scenarios  157
7.1 Introduction  157
7.2  Using Scenario Analysis with Excel’s Scenario Manager  158
7.2.1  Adding 2 More Scenarios  159
7.3  Alternative Ways to Create Scenarios in Excel  163
7.4  Applying Scenarios to SteelCo’s Case  167
7.4.1  Deciding on the Number of Scenarios and Input Variables
under each Scenario  167
7.4.2  Deciding on the Output Variables  168
7.4.3  Assigning Values to the Input Variables under Each Scenario  170
7.4.4  Building the Scenarios in Excel’s Scenario Manager  173
7.4.5  Interpreting the Results  176
Chapter 8
Using Monte Carlo Simulation  179
8.1 Introduction  179
8.2  Building Uncertainty Directly Into the Modelling Process  180
8.3  Probabilities, Cumulative Probabilities, and Frequency Distribution Charts  182
8.4  Modelling Example  187
8.4.1  Identifying the Key Risk Variables  188
8.4.2  Choosing a Probability Distribution for Each Input Variable  188
8.4.3  Performing the Simulation Runs  190
8.4.3.1  The Simple VBA CODE  192
8.4.4  Creating a Histogram (Frequency Distribution Chart) in Excel  195
8.4.5  Interpreting the Results  200
8.4.6  Some Issues of Concern  201
x  TABLE OF CONTENTS
Appendix  203
1.  Walking through the Excel Model Provided with the Book
(SteelCo SA 4yr Business Plan)  203
Introduction 203
Structure of the Model  204
2.  Other Excel Files Provided with the Book  205

LINK DOWNLOAD

Chuyên mục: C. Bài giảng C. Bài giảng chuyên ngành Tài chính - Ngân hàng (Finance & Banking) C. Bài giảng kinh tế