Final Assignment:Red Bull Predictive ModelingScenario: You are an analyst at Red Bull. Your goal is to use predictive modeling to showwhich online teams’ channel should get a $100,000 bonus. You have...

1 answer below »
Final Assignment:Red Bull Predictive ModelingScenario: You are an analyst at Red Bull. Your goal is to use predictive modeling to showwhich online teams’ channel should get a $100,000 bonus. You have been given data from each ofthe 7 subcategories of the online campaigns.The 7 categories are:● Banner ads● Facebook● Instagram● E-zine● TV ads● Twitter● YouTubeUsing the given excel sheets, complete the following to do list:1. Run a Simple Linear Regression for EACH subcategory.Run a regression on each data file in Excel:§ Download the Red Bull files.§ Run a regression (DV: sales). For example, for the Facebook fileo Click the Data tab>Data Analysis>Regression.o Select the sales column as Input Y Range and the Facebook column as Input X Range.§ Determine the predicted sales amount for spending $100.§ Present your analysis in a profession way supporting your results with data visualizationtools. Who should win the bonus?2. Log Transform Sales for EACH categoryPlot your simple linear regression.


Microsoft Word - Red Bull Predictive Analytics Assignment.docx Final Assignment: Red Bull Predictive Modeling Scenario: You are an analyst at Red Bull. Your goal is to use predictive modeling to show which online teams’ channel should get a $100,000 bonus. You have been given data from each of the 7 subcategories of the online campaigns. The 7 categories are: ● Banner ads ● Facebook ● Instagram ● E-zine ● TV ads ● Twitter ● YouTube Using the given excel sheets, complete the following to do list: 1. Run a Simple Linear Regression for EACH subcategory. Run a regression on each data file in Excel: § Download the Red Bull files. § Run a regression (DV: sales). For example, for the Facebook file o Click the Data tab>Data Analysis>Regression. o Select the sales column as Input Y Range and the Facebook column as Input X Range. § Determine the predicted sales amount for spending $100. § Present your analysis in a profession way supporting your results with data visualization tools. Who should win the bonus? 2. Log Transform Sales for EACH category Plot your simple linear regression. § Select Layout>Trendline>Linear Trendline § Create a new column with log sales o In the first row of the new column type “=LN(“, then select the first row of the sales column. Click “Enter.” o Drag that box down the entire column to calculate all log sales. § Run another regression but with log sales as Input Y Range instead of sales. § Plot the new regression. § Present your analysis in a profession way supporting your results with data visualization tools. Who should win the bonus? 3. Run a Multiple Regression § Open the redbull.xlsx file, which holds all of the subgroups’ data. § Run a multiple regression using all of the variables as controls. § This is the same process as before, but this time, select all of the columns besides sales for Input X Range. § Present your analysis in a profession way supporting your results with data visualization tools. Who should win the bonus now? Explain? 4. Remove variables from the Multiple Regression Red Bull model based on: 1. Does it improve adjusted R2? 2. Does it have an insignificant p-value? 3. Is it associated with the response variable by itself? 4. Is it strongly associated with another explanatory variables? (If yes, then including both may be redundant.) 5. Does common sense (face validity) say it should contribute to the model? 6. What would you eliminate from the model? § Present your analysis in a profession way supporting your results with data visualization tools. Explain the results from each step. data twitterbannerfacebookinstagramyoutubetvezinesalesregion 88290.4332.70325.92600525703.7300000W 4477.66250272.825835.625658334000N 345200.7505.705.827205.627003.9344000E 0308.2420.460.9027925.227607363000S 0258406.870.3030863.430605.3405000W 0533.4336.743034504.733970.3460500N 0230.399934.202879028559.8437900E 345424.8523003080230387.3447500S 0958.62809.4305.5200.230233.629275448800W 5445.920852202.49.730204.430208.5464000W 00.3206.4234403059.33059463600E 42322250.33944.507476.37244.2484700W 0624.92447453406649.76024.8501300W 8433.20004630.3349827300N 72960006735.85439.730600E 7850.800050645003.333900E 7832.70006862.36729.636900E 010002496064.663.636500N 04.54.5287805.5038100E 0200785.72057.609070.46964.341000S 0324.50003780.33456.84200E 99946.600400.935042567.54700E 4430800894.92545.222276100E 661000036.8692.4690.46100E 5527.300546.228.306100E 3410000594.423523424000N 89100002024.62022000E 450.40.8002402.62402.218300S 090.2575.6325.908246.68056.424800W 0404.758272.82.85496.7509225200N 0682300.25.86.7205437227500E 45639654.800785.2389.234200S 7892.82200.6804.969231900W 345100000757.9756.932700N 7557488.549.300355.867.335700E 0209.792.300677.4457.736500S 655100200.2200.205927.75926.738100W 77100298.69.707350.77350.736200N 35410000046458000E 45100000208300E 45760007709000E 3520358.500204012300N 35100500506.5505.515200E 2510097.900470.2470.219500S 252100239.2002000200023300W 2553085700673.8365.827500N 55743.40003209475.710000E 52952.80003990.33037.527500S 2254.200056604645.960000W 5965.30400.98.298498883.7115000N 0455.90894.93.33302.8846.9140000E 0839.7036.8722.920898.620058.9165000S 020000546.2458.724509.624427.9207500W 020000594.43232.87005.26959.8266000N 0999.902024.66000.59035.88035.9332500W 234693.7004520.46076.55382.8124300S 2342350.5006020.5254.37902.8143800W 443524.9240.42.87046.46678.43053.5159300N 443346.935.26.7782720000.27753.3175300E 52510000020185800S 25310000.607429742810300W 23410040.800443244309200E 2433255.43255.4003256.4014000E 2342926000850555799000E 423100224003260.73259.770200W 2341007.40356.63609.63508.682000N 3421000.704444069.94068.9109000E 25310040.806405205.85204.8129500S 23410030620.27284.87283.8149500W 243100329.902506.27803.67802.6169400N 23467.9454.90090.49922.5188000E 253100899.60778.8840.7839.7206500S 234203.6742.7009967.6999764227000W 24330.3907.4002778.22747.9239000N 2347000460.4008052.87984.9550000W 4237000807.7002492.82373.4595100S 2345000866.3002443.32363.3556500W 342390.3856.28.204949.44558672000N 070.6862.33.3093908209.5613300E 03038.90722.902405.79276.8578300S 0668.52052458.707679.86000.3629800W 099358.23232.804027.43928.3693300W 0707.8468.66000.508085.87368752300E 030.599.24520.460.92446.52406748700S 08000345.36020.570.33622.52920.5814900W 0800050.97046.443356.8204.6850500W 010000809.7782734.22.70901500W 234289.8358.5007853.37563.598000S 2431003590305.569906989105500W 23482.4002202.47845.37662.9113000N 423730023446972.66799.6110000E 2341005303944.5673672123100S 342682.300453496.6504.3135000W 01000356.60865.8864.8149000N 010034.644402984.52983.5164000W 0100208.864006335.96334.9185500S 030.9553620.2050445003.2193500S 0550.2508.42506.224964240.43690.2198500W 234940028783354.53060.5209000W 24320000778.82057.69370.79370.7218500W 23410070.6002015000S 42310028.20056.455.416500W 234100299002008.82007.816500N 342356.90006095.34738.417500E 234830.40009604.98774.535000S 24397.60004668.7457047500E 2343.80007093.9709063000E 42310072.500282.3280.385000W 1 2 3 4 5 6 7 8 9 A B C D E twitter banner facebook instagram youtube 88 290.4 332.7 0 325.9 44 77.6 625 0 272.8 345 200.7 505.7 0 5.8 0 308.2 420.4 60.9 0 0 258 406.8 70.3 0 0 533.4 336.7 43 0 0 230.3 999 34.2 0 345 424.8 523 0 0 data bannersales 290.4300000 77.6334000 200.7344000 308.2363000 258405000 533.4460500 230.3437900 424.8447500 958.6448800 5.9464000 0.3463600 232484700 624.9501300 33.227300 29630600 50.833900 32.736900 10036500 4.538100 200741000 324.54200 946.64700 3086100 1006100 27.36100 10024000 10022000 0.418300 90.224800 404.725200 68227500 39634200 2.831900 10032700 88.535700 209.736500 10038100 10036200 1008000 1008300 769000 20312300 10015200 10019500 10023300 30827500 743.410000 952.827500 4.260000 965.3115000 455.9140000 839.7165000 2000207500 2000266000 999.9332500 693.7124300 2350.5143800 3524.9159300 3346.9175300 100185800 10010300 1009200 3255.414000 29269000 10070200 10082000 100109000 100129500 100149500 100169400 67.9188000 100206500 203.6227000 30.3239000 7000550000 7000595100 5000556500 390.3672000 70.6613300 3038.9578300 668.5629800 99693300 707.8752300 30.5748700 8000814900 8000850500 10000901500 289.898000 100105500 82.4113000 73110000 100123100 682.3135000 100149000 100164000 100185500 30.9193500 550.2198500 94209000 2000218500 10015000 10016500 10016500 356.917500 830.435000 97.647500 3.863000 10085000 1 2 3 4 5 6 7 8 9 A B C D E banner sales 290.4 300000 77.6 334000 200.7 344000 308.2 363000 258 405000 533.4 460500 230.3 437900 424.8 447500 data ezinesales 25703.7300000 25658334000 27003.9344000 27607363000 30605.3405000 33970.3460500 28559.8437900 30387.3447500 29275448800 30208.5464000 3059463600 7244.2484700 6024.8501300 349827300 5439.730600 5003.333900 6729.636900 63.636500 038100 6964.341000 3456.84200 2567.54700 22276100 690.46100 06100 23424000 022000 2402.218300 8056.424800 509225200 37227500 389.234200 69231900 756.932700 67.335700 457.736500 5926.738100 7350.736200 458000 08300 09000 012300 505.515200 470.219500 200023300 365.827500 475.710000 3037.527500 4645.960000 8883.7115000 846.9140000 20058.9165000 24427.9207500 6959.8266000 8035.9332500 5382.8124300 7902.8143800 3053.5159300 7753.3175300 0185800 742810300 44309200 014000 55799000 3259.770200 3508.682000 4068.9109000 5204.8129500 7283.8149500 7802.6169400 9922.5188000 839.7206500 9764227000 2747.9239000 7984.9550000 2373.4595100 2363.3556500 4558672000 8209.5613300 9276.8578300 6000.3629800 3928.3693300 7368752300 2406748700 2920.5814900 204.6850500 0901500 7563.598000 6989105500 7662.9113000 6799.6110000 672123100 504.3135000 864.8149000 2983.5164000 6334.9185500 5003.2193500 3690.2198500 3060.5209000 9370.7218500 015000 55.416500 2007.816500 4738.417500 8774.535000 457047500 709063000 280.385000 1 2 3 4 5 6 7 8 9 A B C D E ezine sales 25703.7 300000 25658 334000 27003.9 344000 27607 363000 30605.3 405000 33970.3 460500 28559.8 437900 30387.3 447500 data facebooksales 332.7300000 625334000 505.7344000 420.4363000 406.8405000 336.7460500 999437900 523447500 2809.4448800 2085464000 206.4463600 2250.3484700 2447501300 027300 030600 033900 036900 036500 4.538100 85.741000 04200 04700 06100 06100 06100 024000 022000 0.818300 575.624800 5825200 300.227500 54.834200 2231900 032700 49.335700 92.336500 200.238100 298.636200 08000 08300 09000 58.512300 515200 97.919500 239.223300 5727500 010000 027500 060000 0115000 0140000 0165000 0207500 0266000 0332500 0124300 0143800 240.4159300 35.2175300 0185800 010300 40.89200 3255.414000 09000 22470200 7.482000 0.7109000 40.8129500 3149500 329.9169400 454.9188000 899.6206500 742.7227000 907.4239000 460.4550000 807.7595100 866.3556500 856.2672000 862.3613300 0578300 2052629800 358.2693300 468.6752300 99.2748700 345.3814900 50.9850500 809.7901500 358.598000 359105500 0113000 0110000 53123100 0135000 0149000 34.6164000 208.8185500 553193500 508.4198500 0209000 0218500 70.615000 28.216500 29916500 017500 035000 047500 063000 72.585000 1 2 3 4 5 6 7 8 9 A B C D E facebook sales 332.7 300000 625 334000 505.7 344000 420.4 363000 406.8 405000 336.7 460500 999 437900 523 447500
Answered 3 days AfterAug 05, 2021

Answer To: Final Assignment:Red Bull Predictive ModelingScenario: You are an analyst at Red Bull. Your goal is...

Atreye answered on Aug 09 2021
144 Votes
1. Output of simple linear regression for each subcategory:
The predicted sales amount for spending $100 is tabulated below:
    Banner
    $144158
    Facebook
    $162021
    Instagram
    $145320
    e-zine
    $139367
    TV ads
    $141999
    Twitter
    $198160
    Youtube
    $200013
Conclusion:
From the above chart, it is evident that sales for YouTube is the highest among all other categories. This implies that YouTube’s team should w
in the bonus.
2. Plot of simple linear regression for each subcategory:
Output of simple linear regression for log transformed sales:
Plot of simple linear regression of log-sales for each subcategory:
    Banner
    11.1235
    Facebook
    11.1482
    Instagram
    11.1224
    e-zine
    10.8341
    TV ads
    10.8558
    Twitter
    11.3529
    YouTube
    11.2692
The predicted log-sales amount for spending $100 is tabulated below:
Conclusion:
From the above chart, it is evident that log sales for Twitter is the highest among all other categories. This implies that Twitter’s team should win the bonus.
3. Multiple regression model:
The predicted sales amount for spending $100 using multiple regression model is tabulated below:
    twitter
    38656.39
    banner
    42416.32
    Facebook
    45940.5
    Instagram
    46006.01
    YouTube
    38930.96
    TV
    37454.87
    E-zine
    40779.06
Conclusion:
From the above chart, it is evident that sales for Instagram is the highest among all other categories. This implies that Instagram’s team should win the bonus.
4. Remove variables from the Multiple Regression Red Bull model
1. If we remove the twitter and YouTube category from multiple regression model, then it will increase adjusted R-square value from 0.655212357 to 0.66165928.
2. they are both insignificant as the corresponding p-values are 0.961728885 and 0.766094073 which are not significant.
3. From the below graphs it is evident that they are not associated with the response variable sales.
4. From the correlation matrix, it is seen that there is very high correlation between TV and E-zine. So, TV will be removed from the existing model to avoid redundancy.
    
    twitter
    banner
    Facebook
    Instagram
    YouTube
    TV
    E-zine
    twitter
    1
    
    
    
    
    
    
    banner
    -0.0441
    1
    
    
    
    
    
    Facebook
    -0.0436
    0.13665
    1
    
    
    
    
    Instagram
    -0.0469
    0.49549
    0.21514
    1
    
    
    
    YouTube
    -0.0397
    0.12088
    -0.1539
    -0.0735
    1
    
    
    TV
    -0.0767
    -0.0472
    0.30747
    -0.0798
    0.01185
    1
    
    E-zine
    -0.0775
    -0.0698
    0.29394
    -0.082
    -0.0381
    0.97185
    1
5. It does make sense, that banner, Facebook, Instagram and E-zine will contribute to the model.
6. Finally, Twitter, YouTube and TV will be removed from the multiple linear regression model.
The final multiple regression model output is as below:
The predicted sales amount for spending $100 using final multiple regression model is tabulated below:
    banner
    40972.46
    Facebook
    44076.94
    Instagram
    44744.51
    E-zine
    38240.38
Conclusion:
From the above chart, it is evident that sales for Instagram is the highest among all other categories. This implies that Instagram’s team should win the bonus.
References
George A. Morgan, R. J. (June01,2003). Use and interpretation of multiple regression. JOURNAL OF THE AMERICAN ACADEMY OF CHILD & ADOLESCENT PSYCHIATARY, Volume 42, Isusue 6, P738-740.
Khushbu Kumari, S. Y. (2018). Linear regression analysis study. Journal of the PRACTICE OF CARDIOVASCULAR SCIENCES, Volume 4, Issue 1, P33-P36.
SUMMARY OUTPUT
Regression Statistics
Multiple R0.072998143
R Square0.005328729
Adjusted R Square-0.004054962
Standard Error228542.8304
Observations108
ANOVA
dfSSMSFSignificance F
Regression129660953961296609539610.5678712890.452776649
Residual1065.53657E+1252231825309
Total1075.56623E+12
CoefficientsStandard Errort StatP-valueLower 95%Upper 95%Lower 95.0%Upper 95.0%
Intercept198389.495322143.356498.9593235481.22913E-14154488.1373242290.8532154488.1373242290.9
twitter-2.2935068943.04351253-0.7535723510.452776649-8.3275665033.740552714-8.3275665033.740553
SUMMARY OUTPUT
Regression Statistics
Multiple R0.045806221
R Square0.00209821
Adjusted R Square-0.007315958
Standard Error228913.6631
Observations108
ANOVA
dfSSMSFSignificance...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here