2: Wagner Printer Wagner Printers performs all types of printing including custom work, such as advertising displays, and standard work, such as business cards. Market prices exist for standard work, and Wagner must match or better these prices to get the business. The key issue is whether the existing market price covers the cost associated with doing the work. On the other hand, most of the custom work must be priced individually. Since the latter is done on a job-order basis, Wagner routinely keeps track of all the direct labor and direct material costs associated with each job. However, the overhead cost for each job must be estimated using past data. The overhead is applied to each job using a predetermined (normalized) rate based on estimated overhead and labor hours. Once the cost of the prospective job is determined, the manager develops a bid. In the past the, the normalized rate for overhead has been computed by using the historical average of overhead per direct labor hour. Wagner has become increasingly concerned this practice isn't viable for two reasons. First, it hasn't produced accurate forecasts of overhead in the past. Second, technology has changed the printing process so that the labor content of jobs has been decreasing, and the normalized rate of overhead per direct labor hour as steadily been increasing. The data file Wagner.xls shows the overhead data that the company has collected for its shop for the last 52 weeks. The average weekly overhead for these weeks is $54,208, and the average weekly number of labor hours worked is 716. Therefore, the normalized rate for overhead that the company will use in the upcoming week is about $76 (=54208/716) per direct labor hour. Wagner is unhappy with the above estimate and has hired you to help them better estimate overhead costs using the data in the Excel file. Wagner wants variables and predictions to be significant at the 95% confidence level. Wagner is now preparing a bid for a new customer order. The estimated requirements for this project are 15 labor hours, 8 machine hours, $150 direct labor cost, and $750 direct material cost. Using the existing approach to cost estimation, Wagner finds that the cost for this job is $2040 (=150+750+(76 times 15)). Your task is to use the data in the file to better estimate the cost for this new job. Question 2 Create histogram of the dependent variable and interpret it in no more than two short sentences in context of the problem. Do not show the graph. Create appropriate scatter plots and describe each of them (including the correlations) in two short sentences. Understanding these graphs is critical to writing down the \"correct\" model. Do not show the graphs. Develop a regression model using the above, run a regression and report your final equation using the estimated values from Stattools. State the p-values for each regression coefficient using the same style as in the word file, Explaining Regression Output posted in this folder. In one sentence, describe each of the SLOPE coefficients carefully. Explain the R-square for the model in context of the problem. Explain the standard error of the regression in context of the problem. How does your estimate for the new job compare with the company's estimate of $2040? Provide a 95% prediction interval of your estimate for the new job and interpret that in one sentence. Show your calculation of this prediction interval. Question 3: TX Fuels Company (50 points) In the last two years, there is a push to switch from natural gas to Bioheat fuels to heat homes, as well as water (https://genesee-energy.com/natural-gas-vs-heating-oil/), since new technologies have helped oil-based energy to become clean and sustainable. TX Fuels Company is experimenting with this type of fuel in certain rural areas in Texas. It wants to develop a consumption model for residential customers that depend on oil for their heating/water needs. The data on consumption amounts (in gallons of oil) for 40 customers are given to you in the file TX Fuels.xls. It contains the following variables. The number of degree days. A degree day is equal to the difference between the average daily temperature and 68 degrees F. Hence, if the average temperature is 50, the degree days for that day will equal 18. If the degree days is a negative number, it is recorded as 0. The number of people residing in each home. TX Fuel thinks this might be important in predicting oil usage, given that more people in a home would imply more hot water demand. A third variable is the type of home which is a number between 1 and 5, labeled Home Factor. This value is a composite index representing the home size, age, exposure to wind, insulation, and furnace type. Low index values correspond to lower oil consumption per day. The company would like to use regression methods to estimate and predict oil usage in homes. TX Fuel's management want to entertain variables that are significant at 95% percent confidence. Your task is to help them with this modeling phase. See solution template on what you're specifically asked to do. [Note that in the real world, you will not be given such a template; rather, you will only have access to data and the company's request to use analytics to help them with decision-making.] Forecasting Overhead at Wagner Printers The overhead cost is for total jobs per week Dollars Week Overhead LaborHrs MachineHrs 1 53421 695 239 2 44167 531 173 3 60085 839 329 4 55181 379 335 5 44484 602 152 6 68154 807 365 7 49415 515 276 8 63090 851 314 9 55935 774 289 10 56159 766 140 11 51862 537 311 12 54799 642 215 13 42067 434 199 14 49108 741 143 15 55467 995 144 16 53907 675 245 17 64376 935 261 18 48639 638 232 19 77800 1128 361 20 54077 744 191 21 29521 180 172 22 43676 188 254 23 51639 755 187 24 57315 1014 244 25 35410 371 181 26 59348 758 280 27 47437 662 185 28 49433 721 172 29 55003 811 229 30 57783 944 176 31 57084 1184 159 32 69759 1024 290 33 51315 772 162 34 52124 635 244 35 62643 630 286 36 48950 417 246 37 54092 665 257 38 58513 682 286 39 57237 1046 228 40 56184 887 194 41 65824 706 437 42 44095 520 204 43 55454 813 261 44 36927 387 179 45 48322 444 265 46 39093 725 144 47 58789 875 223 48 70844 1026 372 49 38749 407 199 50 60821 815 256 51 65585 706 372 52 77651 1231 338