Acc327 Homework - Regression Problem #1

ACC327 Fall 2007 Regression Problem #1

Due: 09/05/2007 The management of Bogus Corporation has identified two possible cost drivers (labor hours and machine hours) for utility cost. The controller has asked you to analyze the data and select the best cost driver from the two candidates. Data Month

Utility Cost Jan $ 2,527 Feb 2,515 Mar 2,654 Apr 2,593 May 2,455 June 2,509 July 2,557 Aug 2,588 Sept 2,540 Oct 2,707 Nov 2,731 Dec 2,697 Total 31,073

Labor Hours Machine Hours 4,150 2,500 4,000 2,375 4,360 2,600 4,200 2,450 4,050 2,525 4,100 2,410 4,275 2,720 4,250 2,525 4,050 2,350 4,500 2,610 4,600 2,700 4,375 2,675 50,910 30,440

Required: For each possible cost driver (Labor Hours and Machine Hours) use Excel to: 1 Prepare a scatter plot against utility cost. Be sure to label each axis. 2 Add a trend-line to the plot. 3 Does the plot indicate that the assumption of a linear relationship between the possible cost driver and Utility Cost is reasonable? Explain. 4 Based on the plot, would you expect that the cost driver is a "good fit"? Explain. 5 Print your plots and answers to the questiojns on separate pages in Excel.