excelpivot table
Is there a way in Excel to create a pivot table with multiple column totals?
Here's column names to demonstrate what I'm trying to do:
Jan | Feb | Mar | Apr | May | Jun | 6 Month total | Jul | Aug | Sep | Nov | Dec | 6 Month total | 12 Month total
The 12 Month total column is of course the regular Grand Total. The two 6 Month Total columns are the ones I'm looking to create.
If by logarithmic regression you mean the model log(y) = m1.x1 + m2.x2 + ... + b + (Error), you can use LOGEST and GROWTH with multiple independent variables. Note that if you want the estimated coefficients m1, m2, ..., b from LOGEST, you'll have to enter the formula into multiple cells as an array. See Excel's online help for the steps required.
Alternatively, you can log-transform your dependent variable and use LINEST/TREND which does the same thing under the hood.
ObWarning: Excel isn't the best regression package in the world. See, for example, McCullough & Heiser (2008), On the accuracy of statistical procedures in Microsoft Excel 2007, Comp Stats & Data Analysis 52(10) pp.4570-4578.
I am not very familiar with Holt-Winters, however I have this excellent book by @Rob Hyndman. The package forecast (which is based on the book) of statistical package R gives the following result on your data:
> hw<-read.table("~/R/stackoverflow/hw.txt")
> tt<-ts(hw[,3],start=c(1999,1),freq=12)
> aa<-forecast(tt)
> plot(aa)
> summary(aa)
Forecast method: ETS(M,N,A)
Model Information:
ETS(M,N,A)
Call:
ets(y = object)
Smoothing parameters:
alpha = 0.1701
gamma = 1e-04
Initial states:
l = 870.4847
s = -278.0815 -143.6584 151.959 -135.595 514.2527 236.9216
-32.7679 128.8337 115.0829 47.5922 -234.4105 -370.1288
sigma: 0.1122
AIC AICc BIC
1892.756 1896.346 1933.115
In-sample error measures:
ME RMSE MAE MPE MAPE MASE
18.1543007 121.8594668 70.7086492 0.8480306 7.0006920 0.2893504
Here is the graph of the forecast together with the confidence intervals:
Note that the function forecast picks automatically the best exponential smoothing model from 30 models which are classified by the type of trend model, seasonal part model and the additivity or multiplicity of error.
The best model found in your data is with multiplicative error, no trend and additive seasonality, which is less complicated model than you are trying to fit. The way function forecast works is however that the more complicated model was considered and rejected in favor the final model.
If you provide the exact formulas it would be possible to fit the precise model to see whether the problem you described is really property of the model.
Best Answer
Add a variable to indicate the total. In this case, 6 months is half a year, so we will call this variable "half". Sample data below for columns A,B,C
Now set up a pivot table with both half and month as rows and sales as the value, as shown below.