# Overhead Cost Pool Allocation Worksheet > Source: https://netray.co/tools/overhead-cost-pool-allocation-worksheet > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: Free Tools ## Short answer Set a predetermined overhead rate for each cost pool and compare absorbed overhead with actual spend. ## Category cost-finance ## Industry Discrete Manufacturing ## Audience - Controller - Cost accountant - Estimator ## Tool **Kind:** worksheet **Mode:** editable **Currency:** $ **Columns:** #### Cost pool **Key:** pool **Type:** text #### Allocation base **Key:** base **Type:** select **Options:** - Direct labor hours - Machine hours - Direct labor cost - Material cost - Units produced #### Budgeted annual cost **Key:** budget_cost **Type:** number **Format:** currency #### Budgeted base quantity **Key:** budget_base **Type:** number #### Rate per base unit **Key:** rate **Type:** number **Expr:** budget_cost / budget_base **Format:** decimal #### Actual cost **Key:** actual_cost **Type:** number **Format:** currency #### Actual base quantity **Key:** actual_base **Type:** number #### Applied overhead **Key:** applied **Type:** number **Expr:** rate * actual_base **Format:** currency #### Over (+) or under (-) absorbed **Key:** variance **Type:** number **Expr:** applied - actual_cost **Format:** currency **Rows:** **Pool:** CNC machining **Base:** Machine hours **Budget cost:** 540000 **Budget base:** 6000 **Actual cost:** 556000 **Actual base:** 5700 **Pool:** Assembly **Base:** Direct labor hours **Budget cost:** 210000 **Budget base:** 10500 **Actual cost:** 205000 **Actual base:** 10900 **Pool:** Receiving and stores **Base:** Material cost **Budget cost:** 96000 **Budget base:** 1600000 **Actual cost:** 99000 **Actual base:** 1540000 **Pool:** Quality and inspection **Base:** Direct labor cost **Budget cost:** 130000 **Budget base:** 780000 **Actual cost:** 128000 **Actual base:** 800000 **Totals:** #### Budgeted overhead **Key:** budget_cost **Agg:** sum **Format:** currency #### Actual overhead **Key:** actual_cost **Agg:** sum **Format:** currency #### Applied overhead **Key:** applied **Agg:** sum **Format:** currency #### Net over or under absorption **Key:** variance **Agg:** sum **Format:** currency **Summary:** #### Share of actual overhead absorbed **Key:** absorbed_pct **Expr:** sum_applied / sum_actual_cost * 100 **Format:** percent **Primary:** true **Notes:** Rate equals budgeted cost divided by budgeted base. Applied overhead equals that rate times actual base. A negative variance means jobs absorbed less than the plant spent. Use a base that actually drives the cost in each pool. ## Content **Intro:** A single plant-wide overhead rate hides which departments cost what. This worksheet splits overhead into pools, picks an allocation base for each and shows how much overhead your jobs actually absorbed. Edit the rows and export to Excel for the month-end pack. **Sections:** #### How to use the worksheet **Body:** Create a row for each cost pool, such as machining, assembly, stores and quality. Choose the base that best drives the cost: machine hours for equipment-heavy cells, direct labor hours for hand assembly, material cost for purchasing and stores. Enter the budgeted annual cost and base, which sets the rate. Later enter actuals to see applied overhead and the variance. Use the totals to reconcile with the general ledger. **Bullets:** - Use budgeted base capacity that you realistically expect to run - Keep pools homogeneous so the base fits the cost - Update rates when the budget changes materially #### The method behind it **Body:** This is predetermined overhead absorption. The rate equals budgeted overhead divided by the budgeted allocation base. Jobs are charged at that rate times the actual base they consume. At period end, the difference between applied and actual overhead is over or under absorbed. Unlike a single rate, multiple pools reflect that a machining hour and an assembly hour do not cost the same. It is a simplified relative of activity based costing. #### Reading the result **Body:** In the example, CNC machining ran fewer hours than budget while its spend rose, so it under absorbed. Assembly ran more hours than planned and over absorbed. Persistent variance in one pool signals a wrong rate, wrong base or capacity problem. Small variances are usually written to cost of goods sold at period end, while large ones may be prorated across inventory and cost of sales according to your accounting policy and local rules. #### Common mistakes and ERP notes **Body:** Common faults are mixing fixed and variable cost in one pool without realizing it, choosing labor hours for an automated cell, and never revisiting the budget. In SAP this maps to cost centers, activity types and overheads costing sheets; in other ERPs it is a burden rate on a department or work center. Check how your system applies the rate to routing times, since standard hours and actual hours may differ. **Cta:** If you want overhead variance by pool pulled from your ERP each month, we can help build a read-only report that shows the numbers and the query behind them. **Faq:** #### How many cost pools should I use? **Answer:** Start with the departments or work center groups that differ in cost behavior, usually three to six. More pools add accuracy only if the base meaningfully differs. Too many pools create maintenance work without improving decisions. #### What is under absorbed overhead? **Answer:** It means applied overhead was less than actual overhead spend, often because volume or hours came in below the budget used to set the rate. Most companies adjust it through cost of goods sold or prorate it, per policy. #### Which allocation base is best? **Answer:** The one that best explains why the cost occurs. Machine hours suit machine-driven cost, labor hours suit manual work, and material cost suits handling and purchasing. Test by checking whether the pool cost rises when the base does. #### Is this the same as activity based costing? **Answer:** It is a simpler cousin. Pools with different bases capture some of the benefit, but full activity based costing uses more drivers tied to specific activities. For many job shops, pooled rates are sufficient and easier to maintain. ## Search terms overhead cost pool allocation worksheet, predetermined overhead rate excel, over absorbed under absorbed overhead template, manufacturing overhead pools, cost pool rate calculation, overhead variance worksheet, burden rate by department --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json