Fivetran formulas: Creating accurate financial statements

Follow this formula to streamline financial statements and automate data for accurate financial analysis.
November 27, 2023

Financial statements play a crucial role in every organization, serving a multitude of purposes such as regulatory compliance, monitoring financial performance, attracting investors and reporting to Wall Street. They form the fundamental building blocks of any finance analytics project. There are three core financial statements:

  1. The income statement
  2. Cash flow statement 
  3. Profit and loss statements 

Executives such as CFOs, COOs and CEOs rely on financial reports to guide their investment decisions, assess the financial health of the organization and make other strategic decisions.

In order to draw insights and reach decisions with data, you need to have accurate and real-time financial statements.

Why centralize finance data to build financial statements? 

Companies face the common challenge of integrating data from various revenue channels, such as online sales platforms, brick-and-mortar stores and third-party marketplaces. Unfortunately, financial data often exists in separate silos, making it difficult to gain a comprehensive view of the health of the business.

Accurate financial statements are the foundation for making reliable, data-driven decisions related to investment considerations, cost management and quarterly financial planning. The accuracy of these statements has a significant impact on the next course of action that a company takes. Financial statements are publicly available information, making data accuracy critical for maintaining the credibility of your company and team. Relying on manual, legacy excel spreadsheets creates risks with sweeping consequences. Imagine, for example, a single zero in the wrong column breaking all reporting. 

Typically, data teams are responsible for collecting data to ensure accurate quarterly financial reporting, while business teams often request reports or dashboards that highlight essential metrics of the core financial statements. Data engineers face the challenge of figuring out how to build and maintain data pipelines with the correct endpoints to provide data analysts with the necessary metrics for querying. This costly process can result in outages and even greater inaccuracies if done DIY. 

Fivetran’s automated data movement platform simplifies the setup and maintenance of data pipelines by providing fully-managed, pre-built connectors. Fivetran's data pipelines are designed to extract, transform and load data from a multitude of source systems into a centralized data warehouse for our customers. With support for over 400 data sources, including on-premises and cloud databases, SaaS applications, data warehouses, events and files, Fivetran ensures a comprehensive data integration solution. Moreover, Fivetran offers the flexibility to create custom Lite Connectors for customers in cases where a native connector for a specific data source is not currently available.

Centralizing all available data is the first step in achieving accurate and accelerated financial statements. Let's walk through the common data sources, Fivetran connector setup, popular tables and available transformations.

What type of connectors are applicable?

There are two primary data sources for this project: Enterprise Resource Planning Systems (ERPs) and accounting tools. 

Enterprise Resource Planning (ERP) data: 

  • Your organization likely uses a suite of ERP tools or multiple tools by an ERP Vendor. 
  • The ERP data sources will hold valuable finance, inventory, customer, production and manufacturing data. This will lead to valuable insights pertaining to cost optimization, financial analysis, production and inventory optimization and more. 

Commonly used ERP platforms:

  • Netsuite
  • Workday Financial Management 
  • Microsoft Dynamics 365 Finance 
  • Oracle Fusion Cloud Apps - FSCM
  • SAP S4_HANA
  • SAP Business by Design

Accounting and payment data: 

  • Your organization likely uses one accounting tool. Some organizations with multiple product lines or subsidiaries might use multiple accounting tools. 
  • The data from these platforms will provide insights into revenue, costs, tax compliance, budgeting and forecasting. 

Commonly used Accounting Platforms: 

  • Stripe
  • Sage Intacct
  • Quickbooks
  • SAP Concur
  • Coupa 
  • Xero 

How to setup a Fivetran connector for accurate financial reporting

Let’s walk through the steps to enable a connector. We will use the Stripe connector as an example. You will follow similar steps for the other necessary data sources your organization uses, including ERP data.

Step 1 - Add connector

Search for Stripe in the dashboard and click on ‘Setup’. 

Step 2 - Authorize an API Key

Input a destination schema and API key, then begin authorization by clicking "Save & Test." You also have the option to sync connected accounts. To do so, set the "Sync Connected Accounts" toggle to ON.

If you don't have an API key, start by creating one, depending on the resources you want to sync. There are two types of keys you can create:

