项目二日记账管理系统的建立.docx

1、项目二日记账管理系统的建立项目二 日记账管理系统的建立工作目标设置和登记账簿，是企业会计核算处理流程的一项核心工作，是企业会计处理的一个重要环节。会计账簿是由具有专门格式、相互联系的账页组成，按照一定的程序排列或装订在一起，用以序时、分类、连续地记录和反映企业的各项经济业务的发生和完成情况的簿记。我们将账务管理系统分为日记账和分类账两部分，本项目的主要内容就是介绍利用Excel进行日记账管理的方法和步骤。通过完成本项目的五个工作任务，学习者能够进行日记账系统工作簿的制作；并从中学习掌握Excel中公式、函数及宏的基本操作方法。工作思路本项目的工作思路是首先建立日记账管理系统工作簿，完成该系统的

2、首页页面的设计；制作或引用日记账账务处理数据源；创建现金日记账；创建现金日报表；创建银行存款日记账；创建银行存款余额调节表。项目二工作思路如图所示。背景资料资料1 某公司是家商业销售企业，2008年1月初“库存现金”的余额为2 500元，“银行存款”的余额为168 900元。 资料2 2008年1月发生与现金有关的业务如下： (1)1月3日，收到销售商品的营业款35 000元。(2)1月3日，业务员张某报销通讯费300元。(3)1月3日，退还供应商A的商品保证金20 000元。(4)1月3日，收到赊销款25 000元。(5)1月3日，从银行提取现金50 000元，用于发放工资。(6)1月3日，

