A sales force manager is interested in the effects of training on a group of sal
ID: 3268714 • Letter: A
Question
A sales force manager is interested in the effects of training on a group of sales people. She is convinced that training is the key to a successful career in sales. Yet, she also concedes that other factors may be responsible for greater success in sales, for example, education (college or no college). She has compiled some data on 36 sales people that might be able to answer a number of questions. The data includes: 1) scores related to understanding the company's complicated sales process before and after training, 2) the education level of the sales person, and 3) annual sales for the sales person before and after the training. Her trusty assistant, Liliana, suggests that it might be useful to see if there is a relationship between the change in a score after training and the corresponding change in yearly sales. Thus, the data will have to be manipulated slightly; for example, for observation 12, the change in score was +26 (87-61) and the change in sales was +29,133 (268,099-238,986). This suggests that new columns of data will be needed in the table of data. Liliana believes that a simple linear regression is appropriate.
1a) Develop a regression model that explains the relationship between Scores and Sales, before and after training. Place the analysis (Excel or Minitab) below. Also, determine the following the regression formula. I NEED STEP BY STEP INSTRUCTIONS ON HOW TO PERFORM THESE TASKS IN EITHER EXCEL OR MINITAB. ALSO, THE REGRESSION FORMULA IS VERY IMPORTANT.
THANK YOU SO MUCH!
obs (Salesperson) Scores before Training Scores after Training College Ed.? Sales Before Training Sales after Training 1 83 85 N 166,489 150,258 2 73 94 Y 237,781 194,942 3 86 77 N 75,995 120,322 4 90 64 N 92,090 94,612 5 64 90 Y 248,643 229,640 6 69 89 Y 230,373 196,962 7 71 73 N 134,215 190,025 8 95 84 Y 228,740 211,721 9 83 80 N 29,338 84,353 10 93 85 N 107,257 96,278 11 74 76 Y 305,045 340,367 12 61 87 Y 238,986 268,099 26 29,113 13 88 92 N 198,608 229,955 14 87 67 N 158,325 134,040 15 72 71 N 106,689 88,367 16 82 73 N 141,644 132,143 17 79 98 Y 283,687 344,950 18 83 93 Y 199,340 288,641 19 74 75 Y 201,542 207,100 20 89 74 N 260,166 244,982 21 76 83 Y 390,408 455,719 22 63 89 Y 223,731 234,215 23 86 78 N 150,117 140,085 24 79 72 N 95,520 72,631 25 83 85 Y 229,526 218,874 26 76 76 Y 143,256 195,902 27 91 91 N 227,492 240,990 28 74 79 N 125,196 138,925 29 73 65 N 48,124 63,806 30 80 87 Y 210,793 198,237 31 86 81 Y 140,605 108,678 32 77 84 Y 171,861 264,007 33 70 79 N 153,884 200,268 34 92 81 N 161,336 173,929 35 80 68 N 138,517 114,375 36 65 93 Y 227,409 304,206Explanation / Answer
b1= nE(xy)-ExEy/nE(x2)-(Ex2)
= 36*5823391-(71*489876)/36*6525-(71*71)
= 760.73
b0= Ey-b1Ex/n
= 489876-(760.73*71)/36
= 12107.336
y=12107.336+760.73x
r (correlation)= n(Exy)-(Ex)(Ey)/sqrt(nEx2-(Ex)2)(nEy2-(Ey)2)
= 36(5823391)-(71)(489876)/(36*6525-(71)^2)(36*55141916084-(489876)^2)
= 0.276
r^2=0.076
Excel:-
1. Go to Data==> Data Analysis
2. Regression
3. Input Y range and X range on click on Labels if you have labels in the first column.
Change in Scores (x) Change in Sales (y) x^2 y^2 xy 2 -16231 4 263445361 -32462 21 -42839 441 1835179921 -899619 -9 44327 81 1964882929 -398943 -26 2522 676 6360484 -65572 26 -19003 676 361114009 -494078 20 -33411 400 1116294921 -668220 2 55810 4 3114756100 111620 -11 -17019 121 289646361 187209 -3 55015 9 3026650225 -165045 -8 -10979 64 120538441 87832 2 35322 4 1247643684 70644 26 29113 676 847566769 756938 4 31347 16 982634409 125388 -20 -24285 400 589761225 485700 -1 -18322 1 335695684 18322 -9 -9501 81 90269001 85509 19 61263 361 3753155169 1163997 10 89301 100 7974668601 893010 1 5558 1 30891364 5558 -15 -15184 225 230553856 227760 7 65311 49 4265526721 457177 26 10484 676 109914256 272584 -8 -10032 64 100641024 80256 -7 -22889 49 523906321 160223 2 -10652 4 113465104 -21304 0 52646 0 2771601316 0 0 13498 0 182196004 0 5 13729 25 188485441 68645 -8 15682 64 245925124 -125456 7 -12556 49 157653136 -87892 -5 -31927 25 1019333329 159635 7 92146 49 8490885316 645022 9 46384 81 2151475456 417456 -11 12593 121 158583649 -138523 -12 -24142 144 582836164 289704 28 76797 784 5897779209 2150316 71 489876 6525 55141916084 5823391Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.