1. Restricted API key - Restricted API keys allow you to limit the permissions for different resources.

2. Standard API key - Standard API keys provide both read and write permissions to all resources. Create this key if you want to sync the Fraud table along with other resources.

Tip: Try using an admin account where possible, as it has greater control and access privileges. 

Step 3 - Connection tests

After Fivetran completes a series of connection tests, select “Continue”. 

As you click 'Continue', you will have the option of using our free pre-built data model. Make a selection that suits your needs and then click 'Continue’. 

Step 4 - Start Initial Sync

The first historical sync that Fivetran does for a connector is called the initial sync. During the historical sync, we extract and process all the historical data from the selected tables in the source.

P.S. New sync-logic improvements on Stripe 

Our new synchronization logic, which will assist you in reducing MAR by nearly 50 percent, is now available and eliminates the necessity for recurrent historical syncing on the BALANCE_TRANSACTIONS table's endpoints. Try it with our Stripe connector today.

Step 5 - Configure data

You will have an option to select how you want to configure the data. Review the schemas within the Entity Relationship Diagram (ERD) to understand which tables contain relevant data on order history, charges, invoices and more.

Under the Schema tab, you can select/deselect which tables you would like to sync.

Check out the ‘Transformations tab’ to see which transformations are available for the Stripe connector. All transformations are free to use and are available on all our plans.

Step 6 - Validate

You can also select your desired sync frequency under the ‘Setup Tab’. Lastly, validate and standardize data across each new source in the destination, and your connector is now ready for use.

Transformations for your ERP, accounting and payments data

Now you have access to the data, but raw data alone isn’t ready for analysis. After Fivetran loads your raw data into your destination of choice, it also automatically transforms that data to organize and normalize it. This helps your data teams analyze and visualize data without sacrificing quality or analytical flexibility. Ensure you consider your transformation needs in Step 3 and Step 5.

Bonus: You can also integrate your dbt project to orchestrate and manage custom data models or free Fivetran data models that are not yet available via Quickstart transformations. We currently offer Fivetran data models for the following ERP and accounting connectors: Netsuite Analytics, Quickbooks, Sage Intacct, Stripe, Xero and many more. 

Accurate financial statements with automated data movement

Fast-paced companies like Intercom save up to 10 hours per week on creating accurate financial statements by eliminating data silos using Fivetran.

Fivetran enables Intercom to consolidate data from multiple sources, including Zuora, Stripe, Netsuite, Salesforce and more —allowing them to centralize financial data and generate critical insights for their finance, accounting, planning, marketing and strategy teams. The integration of various data sources has accelerated time-to-value, automated quarterly reporting and empowered departments to engage in more strategic work.

“We’ve gone from mostly manual reporting processes to mostly automated processes on top of the Zuora, NetSuite and Stripe data that we get through Fivetran. We can then join that data directly with our product data without resorting to manual workarounds. The data has become increasingly trusted by financial reporting teams, executives and more. The automation here is critical.”
- Perry Wang, Head of Analytics Engineering, Intercom. 

Core financial statements serve as essential foundations for various projects within an organization. Having a clear and consolidated view of the data contained within these financial statements can significantly enhance decision-making within the organization. Fivetran facilitates centralization and data movement from disparate sources, thereby expediting insights and establishing the cornerstones of financial analysis.

Ready to try it for yourself? Try for free today. 

Are you already a Fivetran customer? Add a connector to get started.

Commencer gratuitement

Rejoignez les milliers d’entreprises qui utilisent Fivetran pour centraliser et transformer leur data.

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
Product
Product

Fivetran formulas: Creating accurate financial statements

Fivetran formulas: Creating accurate financial statements

November 27, 2023
November 27, 2023
Fivetran formulas: Creating accurate financial statements
Follow this formula to streamline financial statements and automate data for accurate financial analysis.

Financial statements play a crucial role in every organization, serving a multitude of purposes such as regulatory compliance, monitoring financial performance, attracting investors and reporting to Wall Street. They form the fundamental building blocks of any finance analytics project. There are three core financial statements:

  1. The income statement
  2. Cash flow statement 
  3. Profit and loss statements 

