Of Wages and Scheduling

When your taking a snapshot of the teams attributes every 3-4 months, and then the year to year comparisons, the number of tabs starts to add up.  I think I am up to about 25, and while that’s still not anywhere close to the “Cry for help!” stage, it’s starting to be a concern…

Many of the pages continue to evolve, mostly due to suggestions from others.  One very good suggestion comes from Ponzie on the Football Manager Slack channel, who asked if it wouldn’t be possible to get a table layout of a player progression, and then see how their attributes have improved over time.  And with a little help from Google and Stack Exchange, it turns out there is.

This is essentially the player attributes copied multiple times, with a column for the Tab being referenced.  Now, I will be the first to admit I have no idea what exactly the formats used in the conditional formatting mean exactly, but here is what they are:

INDIRECT(ADDRESS(ROW(), COLUMN ()))>INDIRECT(ADDRESS(ROW()-1, COLUMN()))

This first formula gives a Green colored cell background if the condition is met.

INDIRECT(ADDRESS(ROW(), COLUMN ()))<INDIRECT(ADDRESS(ROW()-1, COLUMN()))

This formula gives a Red colored cell background if the condition is met.

Now, the VLOOKUPS used to populate the attributes are also there, what these two new formulas do is compare the numbers in each column to the number above it.  If the number being compared is greater than the number above, it will turn Green.  If it’s lower than the number above, it will turn red.  No change, and the cell doesn’t change color.

I created a player named “Test Player” on a couple of the previously built roster tabs.  Here is what the results should look like when you get things typed in correctly:

You’ll notice the number of color changes as the attributes improve or get worse.  How does this look on a ‘real’ player?  Well, lets take a look at one of the better youth players I have on my squad, Jean-Marc Parent.  He’s been at Bastia since the beginning, and has turned into one of our best Midfielders.

Now, the row in Green across the top wit the white numbers is the total of ho much each category has improved.  Some, like Strength, have improved quite a bit.  Others, like heading, not so much.  I use this to help me decide on training.  In three years, his heading has improved by 1.  That’s not so great, and leads me to believe that yes, while his Heading may improve over time, chances are slim it actually will, and the time spent practicing heading is probably better spent on First Touch, or Dribbling.

The nice thing about this section of the spreadsheet is that once the initial array in the formula is completed, you can insert as many lines as you would like, and the array will adjust to the new lines.  I currently just have lines for most of the tabs I’ve already created, but as I continue on with the save, I can easily add more.

Now, these next couple of tabs I use could possibly be done a quicker, better way.  I am open to suggestions on that, but for the moment, they take up the majority of the ‘behind the scenes’ work to get done.

First up:  Wages.

Now, the issue with this is the export from Football Manager.  The format it uses, neither Excel or Calc really like, so you have to do some editing to make this:

Look like this:

I don’t want to get into all the nitty gritty behind the scenes stuff you have to do, but I will say that after the first couple of time you do the copy/paste, editing, and formatting, it goes by a lot quicker.

You’ll notice I’ve distilled the exported view down into the necessary columns for this, Player Name, their Wage, Contract Type, Contract End Date, and Value.

Next to that is another couple of columns, and using the formula:

=COUNTIF($D$4:$D$67, “=???”) where ??? equals Contract type in Column J, it counts the number of Key Player, First Team, and so on contracts I have on the squad at the time this view was exported.  Column H totals up each players salary depending on contract type, with the formula:

=SUMIF($D$4:$D$99,”=???”,$C$4:$C$99) again where ??? equals Contract type in Column J

I also have a couple of checks on the total number of players, the first ’61’ counts the number of players in column B, the second 61 sums the total of column J, and for fun I also add in the total value of my players at the moment.  Yes, at the moment this total is very, very sad.

This tab then in turn feeds data to the Wage Distribution tab, which look like this:

The key number to look for here, and you know it’s key because it’s buried about halfway down, is the Wage Budget.  This is the Budget the board set for me last season, €39,005.  Now, the following is as best, a guesstimate with a dash of hunch and a smattering of “What could possibly go wrong here?”  My assumption is that this year, my players salaries will account for 75% of my wage budget, meaning 25% of the wage budget is going to the coaches, scouts, physios and whatnot.  I would think as you move up the leagues structure of whatever country you are in, and you get more Non Players as part of the club, this number could change some.  I know when I started out in the National 3, I set it at 10%, because other than myself, a Chief Scout, and a Physio, we really didn’t have much else.

As you can see though, assuming 75% of our wage budget goes towards players salaries, we have a Wage Cap of €29,254.

