![]() ![]() Then, in cell F10, we calculated the projected value for period 5 by adding the Slope (cell F8) and the Intercept (cell F9) multiplied by 5 (cell F5) to derive the fifth period amount. In cells F8 and F9, we entered the formulas =SLOPE(B6:E6,B5:E5) and =INTERCEPT(B6:E6,B5:E5), respectively. Starting with the same sales numbers used above, we replaced the date labels in cells B5 through F5 with the numbers 1 through 4 (because these numbered data points are essential for the SLOPE and INTERCEPT formulas to work properly). To prove the Fill Handle's accuracy, these same projected numbers can also be calculated mathematically using Excel's SLOPE and INTERCEPT functions, as follows. Image 3: Excel scatter chart and trendline This scatter chart and trendline visually suggest how Excel's Fill Handle tool works - and how the actual historical data are used to project the future data points. The red data points correspond to the future values calculated in cells F7 through H7 in Image 2. To depict the process further, I have extended the linear line and added data points at intervals equivalent to the actual data intervals (as illustrated by the red dotted line, data points, and interval arrows in Image 3). This resulting blue trendline represents the one true linear line that dissects the data points in such a manner that when all the distances from each data point to the trend line are squared and added together, they produce the least amount (hence, the least-squares method). Next, right-click any one of the blue data points, and from the resulting pop-up menu, select Add Trendline (the blue dotted line in Image 3). To do this, highlight the actual data cells (cells B7 through E7 in this example) and create a scatter chart by selecting Insert, Insert Scatter (X,Y) or Bubble Chart, Scatter, as pictured in blue data points in Image 3. These regression calculations can be demonstrated visually using Excel's trendline chart. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |