188 |
POWER EXCEL WITH MR EXCEL |
|
|
|
FORECAST DATA WITH SEASONALITY |
Problem: Straight-line forecasting won’t work because our sales are seasonal. We sell more near the
Christmas holiday and less in the summer.
Strategy: Excel 2016 introduced a new Forecast Sheet command. Select your data with dates and sales. From the Data tab, choose Forecast Sheet. Excel will analyze your data, look for seasonality and produce new cells showing a future forecast and a chart with a confidence interval. The new rows will use new functions such as FORECAST.ETS and FORECAST.ETS.CONFINT.
Figure 462 Excel can detect seasonality and produce a forecast.
Gotcha: If you have two factors for seasonality: Fridays are better than Wednesdays and December is better than July, the command will not handle the real-life scenario well.
BUILD A MODEL TO PREDICT SALES BASED ON MULTIPLE REGRESSION
Problem: I run a Gelato stand. After 10 days of sales, I discovered that each day, I would either make a lot of money or nearly go broke. As I analyzed sales, I began to feel that temperature and rain might be two important determining factors in how much money I make. On rainy or cool days, fewer people buy gelato.
I set up the table below, which shows each day’s sales, temperature, and whether it rained.
Based on the data I’ve collected, how can I determine the relationship between sales, temperature, and rainfall?
Strategy: You need to do a multiple regression. After a multiple regression, you will have a formula that predicts sales like this:
Y = m1x1 + m2x2 + b
Sales = Temperature x M1 + Rain x M2 + b
Figure 463 Sales swing wildly from day to day.
The LINEST function can return the values M1, M2, and b that best describe your sales model. Here’s what you do:
1. LINEST is going to return three values, so select a range of three cells that are side by side. The first argument is the range of known sales figures. The second argument is the range of temperatures and rainfall.
PART 2: CALCULATING WITH EXCEL |
189 |
|
|
2. Press Ctrl+Shift+Enter to calculate the array formula.
Figure 464 Enter one formula in three cells.
3.Enter a prediction formula in column D to see how well the regression calculation describes sales. The results are so-so. The prediction in D6 is right on the mark. The predictions in D11 and D12 are off by $20 each—an error of 10%.
2
Figure 465 Use LINEST to produce a forecast.
4.To get the additional statistics that LINEST can return to show how well the results match reality, add a fourth argument: TRUE. Be sure to enter the function in a five-row range.
Figure 466 Choose five rows and several columns before entering the formula.
5. Press Ctrl+Shift+Enter. You will get the results shown here.
190 |
POWER EXCEL WITH MR EXCEL |
|
|
Figure 467 Excel performs the regression and provides statistics.
I only somewhat paid attention in statistics class, but I know that a key statistical indicator is the R- squared value. It ranges from 0 to 1, where 1 is a perfect match, and 0 is a horrible match. The 0.88 value here confirms that the prediction model is pretty good but not perfect.
Additional Details: Regression models try to force actual results into a straight-line formula. The fact is that life may not fit in a straight-line formula. Because I created the spreadsheet used here, I know that the actual data in the gelato model uses the formula (Temperature - 50) x $2 if raining and (Temperature - 50) x $6 if not raining. In this example, Sally was correct that ice cream sales are dependent on rain and temperature, but even a powerful regression engine could not predict the absolutely correct formula.
Alternate Strategy: The Analysis ToolPak still offers tools to do Regression, as well as testing correla- tion, exponential smoothing, create histograms, generate random numbers, create samples, and more. You have to enable the add-in first. Type Alt+T followed by I. Add a checkmark next to Analysis ToolPak and click OK.
You will now have a Data Analysis icon on the right side of the Data ribbon tab. Click the icon and Excel offers a list of tools. Although some of these tools offer older dialog boxes that really need updating, they can often produce far more detailed results. The Regression tool creates charts of the residuals, Anova analysis, and tables of statistics about the regression.
Figure 468 The Analysis ToolPak offers a variety of statistical tools.
Gotcha: The results from the ToolPak are not live formulas! They are a one-time snapshot. If you change the underlying data, you will have to run the analysis tool again.
PART 2: CALCULATING WITH EXCEL |
191 |
|
|
SWITCHING COLUMNS INTO ROWS USING A FORMULA
Problem: Every day, I receive a file with informa- tion going down the rows. I need to use formulas to pull this information into a horizontal table. It is not practical for me to use Paste Special, Transpose every day. Below, you can see that the first formula in B2 points to A4. If I drag this formula to the right, there is no way that it will pull values from
A5, A6, A7, and so on.
Figure 469 Dragging the fill handle will fail here.
Strategy: You can use the INDEX function to return the nth item from the A4:A10 range. It would be cool 2 if there were a function that could return the numbers 1, 2, 3, and so on as you copy across.
The formula =COLUMN(A1) will return a 1 to indicate that cell A1 is in the first column. While this is not entirely amazing, the beautiful thing about this function is that as you copy to the right, =COLUMN(A1) will change to =COLUMN(B1) and return a 2. Any time you need to fill in the numbers 1, 2, 3 as you go across a row, you can use the =COLUMN(A1) in the first cell. As you copy, Excel will take care of the rest.
Therefore, if you use the formula =INDEX($A$4:$A$10,COLUMN(A1)) in cell B2, you can easily copy it across the columns.
Gotcha: You need to use A1 as the reference for the COLUMN function no matter where you are entering the formula. In this example, the first formula is in column B. That is irrelevant. Even if the formula starts in column XFA, you will still point to A1 in order to return the number 1.
Figure 470 Copy B2 across to transpose with a formula.
Alternate Strategy: It is slightly harder to use, but the TRANSPOSE function will perform the same task as COLUMN. The trick is that a single function has to be entered in many cells at once. Follow these steps:
1. Count the number of cells in A4:A10. In this case, it is seven cells. 2. Select seven horizontal cells. In this case, select B2:H2.
3. Type =TRANSPOSE(A4:A10). Unlike INDEX, dollar signs are not necessary in this formula. Do not press Enter.
4. Because this function will return many answers, you have to hold down Ctrl+Shift while you press
Enter. Excel will add curly braces around the function, and the seven values will appear across your selection.