Allocate Shared Costs Across Departments with Python

Every department P&L has a line for costs it did not choose: a share of the office rent, the IT team, HR, the finance function. How those shared costs are spread changes which departments look profitable, so the method has to be fair, explainable and exactly reproducible. In a spreadsheet the allocation usually lives in a grid of formulas that nobody wants to touch. In Python it is a small table of drivers and a few lines of arithmetic, and the same numbers can be written back to Excel with formulas for review. This page is part of Building Finance and Operations Reports.

Prerequisites

pip install pandas openpyxl xlsxwriter

allocation_inputs.xlsx has two sheets. Departments lists the seven departments for the quarter: four operating departments that serve customers and three service departments (IT, HR, Finance) that serve the others, each with its direct costs and the measures used as drivers: floor area in square metres, headcount, devices supported and finance transactions processed. Pools lists the cost pools and the driver each is spread by. Rent is a pool of its own, 84,000 for the quarter; the three service departments' own direct costs are the other pools.

Choosing drivers

A driver should be the thing that makes the cost go up. Rent follows floor area; IT support follows devices, more than headcount; HR effort follows headcount; finance effort follows the number of transactions a department generates. A driver that is easy to measure but unrelated to the cost, such as allocating everything by revenue, makes the allocation look precise while shifting cost to whoever sells most.

Step 1 — Shares that add up exactly

A department's share of a pool is its driver divided by the total driver. Multiplying the pool by the shares and rounding each result to cents rarely adds back to the pool: three departments of equal size sharing 100.00 get 33.33 each, and a cent disappears. The largest-remainder method fixes that: round everything down to the cent, then hand the missing cents to the departments with the largest fractional remainders:

import numpy as np
import pandas as pd

def allocate(amount, weights):
    """Split amount across weights in proportion, to the cent, summing exactly to amount."""
    weights = pd.Series(weights, dtype=float)
    cents = round(amount * 100)
    raw = cents * weights / weights.sum()
    floor = np.floor(raw).astype(int)
    short = cents - floor.sum()
    order = (raw - floor).sort_values(ascending=False, kind="stable").index[:short]
    floor[order] += 1
    return floor / 100

print(allocate(100.00, {"A": 1, "B": 1, "C": 1}).to_dict())
print(allocate(84000.00, {"Sales": 310, "Support": 180, "Ops": 260}).to_dict())

The results always add to the pool to the cent, which matters when the allocated amounts are posted as journals and the pool account must end at zero.

Step 2 — The direct method

The simplest method allocates every pool straight to the operating departments, ignoring that service departments use each other. IT supports HR's laptops, but in the direct method HR's share of IT is simply not charged:

depts = pd.read_excel("allocation_inputs.xlsx", sheet_name="Departments").set_index("Department")
pools = pd.read_excel("allocation_inputs.xlsx", sheet_name="Pools")
service = depts.index[depts["Kind"] == "Service"]
operating = depts.index[depts["Kind"] == "Operating"]

pool_amounts = {"Rent": 84000.00, **{p: depts.loc[p, "DirectCost"] for p in ["IT", "HR", "Finance"]}}
driver_of = dict(zip(pools["Pool"], pools["Driver"]))

direct = pd.DataFrame({
    pool: allocate(amount, depts.loc[operating, driver_of[pool]])
    for pool, amount in pool_amounts.items()
})
direct["Total allocated"] = direct.sum(axis=1)
print(direct.round(2).to_string())
print("pools:", sum(pool_amounts.values()), " allocated:", direct["Total allocated"].sum().round(2))

Step 3 — The step-down method

The step-down (sequential) method recognises that service departments serve each other. Service departments are allocated one at a time, in a fixed order: each spreads its costs, including whatever it has received from earlier steps, over the operating departments and the service departments not yet allocated. Rent goes first, because every department uses space; then the service department that serves the others most:

ORDER = ["Rent", "IT", "HR", "Finance"]
remaining = list(service)                  # service departments still able to receive costs
balance = {d: 0.0 for d in depts.index}    # costs received so far
for d in service:
    balance[d] += depts.loc[d, "DirectCost"]

