JAPAN SYSTEMS Driving for NEXT NEW with Comfort and Convenience

木も見て森も見たい(6)~自治体財務データから見えるもの~

前回コラムでの作業結果より】
Rのスクリプト例

色々とやって来ましたが

ここまで色々とやって参りましたが、一旦ここまでの話を振り返ります。

元々の問題意識として、財務データなどを俯瞰的に見る際に、様々な切り口が有ると思います。財務データの中でも、財務諸表などは全体像が公表されている反面、例えば、部署別やサービス別(いわゆるセグメント別)の情報が分からないケースが多いのが現状です。
ならば、財務諸表の元になっている、執行伝票のレベルから集計し直して、見たい切り口で眺めてみよう!とはいえ、一口にセグメントと言っても多くの種類が有ります。元々、財務データは予算を計上する段階から細かくセグメントが入っているのだから、ことさらセグメントと言われても・・・、というご意見もあるかもしれませんが、新地方公会計制度が導入された理由の一つでもあった「資産」に着目して財務データを整理するという切り口は、これまであまり注目されておりませんでした。そこで、セグメント分析の切り口を「施設別」ということに措定して、執行伝票を集計してみよう、という話でした。

集計しましょう!

まずは作業の方針です。執行伝票については、何らかの基準によって施設にデータを集計しなければなりません。もちろん、執行伝票に最初から「どの施設の支出なのか」という情報が入っていれば、ほとんど議論は終了です。ただ現状においては、情報が入っているのは極めて稀です。また、仮に執行伝票に施設情報が入っていたとしても、「水光熱費:A小学校ほか10校分」などと情報が入っていた場合、この水光熱費を各学校に按分しましょう、という話になってしまいます。

ということで、執行伝票に手掛かりが無く、原課(※)にお願いして施設情報を入力していただく訳にはいかないため、「按分」でやってみることにします。算出された数字には、多分に違和感は有るかもしれませんが、コストをかけず、スピード重視で俯瞰してみる分には差し支えありません。

では、「素材」集めから始めます。まず、「施設別」にコスト情報を集約するため、当然ですが「施設情報」が必要となります。コスト按分に際しては「延床面積」「簿価」などを使う可能性が高い。また、執行伝票と施設情報を繋げるために、施設の所管課がどこになるのか、その情報も欲しい。そうなると、やはり固定資産台帳に代表されるデータソースを入手したいところです。他方、執行伝票に関しては、担当課の情報はもちろんですが、施設を「ハード事業」と捉え、伝票の性質に着目して集計を行いたいので、節・細節などの項目は欲しいところです。

この様な条件を具備(ぐび)し、かつ、公開情報で賄おうとすると、これがとても難しい。というのは、固定資産台帳については、多くの団体で公開されているのですが、執行伝票は殆ど公開されていないためです。そこで困っていたところ、なんとA区様だけが執行伝票を公開されていらっしゃったので、有り難く利用させていただくことにしました。その代わり、固定資産台帳は公開されていなかったため、「公共施設等総合管理計画」から何とか必要データを抽出することにしました。この作業が一筋縄では行かなかったのは以前のコラムで記載した通りですが、加えて「担当部署」の情報が無いため、まだ、一工夫必要です。

今回は、数ある施設の中から「中学校」に絞ることにしました。まず、A区様が公開されている「財務報告書」から、中学校に関係ある部署は「学校運営部 学校支援課・学校施設課・学務課」であると当たりをつけました。つまり、これらの部署が執行した金額だけを中学校施設に紐付ける訳です。もちろん、他の部署で支出された伝票の中にも、中学校施設に紐付けるべきものは有ると思うのですが。続けて、「公共施設等総合管理計画」の巻末にある「施設一覧」から、中学校の施設名称を取り出しました。これら2つの情報を結合することで「擬似施設マスタ」は出来上がりです。

