Activity Based Costing with Excel

Activity Based Costing with Excel: How to Use ABC for Excel for Activity-Based Cost Analysis

This ABC Video explains how to use for Excel to develop customized ABC analysis for your business. ABC for Excel allows business users to create their own ABC model with a few clicks. The video shows how to use the 3 major components of activity-based cost in your planning process – actually how to easily define your departments, activities and cost objects.

Before you watch the Activity Based Costing for Microsoft Excel video tutorial you need to understand the 3 important keys to creating ABC analysis:

1. ABC Departments

While in most cases these departments used by the ABC model will be equivalent to your company departments, in many cases you can use different departments for ABC.

How to know what departments to use?

As far as activity based cost modeling departments are pools or groups of similar or related activities. For example, your organization may not have an official marketing department but still your company performs many marketing activities such as promotion, advertising, branding, internet marketing, developing marketing materials, publishing white papers, hosting seminars, webinars or tradeshows…

Even without official business department you can group all these activities into a marketing department when you use ABC for Excel.

What is the point of the departments in ABC?

By grouping similar tasks, projects or simply activities you can easier allocate those costs to cost objects like products, services or customers. For example, some products will consume more promotion, selling and advertising or simply will have longer sales cycle which is important for activity based costing.

2. Activities

This component of ABC includes all activities which are used by your organization to create the products and services or simply create any type of value for the internal and external customers. The costs of each activity are allocated to the cost objects in order to measure the profitability at the net margin level.

3. Cost objects

The simplest way to understand cost objects is to think in a way of what you are trying to calculate measure and analyze with your ABC analysis and what ABC reports you need for your business case. This will change and depends on what information and cost analysis do you need. For example, individual products, customers and services can be used as cost objects.

When you need to calculate profitability and ABC / indirect costs per product you simply need to use your products as your cost objects. The ABC for Excel allows you to quickly create different ABC reports and analysis. ABC is among the rare strategies and tools for improving your profitability with your existing business model, existing customers and existing products and services.

Simply by using ABC analysis you will identify areas of improvement in pricing, making better operational decisions, repositioning your products and services, focusing on the most profitable products or services and identifying your top customers based on the real profit not gross margins.

Organizations without ABC generally make pricing and operational decisions based on the gross margin percentage which is in many cases misleading. Products or services with high margins in many cases can make the least profit at the net profit level because they consume many resources compared to other products or they can even lose money.

The same is true for customers – some clients might bring a lot of revenue but they consume so many resources and time that the net profitability suffers. With ABC you can make the right pricing and operational decisions for your business.

The top 7 tips for better ABC modeling

1. When you design your ABC model you don’t need to think in terms / logic of your company structure but in terms of business process and activities used by your organization.

2. Follow the logic: business systems > business processes > business activities. Business systems and processes might be used as departments in ABC and each process will have more than one activity.

3. Activity based costing cost drivers are all activities that consume resources by your company.

4. Use simple logic when you so the cost allocation for your model. The entire point is to allocate in a logical way (in most cases based on estimates) your indirect costs to cost objects.

For example, rent should be allocated based on space consumption – if your warehouse takes 80% of the space you rent that means that 80% of the rent costs should be allocated to the warehouse department.

Another cases requires time driven ABC – for example if your customer service department spends 90% of the time handling returns this cost should be allocated to reflect the reality.

5. In some cases it is useful to outline your business processes by using flow charts and define the flow of activities from departments to activities and cost objects before you go into the cost allocation modeling.

6. The final step is allocating the activities to the outputs like customers or products. This should follow the same logic described above. Example: if 70% of sales activities (70% of your sales reps’ time) are spent on selling, cross-selling, serving and maintaining Customer A this should be allocated as well and calculate the net profit of this client.

7. Some ABC results will reveal potential for identifying idle capacity in various departments. ABC reporting helps you discover both operational issues and opportunities as well as sales and marketing areas for improvement.

For more on activity based costing models, tool and ABC for Excel product as well as using Excel for ABC analysis and business dashboard reporting click here

ABC References:

1. ABC for Excel Official Product Page by Mr Dashboard

2. Activity Based Cost Model for Business Users of Microsoft Excel