Team Management Through Spreadsheets (2) – The 4 WHOs

So many Football Manager spreadsheets, but what if there was one to rule them all? Here is mine (you can even download it). It is not the only one I use, but in this article, I will explain how it works and why it can rule them all.

Once I just had the spreadsheet discussed in a previous post, but now I mainly use it for finding the best roles and re-training young players as I explained there. This is because it has evolved into the spreadsheets I will show you here (5 – one each for central defenders, wing backs, midfielders, attacking midfielder and attackers). I use these new spreadsheets because they are more user-friendly and focused. You can also find links to download these spreadsheets at the end of this post so you don´t have to set them up and format them from scratch.

Attributes

The first sheet (or tab) which you will see will actually be the last to be discussed here as it is an overview which sums up all the information found in the other sheets. So let´s start with the second one: “attributes”, part of which you can see in the image below.

Central Defenders attributes sheet – explanation below

First column you can see is the in-game date. This is useful because it enables you to compare attributes or stats from different seasons. It is followed by the name of the player in question and a list of attributes. There is a list of 10 attributes I consider important for central defenders. These attributes are used to create the “rating” in column N, with the first 5 attributes given more weighting than the others as I consider them as more important. (Keep this in mind if you would like to change the attributes.) The “age” column with its colour formatting is placed between the attributes and the rating to make it easier to see at a glance how good that rating is relative to the age of the player.

Conditional formatting is used to colour-code attributes, age and rating.  In the case of attributes, the colours let me see instantly what stands out. I find this very helpful because:

  1. it highlights certain deficiencies (or exceptional skills) in case of players I am scouting or deciding whether to sell or not, and
  2. because it helps me see a need (if there is one) to adjust tactics according to certain standout attributes. So, for example, in the figure above there is a lot of brown in the “acceleration” column, so unless the first 2 are playing I know that I might need to be more wary of space left behind my defenders.

True, the attributes can be seen in the player profile screen in the game itself, especially if you customise the skin colours, but now have a look at the colours in the “Age” column.  The colour-coding in the age column correlates to the attributes in that the colour would show me what is acceptable at which age.  For example, some attribute values are highlighted in brown, as is “17” in the age column.  This means that values highlighted in brown are acceptable for players aged 18 or under.  So, you can instantly see that Simic (who is 17) has orange and green values meaning that in these areas he is above my expectations for his age.  On the other hand, Katic (19) above him has brown values (so he is below expectations there) and some green ones (above expectations).  32-year-old Paletta has light green all over meaning that he is decent there, but has unacceptable acceleration (brown – 11) and very good bravery (brighter green – 17). Same thing goes for the rating column on the far right. At a glance you can see that overall Paletta is decent, Katic is acceptable for his age and Simic is better than average for his age. What do you think the rating and age in the image below say about Curto?

At a glance – is he good enough for his age?

I find the above useful to help me see:

  1. different strengths and weakness of the different players,
  2. who are the promising, good, or to-offload players,
  3. strengths and weaknesses across the board,
  4. what I need to focus on if I am looking for a new player in that area.

This data is exported to another sheet in the same file to be used in conjunction with data from other sheets. Below, I will show you the relevant sheet to help you understand. I input data from my players as well as those who make the final shortlist when I want to buy certain players, trying to get the very best deals and squad players.

Minimum expectations per age.

As I have noticed that not all attributes increase at the same rate, I looked for and noticed some patterns in the development of different players to come up with this table above.  This is a key for the colour-coding and you can see that while I would expect a minimum of 9 at concentration from an 18-year-old (and a minimum of 15 at 23 years of age as I consider it as a primary attribute), I would expect 10 for jumping and 9 for bravery at the same age even though they are secondary attributes (so I expect a minimum of 13 at 23 years of age). This is obviously not a hard and fast rule as all players develop differently, but I found that it gives me a good general idea which very rarely fails within these parameters.

Central Defenders primary attributes

Next in line is the primary attributes sheet. This works in the same way as the attributes sheet. I decided to do another one here because sometimes – particularly at lower levels – 1 player would get a good rating overall but would not be so good in the primary attributes. This helps to avoid such mistakes which led to a couple of flop signings when I started taking into consideration too many attributes, sometimes as many as 13 including the CoDe. This is not to say that I don´t consider all the important (IMO) attributes when choosing who to sign, but I don´t include all of them in the filters as it could lead to me having a misguided shortlist. This does not entail extra work as the attributes here are automatically imported from the attributes sheet you would have filled in before.

Stats

Central Defenders Stats sheet – explained below.

