5-43 Interpretation of Regression results: Simple Regression Using a Spreadsheet
Hartman Company's Lucas plant manufactures thermostatic controls. Plant management has experienced fluctuating monthly overhead costs and wants to estimate overhead costs accurately to plan its operations and its financial needs. Interviews with plant personnel and studies reported in trade publications suggest that overhead in this industry tends to vary with labor-hours.
A member of the controller's staff proposed that the behavior pattern of these overhead costs be determined to improve cost estimation. Another staff member suggested that a good starting place for determining cost behavior patterns is to analyze historical data. Following this suggestion, monthly data were gathered on labor-hours and overhead costs for the past two years. No major changes in operations occurred over this period of time. The data are shown in the following table:
Month Labor-Hours Overhead Costs
1 251,563 $2,741,204.00
2 238,438 2,166,231.00
3 192,500 1,902,236.00
4 271,250 2,590,765.00
5 323,750 3,071,812.00
6 290,938 2,618,161.00
7 271,250 2,480,231.00
8 251,563 2,745,558.00
9 231,875 2,211,799.00
10 343,438 3,437,704.00
11 185,938 2,314,436.00
12 231,875 2,550,630.00
13 382,813 3,603,709.00
14 376,250 3,404,786.00
15 290,938 3,016,493.00
16 395,938 3,638,331.00
17 356,563 3,553,886.00
18 323,750 3,191,617.00
19 389,375 3,481,714.00
20 317,188 3,219,519.00
21 343,438 3,495,424.00
22 336,875 3,207,258.00
23 382,813 3,600,622.00
24 376,250 3,736,658.00
A. Use the high-low estimation method to estimate the overhead cost behavior (fixed and variable portions components of cost) for the Lucas plant.
B. Prepare a scattergraph showing the overhead costs plotted against the labor-hours.
C. Use a spreadsheet program to compute regression coefficients to describe the overhead cost equation.
D. Use the results of your regression analysis to develop an estimate of overhead costs assuming 350,000 labor-hours will be worked next month.© BrainMass Inc. brainmass.com October 25, 2018, 4:57 am ad1c9bdddf
See attached file.
Your response is in Excel with three tabs. One tab show you the high-low method using the ...
Your response is in Excel with three tabs. One tab show you the high-low method using the highest and lowest direct labor activity points. An equation for estimating overhead costs is given using high low method. The data is then graphed in Excel. Finally, on the third tab, you find the regression output with comments to guide you in how to interpret the printout. The equation for estimating overhead is given and used to project at 350,000 labor hours.
Cost Behavior and Analysis
Here are records from manufacturer who used direct labor hours as its cost driver:
Month Direct Labor Hours Manufacturing Overhead
Jan. 23,000 $454,000
Feb. 30,000 517,000
Mar. 34,000 586,000
Apr. 26,000 499,500
May 25,000 480,000
Jun. 28,000 515,000
March's costs had machine supplies ($102,000), depreciation ($15,000) and plant maintenance ($469,000). These costs exhibit the following respective behavior: variable, fixed and semi-variable.
The manufacturing overhead figures presented in the table do not include supervisory labor costs, which is step-fixed in nature. For volume levels of less than 15,000 hours, supervisory labor amounts to $45,000. The cost is $90,000 from 15,000-29,999 hours and $135,000 when activity reaches 30,000 hours or more.
a. What are the machine supplies cost and depreciation in January?
b. Using high-low method, analyze plant maintenance cost and calculate the monthly fixed portion and the variable cost per direct labor hou.
c. Assume that cost behavior patterns continue into the latter half of the year. Estimate the total amount of manufacturing overhead the company can expect in November if 29,500 direct-labor hours are worked.
d. Major differences in fixed and step fixed cost
e. If the company has a step fixed cost, where on the step should the firm attempt to operate if it desires to achieve a maximum return on its investment?View Full Posting Details