Reference no: EM13301563

Lakeway Enterprises Regression Analysis

Georganna Russell, the controller of Lakeway Enterprises, a fabricator specializing in the manufacture of copper pipe, has undertaken a project to study the behavior of overhead cost. She has assembled data for this month from each of the firm's twenty-eight manufacturing facilities. In addition, all the allocated components of the factory overhead account, such as head office costs, have been eliminated from the factory overhead account balances.

Georganna has asked you to develop a regression model to predict the level of manufacturing overhead based on the data in Table 1.

Required:

• Determine the best regression equation possible given the data in Table 1 and the fact that overhead is to be the dependent variable. You should indicate why the regression model which you selected was determined to be the best and why other models were rejected. Factors which you should consider in your assessment of potential regression models include but are not limited to the following items: (1) whether the independent variable(s) can be used to predict the dependent variable; (2) the greatest amount of confidence which can be placed in the regression equation; and (3) the significance of the coefficients of determination and correlation.
• Discuss key assumptions and limitations of the regression models which you considered.
• Make a cost prediction using the regression equation which you selected if Susan expects to incur 2,000 labor-hours, 1,000 machine-hours, 500 tons of raw materials handled, and 70 production line setups. Next, if management desires to be at least 95 percent certain that actual costs will not exceed their estimate for overhead costs, what would be their estimate of overhead? Lastly, if management desires to be 90 percent certain of the actual overhead cost, what would be their estimate of overhead?
• Discuss the problems of estimating overhead cost at the following projected activity level: 3,000 labor hours, 500 machine hours, 700 tons of raw materials handled, and
  100 production line setups.

You are touse the Excel computer program and include the printout for all regression models considered.

Plant Number	Factory Overhead	Labor Hours	Machine Hours	Tons of Raw Material Handled	Production Line Setups
1	137,896	2,092	959	414	67
2	174,342	1,617	1,227	623	88
3	168,896	2,215	1,351	437	50
4	178,059	1,584	1,480	479	89
1	166,605	1,930	952	678	52
2	165,320	1,717	986	666	50
7	157,585	2,319	931	585	75
8	165,667	2,312	1,439	479	90
9	155,657	1,880	945	619	94
10	144,605	1,723	869	489	60
11	157,608	1,992	1,171	445	56
12	171,700	2,476	1,228	581	75
13	140,686	2,087	928	446	61
14	171,982	2,256	950	688	99
15	155,252	2,179	1,016	580	78
16	140,793	1,806	902	464	60
17	154,377	1,671	948	610	61
18	150,886	2,019	1,130	532	89
19	159,198	1,585	1,335	415	98
20	145,379	1,747	1,052	517	91
21	152,614	1,618	860	640	64
22	159,450	2,122	1,188	548	61
23	160,983	1,697	1,254	425	56
24	175,393	2,406	1,187	695	58
25	153,031	1,917	948	468	97
26	166,110	1,658	1,015	660	74
27	150,041	2,042	971	478	51
28	170,419	1,757	1,111	652	85

Table 1: Lakeway Enterprises

Attachment:- Assignment.rar