This sheet focuses on stats.  It serves 4 main purposes

  1. It lets me compare the performance of different players in the same position to see how they are performing,
  2. it lets me compare the performance of my players to that of others playing in different teams – which proves useful particularly when deciding who comes and who goes,
  3. it lets me compare player performances throughout the years,
  4. it helps me see general positional traits. So, for example, the first time I used this I noticed that my fullbacks (or wingbacks) are making the most tackles in my team. When I looked at it I was OK with the reason why and left it at that, but I did start giving more importance to the tackling ability of players in that position. 

Also, I have noticed that the best attributes and personalities do not necessarily correlate to the best contributions, so the stats are at least as important as other factors including attributes when I am deciding who plays, who stays, who comes and who goes.

The row below the column titles is the average of the values in that column. The highlighted cells show who is a certain percentage below or above average for certain key stats where it makes sense. So for example, I can instantly see that my players, on average, intercept the ball more than other defenders I was scouting (because all my players you can see in the image are above average) and while Otamendi´s rating is above average for the role, Ze Marcos´ and Krychowiak´s are below average.

Team CoDe

Central Defenders – Team CoDe

What follows is my team CoDe (COmmon DEnominator) screen.  This is a concept which I have also seen used by several other FM players and which I explained in my previous spreadsheets post. I have a set of attributes (again, the first 5 are expected to be of a higher level than the second in general) which I see as important for all players in my team.  This works like the “attributes” sheet discussed above so I will not be repeating myself here.

Team Overview

My Team Overview

In this sheet I collate all the data for an overall view. Most of the data (and conditional formatting) here is automatically filled-in from the other sheets by Excel.  First up are the preferred side and foot to make sure that I don´t end up without any left- or right-footed defenders. Then there is the stats column which I fill based on what I see in the stats sheet.  It is followed by “attributes” (the rating from the “attributes” sheet after taking into consideration the 10 attributes I chose), “primary attributes”, and the “communal” (or CoDe) attributes as explained above.  Coach CA and PA are the star ratings given by the coach (for my players) or by the scout (for shortlisted players).

The “outsanding” and “important deficiences” are based on attribute values which I set according to my team, expectations and the player´s age.  They help me by:

  1. highlighting outstanding skills the players may have so that I could use them in the best way possible,
  2. drawing my attention to possible deal-breakers, or
  3. scope for individual focus in training in the case of the deficiencies.

These are followed by the pros and cons (again from the coach/scout report) and information about their current contract.  In the last 3 columns I outline what my plans for that particular player are.

Other Positions

As you may have noticed, the spreadsheet file I was using above is the one for my central defenders.  I have a similar one for the wing-backs, the central midfielders, attacking midfielder and the strikers as well.  These are more or less the same (except for attributes and stats being different according to the roles obviously), with slight variations in the spreadsheet file for the central midfielders and attackers which I will explain below.

Strikers
Strikers attributes sheet

I will start by the strikers´file. You may notice that there are more attributes here. This is because I play with 2 strikers (mainly alternating between 3 roles) so I have all the attributes which I see as important for at least one of the roles. Then, after the age column you can see “af” (Advanced forward), “dlf” and “cf” which take into consideration different attributes for the rating of each of the 3 roles. Next to them you can see which are the attributes I consider as important for each of the roles. (In the screenshot above you can only see the first 3.) These are followed by ratings for the primary (in my opinion) attributes of the advanced forward and the deep-lying forward. (see image below)

Central midfielders

The central midfielders file is different in that:

  1. there are 2 stats sheets (1 for DMs and the other for CMs)
  2. there are even more attributes because there are more different roles (this works likes the strikers´ sheet above)
  3. there is an extra sheet called “mc communal”. This is to focus on certain attributes which I consider as important for all my midfielders.

Main points and conclusion

To summarise – in view of the length of the post and the amount of information in it – I can say that the main areas of interest here are two: attributes and stats. The attributes (including rating and age) help to highlight standout areas for action in the transfer market, tactics or training grounds. The stats highlight the standout individual performers as well as patterns per position. These are then summarised and used in conjunction with other information to advise on the 4 WHOs – who plays, who stays, who comes and who goes.

I am aware that there might be a lot to take in here and that you might need to modify this spreadsheet according to your needs and style of play (this is in fact what I have done with my own spreadsheets over the years to finally come up with this one). So, feel free to leave your comment here or find my Twitter profile (amazing FM community there as well as on Slack) with any questions, thoughts or feedback. Interaction with you, fellow Football Managers, is a big part of what makes all this worth it.

Before you go, here are the download links for the different spreadsheets mentioned above:

5 thoughts on “Team Management Through Spreadsheets (2) – The 4 WHOs”

  1. These are my favourite spreadsheet posts — not only because of the thinking but also because of the writing, which is easy to read yet precise.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.