Perfect World Landscapes, Inc. (PWL) provides commercial landscaping services. Sandy Brooks, the firm's owner, wants to develop cost estimates that she can use to prepare bids on jobs. After analyzing the firm's costs, Brooks has developed the following preliminary cost estimates for each 1,000 square feet of landscaping Direct material Direct labor (5 direct-labor hrs. @ $10 per hour) Overhead (at $18 per direct-labor hr.) Total cost per 1,000 square feet $400 50 90 $540 Brooks is quite certain about the estimates for direct material and direct labor. However, she is not as comfortable with the overhead estimate. The estimate for overhead is based on the overhead costs that were incurred during the past 12 months as presented in the following schedule. The estimate of $18 per direct-labor hour was determined by dividing the total overhead costs for the 12-month period ($648,000) by the total direct-labor hours (36,000). Regular Overtime Total Month Total OH DL Hrs DL Hrs DL Hrs $ 54,000 January 3,100 2,910 190 February 2,380 47,000 20 2,400 March 48,000 2,210 40 2,250 April 2,800 56,000 2,590 210 3,030 57,000 470 3,500 ay June 65,000 3,240 760 4,000 July 64,000 4,000 3,380 620 3,050 2,760 August 56,000 350 3,400 September 53,000 40 2,800 October 47,000 2,770 30 2,800 November 47,000 2,120 30 2,150 December 2,560 240 2,800 54,000 $ 648,000 3,000 Total 33,000 36,000 Note: Overtime premium is 50 percent of the direct-labor wage rate. Brooks believes that overhead is affected by total monthly direct-labor hours. Required: 1. Determine the overhead formula using least-squares regression using total monthly direct-labor hours. Provide the output, in good form, on Excel, with data. Also, calculate the formula using the high-low method. Show on the Excel file using formulas. Discuss the differences in the two formulas. 2. Assuming the overhead rate developed in the least-squares regression is different from Brooks' preliminary estimate of $18 per direct-labor hour. Explain the difference in the two overhead rates (minimum one paragraph on Excel-check for spelling, etc.). 3. Using the overhead formula that was derived from the least-squares regression, determine a total variable-cost estimate for each 1,000 square feet of landscaping. submit a bid on a 4. Brooks has been asked landscaping project for the city of Greensboro consisting of 60,000 square feet. Brooks estimates that 40 percent of the direct-labor hours required for the project will be for overtime. Calculate the total variable costs that should be included in the bid that Brooks would submit on this project. Use the least- squares regression formula for variable overhead. 5. Should management rely on the overhead formula derived from the least-squares regression as the basis for the variable overhead component of the cost estimate? What are some other potential drivers for PWL's variable costs? Explain your answer (minimum one paragraph on Excel-check for spelling, etc.) Perfect World Landscapes, Inc. (PWL) provides commercial landscaping services. Sandy Brooks, the firm's owner, wants to develop cost estimates that she can use to prepare bids on jobs. After analyzing the firm's costs, Brooks has developed the following preliminary cost estimates for each 1,000 square feet of landscaping Direct material Direct labor (5 direct-labor hrs. @ $10 per hour) Overhead (at $18 per direct-labor hr.) Total cost per 1,000 square feet $400 50 90 $540 Brooks is quite certain about the estimates for direct material and direct labor. However, she is not as comfortable with the overhead estimate. The estimate for overhead is based on the overhead costs that were incurred during the past 12 months as presented in the following schedule. The estimate of $18 per direct-labor hour was determined by dividing the total overhead costs for the 12-month period ($648,000) by the total direct-labor hours (36,000). Regular Overtime Total Month Total OH DL Hrs DL Hrs DL Hrs $ 54,000 January 3,100 2,910 190 February 2,380 47,000 20 2,400 March 48,000 2,210 40 2,250 April 2,800 56,000 2,590 210 3,030 57,000 470 3,500 ay June 65,000 3,240 760 4,000 July 64,000 4,000 3,380 620 3,050 2,760 August 56,000 350 3,400 September 53,000 40 2,800 October 47,000 2,770 30 2,800 November 47,000 2,120 30 2,150 December 2,560 240 2,800 54,000 $ 648,000 3,000 Total 33,000 36,000 Note: Overtime premium is 50 percent of the direct-labor wage rate. Brooks believes that overhead is affected by total monthly direct-labor hours. Required: 1. Determine the overhead formula using least-squares regression using total monthly direct-labor hours. Provide the output, in good form, on Excel, with data. Also, calculate the formula using the high-low method. Show on the Excel file using formulas. Discuss the differences in the two formulas. 2. Assuming the overhead rate developed in the least-squares regression is different from Brooks' preliminary estimate of $18 per direct-labor hour. Explain the difference in the two overhead rates (minimum one paragraph on Excel-check for spelling, etc.). 3. Using the overhead formula that was derived from the least-squares regression, determine a total variable-cost estimate for each 1,000 square feet of landscaping. submit a bid on a 4. Brooks has been asked landscaping project for the city of Greensboro consisting of 60,000 square feet. Brooks estimates that 40 percent of the direct-labor hours required for the project will be for overtime. Calculate the total variable costs that should be included in the bid that Brooks would submit on this project. Use the least- squares regression formula for variable overhead. 5. Should management rely on the overhead formula derived from the least-squares regression as the basis for the variable overhead component of the cost estimate? What are some other potential drivers for PWL's variable costs? Explain your answer (minimum one paragraph on Excel-check for spelling, etc.)