H M 1 Fitting a cost model using regression analysis 2 The management of Diggerinc, is trying to develop a cost formula (model for its major manufacturing overhead activities. Digger's manufacturing process is highly 3 automated and power costs are a significant manufacturing cost. Cost analysts have decided that power costs are mixed. The costs mot be separated into their fixed and variable components so that the cost 4 behavior of the power usage activity can ebe better understood. Analysts have determined that machine hours drive power usage, thus machine hours are the cost driver for power costs. Nine months of data s have been collected and are presented in the chart below 6 7 Period Machine Hours Power Cost Use the method of least squares in Excel or a similar computer program to estimate a power cost formula 8 January 22,000 $42,500 Copy the summary output below in this worksheet or in a new worksheet. 9 February 28.000 48,500 $17754 24 $1.25x machine hours 10 March 34,000 54,125 11 April 22.400 47,250 12 May 23.200 43.500 b. Estimate a cost model using the above regression analysis 13 June 19,600 37,250 14 July 28.800 46,000 15 August 33,600 60,000 16 September 31,200 50,000 18 Note: For the following requirements, round the variable cost per unit to the nearest cent and the total 19 fixed cost to the nearest dollar. 20 Mint: if you don't see Data Analysis in Excel refer to P223 for the instructions to add it. 21 Copy Summary Output here 23 24 25 H M 1 Fitting a cost model using regression analysis 2 The management of Diggerinc, is trying to develop a cost formula (model for its major manufacturing overhead activities. Digger's manufacturing process is highly 3 automated and power costs are a significant manufacturing cost. Cost analysts have decided that power costs are mixed. The costs mot be separated into their fixed and variable components so that the cost 4 behavior of the power usage activity can ebe better understood. Analysts have determined that machine hours drive power usage, thus machine hours are the cost driver for power costs. Nine months of data s have been collected and are presented in the chart below 6 7 Period Machine Hours Power Cost Use the method of least squares in Excel or a similar computer program to estimate a power cost formula 8 January 22,000 $42,500 Copy the summary output below in this worksheet or in a new worksheet. 9 February 28.000 48,500 $17754 24 $1.25x machine hours 10 March 34,000 54,125 11 April 22.400 47,250 12 May 23.200 43.500 b. Estimate a cost model using the above regression analysis 13 June 19,600 37,250 14 July 28.800 46,000 15 August 33,600 60,000 16 September 31,200 50,000 18 Note: For the following requirements, round the variable cost per unit to the nearest cent and the total 19 fixed cost to the nearest dollar. 20 Mint: if you don't see Data Analysis in Excel refer to P223 for the instructions to add it. 21 Copy Summary Output here 23 24 25
