Free Activity Based Costing Excel Template

 

Product Costing Template by Excel Skills. Product costs, in. Activity based costing is a free template that step by step shows the process to establish an activity-based cost calculation. Activity based costing is a cost accounting method that assumes that activities consume resources and that objects are using activities. In activity based costing the focus is on activities and costs that have been.

What Is an Activity Cost Estimate?

Need a dependable way to calculate the cost of a project? Begin by estimating the costs associated with performing or completing each work activity. It can feel like a monumental undertaking; however, there’s no better way to accurately project budgetary requirements.

Activity cost estimates help reduce cost uncertainty because they break down project costs to the smallest level, the activity level. By starting with the smallest level of work, the project manager creates a granular decomposition of cost that is later rolled up to higher levels to create the project budget.

An activity cost estimate includes those costs associated with all the resources required to complete the activity. The various types of costs might include direct and indirect costs associated with labor, materials, equipment, services, facilities, exchange rates, inflation and allowance.

Generating activity cost estimates isn’t always an exact science. Assumptions are made, prices may fluctuate and the available resources may change. Because uncertainty exists, it’s a good practice to retain supporting details of how the estimates were developed. Later, the information will be referenced when performing risk identification.

Activity Cost Estimates Template - Word


Download now in Word format.

Get Template

Activity Cost Estimates Template - PDF


Download now in PDF format.

Get Template

The cost of activities are estimated by the person responsible for determining the project funding requirements. Most often, that person is the project manager.

If we’re to estimate the cost of project activities, we must know what the activities are and what resources are required to perform or complete those activities. This means that an activity list and a resource requirements list are inputs to estimating activity costs. Other inputs may include sources and documentation that support how each estimate is reached.

Activity cost estimates are building blocks for creating the project budget. First, the activity cost estimates and the associated contingency reserves are added together or “rolled up” into work package cost estimates. The work package estimates and the contingency reserves are then aggregated into control accounts. The sum of all the control accounts makes up the cost baseline for the entire project. Finally, management reserves are added to the cost baseline to determine the project budget.

Need help from an experienced project manager? Our professionals are ready when you are. Let’s start the conversation.

Definition of Activity Based Costing

What is Activity Based Costing? How does Activity Based Costing work?

Activity-based costing (ABC) is an accounting technique that enables Microsoft to assign overhead activities costs of the firm to the specific products and services it produces & delivers.
Compare to the traditional technique, ABC assigns the overheads and indirect costs less arbitrarily and focuses on true relationship between costs, overhead activities, and related products (manufactured, produced) / services delivered by Microsoft.

Solving Microsoft Costing Problem with ABC Accounting Method

Microsoft markets two types of products in the Software & Programming industry. For sake of simplicity let us call them Technology Standard product and Technology Custom Product. Standard Product is lot simpler than Custom Product, so Microsoft produces Standard product in large batch sizes compare to Custom product which is produced in smaller batch sizes.

Free Activity Based Costing Excel Template Printable

Microsoft Standard ProductMicrosoft Custom Product
Annual Sales (Units)1278811763
Sales Price (Per Unit)62 USD83 USD
Batch Size (units)100050
Direct Labor Time / per unit22.5
Direct Labour rate per hour88
Direct Input cost per unit 22 32
Number of Custom Parts per unit1 4
Number of Set-Ups per batch13
Separate Material per batch11
Number of Sales Invoices - issued per year50240
Overhead Cost AnalysisUSDCost Drivers
Set Up Costs72255Number of Set Ups
Special part handling cost60361Number of Special Parts
Customer invoicing cost29471Number of Invoices
Material handling cost62103Number of Batches
Other overheads107026Labor Hours

Urgent - 6Hr

per Page
  • 100% Plagiarism Free
  • On Time Delivery 27x7
  • PayPal Secure
  • 300 Words / Page

12 Hr Delivery

per Page
  • 100% Plagiarism Free
  • On Time Delivery 27x7
  • PayPal Secure
  • 300 Words / Page

