Question

The Central Company manufactures a certain specialty item once a month in a batch production run. The number of items produced in each run varies from month to month as demand fluctuates. The company is interested in the relationship between the size of the production run (x) and the number of hours of labor (y) required for the run. The company has collected the following data for the ten most recent runs. Number of items: 40 30 70 90 50 60 70 40 80 70 Labor (hours): 85 60 137 180 96 119 140 76 159 143 (a) Find a point estimite for the number of hours of labor required, on average, when 60 units are produced. (Round your answer to two decimal places.) 30.87 x 119.50 hours of labor (b) Find a 95% confidence interval estimate of hours of labor required, on average, when 60 units are produced. (Round your answers to two decimal places.) 1.89 X 117.27 2.12 X 121.73 ) hours of labor

can you please show me the work in excel hsing data analysis? thanks!

Answer 1

This cannot be done using data analysis.

Go to Megastat instead.

File Home Insert Page Layout Formulas Data Review View Help Excel QM Tell me what you want to do Queries & Connections En 21 A FO e Properties From Text/CSV Recent Sources Le From Web E Existing Connections Get Data - From Table/Range Get & Transform Data << > Clear Reapply Advanced Data Analysis ?, Solver 6 Group Ungroup -5 62 Subtotal Refresh All Edit Links Stocks Geography Z Sort Filter MegaStat Text to Columns - What-If Forecast Analysis – Sheet Forecast Queries & Connections Data Types Sort & Filter Data Tools Outline Analyze Statistics E14 fx А B C D E F H 1 K L M N O P Q R S T U V w 1 Item Labor 2 40 85 3 30 60 MegaStat X 4 70 137 180 90 5 6 50 96 7 60 119 Clear 8 70 140 9 40 76 Cancel 10 80 159 MegaStat® Descriptive Statistics... Frequency Distributions Probability Confidence Intervals/Sample Size... Hypothesis Tests Analysis of Variance Correlation / Regression Time Series / Forecasting Chi-square / Crosstab Nonparametric Tests Quality Control Process Charts... Help 11 70 143 12 Scatternlot Correlation Matrix Regression 13 14 15 16 17 Random Number Generation... Utilities Help / Information 18 19 國」 Repeat Last Option 20 21 22

Input the variables and click OK.

E14 X А B с D F F H K 1 M N 0 P O Labor Regression Analysis х 1 Item 2 3 40 85 Input ranges: 30 60 OK 4 X, Independent variable(s) 70 137 Sheet11!$A$1:$A$11 5 90 180 Clear Sheet11! $B$1:$B$11 Y Dependent variable 6 50 96 Cancel 7 60 119 70 140 8 9 Help 40 76 Type in predictor values 601 predictor values 10 80 159 11 70 143 Residuals: 12 Options 95% Confidence Level Output Residuals 13 14 15 Variance Inflation Factors Diagnostics and Influential Residuals 16 Standardized Coefficients (betas) Durbin-Watson Cook's D 17 18 Test Intercept Force Zero Intercept Plot Residuals by Observation 19 All Possible Regressions Plot Residuals by Predicted Y and X 20 21 Stepwise Selection 1 best model of each size Normal Probability Plot of Residuals 22 23 24

The output is:

A1455 for A B с D E F G 1455 1456 Regression Analysis 1457 1458 p2 0.995 1459 [ 0.997 1460 Std. Error 3.052 1461 n 10 1462 k 1 1463 Dep. Var. Labor 1464 1465 ANOVA table 1466 Source SS df MS F p-value 1467 Regression 13,600.0000 13,600.0000 1460.40 2.41E-10 1468 Residual 74.5000 8 9.3125 1469 Total 13,674.5000 1470 1471 1472 Regression output confidence interval 1473 variables coefficients std. error t(df=8) p-value 95% lower 95% upper 1474 Intercept -0.5000 1475 Item 2.0000 0.0523 38.215 2.41E-10 1.8793 2.1207 1476 1477 Predicted values for: Labor 1478 95% Confidence Interval 95% Prediction Interval 1479 Item Predicted lower upper lower upper Leverage 1480 60 119.500 117.275 121.725 112.119 126.881 0.100 1481 1

(a)

Predicted

119.500

(b)

95% Confidence Interval
lower	upper
117.27	121.73

(c)

Predicted

119.500

(d)

95% Prediction Interval
lower	upper
112.12	126.88