GENERAL INSTRUCTIONS Data should be analyzed in Excel. Everything should be appropriately labeled. Simple Regression: Examine...

GENERAL INSTRUCTIONS

Data should be analyzed in Excel. Everything should be appropriately labeled.

Simple Regression: Examine the relationship between the number of units produced and the total manufacturing overhead by performing Regression Analysis and generating a “Line-fit” plot. Highlight the following in YELLOW on the spreadsheet:

What is the coefficient of correlation and what does it say about the relationship?
What is the coefficient of determination and what does it say about the relationship?
What is the equation of this line? ( y = mx + b), where Total Cost = (Variable Cost Per Unit * Units) + Total Fixed Cost.
Multiple Regression: Examine the relationship between the number of units produced and the number of production batches and their effect on total manufacturing overhead. What is the coefficient of determination? How did multiple (versus single) regression help/hurt the r-squared?
Break-Even Analysis: Calculate the following, assuming Simple Regression (show work in the Break-Even tab):
1. What is the average per Unit Direct Materials Cost?
2. What is the average per Unit Direct Labor Cost?
3. Assume variable General and Administrative expenses are $12 per unit. What is the total variable cost per unit?
4. Assume total fixed General and Administrative expenses are $2,000 per month. What is the total fixed cost per month?
5. Assume sales price per unit is $400. What is the Contribution Margin Per Unit?
6. What is the Break Even Point in Units?

Expert Solution

Given that

1) The data was entered in excel and analysis was performed using 'Data Analysis' toolkit. The regression analysis can be show as follows: flitted line plot obtained is as follows::

a) The correlation coefficient is 0.7255 and it suggests that number of units produced and the total manufacturing overhead are positively correlated with a high magnitude.

b) The coefficient of determination is 0.5264 or 52.64% and it suggests that the model explain approximately 52% of the variability in the data.

c) The equation of the line is as follows:

Units produced=-319.473+0.0402(Manufacturing overhead)

orchestra answered 2 years ago

Please answer each question set on a separate Excel worksheet, labeled appropriately and include excel Formulas...

Please answer each question set on a separate Excel worksheet, labeled appropriately and include excel Formulas Question Set 1. You are in charge of quality control for computer monitors at Dell. You have data on twenty-five batches of monitors, tracking five types of defects: brightness, color, contrast, dead pixels, and stuck pixels. These data are given in the table below. 1. For each defect type, find the average number of defects per batch. So, you should have an average defect...

Use the data set which is labeled below and answer using regression output in excel i....

Use the data set which is labeled below and answer using regression output in excel i. Find the correlation coefficients between Y and X1, X2 and X3 and test the significance of population correlation coefficient using the value of r calculated for X2 and X3. ii. Estimate the regression equation for Y and X1, Y and X2 and report the results and explain the intercept and slope coefficients. iii. Check the significance or insignificance of the independent variables? and explain...

Use Excel to estimate a simple linear regression model for the following data (Y is a...

Use Excel to estimate a simple linear regression model for the following data (Y is a dependent variable and X is an independent variable): Y X 0 -2 0 -1 1 0 1 1 3 2 Fill in Multiple Blanks: What is the slope of the estimated line? In your answer, show one (1) digit to the right of the decimal point, for example, 1.0, 1.2. Apply the appropriate rounding rule if necessary. What is the Y-intercept?

Prepare an amortization spreadsheet in Excel. The sheet should be labeled, and I should be able to...

Prepare an amortization spreadsheet in Excel. The sheet should be labeled, and I should be able to change purchase price, interest rate, or the other relevant factors and the spreadsheet should automatically update. As we discussed in class, spend some time labeling the spreadsheet and using proper cell references. This is the first but not the last spreadsheet of this type, it is highly likely that elements of this spreadsheet will be helpful in subsequent assignments, so time spent here may mean...

Please answer each question set on a separate Excel worksheet, labeled appropriately. (Include all formulas) Question...

Please answer each question set on a separate Excel worksheet, labeled appropriately. (Include all formulas) Question Set 1. You are in charge of quality control for computer monitors at Dell. You have data on twenty-five batches of monitors, tracking five types of defects: brightness, color, contrast, dead pixels, and stuck pixels. These data are given in the table below. 1. For each defect type, find the average number of defects per batch. So, you should have an average defect rate...

In the Excel data file, the tab labeled Question 1 contains data on the number of...

In the Excel data file, the tab labeled Question 1 contains data on the number of times boys and girls raise their hands in class. Conduct the t-test: Two-Sample Assuming Equal Variances. Males 9,8,4,9,3,8,10,8,9,10,7,6,12 Females 3,5,1,2,6,4,3,6,7,9,7,3,7,6,8,8 a. What is the null hypothesis? b. What is the research hypothesis? c. Why run a Two-Sample Assuming Equal Variances t-test? d. Interpret the findings. What are the results of the hypothesis test? Can you reject the null hypothesis?

Instructions: All the questions should be answered with a graph Graphs should be made in excel...

Instructions: All the questions should be answered with a graph Graphs should be made in excel and pasted in the word Explanation must be handwritten not typed The assignment should be very neatly done or else it will not be considered for evaluation. Q1. Explain law of demand and Supply with two separate graphs. Q2. What is Contraction and Expansion for Demand and Supply. Explain with a Demand schedule and graph. Q3. Consider Product X is Car. With help of graph...

The following data contains the characteristics of 30 motorcycles that will be analyzed using a Regression...

The following data contains the characteristics of 30 motorcycles that will be analyzed using a Regression model. You will study the relationship between a motorcycles' engine power (measured in horsepower) and the motorcycles' weight (measured in pounds). Power (cf): 145 215 165 85 225 87 175 210 158 86 150 150 76 170 208 150 215 225 220 153 175 130 140 165 180 150 190 110 150 86 Weight (lb): 4082 4735 3693 2310 4951 2672 4464 4382 4363...

Using Excel generate a simple regression model with Y as the dependent variable and X1 and...

Using Excel generate a simple regression model with Y as the dependent variable and X1 and X2 as the independent variables in the attached spreadsheet. Write the following from the output: Intercept: Coefficients of Independent variable: R-square: Significance F: Based on the model generated, forecast profits for a firm with X1= Based on the model generated, forecast profits for a firm with x1=250 and X2=100. Evaluate the predictability of the model using explanatory language that someone who does not have...

How can you perform simple linear regression analysis using Excel?

Question