Question

Will Pugh, director of cost operations for MicroPak, wishes to develop an accurate cost function to explain and predict support costs in the company’s printed circuit board assembly operation. Mr. Pugh is concerned that the cost function he currently uses – based on direct labor costs – is not accurate enough for proper planning and control of support costs. Mr. Pugh directed one of his financial analysts to obtain a random sample of 25 weeks of support costs and three possible cost drivers in the circuit-board assembly department: direct labor hours, number of boards assembled and average cycle time of boards assembled. (Average cycle time is the average time between start and certified completion – after quality testing – of boards assembled during a week.) Mr. Pugh wants his analyst to use regression analysis to demonstrate which cost driver best explains support costs.

Week	Assembly Support costs (Y)	Direct Labor Hours (X1)	Number of completed boards (X2)	Average Cycle Time (Hours) (X3)
1	$66,402	7,619	2,983	186.44
2	$56,943	7,678	2,830	139.14
3	$50,337	7,816	2,413	151.13
4	$50,096	7,659	2,221	138.30
5	$64,241	7,646	2,701	158.63
6	$60,846	7,765	2,656	148.71
7	$43,119	7,685	2,495	105.85
8	$63,412	7,962	2,128	174.02
9	$59,283	7,793	2,127	155.30
10	$60,070	7,732	2,127	162.20
11	$53,345	7,771	2,338	142.97
12	$65,027	7,842	2,685	176.08
13	$58,220	7,940	2,602	150.19
14	$65,406	7,750	2,029	194.06
15	$35,268	7,954	2,136	100.51
16	$46,394	7,768	2,046	137.47
17	$71,877	7,764	2,786	197.44
18	$61,903	7,635	2,822	164.69
19	$50,009	7,849	2,178	141.95
20	$49,327	7,869	2,244	123.37
21	$44,703	7,576	2,195	128.25
22	$45,582	7,557	2,370	106.16
23	$43,818	7,569	2,016	131.41
24	$62,122	7,672	2,515	154.88
25	$52,403	7,653	2,942	140.07

Instructions: The questions that follow pertain to the above data set. Enter your responses in the spaces where indicated. This problem will require the use of MS Excel and/or StatPlus to estimate regression models.

Required:

Use regression analysis to estimate separate cost functions for each of the three potential cost drivers (i.e., estimate the regressions separately for X1, X2 and X3, rather than a single regression that includes all three variables) and then respond to the questions below. Make sure that you properly identify your "Y" and "X" variables in your regression program.

In addition to the discussion questions, you will attach your worksheet that includes your output for each of the three regressions.

A) Based on your regression results, enter the cost functions (using good form) for each of the three cost drivers.



B) Based on your analyses of the regression results, which is the best cost driver for support costs? Why?




C) What is the economic interpretation of the best cost function?

Answer 1

Instruction to create regression model in MS Excel
1. On the Data tab, in the Analysis group, click Data Analysis.
2. Select Regression and click OK.
3. Select the Assembly Support costs (Y) Range . This is the dependent variable
4. Select the X1 / X2 / X3 Range. These are the independent variables.
5. Check Labels ( If you have selected label headings also in step 3 & Step 4)
6. Click OK.

Result

Analysis