Rent-a-Car Case Study

Advertising or not?

You work for a national rental car company. Your company decided to look into the effectiveness of the money spent on advertising. Your manager would like you to provide a comprehensive report on this issue and also include your recommendations for advertising strategies moving forward.

The marketing department provides you a dataset that includes the detailed history data on advertisement spending and sales. You may download the dataset "Rent-a-car advertising" from Moodle.

There are in total 6 variables in the dataset:

week	An indicator of business week that goes from 1 to 52.
QE	The number of rental contracts initiated each week in the economy category
TotlAD	Dollar amount spent on local advertising each week.
AdBlbd	Weekly spending on billboard ads in dollars
AdPaper	Weekly spending on ads in local newspapers, including the online version in dollars
AdTV	Weekly spending on ads placed with local TV in dollars

Please follow the following sequence to construct your report to the manager.

(1) The number of weekly economy rental contracts can be a function of:

(a) current weekly total advertising expenditures;

(b) total advertising expenditures lagged one week; and

(c) current weekly total advertising expenditures and total advertising expenditures lagged one week.

You need to decide among the above three different regression specifications. Explain the basis for your selection in terms of the fit of the equation and statistical significance of the estimated slope parameters. (25%)

(2) Now that you have made your choice among (a), (b), and (c), please explain what is the size of the effect of total advertising expenditure on the number of rental contracts based on the estimated parameter you observed from the model you selected. (10%)

(3) It is possible that the effect of advertising on the number of rental contracts is non-linear. For example, too much advertising may "wear out" the consumer. Can you include a quadratic term of the total advertising spending to address this "wear out" effect? Please clearly define your model and then provide a conclusion on the non-linear effect based on the new regression result. (10%)

(4) One manager mentioned in the meeting that he suspects certain forms of advertising may be better than other forms. Is it possible to better your report by addressing his concern using the data provided on the three subcategories of advertising spending? For simplicity, only test one type of advertisement at a time. You may want to try non-linear form or lagged spending form. However, no matter what you do here, do NOT include TotlAD in the regression. This is because linear regression will automatically drop a variable from a group of variables that are perfectly linearly correlated. TotlAD=AdBlbd+AdPaper+AdTV, so these four variables are linearly correlated. Please clearly specify the model you used and discuss the result on the slope parameter and the statistical significance of the parameter. (45%)

Note:

1. My intention of having this assignment is for you to (a) apply regression analysis on decision making, (b) practice choosing the appropriate dependent, independent variables and regression model, (c) practice running regression using Excel and (d) produce an accurate and comprehensive write-up based on the regression results produced by Excel.
2. Please do NOT attach the original Excel output tables in your report. You may copy and paste part of the regression output table from Excel to your Word document.
3. Please be concise in describing your findings. Use Times New Roman, 12 point font size, normal margin. Please upload your report onto Moodle as a single pdf file. (10%) below is the Excel table to be able to compute this question.

week	QE	TotlAD	AdBlbd	AdPaper	AdTV
1	11	430	256	0	174
2	20	430	254	0	176
3	75	430	254	0	176
4	10	430	258	0	172
5	2	430	153	100	177
6	0	430	149	100	181
7	6	815	488	0	327
8	26	815	488	0	327
9	30	815	394	100	321
10	10	815	490	0	325
11	35	2197	1228	100	869
12	66	2520	1504	0	1016
13	36	1646	883	100	663
14	11	815	389	100	326
15	41	815	393	100	322
16	50	815	381	100	334
17	23	815	396	100	319
18	31	815	393	100	322
19	54	815	389	100	326
20	28	1455	779	100	576
21	62	4965	2876	100	1989
22	77	4325	2591	0	1734
23	59	4325	2490	100	1735
24	31	4325	2589	0	1736
25	78	4325	2582	0	1743
26	43	4325	2587	0	1738
27	67	4325	2497	100	1728
28	80	4325	2588	0	1737
29	58	4325	2488	100	1737
30	69	4018	2303	100	1615
31	65	6268	3667	100	2501
32	79	4018	2306	100	1612
33	88	4018	2305	100	1613
34	72	4018	2409	0	1609
35	66	4853	2911	0	1942
36	70	3477	1992	100	1385
37	35	4485	2683	0	1802
38	59	2642	1492	100	1050
39	69	2642	1590	0	1052
40	54	2642	1585	0	1057
41	16	508	209	100	199
42	2	508	314	0	194
43	39	1237	735	0	502
44	3	1237	739	0	498
45	62	3117	1866	0	1251
46	54	1237	749	0	488
47	52	1237	744	0	493
48	43	1237	738	0	499
49	52	3852	2220	100	1532
50	9	1237	641	100	496
51	30	1237	634	100	503
52	16	1237	631	100	506