We’ve collected these over the years, and wanted to share them as a list. Please add to the list in the comment section below if you’d like!
1. Garbage in, garbage out. We’ve all heard it many times before, but most expressions become hackneyed because they have lasting value. The question to ask yourself is: do my assumptions have current and reliable bases? Every assumption input in your model needs to have backup data and rationale for it. Don’t just use an assumption because that’s what was used in the last model. No two transactions are identical, so if the assumption you use is identical, it should only be a coincidence, not a matter of laziness or an oversight.
2. Annotate with comments (keyboard shortcut: Alt+I+M). Change is the only constant when you are looking at transactions. Keep a running record of the changes that you make in your assumptions and formulas. You won’t regret it.
3. Learn and use keyboard shortcuts. Like spending late nights at the office? Didn’t think so. Get these under your belt and use them so you can move around more quickly in your models and focus on the results coming from them.
4. Save multiple copies of all models. I think we can all agree at this point that electronic file storage is effectively free. Since it virtually doesn’t cost anything to save another copy of your model, go ahead and save new versions every hour (v.2.3, v.2.4, etc.). This way if the file gets corrupted or work is lost somehow (believe me, even with Autosave running it can happen), you never lose more than an hour’s worth of work. It’s not devastating to have to recreate an hour’s worth of work, but it can be a real problem to have to recreate half a day’s worth of work, especially if you are on deadline.
5. One formula per row. There are plenty of things to have to remember when you’re working on a model for a transaction. Now multiply that by 5 or 10 transactions. Do yourself a favor: use only a single formula for each row of your model — for instance, don’t have one formula for Time 0, and another formula for the subsequent periods. This way if you have to change the formula in that row, you don’t need to remember that the formula should differ between one period and another period; you can simply rewrite the formula for Time 0 and paste it all the way across the timeline.
6. Always sanity check your outputs. Our eyes, bodies and minds get fatigued sitting in front of a computer for hours at a time, and they get especially tired dealing with Excel. When you fee like you are done with your work, ask yourself out loud: do these numbers make sense? Generally speaking, if something looks too good to be true, it usually means that you have made a big mistake (did you pay back the loan?) or multiple smaller mistakes that compound one another. It can’t hurt to verbalize the story of your transaction in a narrative manner, plugging in the variables and returns metrics that you have computed. I can guarantee you that if something is off, it will become apparent the moment the words come off your lips.
7. Pay now or pay later. Like most everything in life, there are few responsibilities that you have that you can avoid altogether. You can certainly defer things, but the question is: will you in fact have the time and lack of pressure to do those things at a later time that you have right now? There’s no way to know, so in most cases it’s better to just get it done now.