!! General Spreadsheet Requirements

We talk a lot about the "general requirements" for the layout and formatting of spreadsheets. What exactly  are the "minimum" requirements in order to import something into CFO Scoreboard?

Minimum Requirements

It's actually pretty basic. In order to have a chance of importing successfully, you need three ingredients:

1. At least one header at the top of one column with a DATE that is properly formatted.
2. At least a couple of account names down the left side of the document.*
3. At least some numeric accounting figures (non-zero) where the date header and the account names intersect.

* At minimum, in an income statement you at least need something that can be understood as Revenue, and something else that can be understood as Expenses. In the balance sheet similarly you need at least something like Assets, Liabilities, and Equity.

That's it! So, technically, a pair of documents like this will import just fine:

You wouldn't get much in the way of optics from that data, but it  could technically be imported.

CFOS is extremely flexible

Obviously most clients will provide charts of accounts that are much more complex than the simple example given above. Our parser was built to be incredibly versatile, and can adapt to a lot of different document structures. To get an idea of what I mean, consider that we can directly import files from QuickBooks, QuickBooks Online, Xero, and MYOB. Take a look at how different each of these are:

The diversity is dizzying enough that we're not going to go exhaustively into the key technical capabilities for our parser. Suffice it to say, it will handle a lot of different patterns and formats. What is key is to avoid common mistakes (  detailed elsewhere) and provide documents that are overall consistent in their patterns.

If you're helping a client prepare spreadsheets manually, there's one more recommendation we usually make, aside from satisfying the most basic requirements above. That would be: adding hierarchy to the chart of accounts.

Adding Hierarchy

When we use the term "hierarchy", we mean structuring the chart of accounts more elaborately than a simple 1-dimensional list. Adding groups and sub-groups, and even sub-sub-groups, etc., can mean real value for the client, and a lot less pain during uploads.

The two main benefits of having hierarchy within the document are:

1. Easier imports
   CFO Scoreboard will look at your group names and use smart logic to automatically classify your accounts. If you're uploading for the first time, this will save you an extra 5 minutes -- or 20 minutes! -- depending on how large the chart of accounts is. It will also help you and the client understand how to use CFO Scoreboard's classifications  in general, because it fills out most of them for you.
2. Easier manipulation and viewing within CFOS
   Everything in the Trends area within CFO Scoreboard will become more logically organized and streamlined, if there's more hierarchy present in the document. This means clients (and you) can more easily see what's happening in the numbers, and/or pick up on data issues that could be caused by disagreements in historical data [linkneeded].

There are two main ways to add hierarchy:

1. Indented Hierarchy

If you look at a standard export from any desktop version of QuickBooks,  that is what we're talking about. Sub-accounts and sub-groups are indicated by moving the child items one column to the right. You can continue moving to the right as many columns as the hierarchy needs -- just insert more blank columns if you run out!

Since CFOS doesn't care about the display width of columns, you can resize accordingly to make things much easier on the eyes. QB Desktop exports usually come pre-formatted in this fashion already:

When adding hierarchy of this type, it's helpful to make heavy use of keyboard shortcuts. Basically all you need are the arrow keys and Ctrl-C / Ctrl-V. Just grab sections of the chart of accounts and move them to the left, drilling down as necessary.

If your spreadsheet has "closing" group lines such as e.g  Total Current Assets then they should not be indented along with their group, similar to the way the hierarchy is shown above. However, if your spreadsheet doesn't have these "total" lines or if you'd like to not worry about them, then that's fine! They can be deleted entirely. As long as the rest of the indention is set up properly the "total" lines are not needed.

2. Flat Hierarchy

Sometimes when you start with "flat" chart of accounts, and it would be confusing or tedious to do a bunch of indention along the lines of the above. Luckily there's a simpler way. As long as you have opening lines like e.g Current Assets and then have closing lines like e.g Total Current Assets, CFO Scoreboard will be able to "understand" the hierarchy of your accounts -- even if they're not indented.

Remember, the opener has to say " X" and the closing line should say "Total X". As long as you follow that pattern, you can create as rich a hierarchy as you'd like, even nesting down several levels if appropriate.

Take a look at a typical export from the accounting system Xero to see exactly how this works:

Conclusion

Hopefully this has given you a much better idea of what CFOS expects when it imports your files, and how wide the options really are!

Finally, if you need something more visual and straightforward to work with, just use this reference video to move forward: