This template was built primarily for manufacturers to better understand their unit costs. In the default version of the spreadsheet, I included many slots for each section for maximum flexibility. It is easy to add or delete rows if you don't need that much space and it won't break the model. Absorption costing is a way to account for every single cost that relates to producing a single finished product (all direct variable and fixed costs).
$45.00 USD
After purchase, the template will be immediately available to download. It is also included in the accounting templates bundle, the manufacturing templates bundle, the data table spreadsheets bundle, and the Super Smart Bundle.
Template Features:
- Full absorption cost summary by unit and in total for the period and units produced.
- Profitability sensitivity table, sensitizing units sold selling price.
- Income statement summary with extra inputs for selling and administrative costs.
- Displays break-even units produced, revenue, and break-even price.
The absorption cost summary displays the following costs per unit:
- Direct Material Cost per Unit
- Direct Labor Cost per Unit
- Variable Manufacturing Overhead per Unit
- Total Variable Cost per Unit
- Fixed Manufacturing Overhead per Unit
- Absorption Cost per Unit (total cost per unit)
Template Layout
- Production Data
- Units Produced
- Units Sold
- Direct Costs
- Bill of Materials Breakdown
- Raw Materials
- Subassemblies/Components
- Packaging Materials
- Consumables
- Direct Labor Hours
- Manufacturing Overhead
- Variable Manufacturing Overhead
- Fixed Manufacturing Overhead
- Includes an application costing section for each, similar to activity-based costing.
- Absorption Cost Summary
- Example Financial Statement for Period
- Break-even Analysis
- Sensitivity Table - Profit/Loss
The total absorption cost per unit can be used to calculate your cost of goods sold based on units sold x total absorption cost per unit. Additionally, the break-even price is based on units sold while the break-even units are going to tell you how many units need to be produced.
In my opinion, the most complex part about the model is the manufacturing overhead calculations.
Explaining Direct Variable and Fixed Manufacturing Overhead
For variable manufacturing overhead costs, the model lets the user define cost drivers and then calculates a ratio such as the indirect labor cost per direct labor hour. Then, based on how many direct labor hours are required per unit produced (calculated in the direct labor section), the model calculates the indirect labor cost per unit.
Another example is something such as maintenance and electricity of machines. In order to calculate this, the model first defines the total cost of a given amount of machine hours. Once we know the cost per machine hour, we input the number of machine hours required per single unit produced. Then, multiply the machine hours per unit by the cost per machine hour for electricity to get total electricity cost per unit. You can also have more simple manufacturing overhead where you are already calculating the cost based on units. For example, you know it costs $10,000 in indirect materials to produce 1,000 units. The indirect materials cost per unit is then just $10,000 / 1,000 and your driver quantity per unit is just '1'.
In the above example, you will see there is a helper table calculation for the number of machine hours required per unit produced. This gives a better idea of how to calculate the quantity drivers for this section if it is not already calculated in units produced (such as using direct labor hours or machine hours as the basis).
Moving to fixed manufacturing overhead, these are costs that don't change with unit levels. For example, depreciation expense, insurance / taxes for the building, rent, and electricity cost for lights. So, the more units you produce, the lower the fixed manufacturing overhead cost per unit will be, however, the total cost in the period will be the same since you should be entering the total costs for these items in the period the units are being produced. It could be monthly, annual, or any time frame as long as you are consistent throughout the model. This is different compared to variable manufacturing overhead calculations where you can define any budgeted cost over any period and quantity driver and then convert it afterwards.
Similar Templates:
