Activity Based Costing Example with Excel
In any business that is serious about strict budgeting, activity based costing can be useful. The reason why activity based costing is necessary is because it helps you as manager to see the true cost of what an activity is worth. By looking at the resources and the amount of labor that’s going to be involved in a project.
For example suppose you have started a project or you are going through a project’s life cycle. You are going to use activity based costing to help you set a strict fixed budget that your must team adhere to.
As a manager this can help you to save a lot of cost to use in other areas of your business. So if your rough estimate budget was $50,000. Next when you execute activity based costing your real budget should be $30,000.
You can then use that $20,000 in other operations in your company. It could also prevent you from under-budgeting which can slow up progress if that should happen because you wouldn’t have enough capital to continue the project.
Lastly activity based costing could also help you to predict future budgets. So for example you are executing a project over a six month period where every week. You are going to review the project’s progress or at each milestone.
Based off of past activity, you can use that to predict the budget of future activities. Using Excel to create an activity based costing template is pretty simple. Despite the fact that Excel can be a bit hard to use especially for beginners. If you are in the beginner category there is no need to worry because as you can see by looking at the tutorial below you can get this done in a couple minutes.
Activity based costing tutorial guide
This example is going to showcase the cost of activities over a course of four months. You can then use this to predict the activity based cost for the next quarter.
First create 5 columns, these are going to be January, February, March, April and quarter 1. Leave the first column empty. In the empty column you are going to have revenue, staff costs, pc costs, admin costs, power, rent, total cost and profit.
Most of the work is now done. All you have to do now is enter in the different data. To add up the data to get the value in the total cost column is pretty simple. You are going to use autosum, to do this select all the numbers you want to add up. Next go to home, click on autosum and you’re done your number now is added.
To get your profit that must be inserted in the profit column, minus your total cost from your revenue. You can do this by putting the computation in the profit column. In the column you are going start with an equal sign. Followed by the operation then press enter and you will see the value show up (= revenue-total cost). You can see a diagram of the whole activity based costing example below.
As you can see by looking at the tutorial above, it’s pretty simple to create an activity based costing in Excel. If you don’t want to create it yourself, you could even download a template off the internet which a lot of persons have benefited from doing.
You can also find a lot of other layout that you can use. But this method is pretty simple along with the whole layout of the template. You can modify it to your preferences or find other inspiration off the internet because you have a lot of ways to go about organizing your activity based cost template.
More on Analytics