Results 1 to 6 of 6
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,421
    Thanks
    124
    Thanked 5 Times in 5 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,421
    Thanks
    124
    Thanked 5 Times in 5 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,421
    Thanks
    124
    Thanked 5 Times in 5 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
  •