Excel Function Video: How to use the slope and intercept functions in Excel

Introduction to Slope and Intercept Functions in Excel

• Slope and Intercept functions help in analyzing cost data based on delivery costs and packages delivered.

Slope Function

• Purpose: Calculates the variable cost element from cost data available for multiple periods.

• Steps to Use Slope Function:

  1. Select Cell C11 to display the slope result.

  2. Go to Formulas menu ribbon.

  3. Click on Insert Function from the Function Library tab.

  4. In the Search for a Function field, type "slope" and click Go.

  5. Select the SLOPE function from the list and click Okay.

  6. The Function Arguments Wizard opens:

     • In the Known Ys field, select cells C5 to C9 (total delivery costs).

     • In the Known Xs field, select cells B5 to B9 (packages delivered).

  7. Click Okay to calculate the slope.

• Result: The slope value, $1.25, appears in Cell C11, representing the variable cost per package delivered.

Intercept Function

• Purpose: Determines the y-intercept or fixed cost element from cost data.

• Steps to Use Intercept Function:

  1. Select Cell C13 to display the intercept result.

  2. Go to Formulas menu ribbon.

  3. Click on Insert Function from the Function Library tab.

  4. In the Search for a Function field, type "INTERCEPT" and click Go.

  5. Highlight the INTERCEPT function and click Okay.

  6. The Function Arguments Wizard opens:

     • In the Known Ys field, select cells C5 to C9 (total delivery costs).

     • In the Known Xs field, select cells B5 to B9 (packages delivered).

  7. Click Okay to calculate the intercept.

• Result: Y-intercept value, $2,259, is displayed in Cell C13, representing the total fixed delivery cost amount.

Final Result

• In Cell C11, the formula displayed is: =SLOPE(C5:C9,B5:B9).

• In Cell C13, the formula displayed is: =INTERCEPT(C5:C9,B5:B9).

• Together, these functions allow for a complete analysis of delivery costs and the determination of a cost equation.