150 likes | 1.44k Views
The Hints and Tips. Improve Data QualityUse Advanced Validations techniqueUse of Controller reconcile function via calculation reports and conditional logic (IF statements) in ExcelSpeed up Admin TaskFast set up of periods for new actuality or a new yearStandard admin taskUser Friendly FormsWorks with linked structures
E N D
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.