K2's Road to Excellence with Excel Part 10 and TaxCycle

This is the tenth of sixteen sessions in Excel that will provide you with the information you need to become an Excel expert. In this session we turn our attention to PivotTables that are the most powerful feature of Excel, yet few accountants and bookkeepers use them in their day-to-day activities. For some, PivotTables are too intimidating; for others, PivotTables are a foreign concept. After this webinar you will be ready to begin using PivotTables to accomplish routine analysis and reporting.

Learning Objectives

  • Identify and define the six main elements of a PivotTable report
  • Create PivotTables that summarize large volumes of data
  • Drag-and-drop pivoting, group and ungrouping, and drilling down to underlying details


Excel Integration
with TaxCycle
Put your mastery of Excel to work in TaxCycle. Discover the integration between Excel and TaxCycle professional tax software.

Learning Objectives

Upon completing this session, you should be able to:
  • Import business statement information from Excel into TaxCycle T1;
  • Import GIFI information from Excel into a TaxCycle T2 corporate tax return;
  • Export reports from the TaxCycle Client Manager database.

Speaker: Elizabeth Kohl
Director of Communications and User Experience
Trilogy Software
In 2000, Elizabeth began her career in professional tax software writing documentation and delivering training seminars for Intuit ProFile. She then went on to lead instructional design for a national online-learning provider. Since 2012, Elizabeth champions clear, concise communication, and applies her design skills to make TaxCycle simple, usable and delightful for Canadian tax preparers. After hours, you'll find Elizabeth knitting, reading and exploring the city with her toddler.

More Ways to
Earn Verified CPD


Access the many courses from the leader in providing CPD to professionals throughout North America.

K2's Advanced Excel Reporting - Best Practices, Tools and Techniques
Catalogue of upcoming webinars

Questions & Answers from the Webinar

Excel Questions
Answers
How did he get the number of rows?
I selected the top cell. Then I used the following trick to get to the last filled cell in that column. Here’s an explanation:
 
Double-Clicking Saves Scrolling
  • To quickly jump to the end of a range of data in your Excel worksheet, you can double-click the border of the active cell in the direction you want to move.
  • For example, if you double-click the bottom edge of the active cell, you will instantly jump to the last cell in that column.
  • If there are any blank cells in the column, the cell immediately above the blank cell will be selected.
Once you have that technique mastered, you can hold down the SHIFT key while double-clicking the edge of the active cell to select a range of cells from the beginning cell to the cell immediately above the first blank cell.
How do you make all the numbers in the pivot table have 2 decimal places without a lot of formatting?
Highlight the data area in the PivotTable. Go to the Number group and click on the
“ , ” icon.
When you add "Month" to the quadrant under columns, how do you then sort it by month? Rather than have every date show up?
Select any MONTH in the PivotTable header. Right mouse click and select GROUPS. Make sure that only MONTH is selected.
If my quarters are not by calendar, how do I use the group function?  i.e. Sept-Nov is a quarter
That is easy to do, and I will be demonstrating this in my next mini-webinar on June 25th.
If you change the source data does the pivot table automatically update?
If just the data changes and not the range of your data, you can right mouse click and select REFRESH.

If the range of your dataset changes then you have two options. Option 1 is to change the range. Click on PivotTools at the top of your screen. Then click on the Analyze tab. Next click on the Change Data Source icon and enter the new range. Finally, right mouse click and select REFRESH.

A much better solution is to format your dataset as a table. Then if your range changes Excel will automatically change the data source and you can update the PivotTable by clicking on REFRESH.
Are you going to do sessions on Power Pivot?
Yes, there are tentative plans to have my partner do a session on this topic in the late summer.
TaxCycle Questions
Answers
I use TaxCycle for T1s and Profile for one-pays because TaxCycle doesn't offer that feature. Intuit offers to let me purchase just one tax return. I use it for T2s and T3s as I often only have one or two returns in a year. My preference would be to use TaxCycle if they ever offer this option.
We do not offer pay-per-return pricing.
What about the GIFI numbers, they don't match my COA.
It depends on the software you use, but usually can assign or link your existing COA accounts to specific GIFI codes.
Is TaxCycle cloud based?
It is desktop software. We are in the process of exploring cloud-based tools for file storage, client management and workflow. Keep watching our news.
I already have RC59 online access but I understand there is a third level.  How do I get level three?  I want to file the T2 return so I still need authorization.  Is there an ordering involved with a current client file that has a current T2 filing?
If a preparer already has authorization for online access with a client, the preparer can upgrade the level of authorization from level 1/2 to 3 by transmitting the RC59 from TaxCycle. Nothing prevents the preparers from resubmitting RC59. Select Level 3 authorization in Part 4 of the RC59OnlineConsent form. (Note that Level 3 is only allowed if you enter RepID in Part 2 of the form).
 
Ideally, you should obtain RC59 consent before preparing the tax return so that you can verify information the CRA has on file, such as losses, etc. Filing the RC59 through TaxCycle provides instant access to the business account and T2 Auto-fill downloads.
Is document linking only via drag-and-drop, or are other methods available?
It’s only available via drag and drop right now. It is on the list to eventually expose the feature elsewhere, like in the right-click menu and via keyboard shortcut.