How a spreadsheet to help with a mobile football game, helps me to make business spreadsheets

I love making spreadsheets so much, that I often make ones for fun. However, these are not always just for fun, because they often present me with issues that I have not solved before, therefore forcing me to change my approach to solving spreadsheets. Many business spreadsheets are similar, as people are often hesitant to venture too far from what others use, but with games it is different.

I have often noticed how I spend time trying to figure out how to solve unique issues thrown up when trying to make a spreadsheet like this, or even a spreadsheet that is the game, only to then use that knowledge a few weeks or months later, as a unique business solution is needed. It happens often, so I see this time as not only fun for me, but also educational, and therefore useful for you.

In this case I was playing a mobile football game (Sidelines) and needed to assign training to each player. The problem was that it was tricky to work out what each player needed to train, in order to become the well rounded players that I (as the manager) desired. I also had to take into account what trainers I had, and what they specialised in. This was certainly a scenario that I had not come across in the business sector, so it gave me an opportunity to advance my skills, while providing myself with a solution to the problem I faced. I started off by defining some positions, and stating what percentage of the overall skill should be achieved with each skill for each position. That would then determine the baseline rate for each player, based on their position. This is their training position, as their actual position in the mobile game may vary.

Now that I had a baseline for each training position, I accessed the data for each player in the game, and transferred it to the spreadsheet. I also added the actual positions, as well as the training positions for each player, along with their overall rating. This meant that the spreadsheet was now able to check each skill for each player, and assign it a percentage of the total, and then compare each percentage with the baseline rate for the respective training position. This would then establish what skills were ahead of the curve and didn’t need to be trained, and also show which skills were behind, and therefore needed to be trained.

I then had to tell the spreadsheet what trainers I had, so they could assign the correct training and trainer to each player. The game does tell you what trainers you can hire (and have hired) as well as the skills that they can train and what level they can train up to. I pulled these values from the game, and entered them into my spreadsheet. I now had all the information required to decide what training each player needed, and which trainer I should use for each. Data entry is important, as data needs to be validated and checked. Once this is all set up, it provides clean data that can be used in calculations and formulas to achieve the objective. Below is an example of one of my trainers, and how I entered all 3 trainers into the spreadsheet.

I proceeded to use all that information to run two scenarios. The first one was the ‘manager’s choice’, so, based on the baseline example for each position, this will show the skill needed to be improved the most in each player. It wasn’t concerned with the trainers available, it just wanted each player to be well rounded based on the baseline percentages. The second scenario was what the trainers wanted to do. So, it looked at the skills needed by each player, and cross referenced them with the skills that the trainers were best suited to train. It also checked to see if the trainers could handle the skill level of the players. The spreadsheet then produced the table below, which shows all the players, and what skill the managers and trainers think is best. If the two skills matched, then it was a perfect scenario. It also confirmed if the player would in fact gain from the training, as their current skill level was lower than that which the trainer could successfully train. This showed me exactly what skill to get each player to train, which trainer to use, and it confirms that the training will be successful. All the green ticks and green shaded names are good. If anything failed, it would turn red, and it would flag up if the manager and trainer disagreed on which training was required. All of this was automated once I did the formulas, which is why spreadsheet templates are so useful. No messing about with pivot tables and complex formulas.

I could then go and select the ideal training for each player, as well as assign the correct trainer. I don’t do this each time I run training, but I periodically check to see if my training assignment is correct. At the end of the day, this is just a game. When it comes to spreadsheets for your business, you would decide how often it would be worth checking. This one is a bit of a faff because it’s on a mobile. If I could copy this data from my laptop, I’d run it more often. Here’s the training page of the game, where I assigned all the training to each player.

I wanted to take this one step further. If the manager’s choice and the trainer’s choice were different, what coaches would I need to hire to accomplish the manager’s choice of training? I used the data available to pull out all the training required from the manager’s point of view, and to assign a value to it so that I knew what skill level I needed to train people up to. I also wanted to leave some room for skill levels to increase, so I created a cell called “% Above Skill” where I could assign a percentage to increase the highest skill level by, so I could get a future-proof trainer. This table was produced from those formulas, so now when I hire new trainers, I look at this to see what skills and levels I need to cover so that I can get the right trainers for the skill levels required by the players.

This is where most people would end their spreadsheet. I had established what training to do, and had even gone one step further to see what trainers I needed for the ideal training, so what else could be done? Whenever I make a spreadsheet, the last thing I do is look at what could be done with the available data. Yes, it may not be an urgent requirement, but why not get the spreadsheet to do more with what it has, especially if it can be automated from existing data? In this case, I noticed that I could produce a detailed player report. I could have a drop-down list with all the players, and when one is selected, it could tell me everything about that player. Not only would it show me their skill levels compared to the baseline skills for their training position, but it would also show their training and trainer options for both scenarios, a skills comparison graph, their trainer choices, and how much each skill level was short or over the baseline. A handy tool to inspect each player as needed. All of this completely automated from data that was already being used elsewhere. A relatively small addition from a time point of view, but it is now a tool that I can use over and over without having to add extra data (that I didn’t need to add for something else) to populate it.

So, there you have it. A spreadsheet that I made just to help me to be more efficient in a mobile game. Although it’s more than that. It is an example of my approach to business spreadsheets. I get presented with a problem, and I get to work looking for a solution. I make sure the spreadsheet is as efficient as possible, even using existing data to produce bonus reports. If I go through this much effort for a mobile game, imagine what I could do for your business. What problems do you face? What could be more efficient? What do you need a custom spreadsheet to do for you? Get in touch and let’s chat about what you need.