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.

Paste BI UV OVAv Merge & Center v $ ~ % Clipboard Font Alignment Nu D30 X V fx A B D E F G H Number of Operating Trips Costs High/Low Method Cost 2 January 705 $ 29,965 Variable cost/unit of activity= 10 Correct! 3 February 710 $ 27,910 Fixed cost = 20,019 Correct! 4 March 950 $ 33,503 5 April 670 $ 26,312 May 504 $ 25,160 June 880 $ 29,605 8 685 $ 21,496 9 August 610 $ 27,320 10 September 744 $ 28,247 11 October 1,020 5 37,511 12 November 1,074 $ 30,974 13 December 1,050 $ 34,987 14 15 MAX 1,074 Correct! 16 MIN 504 Correct! Cost Prediction using High-Low Method 17 Number of trips Predicted cost 18 1,000 $ 30,219 Correct! 19 20 21 Cost Prediction using Regression 22 Number of trips Predicted cost 23 1,000 $ 33,191 Correct! 24 25 Fixed Cost 14,296 Correct! 26 Variable Cost per unit 5 19 Correct! 27 28 29 30 31 32 33 34 35 36 37 38AutoSave OFF ... Home Insert Draw Page Layout Formulas Data Calibri (Body) 11 AA Paste BI UV DV Av Clipboard Font G28 X V fx A B C D E Number of Miles Driven N January 16845 W February 14205 4 March 21037 5 April 11996 6 May 9992 7 June 17132 8 July 7873 9 August 16014 10 September 15121 11 October 26879 12 November 19803 13 December 24931 14 15 16 17 18 19 20 21 22010 X V fx A B D E 1 GRADING RUBRIC Possible Points Your Points Part 1: Data set and high-low method W N Data properly formatted with $ signs and commas 1 Correct 2 months highlighted 1 Correct use of MIN and MAX functions 1 Correct variable cost per unit using formula and correct fixed cost 2 6 using formula Inserted textbox in light green with correct equation (1) and x and y 2 variables defined (1) Correct Prediction of cost for 1,000 trips using formula( 1) 1 LD DO 10 Part 2: Scatter plot and regression line using number of trips made 11 Scatter plot on its own page with a descriptive title 1 12 Both axes labeled appropriately Possible outliers identified OR comment that no outliers are present 1 Regression line all the way to the y-axis (1); regression line labeled (1) 15 Regression equation shown 1 16 Correct high-low line (1); high-low line labeled (1) 2 Explanation of why lines differ (1) and which is more representative 2 17 (1) Prediction for 1,000 trips using the regression equation (1); 18 Predictions using regression and high-low compared (1) Tab for worksheet with graph renamed (1) 1 Worksheet with graph moved AFTER Number of trips worksheet (1) 20 21 22 Part 3: Scatter plot and regression line using miles driven 23 Format miles driven data using $ (1) and commas (1) 2 Scatter plot on new sheet with a new tab label (1); move after miles 24 driven worksheet (1) 25 Chart title (1) and axes labeled (1) 2 26 Comment about potential outliers (1) N Regression line back to the y-axis(1) and labeled (1) 2 28 Correct high-low line (1) and labeled (1) 2 29 Management choice of cost driver (1) and explanation (1) 2 30 General appearance (up to 5 points off for sloppy presentation, 31 spelling errors, incomplete sentences, etc.) 32 Total Score on project 35 33 34