The Spreadsheet Conundrum

Ken Pugh ken@kenpugh.comhttps://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