Microsoft Excel is one of the most common spreadsheet applications for performing basic financial calculations or building complex financial models. Regardless of the complexity of your analysis, it is important to create financial models that are accurate, reliable, and easily understood. Readability is as important as accuracy. Here are five tips that can help get you started:
Stick to a color scheme
Each company has its own rules regarding colors and fonts, such as keeping hard-coded numbers in black, calculations in blue, etc. Know and follow your company’s guidelines. If your company doesn’t have specific rules, make sure to maintain a consistent color scheme throughout the workbook to help you and your reader follow input versus output cells.
Keep formulas simple
Don’t go overboard with unnecessarily complex formulas. In fact, formulas should be as simple and straightforward as possible. For example, an “indirect” formula looks impressive when used to create a lookup across multiple sheets, but if all you need is a more straightforward “vlookup,” go with the simpler route. Simpler or shorter formulas allow reviewers and teammates to recreate or trace back formulas when needed. When it makes sense past values particularly on information cells and/or descriptions; this will reduce needless errors in the file that are not valuable for purposes of the file to reference the link.”
Build in flexibility
Flexibility to change assumptions and input data can be a big time saver and result in a spreadsheet that can be multi-purposed. For example, can your formula be copied across columns and work against a timeline of multiple months and years? Some rules of thumb include keeping an assumptions sheet or separate section and building in cell references for time period headers. Take some time to predict future scenarios for upcoming time periods. Can you reuse current formulas if the data needs to be updated? Try your best to make your spreadsheet flexible.
Make your spreadsheet attractive
Even if your results are correct, they will be hard to read if hidden in a scattered, ugly spreadsheet. Take the time to format all visible and hidden pages as best you can so users can easily find the information they need. You never know when someone will want to print out a page or even the entire workbook, so don’t forget to make the document ready to print.
Build in checks
Make sure to test your model several times before calling it final. If you don’t have real input data yet, use different numbers each time you test your model. Most importantly as you do this, add as many checks to your workbook as you can think of. In some cases it could help to link all your checks to one separate sheet, so you can have a clear view of them all at once. Depending on the way your overall spreadsheet is set up, the checks could be calculations across tabs or links to other workbooks. It usually helps to add a conditional format to these checks—for example to make the cell red if it doesn’t check out.
The information contained within your Excel workbook helps readers make business decisions. You can make their job easier by using these tips to create user-friendly and presentable workbooks.
