Cost Behavior Lab*In this lab you will be helping the manager of Shima Limousine Service better understand how the company's operating costs 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. The company has identified two potential cost drivers: 1) Number of trips taken, and 2) Number of miles driven.Instructions: Places where you need to fill in answers are highlighted in blue. In cases where you see "try again," you will see "Correct!" when you have the right answer.Download the Excel spreadsheet to use with this assignment, save it and name it with your first and last name.Part 1: Formatting the data set and calculating the high-low method using number of trips1. Start with the "Number of Trips" data set.2. Use the formatting options on the toolbar to add dollar signs to the cost column and commas to the volume column. Delete any extra decimal places not needed. 3. Use the High-low method to determine the cost equation that expresses how monthly operating costs behave in relation to the number of trips made: i) Find the MAX and MIN values to determine the correct months to use. HIGHLIGHT the two months you will using in NEON YELLOW. ii) Calculate the variable and fixed portions of the equation in the space provided using formulas. Then use "insert textbox" to create a textbox below your answer. State the resulting high-low equation and define the x and y variables in the equation. For example, if you were predicting maintenance costs per month based on the number of hours worked, your formula would be y = $500 + $1.35x where y is maintenance costs per month and x is hours worked. Fill in the textbox light green. iii) Use the resulting cost equation to predict total operating costs for a month in which 1,000 trips are made. Use the space provided Part 2: Create Scatter plot, regression line and high-low line using the number of trips1. Select all of the volume and cost data with the cursor. Click on the "insert" tab on the ribbon and then choose "Scatter" as the chart type with one set of dots, no lines. You'll now see the scatter plot on the page. Click on "Move chart" on the ribbon to move the chart to a new sheet (so that it's nice and big).2. Add a descriptive chart title and axis labels. The chart title should tell the reader exactly what is being analyzed.3. Identify any data points you think are outliers by adding a RED arrow pointing at them and labeling them as outliers. Use the "insert" "shapes" and "insert" "textbox" to label the possible outlier(s) 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.4. To add the regression line, place your pointer on any data point on the graph and RIGHT click the mouse. Choose "Add Trendline", choose "Linear" (the default). To add the regression equation also choose "Display the equation on the chart." Drag and drop the equation to a suitable location on the graph.5. 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 "Regression Line" using the Insert Textbox command.6. 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 fixed cost in your high-low equation agree with the line you just drew? It should. Label the line.7. Use "insert" "textbox" to comment on which line looks like it better represents the data points. Also explain why the two lines are different. Fill in the box with white to make it easier to read.8. On the Number of Trips worksheet, record the Fixed Cost and Variable Cost per unit in the space provided. Then predict Shima's operating costs for a month in which 1,000 trips are made using the regression equation, and compare the result to the prediction you made using the high/low method. 9. 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 such as No. of Trips Chart. Change the color of the tab.10. Move the worksheet so that it follows the "number of trips" worksheet. (To do so, right click on the worksheet tab and choose "move.") Part 3: Create Scatter plot, regression line, and high-low line using Number of miles driven as the cost driver. 1. Click on the "Number of Miles" worksheet. Copy and paste the monthly operating costs into this worksheet so that you can perform a similar analysis based on a different cost driver (miles driven). Label the Operating Costs, wrapping and bolding the text so the heading is in the same format as Column B. Note: Don't calculate the high-low equation for this data set. Format the numbers with $ and commas like in part 2.2. Create a new scatter plot, using "number of miles driven" as the cost driver. Put it on a new sheet, just like before, and give the tab a new name. Move the worksheet after the Number of Miles worksheet.3. Give the scatter plot a title and label both axes.4. Insert a textbox, and comment on possible outliers (or comment that none appear to exist),5. Add the regression line, stretch it back to y-axis, and label it. Add the regression equation.6. Add the high-low line and label it using a textbox.7. Insert a textbox and address the following question: If 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?8. Check your work against the grading rubric and then upload your file.

Normal No Spacing Heading 1 Heading 2 Title Paragraph Styles Number of Trips Operating Costs High/Low Method Cost January 705 $ 29,965 Variable cost/unit of activity= Try Again February 710 $ 27,910 Fixed cost = Try Again March 950 $ 33,503 April 670 $ 26,312 May 504 $ 25,160 June 880 $ 29,605 July 685 $ 21,496 August 610 $ 27,320 September 744 S 28,247 October 1020 $ 37,511 November 1074 $ 30,974 December 1050 $ 34,987 MAX Try Again MIN Try Again Cost Prediction using High-Low Method Number of trips Predicted cost 1,000 Try Again Cost Prediction using Regression Number of trips Predicted cost 1,000 Try Again Fixed Cost Try Again iable Cost per unit Try AgainHome Insert Draw Page Layout Formulas Data Review View Tell me Calibri (Body) 11 AA 20 ap Wrap Text v Paste I UV - v MVA = E Merge & Center Clipboard Font Alignment B19 X V fx A B D E F G H Number of Operating Trips Costs High/Low Method Cost IN January 705 $ 29,965 Variable cost/unit of activity= Try Again 3 February 710 $ 27,910 Fixed cost = Try Again 4 March 950 $ 33,503 April 670 $ 26,312 6 May 504 $ 25,160 7 June 880 $ 29,605 8 July 685 $ 21,496 9 August 510 $ 27,320 10 September 744 $ 28,247 11 October 1020 $ 37,511 12 November 1074 $ 30,974 13 December 1050 $ 34,987 14 15 MAX Try Again 16 MIN Try Again Cost Prediction using High-Low Method 17 Number of trips Predicted cost 18 1,000 Try Again 19 20 21 Cost Prediction using Regression 22 Number of trips Predicted cost 23 1,000 24 Try Again 25 Fixed Cost 26 Try Again Variable Cost per unit 27 Try Again 28 30 31 32 33 34 35 37