Results 1 to 6 of 6
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,568
    Thanks
    141
    Thanked 12 Times in 12 Posts

    Regression Analysis Help

    I have sales data for 12 month and wish to forecast the sales for the next quarter. There has been a steady growth in sales of 8% per quarter. I would like to compute the regression analysis manually (Y = a +bX) showing how this is computed as well as using Excel's built in regression analysis function

    Your assistance in this regard will be most appreciated
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Something like the attached perhaps?<br><br>
    Steve
    Attached Files Attached Files

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    After looking a little more, instead of linear model, the data suggests a dependence on Quarter that is quadratic. A model with Year, Quarter and Quarter² to me yields better results. [Predicted = -3310.4 +Year*1.665 -Quarter*18.828 +Quarter²* 4.32833]

    Steve
    Attached Files Attached Files

  4. #4
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,568
    Thanks
    141
    Thanked 12 Times in 12 Posts
    Hi Steve

    Thanks for all the help much appreciated. I took out an old stats book of mine to brush up on regression analysis. I managed to compute it manually, but your method is a lot easier and quicker. I have attached my data

    Regards

    Howard
    Attached Files Attached Files

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    As far as I know, the slope and intercept functions use those types of calculations (they have been the basis of the linear regression calcs used in hand-calculators for decades. The LINEST function used in my second example is more versatile and can be used many more models. It uses arrays to solve the linear model:

    Y = A0 + A1X1 + A2X2 + ... + AkXk

    Your manual method and the Slope/Intercept functions only work with:

    Y = A0 + A1X

    Steve

  6. #6
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,568
    Thanks
    141
    Thanked 12 Times in 12 Posts
    Hi Steve

    Thanks for the explanation and your invaluable help. Your solution is great and this is the one I will be using

    Regards

    Howard

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •