Customizable Spreadsheet Template: Product Profitability

This template estimates time-dependent 'contribution margin' for each product by subtracting from revenue the cost of goods and operating expenses that are directly attributable to each product. This analysis is often extremely useful for decision-making and profit optimization in many industries, including manufacturing, distribution, services, and software. It is often a 'sleeper'; that is, it can produce surprisingly novel and credible results that were not fully recognized and utilized before.

The model computes gross margin and gross margin % for each product by subtracting cost of sales, which consists of cost of goods and royalties.

• Cost of goods are optionally specified as cost per unit, cost as a % of revenue, or fixed costs for each product, or by all these methods.
• Royalties paid are optionally specified as cost per unit, cost as a % of revenue, or fixed costs for each product, or by all these methods.

The model computes contribution margin for each product by subtracting allocated operating expenses from gross margin. It tracks the following expenses.

• Marketing expenses
  • Tracks marketing staff equivalent headcount allocated to each product, staff expense per head for several job levels, and total expense of marketing staff for each product.
  • Tracks marketing programs expense by program and by product.
• Product development expenses
  • Tracks equivalent number of development staff applied to each product, segmented by development groups; and staff expense per head by job levels.
  • Tracks development program expenses, segmented by program and by product
  • Tracks development overhead expenses
• Customer support expenses. Differences in support expenses across products often are not fully recognized, yet they can be major factors in determining profitability of some products.
• Tracks number of support calls/cases, segmented according to the amount of staff time applied to each call. Tracks staff expense per head by job level. Tracks utilization of support staff.
• Estimates support activity and expense segmented by support issues (such as installation, maintenance, application assistance, explaining poorly documented features). Tracking expense by support issues is a powerful method for identifying opportunities to improve customer satisfaction and product profitability.

Product profitability analysis should be just one factor in decisions about allocating resources among products.

• The most important limitation of product profitability analysis is that is must be balanced with strategic factors, such as revenue growth rate, and long-term potential of products that is not reflected in current profitability.
• Where results of the analysis differ from expert opinions, we recommend that you carefully explore the reasons for the discrepancy. The experts may not have an accurate perception of expenses, or the profitability analysis may have missed factors that the experts recognize.

Not all features are included in the Standard or Light versions.