予実管理ダッシュボード

当初の予算である目標とその実績から達成度合いをモニタリングしていく予実管理ダッシュボードの作成方法を紹介します。

本ダッシュボードでは、年度スライサーを操作することにより予算(目標)と実績を比較して年度ごとの達成率を確認することができます。また、製品カテゴリスライサーを操作することで、どのカテゴリの製品が目標を達成できているのか、目標を達成するための施策を取らなければならない製品が何なのかを簡単に把握することができます。

※DragonFly BIの使用方法について詳しくは、製品ヘルプおよび基本チュートリアルをご参照ください。

データの構成

本ダッシュボードで使用するデータソース(Excelファイル)はこちらからダウンロードできます。

Excelファイルの概要

シート名 説明
売上データ(FY12-16) 2012/4/1~2017/2/28の売上明細データ、および月別製品別の予算データ。「データ区分」列で各行のデータを実績/予算に区分。

Excelファイルの中身

ダッシュボードの作成方法

ダッシュボード作成の一連の流れは次のとおりです。
1.データに接続
2.データセットを作成
3.ダッシュボードを作成
3-1.年度日付フィールドを作成
3-2.スプレッドシートを作成
3-3.スプレッドシートからグラフを作成
3-4.ダッシュボードをレイアウト

データに接続

予実管理サンプルデータに接続するためのExcelデータ接続を作成します。

項目 内容
データソース種別 Microsoft Excelファイル
データ接続名 予実管理サンプルデータ.xlsx
アップロードファイル名

予実管理サンプルデータ.xlsx

関連する情報

データ接続の基本

データセットを作成

作成したデータ接続を使用してデータセットを作成します。

項目 内容
参照するデータ接続 予実管理サンプルデータ.xlsx
データセット名 予実管理サンプルデータ
データを取り込むシート 売上予算実績データ(FY12-16)
フィルタ設定 なし

ダッシュボードを作成

作成したデータセットを使用してダッシュボードを作成します。

項目 内容
参照するデータセット 予実管理サンプルデータ
ダッシュボード名 予実管理デモ

年度日付フィールドを作成
スプレッドシート、グラフ上で会計年度単位のデータ集計を行うため「日付」フィールドから「年度日付」フィールドを作成します。

項目 内容
フィールド名 年度
年度の開始月 4月
終了月の暦年を年度に設定する 無効

関連する情報

開始月および暦年を指定して独自の年度日付を作成したい

金額フィールドの書式設定

カンマ区切りの数値形式に設定します。

項目 設定
カテゴリ 数値
小数点以下の桁数 0
桁区切り(, )を使用する 有効

金額フィールドにマウスカーソルを移動すると、右に [歯車]アイコンが表示されます。[歯車]アイコンをクリックし、書式設定を選択することで表示される書式設定ダイアログにて書式を設定できます。

 

スプレッドシートを作成

スプレッドシートシナリオを新規作成し、売上実績を月別、および累計で集計表示するピボットテーブルを配置します。

関連する情報

スプレッドシートの作成

売上実績(月別)ピボットテーブル
A1セルにタイトルとして「売上実績(月別)」と入力します。
A2セルを起点にピボットテーブルを作成します。

項目 設定
合計: 金額
月(年度日付)
データ区分
トレリス列 -
トレリス行 -

列の合計を非表示にするため、画面右の書式設定ペインで[ピボット]タブを選択し、[レイアウト]-[総計]プロパティを「行」に設定します。

項目 設定
全般 – クロスフィルターを有効にする 有効
レイアウト – 小計
レイアウト – 総計
レイアウト – ヘッダーラベル 繰り返しなし
値の変更 – 変更したセルをハイライト表示 有効

5行目に達成率(実績/予算)を表示します。
A5セルに「達成率」と入力します。
B5セルに「達成率」の計算式を入力します。#DIV/0!エラーを表示させないよう、IF関数を使用し、下記のように実績と予算の割り算を入力します。

  • 計算式:=IF(B3<>"",B3/B4,"")
  • B5セルに入力した計算式をC5:N5の範囲にコピー

B5セルからN5セルの範囲を選択した状態で右クリックし、セルの書式設定を選択します。以下のように表示形式をパーセンテージにします。

項目 設定
カテゴリ パーセンテージ
小数点以下の桁数 1

