Building a Better Price-Volume-Mix on Gross Profit

Additionally, most resources I have found online usually limits itself to Price, Volume, and Mix, which oversimplifies complex managerial needs. Managers are often interested in understanding the effects of discounting, changes in material costs, and value-added expenses, as well as the implications of introducing or discontinuing products, which can disrupt conventional models.

Helper Formulas

• Volume in Current Year at Previous Year Mix:

  • = Total Vol_CY * Mix_PY

  • The hypothetical volume of sales in the current year if the sales mix remained the same as in the previous year, helping isolate the impact of volume changes from mix changes.

• New Product Indicator:

  • = IF (Vol_CY > 0 AND (Vol_PY is blank OR Vol_PY = 0))

  • This logic flags if a product is new to the current year by confirming there are current year sales and no previous year sales.

• Discontinued Product Indicator:

  • = IF (Vol_PY > 0 AND (Vol_CY = 0 OR Vol_CY is blank))

  • This logic flags if a product was discontinued by verifying there were sales in the previous year but none in the current year.

• Active Product Indicator:

  • = NOT ([New?] OR [Discontinued?])

  • This logic flags a product as active if it is neither new nor discontinued, implying that the product had sales in both the current and previous years.

Impact Formulas

• Impact Formulas:

  • New Product Impact:

  • =IF [product is new] THEN Profit_CY

  • Contribution to gross profit from products that were introduced in the current year.

  • Discontinued Product Impact:

  • =IF [product is discontinued] THEN Discontinued Product Impact = Profit_PY * (-1)

  • Loss in gross profit from products that were discontinued and hence not sold in the current year.

    
    For all of the remaining formulas, calculations are only for active products:

  • Volume Impact on Sales:

  • = (Vol_CY@Mix_PY – Vol_PY) (List_P_PY (1 – Discount_Pct_PY))

  • Impact on sales from changes in the volume of products sold, considering the sales mix of the previous year.

  • Mix Impact on Sales:

  • = (Vol_CY – Vol_CY@Mix_PY) List_P_PY (1 – Discount_Pct_PY)

  • How changes in the sales mix from the previous year to the current year impact sales.

  • List Price Impact on Sales:

  • = Vol_CY (List_P_CY – List_P_PY) (1 – Discount_Pct_PY)

  • Impact of changes in the list prices of products from the previous year to the current year on sales.

  • Discount Impact on Sales:

  • = Vol_CY List_P_CY ((1 – Discount_Pct_CY) – (1 – Discount_Pct_PY))

  • Impact of changes in discount percentages offered on products from the previous year to the current year on sales.

  • Volume Impact on Cost:

  • = (Vol_PY – Vol_CY@Mix_PY) * Cost_PY

  • Impact of changes in the volume of products sold on the cost of goods sold, considering the previous year's sales mix.

  • Mix Impact on Cost:

  • = (Vol_CY@Mix_PY – Vol_CY) * Cost_PY

  • How changes in the sales mix impact the cost of goods sold.

  • Materials Impact on Cost:

  • = Vol_CY * (Mat_PY – Mat_CY)

  • Impact of changes in material costs from the previous year to the current year on the overall cost.

  • Value-Added Impact on Cost:

  • = Vol_CY * (VA_PY – VA_CY)

  • Impact of changes in value-added costs (e.g., labor, overhead) from the previous year to the current year on the total cost.

Written by:

Jordan Supowit

Owner, Sharpsight Finance

www.sharpsightfinance.com
jordan@sharpsightfinance.com
720-837-2056