Income statement

Learn about the income statement report.

The income statement provides a detailed revenue and contra revenue breakdown by month. It shows revenue, contra revenue, expenses, gains, and losses. Contra revenue adjustments are deductions from gross revenue. Applying the contra revenue to your gross revenue results in your net income. Use this report to better understand your net revenue and determine how you want to track contra revenue items.

The report is available to download from our accounting reports page with multiple format options.

Replication in Sigma

To replicate the income report in Sigma, use the revenue_recognition_debits_and_credits table.

This sample query generates the report for revenue booked for the accounting period of October 2023 and grouped by account. You can adjust the dates to your desired time frame as well as different grouping parameters.

Note

If you’re using our chart of accounts beta feature, be sure to update the contra_revenue_accounts, income_statement_accounts, and net_changes_accounts mappings in the query below to reflect the accounts in your general ledger.

with contra_revenue_accounts as (
  values
    'Refunds',
    'Disputes',
    'CreditNotes',
    'BadDebt',
    'Voids',
    'UnbilledVoids',
    'Transfer',
    'Discounts'
),
income_statement_accounts as (
  values
    'Revenue',
    'Refunds',
    'Disputes',
    'BadDebt',
    'Voids',
    'UnbilledVoids',
    'CreditNotes',
    'Transfer',
    'ExternalAssetRefunds',
    'Discounts',
    'CustomerBalanceAdjustments',
    'ExternalCustomerBalanceAdjustments',
    'Underpayments',
    'Fees',
    'Recoverables',
    'Exclusion',
    'Overpayments',
    'FxLoss',
    'ConnectTransferLoss'
),
net_changes_accounts as (
  values
    'Revenue',
    'Recoverables',
    'Exclusion',
    'Overpayments'
),
zero_decimal_currencies as (
  values
    'bif',
    'clp',
    'djf',
    'gnf',
    'jpy',
    'kmf',
    'krw',
    'mga',
    'pyg',
    'rwf',
    'vnd',
    'vuv',
    'xaf',
    'xof',
    'xpf'
),
formatted_changes as (
  select
    booked_date,
    date_format(accounting_period_date, '%Y-%m-%d') as accounting_period,
    is_accounting_period_open,
    debit,
    credit,
    invoice_id,
    product_id,
    customer_balance_transaction_id,
    event_type,
    currency,
    presentment_currency,
    if(
      lower(currency) in (
        select
          *
        from
          zero_decimal_currencies
      ),
      cast(presentment_amount as decimal(18, 0)),
      cast(0.01 * presentment_amount as decimal(18, 2))
    ) as decimalized_presentment_amount,
    if(
      lower(currency) in (
        select
          *
        from
          zero_decimal_currencies
      ),
      cast(amount as decimal(18, 0)),
      cast(0.01 * amount as decimal(18, 2))
    ) as decimalized_settlement_amount
  from
    revenue_recognition_debits_and_credits
),
raw_net_changes as (
  select
    debit as account,
    decimalized_settlement_amount as raw_net_change,
    decimalized_presentment_amount as presentment_raw_net_change,
    *
  from
    formatted_changes
  union all
  select
    credit as account,
    - decimalized_settlement_amount as raw_net_change,
    - decimalized_presentment_amount as presentment_raw_net_change,
    *
  from
    formatted_changes
),
net_changes as (
  select
    (
      case
        when account in (
          select
            *
          from
            contra_revenue_accounts
        ) then raw_net_change
        else - raw_net_change
      end
    ) as net_change,
    (
      case
        when account in (
          select
            *
          from
            contra_revenue_accounts
        ) then presentment_raw_net_change
        else - presentment_raw_net_change
      end
    ) as presentment_net_change,
    *
  from
    raw_net_changes
),
ungrouped_results as (
  select
    case
      when is_accounting_period_open then accounting_period
      else null
    end as open_accounting_period,
    case
      when is_accounting_period_open then null
      else accounting_period
    end as accounting_period,
    currency,
    presentment_currency,
    account,
    booked_date,
    product_id,
    invoice_id,
    event_type,
    presentment_net_change,
    net_change
  from
    net_changes
  where
    account in (
      select
        *
      from
        income_statement_accounts
    )
    and accounting_period >= date_format(timestamp '2023-10-01 00:00:00', '%Y-%m-%d')
    and accounting_period <= date_format(
      date_add('second', -1, timestamp '2023-11-01 00:00:00'),
      '%Y-%m-%d'
    )
)
select
  currency,
  presentment_currency,
  open_accounting_period,
  accounting_period,
  account,
  sum(
    net_change * (
      case
        when account in (
          select
            *
          from
            net_changes_accounts
        ) then 1
        else -1
      end
    )
  ) as net_income,
  sum(
    presentment_net_change * (
      case
        when account in (
          select
            *
          from
            net_changes_accounts
        ) then 1
        else -1
      end
    )
  ) as presentment_net_income
from
  ungrouped_results
group by
  currency,
  presentment_currency,
  open_accounting_period,
  accounting_period,
  account