Analyzing cost behavior using the high-low method. I need help determining the 2 months to use and help with the following steps for each of the prompts

Analyzing Cost Behavior Part 1: Data Set and High-low method Enter the following data on a new Excel Spreadsheet and then save the file. Do not enter commas or dollar signs. Just type in the plain numbers. Once you have typed in the plain numbers, you can format them anyway you want by using the \"Number\" options shown in the middle of the top toolbar. Simply highlight your cost data (using your cursor) and then choose \"currency\" from the drop down list of formatting options (the default is \"general\"). To get rid of the extra decimal places, simply click on the \"decrease decimal\" icon that is directly below the drop down list. Use the comma icon to format the machine hour data with commas. Month January February March April May June July August Sept October November December Number of Batches 309 128 249 159 216 174 264 162 147 219 303 102 Manufacturing Overhead (MOH) $84,000 $41,000 $63,000 $44,000 $44,000 $48,000 $66,000 $46,000 $33,000 $66,000 $81,000 $41,000 Machine Hours 3,800 2,400 2,850 2,100 2,700 2,250 3,800 3,600 1,850 3,300 3,750 2,000 Verify that you have input the data correctly by checking the totals in each column against the totals shown below. Don't check with a calculator. Here's the easy way: simply highlight a column of data with your cursor. Now look at the very bottom right of your screen. Excel tells you the average, count, and sum of the numbers you have highlighted. The sum should agree to the totals shown below: Totals 2,432 $657,000 34,400 Alternatively, you can use the \"auto sum\" () feature. Simply click your cursor on the cell directly below your column of data and then click on the auto sum button shown on the right hand side of the top tool bar. It shows the range of numbers to be added. Click again, and it inserts the sum. Once you know the sum is correct, be sure to delete it. On the data page, use the High-low method to determine the cost equation relating the monthly MOH costs to the number of batches produced. First, highlight the 2 months you will using (the highlight icon looks like a spilling paint bucket). When you calculate the variable and fixed components, be sure to round off the answers to the nearest cent. Write the cost equation and define the variables (x = ?; y = ?). Remember, this is in dollars and cents, so use proper formatting. Circle or box in your answer. Then use your cost equation to predict total MOH costs if 130 batches are produced during one month. Again, circle or box in your answer. Part 2: Scatter plot and regression line using \"Number of Batches\" as the cost driver Follow the directions in the textbook (p. 326 of Braun, 3e) for creating a scatter plot using Excel 2007, 2010, and 2013, as shown below: 1) In an Excel spreadsheet, list the volume data in one column and the associated cost data in the next column. 2) Highlight all of the volume and cost data with the cursor. 3) Click on the \"insert\" tab on the menu bar and then choose \"Scatter\" as the chart type. Next, click the plain scatter plot (without any lines). 4) You'll now see the scatter plot on the page. You can choose \"Move chart location\" on the menu bar and select \"New Sheet\" and \"OK\" to move the chart to a new sheet (so that it's nice and big). 5) From the \"Chart Layout\" (\"Quick Layout\" in Excel 2013) menu tab, choose \"layout 1\". Add a descriptive title for the scatter plot and appropriate labels for each axis by clicking on the \"Chart Title\" and \"Axis Titles\" and typing in your own titles. 6) You can delete the \"Series 1\" legend beside the graph by RIGHT clicking on it and then deleting. Do you see any potential outliers? If so, point them out (circle or point to them). If not, say so on the graph (e.g., no outliers appear to be present). You can use the \"insert\" \"textbox\" on the top menu to write in any comments. Even if you see potential outliers, continue to use the full data set in the following analysis. To add the actual regression line to the scatterplot, place your pointer on any data point on the graph and RIGHT click your mouse. Choose \"Add Trendline.\" Note that the regression line doesn't go back to the y-axis. To make it stretch back to the yaxis, put your pointer on the line and RIGHT click the mouse. Choose format trendline, then look at the forecast backward box. This feature allows you to extend the line back towards the y-axis as far as you want to go. Therefore, you need to see what your lowest x value is on your data set and input that number. In other words, you want to go back from your lowest x value to x=0. So, go ahead and input your lowest x value (lowest number of batches). Then Close. Now look at your graph: the regression line should stretch all the way back to the y-axis. If it doesn't (or if it goes past the y-axis), you didn't input the correct number. Once again, put your pointer on the line and RIGHT click. Choose format trendline and check mark the two boxes: Display the equation on the chart and Display the R-squared value on the chart. Close. The regression equation and R-square should now appear on the graph. Drag the information box to a place on the graph where it doesn't interfere with the line or data points. Visual Check: Does the regression line intersect the y-axis at the level of fixed costs specified by the regression equation? It should. Label the regression line by using \"insert\" tab; \"textbox\". Start the textbox next to the regression line and drag it into a box shape. Then type in the label. Part 3: Generate the regression output Go back to your data set by clicking on the \"Sheet 1\" tab at the bottom of the screen. Follow the directions in the textbook (p. 331 of Braun, 3e) for running a regression analysis using Excel 2007, 2010 and 2013 (shown below): 1) Go to the Excel spreadsheet listing your volume and cost data. 2) Click on the \"Data\" tab on the menu bar. Next, click on \"Data Analysis\". If you don't see it on your menu bar follow the DIRECTIONS FOR ADD-INS given next before continuing. 3) From the list of data analysis tools, select \"regression,\" then \"OK\" 4) Follow the two instructions on the screen: i) highlight the y-axis data range (this is your cost data) and ii) highlight the x-axis data range (this is your volume data). Click \"OK\" DIRECTIONS FOR ADD-INS: (to add the free Data Analysis Toolpak that comes with Excel) 5) Click on the Microsoft Office button (colorful button in upper-left corner of screen) and then click on \"Excel Options\" at the bottom of the box. 6) From the list of options, click \"add-ins\" 7) At the bottom of the screen, select \"Excel Add-ins\" and click \"GO\". 8) From the add-ins available, select \"Analysis Toolpak\" check box and then click \"OK\" 9) If asked, click \"yes\" to install. Type in the following directly below the regression output: 1) The regression cost equation. Remember, these are in dollars and cents, so format correctly and round off to the nearest cent (If you leave extra decimal places, I'll take points off.) Be sure to define the variables (x = ?, y = ?) in your equation. 2) A projection of what the MOH cost would be at an activity level of 130 batches. 3) Interpret the R square figure given in the output. i. In general, what does the R-square statistic tell you? ii. What is the R-square value from this regression and what does it tell you about this particular set of data? iii. What does this R-square tell you about the accuracy of any predictions made using this equation? NOTE: You may have to wrap the text to make sure it all fits on the page. Part 4- High-low line You are now going to add the correct high-low line to the scatter plot, so go back to the Scatter plot by clicking on \"Chart 1\" tab at the bottom of the screen. Remember, the high-low method is based on two data points: the highest volume and lowest volume data points. 1. First, click anywhere on your scatter plot to make sure you have the \"chart tools\" as your top tool bar. Then, on the insert tab, click on shapes. The drop-down menu on insert shapes gives you a lot of shape options. Click on the straight line in the top box. Now, put your cursor on the graph and \"drag\" in a line through the high and low-volume points, and all the way back to the y-axis. 2. You'll know you've got the correct line if it intersects the y-axis at the fixed cost value specified by your high-low cost equation. 3. Label the line as the High-low line, using the \"insert\" tab; \"textbox\". Use the \"insert; textbox\" to type in your explanations for: On the graph, explain why the regression and high-low lines differ. Comment on which line is more representative of the data set. PRINT AND TURN IN THE FOLLOWING PAGES: Page 1: Data set; high-low cost equation; prediction using the high-low cost equation. Page 2: Chart 1 (graphs using number of batches as cost driver). Page 3: Regression output page. Page 4: High-low line. Be sure to put your name at the top of the first page