24 Hr

per Page
  • 100% Plagiarism Free
  • On Time Delivery 27x7
  • PayPal Secure
  • 300 Words / Page

Solution Based on Traditional Approach of Costing


Traditional Approach - Absorption Approach
The difference between the traditional method and ABC can be only noticed when there are different types of products produced by Microsoft and indirect costs comprise a significant chunk of the overall cost structure of Microsoft.
One of the key reasons why small firms still use traditional method is its sheer simplicity. ABC can be highly complex if the firm is present in number of industries and, produce & market various products around firm's core competency.
How direct costs are treated in ABC? Is it different from the way it is treated under traditional approach?
The answer is no difference at all. ABC is concerned only with the way in which overheads are charged to jobs to derive the full cost.


Step 1 - Calculating Overhead Recovery Rate of Indirect Costs

Overhead Cost AnalysisUSD
Set Up Costs72255
Special part handling cost60361
Customer invoicing cost29471
Material handling cost62103
Other overheads107026
Total Overheads (1+2+3+4+5) 331216

To calculate the 'Overhead Recovery Rate' add up all the indirect costs that Microsoft is incurrring for both the standard and custom product.


Overhead Recovery Rate Formula

Overhead Recovery Rate = Total Overheads / Number of Labor Hours

Overhead Recovery Rate = 6.02 Per Hour


Step 2 - Product / Service Costs - Adding Direct and Indirect Costs
Standard ProductCustom Product
Direct Costs
Labor
Hrs * Labor/hr
1620
Material2232
Indirect Costs
Overheads (recovery rate * hours) 12.0415.05
Total Costs Per Unit (1+2+3) 50.04 67.05

Using the Overhead Recovery Rate Calculation we can assign the indirect cost to individual products. We are just allocating the variable or indirect costs based on the hours taken by labor to produce the standard and custom products.
Total cost per unit under the traditional method can be calculated by adding up - Raw material costs, labor costs, and indirect cost allocation using Overhead Recovery Rate formula.

The Total Cost per unit of Standard product is 50.04
The Total Cost per unit of Custom product is 67.05

Return on Sales Calculations for Microsoft

Return on Sales metrics enables the firm to allocate resources where it can maximize returns. After subtracting the cost per unit of the product from sales price of the product we can arrive at the profit per unit.

Standard ProductCustom Product
USD per UnitUSD per Unit
Selling Price6283
Total Cost50.0467.05
Profit11.9615.95
Return on Sales19.2919.217

The profit per unit of custom product is higher than standard product.In isolation these numbers don't provide a great insight as price is often the function of competitive forces in the market place. To explore further we should analyze - Return on Sales per unit.



Activity Based Costing System for Microsoft


Activity Based Costing can improve the costing process at Microsoft in three prominent ways –
By increasing the number of cost pools – ABC help in identifying the activities that are being performed by organization’s resources. Often too narrow allocation can result in unclear understanding of both activities and how resources spent on them.
By assigning costs to various activities that are segmented based on the role they perform in overall processes. Instead of treating all indirect costs as one organization wide pool, ABC pools the costs based on each activity.
Finally assigning costs to respective products, service and customers activities in the organization using activity cost drivers.

How to calculate Activity Based Costing & What is the formula for Activity Based Costing?

After identifying all the support activities and associated costs & factors that drive those cost, managers at Microsoft can take following steps to conduct ABC costing -
Step 1- Establishing Cost Pool for each activity based on the Software & Programming dynamics.
Step 2 - Allocating total cost associated with each activity to the relevant pool.
Step 3- Calculating Per Unit Cost based on the relevant cost driver.
Step 4 Final Step - Dividing the amount in each pool by estimated total usage of the cost driver. Then the unit cost is multiplied by the number of units of the cost driver of that specific product.
You can easily follow the above four steps in the following table of ABC Calculations.

ABC Calculations

