Lab Analytics

Staffing Analysis

Interest Rate	Applications
4.0%	3098
4.3%	2785
4.3%	2515
4.7%	2180
4.7%	1873
5.2%	1794
5.6%	1450
5.7%	1177
6.1%	822
6.2%	886
6.2%	740
6.8%	740
7.1%	722
7.6%	540
8.3%	375
8.9%	425
9.4%	360
9.5%	389
9.7%	339
9.8%	336

MGMT 414: Management Analytics Lab Assignment: Chapter 9

Please see the corresponding datasets in blackboard and complete your analysis on the first two questions. Two separate excel files are expected for this assignment. All short answer questions should be completed in a Textbox. Each student will complete a data analysis independently and turn in their own file. Add your name to the file.

1. When interest rates decline, Patriot Bank has found they get inundated with

requests to refinance home mortgages. To better plan its staffing needs in the

mortgage processing area of its operations, Patriot wants to develop a

regression model to help predict the total number of mortgage applications

(Y) each month as a function of the prime interest rate (X1). The bank

collected the data shown in the file PatriotBank.xlsx representing the average

prime interest rate and total number of mortgage applications in 20 different

months.

1. Prepare a scatter plot of these data. Does there appear to be a linear

relationship between these variables.

2. Obtain a simple linear regression model by producing output tables.

3. Interpret the R^2 for the model you obtained.

4. What is the number of mortgage applications Patriot could expect to

receive in a month where the interest rate is 6%.

2. A recruiter for Big Box stores has collected the data in the file BigBox.xlsx

summarizing the amount of money the company spent on print, web, and TV

advertising in California over the past 22 months and the resulting number of

applications received from job applicants during the same months. The

recruiter would like to build a regression model to predict the number of

applications the company should expect based on a given advertising mix.

1. Prepare scatter plots showing the relationship between the number of

applications received and each of the independent variables. What sort

of relationship does each plot suggest?

2. If the recruiter wanted to build a regression model using only one

independent variable to predict the number of applications received,

what variable should be used?

3. What set of independent variables results in the highest value for the

adjusted-R 2 statistic?

4. Suppose the recruiter chooses to use the regression function with all

independent variables X1, X2, and X3. What is the estimated regression

function?

Data

$ Print	$ Web	$ TV	Applications
4,800	990	3,320	576
4,430	1,850	5,340	769
4,260	4,920	6,580	1,193
4,260	4,080	9,360	1,239
4,790	1,430	2,660	531
1,800	2,990	4,720	665
3,100	4,710	1,680	763
1,090	1,780	4,360	508
3,630	1,350	9,560	871
2,200	4,270	9,540	1,163
2,830	4,700	9,180	1,188
670	2,160	2,780	420
3,600	1,000	4,580	539
100	3,930	6,880	827
1,410	3,320	9,740	972
370	2,000	9,440	759
2,640	2,070	7,740	801
4,690	370	7,940	740
760	890	1,700	205
870	1,360	900	214
2,730	510	1,180	264
1,750	2,790	1,340	459