Question

Coffee Co. produces three products: coffee beans, tea bags, and chai. The following monthly information is available regarding Coffee Co.’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:

1. Using Excel, prepare a multiple regression analysis to determine the unit cost of each product.
2. What is the cost-estimating equation based on your multiple regression analysis?
3. 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.
4. Coffee Co. 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?
5. You have a special-order opportunity (assume that you have enough excess capacity to complete the order). A customer has offered to buy 500 boxes of chai for $425 per box. Should you accept the order? Why or why not

Answer 1