Question

can somebody please tell me how to do it on excel

An accountant wishes to predict direct labor cost (y) on the basis of the batch size (x) of a product produced in a job shop. Data for 12 production runs are given in the table below, along with the Excel output from fitting a least squares regression line to the data. Direct Labor Cost Data Direct Labor Batch Cost, y ($100s) 908 Size, x 71 177 80 514 41 84 827 734 7 634 8 412 53 1,014 759 79 42 307 48 827 48 124 14 Batch Size Fit Plot Labour Cost = 473.5263 +2.7038 BSize 1200 1000 800 600 400 200 0 0 20 40 60 80 100 Batch Size (a) Find bi and bo. (Round your intermediate value and final answers to 4 decimal places. Negative amounts should be indicated by a minus sign.) Labourcost

Answer 1

a)

Under the data tab in excel, click on data analysis and select regression.

XU9 Bookl - Micro File Home Insert Page Layout Formulas Data Review View Connections Clear 5 A Em 21 AF Properties Reapply From From From From Other Existing Refresh Sort Filter Text to Re Y Advanced Access Web Text Sources Connections All - Edit Links Columns Du Get External Data Connections Sort & Filter E8 A B C D D E F G H I J Direct labor Data Analysis cost, y Batch size 1 ($100s) X Analysis Tools ок — 908 Covariance Descriptive Statistics Cancel 177 Exponential Smoothing 514 F-Test Two-Sample for Variances Help Fourier Analysis 827 Histogram 734 Moving Average Random Number Generation 634 Rank and Percentile Regression 412 1014 759 307 827 124

----------------------------------------------------------------------------------------

Select the data range. The input Y range consists of data from cell A2 to A13. The input X range consists of data from cell B2 to B13.

Once the data range is selected, click ok.

Bookl - Microsof File Home Insert Page Layout Formulas Data Review View El Connections A1 A2 Properties Refresh A Sort All Go Edit Links * Clear Reapply Y Advanced Existing Connections Filter Text to Remd Columns Duplic Regression Input Input Y Range: ок | A From From From From Other Access WebText Sources Get External Data B2- в | Direct labor cost, y Batch size 1 ($100s) 908 177: $A$2:$A$13 $B$2:$B$13 Cancel Input X Range: Help 2 Labels Confidence Level: Constant is Zero 95 % 514 827: 734 634: Output options Output Range: New Worksheet Ply: New Workbook Residuals Residuals Standardized Residuals 412 Residual Plots Line Fit Plots 1014 759 3071 827 Normal Probability Normal Probability Plots 124....

-------------------------------------------------------------------------------------------

The output of the regression analysis is as shown in the summary output.

b₀ = intercept = 473.5282

b₁ = slope = 2.7038

The equation becomes

y = 473.5282 +  2.7038 x

where y = Direct Labor Cost

x = Batch size

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.253897
R Square	0.064464
Adjusted R Square	-0.02909
Standard Error	297.7582
Observations	12
ANOVA
	df	SS	MS	F	Significance F
Regression	1	61091.65	61091.65	0.689056	0.425859
Residual	10	886599.3	88659.93
Total	11	947690.9
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	473.5282	178.177	2.657628	0.023997	76.52511	870.5314	76.52511	870.5314
X Variable 1	2.703758	3.257172	0.830094	0.425859	-4.55367	9.961189	-4.55367	9.961189