Include excel work

Vice President for Sales and Marketing at Waterways Corporation is planning for production needs to meet sales demand in the coming year. He is also trying to determine how the company's profits might be increased in the coming year. This problem asks you to use regression and cost-volume-profit concepts to help Waterways understand contribution margins of some of its products and decide whether to mass-produce any of them. Instructions (a) Historical information regarding Waterway's annual mixed costs related to a simple water control and timer that it mass-produces is included below. Using Excel: I. Create a scatter plot of the units and total cost data provided and add the cost equation line (right click on the scatter plot and select 'add trendline'. Under Forecast in the Backward field, enter your lowest value of your ' X ' variable.) II. Complete a regression analysis to split the mixed costs into fixed and variable components (round your slope to two decimal places) and write the cost equation. (Check-in with your TA to ensure the correct cost equation before moving to part (b)) (b) (You MUST factor in the fixed/variable costs you calculated in part (a) to your work in part (b).) Waterways estimates that in 2019 it will sell 696,000 units of the simple water control and timer at an average selling price of $4.20 per unit. Expected variable costs per unit, not including mixed costs, are $2.17 and expected fixed costs, not including mixed costs, are $643,737. I. What is the product's contribution margin ratio? (Round to nearest whole percentage) II. What is the company's break-even point in units and in dollars for this product? III. What is the margin of safety, both in dollars and as a ratio? (Round to nearest whole percentage) IV. If management wanted to increase its income from this product by 10%, how many additional units would have to be sold to reach this income level? V. If sales increase by 51,000 units and the cost behaviors do not change, how much will income increase on this product