Financial planning using Excel : forecasting planning and budgeting techniques / 2nd ed.

Contents 6
Preface 10
About the Author 12
Using Excel 2007 14
Part 1 16
Chapter 1 Spreadsheet Skills for all Types of Planning 18
Introduction 20
Spreadsheet 1: Getting started 21
Spreadsheet 2: Ownership and version 21
Spreadsheet 3: Formatting 22
Spreadsheet 4: Documentation 25
Spreadsheet 5: Minimising absolute values 27
Spreadsheet 6: Separating growth and cost factors 28
Spreadsheet 7: Optimising layout 30
Spreadsheet 8: Arithmetic cross-checks 32
Spreadsheet 9: Charts 33
Spreadsheet 10: Multiple sheets 33
Templates 34
Data input forms 36
Summary 37
Part 2 40
Chapter 2 Forecasting 42
Introduction 44
Approaches to forecasting 44
Chapter 3 Collecting and Examining the Data 48
Data collection 50
Using statistical measures 54
Summary 60
Chapter 4 Smoothing Techniques 62
Introduction 64
Moving averages 65
Weighted moving average 66
Adaptive filtering 69
Exponential smoothing 71
Summary 73
Chapter 5 Regression Analysis 74
Introduction 76
Calculating the least square line 80
Function vs command 83
The graphic approach 83
Standard error 84
Multiple linear regression 86
Summary 89
Chapter 6 Time Series Analysis 90
Introduction 92
Multiplicative time series analysis 93
A time series analysis model 94
Summary 99
Chapter 7 Expected Values 100
Introduction 102
A model for analysing expected values 102
Summary 107
Chapter 8 Selecting and Evaluating Forecasting Techniques 108
Introduction 110
Selecting the right technique 110
Accuracy and reliability 110
Charts 112
Statistical methods 112
Subjective methods 114
Summary 115
Part 3 116
Chapter 9 Business Planning 118
Introduction 120
Approaches to business planning 121
Summary 127
Chapter 10 Developing a Financial Plan 128
Introduction 130
The model 130
Getting started 132
Developing the Profit and Loss Account 132
Developing the Profit and Loss Appropriation Account 136
Developing the balance sheet 137
Funds flow statement 139
Ratio analysis 140
Cash flow statement 141
Summary 142
Chapter 11 Business Plans 144
Introduction 146
Capital investment appraisal 146
Developing a capital investment appraisal plan 148
Learning curve costing 154
Break-even analysis 157
Economic order quantities 159
Sales campaign appraisal 161
Summary 164
Chapter 12 What-if Analysis 166
Introduction 168
Three approaches to what-if analysis 169
Summary 179
Chapter 13 Pivot Tables 182
Introduction 184
Creating a Pivot Table in Excel 2003 184
Pivot Tables using Excel 2007 190
Summary 194
Chapter 14 Risk Analysis 196
Introduction 198
Preparing a plan for risk analysis 199
Incorporating the RAND function 201
The results worksheet 202
Frequency distribution 204
Using the risk analysis model 205
Summary 206
Part 4 208
Chapter 15 Budgeting 210
Introduction 212
Scope of budgeting 212
Benefits of budgeting 213
Different approaches to budgeting 213
Budget preparation 215
Spreadsheets for budgets 216
Summary 216
Chapter 16 A Spreadsheet Budgeting System 218
Introduction 220
Preparing the budget template 221
Preparing the actual template 221
Preparing the variance report template 223
Preparing the year-to-date report template 224
Summary 226
Chapter 17 Consolidating Data 228
Introduction 230
Using the CONSOLIDATE command 230
Summary 234
Index 236
A 238
B 238
C 238
D 238
E 238
F 238
G 239
K 239
L 239
M 239
P 239
R 239
S 239
T 239
W 239
Z 239