ExcelInsider Team
Indirect costs are expenses that don’t directly affect production, but still occur in a business. For example, if you have a physical location for your office or store, you must pay rent for it. That cost is indirect because it does not directly affect production, but is still required for the business to run. By calculating the indirect cost rate, we can create our budgets, justify our indirect costs, measure performance, strategize pricing, and comply with regulations that require indirect cost rates. In this article, we will learn how to calculate the indirect cost rate in Excel.
➤ Calculate the direct costs using this formula: =SUM(C2:C6)
➤ Replace C2:C6 with the range that includes the direct costs.
➤ Calculate the indirect costs using the following formula:
=SUM(C7:C10)
➤ Finally, use the following formula to calculate the indirect cost rate:
=C16/C15
➤ Replace C16 with the indirect costs and C15 with the direct costs.
While indirect costs are usually calculated against the direct costs, they are sometimes calculated against revenues. Make sure you know which one you need for your calculation, and follow one of the two methods explained in this tutorial.
What is the Indirect Cost Rate?
In a business, indirect costs represent the costs that cannot be directly assigned to a single unit of product. Indirect cost rate is the rate of indirect cost against the direct cost or the revenue. While setting up the budget of a project, the indirect cost rate is used to allocate the cost to different parts of the project. Calculating the indirect cost rate helps a company ensure that a fair amount of money is assigned to the separate departments of the business.
Calculating Indirect Cost Rate in Excel Against Direct Costs
To calculate the indirect cost, we have a dataset with a few transactions here. There are some direct costs, some indirect costs, and some revenues. We will calculate the indirect cost rate using the information from the table. Follow the steps below to do it:
➤ First, we need to calculate the net direct costs. In C15 cell, write the following formula:
=SUM(C2:C6)
➤ Now, we need to calculate the net indirect costs. In C16 cell, copy the formula from below:
=SUM(C7:C10)
➤ Now, to calculate the indirect cost rate, we need to divide the indirect costs by the direct costs in the C17 cell.
=C16/C15
➤ Now, the result is shown in fractions. To make it show up in percentage, go to the Number group of the Home tab (in the ribbon), and select the percentage (%) icon while you are in the C17 cell.
Measuring Indirect Cost Rate Using Revenue in Excel
Another method to calculate the indirect cost rate, which is not so common, is to use revenue to calculate the rate instead of direct costs. The steps below demonstrate how to do it:
➤ First, calculate the net revenue in the C15 cell with the help of the following formula:
=SUM(C11:C13)
➤ Calculate the indirect costs in the C16 cell:
=C7+C8+C9+C10
➤ Finally, calculate the indirect cost rate in C17 cell using the following formula:
=C16/C15
➤ The result might show up in fractions instead of percentages. Press Ctrl + Shift + % to change the number format of C17 cell to a percentage.
Frequently Asked Questions
What is an example of an indirect cost?
Indirect costs are expenses that don’t directly contribute to production. An example of that would be administrative expenses like the accounting department costs. Rent, utilities, and salaries of officers and clerks are all indirect costs.
How are indirect cost rates applied?
In some cases, the financial departments use an indirect cost rate to allocate budget for indirect costs. For example, if the direct cost is $20000, and the indirect cost rate is 20%, the indirect cost will be $20000*20%= $4000.
What are the two types of indirect costs?
We can classify the indirect costs into fixed indirect costs and variable indirect costs. Fixed indirect costs can contain rent, salaries of staff, insurance, depreciation, etc. In variable indirect costs, office supplies, maintenance expenses, and usage-based costs can be included.
Can a cost be both direct and indirect?
Yes. For example, if you hire a supervisor who works in multiple departments, you cannot divide the part that goes directly to production. As a result, it is both a direct and indirect cost to your production.
How to calculate cost in Excel?
To calculate the total cost, you need to add the direct and indirect costs. Sum up the direct costs in a cell using the SUM function, and indirect costs in another cell. Then, add those values using the plus (+) operator. However, if you want to calculate the cost per unit, divide the total cost by the total production units.
Wrapping Up
In this article, we have learned how to calculate the indirect cost rate in Excel. We have explored two methods that should be sufficient for accounting purposes. The dataset we used in this article, along with the formulas, is available in the Excel file, and it’s free to download. Comment down if you have any confusion regarding the methods, and we will see you in another tutorial soon.
