If you are a banker, the public expects, rightly or wrongly, for your spreadsheets to be as tight as your bank vault. The spreadsheet is our stock and trade, and just like no one likes having an unhealthy doctor, no one wants their banker to have a spreadsheet with bad colors, random decimal places and no version control. Over the past several years, we have seen a steady deterioration in spreadsheet quality, and it’s time to turn things around. If you are new to banking, consider this the minimum level of performance. If you are a senior banker, this article can serve as your template to teach the next generation the basics. At this risk of squashing the creativity of Janus in accounting, in this article, we highlight nine items that, in our opinion, should be considered the minimum standard for bank spreadsheet performance.
While we will talk about substance in future articles, the information below is all about style. While your formulas are important, bankers are usually good on the calculations but struggle on adhering to a fashionable spreadsheet dress code. Use the pro tips below and get your spreadsheets looking like the most actionable set of numbers in the whole bank.
Lead with Your Head(er): Like brushing your hair, every worksheet should start the day with a strong title structure to be both presentable and functional. Format how you would like but be sure to include the title of the analysis, a subtitle that further describes the analysis, when the spreadsheet was run, how old the data in the spreadsheet is, a version number and if the spreadsheet has been validated. Model risk is real so every spreadsheet should have some logical way of versioning detailing the owner of the analysis and the number of times the formulas and material formatting has been changed.
Have a Point: Creating a spreadsheet is no different than writing a report in banking. Every analysis must have a point, a beginning, middle, and end. Figure out what the point of your analysis is and what the most important set of numbers are. If you are working on a department budget, is just the annual figure the most important or the monthly totals? Once you have the end goal in mind, structure your spreadsheet backwards so you can layout your data and analysis to support the outcome. When you are all done, use the five-foot rule and step back five feet from your computer screen and try to figure out if you can see where the most important numbers are located at. A good spreadsheet, like a good report, will lead you right to the salient points.
Name your tabs and organize your workbook: You have created a bunch of worksheets in your workbook, and no one likes a “’Sheet63’!F16:F89” reference. Far better to right click and rename your sheets to tell the user what they are doing and where your references are going. Once all named, get your sheets in some semblance of order.
Go Easy On Fonts and Colors: A spreadsheet is no place to show your individuality. Save for a few examples such as conditional formatting and complex models, try to limit yourself to one color and one shade for the body of the spreadsheet and one color for highlighting important data. For fonts, limit yourself to no more than two, and they can be any fonts you want as long as they are Arial or Calibri (a skinner version of Arial). If you need to write text, Times New Roman is acceptable for clarity. While creative typography has no place in a spreadsheet, hardcore analysis should know that Segoe UI is the font to use if you absolutely have to cram huge amounts of data into a small cell since that font as near the readability of Calibri, but with 20% less of the pixels.
Take Time To Format: Center your titles in your columns, move your numbers either to the far left or center, put sum lines under your columns, accounting underscore your totals, use shading where needed, adjust the width/height of your rows/columns and label all your cells. Be sure to distinguish your column headings with at least bold or colors. Consider horizontal shading of every other line if it is a table and consider putting vertical lines or “zebra stripes” in the appropriate place to set of columns that total across.
If it is a large spreadsheet, consider freezing your titles for easy scrolling. To take your game to the next level, make your sheets print-ready just in case someone want to go analog.
The Standalone Spreadsheet: A good spreadsheet shouldn’t take someone standing over your shoulder teaching you how to use it or answer questions about what you did and where your numbers came from. Assumptions should be clearly highlighted either by putting all assumptions at the top of the sheet, in a separate worksheet or highlighted throughout the sheet in shaded fashion. Input and output sections should be clear. Commentary in separate cells, text boxes and a separate worksheet of instructions should be standard for most spreadsheets. Analysts shouldn’t over do it here, but far too often we are rushed creating the analysis rendering the spreadsheet unusable for others or if you have to come back to it a year later – good notes make good spreadsheets.
Use The Right Amount of Dollar Signs: This is a pet peeve of ours – For columns of numbers, dollar signs go on the first row, and anytime you sum a row. Yes, this sounds retentive but ask any classically trained accountant, and they will tell you that is how they were taught. Forget to put dollar signs in or format your whole column with dollar signs, and you will look like someone that got their Excel education over the internet.
Dotting Your Numbers: Make sure you are using the right number of decimal places and that the whole section is the same. As a rule of thumb, don’t use any decimal places for projections or big picture items, use two decimal places for dollars when working loan or account level detail and go to five decimal places when reconciling to the general ledger or core. For any non-dollar analysis such as percentages, either zero or one decimal place is sufficient.
Be Consistent: Before you turn your analysis over to your boss or co-worker, take a look and make sure your formatting is consistent and contains the right level of information.
Include some graphs: If you have done all the above, if it fits your purpose, now you are ready to take your spreadsheets to the next level. Add in some graphics and graphs to make the data useful to your users. We like to create a “dashboard” or Key Performance Indicator (KPI) worksheet, but you can just as easily include it within a small worksheet like the example below. Keep the graphs simple and just calling attention to your main point.
Follow these Top 10 guidelines, and you will give your staff, co-workers, and customers confidence that you know what you are doing with their hard earned capital. Excel is one of the most powerful tools a bank has at its disposal, and the average bank analysts just use 10% of its capabilities. In future issues, we will be not only highlighting tips and tricks on how to leverage Excel more but will be highlighting how to combine Excel with various cloud data streams, the Microsoft’s Power BI application and various artificial intelligence engines so banks can start to better leverage their data and turn analytics into actionable intelligence.
Submitted by Chris Nichols on July 24, 2017