steps = {}
for pool in ORDER:
    if pool == "Rent":
        amount, receivers = 84000.00, list(depts.index)
    else:
        amount = round(balance[pool], 2)
        balance[pool] = 0.0
        remaining.remove(pool)
        receivers = list(operating) + remaining
    shares = allocate(amount, depts.loc[receivers, driver_of[pool]])
    steps[pool] = shares
    for d, v in shares.items():
        balance[d] += v

step = pd.DataFrame(steps).fillna(0.0)
print(step.round(2).to_string())
left = {d: round(balance[d], 2) for d in service}
print("left in service departments:", left)

Every service department ends at zero: its own costs and everything it received were passed on. The order matters; a different order gives different answers, so it is a policy that should be written down, usually "the department that provides the most service to other service departments goes first".

Step 4 — Compare the methods

The totals must match: both methods allocate exactly the same costs. Which department carries them differs:

compare = pd.DataFrame({
    "Direct": direct["Total allocated"],
    "Step-down": step.loc[operating].sum(axis=1),
})
compare["Difference"] = compare["Step-down"] - compare["Direct"]
print(compare.round(2).to_string())
print("totals:", compare[["Direct", "Step-down"]].sum().round(2).to_dict())

Engineering carries about 1,900 less under step-down, and Sales and Operations about 1,000 more each. Two things move the costs. Rent is now spread over all seven departments, so part of it lands in IT, HR and Finance and from there follows devices, headcount and transactions instead of floor area, and Engineering has the largest floor area. And Finance, which grows from 27,600 to about 35,900 as it receives its shares of rent, IT and HR, is allocated by transactions, which Sales and Operations generate most of. The differences are modest here; they grow when service departments are large and use each other heavily. A third option, the reciprocal method, solves simultaneous equations so that service departments charge each other both ways; it is the most accurate and the hardest to explain, and most businesses settle for step-down.

Step 5 — Department results after allocation

The point of the exercise: each operating department's direct costs plus its allocated share, and for the revenue-earning department, its contribution after overheads:

result = depts.loc[operating, ["Revenue", "DirectCost"]].copy()
result["Allocated"] = step.loc[operating].sum(axis=1)
result["Fully loaded cost"] = result["DirectCost"] + result["Allocated"]
result["Allocated % of direct"] = result["Allocated"] / result["DirectCost"] * 100
print(result.round(1).to_string())

Step 6 — Write the workbook with formulas

Finance will want to see how each number was produced. Write the drivers and pools as inputs, and the direct-method allocation as formulas that read them, pool × driver / SUM(drivers). The step-down results are written as values from Python, with the order and receivers documented beside them:

import xlsxwriter
from xlsxwriter.utility import xl_rowcol_to_cell, xl_col_to_name

wb = xlsxwriter.Workbook("cost_allocation_q3.xlsx")
money = wb.add_format({"num_format": "#,##0.00"})
head = wb.add_format({"bold": True, "bg_color": "#EEF2FF", "bottom": 1})

inp = wb.add_worksheet("Inputs")
cols = ["FloorArea", "Devices", "Headcount", "Transactions"]
inp.write_row(0, 0, ["Department"] + cols, head)
for r, d in enumerate(operating, start=1):
    inp.write(r, 0, d)
    for c, col in enumerate(cols, start=1):
        inp.write_number(r, c, float(depts.loc[d, col]))
inp.write_row(len(operating) + 3, 0, ["Pool", "Amount", "Driver column"], head)
for i, (pool, amount) in enumerate(pool_amounts.items()):
    r = len(operating) + 4 + i
    inp.write(r, 0, pool)
    inp.write_number(r, 1, amount, money)
    inp.write(r, 2, driver_of[pool])
inp.set_column(0, 4, 14)

