Profit Contribution

Profit Contribution allows you to identify the level of surplus/profit generated by components of your business. By applying relative indicators to reflect the amount of variable and fixed expense (resource) a component consumes surplus and contribution levels are calculated. This component performance is combined with indicators for future potential and business ability to identify areas that will maximize current and future performance. Summary recommendations are provided for each breakdown and results presented in graphical and tabular form. This business tool can analyze any chosen component of an overall business or product line. Default breakdown is five Customer Segments and five Product Groups.

Profit Contribution

Download Profit Contribution

downloadzip file Download Profit Contribution - Compressed Zip file
profitcontribution.zip

or

downloadexcel file Download Profit Contribution - .xlsm file
profitcontribution.xlsm

This application is a macro-enabled spreadsheet developed in Microsoft Excel and will only function when opened using a spreadsheet program with macros enabled. You can download Profit Contribution free of charge for evaluation. In evaluation mode some functionality is restricted. To license your application and fully enable all functions requires the purchase of an application license. If you have not already please consider buying a license to fully enable your application.

If you download the Compressed Zip file unzip/uncompress the downloaded file and save the content .xlsm application file. If you download the .xlsm file simply save the .xlsm file. Open the .xlsm file and follow the instructions. Macros must be enabled for this application to function. If you cannot access input or command buttons do not operate macros are not functioning. In this case please refer to your spreadsheet help to enable macros. This application does not access or alter your system or system files in any way. To uninstall simply delete the saved files.

Application suitability must be independently assessed and use indicates acceptance of any and all associated risk.

Help Profit Contribution

All inputs are in blue cells on the Input Sheets. Click the tab, hyperlinks or buttons to access. In evaluation mode some inputs are locked (gray cells).

Overall Performance Data

This section reviews the overall performance of your business for a full year period. You can include up to 6 categories for expenses that vary with your sales (Variable Costs) and another 6 categories for expenses that don't vary with your sales (Fixed Costs). Input your descriptions and values in the blue cells. Example categories are provided. Your input can be based on a recent tax return or Profit and Loss statement. If you exclude asset and capital items the result will represent your day to day operating surplus/profit. Inputs do not have to be perfect but should reasonably reflect business operation. Analysis will determine the contribution made by customer segments and product groups to overall performance. This allows you to identify the most profitable components of your business and direct resources to maximize your current and future returns. If you wish to analyze a specific area of your business i.e. a product line, use data for that specific area as input for overall performance.

Business Name or Identifier

Input a name for the overall business. This appears as an identifier on the Output Sheets.

Revenue

If desired input a descriptive name for your revenue. Input the value of overall revenue for a full year.

Expenses

Select your Expense input as Monetary or Percent. This allows you to input your Expenses directly in Monetary (Dollars, Pounds etc) or Percentage terms.

Variable Costs

Variable Costs change with the volume of product or service you provide. Only include these costs in this section. You can input a descriptive name for these costs and define up to six categories for cost allocation.

Fixed Costs

Fixed Costs are expenses which remain constant (up to a point) while the volume of sales vary. You can input a descriptive name for these costs and define up to six categories for cost allocation.

Input Customer Segment Data

You can break overall performance into any base components you desire. The default breakdown is five Customer Segments and five Product Groups. If desired input a descriptive name for the breakdown you will apply and each of your five segments. Customer Segments can be defined based on customer location, type, age, buying habits, number or value of sales, interests, or any attribute you choose. The sum of segment values in each category will equal overall values.

Revenue

Select your revenue input as Monetary or Percent. This allows you to input your revenue directly in Monetary (Dollars, Pounds etc) or Percentage terms. Input the revenue for each of your first four segments. Revenue for the fifth segment is calculated to make total revenue equal to overall revenue.

Transaction

Select your transaction input as Num Sales (Number of Sales) or Price (Average Revenue per Sale). This allows you to provide transaction input based on the number of sales within each segment or the average revenue (price) per transaction for each segment. Input the value for each of your five segments.

Relative Indicator reference

Select your Relative Indicator reference as Monetary or Transaction. This allows you to input your Relative Indicators referenced per unit of revenue in Monetary (Dollars, Pounds etc) or referenced per unit number of Transactions.

Each expense category provides a resource. This resource is used to secure sales transactions and revenue with the goal of generating a profit. The amount of resource required to secure a set number of transactions or a set amount of revenue varies by segment. Segments that consume more of a resource should contribute more to the expense that provided the resource. Expense allocations are determined using Relative Indicators. Relative Indicators can be referenced to Transactions or Monetary (Dollars, Pounds, etc). The base or "average" relative indicator is 100. This is the level of expense incurred overall business to secure a set number of transactions (if your reference is Transaction) or a set amount of revenue (if your reference is Monetary). If a segment uses 10% more of the expense than "average" to secure the transaction or revenue its relative indicator is 110. If 10% less its relative indicator is 90. If it uses none of the expense its relative indicator is 0. All calculations are relative, it does not matter what the relative indicator totals are only the relationship between each indicator is used.

