Using Excel to Model Cost Behavior

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 companys operating costs behave. Shima offers transportation services around the Cleveland area. Your goal is to use the methods discussed in class to help Shimas management generate a cost equation that will be useful for predicting future operating costs.

Part 1: Formatting the data sets and calculating the High-low method using number of trips

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.

Use the formatting options on the toolbar (format number tab) to add dollar signs to the cost column and commas to the volume column.Delete any extra decimal places not needed.

On the Number of Trips data sheet, 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. First, highlight the two months you will be using (the highlight icon looks like a spilling paint bucket). 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 = ?).

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: Scatter plot, regression line and high-low line using the number of trips

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).Youll 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 its nice and big).

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 Axis Titles and typing in your own titles. Delete the Series 1 legend by right clicking on it and then deleting.

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 (on the menu bar) and textbox(on the toolbar) 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.

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 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 r-square to a suitable location on the graph.

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 .Youll 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.

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.

Use insert textbox to comment on which line looks like it better represents the data points.Explain why the two lines are different.

Use insert textbox to address the following:

Predict Shimas 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.

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 of data and your confidence in using the resulting equation for making predictions?

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.

Part 3: Create Scatter plot, regression line, and high-low line using Number of miles driven as the cost driver.

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: Dont calculate the high-low equation for this data set.

Create a new scatter plot, 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 possible outliers (or comment that none appear to exist), add the regression line and high-low line (both lines stretched back to the y-axis), label the lines, and add the regression equation and R-square, using the same steps described above.

Somewhere on the graph, answer the following question using the insert textbox command:If you were Shimas 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?

Grading Rubric- 40 points possible

Points possible (x)	Points
Part 1: Data set and high-low method
1. Data properly formatted with $ signs and commas (1); correct 2 months highlighted? (1)
2. Correct high-low equation with x and y variables defined (2)
3. Prediction of cost for 1,000 trips (2)
Part 2: Scatter plot and regression line using number of trips made
4. Scatter plot on its own page with a descriptive title (2)
5. Both axes labeled appropriately (1)
6. Possible outliers identified OR comment that no outliers are present (2)
7. Regression line all the way to the y-axis; regression line labeled (2)
8. Regression equation and R-squared shown (2)
9. Correct high-low line; high-low line labeled (2)
10. Explanation of why lines differ (2) and which is more representative (2)
11. Prediction for 1,000 trips using the regression equation (2). Predictions using regression and high-low compared. (2)
12. R-square interpretation(3): -In general, what does the R-square mean? -Specific R-square from this regression. What does it tell you about the data set and your confidence in the ability of the resulting equation to make good predictions?
Part 3: Scatter plot and regression line using miles driven
13. Scatter plot with title and axes labeled (2) and comment on outliers (2)
14. Regression line and label (2)
15. Correct high-low line and label (2)
16. Regression equation and R-square (2)
17. Management choice of cost driver and explanation (2)
Total Score on project=

Number of Trips

January 705 29965

February 710 27910

March 950 33503

April 670 26312

May 504 25160

June 880 29605

July 685 21496

August 610 27320

September 744 28247

October 1020 37511

November 1074 30974

December 1050 34987

High/Low Method Cost

Variable cost/unit of activity=

Fixed cost =

Cost Prediction

Number of trips Predicted cost

1,000

Two options:

1) Hand crunch numbers and input resulting values; OR

2) Use Excel formulas and cell references to arrive at costs WITHOUT using any actual numbers.

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.

Number of Miles Driven

January 16845

February 14205

March 21037

April 11996

May 9992

June 17132

July 7873

August 16014

September 15121

October 26879

November 19803

December 24931