035: A Leaders Guide to Spreadsheets

Transcript

WEBVTT

00:00:05.160 --> 00:00:13.169
spreadsheets can be an amazing tool to analyze data or create a report when planned out and organized properly.

00:00:16.260 --> 00:00:29.370
Our spreadsheet files should be simple and easy to use by all members of our team. leaders should set expectations on how analyst tools should be created and maintained by their teams.

00:00:29.760 --> 00:00:40.469
Let's create analyst tools that deliver what we need, but also easy to use, easy to understand, and easy to maintain. Please enjoy the episode.

00:00:40.859 --> 00:00:52.770
Welcome to the finance leader podcast where leadership is bigger than the numbers. I am your host Stephen McLain. And this is the podcast for developing leaders in finance and accounting.

00:00:53.039 --> 00:01:21.090
This is episode number 35. And today, I'll give you a leaders guide to spreadsheets. Now, this is not going to be a technical discussion, but a leadership discussion around ensuring we are creating analyst tools that are simple to use, and are logical. I will discuss four items around creating better spreadsheets. Number one, what is the purpose of the spreadsheet you will create?

00:01:17.280 --> 00:01:21.090
Number two, keep it simple.

00:01:21.540 --> 00:01:27.930
Number three, map out the spreadsheets logic. And number four, write out the instructions.

00:01:27.989 --> 00:01:33.000
Albert Einstein said, everything should be made as simple as possible.

00:01:33.239 --> 00:01:51.959
Today, we're going to talk about spreadsheets, we're not going to talk deep into the technical aspects of a spreadsheet. And I know you guys get excited out there. Because this is what you do every day you use spreadsheets. you analyze data, and you figure out how to solve a problem using Microsoft Excel.

00:01:51.989 --> 00:02:16.349
I know you got excited. So am I daring to say what spreadsheets should look like? Am I that daring. Getting in between a spreadsheet and an accountant or financial analyst is like getting in between a lion and it's fresh kill right? Looking at a new spreadsheet is like looking at a blank canvas like an artist does. a spreadsheet is art to the financial analysts.

00:02:16.650 --> 00:02:22.919
And it's endless possibilities to the account. That is how they usually looked upon.

00:02:22.979 --> 00:03:23.250
spreadsheets can be amazing when done right and can set you on the wrong path. When not done properly. leaders need to set expectations because the cost is too great if your spreadsheet files are done wrong. Our people often build spreadsheets to impress. Instead of being functional and easy to use. Why do I need to get involved as a leader? Because if the underlying logic is wrong, or no one else can use the same tool, then you are creating future problems and leaders get ahead of problems. Now I'm going to ask you a question. And what I want you to think about is the use of spreadsheets versus using your financial IT system or other apps that you may have, because spreadsheets can be error prone, and they can have flawed logic if not planned out properly. For routine reports, I want to see if your financial system can provide an automated report or download to you that comes into an email every week.

00:03:23.280 --> 00:04:20.699
We all have routine reports that we need to run usually weekly, sometimes monthly, sometimes quarterly talk with your IT team to see if you can set up something in the financial system that can be automated, and then just send you an email then you can cut and paste that data table into an email or other report. And it can be that simple. It's easier than trying to set up a spreadsheet after you've run a report and then try to work the formatting and try to adjust the calculations. Just automated if you can automate a reports can save you time and time is precious in this profession. I believe that simple is better. I don't believe in coding unless everyone knows how to code, everyone on the team should be able to use the same files just in case someone may not be there or someone may not be available to run the report or run the analysis that needs to be done.

00:04:16.980 --> 00:04:45.269
The spreadsheet belongs to the organization not to the one who created it. The logic of the spreadsheets should be highlighted and it should be shown. And I want you to have a variable section so that you don't place constants in the calculations have an area of the spreadsheet or even another tab where you have input cells so they're clearly marked, clearly defined. Now I want to tell you a story I was serving overseas in Norway as a budget officer.