The top part of this references data from the Players tab, with the Contract Type and number of contracts populated.  You’ll notice over in column P and Q what I think a 22 man roster “should” look like, Column D is what I had at the time.  I do not count Youth Contracts or hot Prospects as part of this.  the ‘% of Wage’ Column is again, a guess.  And then the number in Column F is what my max wage ‘should’ be, in this case three players on Key Contracts should be earning 20% of our entire wage budget, or €5,851.

I have 9 players earning €13,200….but I make up for that deficit by not having any Rotation contracts, and when all is said and done, you can see I am ‘ONLY’ €46 over my Wage Cap.

I find this useful especially when it comes to squad dynamics and playing time issues.  If I have two (or more…) players on Key Contracts in the same position, you know the one that gets the least amount of playing time is going to complain about it sooner or later.  Or, come the January transfer window, move some of the Key Players who aren’t playing so much out, and bring their replacements in on Squad Rotation contracts.

Last up for this post, the schedule.  And I will admit, this takes a decent amount of behind the scenes work to get ready, and even then I still can’t make it do what I want it to do…yet.

First, here’s what it looks like in all it’s glory:

This was Bastia’s schedule in the 2021-2022 season.  The video goes into a little more detail, and shows some of the changes when you switch a value, but for the moment, we’re going to start at Column N.

This is the teams in Ligue 2 at the beginning of the season.

Column O shows how they came to be in Ligue 2, and if applicable, where they finished the previous season.

Column P is the pundits prediction on where the teams would finish.

Column O is where they actually finished.

Columns R and S are the points Bastia earned Home and away at each opponent.

Column T is opponent strength, from 0 to 3.  The teams in red get 3’s, blue gets a 2, Yellows 1, Greens 0.  This represents how hard/easy I think it will be to beat them.

Starting in column A is the schedule, which I exported from FM with just the Ligue 2 fixtures on it.

Column J is the Strength of Schedule, change that number, and I have a conditional format that changes the background of the team cell.

Column D is the game result, type in W, L or D and Column E Populates.

Column F is your running points total, and if more than the number in Column G, your running points total will be GREEN, if less it will be RED, like so:

How did I arrive at Column G, Points needed?  I went back and looked at the last 10 years of teams in Ligue 2, and found that you needed, on average, 43 points to avoid relegation.  Add 1 to that you get 44 points.  Divide 44 by 38 games played, you get 1.1578947…well, 1.16.

So, every game I played I needed 1.16 points to avoid relegation.

Column H counts the number of days between matches, but this isn;t the most useful at the moment, because this is just the basic Ligue 2 schedule.  It does not include any Coupe de la Ligue, Coupe de France, or European Fixtures, because adding them in at the moment breaks some of the conditional formatting, and running points total.  It’s something I am working on, but I will admit, I’m not working to hard on solving it at the moment.

Column I is Match Week number (Handy for when your sorting, and yes I learned this the hard way).  Column J is the teams Strength of Schedule number, and the I break the league down into quarters, two of 9 games and two of 10 games.

What these tell me, and what the teams colors tell me, is what sort of battles I have coming up in the future, and how can I prepare for them better.  You’ll see in the 1st Quarter of games I played five pretty good teams, but I want to highlight weeks 8, 9 and 10, when I played Troyes, Reims and Rodez Aveyron.

Troy and Reims are both quality teams, and I am playing them 4 days apart.  4 days after playing Reims, I am playing Rodez Aveyron, who are not that strong.  Should I start my ‘A’ side against both Troy and Reims, and knowing they’ll be shattered from a condition point of view trust the Rodez game to my backups?  What if this wasn’t the first quarter of the season, but the last, and I’m in a relegation battle?  Whats sequence of games can I rest my started a little more, and play a more rotated side?

Now, the one downfall to this time at the moment I alluded to earlier:  It doesn’t include non domestic league fixtures.  IF/when I do get that figured out, it’s another tool to use in determine what the future looks like, and how to plan accordingly.

I can look at this all day long:

But for me, there’s not a lot of useful information there beyond the basics.  And being a pretty visual person, doing what I do to the Schedule helps me plan more.

The video accompanying this post can be found at:  https://youtu.be/K-cUmrMTxzk

It shows some of the changes that happen when you change some of the values, and does go into a little more detail on the schedule side of things.

As always, if you have any questions or comments, leave them down below, or I can also be found at @FM_Jellico on Twitter.  I also frequent the Football Manager Slack channels, and can also be found on the AbsoluteFM Discord server (amongst other Football Manager related Discords 🙂 )

And thats Part 2 of this series.  Next up, in a couple of weeks, my Lineup Tab, also known as “How much Data can you have on one tab Jellico?  JFC….”

Thanks for reading!