Sample Analysis

Project: Provide dedicated "beat" cops to patrol the retail district.

The logic of this intervention is based on that surveys have shown shoppers opt for suburban malls because of safety concerns, and a belief that a police presence has other positive effects through reduction of minor problems. Police services can be "purchased" fractionally in terms of 50% of an officer's time.

stop-and-think-small.gif Do you agree with the categorization of outputs, outcomes, and impacts in the above diagram? Starting from the right, how would you measure each of the results in the diagram? From a graphic design perspective, can you suggest any tweaks to this diagram to improve it?

The benefit is measured in terms of increased sales tax collection based on increases in sales. We begin with data on how much it would cost to hire security staff and what the expected benefits from increased sales would be.

stop-and-think-small.gif Before continuing, let's take a look at the numbers in the table. What do the columns and rows represent? What's the basic in the costs column? How about the benefits column?

Net Benefits

The first step in CBA is to calculate net benefits. This is simply the everyday activity of subtracting cost of something from its value to us. The word "net," though, implies that we are taking into account ALL costs and ALL benefits. As you can imagine, what counts as "all" can make a big difference. More on that later.

Net benefit is just total benefits minus total costs:

Once we have written a formula in Excel, we can use [WWW]"'''autofill'''" to propagate the formula to other cells with Excel making the appropriate changes. Here, for example, our formula is this

The new contents of the cell in column D should be the difference of the value in columns B and C (in this same row).

When we use autofill, we drag the formula down and Excel updates the cell references so that in all of column D the formula is the B cell for that row minus the C cell for that row:

So, here's what we get

picture of data with net benefits

Drawing chart of net benefits

Suppose the proposal on the table is to hire 4 officers to patrol the shopping district. Does the benefit outweigh the cost?

Suppose we face the question of hiring zero, two or four officers.

Finally, suppose we know that we want to deploy some police in our shopping district but we want to determine the optimal number to deploy.