May 4, 2011

When presenting financial models, both form and substance are important. It goes without saying that your assumptions need to be justifiable, the balance sheet, income statement and cash flow statement need to be linked and that the overall story behind the numbers needs to be attractive to investors. But how the numbers are presented is also important. Poorly formatted spreadsheets are a complete turn off to investors. Make it simple for investors to see what you want them to see–namely the story behind the numbers.

Here are some of the most important items to remember when formatting spreadsheets.  This assumes that you are working with Microsoft Excel, but the same principles apply regardless of what application you use.

Sheet structure

Microsoft Excel allows you to have multiple worksheets per Excel file.  Use them!  I have seen many models where the income statement, balance sheet, cash flow statement and other supporting statements are all in the same Excel sheet. Having to scroll to row 1,047 to see your cash balance in month 12 is not associated with good formatting. Make sure you rename each sheet to a name that properly reflects its content.

Good example

Bad example 

Time periods

Your model should project out for five years. The first several years should be projected on a monthly basis. This provides sufficient detail for analysis. Beyond that, presenting annual numbers is sufficient. If your company is a startup or has only been in business a short time, projecting with any accuracy your sales for Month 58 is not realistic. You may choose to build your model monthly for 60 months but should then present Years Three, Four and Five on an annual basis.

Good example 

Bad example

Rounding and decimal places

Investors aren’t interested in amounts down to the last penny. It’s distracting and also provides a false sense of accuracy. Anyone claiming that they know down to the penny what their sales will be in a future month isn’t credible. If your company’s numbers are large, round to the millions. If not, round to the thousands. Present at most one decimal point to the rounded numbers. Clearly indicate what rounding you have implemented.

Good example

Bad example

Fonts and sizes

The current version of Microsoft Excel defaults to Calibri font and size 11 text. This combination is acceptable for your numbers. Equally acceptable are Times New Roman, Arial and Verdana fonts. Font sizes of 10, 11, and 12 are equally acceptable. Microsoft Excel also offers the ability to use pre-formatted cell formats. These are somewhat more elaborate. I wouldn’t recommend them unless you are certain you can use them effectively.

Good example 

Bad example

Lines

Lines should be used sparingly and only in the appropriate places. Sub-totaled numbers should have a single black line above the total. Totals should have a double black line underneath the total. A single black line underneath the dates is also recommended. But other than that, be careful.  Using lines to create boxes or to organize information can be confusing.

Good example

Bad example

Headers and Footers

You should always assume that your spreadsheet will be printed out. Make sure that your headers and footers are properly formatted for each worksheet in your Excel file.  To modify the Headers and Footers, go to the Page Layout Menu, select Page Setup and Custom Header or Custom Footer.

Use the three default sections of an Excel sheet header. Put your company name in the left header, the name of the sheet (i.e. Balance Sheet) in the center header and “Confidential” in the right header. Insert the page number in the center footer.

Colors

Your model should use two colors. Cells that are inputted by the user should be in blue. Cells that are calculated by excel should be in black. All headers should also be in black.

Pagination

Make sure that you test out what your model will look like when printed. Is the pagination correct? Are the first column header and first row header repeated as the pages print?  If you manually adjust the print area borders can you achieve a better result?

Assumptions

Finally, make sure that all of your assumptions are kept on a single sheet. Many models have input cells in all sheets and in awkward places. Having a single sheet where the user can see all of your principal assumptions and make adjustments is a more elegant way to present this information. It is also more user-friendly.

Related Topics

• Technology

Related keywords

Mike Periu, Article, presentations, Microsoft Excel