B5セルからN5セルの範囲を選択した状態で右クリックし、書式設定ペインで[セル]タブを選択します。[表示形式]―[条件付き書式]―[新しいルール]を選択し、以下のようにアイコンセットの条件付き書式を設定します。

項目 設定
ルールの種類 セルの値に基づいてすべてのセルを書式設定
書式設定 アイコンセット
アイコンのみ表示 無効
アイコンの順序を逆にする 無効
ルール1 「>=」、値「1」、種類「数値」
ルール2 「>=」、値「0.9」、種類「数値」

関連する情報

スプレッドシートのセルに条件付き書式を適用したい

売上実績(累計)ピボットテーブル
A7セルにタイトルとして「売上実績(累計)」と入力します。
A8セルを起点にピボットテーブルを作成します。

項目 設定
合計: 金額
月(年度日付)
データ区分
トレリス列 -
トレリス行 -

ピボットテーブルに累積値を集計表示するため、値の「合計: 金額」のメニューから累積合計フィールドを追加します。

累積合計ダイアログの累積対象はトレリステーブルのまま[OK]をクリックします。

このピボットテーブルには各月の合計金額は表示しないため、値の「合計: 金額」のメニューから削除を選択し、「合計: 金額」を削除します。

行と列の合計を非表示にするため、画面右の書式設定ペインで[ピボット]タブを選択し、[レイアウト]-[総計]プロパティを「なし」に設定します。

項目 設定
全般 – クロスフィルターを有効にする 有効
レイアウト – 小計
レイアウト – 総計 なし
レイアウト – ヘッダーラベル 繰り返しなし
値の変更 – 変更したセルをハイライト表示 有効

行と列の合計が非表示になり、以下のようになります。

11行目に達成率(実績/予算)を表示します。
A11セルに「達成率」と入力します。
B11セルに「達成率」の計算式を入力します。#DIV/0!エラーを表示させないよう、IF関数を使用し、下記のように実績と予算の割り算を入力します。

  • 計算式:=IF(B9<>"",B9/B10,"")
  • B11セルに入力した計算式をC11:M11の範囲にコピー

B11セルからM11セルの範囲を選択した状態で右クリックし、セルの書式設定を選択します。以下のように表示形式をパーセンテージにします。

項目 設定
カテゴリ パーセンテージ
小数点以下の桁数 1

B11セルからM11セルの範囲を選択した状態で右クリックし、書式設定ペインで[セル]タブを選択します。[表示形式]―[条件付き書式]―[新しいルール]を選択し、以下のようにアイコンセットの条件付き書式を設定します。

項目 設定
ルールの種類 セルの値に基づいてすべてのセルを書式設定
書式設定 アイコンセット
アイコンのみ表示 無効
アイコンの順序を逆にする 無効
ルール1 「>=」、値「1」、種類「数値」
ルール2 「>=」、値「0.9」、種類「数値」

最後にB9からN10までを選択し、[セル]タブで、表示形式で桁区切り(, )を有効にし、小数点以下を0にします。

スプレッドシート上に、売上実績(月別)と売上実績(累計)を表示するピボットテーブルが完成しました。

グラフの作成
スプレッドシートに作成した売上実績(月別)ピボットテーブルと売上実績(累計)ピボットテーブルからそれぞれグラフを作成します。

関連する情報

スプレッドシートのデータ範囲からグラフを作成したい

売上実績(月別)グラフ

スプレッドシートに作成した売上実績(月別)ピボットテーブルの任意のセル上で右クリックし、[ピボットグラフへエクスポート]―[縦棒]を選択します。

グラフタイトルを変更するため、画面右の書式設定ペインで[グラフ タイトル]を以下のように設定します。

項目 設定
タイトルを表示 有効
自動タイトル 無効
配置 左詰め
タイトル 売上実績(月別)

数値軸の書式を変更するため、画面右の書式設定ペインで[数値軸]-[表示形式]から「その他の表示形式…」を選択し、以下のように設定します。

項目 設定
カテゴリ 数値
小数点以下の桁数 0
桁区切り(, )を使用する 有効

予算データを折れ線グラフで表示するため、画面右の書式設定ペインで[系列名]から「達成率」を選択し、以下のように設定します。

項目 設定
系列名 予算
グラフ種 折れ線
線種 実線
Y軸の種類 Y2
欠損値 描画しない

 

