Spreadsheets for Startups and New Business Plans

Resource Allocation Tables

In my last post, I discussed tying expenses to hiring. One of the more powerful ways I extend this is with a resource allocation table. It really saves a lot of time while removing a real risk factor when it comes to keeping the model in synch with changes in the hiring plan.

For one of my latest clients, I defined every employee and contractor as belonging to one of the following roles:

• none


• standard


• traveling


• field


• it

I then used a matrix to indicate what operating and capital expenditures were tied to each role. For example, traveling personnel were allocated a desk, telephone, laptop computer, cell phone and remote internet access. Consultants covering their own expenses were assigned to role "none".

My department worksheet always has a few rows to calculate headcount. This includes a separate row for the headcount associated with each role. Each expense category based on resource allocations is then defined by a pair of rows:

• A row to define allocation in units, which is calculated by matching the allocation definitions in the resource allocation matrix with each role and multiplying by the headcount in that role in the given month


• A row that translates the unit allocations into dollars, multiplying units by ongoing monthly expense and multiplying increases in units by one-time expense

posted by Scott Clyde @ 11:17 AM