Variable Costs

Input the Relative Indicator for each expense category to generate a set amount of revenue, or number of transactions for each of your five segments. The base or "average" indicator is 100.

Fixed Costs

Input the Relative Indicator for each expense category to generate a set amount of revenue, or number of transactions for each of your five segments. The base or "average" indicator is 100.

Operating Surplus/Profit

The Operating Surplus/Profit indicates the profit for each segment.

Return on Sales

If desired input a descriptive name for the Operating Surplus/Profit as a percentage of sales revenue. This indicates how much of each segment revenue flows through to profit.

Return on Sales Level

If desired input a descriptive name for your Return on Sales Level. This is the strength of profit flow through for each segment compared to each of the other segments. It is used as an indicator of comparative performance. The highest indicator signifies the highest profit flow through. This is also presented in the Combined Assessment Chart.

Profit Contribution

If desired input a descriptive name for the Profit Contribution from each segment. This the percentage contribution each segment makes to overall operating surplus/profit.

Profit Contribution Level

If desired input a descriptive name for the Profit Contribution Level. This is the profit contribution of each segment compared to each of the other segments. It is used as an indicator of comparative performance. The highest indicator signifies the highest profit contribution. This is also presented in the Combined Assessment Chart.

Break even Points

Break even Points are the point where the income from a segment equals the expenses for that segment. Above the break even point you make a profit, below it you make a loss.

Revenue

This is the annual revenue required by each segment for it to break even. If annual revenue is below this point the segment is making a loss. The Break even Chart provides additional break even data. To view each segment you must access the chart from the buttons at the bottom of the Segment Input sheet.

Number of Sales

If desired input a descriptive name for the break even number of sales. This is the annual number of sales required by each segment for it to break even. If the annual number of sales is below this point the segment is making a loss. The Break even Chart provides additional break even data. To view each segment you must access the chart from the buttons at the bottom of the Segment Input sheet.

Potential & Ability

This section establishes the relationship between segment potential and the ability of your business to service it. A Relative Indicator is applied to each segment for these attributes. The base or "average" relative indicator is 100. If segment potential is 10% greater than average its relative indicator is 110. If 10% less its relative indicator is 90. All calculations are relative, it does not matter what the relative indicator totals are only the relationship between each indicator is used.

Potential

If desired input a descriptive name for Segment Potential. This is one component of the Opportunity Level. Input a relative indicator to represent the potential in each segment. Consider size, growth, profitability, customer satisfaction, competition levels, and risk. The base or "average" relative indicator is 100.

Business Ability

If desired input a descriptive name for Business Ability. This is one component of the Opportunity Level. Input a relative indicator to represent your businesses ability to service and develop each segment. Consider your expertise, market share, distribution, marketing, and resources. The base or "average" relative indicator is 100.

Opportunity Level

If desired input a descriptive name for the Opportunity level. This indicates the attractiveness of a segment for your business. It considers the segment potential and your businesses ability to develop that potential. The highest opportunity level indicates the relatively most attractive segment for your business. This is also presented in the Combined Assessment Chart.

Customer Segment Results

Detailed breakdown by expense categories for each segment.

Input Product Group Data

You can break overall performance into any base components you desire. The default breakdown is five Customer Segments and five Product Groups. If desired input a descriptive name for the breakdown you will apply on this sheet and each of your five groups. Product Groups can be based on product type, cost, number or value of sales, use, or any attribute you choose. The sum of group values in each category will equal overall values.

Revenue

Select your revenue input as Monetary or Percent. This allows you to input your revenue directly in Monetary (Dollars, Pounds etc) or Percentage terms. Input the revenue for each of your first four groups. Revenue for the fifth group is calculated to make total revenue equal to overall revenue.

Transaction

Select your transaction input as Num Sales (Number of Sales) or Price (Average Revenue per Sale). This allows you to provide transaction input based on the number of sales within each group or the average revenue (price) per transaction for each group. Input the value for each of your five groups.

Relative Indicator base

Select your Relative Indicator reference as Monetary or Transaction. This allows you to input your Relative Indicators referenced per unit of revenue in Monetary (Dollars, Pounds etc) or referenced per unit number of Transactions.

