Please write out the steps, thanks!
Requirements in second photo 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 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 chair for $425 per box. Should you accept the order? Why or why not Requirements in second photo 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 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 chair for $425 per box. Should you accept the order? Why or why not
