caseware working papers...build formulas with linkage to caseware working papers • use the drill...

CaseWare Working Papers Connector user manual

Index Introduction to CaseWare Connector .......................................................................................... 3

Objective ........................................................................................................................................................................ 3 What is Connector? ........................................................................................................................................................ 3 Where can I use CaseWare Connector? ......................................................................................................................... 3 Using CaseWare Connector ........................................................................................................................................... 3

Getting Started .......................................................................................................................................................... 3 Navigating CaseWare Connector .............................................................................................................................. 4

Connector Linkage ...................................................................................................................... 4

Objective ........................................................................................................................................................................ 4 Accessing the Connector Linkage dialogue .................................................................................................................... 4

Linking Engagement Properties ................................................................................................................................ 6 Linking the Accounts ................................................................................................................................................. 7 Year to Date .............................................................................................................................................................. 9 Current Active Period ................................................................................................................................................ 9 Specific Period (“hard code”) .................................................................................................................................. 10 Monthly 11 Current Active Period .............................................................................................................................................. 11 Specific Period (“hard code”) .................................................................................................................................. 12 Link to Mapping....................................................................................................................................................... 13 Link to Consolidated Entities ................................................................................................................................... 15 Context Menu ......................................................................................................................................................... 15 Specific Entity (“hard coded”) ................................................................................................................................. 16

Other CaseWare Connector Functions ...................................................................................... 16

Objective ...................................................................................................................................................................... 16 Document References .................................................................................................................................................. 16 Tick marks .................................................................................................................................................................... 17 Drill Down .................................................................................................................................................................... 17 Recalculating Formulas ................................................................................................................................................ 18 Unlinking and Relinking Connector Formulas .............................................................................................................. 18

Hot Tricks for CaseWare Connector .......................................................................................... 20

Objective ...................................................................................................................................................................... 20 Quick Copy of Account/Mapping Numbers ................................................................................................................. 20 Find and Replace .......................................................................................................................................................... 22 Non-Financial Data ....................................................................................................................................................... 24

Importing Performance Measures .......................................................................................................................... 25 Mapping the Accounts ............................................................................................................................................ 27 Viewing the Trial Balance ........................................................................................................................................ 29 Linking Performance Measures Using CaseWare Connector .................................................................................. 29

Basic CaseWare Connector Formulae ........................................................................................ 30

Introduction to CaseWare Connector

Objective

After attending this course, the user will be able to:

• Build formulas with linkage to CaseWare Working Papers • Use the drill down feature to drill down a particular cell to view the Working Trial Balance • Use the electronic tick marks and document links to reference from Excel/Word to

CaseWare Working Papers • Automate non-financial information from CaseWare Working Papers using

Performance Measures • Create consolidated reports linking them to CaseWare Working Papers

What is Connector?

CaseWare Connector is an optional component for CaseWare Working Papers, which allows the user to integrate CaseWare engagement file data to MS Excel workbooks and MS Word documents. Changes in the underlying CaseWare data are immediately reflected in any linked Excel or Word schedules. In addition to integrating data with any part of CaseWare Working Papers' database, including all amounts and properties of accounts, the user can add full annotation including referencing and tick marks and drill down to supporting documents.

Where can I use CaseWare Connector? It has a huge range of accounting applications, including management accounting, analytical review, graphical presentation and the automation of audit working papers.

Using CaseWare Connector

Getting Started

CaseWare Connector needs to be installed first. Please follow the installation instructions as shown on the CaseWare Africa Success Community.

https://success.casewareafrica.com/

After installation, the will see the Connector functions under the Add-ins tab on the MS Word and MS Excel toolbar.

Navigating CaseWare Connector

• CaseWare Linkage: This enables the user to use formulas

and values that can be linked from CaseWare Working Papers to MS Excel or MS Word.

• Document: The user is able to reference to documents saved in the Document Manager from MS Excel and MS Word.

• Tick mark: The user can access the electronic tick marks

from CaseWare Working Papers.

• Allows the user multiple functions relating to Connector such as unlinking, relinking, recalculating, and drill downs, etc.

• Help: A help guide on CaseWare Connector is available

Connector Linkage

Objective In this module the user will be able to:

• Create and insert formulas using the CaseWare Connector Linkage tool. This will enable the user to automate reports by pulling the information from an existing CaseWare client file into MS Excel or MS Word.

Accessing the Connector Linkage dialogue

To access the Connector Linkage dialogue click on the icon on the Add-ins tab in Excel or Word.

Using the Category tabs, the user will click the information that they wish to link from CaseWare Working Papers.

• Engagement Properties: Indicates the information that has been completed under the Engagement Properties dialogue in CaseWare Working Papers. The operating name, period begin/end, year-end dates, client address, etc. can be automated using connector should they exist in the client file.

• Document Information: This gives the user a complete view of the documents that exist in the Document Manager.

• User Defined: Displays the User defined Data, as entered through the Tools | User defined Data dialogue in CaseView.

• Class Type: Indicates the information relating to the ratio classes that exists within CaseWare Working Papers. Only the class type values can be linked. The CaseWare Help (F1) document provides the user with a summary of the Class type formulas needed to create a Ratio analysis worksheet/summary.

• Accounts: This enables the user to be able to create links to the trial balance accounts in CaseWare Working Papers.

• Mapping: In addition to linking to account numbers, the user also has the ability to link the Mapping numbers/Mapping groups within the CaseWare Working Papers file.

• Lead Sheet and Groupings: Information relating to the Lead Sheet and Groups 2-10 can be linked; the description and values can be populated.

• Tax Export Codes: Tax code descriptions and values can be linked from CaseWare Working Papers.

• CaseView Data: The CaseView database information can also be linked from CaseWare Working Papers. Values are linked based on their Group/Form/ID attributes as in CaseView.

Linking Engagement Properties Users can automate the engagement properties within a MS Word or MS Excel document. To link the relevant value in MS Excel

• Copy the required Excel Workbook into the CaseWare client file and open it. • Place the cursor on the designated Cell (in our example it will be Cell A1).

• Click the icon to view the CaseWare Connector dialogue. • Click the Engagement Properties tab on the left.

• From the data in the right-hand pane, click the value to be linked.

• The linked formula will appear at the bottom of the screen. Click the icon to accept the value clickion.

• The value will be added to the clicked cell and the formula can be viewed in the formula bar.

• If the operating name changes within the Engagement Properties, the existing Connector document can be recalculated to update the workbook with the changes.

• To recalculate the document go to Tools | Recalculate. The value in Cell A1 changes but the formula remains the same.

Linking the Accounts

The user can create various schedules whereby they can link the schedules to specific account numbers within their trial balance. • Copy the required Excel Workbook into the CaseWare client file and open it. • Place the cursor on the designated Cell (in our example it will be Cell A1).

• Click the icon to view the CaseWare Connector dialogue. Click the Accounts tab on the left.

• From the data in the right-hand pane, click the item to be linked. In our example, we will be linking the account description.

• The linked formula will appear at the bottom of the screen. Click the icon to accept the value clickion (the value of the formula can be seen to the right of the formula field).


• The user can continue to link the accounts manually to the list should they be more comfortable with this. The more advanced users can copy the existing formula and replace the account number in the formula with the new account number being careful that the inverted commas (“”) are not removed.

After the linking the description, the actual and budget values can be linked as per the example below. The user can determine the automated monthly movement as well as the year to date for the particular line item permitted that the trial balance has been imported on a monthly basis and that the movements were imported into each period.

Formulas can be hard coded, meaning that the user can specify the exact period that they want the formula to show. This will not be affected by the current active period clicked in the Engagement Properties dialogue. Alternatively, should the user wish the value to read from the current active period the linkage formula will be different. This will be specified by making use of the drop-down menus located above the links pane.

• Default Entity: Enables the user to click a specific entity within a consolidation should the user wish to hard code the entity into the formula. By leaving the field on the Default Entity, the value of the formulas will be determined by which entity is clicked in the Context menu within CaseWare Working Papers.

• Default Period: Enables the user to click a specific period should the user wish to hard code the period into the formula. By leaving the field on the Default Period, the value of the formulas will be determined by the current active period clicked within CaseWare Working Papers.

• Current Year: This allows the user to specify which year’s balances to show.

• Current Format: The user can choose what format the balances or dates should be displayed in. This will override the current formatting set in MS Excel.

• Report Balance: The user can click which balance type should be displayed by specifying the required balance type from the drop-down menu.

• N/A: The user clicks whether they want to view the actual balances (N/A), the budget balances if imported or the forecast figures if imported.

• This indicates the period balances. The user will click this option should they only wish to display the monthly movements for a particular period.

• Should the user wish to apply the foreign exchange (completed in the Working Trial Balance) they need to click this option.

• Rounding: The user can click to round the account values should this be required.

• Zero lines can be hidden for convenience when searching for values to link.

Both methods for actuals and budgets monthly vs. actuals and

budgets year to date are discussed below:

Year to Date

Current Active Period

• Place the cursor on the designated Cell (in our example it will be Cell E7).

• Click the relevant settings from the drop-down menus:

• Click the value that should be linked.

• The linked formula will appear at the bottom of the screen. Click the icon to accept the value clickion (the value of the formula can be seen to the right of the formula field).


• The user can continue to link the account values manually to the list

should they be more comfortable with this. The more advanced users can copy the existing formula and replace the account number in the formula with the new account number being careful that the inverted commas (“”) aren’t removed. Budget balances are linked in the same way as the actuals. The main differences are the balance type:

As a result, the formula will differ to that of the actual balances linkage.

Specific Period (“hard code”)

• Place the cursor on the designated Cell (in our example it will be Cell E7). Click the relevant settings from the drop-down menus:

• Note: Period has been specified as 30 June 2011.

Click the value that should be linked.

Note: The value for 30 June 2011 is now displayed. The linked formula will appear at the bottom of the screen.

Click the checkmark icon to accept the value clickion (the value of the formula can be seen to the right of the formula field).

• Note: The formula for the specific period method looks significantly different to that of the Current Active Period method.


The user can continue to link the account values manually to the list should they be more comfortable with this. The more advanced users can copy the existing formula and replace the account number in the formula with the new account number being careful that the inverted commas (“”) aren’t removed. Budget balances are linked in the same way as the actuals. The main differences are the balance type:

As a result, the formula will differ to that of the actual balances linkage.

Monthly

Current Active Period

• Place the cursor on the designated Cell (in our example it will be Cell B7).

Click the relevant settings from the drop-down menus:

• Click the value that should be linked.

• The linked formula will appear at the bottom of the screen. Click the checkmark icon to accept the value (the value of the formula can

• be seen to the right of the formula field).


The user can continue to link the account values manually to the list should they be more comfortable with this. The more advanced users can copy the existing formula and replace the account number in the formula with the new account number being careful that the inverted commas (“”) aren’t removed. Budget balances are linked in the same way as the actuals. The main differences are the balance type:

As a result the formula will differ to that of the actual balances linkage.

Specific Period (“hard code”)

• Place the cursor on the designated Cell (in our example it will be Cell B7).

Click the relevant settings from the drop-down menus:

Click the value that should be linked.

The linked formula will appear at the bottom of the screen. Click the checkmark icon to accept the value clickion (the value of the formula can be seen to the right of the formula field).

Note: The formula for the specific period method looks significantly different to that of the Current active period method.

The value will be added to the clicked cell and the formula can be viewed in the formula bar.

The user can continue to link the account values manually to the list should they be more comfortable with this. The more advanced users can copy the existing formula and replace the account number in the formula with the new account number being careful that the inverted commas (“”) aren’t removed. Link to Mapping

The user can create various schedules whereby they can link the schedules to specific mapping numbers or mapping groups within the CaseWare Working Papers file. The advantage to linking to mapping numbers is that the schedules can be used as a firm template which can be copied into multiple CaseWare client files.

• Copy the required Excel Workbook into the CaseWare client file and open it.

• Place the cursor on the designated Cell (in our example it will be Cell A1).

• Click the icon to view the CaseWare Connector dialogue. • Click the Mappings tab on the left.

• From the data in the right-hand pane, click the item to be linked. In our

example, we will be linking the Mapping description. There are two ways to link a mapping numbers:

• Method 1: Link to a specific mapping number (long map number). This will pull through a value for a specific individual map number.

• Method 2: Linking to a mapping group (shorter map numbers) to pull through the values of a certain mapping group. An asterisk (*) needs to be added after the mapping group number to display the values.

The linked formula will appear at the bottom of the screen.

Click the checkmark icon to accept the value clickion (the value of the formula can be seen to the right of the formula field).

• Method 1:

• Method 2:

The value will be added to the clicked cell and the formula can be viewed in the formula bar.

The user can continue to link the mapping manually to the list should they be more comfortable with this. The more advanced users can copy the existing formula and replace the mapping number in the formula with the new mapping number being careful that the inverted commas (“”) are not removed. After linking the description, the actual and budget values can be linked as per the example below. The user can determine the automated monthly movement as well as the year to date for the particular line item permitted that the trial balance has been imported on a monthly basis and that the movements were imported into each period. The user will follow the same steps as with the account linkage.

Link to Consolidated Entities

The user can create various consolidated schedules by making use of links to account numbers or mapping numbers/groups. The focus points will be whether the user wants to hard code the worksheet, that is link it to a specific entity and fix that entity within the file or whether the user wants to view the balances based on what was clicked from the context menu on the Working Trial Balance. Context Menu

• Ensure that the Entity drop-down menu is set to Default Entity. This will pick up the balances from whichever entity has been click from the context menu in the Working Trial balance.

