Warhawk Company produces televisions. The following table contains monthly information regarding Warhawk's manufacturing costs, production volumes,...

 

Transcribed Image Text:

Warhawk Company produces televisions. The following table contains monthly information regarding Warhawk's manufacturing costs, production volumes, machine hours, and direct labor hours. Month January February March April May June July August September October November December Total Manufacturing Costs $1,709,880 $1,708.550 $1,667,130 $2,647,000 $1.918,680 $1,907,030 $1,785,650 $1,569,750 $1,698,350 $1.904.000 $1.906,530 $1,596,150 Televisions produced 13,800 13,350 12,750 14,780 14.330 13,950 13,830 12.530 12.980 14,250 13.950 13,150 Number of machine hours 15,000 11.250 7,500 10,150 7,500 10.500 11.250 7.900 8.250 13.150 10.500 12,750 Number of direct labor hours 1,500 2,100 2.400 5,400 3,160 3,600 2,100 1.800 2,560 3.160 3,600 1,200 Requirements: 1. Starting in cell A1, enter the above table in a worksheet named Data. 2. Create a regression that prodicts total manufacturing costs based on the number of televisions produced. a. The regression output must be on a new worksheet named Televisions. b. On the Televisions worksheet, create an input section where Warhawk can enter the expected number of televisions produced for next month. i. Makeup and enter the expected number of televisions to be produced next month. c. On the Televisions worksheet, use the regression results to create a formula that calculates the predicted total manufacturing costs based on the expected number of televisions produced entered in part 26. 3. Create a regression that predicts total manufacturing costs based on the number of televisions produced assuming that there are no fixed costs. a. The regression output must be on a new worksheet named No Fixed. b. On the No Fixed worksheet, create an input section where Warhawk can enter the expected number of televisions produced for next month. i. Makeup and enter the expected number of televisions to be produced next month. c. On the No Fixed worksheet, use the regression results to create a formula that calculates the predicted total manufacturing costs based on the expected number of televisions produced entered in part 3b. 4. Create a regression that predicts total manufacturing costs based on the number of televisions produced, the number of machine hours, and the number of direct labor hours. Assume that Warhawk DOES have fixed costs The regression output must be on a new worksheet named Multiple b. On the worksheet named Multiple, create an input section where Warhawk can enter the expected number of televisions produced, expected number of machine hours, and expected number of direct labor hours for next month. i Makeup and enter the expected number of televisions to be produced, expected number of machine hours, and expected number of direct labor hours for next month. c. On the worksheet named Multiple, create a formula that calculates the predicted total manufacturing costs using the expected number of televisions produced. expected number of machine hours, and expected number of direct labor hours entered in part 4b. 5. The worksheets must be in the following order: Data (first). Televisions, No Fixed, Multiple (last) Warhawk Company produces televisions. The following table contains monthly information regarding Warhawk's manufacturing costs, production volumes, machine hours, and direct labor hours. Month January February March April May June July August September October November December Total Manufacturing Costs $1,709,880 $1,708.550 $1,667,130 $2,647,000 $1.918,680 $1,907,030 $1,785,650 $1,569,750 $1,698,350 $1.904.000 $1.906,530 $1,596,150 Televisions produced 13,800 13,350 12,750 14,780 14.330 13,950 13,830 12.530 12.980 14,250 13.950 13,150 Number of machine hours 15,000 11.250 7,500 10,150 7,500 10.500 11.250 7.900 8.250 13.150 10.500 12,750 Number of direct labor hours 1,500 2,100 2.400 5,400 3,160 3,600 2,100 1.800 2,560 3.160 3,600 1,200 Requirements: 1. Starting in cell A1, enter the above table in a worksheet named Data. 2. Create a regression that prodicts total manufacturing costs based on the number of televisions produced. a. The regression output must be on a new worksheet named Televisions. b. On the Televisions worksheet, create an input section where Warhawk can enter the expected number of televisions produced for next month. i. Makeup and enter the expected number of televisions to be produced next month. c. On the Televisions worksheet, use the regression results to create a formula that calculates the predicted total manufacturing costs based on the expected number of televisions produced entered in part 26. 3. Create a regression that predicts total manufacturing costs based on the number of televisions produced assuming that there are no fixed costs. a. The regression output must be on a new worksheet named No Fixed. b. On the No Fixed worksheet, create an input section where Warhawk can enter the expected number of televisions produced for next month. i. Makeup and enter the expected number of televisions to be produced next month. c. On the No Fixed worksheet, use the regression results to create a formula that calculates the predicted total manufacturing costs based on the expected number of televisions produced entered in part 3b. 4. Create a regression that predicts total manufacturing costs based on the number of televisions produced, the number of machine hours, and the number of direct labor hours. Assume that Warhawk DOES have fixed costs The regression output must be on a new worksheet named Multiple b. On the worksheet named Multiple, create an input section where Warhawk can enter the expected number of televisions produced, expected number of machine hours, and expected number of direct labor hours for next month. i Makeup and enter the expected number of televisions to be produced, expected number of machine hours, and expected number of direct labor hours for next month. c. On the worksheet named Multiple, create a formula that calculates the predicted total manufacturing costs using the expected number of televisions produced. expected number of machine hours, and expected number of direct labor hours entered in part 4b. 5. The worksheets must be in the following order: Data (first). Televisions, No Fixed, Multiple (last)

Answer rating: 100% (QA)

Ans Month 2 January 3 February 4 March 5 April T 6 May 7 June 9 August 10 September 11 Octobea ... View the full answer