Question

Vintage Coffee Co. produces three products: coffee beans, tea bags, and chai. The following monthly information is available regarding Vintage'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 2019	$1,709,880	13,800	15,000	1,500
May 2019	$1,708,550	13,350	11,250	2,100
June 2019	$1,667,130	12,750	7,500	2,400
July 2019	$2,647,000	14,780	10,150	5,400
Aug 2019	$1,918,680	14,330	7,500	3,160
Sept 2019	$1,907,030	13,950	10,500	3,600
Oct 2019	$1,785,650	13,830	11,250	2,100
Nov 2019	$1,569,750	12,530	7,900	1,800
Dec 2019	$1,698,350	12,980	8,250	2,560
Jan 2020	$1,904,000	14,250	13,150	3,160
Feb 2020	$1,906,530	13,950	10,500	3,600
Mar 2020	$1,596,150	13,150	12,750	1,200

1. Using Excel, prepare a multiple regression analysis to estimate total manufacturing costs using coffee, tea bags, and chai.

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. Based on the regression output, are you confident that each of the independent variables affects the total manufacturing costs? Why or why not?

5. Vintage plans to produce 14,000 pounds of coffee beans, 11,000 teabags, and 1,600 boxes of chai in May 2020. Using your cost-estimating equation, what is the estimated total manufacturing cost for May 2020?

6. 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 chai for $225 per box. You normally sell chai for $275 per box. Should you accept the order? Why or why not.

Answer 1

Answers:

step 1)

First Import or type data in Excel then go to the Data tab --then go to Data Analysis Tool pack ....then click on Regression option...then fill the data.

This is the procedure to obtain multiple regression in Excel.

After the following above procedure, the following result summary is obtained

Ans 1)  

prepare a multiple regression analysis to estimate total manufacturing costs using coffee, tea bags, and chai.

.Ans 2)

The cost-estimating equation based on your multiple regression analysis

Total manufacturing cost = 69782.586 + 78.623 * Pounds of coffee beans produced + 14.337 * Number of teabags produced +  199.887 * Boxes of chai produce

.Ans 3)

The coefficient of determination, r2, is a measure of how well the variation of one variable explains the variation of the other and corresponds to the percentage of the variation explained by a best-fit regression line.

Here the value of the coefficient of determination  r2      

0.895 which is very high. Yes, I think that this equation does a good job of estimating production costs.

because the value of r2  near to 1 indicates that a regression line fit the data well, while r2  near 0 indicates a regression line does not fit the data very well.

Ans 4)

Based on the regression output, Yes we are confident that each of the independent variables affects the total manufacturing costs except only boxes of chai procedure. because every other independent variable is significant based on p-value except only boxes of chai procedure is not singnificant.