An unfortunate byproduct that has evolved in the explosive proliferation of spreadsheets for tracking and analyzing financial reports is the increased complexity that makes them difficult to understand. This is adverse to the direct interest of controlling financial information that describes historical results and facilitates future projections. Moreover, making spreadsheets easier to read helps lenders or investors comprehend the future expectations for your business.
Financial Spreadsheets are Worthless Without This
An essential feature for every spreadsheet is a listing of assumptions. The entire spreadsheet is a model based on logic. Assumptions capture the potential output from available resources. That is, they reflect how much you can deliver with the given level of personnel, equipment, and working capital. You must correlate projected revenue for a specific period with the time available for the people who will perform the work, the machinery or tools at their disposal, and the cash you have for marketing and sales.
Define any abbreviations you use. 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, does your measurement of daily active users mean site visitors or purchasers.
Sensible financial projections are bridged to past results. The initial month in a forecast should indicate where the business already stands. Then projected outcomes flow from an historical base.
A financial forecast summary shows for each period 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. But these may be offset by additions of amounts borrowed or received from investors. 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 to a future period in the forecast when those customers will pay.
Cosmetic Factors on Spreadsheets Matter
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. This permits assessing the practicality of assumptions and adjusting them for instant recalculation of the forecast.
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 definitely use at least 10-point font.
Organize spreadsheet tabs in a logical order from left to right. For instance, the first tab is typically a summary sheet that ties the results on 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. For instance, a sheet with forecasted revenue growth alone might be quite complex, reflecting increased sales from gradually utilizing the full capacity of resources. And variable expenses, which fluctuate with revenue, have their own set of assumptions that are distinctive from fixed overhead costs.
The Finance Writer 