Set up: To ensure our discussions have a proper basis and requisite depth, I’ve put together a basic spreadsheet with financials of Acme Co. Please download the spreadsheet here – I will use a shared Dropbox folder for this series – if you prefer to have access to the whole folder instead of downloading a spreadsheet every time I update it, do send me an email and I’ll share.
I’ll now point out the basic bits of set up that I do every time I get hold of a set of financials for analysis with the “why.” While this is a very very simple version of financials, this will do for the purposes of our discussion. Also, there is no right and wrong here – I’m putting together a list of things that work for me and I look forward to hearing from you on what works for you in the comments.
7 set up habits I have found useful.
1. Set up a control sheet. As you will have noticed, we have a very basic control sheet set up but the importance of this sheet will become apparent as the analysis gets bigger/more complicated. For now, we use it to simply set “named ranges” for currency conversions.
The benefit of the control sheet will become obvious when you have to change conversion rates mid way through the analysis to reflect a new market rate/internal finance rate. If you go to Formulas -> Name Manager, you will see the named range set up.
2. Clean up the base sheet. The base analysis sheet is one you will lose a LOT. So, clean it up by making sure the columns and basic numbers make sense. Depending on the size of the data you receive, this clean up can take many hours and can even extend to a few days. This involves cross checking with the finance team to make sure the totals make sense.
Make sure you do basic checks e.g. are there any rows with negative sales? If so, why? This can be done by running a simple sort or filter.
3. Differentiate important columns with colors. I prefer using a background color for key pieces of data (numbers in this case). I also use a bright yellow highlighter for columns I have added and orange for existing columns.
4. Remove any formulae you added after the number checks. In this case, I’ve left some of the formulae in the rows for discussion e.g. Net Sales = Sales after Discount – Rebate. As a general rule, it’s best if you replace the numbers by copying the entire column, pasting with values once you know the numbers make sense.
5. REALLY understand the way the numbers work. Every company/business unit uses different terms and methods of calculation. Make sure you really understand the assumptions behind the numbers. As a rule, NEVER make any assumptions about the workings of financial numbers. Do a basic clean up and then sit with a finance person who understands them to make sure you have the same basis of understanding.
6. Remove outliers for the purpose of your analysis and document methodology. Most excel sheets are primarily used to make useful charts for management. You will have a few outliers in the data every once in a while
I’ve used very simple discounts, rebates, and cost calculations here but this will not be the case in real financial sheets. There will always be outliers that need to be cleaned out. Make sure you document these in the “Control” sheet as the underlying assumptions behind your analysis.
7. Keep the spreadsheet clean and avoid version control. A spreadsheet is like your work desk – the more organized and orderly it is, the easier it is to work with. As you add tabs for analysis, continue to keep them clean. I typically remove gridlines (View->uncheck gridlines) and add in borders around my data to make sure it’s easy to focus. This is a personal preference.
Also, name your spreadsheet well so you avoid version control. Notice the naming convention – “130703 AcmeCo Financials – ALAD Rockstar Analyst Series v1r.” Having the date (2013, 07, 03), the version (1), and the initials of the person who made the last edits (r) ensures you have no trouble identifying versions.
A Learning a Day 