LAB ASSIGNMENT #4 CHAPTER 5 OBJECTIVES: Compare high low and regression analysis in analyzing mixed costs Use regression and graphing functions in Excel PART 1 Jurgenson Industries has compiled the data below concerning manufacturing overhead cost for its first year of operations. They believe that overhead cost is closely related to the number of batches processed and would like to develop a way of projecting overhead cost for budgeting and future operational decision-making. The data has been entered on your template and should be used for all 3 parts of this assignment. Month Number of Batches (X) Manufacturing Overhead (Y) January 309 $84,000 February 128 $41,000 March 249 $63,000 April 159 $44,000 May 216 $44,000 June 174 $48,000 July 264 $66,000 August 162 $46,000 September 147 $33,000 October 219 $66,000 November 303 $81,000 December 106 $41,000 TOTAL 2,436 $657,000 1. Use the high-low method to calculate the cost equation relating to Manufacturing Overhead and number of batches for the data above by writing Excel formulas to calculate the slope (variable cost) and intercept (fixed cost) in the spaces provided on your worksheet. Round your answers to the nearest one dollar. DO NOT USE THE EXCEL FUNCTIONS FOR SLOPE AND INTERCEPT. Enter the resulting cost equation in the form y=a+bx in the space provided (this does not have to be an Excel formula.) 2. Using your answers from #1, project total Manufacturing Overhead costs in a month in which 300 batches are processed and enter your answer in the space provided. PART 2 1. REGRESSION ANALYSIS Following are directions for this part of the assignment for Excel 2010. If you are using other versions of Excel, the instructions should be almost identical. These procedures are also illustrated in your text. Regression Analysis A. Click on the Data tab on the menu bar. B. Next click on Data Analysis. If you dont see this on your menu bar, follow the directions for add-ins below before continuing. DIRECTIONS FOR ADD-INS: Click the File tab in the upper left hand corner) and then click Options at the bottom Click add-ins In the Manage box at the bottom of the screen, select Excel add-ins and click GO In the add-ins available box, select the Analysis Toolpak check box and then click OK If asked, click yes to install. Once you install it, the data analysis tab will always be available from the data tab on your toolbar C. Select regression from the list of data analysis tools, then click OK D. Follow the instructions on the screen: i. Under Input - Highlight or type in the y-axis data range (this is your cost data); Highlight or type in the x-axis data range (this is your volume data) ii. Uncheck the box labels iii. Under Output select new worksheetdo not select output range as illustrated in the text) iv. Do not select any of the options under Residuals or Normal Probability 2. The output will appear on a new sheet in your workbook. Right click on the sheet and select rename. Rename the sheet to REGRESSION 3. Below the output, answer the following and label your answers (round to the nearest one dollar) a. the cost equation, based on the regression output (this will not be an Excel formula) b. use Excel to project MOH cost at an activity level of 300 batches using the regression estimates c. what percentage of the variability in MOH cost is explained by the change in the number of batches processed? PART 3 Again, the directions below are provided for Excel 2010 and should be virtually identical for other versions of Excel. 1. Scatter plot on your High-Low worksheet: A. Highlight all X and Y data (except totals). B. Click on the Insert tab on the menu bar, and then choose Scatter as the chart type. Next, click on the plain scatter plot (without any lines). Youll see the scatter plot on your screen. (Make sure the volume data is on the x-axis and the cost data is on the y-axis) C. Choose Move Chart Location from the chart menu tab and select new sheet in the display window. Name the sheet Scatter Plot. E. Customize your graph as follows: i. Add the regression line to the scatter plot by placing your pointer on any data point on the graph and RIGHT click your mouse. Choose ADD TRENDLINE, choose LINEAR. At the bottom of the same menu box, check mark the box: display the equation. (After you close this box, you can drag the text box to a place on the graph where it doesnt interfere with the line or data points if necessary.) Does the information agree to your summary output and cost equation in Part 2? It should! ii. Note that the regression line doesnt go back to the y-axis. To make it extend backwards, put your pointer on the line and RIGHT click the mouse. Choose FORMAT TRENDLINE. In the FORECAST BACKWARD section of the menu box, enter the lowest x-value from your data set (this is asking you how far back you want to project the linetherefore, 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.) Now look at your graphthe regression line should stretch all the way back to the y-axis. iii. To add labels to the scatter plot and each axis, choose layout under the chart tools menu tab. Customize the labels for the horizontal and vertical axes to reflect your data set and insert an appropriate chart title. Your chart should have a professional appearance and be easy to interpret by anyone looking at it.