損益計算書

損益計算レポートについて説明します。

損益計算書には、月次の詳細な売上とマイナス収益の内訳が示されます。売上、マイナス収益、経費、利益、および損失が表示されます。マイナス収益調整は総収入から差し引かれます。総収入にマイナス収益を適用すると、純利益になります。このレポートを使用して、実収入を把握し、マイナス収益項目を追跡する方法を決定します。

このレポートは、会計レポートページから複数のフォーマットオプションを使用してダウンロードできます。

Sigma での複製

収入レポートを Sigma で複製するには、revenue_recognition_debits_and_credits テーブルを使用します。

アカウントに分類

このサンプルクエリでは、2023 年 10 月の会計期間に計上され、アカウントごとに分類された売上のレポートが生成されます。目的の期間やさまざまなグループパラメーターに合わせて日付を調整することができます。

with 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,
    debit_account_type,
    credit_account_type,
    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,
    debit_account_type as account_type,
    decimalized_settlement_amount as raw_net_change,
    decimalized_presentment_amount as presentment_raw_net_change,
    *
  from
    formatted_changes
  union all
  select
    credit as account,
    credit_account_type as account_type,
    - 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_type in ('Assets', 'ContraRevenue', 'Expenses', 'Losses') then raw_net_change
        else - raw_net_change
      end
    ) as net_change,
    (
      case
        when account_type in ('Assets', 'ContraRevenue', 'Expenses', 'Losses') 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_type,
    account,
    booked_date,
    product_id,
    invoice_id,
    event_type,
    presentment_net_change,
    net_change
  from
    net_changes
  where
    account_type in ('Revenue', 'ContraRevenue', 'Expenses', 'Gains', 'Losses')
    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_type,
  account,
  sum(net_change * (case when account_type in ('Revenue', 'Gains') then 1 else -1 end)) as net_income,
  sum(presentment_net_change * (case when account_type in ('Revenue', 'Gains') then 1 else -1 end)) as presentment_net_income
from
  ungrouped_results
group by
  currency,
  presentment_currency,
  open_accounting_period,
  accounting_period,
  account_type,
  account

ラインアイテムごとに分類

このサンプルクエリでは、2024 年 2 月の会計期間に計上され、ラインアイテムごとに分類された売上のレポートが生成されます。目的の期間やさまざまなグループパラメーターに合わせて日付を調整することができます。

顧客の住所情報を取得するには、この Sigma クエリの結果を請求書の Sigma テーブルと結合します。

注

Stripe の勘定科目表のベータ機能を利用する場合は、総勘定元帳の勘定科目を反映するために、以下のクエリで必ず unbilled_ar_accounts のマッピングを更新してください。

select "accounting_period", "presentment_currency", "transaction_model_id", "account_type", "account", "booked_date", "presentment_net_income", "invoice_id", "line_item_id", "invoice_item_id", "charge_description", "invoice_line_item_description" from (
  select
  original.*
    , charges.description as charge_description
    , invoice_line_items.description as invoice_line_item_description
FROM
  (
    with unbilled_ar_accounts as (
      values 'UnbilledAccountsReceivable'
    )
  , formatted_changes as (
  select
    livemode,
    invoice_id,
    line_item_id,
    invoice_item_id,
    subscription_item_id,
    customer_balance_transaction_id,
    charge_id,
    refund_id,
    debit,
    credit,
    debit_account_type,
    credit_account_type,
    debit_gl_code,
    credit_gl_code,
    accounting_period_date,
    presentment_amount,
    presentment_currency,
    date_format(accounting_period_date, '%Y-%m-%d') as accounting_period,
    date_format(booked_date, '%Y-%m-%d') as booked_date,
    coalesce(
      invoice_id,
      invoice_item_id,
      charge_id,
      refund_id,
      subscription_item_id,
      customer_balance_transaction_id,
      customer_id
    ) as linked_invoice_id,
    coalesce(
      invoice_item_id,
      line_item_id,
      invoice_id,
      charge_id,
      refund_id,
      subscription_item_id,
      customer_balance_transaction_id,
      customer_id
    ) as linked_invoice_line_item_id
  ,
    if(lower(presentment_currency) in ('bif', 'clp', 'djf', 'gnf', 'jpy', 'kmf', 'krw', 'mga', 'pyg', 'rwf', 'vnd', 'vuv', 'xaf', 'xof', 'xpf'), cast(presentment_amount as decimal(18, 0)), cast(0.01 * presentment_amount as decimal(18, 2))) as decimalized_amount,
    case when debit in (select * from unbilled_ar_accounts) OR credit in (select * from unbilled_ar_accounts) then true else false end AS is_unbilled
  from revenue_recognition_debits_and_credits
)


, raw_net_changes as (
  select
    debit as account,
    debit_gl_code as gl_code,
    debit_account_type as account_type,
    decimalized_amount as presentment_raw_net_change,
    *
  from formatted_changes
  union all
  select
    credit as account,
    credit_gl_code as gl_code,
    credit_account_type as account_type,
    -decimalized_amount as presentment_raw_net_change,
    *
  from formatted_changes
)

, net_changes as (
  select
    (case when account_type in ('Assets', 'ContraRevenue', 'Expenses', 'Losses')
      then presentment_raw_net_change else -presentment_raw_net_change end) as presentment_net_change,
    *
  from raw_net_changes
)


, ungrouped_results as (
  select
    presentment_currency,
  	accounting_period,
    linked_invoice_line_item_id as transaction_model_id,
    account_type,
    account,
    gl_code,
    booked_date,
    invoice_id,
    line_item_id,
    invoice_item_id,
    charge_id,
    presentment_net_change
  from net_changes
  where
    account_type in ('Revenue', 'ContraRevenue', 'Expenses', 'Gains', 'Losses')
    and accounting_period >= date_format(timestamp '2024-02-01 00:00:00', '%Y-%m-%d')
    and accounting_period <= date_format(date_add('second', -1, timestamp '2024-03-01 00:00:00'), '%Y-%m-%d')
),
results as (
  select
    presentment_currency,
    accounting_period,
    transaction_model_id,
    account_type,
    account,
    gl_code,
    booked_date,
    arbitrary(invoice_id) as invoice_id,
    arbitrary(line_item_id) as invoice_line_item_id,
    arbitrary(invoice_item_id) as invoice_item_id,
    max(charge_id) as charge_id,
    sum(presentment_net_change * (case when account_type in ('Revenue', 'Gains') then 1 else -1 end)) as presentment_net_income
  from ungrouped_results
  group by presentment_currency, accounting_period, transaction_model_id, account_type, account, gl_code, booked_date
)

select
  accounting_period,
  presentment_currency,
  transaction_model_id,
  account_type,
  account,
  gl_code,
  booked_date,
  invoice_id,
  invoice_line_item_id as line_item_id,
  invoice_item_id,
  charge_id,
  presentment_net_income
from results
where
  presentment_net_income != 0

order by accounting_period, presentment_currency, transaction_model_id, account_type, account, gl_code, booked_date

  ) original
    left join charges on charges.id = original.charge_id
    left join invoices on original.invoice_id = invoices.id
    left join invoice_line_items on invoice_line_items.id = original.line_item_id

)