Refer to the data in the preceding problem for Nantucket Marine Supply.

In the preceding problem, Nantucket Marine Supply is a wholesaler for a large variety of boating and fishing equipment. The company’s controller, Alan Denney, has recently completed a cost study of the firm’s material-handling department. The activity measure (independent variable) used in the study was hundreds of pounds of equipment loaded or unloaded at the company’s loading dock. Denney compiled the following data.

Required:
1. Construct an Excel spreadsheet and use the Excel commands to perform a least-squares regression. Estimate of the variable- and fixed-cost components in the company’s material-handling department costs.
2. Write the least-squares regression equation for the department’s costs.
3. Predict the firm’s material-handling department’s costs for a month when 2,250 units of activity are recorded.
4. Why do the three cost predictions computed in this and the preceding problem differ? Which method do you recommend? Why?
5. Use your Excel spreadsheet from requirement (1) to compute the R2 value for the regression. Interpret the R2value.

Transcribed Image Text:

Units of Activity (hundreds of pounds of equipment loaded or unloaded) Material-Handling Department Costs $11,700 11,300 11,250 10,200 11,100 12,550 12,000 11,400 12,120 11,050 11,350 11,350 Month January Februaryrr 1,800 1,600 1,300aiauarri 1,000 2,200 2,400 2,000 1,800 2,600 1,100 1,200 1,400 April May June .... July August September . October November. December...