Hugo Enterprises depends on a regular supply of high-tensile steel strapping for
ID: 3250778 • Letter: H
Question
Hugo Enterprises depends on a regular supply of high-tensile steel strapping for its production process. The price of this particular product has varied historically according to a number of factors including time period, steel billet price (per ton), diesel fuel cost, market demand, and labor cost. Each row of the table below shows the historical wholesale price of a four-roll order of high-tensile steel strapping (3040 feet), the date that the price was recorded, and the previous month's values for steel billet price, diesel fuel, market demand, and shipping labor cost.
Using this data, build a high-quality regression model in Excel to predict the price of high-tensile steel strapping. Then predict the price of an order of high-tensile steel strapping using the following values:
Date = 4/1/2015
Steel billet Price = $448
Diesel Fuel = $248
Market Demand = 2,499,421
Labor Cost = $29.7
In the box below, state your forecast, and paste your final model from your spreadsheet.
.
To copy the data to the clipboard, select the table with your cursor and then click on Ctrl-C.
Data
Wholesale Price Date Steel Billet Price (ton) Diesel Fuel (100 gals) Market Demand Labor Cost 728 1/1/2013 446 262 2499525 25.5 734 2/1/2013 437 258 2497705 25.45 730 3/1/2013 436 259 2499865 25.68 756 4/1/2013 475 247 2499641 25.74 714 5/1/2013 425 253 2500453 26.12 729 6/1/2013 449 243 2500861 26.16 725 7/1/2013 444 249 2500744 26.63 730 8/1/2013 452 244 2497192 27.82 741 9/1/2013 439 264 2498189 28.66 740 10/1/2013 461 262 2497357 27.33 734 11/1/2013 446 252 2498142 27.82 747 12/1/2013 452 263 2497907 28.81 748 1/1/2014 468 238 2498384 27.56 727 2/1/2014 444 249 2500149 26.98 735 3/1/2014 428 258 2497435 27.49 754 4/1/2014 471 242 2500460 29.45 749 5/1/2014 457 268 2500328 29.78 741 6/1/2014 430 251 2498503 30.99 740 7/1/2014 447 245 2498426 30.85 747 8/1/2014 455 251 2499102 28.93 743 9/1/2014 465 251 2497113 32.22 736 10/1/2014 428 255 2497981 33.01 739 11/1/2014 436 260 2497904 32.46 761 12/1/2014 468 248 2500999 33.53 737 1/1/2015 444 231 2500406 30.52 733 2/1/2015 442 224 2498788 32.96 749 3/1/2015 462 257 2498470 33.03Explanation / Answer
The model for predicting the price of high-tensile steel strapping is,
Wholesale price = 3.810063 + 0.5471 * Steel bullet price + 0.2425 * Diesel Fuel + 2499421 * Market Demand + 1.7060 * Labor cost
Hence prediction for wholesale price for given values of
Steel billet Price = $448, Diesel Fuel = $248, Market Demand = 2,499,421, Labor Cost = $29.7
Wholesale price = 739.0553
Result of regression analysis:
SUMMARY OUTPUT Regression Statistics Multiple R 0.850273081 R Square 0.722964312 Adjusted R Square 0.672594186 Standard Error 6.037481311 Observations 27 ANOVA df SS MS F Significance F Regression 4 2092.740694 523.1851735 14.35303782 6.60454E-06 Residual 22 801.9259728 36.45118058 Total 26 2894.666667 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0% Intercept 381.0063426 2520.345661 0.151172257 0.881217454 -4845.870645 5607.88333 -4845.870645 5607.88333 Steel Billet Price (ton) 0.547111751 0.086487567 6.325900553 2.29342E-06 0.367747514 0.726475988 0.367747514 0.726475988 Diesel Fuel (100 gals) 0.242452403 0.124964294 1.940173429 0.06528341 -0.016707681 0.501612488 -0.016707681 0.501612488 Market Demand 8.58402E-07 0.001005187 0.000853973 0.999326325 -0.002083772 0.002085488 -0.002083772 0.002085488 Labor Cost 1.706034096 0.468501661 3.641468616 0.001440004 0.734421118 2.677647073 0.734421118 2.677647073Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.