Reconciling Reports

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
Uncapitilized words = column names

TAXES PAYABLE total tax = SALES total tax
BALANCE total = SALES charge + total tax - PAYMENTS total (with Deposit = false filter)
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