BigQueryを使った財務分析の実装 - Day 3
はじめに
前回構築したBigQuery環境を使用して、財務データの分析を実装していきます。今回は部門別収支、月次推移、費用分析を行い、事業の収益構造を明らかにしていきます。
1. データの概要
テストデータの特徴
期間：2023年4月から2024年3月までの1年分
部門：営業1部、営業2部、人事部、総務部、開発部
主要勘定科目
収益：売上高（5111）
原価：売上原価（5511）
費用：給与手当（6111）、旅費交通費（6112）、消耗品費（6113）、地代家賃（6114）
詳しくは、最後にデータ作成クエリを記載していますので、そちらでご確認ください。
2. 部門別収支分析
実装したクエリ
from google.cloud import bigquery
from google.oauth2 import service_account
import pandas as pd
# BigQueryクライアントの初期化
credentials = service_account.Credentials.from_service_account_file('path/to/your-key.json')
client = bigquery.Client(credentials=credentials, project='taki-sample001-202402')
query = """
WITH ProfitLoss AS (
SELECT
department_name,
-- 収益は貸方（credit）がプラス
SUM(CASE
WHEN credit_account LIKE '5%' THEN amount
WHEN debit_account LIKE '5%' THEN -amount
ELSE 0
END) as revenue,
-- 費用は借方（debit）がマイナス（PLの観点）
SUM(CASE
WHEN debit_account LIKE '6%' THEN -amount
WHEN credit_account LIKE '6%' THEN amount
ELSE 0
END) as expenses
FROM `accounting.journal_entries`
GROUP BY department_name
)
SELECT
department_name,
ROUND(revenue, 2) as revenue,
ROUND(expenses, 2) as expenses,
ROUND(revenue + expenses, 2) as profit_loss,
CASE
WHEN revenue > 0 THEN ROUND((-expenses / revenue) * 100, 1)
ELSE NULL
END as expense_ratio
FROM ProfitLoss
ORDER BY profit_loss DESC;
"""
df_pl = client.query(query).to_dataframe()
print(df_pl) 分析結果
部門別の収支状況
収益部門
営業2部：収益46.2M、利益9.2M、経費率80.1%
営業1部：収益46.8M、利益7.4M、経費率84.2%
両部門とも適切な利益率を維持
コストセンター
総務部：支出56.9M（固定費中心）
開発部：支出49.4M（人件費中心）
人事部：支出19.4M（最小規模）
3. 月次推移分析
実装したクエリと可視化
import matplotlib.pyplot as plt
import matplotlib.font_manager as fm
# 日本語対応フォントの設定
jp_font_path = "C:/Windows/Fonts/msgothic.ttc"
jp_font = fm.FontProperties(fname=jp_font_path)
query_monthly = """
SELECT
DATE_TRUNC(accounting_date, MONTH) as month,
department_name,
SUM(CASE
WHEN credit_account LIKE '5%' THEN amount
WHEN debit_account LIKE '5%' THEN -amount
ELSE 0
END) as revenue,
SUM(CASE
WHEN debit_account LIKE '6%' THEN -amount
WHEN credit_account LIKE '6%' THEN amount
ELSE 0
END) as expenses
FROM `accounting.journal_entries`
GROUP BY month, department_name
ORDER BY month, department_name
"""
df_monthly = client.query(query_monthly).to_dataframe()
# グラフ作成
plt.figure(figsize=(12, 6))
for dept in df_monthly['department_name'].unique():
dept_data = df_monthly[df_monthly['department_name'] == dept]
plt.plot(dept_data['month'],
dept_data['revenue'] + dept_data['expenses'],
label=dept, marker='o')
plt.title('Monthly Profit/Loss by Department', fontproperties=jp_font)
plt.xlabel('Month', fontproperties=jp_font)
plt.ylabel('Amount', fontproperties=jp_font)
plt.legend(prop=jp_font)
plt.grid(True)
plt.xticks(rotation=45, fontproperties=jp_font)
plt.tight_layout()
plt.show()
月次分析結果
営業部門の推移
月次利益は0.5M～1.5M円で変動
2024年1月に最高益（約1.5M円）
営業2部の方が安定的に推移
コストセンターの推移
人事部：約-1.6M円で安定
開発部：約-4.1M円で安定
総務部：-4.5M～-6M円で変動（12月に大きな支出）
季節性の特徴
第4四半期に営業部門の収益増加
年末年始に総務部のコスト増加
まとめと考察
収益性分析
全社業績
年間売上高：264.3M円
売上原価：171.3M円（原価率：64.8%）
売上総利益：93.0M円（粗利率：35.2%）
販管費合計：202.0M円
給与手当：159.6M円
地代家賃：30.0M円
旅費交通費：7.3M円
消耗品費：5.1M円
営業利益：-109.0M円
費用構造の特徴
固定費が大半（給与手当と地代家賃で189.6M円）
売上高に対する人件費率：60.4%
変動費（旅費交通費、消耗品費）は比較的少額
次のステップ
明日は、より実践的なデータ活用のために
SQLServerからのデータ移行手法
文字コード対応
データクレンジング の実装に取り組みます。
データ作成用のクエリ
-- テストデータの作成
INSERT INTO `accounting.journal_entries`
WITH
dates AS (
SELECT date FROM UNNEST(GENERATE_DATE_ARRAY('2023-04-01', '2024-03-31', INTERVAL 1 MONTH)) as date
),
departments AS (
SELECT * FROM UNNEST([
STRUCT('SALES01' as code, '営業1部' as name),
STRUCT('SALES02' as code, '営業2部' as name),
STRUCT('HR001' as code, '人事部' as name),
STRUCT('ADMIN01' as code, '総務部' as name),
STRUCT('DEV01' as code, '開発部' as name)
])
),
-- 売上データ生成（営業1部、営業2部）
sales_entries AS (
SELECT
'J' || FORMAT_DATE('%Y%m%d', d.date) || '-S-' || dept.code as journal_id,
d.date as entry_date,
d.date as accounting_date,
'1131' as debit_account,
'普通預金' as debit_account_name,
'5111' as credit_account,
'売上高' as credit_account_name,
CAST(
CASE
WHEN EXTRACT(MONTH FROM d.date) IN (1, 2, 3) THEN 12000000 + CAST(FLOOR(1000000 * RAND()) AS INT64)
ELSE 10000000 + CAST(FLOOR(1000000 * RAND()) AS INT64)
END
AS NUMERIC) as amount,
dept.code as department_code,
dept.name as department_name,
'売上計上' as description,
CURRENT_TIMESTAMP() as created_at,
CURRENT_TIMESTAMP() as updated_at
FROM dates d
CROSS JOIN (SELECT * FROM departments WHERE code IN ('SALES01', 'SALES02')) dept
),
-- 売上原価データ生成
cogs_entries AS (
SELECT
'J' || FORMAT_DATE('%Y%m%d', s.entry_date) || '-C-' || s.department_code as journal_id,
s.entry_date,
s.accounting_date,
'5511' as debit_account,
'売上原価' as debit_account_name,
'1131' as credit_account,
'普通預金' as credit_account_name,
CAST(FLOOR(s.amount * 0.65) AS NUMERIC) as amount, -- 売上の65%を原価に
s.department_code,
s.department_name,
'売上原価計上' as description,
CURRENT_TIMESTAMP() as created_at,
CURRENT_TIMESTAMP() as updated_at
FROM sales_entries s
),
-- 給与データ生成
salary_entries AS (
SELECT
'J' || FORMAT_DATE('%Y%m%d', d.date) || '-P-' || dept.code as journal_id,
d.date as entry_date,
d.date as accounting_date,
'6111' as debit_account,
'給与手当' as debit_account_name,
'1131' as credit_account,
'普通預金' as credit_account_name,
CAST(
CASE
WHEN dept.code = 'SALES01' THEN 3000000
WHEN dept.code = 'SALES02' THEN 2800000
WHEN dept.code = 'DEV01' THEN 4000000
WHEN dept.code = 'HR001' THEN 1500000
ELSE 2000000
END
AS NUMERIC) as amount,
dept.code as department_code,
dept.name as department_name,
'給与支払' as description,
CURRENT_TIMESTAMP() as created_at,
CURRENT_TIMESTAMP() as updated_at
FROM dates d
CROSS JOIN departments dept
),
-- 旅費交通費データ生成
travel_entries AS (
SELECT
'J' || FORMAT_DATE('%Y%m%d', d.date) || '-T-' || dept.code as journal_id,
d.date as entry_date,
d.date as accounting_date,
'6112' as debit_account,
'旅費交通費' as debit_account_name,
'1131' as credit_account,
'普通預金' as credit_account_name,
CAST(
CASE
WHEN dept.code LIKE 'SALES%' THEN 200000 + CAST(FLOOR(50000 * RAND()) AS INT64)
ELSE 50000 + CAST(FLOOR(10000 * RAND()) AS INT64)
END
AS NUMERIC) as amount,
dept.code as department_code,
dept.name as department_name,
'旅費精算' as description,
CURRENT_TIMESTAMP() as created_at,
CURRENT_TIMESTAMP() as updated_at
FROM dates d
CROSS JOIN departments dept
),
-- 消耗品費データ生成
supplies_entries AS (
SELECT
'J' || FORMAT_DATE('%Y%m%d', d.date) || '-O-' || dept.code as journal_id,
d.date as entry_date,
d.date as accounting_date,
'6113' as debit_account,
'消耗品費' as debit_account_name,
'1131' as credit_account,
'普通預金' as credit_account_name,
CAST(50000 + CAST(FLOOR(20000 * RAND()) AS INT64) AS NUMERIC) as amount,
dept.code as department_code,
dept.name as department_name,
'消耗品購入' as description,
CURRENT_TIMESTAMP() as created_at,
CURRENT_TIMESTAMP() as updated_at
FROM dates d
CROSS JOIN departments dept
),
-- 地代家賃データ生成
rent_entries AS (
SELECT
'J' || FORMAT_DATE('%Y%m%d', d.date) || '-R-ADMIN' as journal_id,
d.date as entry_date,
d.date as accounting_date,
'6114' as debit_account,
'地代家賃' as debit_account_name,
'1131' as credit_account,
'普通預金' as credit_account_name,
CAST(2500000 AS NUMERIC) as amount,
'ADMIN01' as department_code,
'総務部' as department_name,
'オフィス賃料' as description,
CURRENT_TIMESTAMP() as created_at,
CURRENT_TIMESTAMP() as updated_at
FROM dates d
),
-- 異常値データ
anomaly_entries AS (
SELECT
'J20231225-ANOMALY' as journal_id,
DATE '2023-12-25' as entry_date,
DATE '2023-12-25' as accounting_date,
'6113' as debit_account,
'消耗品費' as debit_account_name,
'1131' as credit_account,
'普通預金' as credit_account_name,
CAST(1500000 AS NUMERIC) as amount,
'ADMIN01' as department_code,
'総務部' as department_name,
'年末大掃除用品' as description,
CURRENT_TIMESTAMP() as created_at,
CURRENT_TIMESTAMP() as updated_at
)
SELECT * FROM sales_entries
UNION ALL SELECT * FROM cogs_entries
UNION ALL SELECT * FROM salary_entries
UNION ALL SELECT * FROM travel_entries
UNION ALL SELECT * FROM supplies_entries
UNION ALL SELECT * FROM rent_entries
UNION ALL SELECT * FROM anomaly_entries;#Python #BigQuery #財務分析 #AdventCalendar