Fivetran is constantly working on building out ready-made data models that take away most of the dirty work for the analyst.
However, as we build out our models for our end customers, one of the key mantras we stress in development is flexibility. Data can be complex and many factors can update and change. So it’s important for our models to be configurable for the end user.
We most recently made updates to our QuickBooks package to provide our customers with more advanced features and functionality. This way our end users can produce the necessary reporting they need for their financial teams.
One of the key new features in QuickBooks is the cash flow statement. The cash flow statement is one of the three required financial statements for most organizations (Fivetran has already built out the balance sheet and the profit and loss model), and will allow for teams to track their cash flow.
But the cash flow statement is definitely the most complex of the three models, and the one that requires the most thought in terms of constructing and developing.
For example, here is a cash flow statement from Amazon.
You can see each of these line items in the cash flow belong to several major cash flow activity categories. Then you have subcategories that fall underneath those cash flow types, which we then assign based off various account values.
For the purposes of having a usable cash flow statement, we deployed a model based on our own best understanding of how financial models are usually set up. Cash flow types fall into one of four buckets that we assign based on how most statements are set.
- Operating: A measure of the amount of cash generated by a company's business operations. Account types like current assets & liabilities, accounts receivable & payable, credit card as well as account names like net income adjustment were assigned here by our default logic.
- Investing: Any inflows or outflows of cash from a company's long-term investments. Account types like fixed and other assets end up here by default.
- Financing: The movement of cash between a firm and its owners, investors, and creditors. Account types like long term liability and account classes like equity fall here under our logic.
- Cash or Cash Equivalents: Line items that report the value of a company's cash assets. Bank account types are assigned here.
Also, we define the order based on our best knowledge of how statements generally get ordered. For the cash flow, for example, the ordering usually goes:
- Operating
- Investing
- Financing
- Cash or Cash Equivalents
However, cash flow statements are entirely dependent on the definitions that your finance and accounting teams set. Our definitions are based on the broadest best practices that are used for most of the industry, but each business is different and has its own financials to use.
Additionally, for ordering, perhaps you want to organize each of your line items based on specific account categories and dig one step deeper.
So there is not a “one size fits all” approach we can use that can create a satisfactory data model that you can update and produce. That means we cannot just hardcode our own definitions, but only provide the most basic of setups for Quickbook customers with the simplest cash flow requirements.
Enter the dbt seed file!
With this seed file, you can import your own custom data definitions for the cash flow type based off of the various account fields described above. This seed configuration will override our default setup in our models, and allow you to move closer to a final cash flow statement that matches your own financial accounting practices!
Below is an example of a more advanced seed file that we created that helps you better understand how to configure your own.
NOTE: You can create your own CSV with a similar structure in Google Sheets and use that in place of the one below to fit your own needs.
report,account_class,account_type,account_sub_type,account_number,cash_flow_type,ordinal
Cash Flow,,Accounts Receivable,,,Operating,1
Cash Flow,,Bank,,,Cash or Cash Equivalents,2
Cash Flow,,,MoneyMarket,,Cash or Cash Equivalents,3
Cash Flow,,,,1151-17,Cash or Cash Equivalents,4
Cash Flow,,Credit Card,,,Operating,5
Cash Flow,,,,2155-00,Operating,6
Cash Flow,,Other Current Asset,,,Operating,7
Cash Flow,,,Inventory,,Operating,8
Cash Flow,,,,1311-16,Operating,9
Cash Flow,,Accounts Payable,,,Operating,10
Cash Flow,,Other Current Liability,,,Operating,11
Cash Flow,,,GlobalTaxPayable,,Operating,12
Cash Flow,,,,2412,Operating,13
Cash Flow,,Fixed Asset,,,Investing,14
Cash Flow,,,FixedAssetComputers,,Investing,15
Cash Flow,,,,1622-00,Investing,16
Cash Flow,,Other Asset,,,Investing,17
Cash Flow,,,Goodwill,,Investing,18
Cash Flow,,,,1688-00,Investing,19
Cash Flow,,Long Term Liability,,,Financing,20
Cash Flow,,,NotesPayable,,Financing,21
Cash Flow,,,,2514-00,Financing,22
Cash Flow,Equity,,,,Financing,23
Cash Flow,,,PaidInCapitalOrSurplus,,Financing,24
Cash Flow,,,,3800-00,Financing,25
Instead of just relying on our own defaults, you can now utilize and modify the `account_class`, account_type`, `account_sub_type` and `account_number` values to configure what exactly your cash flow type and ordinal values should look like in your end cash flow statement.
Once you add this seed file to your dbt_project.yml by defining a new variable, it will override our default logic and reference this configuration.
Notice the following:
- In each row of the seed file, you should only populate **ONE** of the `account_class`, `account_type`, `account_sub_type`, and `account_number` columns to avoid duplicated ordinals and test failures. This should also make the logic cleaner in defining which account value takes precedence in the ordering hierarchy.
- There are four account categories (account_class, account_type, account_sub_type, account_number) that you can utilize to define your own cash flow types and ordering.
- The cash flow types (Financing, Investing, Operating, Cash or Cash Equivalents) we created are here, but you can certainly add more types if you like that fit your own cash flow practices! Feel free to reconfigure the seed file to create your own custom types for your own financial operations.
For full instructions on how to set up your seed configuration, check out our `dbt_quickbooks` README! You can also see how we set up an ordering seed file for both the balance sheet and profit and loss statement.
Using seed functionality in our dbt packages will allow you to automate most of the work in creating more definitive financial statements that suit your needs! We’d love to hear from you about how you set up your own custom seed configurations.
Let us know about your own particular seed configurations in the comments, or leave any questions in the Fivetran community user group!