執行伝票の方には次の様な作業を施します。

  1. ① 部:学校運営部 で支出された伝票を抽出します。
  2. ② 項:中学校費 で支出された伝票を抽出します。
  3. ③ 「支払内容」の列に、「小・中学校」「小中学校」 とある伝票は削除します。
  4. ④ 「担当課」の列に、個別の中学校名が入っている伝票がありました。これは、100%その中学校の費用と見なします。
  5. ⑤具体的な中学校名が分からない伝票は、「延床面積」で、各中学校に配賦します。節レベルで、特に絞りこまず、全部、施設に紐付けます。

③の理由は、執行伝票の内容を詳しく見ると、どうやら小中学校の給食センター関連支出であり、その場合、配賦対象として小学校情報が必要になります。今回は作業簡略化のため、小学校情報は抽出していないので、配賦対象外経費としました。

作業の結果は下記の通りです。「ハード事業」として見たければ、任意の定義にしたがって、必要な節だけで絞り込んでいただくこともできます。念のため、11・13・15節に絞ってグラフも描いておきました(Y軸は天井を適当にカットしてあります)。

節別合計の集計結果(タップすると拡大表示されます。)
節別合計

# ディレクトリの設定
setwd("/Users/tm/Dropbox/_lg/A区/")

# 所属マスタの作成
shozoku <-
    c("学校支援課", "学校施設課", "学務課") %>%
    as_tibble() %>%
    rename(課 = 1) %>%
    group_nest()

# 施設マスタの作成
shisetsu <-
    # 事前に抽出した中学校一覧を読み込む
    read_excel("./200922_中学校一覧_Using_Adobe_Pages from kannrikeikaku.xlsx") %>%
    # 「名称」「延床面積」だけ残す
    select(1, 9) %>%
    # 列名称変更
    rename(延床面積 = 2) %>%
    # 施設名の頭にくっ付いている番号を分離
    separate(名称, c("番号", "名称"), sep = "  ") %>%
    # 配賦列を挿入
    mutate(紐付け先 = "配賦")

shisetsu_join <-
  shisetsu %>%
    group_nest(紐付け先)

# 施設辞書の作成:摘要欄から施設名を抽出するために辞書を作成
shisetsu_dic <-
  shisetsu  %>%
    select(名称, 番号) %>%
    # 重複を削除
    distinct() %>%
    # 列を行に置換
    pivot_wider(names_from = 番号, values_from = 名称) %>%
    # 1レコードに統合
    unite("辞書", 1 : 36, sep = "|") %>%
    chuck(1) %>% as.vector()

# 執行伝票の読み込み
  denpyo_all <-
      read_csv("./200511_data.csv") %>%
      # 名称変更
      rename(摘要 = 11, 支払額 = `支払額(円)`) %>%
      # 部・項  で絞り込み
      filter(部 == "学校運営部") %>%
      filter(項 == "中学校費") %>%
      # 小学校にも配賦すべき?伝票を除外
      filter(!str_detect(摘要, "小・中学校")) %>%
      filter(!str_detect(摘要, "小中学校"))

# 検算用
setsu_all <-
  denpyo_all %>%
      group_nest(節) %>%
      mutate(合計額 = map_dbl(data, ~ sum(.$支払額))) %>%
      select(-data)

  # # 作表
  # setsu_all %>%  
  #   datatable(caption = "節別合計", rownames = F,
  #             options = list(
  #               pageLength = 30,
  #               # dom = 't',
  #               scrollX = F,
  #               scrollCollapse = F)) %>%    
  #     formatCurrency(columns = c('合計額'), currency = "", mark = ",", digits = 0)


