Many small and medium businesses use a hosted accounting (NetSuite/Dynamics/QuickBooks)
and a sales/CRM system (Salesforce/Zoho) for managing sales pipelines and forecasts. While
both systems provide reporting, the data is siloed and analysis requires manual effort to
interpret. By applying artificial intelligence techniques to integrate and analyze the data,
businesses can gain deeper insights into future cash flows and profitability. This white paper
explores methods for extracting and correlating QuickBooks and CRM data using AI to provide
actionable visibility into projected financial performance.

Integrating QuickBooks and CRM Data

The first step is consolidating data from QuickBooks and CRM systems into a single analytics
platform. QuickBooks provides an API for extracting key accounting data like revenue and
expenses by account, account balances, budget vs. actuals, and cash flow. CRM systems like
Salesforce include APIs for accessing pipelines, best case forecasts, quotas, and closed sales
data. An AI system can automatically label the data from both APIs and consolidate it to analyze
further. Natural language processing techniques can parse transaction descriptions and map
QuickBooks accounts into standardized categories for further processing.
Once integrated into a common data store, the AI system can correlate sales forecasts and
pipelines with financial metrics like revenue run rates, upcoming payments, and projected cash
balances. Disparate data points become connected, providing a 360-degree view into future
financial performance.

Applying AI for Automated Forecasting and Planning

With integrated datasets, AI algorithms can be applied to automate financial forecasts and
provide insights into trends. Time series analysis and regression algorithms can detect
seasonality and model revenue run rates based on historical data. As new sales pipeline and
actual booking data comes in from the CRM, revenue predictions can be automatically
adjusted.

Expenses can also be forecasted based on historical data and expected changes, providing estimates of future profit. With cash flow visibility from AI-generated revenue and expense forecasts, the system can predict potential cash shortfalls and surpluses. Scenario analysis allows models to be generated based on different assumptions for revenue or expense growth.

Operationalizing Insights

Making sense of reams of data is not valuable unless it enables smarter decisions. The system should provide financial intelligence in accessible dashboards for business users. Interactive visualizations can highlight emerging trends and changes in projected cash flows.

Actionable alerts can be configured, such as notifications if the sales pipeline falls below a threshold or if a cash shortfall is predicted. The AI assistant can recommend relevant actions – for example, slowing spending if expenses are forecasted to exceed revenues. Integrations with accounting and CRM systems allows the AI to take action, like creating budget adjustment entries or credit limit warnings.

Here are some tips on cleaning up QuickBooks API data for use in AI applications:

• Handle missing data – The API may sometimes return null or blank values. These need to be handled appropriately depending on the specific AI algorithm. Imputation techniques like replacing missing values with column means can be used.
• Normalize transaction descriptions – Use natural language processing techniques like lemmatization to process transaction description texts into normalized forms. This makes it easier to categorize transactions for analysis.
• Standardize accounts – Map QuickBook accounts and subaccounts into a standardized ontology so that revenues, expenses, assets, etc are categorized consistently. Look for accounts like “Consulting Revenue” and map to a standard “Revenue” parent category.
• Denormalize transaction splits – QuickBooks uses split transactions to allocate amounts across multiple accounts. Denormalize these so each transaction row represents a total debit/credit amount rather than splits.
• Normalize vendor names – Use string matching or entity resolution techniques to map vendor names like “ABC Corp” and “ABC Company” to unique vendor IDs. This provides consistency when analyzing expenses.
• Filter unused data – Remove unused metadata returned by the API like transaction IDs, foreign keys etc that are not needed for analysis.
• Handle data quirks – QuickBooks data may have quirks like negative expenses or liability accounts with positive balances. Handle these edge cases appropriately.
• Handle data quirks – QuickBooks data may have quirks like negative expenses or liability accounts with positive balances. Handle these edge cases appropriately.

The goal is to transform QuickBooks data into a clean, consistent format optimized for applying AI algorithms for tasks like predictive modeling, classification, forecasting etc. Testing different preprocessing techniques and tuning based on model performance is recommended.

Business Example

One customer was an electrical engineering (EE) design firm that uses QuickBooks for accounting and Salesforce for CRM. They sell engineering consulting services. They needed to increase sales by 20% annually to be positioned for sale, but were not sure why their business had been flat.

EE’s sales team uses Salesforce to manage a pipeline of prospective deals with prospects and clients. The accounting team uses QuickBooks to track expenses, invoices, and cash flow. Financial planning is difficult because sales forecasts don’t automatically flow to the accounting system.

By implementing an AI solution that integrated and analyzed data from both systems, EE now unlocks new insights. For example, the AI assistant analyzes 3-years of monthly sales data and identifies conversion rates and the impact on cash flows. New minimums for proposals were developed. When the sales team added new quotes to the Salesforce pipeline, the AI assistant automatically adjusted its revenue forecasts in QuickBooks based on historic and predicted conversion ratios. The AI then alerts the treasury team to working capital and line of credit requirements.

With AI extracting insights between systems, EE can make data-driven decisions to manage the sales pipeline and capital requirements. This boosted revenue while avoiding cash flow issues.

AI integration of QuickBooks, CRM, and other systems enables deeper visibility into financial performance. Automated forecasting and intelligence from AI algorithms allow businesses to proactively understand and respond to potential future scenarios. The result is data-driven decision making that helps growth-oriented companies plan for profitability and manage cash flow. By unleashing the power of AI on their data, businesses can realize efficiencies and strategic advantages over competitors still relying on manual reporting and human intuition.