Heduiry Using Excel to Model Cost Behavior Part 3: Create Scatter plot, regression line, and high-low line using Number of milles driven as the coat driver. Identify any data points you think are outliers by pointing to them with an arrow using the "Insert on the menu bar) and shapes on the toolbar. Use the "Insert lan the menu bar) and textbox"lan the toolbar to label the possible outliers) or to comment that none appear to exist. Even if you see potential outliers, continue to use the full data set in the following analysis Overview The goal of this project is to better acquaint you with the Excel features that will allow you to easily analyze and model cost behavior. In this project you will be helping the manager of Shima Limousine Service better understand how the company's operating casts behave. Shima offers transportation services around the Cleveland area. Your goal is to use the methods discussed in class to help Shima's management generate a cost equation that will be useful for predicting future operating costs . Click on the "Miles Driven" tab at the bottom of the screen. Copy and paste the monthly operating costs into this sheet so that you can perform a similar analysis based on a different cost driver miles driven). Note: Don't calculate the high-low equation for this data set. To add the regression line, place your pointer on any data paint on the graph and RIGHT click the mouse. Choose "Add Trendline", choose "Linear" (the default). To add the regression equation and R value, also choose Display the equation on the chart and Display the R-squared value on the chart." Drag and drop the equation and square to a suitable location an the graph. Part 1: Formatting the data sets and calculating the High-low method using number or los Create a new scatter plat, using "number of miles driven as the cost driver Put it on a new sheet, just like before and then give the sheet an appropriate name. Give the scatter plot a title, label both axes, comment on passible outliers (or comment that none appear to exist), add the regression line and high-low line (both lines streiched back to the y-axis), label the lines, and add the regression equation and Reque, using the same steps described above. Download the Excel file from Blackboard that contains the data sets. You will notice that there are two sheets: one for two potential cost drivers (number of trips made number of miles driven). Click on the tabs at the bottom of the screen to switch back and forth between the data sheets The regression line you see does not automatically stretch back to the y-axis. To make it intersect the y-axis, 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 to the y-axis by forecasting back from your lowest x-value. To do this, insert the lowest x value in the forecast backwards box. You'll find the lowest x value by hovering your cursor over the lowest volume data point and looking at the x value associated with it. Label the line using the Insert Textbox command. Somewhere on the graph answer the following question using the Insert textbox command you were Shima's management, would you use 1) number of trips made, or 2) number of miles driven as the best cost driver for making future cost estimates. WHY?? What helped you reach your decision? Use the formatting options on the toolbar format number tab) to add dollar algna to the cost column and commao to the volume column. Delete any extra decimal places no needed. On the Number of Trips" data sheet, use the High-low method to determine the cost equation that expresses how monthly aperating costs behave in relation to the number of arips made. First, highlight the two months you will be using the highlight icon looks like a spilling paint bucket). Fallow directions on the screen to calculate the variable and forced portions of the equation in the space pravided. Then use "insert textbox" to state the resulting high-low equation and define the x and y variables in the equation (X=?: y = 2). To draw in the high-low line.choose the "Insert then shapes, then click on the straight line from the list of possible shapes. Put your cursor on the highest-volume data point and drag the line through the lowest volume data point, all the way back to the y-axis. Now check: does the forced cost in your high-low equation agree with the line you just drew? It should. Label the line Use the resulting cost equation to predict total operating costs for a month in which 1,000 trips are made. Use the space provided. Use "Insert textbox" to comment on which line looks like it better represents the data points. Explain why the two lines are different. Part 2: Scatter plot, regression line and high-low line using the number of ups Use "Insert textbox to address the following: Highlight all of the volume and cost data with the cursor 1) Predict Shima's operating costs for a month in which 1,000 trips are made. This time, use the regression equation. Compare the result to the prediction you made using the high-low method. . Click on the "Insert tab on the menu bar and then choose "Scatter" as the chart type. Next, dick the plain scatter plot without any lines). You'll now see the scatter plot on the page. Click on "Move chart location on the toolbar to move the chart to a new sheet (so that it's nice and blg). 3) Interpret the R square figure. i. In general, what does the R-square statistic tell management? ii. What is the R-square value from this regression? What does it tell you about this particular set af data and your confidence in using the resulting equation for making predictions? . Click on "Quick Layout on the toolbar and choose "layout 1. Add a descriptive title for the scatter plot and appropriate labels for each axis by clicking on the Chart Title and Axle Titles and typing in your own titles. Delete the Series 1" legend by right clicking on it and then deleting. Rename the sheet by right-clicking on the "Chart 1" tab at the bottom of the screen and choosing "rename". Give the sheet an appropriate name. Cost Number of Trips 705 710 950 High/Low Method Variable cost/unit of activity= Fixed cost = 670 Insert text box BELOW showing resulting H/L equation and fill in the box with highlighting color like this one. Be sure to tell what the x and y variables represent (e.g., X = ?;y= ?) Operating Costs 29965 27910 33503 26312 25160 29605 21496 27320 28247 37511 30974 34987 January February March April May June July August September October November December Two options 1) Hand crunch numbers and input resulting values; OR 504 880 685 610 744 1020 2) Use Excel formulas and cell references to arrive at costs WITHOUT using any actual numbers. 1074 1050 Cost Prediction Number of trips 1,000 For anyone unfamiliar with Excel, a basic arithmatic formula begins with "=" and contains cell references (e.g., B4, C9) rather than numbers. -To multiply, use "*" -To divide, use "/" - To add or subtract FIRST before multiplying or dividing, you must put parentheses around the cell references you wish to add or subtract. Predicted cost January February March April May June July August September October November December Number of Miles Driven 16845 14205 21037 11996 9992 17132 7873 16014 15121 26879 19803 24931