Sales Price Per Unit: 400.00 Variable Selling and General Expenses per unit Monthly Fixed Selling and General Expenses 2,000 Number of Direct Direct Observation Production Units Manufacturing Machine Material Number Month Batches Produced Overhead 10,991 9,724 9,830 11,006 Year 20x1 20x1 20x1 20x1 20x1 20x1 20x1 20x1 20x1 201 20x1 20x1 20X2 20X2 20X2 20X2 20X2 202 Hours 1,557 1,259 970 3,703 3,059 103 3,674 4,723 10,413 1,235 2,850 4,028 4,280 5,750 2,530 10,310 1,808 3,822 4,992 3,520 123 3,653 10,403 11,307 9,716 2,970 3,750 125 1,479 955 9,622 10,237 11,120 10,311 10,103 10,777 9,822 10,527 6 1,483 1,597 1,715 2,448 3,683 3,802 3,630 5,530 4,453 3,039 4,197 1,330 4,083 2,006 2,698 1,507 473 1,493 578 10,697 9,767 10,241 9,695 10,746 10,790 3,587 1,050 3,480 1,294 2,087 1,727 1,697 2,146 3,802 3,420 20X2 6 202 692 2,419 3,066 20X2 20X2 6 1,498 1,615 4,697 Cost Behavior Analysis using Regression GENERAL INSTRUCTIONS Data should be analyzed in Excel. Everything should be appropriately labeled 1. 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: a. What is the coefficient of correlation and what does it say about the relationship? b. What is the coefficient of determination and what does it say about the relationship? c. 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? 2. 3. Break-Even Analysis: Calculate the following, assuming Simple Regression (show work in the Break- Even tab): a. b. c. What is the average per Unit Direct Materials Cost? What is the average per Unit Direct Labor Cost? Assume variable General and Administrative expenses are $12 per unit. What is the total variable cost per unit? Assume total fixed General and Administrative expenses are $2,000 per month. What is the total fixed cost per month? Assume sales price per unit is $400. What is the Contribution Margin Per Unit? d. e. hat is the Break Even Point in Units