Lab Analytics : | Management

PatriotBank.xlsx 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 Chapter9Lab-MGMT414-S24.docx.pdf 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? BigBox.xlsx 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