Microsoft Excel Forecasting & Data Analysis

✍ Scribed by Sheik, Ahmed

• Year: 2020
• Tongue: English
• Leaves: 164
• Category: Library

✦ Synopsis

This Microsoft Forecasting & Data Analysis book is aimed at people who want to expand their knowledge into forecasting and more specialized analytical functionality offered by Microsoft Excel.

✦ Table of Contents

CONTENTS
CONTENTS...................................................................................................................................... 2
QUICK REFERENCE: EXCEL SHORTCUTS ................................................................................. 6
CTRL combination shortcut keys................................................................................................... 6
Function keys ................................................................................................................................ 8
Other useful shortcut keys.............................................................................................................. 9
UNIT 1 - FORECASTING.............................................................................................................. 12
Concept and Terms...................................................................................................................... 13
Linear Regression........................................................................................................................ 14
The Forecast function.............................................................................................................. 14
The Trend function.................................................................................................................. 19
The Slope and Intercept Function............................................................................................ 22
Exponential Regression ............................................................................................................... 26
The Growth function ............................................................................................................... 27
Exponential Smoothing................................................................................................................ 30
The Data Analysis Tool Exponential Smoothing .......................................................................... 32
Naïve forecasting......................................................................................................................... 35
Moving Average.......................................................................................................................... 36
Weighted Moving Average .......................................................................................................... 37
The Data Analysis tool Moving Average ..................................................................................... 39
Seasonal Forecasting ................................................................................................................... 42
Exercises..................................................................................................................................... 46
Exercise 1 Linear Regression .................................................................................................. 46
Exercise 2 Exponential Regression.......................................................................................... 47
Exercise 3 Exponential Smoothing forecasting ........................................................................ 48
Exercise 4 Naïve forecasting and Moving Average ................................................................. 49
Exercise 5 Seasonal forecasting .............................................................................................. 50
UNIT 2 - MEASURING FORECAST ACCURACY....................................................................... 51
Concepts and terms...................................................................................................................... 52
Calculate error/deviation.............................................................................................................. 52
Calculate absolute error/deviation ................................................................................................ 53
Calculate percentage error/deviation ............................................................................................ 55
Calculate absolute percentage error/deviation .............................................................................. 56
Calculate square error.................................................................................................................. 57
Calculate standard error............................................................................................................... 58
Calculate MAD or MAE (Mean Absolute Deviation or Mean Absolute Error) ............................. 61
Calculate MSQ (Mean Square Error) ........................................................................................... 62
Calculate MPE (Mean Percentage Error)...................................................................................... 62
Calculate MAPE (Mean Absolute Percentage Error): ................................................................... 63
Calculate TSE (Tracking Signal Error)......................................................................................... 64
Exercises..................................................................................................................................... 66
Exercise 1 Error/deviation ...................................................................................................... 66
Exercise 2 Mean errors/deviation............................................................................................ 67
UNIT 3 – USING THE SOLVER TO OPTIMISE FORECASTS..................................................... 68
Concept and Terms...................................................................................................................... 69
Installing the Solver..................................................................................................................... 70
Use the Solver to optimise exponential smoothing forecasts......................................................... 71
Use the Solver to optimise weighted moving average forecasts .................................................... 73
Use the Solver to optimise seasonal forecasts............................................................................... 75
Exercises..................................................................................................................................... 78
Exercise 1 Optimising exponential smoothing forecasts using the Solver ................................ 78
Exercise 2 Optimising weighted 3 periods moving average using the Solver ........................... 79
Exercise 3 Optimising seasonal forecasts using the Solver....................................................... 80
UNIT 4 - SHOWING TRENDS AND FORECASTS USING CHARTS.......................................... 81
Concepts and terms...................................................................................................................... 82
Choose the right chart type .......................................................................................................... 82
Create Trendlines ........................................................................................................................ 84
Choosing the best trendline for your data ..................................................................................... 87
Visualise forecasts and forecast errors in a chart .......................................................................... 89
Exercises..................................................................................................................................... 94
Exercise 1 Adding trendlines.................................................................................................. 94
Exercise 2 Trendline types...................................................................................................... 95
Exercise 3 Visualising forecasts and errors............................................................................ 96
UNIT 5 – COMPARING FORECASTING METHODS AND MODELS ........................................ 97
Concept and Terms...................................................................................................................... 98
UNIT 6 – FORECASTING USING WHAT-IF ANALYSIS.......................................................... 100
Concepts and terms.................................................................................................................... 101
The Scenarios Manager ............................................................................................................. 101
the Goal Seek tool ..................................................................................................................... 106
the Data Table tool .................................................................................................................... 107
Exercises................................................................................................................................... 110
Exercise 1 Forecasting using scenarios................................................................................ 110
Exercise 2 Forecasting using goal seek ................................................................................ 111
Exercise 3 Forecasting using data tables.............................................................................. 112
UNIT 7 - CORRELATION COEFFICIENT.................................................................................. 113
Concepts and terms.................................................................................................................... 114
The Data Analysis Tool Correlation........................................................................................... 116
Create a scatter chart to Display Correlation Coefficient ............................................................ 119
EXERCISE ............................................................................................................................... 122
Using Correl function and the data analysis tool Correlation ................................................ 122
UNIT 8 – BREAK-EVEN ANALYSIS.......................................................................................... 123
Concepts and terms.................................................................................................................... 124
Calculate Break-even................................................................................................................. 124
Visualise break-even using scatter chart..................................................................................... 126
Use Scenarios and the goal seek tool to calculate break-even ..................................................... 130
Use the Solver tool to calculate break-even ................................................................................ 130
EXERCISE ............................................................................................................................... 131
Calculating break-even ......................................................................................................... 131
UNIT 9 – DATA ANALYSIS TOOLS.......................................................................................... 132
Concepts and terms.................................................................................................................... 133
Descriptive Statistics ................................................................................................................. 133
Histogram.................................................................................................................................. 135
Regression................................................................................................................................. 138
Sampling ................................................................................................................................... 141
Rank & Percentile ..................................................................................................................... 142
APPENDIX A – ADDING DATA ANALYSIS TOOLPAK TO EXCEL....................................... 144
Installing Data analysis toolpak ................................................................................................. 144
APPENDIX B – TEXT FUNCTIONS ........................................................................................... 147
Concept and Terms.................................................................................................................... 148
Use the Right, Left, and Mid functions....................................................................................... 148
Use the Concatenate function..................................................................................................... 153
Use the Len functions................................................................................................................ 155
Use the Find function................................................................................................................. 156
Use nested text functions ........................................................................................................... 158
INDEX.......................................................................................................................................... 162

✦ Subjects

Microsoft Excel for Forecasting & Data Analysis