Executives such as CFOs, COOs and CEOs rely on financial reports to guide their investment decisions, assess the financial health of the organization and make other strategic decisions.

In order to draw insights and reach decisions with data, you need to have accurate and real-time financial statements.

Why centralize finance data to build financial statements? 

Companies face the common challenge of integrating data from various revenue channels, such as online sales platforms, brick-and-mortar stores and third-party marketplaces. Unfortunately, financial data often exists in separate silos, making it difficult to gain a comprehensive view of the health of the business.

Accurate financial statements are the foundation for making reliable, data-driven decisions related to investment considerations, cost management and quarterly financial planning. The accuracy of these statements has a significant impact on the next course of action that a company takes. Financial statements are publicly available information, making data accuracy critical for maintaining the credibility of your company and team. Relying on manual, legacy excel spreadsheets creates risks with sweeping consequences. Imagine, for example, a single zero in the wrong column breaking all reporting. 

Typically, data teams are responsible for collecting data to ensure accurate quarterly financial reporting, while business teams often request reports or dashboards that highlight essential metrics of the core financial statements. Data engineers face the challenge of figuring out how to build and maintain data pipelines with the correct endpoints to provide data analysts with the necessary metrics for querying. This costly process can result in outages and even greater inaccuracies if done DIY. 

Fivetran’s automated data movement platform simplifies the setup and maintenance of data pipelines by providing fully-managed, pre-built connectors. Fivetran's data pipelines are designed to extract, transform and load data from a multitude of source systems into a centralized data warehouse for our customers. With support for over 400 data sources, including on-premises and cloud databases, SaaS applications, data warehouses, events and files, Fivetran ensures a comprehensive data integration solution. Moreover, Fivetran offers the flexibility to create custom Lite Connectors for customers in cases where a native connector for a specific data source is not currently available.

Centralizing all available data is the first step in achieving accurate and accelerated financial statements. Let's walk through the common data sources, Fivetran connector setup, popular tables and available transformations.

What type of connectors are applicable?

There are two primary data sources for this project: Enterprise Resource Planning Systems (ERPs) and accounting tools. 

Enterprise Resource Planning (ERP) data: 

  • Your organization likely uses a suite of ERP tools or multiple tools by an ERP Vendor. 
  • The ERP data sources will hold valuable finance, inventory, customer, production and manufacturing data. This will lead to valuable insights pertaining to cost optimization, financial analysis, production and inventory optimization and more. 

Commonly used ERP platforms:

  • Netsuite
  • Workday Financial Management 
  • Microsoft Dynamics 365 Finance 
  • Oracle Fusion Cloud Apps - FSCM
  • SAP S4_HANA
  • SAP Business by Design

Accounting and payment data: 

  • Your organization likely uses one accounting tool. Some organizations with multiple product lines or subsidiaries might use multiple accounting tools. 
  • The data from these platforms will provide insights into revenue, costs, tax compliance, budgeting and forecasting. 

Commonly used Accounting Platforms: 

  • Stripe
  • Sage Intacct
  • Quickbooks
  • SAP Concur
  • Coupa 
  • Xero 

How to setup a Fivetran connector for accurate financial reporting

Let’s walk through the steps to enable a connector. We will use the Stripe connector as an example. You will follow similar steps for the other necessary data sources your organization uses, including ERP data.

Step 1 - Add connector

Search for Stripe in the dashboard and click on ‘Setup’. 

Step 2 - Authorize an API Key

Input a destination schema and API key, then begin authorization by clicking "Save & Test." You also have the option to sync connected accounts. To do so, set the "Sync Connected Accounts" toggle to ON.

If you don't have an API key, start by creating one, depending on the resources you want to sync. There are two types of keys you can create:

1. Restricted API key - Restricted API keys allow you to limit the permissions for different resources.

2. Standard API key - Standard API keys provide both read and write permissions to all resources. Create this key if you want to sync the Fraud table along with other resources.

Tip: Try using an admin account where possible, as it has greater control and access privileges. 

Step 3 - Connection tests

After Fivetran completes a series of connection tests, select “Continue”. 

