Microsoft Excel Forecasting & Data Analysis

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. 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