The Spreadsheet Conundrum

Ken Pugh ken@kenpugh.com https://kenpugh.com

The spreadsheet is a simple analogy to allocate responsibilities for many organizational and design decisions you need to make.  Responsibilities can be assigned by rows, by columns, or by some combination of the two.  The spreadsheet conundrum gives you suggestions on how you can do that assignment. 

An Insurance Example

For example, in an insurance company, there are different types of policies and different activities for each of the policies.

Type/Activity	Sell Policy	Renew Policy	Pay Claim	Audit Claims
Farm
Home
Life
Auto

The responsibilities could be assigned so that each activity for a policy type is performed by the same unit (individual, team, or larger).  This is shown by the ovals on the row which encompass one unit’s responsibilities

Type/Activity	Sell Policy	Renew Policy	Pay Claim	Audit Claims
Farm
Home
Life
Auto

Alternatively, the responsibilities could be assigned so that for each activity the same unit is responsible for all types of policies.  This is shown by the ovals on the columns which encompass one unit’s responsibilities: 

Type/Activity	Sell Policy	Renew Policy	Pay Claim	Audit Claims
Farm
Home
Life
Auto

Note that one unit might be responsible for two or more of the activities.   For example, Sell Policy and Renew Policy might be the responsibility of the same unit.   This could be shown as:

Type/Activity	Sell Policy	Renew Policy	Pay Claim	Audit Claims
Farm
Home
Life
Auto

Another alternative is to assign units to most of the activities for a policy type and then one unit for one activity (e.g. Audit Claims) across all policies.  

Type/Activity	Sell Policy	Renew Policy	Pay Claim	Audit Claims
Farm
Home
Life
Auto

A Development Example

In a software development, there are different products and different activities to provide each product.   The activities for each product might be the responsibility of a single unit or multiple products might be shared by the same unit.   In this case, the units represent complete cross-functional teams. 

Activity/Product	Product 1	Product 2	Product 3
User Interface
Backend
Customer
Testing
User Experience
Security
Infrastructure
Data Analysis
External Acquisitions

One could assign responsibilities for each activity to a unit:  

Activity/Product	Product 1	Product 2	Product 3
User Interface
Backend
Customer
Testing
User Experience
Security
fInfrastructure
Data Analysis
External Acquisitions

Some of the activities for a product might be the responsibility of a unit.  Other activities (e.g. infrastructure) are performed by unit for all projects.   There are many possibilities in which responsibilities are arranged by product and activity.  This is just one of them.

Activity/Product	Product 1	Product 2	Product 3
User Interface
Backend
Customer
Testing
User Experience
Security
Infrastructure
Data Analysis
External Acquisitions

For some activities, there could be two more units that provide that activity for different products.  For example, two units might provide User Experience for different products:

Activity/Product	Product 1	Product 2	Product 3
User Experience

Tradeoffs

There are tradeoffs in how you assign the responsibilities.   Your decisions are based on organizational and design principles, context, the group structure, the complexity of the responsibilities, commonality, and so forth.   The spreadsheet gives a simple diagram to discuss the potential groupings and tradeoffs.

You might try creating a spreadsheet for your current allocation of responsibilities to see how they line up. 

References

Section 3.11 in Prefactoring: Extreme Abstraction, Extreme Separation, Extreme Readability