# Service Cost Center Allocation Worksheet > Source: https://netray.co/tools/service-cost-center-allocation-worksheet > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: Free Tools ## Short answer Spread maintenance, quality, stores and other service costs to production lines using a driver, with leftover cost shown. ## Category cost-finance ## Industry Discrete Manufacturing ## Audience - Cost accountant - Controller - Plant finance ## Tool **Kind:** worksheet **Mode:** editable **Currency:** $ **Columns:** #### Service cost center **Key:** service **Type:** text #### Allocation driver **Key:** driver **Type:** select **Options:** - Machine hours - Labor hours - Square feet - Headcount - Work orders - Receipts processed #### Period cost **Key:** cost **Type:** number **Format:** currency #### Total driver units **Key:** driver_total **Type:** number #### Driver units, Line A **Key:** units_a **Type:** number #### Driver units, Line B **Key:** units_b **Type:** number #### Cost per driver unit **Key:** rate **Type:** number **Expr:** cost / driver_total **Format:** decimal #### Allocated to Line A **Key:** alloc_a **Type:** number **Expr:** cost / driver_total * units_a **Format:** currency #### Allocated to Line B **Key:** alloc_b **Type:** number **Expr:** cost / driver_total * units_b **Format:** currency #### Unallocated **Key:** left **Type:** number **Expr:** cost - cost / driver_total * (units_a + units_b) **Format:** currency **Rows:** **Service:** Maintenance **Driver:** Machine hours **Cost:** 84000 **Driver total:** 12000 **Units a:** 7000 **Units b:** 4200 **Service:** Quality and inspection **Driver:** Work orders **Cost:** 56000 **Driver total:** 1400 **Units a:** 600 **Units b:** 700 **Service:** Stores and receiving **Driver:** Receipts processed **Cost:** 62000 **Driver total:** 5200 **Units a:** 2800 **Units b:** 2000 **Service:** Facilities **Driver:** Square feet **Cost:** 91000 **Driver total:** 60000 **Units a:** 30000 **Units b:** 24000 **Service:** Production planning **Driver:** Work orders **Cost:** 47000 **Driver total:** 1400 **Units a:** 600 **Units b:** 700 **Totals:** #### Total service cost **Key:** cost **Agg:** sum **Format:** currency #### Total to Line A **Key:** alloc_a **Agg:** sum **Format:** currency #### Total to Line B **Key:** alloc_b **Agg:** sum **Format:** currency #### Total unallocated **Key:** left **Agg:** sum **Format:** currency **Summary:** #### Unallocated share of service cost **Key:** left_pct **Expr:** sum_left / sum_cost * 100 **Format:** percent **Primary:** true **Description:** The remainder is cost used by other areas such as shipping or admin. **Notes:** This is a direct allocation: each service cost goes to production lines only, not to other service centers. Total driver units should include every consumer, so the unallocated remainder shows usage outside the two lines. Add columns for more lines if needed in Excel. ## Content **Intro:** Maintenance, quality, stores and facilities do not make parts, but their cost belongs in the cost of parts. This worksheet allocates each service cost center to production lines using a driver you choose, so product cost reflects the support each line really uses. **Sections:** #### How to use the worksheet **Body:** List each service cost center with its period cost. Choose the driver that best explains consumption: machine hours for maintenance, work orders for inspection and planning, receipts for stores, square feet for facilities. Enter the total driver units across the plant and the units used by each production line. The worksheet computes a rate per unit and allocates to each line, then shows what remains unallocated. Export to Excel to feed overhead rates. #### Method and formulas **Body:** Rate = service cost divided by total driver units. Allocation to a line = rate times that line's driver units. This is the direct method: it ignores services that service centers provide to each other. The step-down or reciprocal methods are more precise, but the direct method is common and usually close enough unless service centers rely heavily on each other. The unallocated remainder is cost for consumers you did not list, such as shipping or administration. #### Reading the output **Body:** Compare allocated cost per line with revenue or direct labor. A line that looks profitable under a plant-wide rate may be expensive once maintenance and quality are allocated by actual use. A large unallocated remainder means either the driver totals include other consumers or the line list is incomplete. Decide whether to add another line column or to move the remainder to a general overhead pool. #### Common mistakes **Body:** Using headcount as the driver for everything is easy but rarely tracks cost. Another mistake is changing drivers each month, which makes trends meaningless. Also, check that driver data comes from a reliable source, such as work order counts from the maintenance system or machine hours from shop floor reporting, not estimates. Review the drivers once a year. **Cta:** If drivers like machine hours, work orders and receipts are scattered across systems, Netray can help bring them together from your ERP and maintenance data. **Faq:** #### What is service cost center allocation? **Answer:** It is the process of assigning the cost of support departments such as maintenance, quality and stores to the production departments or product lines that use them, so product cost includes support activity. #### What is the direct method of allocation? **Answer:** The direct method allocates each service department cost straight to production departments, ignoring services between service departments. It is simple and widely used. Step-down and reciprocal methods add precision when service departments serve each other heavily. #### How do I choose an allocation driver? **Answer:** Choose a measure that moves with the cost: machine hours for maintenance, number of work orders for inspection, receipts for stores. If two drivers fit, pick the one you can measure reliably each period. #### What does unallocated cost mean here? **Answer:** It is the share of service cost used by consumers not listed in the worksheet, such as shipping, administration or other plants. Either add those consumers or assign the remainder to a general overhead pool. ## Search terms service department cost allocation worksheet, cost center allocation excel, overhead allocation by driver template, maintenance cost allocation to production, direct method cost allocation, manufacturing cost center allocation, indirect cost allocation worksheet --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json