Mickey Toys Company is attempting to determine the behavior of setup costs for its Minneapolis Plant. Two possible activity drivers have been mentioned: direct labor hours and number of batches produced. The plant controller has accumulated the following data.

Month Overhead Direct Labor Number of

Cost Hours Batches

Feb. 7,700 2,000 55

Mar. 7,690 2,100 50

April 10,400 3,100 75

May 9,400 2,700 62

June 9,584 2,800 65

July 8,680 2,500 58

Aug. 8,550 2,400 56

Sept. 9,735 2,900 66

Oct. 10,500 3,000 80

Required:

1. Prepare a Scattergraph by plotting Overhead costs (y axis) against Direct Labor Hours (x axis).

(Hint: Use the Help function in Excel to figure out how to draw a Scatter graph. Make sure to label the axes and choose the scale such that the graph is nice. PRESENTATION carries points).

a. Fit a line to the data; select any two points and determine the cost equation for Overhead costs.

(Hint: Once you fit the line, choose any 2 points on the fitted line and then compute the slope and intercept for the fitted line. The slope and intercept enable you to determine the cost equation. Note that the line you fit on the scatter graph is NOT the regression line.)

b. Using a high-low method, compute the cost formula for Overhead with Direct Labor Hours as the activity.

(Hint: You do not need the graph for this. Simply use the method discussed in the class. Also, there is no function is Excel that will do this. You can use the cell references to compute the rise and the run etc.).

c. Estimate a regression equation with direct labor hours as the activity driver. If the Minneapolis plant forecasts 2,600 direct labor hours for November, what will be the estimated overhead cost?

(Hint: Use the computational method shown in the book or Excel function to estimate regression equations. Its easier to use the SLOPE and INTERCEPT functions of Excel. Look at help on how to use these two functions. Excel gives you the slope and intercept, from which you can write down the cost formula).

2. Repeat parts a, b, and c with number of batches as the activity driver. If the Minneapolis plant forecasts 85 batches for November, what will be the estimated overhead cost?

3. Which of the two regression equations (from part 1 & 2) does a better job of predicting overhead costs? Explain why.