利用Excel建立家庭理财规划分析模型_陈嘉立

费用账户
吃 穿 住 行 通讯 医疗 娱乐 学习 敬老
交际
投资损失
其他支出
选定Ｂ３：Ｂ３０的所有单元，右击选中“命名单元格区域（Ｒ）”，在
弹出单元框的“名称”输入“Ａｃｃｏｕｎｔ”。 二、建立家庭日记账 在工作簿中选择一个工作表，将其命名为“家庭日记账”，相关
项目的设置和使用，如表２所示。选定Ｃ３单元，从菜单栏中选择“数 据 ” 下 的 “ 数 据 有 效 性 ”：“ 允 许 ” 选 择 “ 序 列 ”，“ 来 源 ” 填 入 “＝Ａｃｃｏｕｎｔ”，勾选“忽略空值”、“提供下拉箭头”和“对有同样设置 的所有其他单元格应用这些更改（Ｐ）”。同理，可以设定Ｃ列和Ｄ列 中所有单元的下拉列表。
项目
金额
三、收入 月薪收入 利息收入 投资收益 其他收入 总收入
８５００ ５０ ７５０ ５００
９８００
四、费用 吃 穿 住 行 通讯 医疗 娱乐 学习 敬老 交际 投资损失 其他支出 总费用
８００ １７５ ９５０ ３２５ ２２５ １００ ７２５ ３５０ ４５０ ３５０ ２５０ ５０ ４７５０
Ｃ 月度 ５
６
·综合 2010 年第 11 期（中）
投资理财 INVESTMENT FINANCING
函数：＝ＳＵＭＩＦ（Ｉｎ，Ａ４，Ａｍｏｕｎｔ）－ＳＵＭＩＦ（Ｏｕｔ，Ａ４，Ａｍｏｕｎｔ）；对应“占 资产比重”为单元Ｃ４，输入：Ｂ４／Ｂ９。对于负债账户，如“信用卡”项 目，对应“金额”为单元Ｂ１２，输入目标函数：＝ＳＵＭＩＦ（Ｏｕｔ，Ｂ１２， Ａｍｏｕｎｔ）－ＳＵＭＩＦ （Ｉｎ，Ｂ１２，Ａｍｏｕｎｔ）；对应“占负债比重”为单元 Ｃ１２，输入：Ｂ１２／Ｂ１６。其余同理。
行
￥１００羊城通充值
７ ２０１０年１月５日 信用卡
通讯
￥３００手机话费充值
８ ２０１０年１月６日 月薪收入 现金
￥８０００２００９年１２月工资
９ ２０１０年１月７日 金融资产 银行存款 ￥４０００出售所持有中信银行股票（买入价）
１０ ２０１０年１月７日 投资收益 银行存款 ￥３５０出售所持有中信银行股票（收益）
DOI:10.16144/j.cnki.issn1002-8072.2010.32.071
投资理财 INVESTMENT FINANCING
利用 Exc e l 建立家庭理财规划分析模型
广东工业大学 陈嘉立 李学建
家庭理财，就是通过对家庭财产的妥善管理，达到提高财产效 用，节流增收目的的日常家庭经济活动，是一门新兴的实用型科 学，是以经济学为核心思想、以会计学为理论基础、以财务管理为 手段，以计算机软件为工具的综合型科学。立足于家庭理财的综合 性特点，本文拟对其进行探讨，并建立理财规划分析模型，即家庭 财务记账分析系统。结合家庭自身实际情况，进一步细化和实用 化，利用Ｅｘｃｅｌ实现家庭理财规划分析模型，以便更好地对家庭收 支进行核算、统计和分析，最终达到帮助人们有计划地花费，有效 地控制家庭开支，并落实家庭的储蓄计划和投资计划。为更方便说 明问题和突出重点，本文拟借用某外资企业甲员工的家庭资产、负 债状况和收入、支出数据进行说明。
四、建立家庭现金流量表（月度） 在工作簿中选择一个工作表，将其命名为“家庭现金流量表 （月度）”，相关项目设置如表４所示。
表4
１ ２ ３ ４ ５ ６ ７ ８ ９ １０ １１ １２ １３ １４ １５ １６ １７ １８ １９ ２０ ２１ ２２ ２３ ２４ ２５ ２６
家庭现金流量表（月度）
Ａ
Ｂ
年度
２０１０
（编辑 余俊娟）
·综合 2010 年第 11 期（中） ７
二、负债 信用卡 住房贷款 车辆贷款 应还借款 负债总计
５０００ ３６００００ １２００００
５００ ４８５５００
Ｃ 分析
占资产比重 ４．１５％
６２．２１％ ２３．０４％
２．７６％ ７．８３％
占负债比重 １．０３％
７４．１５％ ２４．７２％
０．１０％
家庭资产负债表统计截至当前时点的资产和负债最终情况。 对于资产类账户，如“现金”项目，对应“金额”为单元Ｂ４，输入目标
１１ ２０１０年１月８日 现金
交际
￥１５０大学同学聚会
１２ ２０１０年１月９日 现金
学习
￥７５购买《人月神话》
１３ ２０１０年１月１０日 应收借款 现金
￥２００同事归还借款
从菜单栏中选择“公式”下的“名称管理器”，点击新建“，名称” 输入“Ｔｉｍｅ”，“引用位置”输入：＝家庭日记账！﹩Ｂ１：﹩Ｂ１００００。同 理，分别新建“Ｏｕｔ”、“Ｉｎ”和“Ａｍｏｕｎｔ”，其对应“引用位置”分别输 入：＝家庭日记账！﹩Ｃ１：﹩Ｃ１００００、＝家庭日记账！﹩Ｄ１：﹩Ｄ１００００和 ＝家庭日记账！﹩Ｅ１：﹩Ｅ１００００。
３５０５０
７７０５０
Ｃ 年度 ２０１０
分析
占总收入比重 ８９．４０％ ０．３８％ ８．７３％ １．４９％
占总费用比重 １２．６６％ ７．６２％ １７．８９％ ２．７２％ ４．３７％ ２．４９％ １３．９２％ ５．５７％ １４．２５％ ４．３１％ １２．９２％ １．２９％
占总收入比重 ６５．６０％
使用者只需要在家庭日记账中录入每天的日记账，Ｅｘｃｅｌ强大 的统计功能即可完成各个分类统计。考虑到非财务类使用者可能 对于“资产、费用类账户，借方登记增加贷方，贷方登记减少；负债、 收入类账户，借方登记减少，贷方登记增加”这一入账规则较难理 解和掌握，“家庭日记账”中采用简单易懂的“从哪个账目支出”和 “流入哪个账目”取而代之。资产负债表根据每一笔家庭日记账的 录入，进行分析统计，核算出实时的资产和负债对应的总账和明细 账目的结果。家庭现金流量表（月度）和家庭现金流量表（年度）只 需要输入指定的年度和月份时间，Ｅｘｃｅｌ会自动按照该时间条件， 归集所有符合条件的数据，进行分析统计，核算出收入和费用对应 的总账和明细账的结果。
家庭现金流量表（年度）
Ａ
Ｂ
项目
五、收入 月薪收入 利息收入 投资收益 其他收入 总收入
六、费用 吃 穿 住 行 通讯 医疗 娱乐 学习 敬老 交际 投资损失 其他支出 总费用
七、上年现金和银行存款
八、净现金流量 上年 ＋ 总收入 － 总支出
金额
１０５０００ ４５０
１０２５０ ７５０
１１７４５０
９５５０ ５７５０ １３５００ ２０５０ ３３００ １８７５ １０５００ ４２００ １０７５０ ３２５０ ９７５０ ９７５ ７５４５０
五、建立家庭现金流量表（年度） 在工作簿中选择一个工作表，将其命名为“家庭现金流量表 （年度）”，相关项目设置，如表５。家庭资产负债表（年度）统计某一 年现金流量状况。如查询了解２０１０年现金流量情况，需要在单元 Ｃ２输入：２０１０。对于收入类账户，如“月薪收入”项目，对应“金额” 为单元Ｂ７，输入目标函数：＝ＳＵＭ（ＩＦ（（ＹＥＡＲ（Ｔｉｍｅ）＝﹩Ｃ﹩２），ＩＦ （Ｏｕｔ＝Ａ７，Ａｍｏｕｎｔ，０），０）），然 后 按“ｃｔｒｌ＋ｓｈｉｆｔ＋ｅｎｔｅｒ”；对 应“ 占 总 收 入比重”为单元Ｃ７，输入：Ｂ７／Ｂ１１。对于费用类账户，如“吃”项目， 对应“金额”为单元Ｂ１４，输入目标函数：＝ＳＵＭ（ＩＦ（（ＹＥＡＲ（Ｔｉｍｅ）＝ ﹩Ｃ﹩２），ＩＦ（Ｉｎ＝Ａ１４，Ａｍｏｕｎｔ，０），０）），然后按“ｃｔｒｌ＋ｓｈｉｆｔ＋ｅｎｔｅｒ”；对 应“占总费用比重”为单元Ｃ１４，输入：Ｂ１４／Ｂ１６。其余同理。上年现
分析
占总收入比重 ８６．７３％ ０．５１％ ７．６５％ ５．１０％
占总费用比重 １６．８４％ ３．６８％ ２０．００％ ６．８４％ ４．７４％ ２．１１％ １５．２６％ ７．３７％ ９．４７％ ７．３７％ ５．２６％ １．０５％
家庭资产负债表（月度）统计某一个月现金流量状况。如查询 了解２０１０年５月现金流量情况，需要在单元Ｂ２输入：２０１０、单元Ｃ２ 输入：５。对于收入类账户，如“月薪收入”项目，对应“金额”为单元 Ｂ７， 输 入 目 标 函 数 ：＝ＳＵＭ （ＩＦ （（ＹＥＡＲ （Ｔｉｍｅ）＝ ﹩ Ｂ ﹩ ２），ＩＦ （（ＭＯＮＴＨ（Ｔｉｍｅ）＝﹩Ｃ﹩２），ＩＦ（Ｏｕｔ＝Ａ７，Ａｍｏｕｎｔ，０），０），０）），然后 按“ｃｔｒｌ＋ｓｈｉｆｔ＋ｅｎｔｅｒ”（此为数组函数的输入方法，以下同）；对应“占 总收入比重”为单元Ｃ７，输入：Ｂ７／Ｂ１１。对于费用类账户，如“吃”项 目 ，对 应“ 金 额 ”为 单 元 Ｂ１４，输 入 目 标 函 数 ：＝ＳＵＭ（ＩＦ（（ＹＥＡＲ （Ｔｉｍｅ）＝ ﹩ Ｂ ﹩ ２），ＩＦ （（ＭＯＮＴＨ （Ｔｉｍｅ）＝ ﹩ Ｃ ﹩ ２），ＩＦ（Ｉｎ＝Ａ１４， Ａｍｏｕｎｔ，０），０），０）），然 后 按“ｃｔｒｌ＋ｓｈｉｆｔ＋ｅｎｔｅｒ”；对 应“ 占 总 费 用 比 重”为单元Ｃ１４，输入：Ｂ１４／Ｂ１６。其余同理。
表1
１ ２ ３ ４ ５ ６ ７ ８ ９ １０ １１ １２ １３ １４ １５ １６ １７ １８ １９ ２０ ２１ ２２ ２３ ２４ ２５ ２６ ２７ ２８ ２９ ３０
参数配置表
Ａ
Ｂ
Ａｃｃｏｕｎｔ
资产账户
现金 银行存款 金融资产 应收借敦 冻结资产
负债账户
信用卡 住房贷敦 车辆贷款 Βιβλιοθήκη Baidu还借款
收入账户
月薪收入 利息收入 投资收益 其他收入
表2
家庭日记账
ＡＢ
Ｃ
Ｄ
Ｅ
Ｆ
Ｇ
１
２ 入账时目 从哪个账目支出 流入哪个账目？ 金额
详细
３ ２０１０年１月１日 银行存款 住房贷款 ￥２５００偿还本月住房贷款
４ ２０１０年１月２日 银行存款 车辆贷款 ￥１０００偿还本月车辆贷款
５ ２０１０年１月３日 银行存款 金融资产 ￥３５００购买渣打银行理财产品
６ ２０１０年１月４日 现金
金和银行存款，对应“金额”需要在单元Ｂ２８输入。净现金流量，对 应金额为单元Ｂ３１，输入：＝Ｂ２８＋Ｂ１１－Ｂ２６，对应“占总收入比重”为 单元Ｃ３１，输入：＝Ｂ３１／Ｂ１１。
表5
１ ２ ３ ４ ５ ６ ７ ８ ９ １０ １１ １２ １３ １４ １５ １６ １７ １８ １９ ２０ ２１ ２２ ２３ ２４ ２５ ２６ ２７ ２８ ２９ ３０ ３１
一、建立参数配置表 在工作簿中选择一个工作表，将其命名为“参数设置表”，相关 项目设置，如表１。对于账户的设置，是结合各个家庭自身特点而确 定，可以在现有账户设置的基础上进行增减。例如，有的家庭喜欢 出行，每年在旅游上有不少消费，则建议在“费用账户”下单独设置 “旅游”账户，独立核算以便进行细化的统计分析，更好把握家庭资 产流向。
三、建立家庭资产负债表 在工作簿中选择一个工作表，将其命名为“家庭资产负债表”， 相关项目设置如表３所示。
表3
１ ２ ３ ４ ５ ６ ７ ８ ９ １０ １１ １２ １３ １４ １５ １６
Ａ 项目
家庭资产负债表 Ｂ 金额
一、资产 现金 银行存款 金融资产 应收借款 冻结资产 资产总计
４５００ ６７５００ ２５０００ ３０００ ８５００ １０８５００
在家庭理财越来越受到重视的今天，利用Ｅｘｃｅｌ建立家庭理财 模型，帮助使用者了解到自己资产、负债现状，以及收入、支出情 况，以便更好控制金钱流向，实现量入为出、有计划地花费，有效控 制家庭开支，真正实现家庭的储蓄计划和投资计划。
参考文献： ［１］王煜：《家庭账户及个人理财研究》，《财会通讯》２０１０年第３ 期。 ［２］郑皓斌：《晋身ＥＸＣＥＬ高手》，上海交通大学出版社２００４年 版。 ［３］陈镇、赵敏捷：《家庭理财》，清华大学出版社２００９年版。