Overhead Cost Driver(a) Standard Diver Volume(b) Custom Diver Volume(c) Total Diver Volume a+b(d) Costs USD(e) Driver Rate (d/c)(f) Standard Total Cost a*e(g) Custom Total Cost b*e Standard Unit Costs Custom Unit Costs
Set Up CostsSet-up per Batch127207327225598.7090163934431184.508196721371070.4918032790.0926265402503376.0418678741204
Special part handling costSpecial Parts per unit127884705259840603611.008706550802112899.33937165847461.6606283421.00870655080214.0348262032086
Customer invoicing costInvoices per year5024029029471101.624137931035081.206896551724389.7931034480.397341796727542.073433061587
Material handling costNumber of Batches12.788235.26248.04862103250.366864477843201.691462942758901.3085370570.250366864477845.0073372895569
Other overheadsLabor Hours2557629407.554983.51070261.946511226095149783.97111860857242.0288813923.89302245219024.8662780652378

Job Costing Excel Template

Per Unit Overhead Cost using ABC


There are two steps to calculate per unit overhead costs using ABC method. First calculate per unit cost based on cost driver usage by specific product and then add those overhead costs for the product to arrive at Per Unit Overhead Cost of a product

Overhead Unit Cost StandardCustom
Set Up Costs0.092626540250337 6.0418678741204
Special part handling cost1.0087065508021 4.0348262032086
Customer invoicing cost0.4 2.07
Material handling cost0.25036686447784 5.0073372895569
Other overheads3.8930224521902 4.8662780652378
Total Overheads5.64 22.02
The total overheads of standard unit using ABC costing is 5.64 and the overheads of custom unit uning ABC costing method is 22.02

Total Cost Using ABC Technique


The total cost per unit of standard and custom product can be calculated by adding up - direct cost, raw material cost, and ABC derived per unit indirect cost.


Standard ProductCustom Product
Direct Costs
Labor
Hours * Cost of Labor per Hours
1620
Material2232
Indirect Costs
ABC- Overhead 5.6422.02
Total Costs Per Unit
(1+2+3)
43.64 74.02
The Total Cost per unit of Standard product is 43.64
The Total Cost per unit of Custom product is 74.02
Free Activity Based Costing Excel Template
If we compare the total costs using traditional method and ABC method, we can easily observe that the difference between the total cost of custom product to that of standard product is far higher under ABC method than it is under the tradtional method.

Return on Sales Using ABC Costing Analysis

Standard ProductCustom Product
USD per UnitUSD per Unit
Selling Price6283
Total Cost43.6474.02
Profit18.368.98
Return on Sales29.6110.815

The profit per unit of standard product is higher than custom product under ABC technique.By comparing the Return on Sales numbers under both traditional and ABC technique - We can easily conclude that ABC is far more effective costing technique for Microsoft as it stops inefficient allocation of costs to Standard product. Microsoft should focus on standard product rather than custom product as standard product is delivering much higher returns on sales.


Limitations of ABC Costing Method

ABC is time consuming and costly.
Setup costs and updating cost for example in the automobile industry is very high and ABC fails to inculcate it.
Business with similar product and services output that require similar activities then the benefits from ABC are very limited.


References for Activity-Based Costing for Microsoft

Peter Atrill & Eddie McLaney (2011) 'Management Accounting for Decision Makers'. Published by Prentice Hall
Kaplan & Anderson (2001) 'Time Driven Activity Based Costing - A simpler more powerful path to profitability', published by Harvard Publications.



Related Terms

Cost driver

The factors that cause a change in the costs of each support activity are called cost drivers

Free Activity Based Costing Excel Template Excel

Cost pool

The costs of each support activity enter a cost pool and the relevant cost drivers are used to attach an amount of overheads from this pool to each unit of output.

Activity Based Costing

Activity-based costing is an financial analysis tool to deal with overheads

Target costing

Target costing is an effort on part of firm to reduce costs to an extent where they cover cost plus expected profits. It is often done when firm employ cost plus pricing strategy.

Benchmarking

Benchmarking is strategy to emulate successful aspects of other projects, business or best practices in industry.