利益シミュレーションをひと目で把握しようツイートシェアはてブLINEPocketExcelでできることはいろいろあります。わたしもExcelはまだまだわからないことが多いです。単価がいくらだと何人に来てもらえば利益が出るかを一瞬で知りたい以前の記事でExcelでゴールシーク機能を使って、分析する方法をご紹介しました。url=”https://www.gou-blog.com/goalseek/”]ただ、これを1つ1つやるのは変化させたい数字が複数あれば大変です。この作業を何回もやる必要があります。いった場合にはExcelの「データテーブル」という機能が便利です。単価と客数の2つの数字が変わると、利益がいくら出るのか?といったことを知りたい場合に使います。感応度分析というものです。この「月間の損益」を使って実際にやってみます。今回の変数を「単価」と「数量」にします。売上単価を横軸、1日当たりの客数を縦軸として分析用の表をつくります。金額の幅はモデル値を最低の数字としないで、モデル値より低いものも、高いものも両方設定すると幅広い検証ができます。私は単価と人数の変数は、わかりやすいようにフォントの色を変えています。単価はとりあえず500円~1,000円まで、1日当たりの客数は50人~100人で設定してみました。後で検証しやすいように、モデルと同じシートに作成するのをオススメします。次に答えが知りたい値(今回の場合は利益)を分析用の表の左上(L3のセル)に利益のセル(G17)を参照させます。(式:=G17)利益の238(千円)がL3のセルに表示されました。これで準備は整いました。データテーブルで感応度分析ここで、データテーブルを使います。「データ」タブ→「What-if分析」から「データテーブル」を選択します。Alt→A→W→Tでもできます。データテーブルの画面が表示され、「行の代入セル」と「列の代入セル」の指定を聞いてきます。行は横軸なので「単価」、列は縦軸なので「客数」を指定します。データテーブルが完成。見やすくするために工夫をすると、分析の表に数字が一瞬で埋まりました。モデル値は単価700円の客数100人で、利益が238千円でした。客数が10人増えて110人になれば、単価は同じでも352千円の利益になるということがわかります。一方で単価が100円下がったら20人客数が増えても、165(千円)の利益しか出ないというわけです。単価の幅を50円ずつにしたかったと思ったら青い部分を変更すればいいだけです。こちらの方が現実的でしょう。変数を変えても利益の部分は自動的に再計算されます。見やすくするために、もうひと工夫しましょう。条件付き書式設定を使います。「セルの強調表示ルール」→「指定の値より小さい」を選択します。Alt→H→L→H→Lでも大丈夫です。ゼロより小さいものは赤になるようにします。するとOKを押す前でもプレビュ-表示されます。マイナス、つまり利益でなく赤字になった部分はすべてセルが赤色になったのが確認できます。条件付き書式設定を使います。一度「セルの強調表示ルール」を選択し、今度は「指定の値より大きい」を選択します。Alt→H→L→H→Gでも大丈夫です。今度はゼロより大きなものは緑になるようにします。これで黒字と利益の分岐がハッキリしました。見る人にとってみやすい資料を作成することを意識しましょう。url=”https://www.gou-blog.com/form/”]後は黒の238(千円)は見栄えよくするために白フォントにして見えなくしましょう。すれば、利益の出るところと赤字になるところが一目瞭然になりました。750円に単価を上げれば、10人減少してもほぼ同額の利益が出るとわかります。750円で客数100人であれば、363(千円)の利益となり、125(千円)も利益が増えます。することで、より今後の方針を検討しやすくなるのではないでしょうか。ゴールシーク機能を使って検算してみよう。最後に以前記事にしたゴールシーク機能を使って簡単な検算をしてみます。url=”https://www.gou-blog.com/goalseek/”]「データ」タブ→「What-if分析」から「ゴールシーク」を選択します。Alt→A→W→Gでもできます。先ほど触れた利益が363になるような単価は?というゴールを目指します。利益は363(千円)になるには、単価が750円である必要があるとの答えが出ました。分析の表にある数字で確認する場合にはこれで検証ができますが、表にないような50円単位でない単価や10人単位でない数字を答えとして欲しい場合にはゴールシークで計算する、もしくは分析表の価格や人数の単位を変更することで確認しましょう。法人2件の決算業務で1日事務所。6月決算のお客様を2件ほぼ完成させてからお盆休みに入りたいので、頑張ります。貸借対照表で考えるとわかりやすいRECOMMENDこちらの記事も人気です。相続税の申告や対策、会社・フリーランスの経理を効率化することや決算書を使って経営のサポートをすることが得意です。現在は主に名古屋市周辺で活動中。