Tuesday, November 6, 2018

Sales by Customer Report that will Tie Up with the Revenue information in the Income Statement

In most cases the main reason why Financial Statement Report balances like the Income Statement do not coincide with the balance of some Non-Financial Statement Reports is because of the differences in the report designs between the (2) reports. 

Financial Statement Reports in general are account type specific.  Balances derived from these reports are based on how specific account types are grouped whereas Standard Non-Financial Statement Reports have varying predefined settings already created within the system depending on the purpose and nature of the report.  Most of which are tied up to a particular transaction or type of transactions

As an alternate solution, we may create a custom saved search that groups the revenue related balances associated to each customer record. 

Here are the steps to create the search:

-Navigate to Reports>Saved Searches>All Saved Searches>New

-Select Search Type = Transaction

-Navigate to Criteria ta>Standard subtab>Filter column. Add the following fields:

a. Account Type = is Income

b. Posting = is true

c. Customer/Project Fields... >Entity Type = is Customer

-Navigate to Results tab:

Sort by : Select from the drop down list = Name

Then by: Select from the drop down list = Account

Then by: Select from the drop down list = Date

Show Totals: mark the box next to this field

-Navigate to Results tab > Columns subtab > Field column. Add the following fields:

a. Name | Summary Type: Group

b. Date

c. Type

d. Number

e. Account

f. Amount (Gross) | Summary Type: Sum

-Navigate to Available Filters tab > Filters column. Add the field : Date and mark the box under the Show in Footer column next to the field added.

-Hit Save & Run button

Note:

a. Remove the criteria filter set for: Customer/Project Fields...>Entity Type = is Customer should you need to display the total balance in the report equal to that of your income statement balance for sales. The discrepancy signifies that there are transactions recorded that impact sales accounts but are either tied up to a different entity record other than the customer or no entity type was associated at all.  Transactions that are not associated to a certain entity type will be grouped under: -None-

b.  If the Saved Search takes too long to pull up the results; you can set a filter under the Criteria tab to specify a shorter date range

No comments:

Post a Comment