• #CF精算表
  • #在庫管理
  • #Python
  • #Excel自動化
  • #openpyxl
  • #簿記
  • #三分法
  • #フィードバック制御
開発eurekapu-nuxt4メモ

CF精算表: 運転資本への在庫管理追加と全ステップ完了

朝、運転資本（304）のStep Aを2022/01/01スタート・12月決算で再生成するところから始めた。タイトル行にFILL_MID_GRAY書式を適用し、MF仕訳シート・集約シート・資本政策シートを追加する。ここまでは前日の延長で手が動く。問題は在庫管理だった。「数量」という概念を取引モジュールに持ち込んだ瞬間、シート構造の前提が次々と崩れ、気づけば夕方まで列シフト・バグ修正・数式再設計に追われていた。

数量ベース生成モデルへの転換

商品パラメータの設計

在庫管理を組み込むにあたり、金額だけで取引を生成していたモデルを数量ベースに切り替えた。商品パラメータとしてリンゴを定義し、仕入単価からCOGS_RATEを逆算する構造にした。

NamedTupleに数量フィールドを追加し、売上データ・仕入データの各行が「何個売れたか」「何個仕入れたか」を持つようにした。

三分法の決算整理仕訳

期末の棚卸資産を正しく計上するため、三分法の決算整理仕訳を実装した。いわゆる「しーくりくりしー」の仕訳だ。

期首: 仕入 / 繰越商品（期首繰越商品を仕入勘定に振替）
期末: 繰越商品 / 仕入（期末繰越商品を資産計上）

期首と期末の棚卸高をパラメータから計算し、決算整理仕訳として自動挿入する処理を追加した。

新規シートの追加

商品台帳シートと在庫管理シートを新規に追加した。商品台帳は商品マスタとパラメータを一覧化し、在庫管理シートは月次の入出庫と残高を追跡する。

列シフト問題との格闘

何が起きたか

売上データシートと仕入データシートにB列として「数量」列を追加した。この1列の挿入が、下流のシート群を壊した。

売掛金管理・買掛金管理・クレカ決済シートがINDEX/MATCH数式で売上データ・仕入データを参照しているが、参照先の列番号がハードコードされていた。B列に数量が割り込んだことで、金額を取得しているつもりの列が1つずれ、数量の値を引いてしまう。

対応

INDEX/MATCHの列番号指定を全て洗い出し、数量列の挿入分だけオフセットを修正した。影響範囲は売掛金管理・買掛金管理・クレカ決済の3シート。列番号を定数化して、将来の列追加時にも1箇所の変更で済むようにした。

在庫が増え続けるバグ

症状

全シートを生成して在庫管理シートを開くと、月を追うごとに在庫残高が右肩上がりに膨れ上がっていた。4年目には在庫が初月の10倍近くまで積み上がり、明らかに実態と乖離している。

原因の特定

generate_daily_purchases関数のノイズ係数を確認すると、仕入数量に1.025を掛けていた。「売上より少し多めに仕入れる」意図だったが、販売実績と無関係に毎日一律で上乗せしているため、売れない分が毎月蓄積される。積み上がった在庫が消化される仕組みがどこにもない。

フィードバック制御への修正

仕入数量の決定ロジックを、販売実績に連動するフィードバック制御に書き換えた。

# 修正後: 販売数量ベースのフィードバック制御
shortage = max(0, target_stock - current_stock)
purchase_qty = daily_sales_qty + int(shortage * 0.2)
purchase_qty = int(purchase_qty * random.uniform(0.95, 1.05))

前日の販売数量をベースにし、目標在庫との差分（不足分）の20%を補充、そこに±5%のノイズを乗せる。在庫が十分あれば補充量は販売量とほぼ同じになり、不足していれば多めに仕入れる。このロジックに変えた瞬間、在庫残高が目標水準の前後で安定し始めた。

在庫管理シートのINDEX/MATCH化

当初は在庫管理シートのデータをPythonで直接書き込んでいたが、売上データ・仕入データとの整合性を保証するため、INDEX/MATCHとSUMIFS数式ベースに変更した。

売上データシートの販売数量をSUMIFSで月次集計し、仕入データシートの仕入数量も同様に集計する。在庫管理シートはこの2つの集計結果を参照して入出庫と残高を計算する。Pythonが書き込むのはシート構造と数式だけで、値はExcelの計算エンジンに任せる方針に統一した。

openpyxl数式未計算問題の根本対策

問題

gen_annual_table.py（Step B: 年次推移表生成）は、Step Aで生成したExcelを読み取って年次推移表を組み立てる。しかしopenpyxlは数式の計算結果を持たない。data_only=Trueで開いても、一度もExcelで開いていないファイルはキャッシュ値がNoneになる。

--from-excelオプションと--json-outputオプション

根本対策として、gen_annual_table.pyに2つのオプションを追加した。

  • --from-excel: COM経由でExcelを起動し、数式を計算させてから値を読み取る
  • --json-output: 読み取った値をJSON形式で出力し、他スクリプトから参照可能にする

COM経由でExcelを開くと数式が全て計算された状態で値を取得できる。Windows環境限定だが、開発マシンでの生成パイプラインには十分だ。

全ステップ完了: 4年度check=0

Step A（取引モジュール）、Step B（年次推移表）、Step C（CFWS）の順に生成し、全4年度でcheck=0を確認した。checkセルはCFWSの各年度シートに埋め込んだ検証数式で、BS残高の整合性とCFの合計が期首期末の現金差額と一致するかを検証する。4年度分すべてゼロが並んだ画面を見て、ようやく肩の力が抜けた。

スキルファイルの整理

在庫管理の追加に伴い、関連するスキルファイルを作成・更新した。進捗管理の記述をチェックボックス形式に統一し、完了・未完了の状態が一目で分かるようにした。

  • Step A: 取引モジュール生成（在庫管理込み）
  • Step B: 年次推移表生成（--from-excelオプション対応）
  • Step C: CFWS生成
  • 全4年度 check=0 通過

学びメモ

  • 1列追加したら3シートが壊れた: B列に数量を挿入しただけでINDEX/MATCHの参照先がずれ、売掛金・買掛金・クレカ決済の3シートが間違った値を返し始めた。列番号を定数にまとめておけば、修正箇所は1行で済む
  • ノイズ係数1.025が在庫を10倍に膨らませた: フィードバックなしで毎日上乗せし続けると、売れ残りが積み上がる一方になる。販売数量に連動させた瞬間、グラフが目標水準の前後で横ばいに変わった
  • openpyxlのdata_only=TrueはNoneを返す: 一度もExcelで開いていないファイルにはキャッシュ値がない。COM経由でExcelに計算させてから値を読み取ることで、Noneが消えた