Welcome to PatsFans.com

GM Excel Spreadsheet

Discussion in 'PatsFans.com - Patriots Fan Forum' started by DocHoliday, Feb 26, 2013.

Thread Status:
Not open for further replies.
  1. DocHoliday

    DocHoliday Rookie

    Joined:
    Dec 28, 2010
    Messages:
    2,755
    Likes Received:
    0
    Ratings:
    +0 / 0 / -0

    After taking a look at the "You be the GM" thread, I decided it'd be far easier if you could do all the legwork in an excel spreadsheet.

    So I made one. I'm sure some of you have your own versions, but I decided to make one for everyone else. If you have any additions or upgrades, feel free to add it on and reupload.

    I added the current roster, positions, ages, and set up the spreadsheet so it automatically counts the numbers of players per position. There are two tabs - base and projected. Base is the current as of now roster and projected is exactly the same, but it allows you to change the roster around without making any changes you can't easily reverse by duplicating the base tab again.

    I also instituted a 'cap hit' feature that allows you to enter in the cap hit for the year. I did it for all currently signed players barring Jake Ballard and Jason Vega, whose numbers I could not find. I copied my cap hit numbers from from spotrac.com

    You can copy paste your work fairly easily, but I'm working on making it more readable to the forum. I'm eager to see what you guys come up with.

    Screenshot: http://i.imgur.com/Zm6n7H2.png

    It's attached as a zip file. Extract and open with Excel or OpenOffice Calc

    Attached Files:

  2. ausbacker

    ausbacker Brady > Manning. PatsFans.com Supporter

    Joined:
    Apr 23, 2008
    Messages:
    12,963
    Likes Received:
    17
    Ratings:
    +22 / 0 / -0

    #51 Jersey

    I'd prefer to sit here and force a psychic connection where you attempt to guess the moves I make via telepathy Doc. Is that too much to ask for?
  3. DocHoliday

    DocHoliday Rookie

    Joined:
    Dec 28, 2010
    Messages:
    2,755
    Likes Received:
    0
    Ratings:
    +0 / 0 / -0

    Trade Brady and a first round pick for Rex Grossman?
  4. DocHoliday

    DocHoliday Rookie

    Joined:
    Dec 28, 2010
    Messages:
    2,755
    Likes Received:
    0
    Ratings:
    +0 / 0 / -0

    Update: I can't sleep, so I went back and added 'Dead money', 'cap rollover', and filled in all the cap hit numbers except free agents (obviously), Jake Ballard, and Jason Vega, whose numbers I could not find.

    Enjoy
  5. kurtinelson

    kurtinelson Rookie

    Joined:
    Nov 17, 2004
    Messages:
    2,365
    Likes Received:
    4
    Ratings:
    +4 / 0 / -0

    Nice Job! Its usefully to see entire roster laid out like that. I'm surprised Gronk only has a 2.7 mil cap hit this year. Is that number going to explode next year? Other surprises - Gostkowski at 3.4 mil - is an extension in order? Best value to the team - Ninko at 2 mil and change.
  6. betterthanthealternative

    betterthanthealternative Rookie

    Joined:
    Aug 27, 2006
    Messages:
    2,156
    Likes Received:
    0
    Ratings:
    +0 / 0 / -0

    This is a very nice piece of work and an appreciated contribution.
  7. ForThoseAboutToRock

    ForThoseAboutToRock Rookie

    Joined:
    Dec 4, 2007
    Messages:
    844
    Likes Received:
    0
    Ratings:
    +0 / 0 / -0

    Seconded! Cheers and thanks for the hard work (and insomnia!) :peace:
  8. patfanken

    patfanken Rookie

    Joined:
    Jul 11, 2005
    Messages:
    5,491
    Likes Received:
    26
    Ratings:
    +36 / 0 / -2

    #91 Jersey

    We will all be well served by your efforts, Doc. Thanks

    In fact if there are any moderators "listening", this should probably be a candidate for becoming a sticky, so more will take advantage of it when we are doing our "speculations" this LOOOOONNNNNNGGGGG off season.
  9. DocHoliday

    DocHoliday Rookie

    Joined:
    Dec 28, 2010
    Messages:
    2,755
    Likes Received:
    0
    Ratings:
    +0 / 0 / -0

    Updated again. This version is much more resilient to modification (equations would mess up when you would cut and paste things).

    I also filled in Ballard's cap numbers, but still can't find Vega's.

    Enjoy

    Attached Files:

  10. DocHoliday

    DocHoliday Rookie

    Joined:
    Dec 28, 2010
    Messages:
    2,755
    Likes Received:
    0
    Ratings:
    +0 / 0 / -0

    Another update.

    Version 1.3

    Changes:

    Reworked some of the counting equations to make them even more resilient to cutting and pasting. Should be good now. Also added a spot on the LBs.

    Jason Vega's numbers were added, and some other players had their ages and positions fixed. Eg Ninko moved to LB but his role listed as LB/DE.

    Attached Files:

  11. AƟƟynormal

    AƟƟynormal PatsFans.com Supporter PatsFans.com Supporter

    Joined:
    May 19, 2009
    Messages:
    667
    Likes Received:
    1
    Ratings:
    +1 / 0 / -0

    Thanks for the schedule.


    Minor note: I think we still have rights to Pryor.
  12. DocHoliday

    DocHoliday Rookie

    Joined:
    Dec 28, 2010
    Messages:
    2,755
    Likes Received:
    0
    Ratings:
    +0 / 0 / -0

    That we do - his contract was tolled I believe because he was ineligible for the season. He should be in there, I listed him as DT right after Forston.

    Thanks for downloading!
  13. murphysbuddylove

    murphysbuddylove Rookie

    Joined:
    Sep 24, 2007
    Messages:
    71
    Likes Received:
    0
    Ratings:
    +0 / 0 / -0

    Awesome work. I've made a copy and can play now.
  14. DocHoliday

    DocHoliday Rookie

    Joined:
    Dec 28, 2010
    Messages:
    2,755
    Likes Received:
    0
    Ratings:
    +0 / 0 / -0

    Update time!

    Version 1.32

    Minor update. Reflects the release of Kyle Hix and Spencer Larson, and takes into account their dead money hits.

    If you want to update your own version rather than start all over, just replace the dead money number with $4,637,525.00

    Attached Files:

  15. Miguel

    Miguel Patriots Salary Cap Guru PatsFans.com Supporter

    Joined:
    Sep 13, 2004
    Messages:
    3,980
    Likes Received:
    6
    Ratings:
    +7 / 0 / -0

  16. DocHoliday

    DocHoliday Rookie

    Joined:
    Dec 28, 2010
    Messages:
    2,755
    Likes Received:
    0
    Ratings:
    +0 / 0 / -0

    Thank you for the estimates, I just winged them for my draft picks.

    The existing numbers were taken from Sportrac. For the missing (Quarles and Miller) would you just multiply 6000 * 52 weeks for the year?
  17. Miguel

    Miguel Patriots Salary Cap Guru PatsFans.com Supporter

    Joined:
    Sep 13, 2004
    Messages:
    3,980
    Likes Received:
    6
    Ratings:
    +7 / 0 / -0

    6000 X 17 weeks of the regular season. I would not use any players' current salary for their Practice Squad Salary as most PS players are signed for the minimum. You may want to add a fudge factor for a couple of them as most teams pay a couple of their PS players more than minimum.
    Last edited: Mar 2, 2013
  18. DocHoliday

    DocHoliday Rookie

    Joined:
    Dec 28, 2010
    Messages:
    2,755
    Likes Received:
    0
    Ratings:
    +0 / 0 / -0

    They're not paid in preseason? Or do those numbers not count for cap?
  19. mgteich

    mgteich PatsFans.com Veteran PatsFans.com Supporter

    Joined:
    Sep 13, 2004
    Messages:
    19,975
    Likes Received:
    24
    Ratings:
    +28 / 1 / -0

    I don't sweat the late costs. There are many more the the Practice Squad. There are the two players not included in the cap. There the rookies. There is an in-season injury fund. There is paying for players put on IR during camp, and the cost of their replacements. I may have forgotten one or two.

    If you must include these costs, I would suggest including the savings from a Mankins restructure. This saves $3.75M and is a no-brainer at any time. This would approximately pay for the above costs. For example, the net draft costs are negligible except for the net cost of our first pick.

    We have $24M - $25M available now, if we choose. Of course, we should make sure that we include the JAG's needed to fill out the roster (I usually allocate $2M - $3M for that. This year, this might include a corner (Cole equivalent or two), backup WR's, backup LB, and a backup DE.

    To me, this exercise is straightforward. We need to re-sign or replace

    Welker
    Vollmer
    Thomas
    Edelman
    Woodhead
    Talib
    Arrington
    Cole
    Chung

    We will go into the draft with no "holes" as such. Of course, posters have already decided what positions will be drafted. I'm sure Belichick (or his staff) laughs at such nonsense.

    If we (on balance) replace with equal players, our team will be a bit stronger next year due to the expectation of net improvement among the youngsters. I do NOT predict no or few injuries. I think it to be unreasonable to do so. I expect about the same as last year or a bit more.
  20. DocHoliday

    DocHoliday Rookie

    Joined:
    Dec 28, 2010
    Messages:
    2,755
    Likes Received:
    0
    Ratings:
    +0 / 0 / -0

    Care to share a rough number for the sum of the costs you wrote in your first paragraph? I'll just lump them into one Miscellaneous box.
  21. mgteich

    mgteich PatsFans.com Veteran PatsFans.com Supporter

    Joined:
    Sep 13, 2004
    Messages:
    19,975
    Likes Received:
    24
    Ratings:
    +28 / 1 / -0

    Given the low cost of our draftees this year, $4M should be fine. Miguel may have a better estimate. I have used as high as $6M in previous years. But, as I say, late restructures can relatively easily cover all these costs.

    In addition to mankins, we could always restructure Wilfork to send just a couple of million into next year, rather than a full restructure which is clearly a bad idea. Or we could extend Wilfork.

  22. Miguel

    Miguel Patriots Salary Cap Guru PatsFans.com Supporter

    Joined:
    Sep 13, 2004
    Messages:
    3,980
    Likes Received:
    6
    Ratings:
    +7 / 0 / -0

    NFL players are paid for their preseason work. However, preseason pay does not count against the cap.
    Last edited: Mar 2, 2013
  23. Miguel

    Miguel Patriots Salary Cap Guru PatsFans.com Supporter

    Joined:
    Sep 13, 2004
    Messages:
    3,980
    Likes Received:
    6
    Ratings:
    +7 / 0 / -0

    Accounted for in his workbook
    Also accounted for


    I would use one number for this. 3 million should suffice.
  24. DocHoliday

    DocHoliday Rookie

    Joined:
    Dec 28, 2010
    Messages:
    2,755
    Likes Received:
    0
    Ratings:
    +0 / 0 / -0

    Miguel, can you give an explanation of how the two player exempt from the cap works? Is it just the two lowest paid players on the 53? That's what I've read in searches across this forum, but it seems a little simplistic
  25. Miguel

    Miguel Patriots Salary Cap Guru PatsFans.com Supporter

    Joined:
    Sep 13, 2004
    Messages:
    3,980
    Likes Received:
    6
    Ratings:
    +7 / 0 / -0

    That is right. Right now until right before the season opener there is a Top 51 rule in effect. That is, a team's cap consists of
    its dead money,
    the cap hits of the players with the 51st highest cap numbers
    the cap hits of all other players minus their salaries.

    Example
    Patriots has 60 players.
    The salaries of the 9 players with the 9 lowest cap figures do not count against the cap but everything else that counts against the cap (prorated signing bonus, roster bonus, offseason workout bonus) does. These players typically have the rookie salary.

    This mechanism allows team to sign 80 plus players for the training camp and preseason.
  26. mgteich

    mgteich PatsFans.com Veteran PatsFans.com Supporter

    Joined:
    Sep 13, 2004
    Messages:
    19,975
    Likes Received:
    24
    Ratings:
    +28 / 1 / -0

    It is that simple.

    Until the season starts, the bottom 2 of the 53 are not counted against the cap.

  27. DocHoliday

    DocHoliday Rookie

    Joined:
    Dec 28, 2010
    Messages:
    2,755
    Likes Received:
    0
    Ratings:
    +0 / 0 / -0

    Thanks guys, I updated the workbook to reflect the 3 million 'fund' with a little note explaining not to worry if you get too close to the cap by that amount, since simple restructures can take care of it.

    I'll hold off on releasing it until some other players are released or new information comes in that needs updating. Don't think it's too pressing at the moment.
  28. mgteich

    mgteich PatsFans.com Veteran PatsFans.com Supporter

    Joined:
    Sep 13, 2004
    Messages:
    19,975
    Likes Received:
    24
    Ratings:
    +28 / 1 / -0

    As you say, we will have more info in the next week or so.
  29. mgteich

    mgteich PatsFans.com Veteran PatsFans.com Supporter

    Joined:
    Sep 13, 2004
    Messages:
    19,975
    Likes Received:
    24
    Ratings:
    +28 / 1 / -0

    I think that we need to remember that the cap number if a player is on the team is NOT equal to the cap savings of cutting him.'

    Cap savings can be complicated. For example, Lloyd's current cap is $4.5M. We could avoid his salary, and bonuses for a total of $5.0M.

    However, we would still have dead money of $2M (what's left of his original bonus) spread over this year and next. The total cap savings for this year is $3.5M
  30. NE_PATS_FAN_54

    NE_PATS_FAN_54 Rookie

    Joined:
    Mar 13, 2006
    Messages:
    948
    Likes Received:
    4
    Ratings:
    +4 / 0 / -0

    This thread is awesome, probably my personal favorite on the board right now. Granted, I'm a bit of a numbers nerd - but its nice to see everything laid out clearly here. Great to hear you guys go back and forth on the workings on the cap.

    One small note: Not sure if you have updated for this yet, but the cap was set at $123M (NFL sets 2013 salary cap at $123M, up from $120.6M - NFL.com)

    Also, based off of Bedard's sunday notes: your #'s are just about spot on, overall:

Thread Status:
Not open for further replies.

Share This Page