out = wb.add_worksheet("Direct method")
out.write_row(0, 0, ["Department"] + list(pool_amounts) + ["Total"], head)
n = len(operating)
for r, d in enumerate(operating, start=1):
    out.write(r, 0, d)
    for c, pool in enumerate(pool_amounts, start=1):
        dcol = xl_col_to_name(1 + cols.index(driver_of[pool]))
        amount_cell = "Inputs!" + xl_rowcol_to_cell(n + 4 + c - 1, 1, row_abs=True, col_abs=True)
        out.write_formula(r, c, f"=ROUND({amount_cell}*Inputs!{dcol}{r + 1}/SUM(Inputs!{dcol}$2:{dcol}${n + 1}),2)", money)
    out.write_formula(r, len(pool_amounts) + 1, f"=SUM(B{r + 1}:{xl_col_to_name(len(pool_amounts))}{r + 1})", money)
out.write(n + 1, 0, "Total", head)
for c in range(1, len(pool_amounts) + 2):
    out.write_formula(n + 1, c, f"=SUM({xl_col_to_name(c)}2:{xl_col_to_name(c)}{n + 1})", money)
out.set_column(0, 5, 14)

sd = wb.add_worksheet("Step-down")
sd.write(0, 0, "Order: " + " → ".join(ORDER) + ". Each service department allocates to operating departments and to service departments later in the order.")
sd.write_row(2, 0, ["Department"] + ORDER + ["Total"], head)
for r, d in enumerate(step.index, start=3):
    sd.write(r, 0, d)
    for c, pool in enumerate(ORDER, start=1):
        sd.write_number(r, c, float(step.loc[d, pool]), money)
    sd.write_formula(r, len(ORDER) + 1, f"=SUM(B{r + 1}:E{r + 1})", money)
sd.set_column(0, 5, 14)
wb.close()
print("written")

The formula version uses Excel's ROUND per cell, so it can differ from the pool total by a cent, and here it does: recalculated, the IT column adds up to 52,499.99, because Operations' share rounds to 6,273.58 where the largest-remainder allocation gave it the missing cent, 6,273.59. That is the reason to post the Python figures and keep the formulas as an explanation. The note in the step-down sheet records the order, because without it the numbers cannot be reproduced.

Common pitfalls

• Rounding that does not tie out. Round each share independently and totals drift by cents; use largest-remainder allocation.
• Undocumented order. Step-down results depend on the order of service departments; record it with the report.
• Allocating to the department itself. A service department's own driver share is excluded when it allocates its costs.
• Stale drivers. Floor plans and device counts change; date the driver table and refresh it each period.
• Drivers that do not drive cost. Revenue-based allocations penalise growth and hide which departments actually consume the service.

Conclusion

Pick one driver per cost pool that reflects what makes the cost rise, split each pool with largest-remainder rounding so it ties out to the cent, and choose between the direct method and the step-down method, which passes service departments' costs through each other in a documented order. Compare the methods, show fully loaded costs by operating department, and write an Excel workbook whose inputs and formulas let finance trace every number.

Frequently asked questions

How do I allocate costs by percentage in pandas? Divide each department's driver by the total driver to get its share, multiply by the cost pool, and round with a largest-remainder method so the rounded amounts still add up to the pool.

What is the difference between the direct and step-down methods? The direct method allocates service department costs only to operating departments. The step-down method allocates service departments one at a time, including to service departments later in the sequence, so their use of each other is partly recognised.

Why don't my allocated amounts add up to the total? Because each amount was rounded independently. Round down to the cent, then give the leftover cents to the departments with the largest remainders.

What drivers should I use for overhead allocation? Use a measure that causes the cost: floor area for rent, devices or users for IT, headcount for HR, transactions for finance. Document the choice and review it yearly.

Related

• Up one level: Building Finance and Operations Reports — the rest of the finance reports.
• Build a Profit and Loss Statement from a General Ledger — the statement these costs land in.
• Build a Budget vs Actual Variance Report — comparing allocated costs with budget.
• Build a Headcount and Attrition Report — the source of the headcount driver.
• Calculate Percent of Total per Group in pandas — the share calculation on its own.