Auto Spreading of Financial Statements

  • Updated

Financial statement spreading is the process of transferring information from a borrower’s financial statements and feeding it to the bank’s financial analysis spreadsheet. It can involve inserting the borrower’s financial statements and tax documents that go back years into a special credit spreading software, thus helping the analyst spot trends over the years and understand the borrower’s financial condition. Capturing and analyzing a borrower's financial statements can be a time-intensive process and lead to lengthy underwriting and processing times.

Currently, the bank officials refer to multiple tax documents to populate the "Income" statement for the borrowers. For example, financial data from Form 1040 + Schedule C's are used to generate the income statement. If the borrower owns multiple businesses, there are multiple schedule C's (one for each company).

Q2 Origination extracts the data automatically from these financial statements by leveraging optical character recognition (OCR). With automated spreading, financial institutions can streamline the underwriting processes, increase efficiencies, and improve data quality. Q2 Origination is enhanced with the following changes to support the auto spreading of financial statements:

  • OCR of financial statements

Note

In the Spring'22 release, Form 1040 and Schedule C of the year 2020 are supported for auto spreading to create income statements.

  • Extract data from Form 1040 and Schedule C by invoking the ABBY OCR adapter

  • Configuration tool to map the fields from extracted data to the financial statement template

  • Create financial statement

Prerequisites

Before you begin to use the auto spreading feature of Q2 Origination to analyze the financial data, ensure that the following prerequisites are met:

  • Document definition of Document Type = 'Personal Tax Statements' is created in ABBY.

    To OCR any document from ABBYY, document definition for the respective document should exist in ABBYY.

    Note

    To define a document definition, see Q2 Integrations Documentation > ABBYY OCR - Installation Guide > Document definitions setup.

  • The integration object model is ready

  • OCR Enabled flag is true

  • The following financial statement templates are configured for the income statement and are available post-installation of Spring'22 packages:

    • Personal Statement and Tax Return

    • Sole Prop Schedule C

    • S-Corp Schedule E

    • Rents & Royalties Schedule E

    • Farming Schedule F

    • Partnerships Schedule E

    • Estates and Trust Schedule E

Steps

To use the auto spreading feature of Q2 Origination to analyze the financial data:

  1. Log in to your Salesforce account.

  2. Go to image idm3559 (App Launcher) > Applications.

  3. From the list of applications, select the required application id for which you want to analyze the financial data.

  4. Go to Documents and upload Form 1040 and Schedule C for the year 2020.

    Note

    • If the OCR Enabled flag is enabled and OCR Status is not Donefor the documents, these documents are sent to ABBYY to extract data and store it in the integration object model.

    • The extracted financial data of Form 1040 is stored in the intdm__tax_form_1040__c integration object and of Schedule C is stored in the intdm__schedulec_tax_form_1040__c integration object.

    • To know how OCR works, see Q2 Integrations Documentation > ABBYY OCR - User Guide.

  5. To create financial statements, the extracted financial data needs to be mapped with the Q2 Origination's financial statement template. To know how to perform the mapping, see Auto Spreading Mapping Configuration.

  6. Execute the following two APIs to create a financial statement for a particular year:

    • createUpdateOCRFinancialStatement API to create the income statement for the borrower

    • addUpdateOCRFinancialSpreadSchedule API to create the additional schedule c statement for the borrower

      Note

      To know more about these APIs, see Q2 Origination Global Methods > FinancialStatementOCRActions.

Example

