Which costing systems can often be used to calculate product profitability

However, business decisions such as whether to terminate a particular product line or to fire a customer, require looking at other costs associated with those particular sales to determine an estimate of their net profitability. I use the word estimate because arriving at a net profit by customer or product requires allocating indirect costs to products or customers, not usually an exact science.

Without wading into cost accounting or activity-based costing methodology, this article discusses one way some simple cost allocations could be modeled in the Excel Data Model to determine product profitability. Similar logic could be applied in a project to determine customer profitability. The article does not recommend how particular costs should be allocated; the example simply show the functionality behind three allocation methods. As with all things Excel, Power Pivot, Power Query, there are numerous ways of tackling a project like this, some may be better than the ones described here. As always, thoroughly test your own data models or better, have someone else do it.

Sample Data

Sample data is input, via Excel worksheets, as Linked Tables. The data comprises three months of motor vehicle sales data (Sales, Cost of Goods Sold, Units Sold), by product group. There is also some indirect expenses data. See the Excel extracts below of the Selling and Expenses tables:

In practice, the sales, costs, and overheads data would likely be extracts from an accounting or ERP system.

There is also a table of month-by-month showroom usage percentages:

1. Labor Expenses are allocated to a product group in proportion to the monetary value of the sales (i.e. the value of the Sales account)

2. Rent expenses are allocated to a product group in proportion to percentage of showroom space allocated to the product group in that month.

3. Both Marketing and General Expenses (the combination of which is referred to as Other Expenses in this example) are allocated in proportion to Unit Sales.

Data Model

The completed Excel Data Model diagram is shown below. The Measures and Calculated Columns are described in the next section.

This is what the Selling table (where most of the action is) looks like in the Data Model:

Measures, Calculated Columns, and More Measures

Some basic measures have been created to calculate explicit measure summations of the key accounts referred to in Allocation Assumptions above. Hopefully the workings of these measures are somewhat self-explanatory:

Total Sales:=CALCULATE(SUM([SellingAmt]),Selling[Account]="Sales")

Total COGS:=CALCULATE(SUM([SellingAmt]),Selling[Account]="Cost of goods sold")

Gross Profit:=[Total Sales]-[Total COGS]

Total Unit Sales:=CALCULATE(SUM([SellingAmt]),Selling[Account]="Units Sold")

Total Expenses:=SUM(Expenses[ExpenseAmt])

Total Labor Expenses:=CALCULATE(SUM('Expenses'[ExpenseAmt]),'Account'[Account]="Labor")

Total Rent Expenses:=CALCULATE(SUM('Expenses'[ExpenseAmt]),'Account'[Account]="Rent")

Total Other Expenses:=[Total Expenses]-[Total Labor Expenses]-[Total Rent Expenses]

Now, the allocations are computed in Calculated Columns in the Selling table, one column for each of the three allocation types referred to above. This DAX is somewhat less self-explanatory. Refer to the image of the Selling table under Data Model to see all columns.

AllocatedLaborExpenses

This broadly says that for the particular month, 

Allocated Labor Expenses = 

(Sales for that product group / Total Sales)   x   Total Labor Expenses

AllocatedOtherExpenses

Similar to the first allocation, this broadly says that for the particular month, 

Allocated Other Expenses = 

(Sales for that product group / Total Sales)    x     Total Other Expenses

The first two Calculated Columns make use of the EARLIER() function. I do not propose to explain this function, mainly because I would struggle and make a mess of it, but don’t think of EARLIER as referring to earlier in time.

AllocatedRentExpenses

In the case of the two allocations that use the value in the SellingAmt column, the value is computed on the same line as the appropriate account (Sales or Units Sold) and is set to be BLANK() otherwise.

The AllocatedRentExpenses amount has been arbitrarily allocated to the Sales account row but could have been allocated to any one of the other rows (Unit Sales, Cost of Goods Sold) since it is not using values from within the Selling table.

Finally, the following summation measures have been created from these new columns:

Allocated Labor Expenses:=SUM('Selling'[AllocatedLaborExpenses])

Allocated Rent Expenses:=SUM('Selling'[AllocatedRentExpenses])

Allocated Other Expenses:=SUM('Selling'[AllocatedOtherExpenses])

Allocated Expenses:=[Allocated Labor Expenses]+[Allocated Other Expenses]+[Allocated Rent Expenses]

Net Profit:=[Gross Profit]-[Allocated Expenses]

Monthly Product Profitability Report

This screenshot shows a cube formula report with the key measures and Excel calculation in grey font to test the accuracy. On the basis of the insights from this artificial, contrived data, the business owners might decide to cease sales of SUVs (very unlikely in the real world).

Conclusion

The above example shows one way to allocate indirect and overhead expenses to product groups. As mentioned, a similar approach could be potentially be adopted to determine Customer profitability. For each method of allocation, the computation is done in a Calculated Column of one of the tables of the Data Model (Selling in this case). The balances of accounts that are subject to the same allocation methodology are summed using a measure, the example here being Marketing and General Expenses.

Which costing systems can often be used to calculate product profitability *?

What is absorption costing and marginal costing?

What is variable costing and absorption costing?

What is the absorption costing method?