Hints and Tips Cognos Controller

1. Hints and Tips � Cognos Controller Nilesh Dodhia � Cognos Controller Consultant

2. The Hints and Tips Improve Data Quality Use Advanced Validations technique Use of Controller reconcile function via calculation reports and conditional logic (IF statements) in Excel Speed up Admin Task Fast set up of periods for new actuality or a new year Standard admin task User Friendly Forms Works with linked structures � especially company/Dim(x) Company specific items in rows/columns for any company

3. Advanced Validations - advantages Push data quality issues back to the units Reduce post data submission quality control burden on Head Office team Speed up consolidation process � reducing the need to correct data post submission from the units. Uses standard controller functionality

4. Advanced Validations � examples of use Ensure statistics are populated Check for bad debt provision percentages � do they agree to corporate standards? Ensure cashflow related accounts populated Can be used for �soft� and �hard� validations Use it for any business rule for which conditional logic can be built in Excel.

5. Advanced Validations - Steps Chart of Accounts setup: Set up Validation Reference Accounts (statistical) Set up a validation account for each advanced validation (calculated account) Create a Validations Calculations report. Include all accounts used as a reference source Program the validation calculation in the Excel layout of the calculation report

6. Chart of Accounts Insert a Validation Reference Account � say V0 This account will never have a value. (i.e. will be zero) Insert a Validation Reference Account � say VSum1 This accounts is a summation account. Sum key summation accounts like total debtors, total creditors, total equity etc. Insert a Validation Reference Account � say VCalcSum1 This account needs to be flagged as a calculation account and reconcile to VSum1 Insert Validation Reference Accounts � say V1 etc. Need one account per validation rule Flagged as calculation accounts Reconcile to V0

7. Program Validation Logic � part 1 Create a new Calculation Report with all the accounts needed for the validations logic. Ensure that the �Calculation Report� option is ticked. Open the Excel Layout and run the report to see the values. Insert formula for account VCalcSum1 = VSum1. Assign the value to VCalcSum1 via �cc.fcalcval� formula This step is required to force users to run the validation calculation report.

8. Program Validation Logic � part 2 Program validation logic for each validation account E.g If {Bad_Debt_provision/Debtors} >= 0.05 V1 = 0 Else V00001 = {Bad_Debt_provision} The Else value can be any non zero value � try to make it meaningful. Just use your Excel skill to program a business rule formula. Complex logic can be broken into several steps Use �cc.fcalcval� formula to assign value to V1 Insert Controller Calculation Account Formula for each Validation account. ?Hint � Format the report to show validation errors Include a note of forms and hints on where to fix the validations

9. Advanced Validations � Summary Chart of Accounts setup: Set up Validation Reference Accounts (statistical) Set up a validation account for each advanced validation (calculated account) Create a Validations Calculations report. Include all accounts used as a reference source Program the validation calculation in the Excel layout of the calculation report

10. What the user needs to do Complete the data input steps Run the calculations report via Menu : Group>Calculate Report Formulas and chose the validation report Correct any errors and rerun the calculation report Run Reconciliations � menu Company>Reconcile � Between accounts �� ?Hint � users can run Reconciliations as a first step to ensure all errors get corrected in fewer steps

11. Speed up an Admin Task � setting up periods Useful when: Setting up periods for a new actuality Setting up periods for a new year Setting up periods in a new submission Particularly useful for weekly periods actuality Only 2 Steps: Set up the first period , e.g. period 0110 Use the Expand button to create periods 0210 to 1210 (or 5210)

12. User Friendly Form with extended dimensions Example of use: Company uses Division/Department (Dep) codes for each P&L account. Assume Dep is part of Dimension 1 Dep codes are unique and specific to each company Linked structures have been used to ensure correct Company/Dep combinations are used. Requirement for a company user � to be able to see each Dep in its own column on a form

13. Steps to create a form Same simple steps: Create a new Form � make sure DIM1 data entry level number is populated For Column axis chose Dim1. For Row axis chose accounts Insert all valid accounts in Rows tab Insert all valid Dim1 values in Columns tab.