R. Saltzman DS 412 Capacity Planning Assignment Operations Management Name __ Due Date: Thurs., 2/16/17 This assignment uses least squares regression to find the total cost function for a manufacturing process. This equation will then be used with revenue information to determine the process's break-even point. Gizmo Game Co. has developed a new process to manufacture its latest children's game, which they customize for each order. So far, Gizmo has produced 17 batches of games to meet 17 different orders. The batch size of each order and the total cost of producing the batch are shown in the table below. 1. Make a scatter (XY) chart of Total Cost (Y) vs. Batch Size (X). 2. Find the least squares regression equation that gives Total Cost as a linear function of the number of games produced in a batch. The least squares regression equation here is: 3. Accurately plot the regression line on your scatterplot either by hand (carefully) or by using the Excel's Add Trendline tool. 4. Based on the regression equation, what is the: 4a) fixed cost of producing a batch? 4b) variable cost of producing a game? 5. The strength of the linear relationship between Total Cost and Batch Size is weak, moderate or, strong because (support your answer with a number) _____________________________. Batch Size (# games) 20 35 20 45 40 25 70 50 15 60 80 55 95 60 70 50 100 Total Cost $1,245 $1,640 $1,500 $1,600 $1,950 $1,000 $2,425 $2,100 $820 $2,025 $2,200 $2,100 $2,625 $2,165 $2,000 $1,450 $2,920 6. Suppose that Gizmo sells each game for $33.95. Assuming your regression equation above accurately reflects their total costs, calculate the: 6a) BEPx = Break-even point in games: 6b) BEP$ = Break-even point in dollars: 7. Gizmo receives an order 85 games which they plan to make in one batch. What is the expected: 7a) Total cost of this batch? 7b) Total profit from this batch? *** Along with this page, please turn in a print out of your scatter chart. R. Saltzman DS 412 Capacity Planning Assignment Operations Management Name __ Due Date: Thurs., 2/16/17 In an Excel worksheet, copy the data for the two variables into two adjacent columns. To get an scatter (X, Y) chart of the data: 1. Select the cells in two columns where the data are. 2. Insert tab > Scatter (X, Y) chart ... 3. Select the chart and then use functions on the Layout tab to add Chart and Axis titles, etc. To get the correlation coefficient, there are two ways: 1. Use the CORREL(x-range, y-range) or CORREL(y-range, x-range) cell function, or 2. Data tab > Data Analysis > Correlation In the Correlation dialog box: Input Range: Click & drag the cursor over values in BOTH columns of data Grouped by: Select radio button for Columns Output Options: Specify where you want the results to appear Click on OK button To get the least squares regression equation, there are three ways: 1. Use the INTERCEPT(y-range, x-range) and SLOPE(y-range, x-range) cell functions, or 2. Right click on any of the points in the scatter chart and select \"Add Trendline...\" Then select the Linear trend/regression type, and check the box near the bottom of the dialog box that says \"Display Equation on chart,\" or 3. Data tab > Data Analysis > Regression In the Regression dialog box: Input Y Range: Click & drag the cursor over the values in the y data column only Input X Range: Click & drag the cursor over the values in the x data column only Output Options: Specify where you want the regression output to appear Residuals: optionally check 1 or more of the boxes, e.g., Residual Plots Click on OK button Note: The a & b of the regression equation are given in the first 2 rows below \"Coefficients\