Overview The goal of this project is to better acquaint you with Excel features that will allow you to easily analyze and model cost behavior in your future careers. In this project 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 Charlotte 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. I Part 1: Formatting the data set and calculating the High-low method using number of trips 1. 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: 1) Find the MAX and MIN values to determine the correct months to use. HIGHLIGHT the two months you will using in NEON YELLOW (the highlight icon looks like a spilling paint bucket). u) Follow directions on the screen to calculate the variable and fixed portions of the equation in the space provided. Then use "Insert textbox" to state the resulting high-low equation and define the x and y variables in the equation (x = ?; y = ?). Fill in textbox light green ) Use the resulting cost equation to predict total operating costs for a month in which 1,000 trips are made. Use the space 8 3 1 2 3 provided Part 2: Create Scatter plot, regression line and high-low line using the number of trips 4. Highlight all of the volume and cost data with the cursor. Click on the "insert" tab on the menu bar and then choose "Scatter" as the chart type. Next, click 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 big). 5. Add a descriptive chart title and axis labels. The chart title should tell the reader exactly what is being analyzed. 6. Identify any data points you think are outliers by polnting to them with an arrow 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. 7. 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 and R2 value, also choose "Display the equation on the chart" and "Display the R-squared value on the chart." Drag and drop the equation and r-squared statistic to a suitable location on the graph. 8. The regression line you see does not automatically stretch back to the y-axls. 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. 9. 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-axls. Now check: does the fixed cost in your high-low equation agree with the line you just drew? It should. Label the line. 10. Use "insert" "textbox" to comment on which line looks like it better represents the data points. Also explain why the two lines are different. 11. 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. Insert a text box to comment on your prediction and comparison. . 3 0 1 2 3 12. Interpret the R square figure, 1. In general, what does the R-square statistic tell managers? II. What is the R-square value from this regression? What does it tell you about this particular set of data and your confidence in using the resulting equation for making predictions? 13. 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. 14. Move worksheet so that it follows the number of trips" worksheet. 25 Part 3: Create Scatter plot, regression line, and high-low line using Number of miles driven as the cost driver. 15. Click on the "Miles Driven" tab at the bottom of the screen. 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). Note: Don't calculate the high-low equation for this data set. 16. 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 worksheet after Miles driven worksheet. 17. Give the scatter plot a title and label both axes. 3 18. Comment on possible outliers (or comment that none appear to exist), 19. Add the regression line, stretch it back to y-axis, and label it. Add the regression equation and R-squared. 1 33 2 20. Add the high-low line and labelit. 21. Insert a texbox 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? 96 97 22. Check your work against the grading rubric and then upload your file to the website. 95 98 99 Number of Trips Cost 705 High/Low Method Variable cost/unit of activity Fixed cost January February March April May June July August September October November December Operating Costs 29965 27910 33503 26312 25160 29605 21496 27320 28247 37511 30974 34987 "Insert text boxe directly BELOW. Write out resulting Hequation. Be sure to tell what the x and y variables represente. 7. Fill in the textbox color light green luse spilling point bucket You must use formulat and cell references in these boxes 710 950 670 504 880 685 610 744 1020 1074 1050 -To multiply use To divide, use To add or subtract FIRST before multiplying or diding you must put parentheses around the cel references you wish to add or subtract MAX MN To reference scellanen in the destination cell and then click on the cell that contains the Information you want in the destination cell Presserter Cost Prediction Number of trips 1.000 Predicted cost FINALLY, If you are unfamiliar with release plan time to learn it (ether by yourself or in a casal. You will need to know ace in your fut business career, I am always happy to answer Questions on how to use the features Use the MAX and MN functions to find the high and low months for your actions. Although we can easily see the hand low months this small data set, want you to learn how to find the Nghest and west value in a lot of numbers that may have thousands of To find Maxtor MINI put cursor in the destination cell, click on fron toolbarthen Type MAX for MINand click enter. When the dialogue box apeni, night the way of cels that you want Excel to search in order to find the maximum (minimum value Highlight spilling paint bucket, neon yellow the months corresponding to the high and low value in a large data set you could use ind select on the home toolbar to find where the values in the list Alternatively, you could use a combination of Minard Mex.Match, and Index functions to return the month with the month name with the Min and Max 1 2 2 4 5 36 Number of Miles Driven 16845 14205 21037 11996 9992 17132 - January February ! March 5 April 5 May June 3 July 3 August 0 September 1 October 2 November 3 December 7873 16014 15121 26879 19803 24931 4
