会計freeeからROICツリーを作ろう

Last updated at 2020-12-25Posted at 2020-12-20

遅くなりました！今回のカレンダーのテーマで初めてfreeeを触りました。

これまでfreeeはSMBをセグメントに主戦場にされてきたのかなと思いますが、ユーザー企業の成長やfreee自体の成長に伴ってエンタープライズ一歩手前の会社がターゲットになってくると考え、「(税引前)ROICツリーを作ろう」をテーマにMicrosoft ExcelからfreeeのAPIを叩こうと思います。

[背景補足] 【保存版】外資系SaaSユニコーンの日本事業立ち上げから学ぶ、エンタープライズSaaSグロース戦略｜志村裕司｜note

まえおき

ROICツリー

会社規模拡大にともなって「経営と現場の意思疎通が難しいな」となってきたときに企業価値向上と各部門の活動の対応を数字で表現して目標を追っていく管理手法です。

ヤマシンフィルタの決算説明資料(2019)に詳しいです。

急成長企業だとこういった複雑な構成を取るかバーンレートとキャッシュをモニタしながらシンプルにトップラインを追うかはマネジメントの考え方次第ですが、会社規模が300人を超えてくると色々難しくなってくると聞きますし、ROICツリーみたいな考え方も大事なのかなと思ってます。

KPIは足し算と掛け算を併用しよう

ExcelでAPIを叩く

Excel 2016からはPower Query M 言語という強力な機能が使える様になりした。これはMicrosoft Power BIのデスクトップ版でも共通で使えるユニークな言語です。

今回は1ページのデータ取得程度にしか使いませんが、複数ページにまたがるjsonの取得などに非常に便利です。たとえばkintoneでは1回のAPIコールで500件のレコードまでしか取り出せませんが、これを複数ページつなげて取得するクエリも書くことができます。

Power BIでkintoneのレコードを取り出す(REST API) - 夜間飛行

ただ、今回書いてみて感じましたが永続的なアプリケーショントークンを発行するKintoneと比べて、きちんとしたOAuth2に従ってリフレッシュトークンの扱い等があるfreeeではプロトタイプにとどめておくのが良いのかもしれません。

ワークパッケージ

では前置きはこのくらいにしてワークパッケージです。

Excelでfreeeの貸借対照表と損益計算書の試算表を取得する

取得情報の設定シート作成
freeeのAPIの呼び出し設定

読み出した情報からROICツリーKPIの計算
見た目を整える(今回はしない)

貸借対照表と損益計算書の試算表取得

取得情報設定シート

Configとでも名前をつけたシートに'ConfigTable'と名前をつけた表を作ります。
この中にはROIC計算をするターゲットの年次、事業所の他、アクセストークンやリフレッシュトークンを書き込みます。

そして、データタブからからのクエリを作成します。

そうするとPower Queryのエディタが開くのでAdvanced Editorを開いて

こちらのコードを入力します。テーブル名と参照行数でパラメータを取り出す関数です。

Code From How To Parameterize Your Power Query | How To Excel

let 
    Parameter=(TableName, RowNumber) =>
        let
            Source = Excel.CurrentWorkbook(){[Name=TableName]}[Content],
            value = Source{RowNumber-1}[Value]
        in
            value
in Parameter

How To Parameterize Your Power Query | How To Excel

エディタで編集後は右端のクエリーの設定で関数名をfParameterと設定することを忘れないでください。

こうすると、他のクエリーの中で

FiscalYear = fParameter("ConfigTable", 1)

などとしてシートで定義した設定を読み出せるようになります。

Free APIの呼び出し

さて、いよいよfreee APIで試算表を取り出します。

貸借対照表の呼び出し

まずは賃貸貸借表からです。先程と同様に空のクエリーを作って、BalanceSheetとQueryを名付けます。取得したデータの表が含まれているシートは1.BSとでも名前をつけておきます。

let 
  // Author: Chachay https://blog.chachay.org/
  BaseUrl         = "https://api.freee.co.jp/api/1/reports/trial_bs?account_item_display_type=group&breakdown_display_type=account_item&company_id=",
  CompanyID       = fParameter("ConfigTable", 2),
  FiscalYear      = fParameter("ConfigTable", 1),
  Token           = fParameter("ConfigTable", 3),

  GetJson = (Url) =>
      let Options = [
            Headers=[
              Authorization="Bearer " & Token,
              Accept = "application/json",
              #"X-Api-Version" = "2020-06-15"
            ]
          ],
          RawData = Web.Contents(Url, Options),
          Source  = Json.Document(RawData),
          records = Source[trial_bs]
      in  records,

  Data        = GetJson(BaseUrl&Number.ToText(CompanyID)&"&fiscal_year="&Number.ToText(FiscalYear)),
  balances = Data[balances],
  #"Converted to Table" = Table.FromList(balances, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  #"Expanded Column" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", 
   {"account_category_name", 
    "account_group_name", 
    "account_item_name",
    "hierarchy_level", 
    "closing_balance", 
    "account_item_id"
})
in
  #"Expanded Column"

クエリを実行すると、こんな雰囲気で取得できます。

損益計算書の呼び出し

もうわかってきましたね。空のクエリーから…です。2.PLとシートに名前をつけておきます。

let 
  // Author: Chachay https://blog.chachay.org/
  BaseUrl         = "https://api.freee.co.jp/api/1/reports/trial_pl?company_id=2749740&fiscal_year=",
  FiscalYear      = fParameter("ConfigTable", 1),
  Token           = fParameter("ConfigTable", 3),

  GetJson = (Url) =>
      let Options = [
            Headers=[
              Authorization="Bearer " & Token,
              Accept = "application/json"
            ]
          ],
          RawData = Web.Contents(Url, Options),
          Source  = Json.Document(RawData),
          records = Source[trial_pl]
      in  records,

  Data        = GetJson(BaseUrl&Number.ToText(FiscalYear)),
  balances = Data[balances],
  #"Converted to Table" = Table.FromList(balances, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  #"Expanded Column" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", 
    {"account_category_id", 
     "account_item_name",
     "parent_account_category_id", 
     "parent_account_category_name",
     "account_category_name", 
     "total_line", 
     "hierarchy_level", 
     "closing_balance"})
in
  #"Expanded Column"

こんなんが取れます。

読み出した情報からROICツリーKPIの計算

ROICツリーについては様々な記事が出ていますので、そちらを参照していただくと良いと思います。

ただ、Freeeがテスト用用意しているアカウントではダミーデータに人件費関連が入力されていません。本件ではダミーデータを作るところまで手を入れませんでしたが、「人件費」はその性格によって「原価」になったり、「研究開発費」になったり、「販売費」になったりetc.するので会社のビジネスによって適切に分けましょう。

最近ではAIブームで受託開発などを取る事業が多くありました。80万円/月などの報酬でスターエンジニアを雇い(原価)として月額単価500万円～/人月で販売する類のビジネスが多かったと思います。シリコンバレーだと研究開発に寄せるAI企業が多くてビジネスのやり方が違うなぁという個人的感想を持っています。

さて、そうこう言っているうちにツリーに入れる数字ができました。