Question

4. Use Table II below to estimate costs for a company.

Reporting Period (Month)	Total Production Costs	Level of Activity (Units Produced)
January	$460,000	300
February	300,000	220
March	480,000	330
April	550,000	390
May	570,000	410
June	310,000	240
July	440,000	290
August	455,000	320
September	530,000	380
October	250,000	150
November	700,000	450
December	490,000	350

Table II

4a. Plot the level of production and cost data for each period. Insert a trend line and display the R² in the plot. Attach the plot to this assignment.

4b. Estimate the fixed and variable costs of production, and the total cost function using simple linear regression.

4c. Evaluate the model’s goodness of fit for cost estimation

4d. Estimate the total cost of producing 400 units and the 95% confidence interval of the estimation.

Answer 1

Solution :

I am using excel for plotting the data and regression  

Question 4A)

The graph has been plotted in excel and image is attached

Question 4 B)

Linear regression is done in excel and results are given in below image

Method: Go to data analysis section in excel then select regression

Give input Y = Total cost , X = Production

Coefficient = 703.42

Intercept = 1442.96

So total cost function = 703.42 + 1442.96 * No of Units

Fixed cost = 703.42 , Variable cost = 1442.96

Question 4C)

For model's goodness for fit see the Adjusted R-square value. It is 0.95( very close to 1, anything above 0.8 seems good ), in this case, means this model is a very good fit to explain the total cost with total production level

Significance value is 0.00000041 which well below 0.05, So cost has a significant relationship with production

Regression Statistics
Multiple R	0.977779912
R Square	0.956053556
Adjusted R Square	0.951658911
Standard Error	27858.96037
Observations	12

Question 4D)

Total cost of producing 400 unit

Total cost = 703.42 + 400 * 1442.96 = 577,889.73