• Values will change as the specified entity is clicked in the context menu.

Specific Entity (“hard coded”)

• By clicking the specific entity from the drop-down menu, the user

can hard code the clicked entity in the formula.

• The entity abbreviation will appear in the formula.

Other CaseWare Connector Functions

Objective

After completing this section, the user will be able to:

• Reference documents from Excel and Word • Use electronic tick marks • Drill down on specific linked items • Recalculate formulas • Unlink and relink connector formulas

Document References

Documents that exist in the Document Manager can be referenced to from MS Excel or MS Word. This creates quick jump links to the specific document.

• Copy the required Excel Workbook into the CaseWare client file and open it. • Place the cursor on the designated Cell (in our example it will be Cell A1). • Click Tools | Reference | Document

• A hyperlink will be created to jump to the specified document.

Tick marks

Users have access to the electronic tick marks located in CaseWare Working Papers. • Copy the required Excel Workbook into the CaseWare client file and open it. • Place the cursor on the designated Cell (in our example it will be Cell A1).

Click the green checkmark icon to view the CaseWare Tick mark dialogue.

• Click the relevant tick mark to be used. Click the relevant options below that should be applied.

The clicked tick mark will be added and can be dragged and dropped into position.

Drill Down

The user can drill down on a specific linked lined item, whether it is mapping or account. This will open up an automatic document with the breakdown of the mapping number/group and the account number.

• To perform a Drill Down click the relevant balance and go to the Tools menu under the Add- ins tab and click Drill Down.

• A Drill Down can also be performed by right-clicking on the relevant cell and clicking Connector | Drill Down.

Recalculating Formulas

The Connector worksheet can be recalculated at any time should a change occur to any of the balances in the CaseWare client file.

• This can be done by going to the Add-ins tab. Click the Tools | Recalculate option.

Note: When document is open when the change occurs, the user needs to manually recalculate the document by using the Tools | Recalculate option. Should the document be closed at the time of the change, MS Excel or MS Word will recalculate the document if the Calculation Options under the Formulas tab in MS Excel and MS Word has been set to Automatic.

Unlinking and Relinking Connector Formulas

Connector formulas can be unlinked. This converts Formulas to values, enabling the user to view the report outside CaseWare Working Papers without Connector. The Worksheet can be relinked in at any time to restore the formulas.

• To unlink the worksheet formulas, go to Tools | Unlink.

• CaseWare Connector will display a prompt to unlink all formulas. Click Yes to continue.

• To relink the formulas got to Tools | Relink.

• CaseWare Connector will display a prompt to restore formulas. Click Yes to continue.

Hot Tricks for CaseWare Connector

Objective

After completing this section, the user will be able to: • Copy account numbers from the working trial balance into a document

and reference the formula to a cell • Copy the content of an existing Connector data table into multiple sheets or columns • Automate non-financial data

Quick Copy of Account/Mapping Numbers Instead of linking the account/mapping values manually or replacing the account/mapping numbers manually in the formula (refer to CaseWare Connector Linkage), the user can copy the account numbers from the Working Trial Balance into the document and reference the formula to the cell. The advantage of doing this is that this only needs to be done on one formula. The cell can then be copied down. MS Excel should then autocomplete the reference in the new formulas.

• Highlight the required account numbers in the Working Trail Balance. Right-click on the highlighted area and from the menu and click Copy or alternatively use the CTRL+C shortcut.

• Copy the clicked account numbers into the workbook. This can be

pasted (CTRL+V) anywhere in the workbook.

• The user can create a Control Sheet that can be referenced to and hidden when the workbook has been completed.

• Go to the linked formula, in the formula, highlight the account number include the inverted commas (“”).

• Reference to the specific cell in the Control Sheet and click Enter.

• The formula will be adjusted in the actual data table. • Note: The same procedure can be followed with the account description.

• Click the original cell (with the reference Connector formula), click on the bottom right corner and drag the data down. The cells below will now reference to the next cells.

Find and Replace

There is no need to manually create/link the same Connector data tables for different entities or for different periods. The user can copy the content of an existing Connector data table into multiple sheets or columns and simply perform a find and replace (CTRL+H) to replace the existing reference with the required references. For the purposes of demonstrating this we are going to create a monthly Income and Expenditure Schedule with Actuals and Budget balances for different entities. We will be using the Link to mapping groups method.

• Open the workbook and set the sheet up as required.

• We will first complete the linkage for the first period. Linking to Holding Proprietary Limited.

• Pulling through the Actual period movements for the current year.

• The above step will be followed for each line item requiring automation. After completing the first column, the formulas can now be copied across to the rest of the period columns.