3、发放工资50 000元。(7)1月3口，将库存现金40 000元存人银行。(8)1月3日，收到李某个人还款500元。(9)1月4日，业务员王某报销差旅费l 200元。(10)1月4日，收到销售商品的营业款20 000元。 资料3 2008年1月发生与银行存款有关的业务如下：(1)1月3日，从银行提取现金50 000元(现金支票9558#)，用于发放工资。(2)1月3日，将库存现金40 000元存人银行。(3)1月6日，收到银行转来的资金汇划补充凭证，金额200 000元，为购货单位电汇来的货款。(4)1月7日，向外地供货单位电汇商品及增值税款8l 900元。(5)1月10日，购买银行转账支票等

4、结算凭证，支付200元。(6)1月15日，开出转账支票(6370#)，将46 200元转账用于补缴税款。(7)1月20日，开出现金支票(9559#)一张，提取现金5 000元备用。(8)1月21日，拟到外地采购特向银行申请签发银行汇票100 000元，银行给予签发(银行汇票743#)并收取全额保证金。(9)1月23日，因生产经营需要向银行贷款500 000元，根据借款合同，银行将款项划转到账。(10)1月28日，收到银行特种委托收款的付款通知(7543#)及电信收费单据，支付电话费5 340元。 资料4 2008年2月3日收到银行发来的1月份银行对账单，如表所示。XX银行对账单客户名称： 20

5、08年1月月日摘要借方贷方余额13上月结存13现支9558#13现缴16汇划17电汇110结算凭证116转支6370#120现支9559#121银汇6743#123贷款128特委7543#131汇划进账131特委8742#131月末余额工作准备首先建立一个名为“班级 姓名 项目二.xls”的工作簿，在此工作簿中建立“页面”、“现金日记账”、“现金日报表”、“银行存款日记账”、“银行存款余额调节表”、“银行存款类别定义”、“现金类别定义”等工作表，各工作表分别完成相应的工作任务。下面分别介绍各工作表的设计方法和步骤。工作任务一 页面的设计给项目二日记账管理工作簿增加“页面”，我们可以将项目一中的

6、会计凭证系统的首页进行复制，并进行相应的更改，这样能减少一些工作量，具体步骤如下： (1)打开“项目一”工作簿，单击“页面”工作表。(2)将鼠标指针移至工作表标签处，点击鼠标右键，选择“移动或复制工作表”命令。点击【确定】，则整张“页面”工作表就复制到“项目二”工作簿中。(3)修改艺术字并调整位置 （4）建立超链接工作任务二 现金日记账的创建现金日记账是由出纳人员根据审核后的现金收、付款记账凭证和银行付款凭证，逐日逐笔按顺序登记和反映现金增减变动情况的一种特种日记账。每笔业务发生后，需要计算余额；在每日收付款项逐笔登记完毕后，应分别计算现金收入和现金支出的合计数及账面余额，并与库存现金实存数相

7、核对，以此检查每日现金收支和结存的情况，最终形成现金日记账簿。下面介绍现金日记账的设置方法。项目活动一 现金日记账的结构设计设计现金日记账工作表格式的具体步骤如下：(1)打开工作簿“项目二”，选择工作表“现金日记账”。(2)在单元格B2中输入“现金日记账”，选取单元格区域B2：I2，单击工具栏上的【合并及居中】按钮，再右键设置单元格格式，打开【设置单元格格式】对话框，单击【字体】选项卡，将“字体”设置为“楷体”，将“字号”设置为“18”，将“下划线”设置为“双下划线”，将“颜色”设置为“绿色”。单击【确定】按钮。(3)在单元格B3中输入：“2016年”，选择单元格B3：C3，单击工具栏的【合并

8、及居中】按钮。(4)在单元格B4、C4中分别输入“月”、“日”。(5)在单元格D4、E4、F4、G4、H4、I4中分别录入“凭证编号”、“类别”、“摘要”、“借方”、“贷方”和“余额”。(6)分别选择单元格区域D3：D4、E3：E4、F3：F4、G3：G4、H3：H4、I3：I4，单击【格式】工具栏的【合并及居中】按钮，结果如图所示。(7)选中第3、4行，右键打开【设置单元格格式】对话框，单击【对齐】选项卡，设置“水平对齐”、“垂直对齐”居中（如图所示)；单击【字体】选项卡，将“字体”设置为“宋体”，将“字形”设置成“加粗”，将“字号”设置为“12”，将“颜色”设置为“绿色”(如图所示)，单击

9、【确定】按钮。(8)选择单元格区域B3：I70，单击菜单【格式】一【单元格】命令，打开【单元格格式】对话框，单击【边框】选项卡，设置“线条颜色”为“绿色”，设置“线条样式”为“粗实线”，单击“上边框”和“下边框”；设置“线条样式”为”细实线”，单击“内部边框”(如图所示)；单击【确定】按钮。(9)选择单元格区域B3：I4，打开【设置单元格格式】对话框，单击【边框】选项卡，设置“线条颜色”为“绿色”，设置“线条样式”为“粗实线”，单击“下边框”；单击【确定】按钮。(10)适当调整各列宽度。完成现金日记账的结构设计。项目活动二 设置数据有效性为了提高输入数据的效率，可以对一些单元格设置数据有效性。

10、下面介绍现金日记账“类别”这一列的数据有效性设置，其具体步骤如下：(1)打开工作簿“项目二”，选择工作表“现金类别定义”。(2)在列A输入如右图所示的文字。 (3)返回“现金日记账”表单，选择“类别”所在的E列，单击【数据】菜单中的【数据有效性】命令，打开【数据有效性】对话框；设置“允许”为“序列”(如图)；将光标移到“来源”位置，输入“=现金类别定义!$A$1:$A$10”，再单击【确定】按钮退出【数据有效性】对话框。(4)选择单元格E5，在E5单元格的右面便会出现一个下拉箭头，单击下拉箭头，便可选择相应的类别。给“类别”列设置了有效性以后可以省去打字的麻烦，同时也避免了录入错误。同样，我们

11、也可以给“银行存款日记账”的“类别”设置有效性检查。在表单“银行存款类别定义”的A列输人如图所示内容。项目活动三 设置条件格式我们可以通过添加条件格式的方法，给特定单元格设置醒目的格式，以方便用户查阅。具体步骤如下：（1） 在“现金日记账”表单选择单元格F5，单击开始选项卡中的【条件格式】命令，打开【新建格式规则】对话框。规则类型选择【使用公式确定要设置格式的单元格】，在公式编辑栏输入条件公式“=OR($F5=本日合计,$F5=本月累计,$F5=期初余额)”（2）打开【设置单元格格式】对话框，选择字体颜色为“红色”。（4）设置“线条样式”为“细实线”，设置“线条颜色”为“红色”，然后选择“下边

12、框”。（5）单击【常用】工具栏上的【格式刷】按钮，然后选择要设置格式的单元格区域B5：I70。设置了上述条件格式以后，当在“摘要”栏（F列）输入“期初余额”、“本日合计”、“本月累计”字样的时候，字体颜色自动设置成醒目的红色，并且该行的下边框也变成红色，而其他的地方保持不变。项目活动四 余额自动计算本活动将学习余额的自动计算，每笔业务发生后，应将上次的余额加上本次的借方金额，减去贷方金额，得到本次的余额。（1）输入期初余额和本月发生的第一笔现金业务。（2）在单元格I6输入公式“=IF(AND(G6=,H6=),I5+G6-H6)”，按回车自动计算出余额。（5）依次输入背景资料中1月3日发生的现

13、金业务，形成现金日记账。项目活动五 计算本日合计和本月累计每日营业终了，需要计算当日的合计数和本月的累计数。按项目活动四的例子，计算本日合计和本月合计的详细步骤如下：(1)在单元格F14输入“本日合计”，在单元格G14输入“=SUM(G6：G13)”，并将公式填充到H14，形成本日合计的结果。(2)单元格F15输入“本月累计”，在单元格G15输入“=sum(G6:G13)”，并并将公式填充到H15，形成本月累计的结果。(3)从图中可以直观看出，本月合计和本月累计行的余额显然是错误的。错误原因在于余额列的计算公式为“=if(and(G14=”,h14=”)，”,I13+G14-H14)”和“=i

14、f(and(G15=”,h15=”)，”,I14+G15-H15)”，合计和累计本来不该继续计算的，它们也进行了一次计算，而实际上它们的值应该等于I13单元格的值。所有我们应该对公式进行一定的修改。选中I6单元格，把公式改为“=IF(AND(G6=,H6=),IF(OR(F6=本日合计,F6=本月累计,F6=本年累计),I5,I5+G6-H6)”，并复制到单元格I7:I70区域，从而达到每一行都能够正确显示余额的目的。(4)依次输入背景资料中1月4日发生的现金业务，形成现金日记账。(5)在单元格F19输入“本日合计”，在单元格G19输入相应公式，并将公式复制到单元格H19，形成本日合计的结果。

15、在单元格F20输入“本月累计”，在单元格G20输入相应公式，并将公式复制到单元格H20，形成本月累计的结果。工作任务三 现金日报表的编制现金日报表是企业管理部门为了及时掌握和了解现金的流动情况而要求出纳人员每口提供的报表。现金日报表的编制不仅为企业现金的管理提供了方便，而且为管理者及时了解和掌握本企业的资金状况和合理运用资金提供了参考数据。下面介绍现金日报表的编制方法。项目活动一 现金日报表的格式设计在Excel上设计的现金日报表工作表具体的设计步骤如下：(1)在单元格B2中输入“现金日报表”，选取单元格区域B2：G2，单击【格式】工具栏的【合并及居中】按钮，再单击菜单【格式】-【单元格】，打

16、开【单元格格式】对话框，单击【字体】选项卡，将“字体”设置为“楷体GB2312”，将“字号”设置为“l8”，将“下划线”设置为“双下划线”，将“颜色”设置为“绿色”，单击【确定】按钮关闭【单元格格式】对话框。现金日报表标题设置完成。(2)在单元格B4中输人“2008- l-3”，选择单元格B4：F4，单击【格式】工具栏的【合并及居中】按钮，再单击菜单【格式】-【单元格】，打开【单元格格式】对话框，单击【数字】选项卡，分类列表选择“自定义”。类型输人“日期!：yyyy年m月d日”。单击【确定】按钮，关闭【单元格格式】对话框。现金日报表日期设置结束。(3)在单元格G4中输入“单位：元”，并设置其格

17、式。现金日报表表头设置完成。(4)选择单元格B6：C7，单击【格式】工具栏的【合并及居中】按钮，输入“项目”并设置其格式。(5)选择单元格D6：F6，单击【格式】工具栏的【合并及居中】按钮，输入“金额”并设置其格式。(6)在单元格D7、E7、F7中分别录入“本日”、“本月累计”、“本年累计”并设置其格式。(7)选择单元格G6：G7，单击【格式】工具栏的【合并及居中】按钮，输人“备注”并设置其格式。(8)选择单元格B8；B13，单击【格式】工具栏的【合并及居中】画按钮，输入“本日收入”并设置其格式。(9)在单元格C8：C13中分别输入“营业款”、“个人还款”、“保证金收人”、“取款”、“其他收入

18、”、“本日收入合计”并设置其格式。(10)选择单元格B14；B19，单击【格式】工具栏的【合并及居中】按钮，输入“本日支出”并设置其格式。(11)在单元格C14：C19中分别输人“费用报销”、“个人借款”、“存款”、。退还保证金”、“其他支出”、“本日支出合计”并设置其格式。(12)选取单元格区域B20：C20，单击【格式】工具栏的【合并及居中】按钮，输入“昨日现金余额：”并设置其格式。合并D20：G20。(13)选取单元格区域B21：C21单击【格式】工具栏的【合并及居中】按钮，输入“本日现金余额：”并设置其格式。合并D21：G21。(14)点击菜单【工具】-【选项】，在视图对话框中取消网格

19、线选项。最终格式效果图如下：项目活动二 现金日报表数据的自动生成我们可以利用Excel公式和函数的操作，将现金日记账的数据引用过来，完成现金日报表数据的自动生成，具体步骤如下： ，(1)选择单元格D8，输入公式“=SUMPRODUCT(现金日记账!$B$6:$B$70=MONTH(现金日报表!$B$4)*(现金日记账!$C$6:$C$70=DAY(现金日报表!$B$4)*(现金日记账!$E$6：$E$70=C8)*现金日记账!$G$6：$G$70)”。公式的含义为：当现金日记账单元格区域B6：B70的值为现金日报表单元格区域D4月份值、现金日记账单元格区域C6：C70的值为现金日报表单元格区域

20、D4的天数值、现金日记账单元格区域E6：E70的值为现金日报表单元格区域C8的值(营业款)时，现金日报表单元格区域D8的值为现金日记账单元格区域G6：G70的正总数。应特别注意公式中的绝对引用和相对引用。(2)选择单元格D8，使用填充柄向下填充到D9：D12。(3)选择单元格D13，输入公式“=SUM(D8：D12)”。并填充到E13：F13。(4)选择单元格D14，输入公式“=SUMPRODUCT(现金日记账!$B$6:$B$70=MONTH(现金日报表!$B$4)*(现金日记账!$C$6:$C$70=DAY(现金日报表!$B$4)*(现金日记账!$E$6:$E$70=C14)*现金日记账!

21、$H$6:$H$70)”。(5)选择单元格D14，使用填充柄向下填充D15：D18。(6)选择单元格D19，输入公式“=SUM(D14:D18)”，并填充到E19：F19。(7)本月累计数只要把本日列公式中天数的条件“=”改成“=”。本年累计只要把本日列公式中月份的条件“=”改成“”，然后加E本月累计数即可。单元格E8的公式为“=SUMPRODUCT(现金日记账!$B$6:$B$70=MONTH(现金日报表!$B$4)*(现金日记账!$C$6：$C$70=DAY(现金日报表!$B$4)*(现金日记账!$E$6:$E$70=C8)*现金日记账!$G$6:$G$70)”。单元格F8的公式为“=SU

22、MPRODUCT(现金日记账!$B$6:$B$70MONTH(现金日报表!$B$4)*(现金日记账!$E$6:$E$70=C8)*现金日记账!$G$6:$G$70)+E8”。(8)选择单元格D21，输人公式“=现金日记账!I5+现金日报表!F13-现金日报表!F19”。(9)选择单元格D20，输入公式“=D21-D13+D19。(10)日报表的数据生成完毕，形成2008年1月3日的报表，如图。(11)选择单元格D4，输入新的日期“200B-1-4”，自动生成1月4日的现金日报表，如图。工作任务四 银行存款日记账的创建银行存款日记账是由出纳人员按照银行存款的收，付款记账凭证逐日逐笔顺序登记，用以

23、记录和反映银行存款收支及结存情况的一种特种日记账。银行存款日记账一般采用借方、贷方、余额三栏式账页格式，分别反映银行存款收入、付出和结存情况，以便检查监督各项收入和支出款项，并便于定期同银行送来的对账单核对。下面介绍银行存款日记账的设计方法和步骤。项目活动一 银行存款日记账的结构设计银行存款日记账工作表格式与现金日记账基本相同，因此可以复制一份“现金日记账”工作表，再对工作表进行修改具体步骤如下：(1)删除现存的“银行存款日记账”工作表。(2)选择“现金日记账”工作表。(3)用鼠标对准“现金日记账”工作表标签，单击鼠标右键在弹出的快捷菜单中选择“移动或复制工作表”命令，打开【移动或复制工作表】

24、对话框。在“下列选定工作表之前”列表框中选择“银行存款类别定义”，选中【建立副本】复选框，单击【确定】按钮，关闭【移动或复制工作表】对话框，就建立了一个名为“现金日记账(2)“的工作表。(4)将工作表“现金日记账(2)”重命名为“银行存款日记账”(5)将工作表中的有关文字进行修改，例如，将“现金日记账”改为“银行存款日记账”。(6)在E列和F列之间插入两列，选中E、F、G列，单击菜单【数据】-【有效性】，取消之前设定的有效性规则。选择【全部清除】，如图：合并F3:F4，G3:G4，在合并后的单元格F3和G3中分别输入“结算方式”和“票据号码”。清空B5:K70的数据。 (7)选取F列，对其设置

25、数据有效性，即在【有效性】对话框中，在【设置】选项卡的“允许”中选“序列”，在“来源”中输人“现金,转支,现支,信汇,电汇,本票,委托收款,专用收款”(注意：此处逗号是英文逗)，可以根据企业的实际情况来输入不同的数据在【输人信息】选项卡的“输入信息”文字框中输入“输入结算方式种类”，在【出错警告】选顼卡的“样式”中选“警告”。(8)选取G列，对其设置数据有教性，即在【有效性】对话框中，仅在【输入信息】选项卡的“输人信息”文字框中输入“输入结算票据号码”，其他保持系统默认值。项目活动二 设置条件格式银行存款日记账的设置条件格式的方法和步骤在工作任务二现金日记账项目活动三中已有详细介绍，可参照设计

26、。项目活动三 余额自动计划银行存款日记账余额自动计算的具体方法和步骤在工作任务二现金日记账项目活动四中已有详细介绍，可参照设计。项目活动四 计算本日合计和本月累计银行存款日记账计算本日合计和本月累计的具体方法和步骤在工作任务二现金日记账项目活动五中已有详细介绍，可参照设计。项目活动五 输入银行存款日记账按【资料3】的内容输入银行存款日记账。实操练习【资料】天利公司在2010年1月和2月发了如下和现金有关的业务：（1）2010年1月1日从上午结转过来的现金余额为3,630元。（2）2010年1月3日使用现金支付招待费3,000元。（3）2010年1月14日提取现金90,000元。（4）2010年1月15日支付工资90,000元。（5）2010年1月20日提取现金9,000元。（6）2010年1月22日支付劳保费用9,000元。（7）2010年2月1日销售部门出售包装废品4,600元。（8）2010年2月2日付差旅费1,500元。要求：1 通过直接输入的方法和记录单的方法将上述内容输入到现金日记账中。2 根据以上现金业务，编制一个使用函数和公式创建的现金日记账账页。3 以“项目二天利公司现金日记账”为名保存在自己的目录下。