I installed the Taxcycle Client Manager yesterday. It went pretty well, thanks to some very descriptive tutorials about setting the CM up on a NAS box, which happens to be our preferred method of storing all data files. I also learned a considerable amount watching Andrew’s 40 minute video on using the Client Manager. It seems like a very powerful utility.
I would like to perform some fairly basic searches in an attempt to mine some financial data that will assist me in remembering client obligations. For instance, in Ontario Employer’s get fined 1.95% of their payroll in excess of 450,000. They call it Employer Health Tax. I would like to be able to run a basic search which lets me know which employers (T4 module) have over 450,000 on their T4 Summary.
Additionally, I want to make sure I don’t forget to issue T5 slips for dividends issued by my client corporations. In this regard, I would like to be able to perform a search that lets me know all corporations that have issued dividends during 2016.
I tried doing this with the Client Manager yesterday, thinking the Search Bar would be able to interpret Taxcycle Field Codes, but no such luck. After setting the proper filters, here are some examples of what I typed into the Search Bar that did not give me the expected (ie any) results.
(Check for Employers with Gross Payroll over 450k)
T4SummaryInfo.EmploymentIncomeTotal>450000
(Check for Corporations that have distributed dividends)
S3.TaxableDividendsPaid.M[0]>0 or S100.RetainedEarnings[0].CurrentYear>0
(Individual Taxpayers with Gross Farm Income over $7,000)
T1.Income.M[8]>7000
I have noticed the Search Bar is able to do some really cool things once the filters are set, so I think I must be missing something basic. I need guidance on the arguments that I should be using in the search bar to obtain the desired results.
To my knowledge the ability to access specific fields in data files through the Client Manager doesn’t exist right now. I think I asked for this ability last year since I, too, would like to use the CM to ensure I haven’t missed filing T5’s for example, but there are many more uses for this ability. I am happy to see that the CM seems to be operating much better now.
Exactly Kevin… there are many uses for this ability, and I have relied on many of these saved data field searches over the last 15 years or so using the Classic Database from back in the Greenpoint days.
In Ontario, if your payroll is over 450,000 in any calendar year, you are required to be registered for EHT and file an annual return by March 15th of the following year. If you were never registered for the EHT because your payroll has always been under the exemption, but happen to cross the 450,000 threshhold in 2016 your T4 summary would have this information. You should be able to run a basic search to get a list of all employers that are over 450k in annual payroll. Running the search is how we have always caught any new fish.
Being able to run searches on T2 returns for dividends issued is a great way of double checking you haven’t missed any T5 slips for dividends. It isn’t too bad if a corporation has been in the habit of issuing annual dividends, but what about those where dividends don’t get issued every year. We have always used searches to mine this data.
In Ontario, if you have a farm with an adjusted Gross Income over $7,000 you are required to have a valid Farm Business Registration number, and generally a membership to one of Ontario’s 2 General Farm organizations. Being able to search on the Gross Farm income field has always given me a good starting point in determining if we have any clients that should have an FBR but maybe don’t.
We run searches for seniors with low income to see whether anyone who should qualify for the OAS Supplement is actually receiving it.
Given my experience with Profile I thought being able to mine data and produce reports based on Field Codes, along with some conditional operators, was something that would be basic to the software, since the ability has always been there. Missing the ability to perform these searches was the primary reason I installed the Client Manager.
At the moment TaxCycle can retrieve field values from returns via a Batch Calculate. This doesn’t filter returns on a field value, but it does read the the field value and display it. You can then export your results to Excel.
For the T4 example, try this:
Put some T4 returns into a Batch.
Select Batch Calculate. You can optionally set Calculation detail to “Calculate only” to minimize the amount of data shown in the results.
Under Query fields, enter a description. This can be anything you want.
Then enter T4Summary[0].T4SummaryInfo.EmploymentIncomeTotal as the expression. The expression syntax is the same as you would use for a Data Monitor. So what works for a Data Monitor will work here.
I went through the steps you described above…and although it seems a little crude… I have been able to get it to work good enough to give me what I’m looking for.
I was able to search for corporations that issued dividends by using the expression in your step 4 as follows:
S3.TaxableDividendsPaid.M[0]
To double check, I could also use the Expression
S100.RetainedEarnings[0].CurrentYear
The last column of the batch calculate would put an amount of Dividends in beside any corporation that paid a dividend during the year. I could scroll through the list and write down the corps that paid out dividends. I’m not going to export to Excel… I try to avoid the use of Excel if I can help it.
Once again, thank you. I should be able to use this method to pull the data that I need. It’s for my own use so it doesn’t need to look pretty, it just needs to function accurately.