Make Financial Spreadsheets More Meaningful

The explosive proliferation of spreadsheets has brought with it an unfortunate byproduct: complexity. This quality is not bad in and of itself. However, some financial analysis reports have increased in complexity to the point where they are indecipherable. In some cases, creators fail to incorporate appropriate information, rendering the report useless.

If developed and used properly, forecast spreadsheets can facilitate projections for in-house use and allow lenders or investors to better comprehend the future expectations of your business. With this in mind, use the following principles to craft your data.

Make assumptions. In other areas of life, you’re told never to assume. When it comes to financial spreadsheets, however, assumptions are a good thing. In fact, spreadsheets are worthless without them. Your spreadsheet must have a listing of assumptions. These reflect how much you can deliver with the given level of personnel, equipment, and working capital. You must correlate projected revenue with the time, people, and tools available to complete the work, and with the cash on hand for marketing and sales.

Place your assumptions right in the spreadsheet. Use color-coding to distinguish assumption constants from formula financial results. Assumptions should appear in front of dollar figures – on top or to the left of the money numbers – for easy access and instant adjustments.

Crack your code. If you use any abbreviations, define them. If, for example, you operate a fleet of trucks delivering construction materials, make sure to convey the unit of measurement – like square yards or board feet. Or, if you sell items through a website, make it clear whether your measurement of daily active users reflects site visitors or purchasers.

Learn from the past. Sensible financial projections are bridged to past results. The initial month in a forecast should indicate where the business already stands. Projected outcomes then flow from this historical base.

Cover all the bases. For each period, a financial forecast summary should show the beginning cash, the addition of revenue collected, the subtraction of every expense category, and the ending cash. Don’t forget to subtract the costs for new capital goods like machinery and equipment.

You must also remember to consider any lag time between selling and getting paid. Reduce incoming revenue by a reasonable percentage of sales that are not paid upon delivery; add that revenue to a future period in the forecast, when those customers will pay.

Tab it. Organize spreadsheet tabs in a logical order from left to right. For instance, the first tab is typically a summary sheet that tallies the results from subsequent sheets of projected revenue, variable expenses, fixed costs, and asset purchases.

Separating these elements on distinctive sheets is often necessary for comprehending detailed connections. You may also need to apply different sets of assumptions to various sheets.

Keep it neat. Cosmetic factors on spreadsheets matter. Make your spreadsheets readable with numbers that have commas and a consistent number of digits after the decimal point. Align all column headings over the numbers. And give your eyes a break: use at least 10-point font.