Income Statement Reporting Challenges with BI Tools

Greg Herrera
President and Co-Founder, Senturus, Inc.

Greg originally founded Senturus in 2001. He now heads sales, recruiting, marketing, human resources, and new ventures.

John Peterson
CEO and Co-Founder, Senturus, Inc.

John is the company’s thought leader and visionary. John directs the delivery of all projects with Senturus, providing the bridge of technical and business understanding.

Andrew Wyatt
Senior Managing Consultant, Senturus, Inc.

Income Statement Fundamentals: The Language and the Math

• The Basic Language of an Income Statement
  • The language of the income statement often varies by company.
  • The income statement is sometimes called the profit and loss statement (P&L), sometimes the statement of operations, and sometimes the statement of income.
  • It shows the profitability of the company for the period, on the bottom line.
• The Basic Math of an Income Statement
  • Net Profit = Total Income less Total Expenses
  • Income statement includes break-outs of the income and expenses associated with different types of activities (e.g. primary and secondary activities).
  • In the basic math, the income items are positive, the expense items are negative, and the totals and subtotals are summation roll-ups using straight addition.
    • Straight addition roll-ups are especially important in ad hoc reporting.
• The Basic Math with Budgets and Variances
  • Budget versus Actual (with Variance) is a very common business requirement, and must be supported in both standard and ad hoc reporting.
  • Business Users need the income statement to indicate whether the Variance is Favorable or Unfavorable.
  • In basic math, the Variance equation is always Actual minus Budget (A minus B).
  • In basic math, the positive numbers are the Favorable variances and the negative numbers are the Unfavorable variances.
  • In the rate cases where the business requirement accepts the mix of positive for income and negative for expense, you are almost done.

Challenge #1 Feeling Negative: Keeping It Positive Creates Challenges

• Keeping it Positive – Challenge 1A: No Rollups
  • As seen, the business requirements almost always mandate that both revenues and expenses be presented as positive values.
  • The major challenge of the “Keep it Positive” requirement is that it removes the ability to use straight addition for summation rollups. Hierarchical rollups no longer work, because those rollups rely on straight summation where the parent value equals the total of the child values. When all the values are positive, summations no longer hold, as additions are required in some places and subtractions in others.
  • This forces both report authors and ad hoc users to create calculated rows for each total and subtotal.
    • Requires tedious formula creation.
    • Not user friendly.
    • Prone to error.
    • Requires accounting knowledge.
• Keeping it Positive – Challenge 1B: Variance Formulas
  • The need to present both revenues and expenses as positive numbers forces variations of the variance formulas, which equates to four separate variations.
  • This forces both report authors and ad hoc users to figure out which of the four variance formulas to apply, then apply it manually to each row of the report.
    • Cannot call it ad hoc reporting when that is not the case.
    • Requires accounting knowledge that not all report developers have.
    • Prone to error.

Tip #1: Delay The Positive – Do the Math First, Then Reverse the Sign

• The Common Mistake
  • The roll-up and variance formula challenges are the result of using positive values for both revenue and expenses, and trying to accommodate via situation-specific formulas.
  • The challenging reports are either using:
    • A source database that maintains positive values for both revenue and expenses.
    • A technique that converts the negative values to positives.
  • Common Mistake
    • Causes the reporting nightmares.
    • Removes hope of reasonable ad-hoc analysis.
• Better Way: Leverage the Basic Math
  • The basic math enables consistent, correct:
    • Roll-ups at every level.
    • Representation of favorable and unfavorable variances.
  • The problem with the basic math is that it displays negative numbers when the business requires positive.
  • Solution: Have the reporting software use the basic math behind the scenes, and selectively reverse the sign only when the data is being displayed to the user
• Tip: Selectively Reverse the Sign via a Rule
  • This report illustrates the concept of the selective sign reversal.
  • The values in the Variance column already correctly represent favorable and unfavorable variances.
  • The positive numbers in the Actual and Budget columns are also already correct.
  • The numbers highlighted in blue are the ones that need their signs to be selectively reversed, after the basic math is complete.
• Tip: Use the Fundamental Accounting Elements
  • As a rule, once the basic math is complete, selectively reverse the sign when:
    • The value is in an account having Normal Balance Type of Debit, and the value is either an Actual measure or a Budget measure.
• Result: Correct Signs, Rollups, and Variances
  • Bonus: Works for ad hoc analysis as well.

Challenge #2: The Data is Almost Always Sore, and Needs Massaging

• The Basic Math Needs the Right Data
  • Sources of accounting data from which the Income Statement information is derived include:
    • General Ledger software module (or package).
    • Planning/Budgeting software packages.
    • Spreadsheet-based budget models.
    • Finance Marts or Data Warehouses.
    • Operational Data Stores (ODS).
    • Consolidation software packages.
    • Disclosure Management software packages.
  • Positives and negatives are not always maintained in the data values themselves.
  • The data needs to be organized and harmonized.
• Five Fundamental Accounting Examples
  • Element: Asset
    • Debit: Increase
    • Credit: Decrease
    • Example Accounts: Cash, Accounts Receivable, Inventory, Land, Furniture, Equipment, Patents, and Prepays.
  • Element: Liability
    • Debit: Decrease
    • Credit: Increase
    • Example Accounts: Accounts Payable, Salaries Payable, Prepayments, Income Taxes, etc.
  • Element: Income / Revenue
    • Debit: Decrease
    • Credit: Increase
    • Example Accounts: Sales, Interest Income, Rent Income, Interest from Investments, etc.
  • Element: Expense
    • Debit: Increase
    • Credit: Decrease
    • Example Accounts: Salaries, Rent, Repairs, Insurance, Utilities, and Telecommunications.
  • Element: Equity / Capital
    • Debit: Decrease
    • Credit: Increase
    • Example Accounts: Common Stock, Paid-in Capital, Retained Earnings.
• Organizing the Data: Scenario #1All Positive
  • Scenario #1: Source data holds both Debits and Credits as positive numbers.
    • Create derived Natural GL Amount field by multiplying Source Amount by -1 whenever the account has a normal balance type of Credit. When the normal balance type is Debit, the derived Natural GL Amount is the same as the Source Amount.
    • For use in the Basic Math approach, create a derived Reversed GL Amount field that is the reverse of Natural GL Amount.
• Organizing the Data: Scenario #2 Already Mixed
  • Scenario #2: Source data has Debits as positive and Credits as negative.
    • Derived Natural GL Amount field is the same as Source Amount.
    • For use in Basic Math approach, create derived Reversed GL Amount field that is the reverse of Natural GL Amount.
• Organizing the Data – Decompose Chart of Accounts
• Harmonizing the Data –Enables the Drillable P&L

Realizing the Potential: Income Statements with General-purpose IBM Cognos Business Intelligence

• Technique for Selective Sign Reversal in Cognos Transformer
• Settings for Measures: Actual and Budget
  • Make sure you source Actual and Budget measures from appropriate Reversed GL Amount field.
• Settings for Categories: Account Dimension
• Don’t Forget Mixed Rollup Accounts
• Actual and Budget Need to be Measures

Putting it to Work: Income Statement Demonstrations Using IBM Cognos Workspace Advanced

• Demonstrations
  • Row and Column Selection
  • Budget versus Actual
  • Relative Time and Comparisons
  • Expanding Specific Sections
  • Business Unit per Page
  • Asymmetrical Reporting
  • Seasonality
  • Conditional Highlighting
  • Actuals and Forecast by Month
  • Percent of Total
  • Drill-through to Transaction Detail