Prices & Trades Spreadsheet for R5

OK, spreadsheet out early this week.  Why?  Because I have vowed to be a lot more organised after missing lockout last round – I didn’t make any changes from the week before!

Friday was a travel day for me.  In retrospect the decision to drive via the Yarra Valley and visit a few wineries wasn’t the choice of a responsible DT coach.  A 3 hour trip became a 6 hour trip, and no coaching was done.  Worse still this is the 2nd time this year.  So my team is doing sh*t and I have missed the boat on many players.  At this rate I will be lucky to get into top 10k.

But good news is that I’ve bought a new Chromebook to travel with; so now will be online all the time.  Maybe my team can produce a Melbourne like last quarter and save their coach’s job.

Anyway for new readers this is a spreadsheet to help work out who to trade into, or out of, your team to get the best money changes for the next few rounds.

Changes this week:

• Actual scores up to R4 and current prices automatically pulled up for every player.

• Improvements to Trader section.  Put in a pair of players you want to trade to see the outcome. You can compare 5 different trades at the same time.

Here is link to download spreadsheet (excel2007) – DT2013 R5pub

If you used the spreadsheet from previous weeks, you can just select your team block of players’ names from there and copy + paste your team into this spreadsheet.

I am interested in how you are going with this.  Are there any features you would like added for future versions?

If you have any questions, post comments below, or tweet to @NixTrader

 

————————– notes below are mostly the same each week ——————————————-

Brief instructions:

• You only have to enter information into the green cells, the other cells are protected to prevent any errors.

• Confirm or change Magic Number estimate for the end of next two rounds.  I have put in values for MN, but if you think you know enough about MN to prefer other numbers you can change them.

• Enter your players using the drop down menu.  In the menu only the players who fit that position are named in full, other players are reduced to 3 letters.  If you used the spreadsheet from previous rounds you should be able to select and copy groups of players from there into this spreadsheet.

• For each player in your team data is pulled up automatically –

• Team and position for 2013.  If this is different to 2012, their previous team and position is also shown, or if this is their 1st year playing.

• 2013 starting price.

• Points expected based on that price (using initial magic number 5,150).  The difference in points between their 2013 base and their points average for all of 2012.  For most players who played last year this difference will be zero.

• 2012 Performance in 3 columns – average points, games played, standard deviation.

• Scores from the completed rounds in 2013 – “n” indicates they did not play.

• A projection for that player headed “Points Proj.”.  This is used as default in the calculations.  If a player has played more than 3 games this is their current average; if less than 3 games the projection is calculated from their starting price.

• There are 2 green cells to enter the actual scores (or your estimates) for your players for the next 2 rounds.  Based on these scores the last other columns calculate the averages, BEs, price change and new prices after the rounds.

• If you leave any cell blank, the calculation assumes the player gets their 2013 base score for that round – i.e. the score in column headed “Points Proj.” is used as default in the calculations.

• The player has to play 3 games for their price to change, so if the players miss games their price change won’t happen until after they play 3 games.

• If one of your players misses a round, put an “n” in the green cell for that round.  The calculations for the next round will then update to take account of this.

• The captain’s score isn’t doubled, so the total score won’t match your real DT score for the rounds.

 

At the bottom of the spreadsheet there are 3 more tables.

• A table looking at the structure of your team,

• A table looking at distribution of byes in your team.

• A table that compares different trading options.
  

 

FAQ:

• Why are these calculations different to other published BEs and valuations?
  • Everyone’s calculations depend on their assumptions about Magic Number, projections of future scores and how they round averages and values.  This spreadsheet should replicate any other published calculations if you make the same projections, including changing the magic number.  However, this spreadsheet is unique in that it allows you to change the estimates to do your own analysis.

• Tech talk: You need a device that supports microsoft excel 2007 spreadsheets to be able to open and use the spreadsheet.  It won’t open on mobiles or tablets unless you have software or apps that support excel files.