00:04:45.509 --> 00:07:05.040
Now one of the other officers I served with was incredibly proficient in coding. He built a spreadsheet to track projects for the IT infrastructure improvements. He used Visual Basic, the tool he built was stable The art, it was impressive. But after he left that assignment, his replacement cannot use the tool nor update it, he couldn't perform any maintenance on it. Now a great tool was now worthless, we had to pay several $1,000 to a local IT company to break it and simplify it for us. So I'm asking you to simplify your spreadsheet files. If you have one person who codes then I would say don't code inside your spreadsheet files. If everyone is coding, you know, you're going to get replacements on your team. as people move on to other opportunities, then go ahead and use Visual Basic and code and ensure you know how to code as the leader, everyone on the team should be able to use the same analyst tools, what happens when a tool is passed down, as people leave or is shared amongst the team, or shared with another team, think about how the spreadsheet will be used, and how long this tool will be around. I have been asked to fix old spreadsheet files, I usually find hidden calculations that throw off the entire outcome. These are files that are passed down and occasionally modified. But no one really knows what is happening inside the logic, they are usually a mess, because as they have been passed down little modifications here and there had been done. But the overall logic of the spreadsheet will get flawed over time, I have seen spreadsheet files that have been used for 10 and 20 years. And with these modifications done by different people, different people touching it, they become a disaster. And then your output is not really what you intended, something is affecting it. And no one really knows sometimes these calculations are hidden in a tab somewhere in the spreadsheet. Now usually when I get a hold of these, I do a complete audit of the file, and I find it and then I do a map and I show the owner of the spreadsheet where you're having the trouble. And then I go ahead and try to simplify it, I try to get rid of all those old tabs, those hidden tabs and bring everything onto one or two tabs if possible. To make things a lot easier.

00:07:03.240 --> 00:07:17.360
Now I'm going to take you through four requirements for better spreadsheets and better analyst tools. I don't really want to get into a highly technical discussion. This is really a leadership discussion.

00:07:13.769 --> 00:07:43.226
I'm going to talk about how leaders can set expectations and can set standards across the team. So you do have better analyst tools. The first thing is what is the purpose of the spreadsheet that will be created? What are you trying to solve or answer? Is it going to be a routine report? Is that a projection? Is that some analysis for a project or a one time request? Are you going to conduct some analytical testing?

00:07:39.271 --> 00:07:46.939
Is there going to be a tool that is going to be used for maybe several years into the future?

00:07:47.000 --> 00:08:04.894
And you need to have a plan for that? If that's the case? What are you trying to accomplish? a one time use file will be treated differently than a file that is expected to have a significant lifespan, then I would ask is there another solution available? Like an app?

00:08:01.363 --> 00:08:33.378
Or is this something your financial system can produce or manage? Can you use the financial system instead of using Excel? Can you automate the report? Remember, figure out your purpose, and then you can figure out a better solution for what you're trying to accomplish. Number two, just like I said before, keep it simple, the more complex your files than it is harder to modify and edit and manage down the road. Especially if this file is going to be used by different people down the road.

00:08:33.438 --> 00:09:43.918
The people who use it down the road probably won't understand the logic that the person used, who created it complex files are more error prone than his a model that I tend to use, I try to use the same logic. As far as managing my tabs for all the spreadsheets that I make, I like to place instructions in the far left tab. And then I put an input cell tab next to that, you know, if there are a lot of input cells, then I tend to make it a different tab. But if they're just a couple of input cells, then I'll usually put it at the top of the Analysis tab that I'll be working on. And then you're going to have an analysis tab after any kind of input cell tab and then data tabs that you can use to drop in data that you've pulled from your financial system or your database. Remember to keep it simple. My spreadsheets usually follow this pattern and I label my tabs. Make sure you are labeling your tabs, keep it simple. Number three, I want you to map out your spreadsheet, I recommend drawing out the relationships between the tabs in your file and between the major calculations that create the outcome you're looking for.

00:09:40.268 --> 00:10:09.846
I use PowerPoint most of the time to map out each tab showing what information and also the data that is moving between the tabs. I show that relationship I show what input cell that is being used. I also show what calculation is being made and what output is moving From tab to tab, if you have multiple tabs, it helps the next person to understand the logic flow that's in the file, especially someone that will be using it down the road in the future.

00:10:09.908 --> 00:10:17.273
Now, how do the mathematical and logic relationships affect the outcome? How do they work?

