Multiple Regression: Examine the relationship between the number of units produc
ID: 3303950 • Letter: M
Question
Multiple Regression: Examine the relationship between the number of units produced and the number of production batches and their effect on total manufacturing overhead.Consider the following in your report: a. What is the coefficient of determination? How did multiple (versus single) regression help/hurt the r-squared? b. What is the estimated Variable Manufacturing Overhead Per Unit? c. What is the estimated Total Fixed Manufacturing Overhead?
Queens Lamps Inc specializes in manufacturing custom designed lamps. It is a low volume manufacturer. Its cost data for four years is as follows. Sales Price Per Unit: $ 400.00 Variable Selling and General Expenses per unit $ 12.00 Monthly Fixed Selling and General Expenses $ 2,000 Observation Number Month Year Number of Production Batches Units Produced Manufacturing Overhead Machine Hours Direct Material Cost Direct Labor Cost 1 Jan 20X1 8 121 10,991 1,557 3,703 4,646 2 Feb 20X1 4 103 9,724 1,259 3,059 4,408 3 Mar 20X1 5 82 9,830 970 2,411 3,674 4 Apr 20X1 7 123 11,006 1,631 3,653 4,723 5 May 20X1 10 95 10,413 1,235 2,850 4,028 6 Jun 20X1 5 100 10,403 1,391 2,970 4,280 7 Jul 20X1 11 125 11,307 1,479 3,750 5,750 8 Aug 20X1 5 68 9,716 955 2,060 2,530 9 Sep 20X1 3 130 10,310 1,808 3,822 4,992 10 Oct 20X1 6 80 9,622 988 2,448 3,520 11 Nov 20X1 4 124 10,237 1,483 3,683 4,613 12 Dec 20X1 10 128 11,120 1,597 3,802 5,530 13 Jan 20X2 5 121 10,311 1,715 3,630 4,453 14 Feb 20X2 7 71 10,103 951 2,173 3,039 15 Mar 20X2 7 122 10,777 1,507 3,587 4,197 16 Apr 20X2 10 35 9,822 473 1,050 1,330 17 May 20X2 4 116 10,527 1,493 3,480 4,083 18 Jun 20X2 8 44 9,331 578 1,294 2,006 19 Jul 20X2 11 71 10,697 886 2,087 2,698 20 Aug 20X2 6 57 9,767 771 1,727 2,440 21 Sep 20X2 10 56 10,241 692 1,697 2,419 22 Oct 20X2 5 73 9,695 940 2,146 3,066 23 Nov 20X2 6 128 10,746 1,498 3,802 5,171 24 Dec 20X2 9 114 10,790 1,615 3,420 4,697Explanation / Answer
Here manufacturing overhead is dependent variable and number of production batches and units produced are independent variables.
This is problem of multiple regression.
Now we have to fit regression of manufacturing overhead on number of production batches and units produced.
We can do regression in EXCEL.
steps :
ENTER data into EXCEL sheet --> Data --> Data Analysis --> Regression --> ok --> Input Y Range : select range of Manufacturing overhead --> Input X range : select range of number of production batches and units produced --> Output Range : select one empty cell --> ok
Here correlation between manufacturing overhead and number of production batches and units produced is 0.9361.
There is poisitve relationship between manufacturing overhead and number of production batches and units produced.
R-sq = 0.8762
It expresses the proportion of variation in manufacturing overhead which is explained by variation in number of production batches and units produced.
The regression equation is,
manufacturing overhead= 7899.28 + 133.77*number of units produced + 15.61*units produced.
intercept= 7899.28
Regression coefficients = 133.77 and 15.61
Interpretation of slopes : When we fixed number of units produced then one unit change in units produced will be 15.61 units increase in manufacturing overhead.
When we fixed unit change then one unit change in number of units produced will be 133.77 units increase in manufacturing overhead.
Hypothesis testing :
Overall significance :
Here we have to test the hypothesis that,
H0 : Bj = 0 Vs H1 : Bj not= 0
where Bj is population slope for jth independent variable.
Assume alpha = 0.05
Here test statistic follows F-distribution.
F = 74.31
P-value = 2.98E-10
P-value < alpha
Reject H0 at 5% level of significance.
Conclusion : Atleast one of the population slope is differ than 0.
Individual significance :
Here we have to test the hypothesis that,
H0 : B = 0 Vs H1 : B not= 0
where B is population slope for an independent variable.
Assume alpha = 0.05
Here test statistic follows t-distribution.
Here we can see that all the three variables are significant since p-value for all is less than alpha.
P-value < alpha
Reject H0 at 5% level of significance.
We get significant result about t-test.
SUMMARY OUTPUT Regression Statistics Multiple R 0.936056 R Square 0.8762 Adjusted R Square 0.86441 Standard Error 199.8574 Observations 24 ANOVA df SS MS F Significance F Regression 2 5936671 2968336 74.31434 2.98E-10 Residual 21 838802.5 39942.98 Total 23 6775474 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0% Intercept 7899.284 204.8807 38.55554 5.62E-21 7473.212 8325.357 7473.212 8325.357 Number of Production Batches 133.7729 17.36656 7.702902 1.5E-07 97.65716 169.8886 97.65716 169.8886 Units Produced 15.6086 1.423141 10.96771 3.75E-10 12.64902 18.56819 12.64902 18.56819Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.