Problem 2-28A Estimating fixed and variable cost using the regression method Dean and Powell Tax Services Company has 31 branch offices in the nation. Each office has about three to six professional accountants and one to two secretaries. In a busy season, the office manager, who is also a professional accountant, can hire temporary employees for support work such as document filing and typing. Norman Dean, the president, is wondering whether he should expand his business by opening more offices. One of the factors that he is considering is how to estimate office support costs. Andrea Jones, the accountant, collected the following cost data for all 31 offices. Branch Professional Hours Support Costs Branch Professional Hours Support Costs A1 225 $4,241 F2 165 $3,856 A2 113 3,435 G1 358 5,936 A3 387 6,398 G2 471 8,615 A4 412 6,502 G3 492 9,639 B1 258 4,140 G4 328 5,968 B2 146 3,368 G5 359 7,115 B3 275 3,820 G6 174 3,287 D1 364 6,396 H1 394 7,515 D2 190 3,946 H2 386 7,374 D3 484 8,189 I2 279 5,376 D4 251 4,506 I5 314 5,784 D5 377 6,744 J2 283 5,426 E1 264 4,645 J3 198 4,418 E2 169 6,073 J4 226 4,506 E3 338 6,290 J5 341 6,488 F1 437 9,113 RequiredPage 92 The company uses the number of professional hours as the cost driver for office support costs. Use an algebraic equation to describe how total office support costs can be estimated. Use a spreadsheet program to perform a regression analysis. Use office support costs as the dependent variable (Y) and the professional hours as the independent variable (X). Determine the total fixed cost per office and variable cost per professional hour. Identify the R2 statistic provided by the Excel program and explain what it means. Mr. Dean plans to open a new branch office in a Dallas suburb. He expects that the monthly professional hours will be 3,000. Estimate the total office support cost for Mr. Dean. What portion of the total cost is fixed and what portion is variable? Explain how multiple regression analysis could be used to improve the accuracy of the cost estimates.
Problem 2-28A Estimating fixed and variable cost using the regression method
Dean and Powell Tax Services Company has 31 branch offices in the nation. Each office has about three to six professional accountants and one to two secretaries. In a busy season, the office manager, who is also a professional accountant, can hire temporary employees for support work such as document filing and typing. Norman Dean, the president, is wondering whether he should expand his business by opening more offices. One of the factors that he is considering is how to estimate office support costs. Andrea Jones, the accountant, collected the following cost data for all 31 offices.
| Branch | Professional Hours | Support Costs | Branch | Professional Hours | Support Costs |
|---|---|---|---|---|---|
| A1 | 225 | $4,241 | F2 | 165 | $3,856 |
| A2 | 113 | 3,435 | G1 | 358 | 5,936 |
| A3 | 387 | 6,398 | G2 | 471 | 8,615 |
| A4 | 412 | 6,502 | G3 | 492 | 9,639 |
| B1 | 258 | 4,140 | G4 | 328 | 5,968 |
| B2 | 146 | 3,368 | G5 | 359 | 7,115 |
| B3 | 275 | 3,820 | G6 | 174 | 3,287 |
| D1 | 364 | 6,396 | H1 | 394 | 7,515 |
| D2 | 190 | 3,946 | H2 | 386 | 7,374 |
| D3 | 484 | 8,189 | I2 | 279 | 5,376 |
| D4 | 251 | 4,506 | I5 | 314 | 5,784 |
| D5 | 377 | 6,744 | J2 | 283 | 5,426 |
| E1 | 264 | 4,645 | J3 | 198 | 4,418 |
| E2 | 169 | 6,073 | J4 | 226 | 4,506 |
| E3 | 338 | 6,290 | J5 | 341 | 6,488 |
| F1 | 437 | 9,113 |
RequiredPage 92
-
The company uses the number of professional hours as the cost driver for office support costs. Use an algebraic equation to describe how total office support costs can be estimated.
-
Use a spreadsheet program to perform a
regression analysis . Use office support costs as the dependent variable (Y) and the professional hours as the independent variable (X). Determine the total fixed cost per office and variable cost per professional hour. -
Identify the R2 statistic provided by the Excel program and explain what it means.
-
Mr. Dean plans to open a new branch office in a Dallas suburb. He expects that the monthly professional hours will be 3,000. Estimate the total office support cost for Mr. Dean. What portion of the total cost is fixed and what portion is variable?
-
Explain how multiple regression analysis could be used to improve the accuracy of the cost estimates.
*You will use excel to setup a worksheet with your data and run regression analysis.*
Trending now
This is a popular solution!
Step by step
Solved in 6 steps with 3 images
