I have worked int the CTRM space for 20+ years and I have seen systems come and go. One thing that has remained constant is that Excel is still King.
There is good reason for that. It’ pretty good and it get’s the job done for most scenarios. In fact, the majority of companies that purchase CTRM software still export their data into Excel.
Here’s the thing. Excel is great as a CTRM system until it’s not great. Excel is extremely powerful but in the words of uncle Ben “with great power comes great responsibility”
If you are using excel to manage your commodity price risk you should also try to have the mentality that just because you can do it doesn’t mean you should do it. Below I will outline a few pitfalls to avoid and some steps you should take so that it doesn’t get out of control.
- Never NEVER use references in an excel spreadsheet to another Excel spreadsheet. If you do find that you have massive spreadsheets that require data from other massive spreadsheets it might be time to look into other technologies like databases to warehouse the data. Excel can pull data directly from a database which is something we explain in this article
- Never use Excel as a database. This is kind of a follow-up to the first point but excel is a great tool for analyzing data but if you find yourself using Excel for data entry and every day you need to add new data then you are on the Bus to spreadsheet hell. You might not be there yet but I assure you that you will reach your destination
- Having a very large spreadsheet. If your spreadsheet is several megabytes and takes a while to load then you really should rethink your approach. Not only is it painful to open and update spreadsheets that are very large but they are without a doubt riddled with errors.
- Having a Master spreadsheet. If you and your team refer to any of your spreadsheets as the master spreadsheet you probably already know that you are in a form of spreadsheet hell. Master spreadsheets are really bad because they get duplicated and that means they really can’t be master spreadsheets. You probably also have a spreadsheet Master who is the person that controls the master spreadsheet. You know who you are and where you are.
- Protecting sheets. This might get me some flack but if you need to protect your spreadsheet from people making changes to it then you’re probably doing something you shouldn’t be doing. Yes occasionally there are portions of a spreadsheet that might warrant some protecting like complex formulas but tread carefully.
- Overly complex formulas. If the formulas inside your spreadsheet are so complex that no one really understands them but the uber-genius who left the company 5 years ago then you should break up those formulas into smaller chunks.
- Too many tabs
Ok enough with the doom and gloom. How can I use spreadsheets in a good way? Here I will list out a few simple things that you can do to get your spreadsheets into a useable state. One thing I always tell my clients is that excel is a great BI and analytics tool so try to make most of your focus in using it to create a report rather than warehousing data.
- Use Tables in Excel. This is the easiest way to make your Excel sheets cleaner and your formulas easier to manage. But you said don’t use Excel as a database! Yes, I know but Excel does require data to work so you need to properly structure that data to make it useable and to do that all your data should be in Tables. A great part about tables is that automatically adjust their range so your formulas never need to be changed when you add or remove rows. I have explained excel tables for commodity risk management here
- Create a tab for your report that uses the data in the tables. It doesn’t really matter what information you are trying to extract. It is always a good practice to separate formula or derived data from the data that is populating the spreadsheet.
- Make it a template. If the spreadsheet itself is really a template that just needs some structured data for the formulas to get you results then you have a pretty good setup and your spreadsheet will serve its specific purpose.
- Be specific. Your spreadsheet should not be the everything spreadsheet but rather it should have a theme and it should address that theme. Don’t try to make the spreadsheet do more just create another spreadsheet.
- Use the Data tab. This is where you can make your spreadsheets more powerful. The Data tab in Excel allows you to pull data from other sources like databases and APIs. It then places that data into a Table in your Excel spreadsheet. The best part is that when data is update in the datasource you can click refresh and it will update your table.
