From the course: Excel: Management Accounting

Product line profitability - Microsoft Excel Tutorial

From the course: Excel: Management Accounting

Start my 1-month free trial

Product line profitability

“

- [Instructor] All business leaders understand the importance of building profitable products. And activity-based costing gives us a clean and effective way to evaluate profitability after taking into account direct and indirect costs of production. And sometimes ABC can reveal surprising insights to us that weren't obvious at first. I'm in the 03_04_Begin Excel file. So we have our activity-based costing analysis done for Acme Inc., the car company. Acme produces two products, the economy car and the sports car. And we've already evaluated the direct and indirect costs for these products. Now we need to understand the profit margins on them. So when we look at the direct costs only for the economy car, what we see is that those costs total $25,500. The direct costs for the sports car are $39,000. We sell the economy car for $48,000. And perhaps unsurprisingly, we sell the sports car for a little bit more money, $65,000. So our profit margin looks pretty healthy on this economy car, right? When we take the sales price of 48,000, minus the direct cost, all divided by the sales price, we get a gross margin of 46.88%, almost 47%. When we look at how this does for the sports car, we see the margins are still very strong at 40%. But despite the higher sales price on the sports car, it's just not quite as profitable a product. Now let's look at what happens if we take a bigger and broader view of cost which incorporate both our direct costs that traditional costing would take into account and indirect costs as revealed by activity-based costing. So I'm going to go through and update our profit margins based on our indirect costs. Our indirect costs plus direct costs total $40,700 for the economy car and 51,500 for the sports car. So once we take into account all of our indirect costs, our profit margin on the economy car falls considerably. The profit margin is now only 15.21%. For the sports car, the profit margin falls as well, but it's still 20, almost 21%. What this shows is that after taking into account our indirect costs which are higher for the economy car than they are for the sports car, the margin flips. Now we see that the true highly profitable product for this firm is the sports car, something that wasn't obvious when we looked only at the direct costs. Now we've seen how to evaluate product line profitability across a firm using ABC. We used a simplified example here with just two products, but the same basic technique could be applied to 200 products. And the key is making sure that we go through and break down all of our direct costs and indirect costs and then assign them to products based on the drivers in question.

Contents