This document will provide samples of reports built using the excel add-in link for IBM Cognos Controller.
IBM Cognos Controller
Exclusions and Exceptions
There are no known exceptions and exclusions at the time this document was created.
Income Statement Report Sample
Income Statement Report
The income statement report is that can be built using the excel add-in link. However, you need to understand that IBM Cognos Controller stores information as a year-to-date and needs to be broken down into its monthly amount or even quarterly. The important aspect of the report will be the accounts and periods/actualities.
Axis Definition – This is used to load information by rows and columns.
Figure 1 shows the defining of the row and columns
Row Definition – Profit and loss accounts are used as the row definition.
Figure 2 shows the insertion of the accounts. The profit and loss accounts are included in the row definition.
Column Definition is using the time periods. This is where you need to look at what you want to include in the report, i.e. 1 month, quarterly, YTD or Budget and Actual Information.
Figure 3 shows the time period. The report definition is actually doing the last three months with a year-to-date for actual and budget.
Figure 4 – Sample Income Statement Report
Hint: IBM Cognos Controller has certain information that is retrieve from the IBM Controller database. The information may not be in a format that is suitable for reporting, so you may need to use multiple sheets and excel commands to format the information in the desired format.
Figure 5 shows the spreadsheet tabs
- You may want to convert the date from the IBM Cognos Controller format 0812 to something that makes more sense.
- Currency reference may also need to be converted and formatted in the reporting.
- You can modify the column headings.
- The first sheet brings in the accounts. It is good to use multiple sheets to report different views of the data.
- The key is once the report is pulled into Excel then you have the options to used excel commands to format and build report to your specifications.
Balance Sheet Report Sample
Balance Sheet Report
The Balance Sheet can be built using the excel add-in link. The Balance Sheet report is reflected as at certain period of time.
Axis Definition – Accounts should be included in row definition and the period/actuality in the column definition.
Figure 6 shows the the definition of the report axis
Row Definition – The accounts can be pulled into the general ledger or you can use a Balance sheet form. This example will pull the accounts into the report.
Figure 7 shows the account definition for the Balance Sheet Report
Hint: It is recommended to pull in all the accounts to build the balance sheet report. You may want to do ratios/metrics as part of the report building.
Column Definition - The column definition is where you will define the periods and actualities to be used in the report.
Figure 8 shows the column definition for the Balance Sheet Report
Figure 9 shows Balance Sheet Report
Figure 10 shows the worksheets that can be used as part of formatting and/or for pulling the information together
Date Conversion Sheet: You can create a spreadsheet that converts the IBM Cognos Controller date format to more desirable format. This conversion of the date can done using concatenate excel function along with column references.
Figure 11 shows date conversion spreadsheet
Analysis and Commentary
Analysis and other reporting requirements
The document discussed earlier about pulling all the account information into one spreadsheet and then building reports from this information. The account structures will allow you to build income statement and/or balance sheet reports. You may also build other reports in excel like cash flow if you track the movements of specific accounts, like fixed assets, share capital, loans, etc. The movement accounts can be integrated and/or non-integrated. However, if you are using non-integrated accounts then you will need to ensure you have a reconciliation process between the real account and the statistical account. The following example will show how to build some additional reports and analysis using the excel add-in link.
Figure 12 shows the tabs for building additional reports and analysis
The axis definition will be same, i.e. Accounts (rows) and Period/Actuality (columns). If you want to use the balance sheet and income statement account information in the same the file then you will need to define the periods correctly in column data pulled.
Balance sheet for the period should reflect as the YTD.
Figure 13 shows the column definition for the balance sheet accounts
The income statement account usually want to be reflect by their monthly total or by the year-to-date. So you may have to create column identifier for the income statement period information (i.e. Monthly amounts).
Figure 14 shows the column definition for Income Statement accounts
If you pull in all the accounts:
- Income Statement
- Balance Sheet Accounts
- Movement accounts/Extension
- Statistical accounts (headcount, Production stats, days, etc)
You can build reports based on the above information, because you are only pulling information from IBM Cognos Controller via the initial data pull. You are using excel formulas, formatting, design, etc to build reports to your own specification.
Cash Flow Schedule
You can create cash flow schedule using the account structures or you can build a report in the excel link based requirement information. Listed below is a sample Cash Flow Report.
Figure 15 shows sample of cash flow schedule that can be built in IBM Cognos Controller
You can build ratios and/or analysis by pulling information from the accounts, statements within the same excel file.
Figure 16 shows some analysis that can be done based on the accounts
Hint: You can recycle some analysis and other information in other reports within the excel linked report.
Building Reports that used Dimension
The dimensional level of detail in IBM Cognos Controller can be used for additional analysis. However, you may need to build separate reports to include all those accounts that have dimensional level of detail, i.e. Revenue, Cost of Goods Sold, etc.
There are four main dimension in IBM Cognos Controller called Dim1, Dim2, Dim3 and Dim4. So, you will want to review what dimension are active and for what account.
Figure 17 shows the extended dimension structure settings
Figure 18 shows the account structure and the dimensional levels
Hint: You will need to understand the level of detail for each account and how that relates to the dimension code.
Figure 19 shows reporting with dimension in column and the accounts in the rows
- Axis – Rows (Accounts) and Columns (Dim 2 – Product)
- Row only includes accounts with dimensionality
- Column brings in the complete product dimension
Hint: You may want to review the report design based on either # of accounts and/or dimension being reported.
Figure 20 shows the excel add-in tabs
You can create a data, currency and company tab to modify those headings.
Variance Analysis Reports
Building Reports that show variance analysis
Once you have pulled in the account and period/actuality information into excel sheet then you can do a budget variance analysis. The budget variance analysis column can be set up on how you review the budget variances.
Figure 21 shows the Income Statement with the Budget Variance
Hint: You can us existing reports or create a separate variance report. It just depends on your preference and management requirements.
Figure 22 shows the tabs to support the income statement and variance report
|Article contained in ZIP file||Samples_of_Report_Building_using_the_Excel_add-in_Link_for_IBM_Cognos_Controller.zip||1453KB|
Dig deeper into Business analytics on developerWorks
Experiment with new directions in software development.
Tips for improving outcome and controlling risk.
Software development in the cloud. Register today and get free private projects through 2014.
Evaluate IBM software and solutions, and transform challenges into opportunities.