00:10:13.802 --> 00:10:51.173
Overall, I want you to explain the input cells in your map and explain what is moving have a coding system on your map to show when information is moving, if you've got an input, or if you've got a calculation, and maybe even you're dropping in data, can I explain that in your map, and sure you explain hidden tabs, and why are they hidden? I don't really like hidden tabs unless they really have to be due the hidden tabs affect any calculations or the output in any significant manner, I have found too many hidden tabs that affect the final calculation.

00:10:47.582 --> 00:10:58.355
And the person using the file didn't even realize something was being manipulated or calculated in that hidden tab.

00:10:54.581 --> 00:12:26.730
Now finally, what I want you to do is write out the instructions, your file should be able to be used by everyone on the team, especially if this file if this tool is going to be used down the road, and should be able to be available by other people in case you are not available. In case you're on vacation, you're taking some personal time, or if you're unfortunately sick or unable to perform your duties. Update the instructions monthly and include pictures of what you're talking about. Explain the input tabs, and any variables also explain the methodology used in the spreadsheet? How does the spreadsheet work? And what assumptions Have you used, I want to make one more recommendation is that I don't like to use constants in a spreadsheet, I like to have everything input, because just in case you need to change that constant. So you just have to change one input tab, and then it flows automatically through the entire spreadsheet. Because if you're using a constant, then you're gonna have to go do a search, and then do a change throughout the file. If you're, for example, if you're going to be doing a projection, and you're going to be using 2%, we'll put that 2% and then in a cell and it's grabbed that cell instead of putting 2% throughout all the calculations just makes the flow a lot easier. And then you'll be able to do some What if analysis, you can change it to 3% or 1.5%. It's a lot easier, the flow is easy, and you're able to make changes very quickly.

00:12:26.730 --> 00:12:56.490
Now for an easy win. Today, I want to ask you a couple of questions. Can everyone on your team use the analyst tools in case someone is out for the day or the week? Are there written instructions available for each major analyst tool that you have? If you are in a leadership role? Have you set expectations for your team, there is much that we can do to ensure our tools are more effective, and more usable and simple to use for everyone on our team.

00:12:56.490 --> 00:13:00.000
I have a free guide for you.

00:12:56.490 --> 00:13:11.298
It's called the leadership growth blueprint for finance and accounting managers. In the guide I talked about three leadership areas communication, team growth, and empowerment.

00:13:07.722 --> 00:13:27.000
Plus a few recommendations around challenges with the systems you are probably using to complete your work. The link to the guide is in the episode description. Or you can go to Stephen McLain dot com. Please use it to help you with a few leadership wins today. Thank you.

00:13:27.000 --> 00:13:49.529
This episode is sponsored by my new online course offering to finance leader Academy is called Advanced your finance and accounting career developing a promotion strategy that will set you apart. Are you having difficulty getting recognition from your leadership? Despite all the hard work you pour into your job in your organization.

00:13:45.816 --> 00:14:04.075
This course helps you analyze what you bring to the organization, how you can set yourself apart from your peers through high visibility work and developing your leadership skills. Plus how you can devise a strategy to move ahead, you can go to Stephen mclain.com.

00:14:04.136 --> 00:14:05.190
For more details.

00:14:05.190 --> 00:14:17.240
Today I talked about how as leaders we can set a higher standard for the spreadsheet files that our team creates. And I talked about four areas.

00:14:13.269 --> 00:14:36.480
Number one, what is the purpose of the spreadsheet you will create? Number two, keep it simple. Number three, map out your spreadsheet. And number four, write out the instructions. This episode concludes season four of the podcasts the next two weeks, I'll be sharing bonus content.

00:14:32.371 --> 00:14:40.655
And season five will debut on January 5 in the next year.

00:14:36.548 --> 00:15:33.720
Season Five is going to be about self development. I'm going to talk about goal setting, how to be coachable. I'll talk about what is holding you back and also about perfection and why we should not try to be perfect. We will talk about your wellness and your morning routine and how to deal with conflict plus several More topics, we are going to get 2021. Right. Let's continue to improve and accomplish our goals. I hope you enjoyed the finance leader Podcast. I am dedicated to helping you grow your leadership. I hope you enjoyed the show. You can get this episode wherever you find the podcast. Until next time, you can check out more resources at Stephen McLain calm and sign up for my updates. So you don't miss an episode of the show. And now go lead your team and I'll see you next time. Thank you

035: A Leaders Guide to Spreadsheets

Listen On

Recent Episodes