Question

A company would like to estimate its total cost equation using customer records.  The company has randomly sampled 28 customer records. Each customer record contains a Customer #, the Order Size, and the Total Cost of the Order.  The analyst remembers from accounting and economics classes taken in college that

TOTAL COST = Fixed Costs + Variable Cost per Unit *Order Size.

The analysis sees that this is a linear relationship where the TOTAL COST depends on the Fixed Costs, which do not depend on order size, and a variable cost per unit, which is multiplied by the Order Size.  The analysis decides to use simple linear regression to estimate the firm’s Total Cost function.  Use the data file, Estimating a Total Cost Regression Model.xlsx to answer the following questions

1. Develop a 95% confidence interval for the true average unit variable cost. (Look at the regression output produced by Excel for part b.)
2. What percent of the variation in monthly total costs is “explained” by the regression model with monthly production output as the explanatory variable? (Look at the regression output produced by Excel for part )
3. Suppose the plant manager is interested in estimating the mean total costs for several months where output is 30,000 units (i.e., Xp = 30) each month. Develop a 95% confidence interval for the mean total costs for months that average 30,000 units of output.

Customer #	Order Size (Quantity)	Total Cost of Order
10211	28	1631
10212	31	1923
10213	43	2070
10214	47	2392
10215	32	1886
10216	43	2307
10217	25	1486
10218	46	2448
10219	41	2210
10220	48	2401
10221	29	1860
10222	32	1786
10223	49	2485
10224	44	2203
10225	33	1855
10226	46	2380
10227	42	2102
10228	31	1683
10229	30	1706
10230	35	1955
10231	34	1992
10232	33	1926
10233	27	1852
10234	32	1807
10235	31	1880
10236	42	2134
10237	39	1979
10238	36	1882

Answer 1

using excel

we have

Simple Linear Regression Analysis
Regression Statistics
Multiple R	0.9429
R Square	0.8891
Adjusted R Square	0.8848
Standard Error	89.8751
Observations	28
ANOVA
	df	SS	MS	F	Significance F
Regression	1	1683962.6247	1683962.6247	208.4747	0.0000
Residual	26	210016.0539	8077.5405
Total	27	1893978.6786
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	725.6413	90.4165	8.0255	0.0000	539.7875	911.4951
Order Size (Quantity)	34.8912	2.4165	14.4387	0.0000	29.9240	39.8584

Confidence Interval Estimate
Data
X Value	30
Confidence Level	95%
Intermediate Calculations
Sample Size	28
Degrees of Freedom	26
t Value	2.055529
XBar, Sample Mean of X	36.75
Sum of Squared Differences from XBar	1383.25
Standard Error of the Estimate	89.87514
h Statistic	0.068653
Predicted Y (YHat)	1772.377
For Average Y
Interval Half Width	48.4053
Confidence Interval Lower Limit	1723.9719
Confidence Interval Upper Limit	1820.783
For Individual Response Y
Interval Half Width	190.9772
Prediction Interval Lower Limit	1581.4000
Prediction Interval Upper Limit	1963.355

Ans 1 ) a 95% confidence interval for the true average unit variable cost is in between (29.924,39.8584 )

Ans 2 ) 88.91% percent of the variation in monthly total costs is “explained” by the regression model with monthly production output as the explanatory variable

Ans 3 ) a 95% confidence interval for the mean total costs for months that average 30,000 units of output is (1723.9719 ,1820.783)