Can and Should we go All In for Ring #4? Geeky Spreadsheet Stuff


via Ernie Adams loves Excel, as much as Belichick loves customised Hoodies

This is a fan post for the number nuts, and those with too much spare time. It is too explain how I've constructed my Cap Spreadsheet, so you can check it out for yourselves, and make your own changes to it, if you so desire. The main post for those who don't have tape holding their glasses together is over here.

The Basics

Where I have created contract information, the spreadsheet has little automation. So if you change the signing bonus, and/or total value of the contract, you'll need to manually change the cap hits, as it was too much effort to try and automate the whole thing.

Also if you want to add rows to the spreadsheet, try and add them in the middle of the date, and then copy and paste the data up. If you add them just above the totals, then the SUM and COUNT functions won't include your expanded range.

In the NFL only the top 51 contracts count against the salary cap, which I refer to as 'The rule of 51'. So to keep the numbers working, I've made sure that each year, has only 51 guys counting against the cap, by adding or subtracting the appropriate number of minimum price contracts. This means that any new or optional contracts that are added accrue an additional cap hit of that contract, minus the cost of what has now become the 52nd most expensive contract. To keep it simple, I've used $495,000 as that is the normal cost of the cheapest guys.

So let's take it One Sheet at a time.


This sheet gives predicted extensions for guys who are either not worth their cap hit, or I'd like to extend and shift some of the pain down the road. Each guy has the year they'd be restructured in, their current dead money, which would hit the cap anyway and can't be pushed back, and then my proposed parameters for their new deal.

Fairly straightforward, but one extra point. Because the individual year pages already include the dead money hit, I have a second total that excludes it, to make sure it isn't double counted. That's what the 'Extra On Cap' row is, it is the money we'd give out, on top of the dead money for cutting them, by restructuring them.

The optional restructures aren't included in my main figures for each year, but are added in later just to show what would happen if we kept 'everyone'. The totals include the deduction for the rule of 51.

FA Retained

A fairly straightforward sheet, with four categories of FAs. The Key guys as well as the Backups, are automatically included in the calculations. With the other categories Luxury Guys, and Further Depth not being included automatically, and so the totals factor in the rule of 51.

Draft Contracts

There is not much to see here, although you could experiment with changing the value assuming we have traded down, or up.

FA Signed

This has been left blank, but with live totals, that will carry the cumulative data into the individual years. This would be the best place to make additional modifications, and there is a box just below the cap figure that on each year sheet that will show their impact. You can speculate with your own figures for outside FAs, like Boldin or Mack, or put in numbers for guys you don't want to see leave, like Edelman, and see how it all works out.

201x Patriots Season

Each season should be fairly straightforward. The main bulk of the data is a copy and paste from spotrac with the contract data. Guys in red I'm suggesting are cut/restructured, and I have edited their cap figure to be their dead money figure.

At the bottom this is added together taking into account cap nuances, to provide a number with our cap space on. There is a box for the rule of 51, and the number next to it, is just showing how many guys have been added or subtracted. The cap rollover box is left blank, with the needed formula in the box to the right. This is because it seems more useful to me to know how much free money is where, and rolling it over would make it too easy to double count it. If you want to you can copy and paste it into the appropriate box.

Below the two boxes, are additional numbers that include all the key resignings, FAs, and the luxury and extra back up guys.

At the top on the right, is the list of who we've kept in previous years, that isn't on the main list, and who has been lost to FA that year, and who I've elected to retain, and guys that one might choose to retain, but haven't been factored into the number,s and so are considered to have also left. This includes draft pick numbers, so it is possible to check that enough guys have been added or subtracted to make the rule of 51 work.

Well I hope you have fun messing about with the spreadsheet, and that we can have some good informed discussions moving forward about just what is possible and sensible with the cap situation we have.

The views expressed in these FanPosts are not necessarily those of the writers or SBNation.

Log In Sign Up

Log In Sign Up

Forgot password?

We'll email you a reset link.

If you signed up using a 3rd party account like Facebook or Twitter, please login with it instead.

Forgot password?

Try another email?

Almost done,

Join Pats Pulpit

You must be a member of Pats Pulpit to participate.

We have our own Community Guidelines at Pats Pulpit. You should read them.

Join Pats Pulpit

You must be a member of Pats Pulpit to participate.

We have our own Community Guidelines at Pats Pulpit. You should read them.




Choose an available username to complete sign up.

In order to provide our users with a better overall experience, we ask for more information from Facebook when using it to login so that we can learn more about our audience and provide you with the best possible experience. We do not store specific user data and the sharing of it is not required to login with Facebook.