As you click 'Continue', you will have the option of using our free pre-built data model. Make a selection that suits your needs and then click 'Continue’. 

Step 4 - Start Initial Sync

The first historical sync that Fivetran does for a connector is called the initial sync. During the historical sync, we extract and process all the historical data from the selected tables in the source.

P.S. New sync-logic improvements on Stripe 

Our new synchronization logic, which will assist you in reducing MAR by nearly 50 percent, is now available and eliminates the necessity for recurrent historical syncing on the BALANCE_TRANSACTIONS table's endpoints. Try it with our Stripe connector today.

Step 5 - Configure data

You will have an option to select how you want to configure the data. Review the schemas within the Entity Relationship Diagram (ERD) to understand which tables contain relevant data on order history, charges, invoices and more.

Under the Schema tab, you can select/deselect which tables you would like to sync.

Check out the ‘Transformations tab’ to see which transformations are available for the Stripe connector. All transformations are free to use and are available on all our plans.

Step 6 - Validate

You can also select your desired sync frequency under the ‘Setup Tab’. Lastly, validate and standardize data across each new source in the destination, and your connector is now ready for use.

Transformations for your ERP, accounting and payments data

Now you have access to the data, but raw data alone isn’t ready for analysis. After Fivetran loads your raw data into your destination of choice, it also automatically transforms that data to organize and normalize it. This helps your data teams analyze and visualize data without sacrificing quality or analytical flexibility. Ensure you consider your transformation needs in Step 3 and Step 5.

Bonus: You can also integrate your dbt project to orchestrate and manage custom data models or free Fivetran data models that are not yet available via Quickstart transformations. We currently offer Fivetran data models for the following ERP and accounting connectors: Netsuite Analytics, Quickbooks, Sage Intacct, Stripe, Xero and many more. 

Accurate financial statements with automated data movement

Fast-paced companies like Intercom save up to 10 hours per week on creating accurate financial statements by eliminating data silos using Fivetran.

Fivetran enables Intercom to consolidate data from multiple sources, including Zuora, Stripe, Netsuite, Salesforce and more —allowing them to centralize financial data and generate critical insights for their finance, accounting, planning, marketing and strategy teams. The integration of various data sources has accelerated time-to-value, automated quarterly reporting and empowered departments to engage in more strategic work.

“We’ve gone from mostly manual reporting processes to mostly automated processes on top of the Zuora, NetSuite and Stripe data that we get through Fivetran. We can then join that data directly with our product data without resorting to manual workarounds. The data has become increasingly trusted by financial reporting teams, executives and more. The automation here is critical.”
- Perry Wang, Head of Analytics Engineering, Intercom. 

Core financial statements serve as essential foundations for various projects within an organization. Having a clear and consolidated view of the data contained within these financial statements can significantly enhance decision-making within the organization. Fivetran facilitates centralization and data movement from disparate sources, thereby expediting insights and establishing the cornerstones of financial analysis.

Ready to try it for yourself? Try for free today. 

Are you already a Fivetran customer? Add a connector to get started.

Articles associés

Financial transaction analytics 101: Efficiently identifying impact
Data insights

Financial transaction analytics 101: Efficiently identifying impact

Lire l’article
Seven ways finance teams drive efficiency with automated pipelines
Data insights

Seven ways finance teams drive efficiency with automated pipelines

Lire l’article
ERP data, financial efficiency and the current state of enterprise data strategy
Data insights

ERP data, financial efficiency and the current state of enterprise data strategy

Lire l’article
Financial transaction analytics 101: Efficiently identifying impact
Blog

Financial transaction analytics 101: Efficiently identifying impact

Lire l’article
Five dashboards to jump-start your analytics
Blog

Five dashboards to jump-start your analytics

Lire l’article
Financial transaction analytics 101: Efficiently identifying impact
Blog

Financial transaction analytics 101: Efficiently identifying impact

Lire l’article
Five dashboards to jump-start your analytics
Blog

Five dashboards to jump-start your analytics

Lire l’article

Commencer gratuitement

Rejoignez les milliers d’entreprises qui utilisent Fivetran pour centraliser et transformer leur data.

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.