Writers of Pro Football Prospectus 2008

10 Aug 2012

Yearly Spreadsheet Tweeks

For the past couple of years, I've been modifying (fixing) the KUBIAK spreadsheets. If you notice, when you add up the auction values for every player worth being purchased, the total doesn't add up to the amount of money to be spent in the league. The following corrections fix those errors:

Step 1: On the 2012 KUBIAK projections tab, go into cell CZ2 and paste:

=IF(CO2>nrAuctionBaselineFPOB,(CO2-nrAuctionBaselineFPOB)^(1.05+0.05*nrAuctionAggressiveness),CO2-nrAuctionBaselineFPOB)

Then fill (click the little box in the lower right hand corner of the selected cell (which turns the mouse into a plus) and drag) all the way down to the end of the player list.

Step 2: On the Baselines tab cell C40 (Auction Adjustment), paste:

=((nrAuctionBudget*nrPlayers)-(nrAuctionMin*nrAuctionPopulation))/(SUM(OFFSET('2012 KUBIAK Projections'!CZ2,0,0,nrAuctionPopulation-nrAuctionBenchCount,1)))

Step 3: On the Baselines tab cell C41 (Current Auction Adjustment), paste:
=((nrAuctionBudget*nrPlayers)-(nrAuctionMin*C36)-nrSalarySpent)/(SUM(OFFSET('2012 KUBIAK Projections'!CZ2,0,0,nrAuctionPopulation-nrAuctionBenchCount,1))-C34)

That'll fix everything.

Chris

Posted by: jayhawkco on 10 Aug 2012

0 replies

Login or register to post comments