# 施設別集計
denpyo_junior <-    
  denpyo_all %>%
    # 摘要から中学校名を抽出;map_chrを使うこと!
    mutate(紐付け先 = map_chr(摘要, ~ str_extract(., shisetsu_dic))) %>%
    # 摘要に学校名が入っていなかった伝票に「配賦」を挿入
    mutate_at(vars(紐付け先),  ~ replace_na(., "配賦")) %>%
    group_nest(紐付け先) %>%
    # 配賦が必要な伝票にのみ施設名称を紐付け
    left_join(shisetsu , by = "紐付け先") %>%
    group_nest(紐付け先) %>%
    mutate(総面積 = map_dbl(data, ~ sum(.$延床面積))) %>%
    unnest() %>%
    unnest() %>%
    # 按分比率の計算
    mutate(按分比率 = 延床面積 / 総面積) %>%
    # 配賦金額の計算
    mutate(配賦額 = 支払額 * 按分比率) %>%
    # 施設名称の統合
    mutate(紐付け先 = if_else(str_detect(紐付け先, "配賦"), 名称, 紐付け先)) %>%
    mutate_at(vars(配賦額), ~ if_else(is.na(配賦額), 支払額, 配賦額))

  # 節別:作表
  denpyo_junior %>%
    group_nest(節) %>%
    mutate(節合計額 = map_dbl(data, ~ sum(.$配賦額))) %>%
    select(-data) %>%

# テーブル表示
    datatable(caption = "節別合計", rownames = F,
              options = list(
                # columnDefs = list(list(targets = c(0 : 2), width = 50)),
                pageLength = 30,
                # dom = 't',
                # scrollX = F,
                scrollCollapse = F)) %>%
      formatCurrency(columns = c('節合計額'), currency = "", mark = ",", digits = 0)

施設別合計の集計結果(タップすると拡大表示されます。)
節別合計

  # 施設別:作表
  denpyo_junior %>%
    group_nest(紐付け先) %>%
    mutate(施設合計額 = map_dbl(data, ~ sum(.$配賦額))) %>%
    select(-data) %>%

    datatable(caption = "施設別合計", rownames = F,
              options = list(
                pageLength = 15,
                # dom = 't',
                scrollX = F,
                scrollCollapse = F)) %>%
      formatCurrency(columns = c('施設合計額'), currency = "", mark = ",", digits = 0)

施設別内訳の集計結果(タップすると拡大表示されます。)
節別合計

  # 施設別:節別:作表
facil_setsu_junior <-  
  denpyo_junior %>%
    group_nest(紐付け先, 節) %>%
    mutate(施設合計額 = map_dbl(data, ~ sum(.$配賦額))) %>%
    select(-data)    

  facil_setsu_junior %>%

# テーブル表示
    datatable(caption = "施設別合計", rownames = F,
              options = list(
                pageLength = 15,
                # dom = 't',
                scrollX = F,
                scrollCollapse = F)) %>%
      formatCurrency(columns = c('施設合計額'), currency = "", mark = ",", digits = 0)

グラフ(タップすると拡大表示されます。)
節別合計

# グラフ表示
  facil_setsu_junior %>%
    filter(節 %in% c("工事請負費", "委託料", "需用費")) %>%
    ggplot(aes(x = 節, y = 施設合計額, fill = 節))+
      geom_bar(stat = "identity", postion = "dodge") +
      # 日本語フォント表示
      theme_gray(base_family = "HiraKakuPro-W3") +
      # Facet
      facet_wrap(~ 紐付け先) +
      # 軸文字の角度を変える
      theme(axis.text.x = element_text(angle = 45, hjust = 1)) +
      # # 上限の設定
      # scale_y_continuous(limits = c(0, NA)) +
      # 軸タイトルを消す
      theme(axis.text.x = element_blank()) +
      # 表示上限値を規定
      coord_cartesian(ylim = c(0, 30000000))

(次回に続く・・・)

コラムニスト
淑徳大学コミュニティ政策学部 専任講師 松村 俊英

参考

原課(げんか):官庁で用いられる用語で、主に担当課を指します。

関連コラム

Adobe® Reader®

PDFファイルの閲覧には、Adobe® Reader®が必要です。

Adobe® Reader®のダウンロード

お問い合わせ