売上実績(累計)グラフ

シナリオタブからスプレッドシートに戻り、作成した売上実績(累計)ピボットテーブルの任意のセル上で右クリックし、[ピボットグラフへエクスポート]―[縦棒]を選択します。

グラフタイトルを変更するため、画面右の書式設定ペインで[グラフ タイトル]を以下のように設定します。

項目 設定
タイトルを表示 有効
自動タイトル 無効
配置 左詰め
タイトル 売上実績(累計)

実績データを折れ線グラフで表示するため、画面右の書式設定ペインで[系列名]から「予算」を選択し、以下のように設定します。

項目 設定
系列名 予算
グラフ種 折れ線
線種 実線
Y軸の種類 Y1
欠損値 描画しない

デルタ付きナンバー

売上実績と予算の差分を示すデルタ付きナンバーを作成します。グラフを新規追加し、デルタ付ナンバーを選択します。

 

デルタ付きナンバーには値(実績値)と目標値の2つのデータ項目を設定する必要がありますが、データセットでは実績、予算が同じ「金額」列に混在しており、これを区別して集計するために計算フィールド機能を使用して「実績」、「予算」フィールドをそれぞれ作成し、デルタ付きナンバーの値と目標値に設定します。

「データ区分」が「実績」の場合に「金額」の値、そうでなければ0を代入する「実績」フィールド(計算フィールド)を作成します。

  • フィールド名:実績
  • 式:IF([@[データ区分]]="実績",[@[金額]],0)
  • フィールドタイプ:自動

「データ区分」が「予算」の場合に「金額」の値、そうでなければ0を代入する「予算」フィールド(計算フィールド)を作成します。

  • フィールド名:予算
  • 式:IF([@[データ区分]]="予算",[@[金額]],0)
  • フィールドタイプ:自動

ここまでの設定を行うと、メジャーに「実績」フィールドと「予算」フィールドが追加されます。

「実績」、「予算」フィールドの書式をカンマ区切りの数値形式に設定します。

項目 設定
カテゴリ 数値
小数点以下の桁数 0
桁区切り(, )を使用する 有効

デルタ付きナンバーを作成します。ピボット設定は以下のとおりです。

項目 設定
合計: 実績
目標値 合計: 予算
トレリス列 -
トレリス行 -

グラフタイトルを変更するため、画面右の書式設定ペインで[グラフ タイトル]を以下のように設定します。

項目 設定
タイトルを表示 有効
自動タイトル 無効
配置 左詰め
タイトル 売上実績/予算

 

ダッシュボードをレイアウト

ここまでの手順で作成したスプレッドシートとグラフ、スライサーを下記のように配置してダッシュボードを作成します。

スライサー

年(年度)スライサー

項目 設定
ヘッダーを表示 有効
複数 無効
フォント デフォルト
塗りつぶし -

製品カテゴリスライサー 

項目 設定
ヘッダーを表示 有効
ヘッダー 製品カテゴリ
複数 有効
フォント デフォルト
列数 3
選択モード 単一
データのないアイテムを非表示にする 無効
データのないアイテムを視覚的に示す 有効
データのないアイテムを最後に表示する 有効
塗りつぶし -

関連する情報

ダッシュボードの作成

ダッシュボードのプレビュー(実践)

スライサーによるフィルタリング

作成したダッシュボードで、年度別の実績や製品カテゴリごとの実績を確認できます。

例えば、2015年度の売上を見てみると、全体的には好調ですが、製品カテゴリで「その他」を選択すると、月別売上が未達の月が多く、年間を通した累計も良くないことがわかります。

実績のシミュレーション

2016年度は2月までの実績累計が予算に対して102.6%と好調です。2016年度3月でどの程度の実績を出せば、予算を達成できるかを計算してみましょう。

スプレッドシートのピボットテーブル「売上実績(月別)」上のセルB3に
式:=M10-L9
を入れてみます。

式が計算され、2016年3月に453,400円の実績を上げることで予算が達成されることがわかります。

逆に2016年3月の予算通りに586,000円の実績を出せた場合には、102.4%で着地することがわかります。

この記事は役に立ちましたか?

0人中0人がこの記事が役に立ったと言っています
他にご質問がございましたら、リクエストを送信してください