Each expense category provides a resource. This resource is used to secure sales transactions and revenue with the goal of generating a profit. The amount of resource required to secure a set number of transactions or a set amount of revenue varies by group. Groups that consume more of a resource should contribute more to the expense that provided the resource. Expense allocations are determined using Relative Indicators. Relative Indicators can be referenced to Transactions or Monetary (Dollars, Pounds, etc). The base or "average" relative indicator is 100. This is the level of expense incurred overall to secure a set number of transactions (if your reference is Transaction) or a set amount of revenue (if your reference is Monetary). If a segment uses 10% more of the expense than "average" to secure the transaction or revenue its relative indicator is 110. If 10% less its relative indicator is 90. If it uses none of a the expense its relative indicator is 0. All calculations are relative, it does not matter what the relative indicator totals are only the relationship between each indicator is used.

Variable Costs

Input the Relative Indicator for each expense category to generate a set amount of revenue, or number of transactions for each of your five groups. The base or "average" indicator is 100.

Fixed Costs

Input the Relative Indicator for each expense category to generate a set amount of revenue, or number of transactions for each of your five groups. The base or "average" indicator is 100.

Operating Surplus/Profit

The Operating Surplus/Profit indicates the profit for each group.

Return on Sales

This indicates the how much of each group revenue flows through to profit.

Return on Sales Level

This is the strength of profit flow through for each group compared to each of the other groups. It is used as an indicator of comparative performance. The highest indicator signifies the highest profit flow through. This is also presented in the Combined Assessment Chart.

Profit Contribution

This the percentage contribution each group makes to overall operating surplus/profit.

Profit Contribution Level

This is the profit contribution of each group compared to each of the other groups. It is used as an indicator of comparative performance. The highest indicator signifies the highest profit contribution. This is also presented in the Combined Assessment Chart.

Break even Points

Break even Points are the point where the income from a group equals the expenses for that group. Above the break even point you make a profit, below it you make a loss.

Revenue

This is the annual revenue required by each group for it to break even. If annual revenue is below this point the group is making a loss. The Break even Chart provides additional break even data. To view each group you must access the chart from the buttons at the bottom of the sheet.

Number of Sales

If desired input a descriptive name for the break even number of sales. This is the annual number of sales required by each group for it to break even. If the annual number of sales is below this point the group is making a loss. The Break even Chart provides additional break even data. To view each group you must access the chart from the buttons at the bottom of this page.

Potential & Ability

This section establishes the relationship between group potential and the ability of your business to service it. A Relative Indicator is applied to each group for these attributes. The base or "average" relative indicator is 100. If group potential is 10% greater than average its relative indicator is 110. If 10% less its relative indicator is 90. All calculations are relative, it does not matter what the relative indicator totals are only the relationship between each indicator is used.

Potential

This is one component of the relative opportunity level. Input a relative indicator to represent the potential in each group. Consider demand, growth, profitability, customer satisfaction, competition levels, and risk. The base or "average" relative indicator is 100. If group potential is 10% greater than average its relative indicator is 110. If 10% less its relative indicator is 90. All calculations are relative, it does not matter what the relative indicator totals are only the relationship between each indicator is used. This is one component of the relative opportunity level.

Business Ability

This is one component of the Opportunity Level. Input a relative indicator to represent your businesses ability to service and develop each group. Consider your expertise, market share, distribution, marketing, and resources. The base or "average" relative indicator is 100.

Opportunity Level

This indicates the attractiveness of a group for your business. It considers the group potential and your businesses ability to develop that potential. The highest opportunity level indicates the relatively most attractive group for your business. This is also presented in the Combined Assessment Chart.

Product Group Results

Detailed breakdown by expense categories for each group.

Customer Segment Chart

Revenue and Surplus Chart by Segment.

Product Group Chart

Revenue and Surplus Chart by Group.

Combined Assessment Chart

This chart plots the Opportunity Level against Return on Sales Level and shows the current Profit Contribution level for each segment and group. It allows you to easily identify areas for increased focus. The closer to the top the higher the comparative return on sales. The further to the right, the greater the comparative opportunity. Customer segments and product groups towards the top right hand side represent the best opportunities for development. The greatest opportunity may exist where a high rating product group is purchased by a high rating customer segment. This chart indicates where effort and resources should be directed for greatest return. The current profit contribution is indicated by the size of the marker. If the contribution is negative (the segment or group makes a loss) no marker will be seen.

Break even Chart

View the Break even Chart by clicking the required button. To update chart data you must access it using the buttons.

License Profit Contribution

Please consider supporting application development. Licensing your application will fully enable all functions.

License your application now
  $69.00 USD per year

Secure online transaction processing is provided via PayPal. License details are sent by email as soon as your transaction is processed and will fully enable your application.

Application suitability must be independently assessed and use indicates acceptance of any and all associated risk.

Profit Contribution
-