regression project 2

Warhawk Company produces three products: coffee beans, tea bags, and chai. The following monthly information is available regarding Warhawk’s manufacturing costs and production volumes.

Month

Total Manufacturing Costs

Pounds of coffee beans produced

Number of tea bags produced

Boxes of chai produced

April, 2017

$1,709,880

3,800

30,000

750

May, 2017

$1,708,550

3,350

22,500

1,050

June, 2017

$1,667,130

2,750

15,000

1,200

July, 2017

$2,647,000

4,780

20,300

2,700

August, 2017

$1,918,680

4,330

15,000

1,580

September, 2017

$1,907,030

3,950

21,000

1,800

October, 2017

$1,785,650

3,830

22,500

1,050

November, 2017

$1,569,750

2,530

15,800

900

December, 2017

$1,698,350

2,980

16,500

1,280

January, 2018

$1,904,000

4,250

26,300

1,580

February, 2018

$1,906,530

3,950

21,000

1,800

March, 2018

$1,596,150

3,150

25,500

600

Requirements:

  • Using Excel, prepare a multiple regression analysis to determine the unit cost of each product. (20 points)
  • What is the cost-estimating equation based on your multiple regression analysis?
  • How much of the variation in monthly cost is explained by your cost-estimating equation? Do you think that this equation does a good job estimating production costs? Why or why not. (8 points)
  • Warhawk plans to produce 4,000 pounds of coffee beans, 27,000 teabags, and 800 boxes of chai in May 2018. Using your cost-estimating equation, what is the estimated total manufacturing cost for May 2018? (8 points)
  • You have a special-order opportunity (assume that you have sufficient excess capacity to complete the order). A customer has offered to buy 500 boxes of chair for $425 per box. Should you accept the order? Why or why not. (6 points)

(8 points)

Submission Instructions:

  • Submit your Excel file that contains your multiple regression analysis.
  • Submit your Word file that contains your answers to requirements 2, 3, 4, & 5.