LAB ASSIGNMENT #5 CHAPTER 5 OBJECTIVES: Compare high low and regression analysis approaches to 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. Manufacturing Overhead (Y) Number of Batches Month January 309 $84,000 February 128 $41,000 249 $63,000 March April 159 $44,000 May $44,000 216 June 174 $48,000 July August 264 $66,000 162 $46,000 September $33,000 147 October 219 $66,000 $81,000 November 303 December 106 $41,000 Manufacturing Overhead (Y) Month Number of Batches (X) 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 BUILT IN EXCEL FUNCTIONS FOR SLOPE AND INTERCEPT-write formulas to calculate the high-low estimates. Enter the resulting cost equation in the form ya+bx in the space provided (this does not have to be an Excel formula.) Using your answers from # 1, project total Manufacturing Overhead 2. 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 vou 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 don't 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 and click "GO In the add-ins available box, select the "Analysis Toolpak" check box and then click "OK t the bottom f the screen, select "Excel add-ins" 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) Uncheck the box "labels" ii Under "Output" select new worksheet-do not select output range as illustrated in the text) Do not select any of the options under "Residuals" or "Normal Probability iv 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 eg ability in MOH cost is explained by C. 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 Highlight all X and Y data (except totals). A 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). You'll 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." 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 E 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 doesn't interfere with the line information agree to your summary output and cost equation in Part 2? It should! i. To make it extend backwards,, put vour 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 line data points if necessary.) Does the back to the y-axis. line doesn" -th elore, you ed lo see what your lowest X value is on want to go back from your lowest x-value to x-0.) Now look at your graph-the regression line should stretch all the way back to the y-axis. NOTE: If you are working on a Mac, you may not be able to extend the regression line to the y-axis. 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 an appropriate chart title), Your chart should have a professional appearance and be easy to interpret by anyone looking at it NAME: LAB #5 Manufacturing Overhead (Y) Number of Batches (X) Month $84,000 $41,000 $63,000 $44,000 $44,000 $48,000 January 309 February March 128 249 April May June 159 216 174 July August September $66,000 264 $46,000 162 $33,000 147 $66,000 $81,000 October 219 November 303 $41,000 December 106 TOTAL $657,000 2,436 #1 SLOPE INTERCEPT COST EQUATION #2 COST FOR 300 BATCHES