As an investor that has worked with hundreds of startups, I look at a lot of spreadsheets and analytic reports all the time and am continually surprised so many are difficult to read and hard to understand.
To help entrepreneurs avoid the financial-model mistakes I see over and over again, I interviewed the two most knowledgeable people I know about financial modeling: my colleague Paul Bianco and Michael Hutchens, the CEO and co-founder of BPM Global, a company that helps businesses build and maintain financial models.
Hutchens recommends before building the model to speak to the people who will be seeing it and determine what is and isn't important to them when analyzing the model. "Often I speak with companies that have charged ahead building an ultra-complex daily or weekly model with thousands of assumptions and complex dashboard outputs, when their potential investors simply want a high-level 24 month forecast with 12 months of reconciling historical data.”
Once you determine the wants of the investors, here is how to make your model stand out from others:
1. Include a key to abbreviations and terms.
While many investors are familiar with terms like DAUs (daily active users) and QoQ (quarter over quarter), why not make it unambiguous for everyone? In addition, make sure to formally define all terms. For instance, when calculating DAUs, does active mean visited the website once, performed an engagement action or bought something?
2. Format your numbers properly.
Here are a few pointers on numbers. Use commas for all numbers over a thousand. Also, use an appropriate number of significant digits. For instance, it’s too precise to have two digits to the right of the decimal in Customer Acquisition Cost/Lifetime Value of Customer multiple for let's say year three of your forecast. On the other hand, it imprecise to show only one digit after the decimal when showing a $2 million topline income statement formatted in millions. This can be tricky, so I recommend looking at past examples or speaking to someone.
3. Use color coding.
As a general rule, font coloring is a great way to distinguish constants from formulas. However, when choosing font and fill colors to do this, bear in mind that about 8 percent of all men are colorblind, and many spreadsheets are printed in black and white. It is recommended that assumptions sheets use a light grey fill color, and white/no fill for output sheets.
4. Use a minimum font size of 10 points.
Font size plays a huge factor in readability. If you have to fit a report on one page, shrink your column sizes and use less white space.
5. Do not use hard black lines to divide rows and columns.
This makes the page look very busy and hard to scan. Instead, use alternating shaded lines or use soft dotted gray lines (suggestion courtesy of statistician and artist Edward Tufte).
6. Title your file with an easy-to-remember name.
Also, keep it simple -- something like [Company Name] [Forecast] [yyyymmdd] works. This allows readers to differentiate your file from all of the other files they receive and also easily track the evolution of your document as it changes.
7. Organize your tabs.
When multiple worksheets are used in a workbook, every tab should have a meaningful name and should read in a logical order, left to right. An amateur move is to use Excel's default three tab setup, with only one actually having data.
8. Consistency builds confidence.
Given the same calculation logic, the difference between a model that breeds confidence and one that creates anxiety is consistency. And consistency doesn’t apply only to formats and styles, it applies to every part of a spreadsheet including sheet zoom levels, heading indentation, decimal places, alignment, frozen panes placement and time series columns, to name a few.
9. Assign formatting for particular cells.
Again, this ensures the consistency of similar content (e.g. headings, assumptions, etc.). Do this in the beginning, so you don't need to manually format each cell.
10. Create an area for assumptions and main drivers.
This should be done at the beginning and be placed at the top or left of the model. Jake Perlman-Garr, co-founder of big-data company Datavore, points out that this helps facilitate both the auditing and tweaking of forecasts and also helps a potential investor understand better what the entrepreneur thinks are the important drivers of their business.
11. Separate out your model output.
Many decision makers will want to look at selected outputs (e.g. financial statement summaries, valuation analysis, ratio analysis, etc.) once a model has been finalized. Make this easy for them by creating a dedicated presentation outputs section and positioning it at the front of the spreadsheet.
12. Design for printing.
Decision makers often don’t attend meetings with a laptop, so they’re likely to print key model assumptions and outputs as meeting discussion points. By setting print areas, page breaks and scaling to ensure quick and high-quality printing, you will score points with model users. I recommend inserting report title, date printed and a page number on every footer for easy auditing.
13. Design for auditability.
Ideally, create a visual map of the structure of your analysis, so readers can understand the logic of your model. This also allows for easier auditing and if/then testing that flows through each step.
14. Explain assumptions and make sure they tie to reality.
For instance, a model that shows X% growth over time with no embedded correlation to sales/marketing is a huge red flag. Instead, the entrepreneurs should have tie the revenue growth, for instance, based on the number of sales reps and a quota. You need to show how you plan on achieving those milestones in the model.
15. Bridge historical and projected data.
A big pet peeve is reviewing an operational model that starts in "Month 1" and excludes anything that has happened in the past. A model that starts with historical data that flows seamlessly into projected data allows a user to understand how assumptions relate to reality.
16. Cash is king.
A model that shows beginning cash + revenue - expenses = ending cash doesn't tell the whole story. A good model needs to factor in cash collections, disbursements, and other working capital considerations with a balance sheet and cash flow statement.
17. Include a sensitivity analysis.
While not every model needs to have multiple pre-set scenarios built in, it is useful to see, at a minimum, how changing key assumptions impacts the model outputs in real time. It is useful to see this output directly on the assumptions tab so there’s no need to flip between tabs after making an assumption tweak.
18. Output key metrics, not just financial statements.
In addition to summary financial statements, include an output of key metrics like acquisition costs, lifetime value and cohort retention.
Hutchens of BPM Global recommends studying the Spreadsheet Standards Review Board’s "Best Practice Spreadsheet Modeling." I also suggest reading everything that Edward Tufte has written and see Hans Rosling’s work for another expert in visual display of quantitative information. I’ve also written more previously on preparing spreadsheets and financial models.