A find and replace (CTRL+H) can now be performed to update each period.

• Highlight the relevant column or data and press CTRL+H. The Find and Replace dialogue will open.

• The following component in the Connector formula needs to be replaced.

• Complete the Find and Replace dialogue as illustrated below:

• Follow this process for each period, replacing PER1 with the relevant period (PER3, PER4, etc.). The user might want to use the same schedule for multiple entities. If the original entity has been hard coded in the Connector formula, the procedure will be as follows:

• Copy the entire Connector data table into a separate sheet (based on the assumption that the user will have a separate MS Excel sheet for each entity).

• The following component in the Connector formula needs to be replaced.

• Highlight the relevant fields and click CTRL+H.

• Complete the Find and Replace dialogue as illustrated below:

Non-Financial Data

Non-financial data can also be automated using CaseWare Connector. This will be data such as employees’ data, debtors’ age analysis, stock information, etc. To be able to link the information, it needs to be imported into the CaseWare client file as Performance Measures as to not affect the financial data.

Importing Performance Measures

To import Performance Measures, a fixed format needs to be created. Like the trial balance, the Performance Measures sheet needs to have a fixed reference (similar to the account numbers of a regular trial balance). The values can be imported into the relevant period or into the last period depending on the frequency. Note: Performance Measures can be imported into IFRS, IFRS for SME, GRAP however mapping the Performance measures can only be done in files based on in GRAP.

• From the CaseWare Working Papers go to Engagement | Import | Excel File…

• The Excel File Import dialogue:

• Browse for the Excel File to import. • Click the relevant Worksheet to import from. • Click Next.

In the Specify data properties dialogue, click items to be imported.

Click Next.

In the Field Details dialogue, specify the field details.

Click Next.

In the Advanced Specifications dialogue, change the Default Account Type as Performance Measures.

Click Next. On the Completing the Excel Import Wizard the user can create an import

layout that serves as a template that can be used for the next imports as all the fields will already be assigned accordingly.

Click Next.

Mapping the Accounts

After importing, the user is required to map the Performance Measures accounts. Note: As mentioned before, this is only possible for files based on GRAP. This is not necessary for IFRS or IFRS for SME.

Go to Account | Assign Mapping Numbers.

Right-click anywhere on the mapping dialogue and click Show Performance Measures.

The Performance Measures account will appear on the left-hand side under Unmapped Accounts. The Performance Measures accounts can now be mapped to the relevant mapping number.

Click on Apply and OK.

Viewing the Trial Balance The Performance Measures can be viewed from the Working Trial Balance dialogue in CaseWare Working Papers. Click on the trial balance button or CTRL+W to view the Working Trial Balance. Click the Performance Measures tab.

Linking Performance Measures Using CaseWare Connector

Linking the Performance Measures Trial Balance using CaseWare Connector will occur in the same way as explained previously. The Performance Measures accounts can be found at the bottom of the trial balance under the Accounts tab (this is relevant for IFRS, IFRS for SME, and GRAP). Performance Measure mapping numbers GRAP) can be found under the Mappings tab on the CaseWare Connector Linkage dialogue. Accounts Tab

Mappings Tab

Basic CaseWare Connector Formulae The following reference table can be used in the CaseWare Connector application.

“BR” Balance Report

“BBR” Balance Budget Report

“BFR” Balance Forecast Report

cw_act Refers to a CaseWare account linkage

cw_map Refers to a CaseWare mapping linkage

cw_actdesc Refers to a CaseWare account description linkage

cw_mapdesc Refers to a CaseWare mapping description linkage

“M:PER1” Refers to Monthly period date sequence and reading period 1. This indicates the year-to-date balances of the specified period.

“M:PER1:DISC” Refers to Monthly period date sequence and reading period 1 but only the discreet movement for that period.

cw_clp Refers to linkage to the Engagement properties of the client file.

"HPL->” Refers to the Linkage of a specific entity within the CaseWare client file. This links to the entity abbreviation completed on the consolidations screen.

Contact Us

Main Contact Details

Adapt IT Reception +27 10 494 1000

Help Desk Direct Extension

E-mail CaseWare Support [email protected]

Other Important Details

Register at https://success.casewareafrica.com/ for easy access to frequently asked questions, upgrades, downloads and technical documentation.

Email us at [email protected] regarding:

Feedback Ideas on service or products Product information Account or license information Any other queries

Follow us

CaseWare Africa – South Africa




https://success.casewareafrica.com/

https://www.facebook.com/CaseWareSA/

https://twitter.com/CWA_SA

https://www.youtube.com/user/CQSTechnology

https://www.linkedin.com/company/58415/