EXCELの 練 習 ~10 年 後 の 自 分 をシミュレーションしてみよう~ 2006 年 6 月 2 日 ~9 日 基 礎 実 習 講 義 ( 金 5) 東 洋 大 学 経 営 学 部 会 計 ファイナンス 学 科 堀 田 真 理 1
1. 目 的 (1)エクセルの 基 本 的 な 操 作 方 法 について 学 ぶ 具 体 的 な 入 力 方 法 ( 数 値 や 文 字 ) 表 の 作 成 (セルの 挿 入 や 結 合 など) 連 続 データの 入 力 (オートフィル 機 能 ) 具 体 的 な 計 算 方 法 ( 単 純 計 算 や 関 数 機 能 の 使 用 ) グラフの 作 成 相 対 参 照 と 絶 対 参 照 について(セル 番 地 による 計 算 ) (2) 具 体 的 な 使 用 例 : キャッシュフロー 表 の 作 成 ~ ( 作 成 課 題 ) 10 年 後 の 自 分 ~ 作 成 課 題 を 通 じて 具 体 的 な 操 作 方 法 について 学 ぶ キャッシュフロー 表 の 作 り 方 を 学 ぶとともに ライフプランニングの 考 え 方 を 身 につける ( 参 考 文 献 ) 日 本 ファイナンシャル プランナーズ 協 会 10 代 から 学 ぶパーソナル ファイナンス 第 9 章 :ライフプランとお 金 これならわかる 生 活 設 計 の 手 引 き Ⅲ 2
2.EXCELについて どんなときに 使 用 すると 便 利 か? 表 の 作 成 ( 一 般 的 な 表 の 作 成 にも 活 用 できる) グラフ 作 成 ( 統 計 データをダウンロードして 活 用 しても 便 利 ) 計 算 データの 管 理 ( 分 析 ツールや 統 計 関 数 の 活 用 ) 回 帰 分 析 基 本 的 な 操 作 についての 注 意 事 項 数 字 入 力 は 直 接 入 力 の 状 態 で 計 算 や 関 数 の 場 合 には 必 ず = から 入 力 する なるべく セル 番 地 による 計 算 をおこなう 入 力 を 間 違 えたときは 戻 る ボタンを 利 用 すると 便 利 3
3.キャッシュフロー 表 の 作 成 ライフプランの 考 え 方 を 身 につけよう まずは 自 分 自 身 の 将 来 について 夢 や 目 標 を 考 える (いつ どのようなことをしたいのか? 家 族 はどうなのか?) ライフイベント 表 その 実 現 に 向 けて どうすればよいのか? そのための 資 金 は 十 分 か? など ライフイベントと 合 わせて 具 体 的 な 情 報 を 数 値 化 したシミュレーション キャッシュフロー 表 キャッシュフロー 表 上 で さまざまな 可 能 性 を 試 すことができる のも 大 きな 魅 力! 顧 客 の 要 望 や 情 報 をもとに キャッシュフロー 表 を 作 成 し 顧 客 の 夢 の 実 現 にむけて 改 善 点 などのアドバイスや 提 案 をしていくのがFPの 仕 事 FPがおこなう 提 案 書 の 作 成 には 欠 かせないもの 4
4. 作 成 課 題 : 具 体 的 なキャッシュフロー 表 の 作 成 作 成 に 必 要 なデータ 以 下 の 状 況 を 前 提 に EXCELファイルのライフイベント 表 とキャッシュフロー 表 を 完 成 させる 自 分 を 中 心 に 今 後 10 年 間 (30 歳 ぐらいまで)を 予 測 する 1. 現 在 (2006 年 )の 家 族 状 況 家 族 現 在 の 年 齢 職 業 備 考 父 52 歳 会 社 員 母 51 歳 専 業 主 婦 自 分 19 歳 大 学 1 年 生 独 身 同 居 妹 16 歳 高 校 1 年 生 独 身 同 居 5
2. 私 の 将 来 の 希 望 1 現 在 は 両 親 と 同 居 中 生 活 費 がかからない 分 アルバイトで 収 入 を 得 ながら 在 学 中 に 資 格 取 得 ( 簿 記 検 定 FP 自 動 車 免 許 など)を 目 指 したい 自 分 のパソコンを 持 ちたい 中 国 で 開 催 予 定 のオリンピックに 行 ったり 卒 業 時 には 卒 業 旅 行 でヨーロッパに 行 きたい 2 取 得 した 資 格 を 生 かして 金 融 業 界 に 就 職 後 は 独 立 して 独 り 暮 らしを 始 めたい マイカーを 取 得 し 時 々は 家 族 と 国 内 旅 行 をしたい 持 ち 運 びが 便 利 なノートパソコンに 買 い 換 えたい 父 の 定 年 退 職 時 には 記 念 に 家 族 で 海 外 旅 行 をしたい ダイビングやゴルフなどの 趣 味 をもっと 充 実 させたい 結 婚 後 の 資 金 を 得 るためにも 株 や 投 資 信 託 などの 資 産 購 入 を 始 めてみたい 3 28 歳 で 結 婚 して 早 めに 子 供 が 欲 しい 子 供 が 生 まれたら なるべく 妻 には 子 供 と 接 する 時 間 を 大 切 にしてほしい 結 婚 式 は 友 人 を 招 待 して 盛 大 にホテルで 行 ないたい 新 婚 旅 行 はイタリアに 行 きたい 子 供 誕 生 後 は マイカーを 買 い 換 えたい 6
3. 今 後 の 収 入 と 支 出 の 予 定 (1) 今 後 の 収 入 ( 単 純 化 のため 株 や 預 金 にかかわる 収 益 は 考 慮 しない) 1 アルバイト 収 入 年 間 収 入 ( 手 取 り) 80 万 円 (19 歳 ~22 歳 ) 2 就 職 後 の 給 与 年 齢 別 23 歳 ~24 歳 25 歳 ~26 歳 27 歳 ~28 歳 29 歳 ~30 歳 年 間 収 入 ( 手 取 り) 250 万 300 万 350 万 400 万 3 その 他 の 収 入 ( 今 後 予 想 されるもの) 結 婚 後 の 配 偶 者 の 収 入 250 万 ( 第 1 子 誕 生 後 は80 万 ) 親 からの 援 助 30 万 ( 独 り 暮 らしスタート 時 の 住 宅 資 金 ) 100 万 ( 結 婚 資 金 ) 7
(2) 今 後 の 必 要 資 金 1 経 常 的 な 生 活 費 ( 単 純 化 のため 基 本 生 活 費 のみ 変 動 率 を1%とする ) 項 目 現 在 の 支 出 額 今 後 の 支 出 額 備 考 ( 年 間 額 ) ( 年 間 額 ) 基 本 生 活 費 0 120 万 就 職 後 240 万 結 婚 後 住 居 費 ( 家 賃 ) 0 80 万 就 職 後 120 万 結 婚 後 養 育 費 0 24 万 第 1 子 誕 生 後 保 険 料 0 10 万 就 職 後 40 万 結 婚 後 20 万 50 万 就 職 後 その 他 の 費 用 趣 味 娯 楽 費 資 格 取 得 のための 費 用 マイカーの 維 持 費 など 8
2 一 時 的 に 必 要 な 支 出 (イベント 費 ) 時 期 イベントの 内 容 必 要 資 金 20 歳 自 動 車 免 許 取 得 費 25 万 パソコン 購 入 費 20 万 21 歳 海 外 旅 行 費 ( 中 国 ) 15 万 22 歳 卒 業 旅 行 の 費 用 30 万 23 歳 自 動 車 購 入 100 万 24 歳 パソコン 買 換 え 20 万 国 内 旅 行 10 万 25 歳 国 内 旅 行 10 万 27 歳 海 外 旅 行 ( 父 の 退 職 記 念 ) 50 万 資 産 購 入 50 万 28 歳 結 婚 ( 結 婚 資 金 ) 400 万 新 婚 旅 行 50 万 29 歳 第 1 子 の 出 産 30 万 30 歳 自 動 車 買 換 え 200 万 以 上 のデータをもとに 今 後 10 年 間 のライフイベント 表 と 予 想 されるキャッシュフロー 表 をEXCELを 用 いて 作 成 する なお 初 年 度 ( 現 時 点 )での 貯 蓄 残 高 を20 万 円 とする 9
作 成 の 手 順 1. 年 間 の 収 支 額 を 把 握 簡 単 家 計 簿 の 作 成 によって 各 年 ごとに 発 生 する 収 支 額 を 予 想 する 2.ライフイベント 表 の 作 成 年 号 や 年 齢 家 族 イベント 発 生 について 記 入 3.キャッシュフロー 表 の 作 成 1 収 入 欄 の 作 成 収 入 合 計 記 入 2 支 出 欄 の 作 成 支 出 合 計 記 入 3 年 間 収 支 の 算 出 収 入 合 計 - 支 出 合 計 4 貯 蓄 残 高 の 算 出 前 年 度 の 貯 蓄 残 高 +(-) 今 年 度 の 年 間 収 支 10
作 成 課 題 ~EXCELファイルの 完 成 ~ (1) Sheet1 : 我 が 家 の 簡 単 家 計 簿 の 作 成 1 ( 表 1)の 空 欄 にあてはまる 年 間 額 を 計 算 して 記 入 しなさい ( 妻 の 給 与 総 額 基 本 生 活 費 住 居 費 ) = 月 額 *12+ 一 時 的 な 額 (セル 番 地 で 直 接 入 力 ) 2 ( 表 1)の 支 出 項 目 に 7. 国 内 旅 行 を 追 加 し 一 時 的 な 支 出 額 として 20 万 を 記 入 しなさい 挿 入 行 またはセル 3 ( 表 1)で 収 入 合 計 と 支 出 合 計 を 入 力 しなさい ( 月 額 と 年 額 ) =Σ で 入 力 する 4 ( 表 1)で 貯 蓄 可 能 額 ( 月 間 と 年 額 )を 求 めなさい = 収 入 合 計 - 支 出 合 計 5 ( 表 2)で 株 式 の 保 有 比 率 を 求 めなさい = 株 式 / 合 計 6 ( 表 2)の 金 融 資 産 保 有 状 況 を 円 グラフにしなさい 範 囲 指 定 し グラフ 機 能 を 使 用 11
(2)sheet2: ライフイベント 表 の 完 成 1 自 分 の 年 齢 欄 を 完 成 させなさい 連 続 データは オートフィル 機 能 を 使 うと 便 利 19 20と2つ 入 力 しておいて これらのセルをまとめて 選 び まとめて 選 択 した 領 域 の 右 下 角 に 表 示 される を ドラッグしていく 2 年 齢 の 文 字 を 自 分 ~ 妹 までの 領 域 の 中 央 に 配 置 しなさい 該 当 する 表 の 幅 にあわせて 領 域 を 選 択 し 書 式 セル 配 置 セルを 結 合 中 央 揃 え など もしくは 直 接 セルの 結 合 ボタンを 使 用 (3)sheet3: キャッシュフロー 表 の 完 成 1 キャッシュフロー 表 ( 対 策 前 )において 同 様 にオートフィル 機 能 で 自 分 の 年 齢 欄 を 完 成 させなさい 2 上 記 の 表 において 支 出 項 目 の その 他 の 支 出 欄 を 完 成 させなさい 同 じデータの 連 続 記 入 コピーするセルを 選 択 し 右 下 角 を +に 変 えた 状 態 でそのままドラッグ 12
3 収 入 合 計 欄 の 入 力 方 法 を 参 考 に 支 出 合 計 を 記 入 しなさい =Σの 範 囲 指 定 で 入 力 できるが 各 年 度 とも 同 じ 計 算 方 法 になるので 2 年 度 分 を 入 力 したら オートフィル 機 能 でドラッグすると 簡 単 4 年 間 収 支 貯 蓄 残 高 を 求 めなさい 相 対 参 照 の 使 い 方 : セルを 対 象 にした 計 算 式 のコピー セル 番 地 で 計 算 式 を 入 力 しておき オートフィル 機 能 で ドラッグしていけば 同 じ 計 算 方 法 が 場 所 を 変 えて 隣 のセル でも 実 施 される 5 11 年 間 の 支 出 額 の 平 均 を 求 めなさい = 関 数 の AVERAGE で 範 囲 指 定 6 キャッシュフロー 表 ( 対 策 後 )のシートを 用 いて 貯 蓄 残 高 をプラスに 改 善 する 方 法 を 考 えなさい ( 例 ) 例 えば この 表 で 赤 い 文 字 にしてある 支 出 額 を 変 えてみる イベント 費 用 : 27 歳 時 の100 万 80 万 に( 旅 行 費 削 減 ) 28 歳 時 の450 万 350 万 に( 結 婚 資 金 削 減 ) 30 歳 時 の200 万 150 万 に( 安 い 自 動 車 に) その 他 の 支 出 : 就 職 後 の50 万 30 万 に( 趣 味 娯 楽 費 の 削 減 ) 数 字 を 変 えていくと 結 果 がそのまま 貯 蓄 残 高 まで 反 映 されていく 13
7 同 様 に キャッシュフロー 表 ( 対 策 後 )についても 改 善 された 結 果 をもとに 収 入 や 支 出 の 平 均 を 計 算 しなさい 8 キャッシュフロー 表 の( 対 策 前 )と( 対 策 後 )のそれぞれについて 年 間 収 支 額 をグラフにしなさい どのようなグラフの 種 類 がよいか 自 分 で 検 討 する 色 を 変 えるなど いろいろ 試 してみる 9 キャッシュフロー 表 ( 対 策 前 の 変 動 率 加 味 )において 基 本 生 活 費 欄 を 完 成 させなさい 物 価 変 動 などを 考 慮 すると 4 年 後 の 基 本 生 活 費 120 万 は あくまでも 現 時 点 でみた 価 値 額 であって 厳 密 には4 年 後 の 価 値 額 ではない 変 動 率 を1%とすると 4 120 (1.01) でなければならない ( 注 意 ) すでに 入 力 されているセルの 入 力 方 法 を 参 考 にする 絶 対 参 照 と 相 対 参 照 ふつうに 入 力 すれば 例 えば この4 年 後 の 数 値 は 120*1.01^4 と 入 力 することになるが ここでも なるべく 簡 単 な 入 力 方 法 を 用 いる 14
絶 対 参 照 と 相 対 参 照 について 4 乗 や5 乗 と 同 じ 計 算 方 法 で 隣 のセルに 移 動 していく 方 法 は 相 対 参 照 になる 4と 同 様 に オートフィル 機 能 が 使 える しかし 120や1.01は 隣 のセルに 移 動 したとしても 固 定 された 数 値 そのまま オートフィル 機 能 で 移 動 してしまうと この 値 も 変 化 してしまう 絶 対 参 照 そこで 120や240 1.01などの 固 定 値 は 別 に 入 力 しておいて この 値 を 用 いるときは F4キーで 変 換 する 例 えば D78のセルに 入 力 しておいた120は F4キーで 変 換 すると $D$78 という 表 記 に 変 わる 120*1.01^4 は? $D$78*$D$80^4 になる 15
10 支 出 合 計 年 間 収 支 貯 蓄 残 高 などを 完 成 させなさい なお 貯 蓄 残 高 については 運 用 利 率 を1%で 加 味 すること 支 出 合 計 年 間 収 支 はこれまでと 同 様 に セル 番 地 で 入 力 計 算 し オートフィル 機 能 でドラッグ 貯 蓄 残 高 については 運 用 利 率 を 加 味 すると 今 年 の 貯 蓄 残 高 = 前 年 の 貯 蓄 残 高 1.01+(-) 年 間 収 支 絶 対 参 照 で 固 定 値 として 入 力 1.01は 固 定 値 となることに 注 意 すれば あとは 他 と 同 様 変 動 率 を 加 味 して 考 えると 貯 蓄 残 高 が 赤 字 の 場 合 には よりその 赤 字 が 大 きくなってしまうことがわかる 11 興 味 があれば 自 分 自 身 のことについて 作 成 してみる 16