Reconciling Reports

Owned by Mahnoor Khan (Deactivated)
Last updated: Sept 15, 2021 by Greg Walsh
5 min read

Summary

This document will list reports between which reconciliation can be done assuming similar time periods or other constraints for use in accounting.

Reconciliation Q and A

How Much Money Was Billed?

The Sales Report will show all debits for all users including tax. The report is  broken down by individual user with their debit and separated tax with a total for all debits including tax.

How Much Money Was Paid?

Payments report will provide a list of all payments in the system, as well the Credits report will provide any credits on accounts.

Note: Make sure to exclude deposits from this report when reconciling against Aged reports.

How Much Money Is Owed?

There are multiple balance reports that can be used. Aged A/R, Deferred Revenue, and Taxes Payable will provide details.

The reports below will have specific equal values when they are generated with the same date range:

Reconciliation Equations

Note: The content below assumes no grouping and is filtered by date only using the same AS OF dates for each.

Format:
Capitalized words = Report name
Uncapitalized words = column names

  1. TAXES PAYABLE total tax = SALES total tax

  2. BALANCE total = SALES charge + total tax - PAYMENTS total (with Deposit = false filter)

  3. CHARGE total (Sales report) + TAX total (sales report)  AMOUNT total (Payments report, posting date to be used - with Deposits removed) = AR total (as of same date)

An SQL query that can be run as well here is this:

DECLARE @periodstart datetime DECLARE @periodend datetime DECLARE @agedstartbalance DECIMAL DECLARE @agedendbalance DECIMAL DECLARE @sales DECIMAL DECLARE @taxes DECIMAL DECLARE @payments DECIMAL DECLARE @difference nvarchar(50)SET @periodstart = '2009-2-1' SET @periodend = '2010-1-1' EXEC [boss].[dbo].logisense_boss_aged @periodstart, 30, 30, 30, 30 SELECT @agedstartbalance = SUM(balance) FROM [boss].[dbo].viewagedreport EXEC [boss].[dbo].logisense_boss_aged @periodend, 30, 30, 30, 30 SELECT @agedendbalance = SUM(balance) FROM [boss].[dbo].viewagedreport SELECT @sales = SUM(charge) FROM [boss].[dbo].viewreportsalebyservice WHERE DATE >= @periodstart AND DATE < @periodend SELECT @taxes = SUM(tax) FROM [boss].[dbo].viewreportsalebyservice WHERE DATE >= @periodstart AND DATE < @periodend SELECT @payments = SUM(amount) FROM [boss].[dbo].viewpayment WHERE deposit = 0 AND DATE >= @periodstart AND DATE < @periodend SET @difference = @agedstartbalance + @sales + @taxes - @payments - @agedendbalance SELECT @difference

Other Reports That Will Reconcile

Aged Invoice Balance, Column: Total = Aged Invoice Detail, column: Balance

Reports That Will Not Match

Sales report by posting date or any date vs. Invoices report: These will not necessarily match because an invoice dated in February may contain transactions for March or January. For matching to occur, the assumption would need to be that an invoice dated in February had ONLY transactions on it that were also dated in February.

Automated Reconciliation and Monthly Status Emails

The below takes the existing report views and a range of dates and calculates the aged reconciliation values for you. This process can be scheduled to be emailed monthly to you as well for quick and easy reconciliation numbers. Notice that it reads (in column headers) left to right so you can calculate out the aged difference and spot check the accounting.

  • AgedStartBalance is Aged Balance report run as of 5/31

  • Sales is Sales report filtered on posting date of 6/1 to 6/30

  • Taxes is sales report filter by posting date of 6/1 to 6/30 and using the tax total sum at bottom

  • Payments is the Payments report filtered on posting date of 6/1 to 6/30

  • AgedEnd is Aged Balance report run as of 6/30

  • Aged Difference is the sum of the whole equation (the plus and minus text in the column headers are there so you can read from left to right)

Below is the SQL query that can be setup as  a job and emailed for monthly reconciliation.

Note: that this below query is fully based on POSTINGDATE as opposed to just DATE. You need to ensure your Aged Report view is updated to use posting date here

DECLARE @periodstart datetime DECLARE @periodend datetime DECLARE @agedstartbalance money DECLARE @agedendbalance money DECLARE @sales money DECLARE @taxes money DECLARE @payments money DECLARE @difference nvarchar(50) SET @periodstart = '2013-6-1' SET @periodend = '2013-7-1' EXEC [engageip].[dbo].logisense_boss_aged @periodstart, 30, 30, 30, 30 SELECT @agedstartbalance = SUM(balance) FROM [engageip].[dbo].viewagedreport EXEC [engageip].[dbo].logisense_boss_aged @periodend, 30, 30, 30, 30 SELECT @agedendbalance = SUM(balance) FROM [engageip].[dbo].viewagedreport SELECT @sales = SUM(charge) FROM [engageip].[dbo].viewreportsalebyservice WHERE PostingDate >= @periodstart AND postingdate < @periodend SELECT @taxes = SUM(tax) FROM [engageip].[dbo].viewreportsalebyservice WHERE postingdate >= @periodstart AND postingdate < @periodend SELECT @payments = SUM(amount) FROM [engageip].[dbo].viewpayment WHERE deposit = 0 AND PostingDate >= @periodstart AND postingdate < @periodend SET @difference = @agedstartbalance + @sales + @taxes - @payments - @agedendbalance SELECT @agedstartbalance AS 'AgedStartBalance plus' , @sales AS 'Sales plus' , @taxes AS 'Taxes minus' , @payments AS 'Payments minus' , @agedendbalance AS 'AgedEndBalance equals', @difference AS AgedDifference

Code for the automated job looks like this (the below code does not include the posting date change). To use it, create an SQL job, setup your SQL mail configuration and use this to send it out weekly, or monthly as required.

EXEC msdb.dbo.sp_send_dbmail @recipients=N'test@logisense.com;test1@logisense.com',@body='This is the monthly reconciliation report >>>', @subject ='Scheduled Report Reconciliation',@profile_name ='Mail Sendout',@query = "USE engageip USE engageip declare @periodstart datetime declare @periodend datetime DECLARE @Today datetime SELECT @Today = getdate() SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,-1,@Today)) SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,0,@Today)) --set @periodstart = SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,0,@Today)) -- first date of period --set @periodend = '2012-12-1' -- first day of next monthh select @periodstart = DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,0,@Today)) -- first date of period select @periodend = DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,1,@Today)) exec [engageip].[dbo].logisense_boss_aged @periodstart, 30, 30, 30, 30 declare @agedstartbalance money select @agedstartbalance = sum(balance) from [engageip].[dbo].viewagedreport exec [engageip].[dbo].logisense_boss_aged @periodend, 30, 30, 30, 30 declare @agedendbalance money select @agedendbalance = sum(balance) from [engageip].[dbo].viewagedreport declare @sales money select @sales = sum(charge) from viewreportsalebyservice where date >= @periodstart and date < @periodend declare @taxes money select @taxes = sum(tax) from viewreportsalebyservice where date >= @periodstart and date < @periodend declare @payments money select @payments = sum(amount) from viewpayment where deposit = 0 and date >= @periodstart and date < @periodend declare @difference smallmoney set @difference = @agedstartbalance + @sales + @taxes - @payments - @agedendbalance select @agedstartbalance as AgedStart,@sales as Sales,@taxes as Taxes,@payments as Payments,@agedendbalance as AgedEnd,@Difference as [Difference] ", @attach_query_result_as_file = 0

Â