Contents
Preface vii
About the Author ix
Part 1 1
1 Forecasting 3
Introduction 5
Approaches to forecasting 5
2 Collecting and Examining the Data 9
Data collection 11
Using statistical measures 15
Summary 21
3 Smoothing Techniques 23
Introduction 25
Moving averages 26
Weighted moving average 27
Adaptive filtering 31
Exponential smoothing 32
Summary 35
4 Regression Analysis 37
Introduction 39
Calculating the least square line 43
Function vs command 46
The graphic approach 46
Standard error 47
Multiple linear regression 49
Summary 52
5 Time Series Analysis 53
Introduction 55
Multiplicative time series analysis 56
A time series analysis model 57
Summary 62

6 Expected Values 63
Introduction 65
A model for analysing expected values 65
Summary 70
7 Selecting and Evaluating Forecasting Techniques 71
Introduction 73
Selecting the right technique 73
Accuracy and reliability 75
Charts 75
Statistical methods 75
Subjective methods 77
Summary 78
Part 2 79
8 Business Planning 81
Introduction 83
Approaches to business planning 84
Summary 90
9 Spreadsheet Skills for all Types of Planning 91
Introduction 93
Spreadsheet 1: Getting started 94
Spreadsheet 2: Ownership and version 95
Spreadsheet 3: Formatting 96
Spreadsheet 4: Documentation 99
Spreadsheet 5: Minimising absolute values 100
Spreadsheet 6: Separating growth and cost factors 101
Spreadsheet 7: Optimizing layout 103
Spreadsheet 8: Arithmetic cross-checks 105
Spreadsheet 9: Charts 106
Spreadsheet 10: Mutiple sheets 106
Templates 108
Data input forms 109
Summary 110
10 Developing a Financial Plan 113
Introduction 115
The model 115
Getting started 116

Developing the Profit and Loss Account 117
Developing the Profit and Loss Appropriation Account 121
Developing the balance sheet 122
Funds flow statement 124
Ratio analysis 125
Cash flow statement 125
Summary 126
11 Business Plans 129
Introduction 131
Capital investment appraisal 131
Developing a capital investment appraisal plan 133
Learning curve costing 139
Break-even analysis 142
Economic order quantities (EOQ) 144
Sales campaign appraisal 147
Summary 149
12 What-if Analysis 151
Introduction 153
Three approaches to what-if analysis 154
Manual what-if analysis on opening assumptions 154
Data tables 156
Backward iteration or goal seeking 161
Summary 165
13 Risk Analysis 167
Introduction 169
Preparing a plan for risk analysis 170
Incorporating the RAND function 172
The results worksheet 173
Frequency distribution 175
Using the risk analysis model 176
Summary 177
Part 3 179
14 Budgeting 181
Introduction 183
Scope of budgeting 183
Benefits of budgeting 184
Different approaches to budgeting 184

Budget preparation 185
Spreadsheets for budgets 186
Summary 186
15 A Spreadsheet Budgeting System 187
Introduction 189
Preparing the budget template 190
Preparing the actual template 191
Preparing the variance report template 192
Preparing the year-to-date report template 193
Summary 195
16 Consolidating Data 197
Introduction 199
Using the Consolidate command 199
Summary 202
Index 203