To generate an income statement, perform the following steps:

  1. Upload the documents for the following document categories on the Documents page of Q2 Origination's application dashboard:

    Personal Tax Return (Prior Year 1)

    Upload the signed federal income tax returns from the previous fiscal year.

    Personal Tax Return (Prior Year 2)

    Upload the signed federal income tax returns from two years prior to the current fiscal year.

    Personal Tax Return (Prior Year 3)

    Upload the signed federal income tax returns from three years prior to the current fiscal year.

    P&L Statement/Schedule C (Prior Year 1)

    Upload the P&L statement from the previous fiscal year.

    P&L Statement/Schedule C (Prior Year 2)

    Upload the P&L statement from two years prior to the current fiscal year.

    P&L Statement/Schedule C (Prior Year 3)

    Upload the P&L statement from three years prior to the current fiscal year.

    OCR is triggered when Documents' Status = APPROVED. And the financial data are extracted and stored in the integration data model.

  2. Go to Q2 Origination > Origination Configuration > Financial Spread Mapping.

  3. Select New.

  4. Perform the following actions as described in the following table, and then select Save:

    Field Name

    Action and Information

    Financial Spread Mapping

    From Object

    Select Tax Form 1040 for Form 1040 and Schedule C Tax Form 1040 for Schedule C.

    Financial Statement Template

    Select the search icon to select Personal Statement and Tax Return for Form 1040 and the following for Schedule C:

    • Sole Prop Schedule C

    • S-Corp Schedule E

    • Rents & Royalties Schedule E

    • Farming Schedule F

    • Partnerships Schedule E

    • Estates and Trust Schedule E

    Additional Schedules Mapping

    Parent Template

    Select the search icon to select Personal Statement and Tax Return.

    This section is displayed if you define the mapping for any Schedule C template.

    Financial Spread Mapping Details (Select image idm15946 to add new mapping)

    From Field

    Select the field from the integration object that you want to map with the financial statement template details.

    This drop-down lists all the fields stored in the integration object.

    Financial template Details

    Select the financial statement template details to which you want to map the selected field of the integration object.

    This drop-down lists all the line items configured for the financial statement template.

    A message is displayed on successful mapping of integration object and financial statement template details, "Success: Saved Successfully".

  5. The createUpdateOCRFinancialStatement API is executed and the income statement is created for the borrower based on the mapping defined in the above step.

  6. The addUpdateOCRFinancialSpreadSchedule API is executed and the additional schedule c statement is created for the borrower based on the mapping defined in the above step.

  7. Once the financial statements are created, the financial statement summary is generated and these values are used in scorecard evaluation to arrive at an overall risk score.

Auto Spreading Mapping Configuration

Q2 Origination is enhanced to support the auto spreading of financial statements. The default templates are created for the following two financial statements:

  • 1040 Individual tax form (US Region Only)

  • Schedule C - of 1040 (US Region Only) - Schedule C is typically for borrowers who operate sole proprietorships or single-member LLCs. A Schedule C is the profit and loss statement for each business entity owned by the borrower. For example, John Doe owns Doe Toy Stores and Doe Toy Supplies. John Doe will have to submit the following forms every year:

    • Form1040

    • Schedule C for Doe Toy Stores

    • Schedule C for Doe Toy Supplies

    Financial data from Schedule C is captured to determine the overall cash flow from each business owned by the borrower.

The financial data of these documents should be extracted and mapped into yearly financial statements for the borrower on the Financial Spreads page. To perform the mapping of these details, perform the following steps:

  1. Log in to your Salesforce account.

  2. Go to image idm3559 (App Launcher) > Origination Configuration > Financial Spread Mapping.

    image idm16085

    The following templates are configured for the income statement and are available post-installation of Spring'22 packages:

    • Personal Statement and Tax Return

    • Sole Prop Schedule C

    • S-Corp Schedule E

    • Rents & Royalties Schedule E

    • Farming Schedule F

    • Partnerships Schedule E

    • Estates and Trust Schedule E

  3. Select New.

  4. To define a new mapping, perform the following actions as described in the following table, and then select Save:

    image idm16090

    Field Name

    Action and Information

    Financial Spread Mapping

    From Object

    Select Tax Form 1040 for Form 1040 and Schedule C Tax Form 1040 for Schedule C.

    Financial Statement Template

    Select the search icon to select Personal Statement and Tax Return for Form 1040 and the following for Schedule C:

    • Sole Prop Schedule C

    • S-Corp Schedule E

    • Rents & Royalties Schedule E

    • Farming Schedule F

    • Partnerships Schedule E

    • Estates and Trust Schedule E

    Additional Schedules Mapping

    Parent Template

    Select the search icon to select Personal Statement and Tax Return.

    This section is displayed if you are defining the mapping for any Schedule C template.

    Financial Spread Mapping Details (Select image idm15946 to add new mapping)

    From field

    Select the field from the integration object that you want to map with the financial statement template details.

    This drop-down lists all the fields stored in the integration object.

    Financial Template Details

    Select the financial statement template details to which you want to map the selected field of the integration object.

    This drop-down lists all the line items configured for the financial statement template.

    A message is displayed on successful mapping of integration object and financial statement template details, "Success: Saved Successfully."

  5. To edit a mapping, for the financial spread mapping, select image idm16095 and then select Edit. You can add/edit/delete the mapping details and then select Save.

    Note

    The modifications to the mapping must apply to the new financial statement creation and must not impact the financial statements created prior to modification.

In This Article