本帖最后由 ofhzxzg 于 2013-1-6 09:28 编辑
又到了一年一度的年报审计时间，在去年的基础上，本人对银行询证函的制作又进行一些优化，希望对各位从事审计工作的同仁有所帮助，同时也欢迎高手能进一步优化和指正。
一、银行审计中询证函的特点：（银行审计中询证函，指向借款人寄发的针对借款的询证函）
1、数量大。一般企业审计询证函往往只有十几张，而银行审计中，对贷款函证涉及客户往往超过上千个，记录个数超过上万个，必须借助自动化手段方能完成；
2、记录个数超过1个，即1个借款人往往有多笔贷款记录，而这多笔贷款记录需要合并在1张询证函上，及1个借款人1张询证函，这对通过邮件合并手段自动生成询证函造成了一定的困难，对此，我去年已经发表过文章；
3、存在重名客户情况（自然人客户），故对数据的前期处理提出一定的要求。
二、准备工作(本段系审计知识，一般可略过）
1、取得截止审计日所有贷款明细记录，字段很多，各银行不同，但至少要包括询证函上需要的字段，一般有：机构名称（贷款的支行或分理处）、客户号（唯一字段，区分同名同姓的客户靠他了），客户名称、贷款余额、担保方式，利率、贷款发放日期、贷款到期日期；
2、验证取得的贷款明细记录的正确性：将所有客户“贷款余额”加总数与资产负债表中的“发放贷款与垫款”-“贷款损失准备”核对一致，如有委托贷款，应去除；
3、制定样本选取方案：如选取函证客户的贷款余额占所有贷款余额的百分比，结合当前监管重点，确定重点选择的行业（可以根据行业字段，或客户名称中的关键字），如地方融资平台客户、钢贸、纺织、光伏等行业。
三、样本选取（本段可理解为业务模型，即对数据处理的要求为：按某字段分类并将另一字段加总，显示时除刚才2个字段外，还需要其他字段）
因为取得的贷款明细记录，即不能直接得到各客户的借款总额，故需要对贷款明细记录按客户进行汇总；又因为存在重名客户情况，故只能按客户号而不能按客户名称进行汇总；但在汇总这张表中，因为后续选取样本的需要，又要显示客户名称。这样的业务需求，好像只能数据透视表能完成。
1、对贷款明细记录执行数据透视表，在“布局”中"行"放入”客户号“,"为客户号，”数据“放入”（求和项）贷款余额“，此时便得到按客户号汇总的借款金额，但尚未排序；
2、任意点击一个汇总金额，之后点菜单中的”数据“-”排序“，再按降序即可。此时我们便得到了按客户号汇总的借款金额，并从大到小进行排序；
3、在按数据透视表-布局，在”客户号“下放入”客户名称“，确定。此时的表格，变成三列，分别为客户号、客户名称、借款金额，从大到小进行排序，但有汇总行；
4、按任意一行汇总行，按右键，点”隐藏“，此时所有的汇总行全部消失。之后选择全部记录，拷贝粘贴形成一张新的SHEET；
5、在新的SHEET中按当初制定的样本选取方案选取函证客户。选择后，则在该行中最后1列做个记号，列如”1“。
6、在贷款明细记录中的最后1列，写一个函数：VLOOKUP(A2,Sheet2!$A$2:$D$10837,4,FALSE)，当然参数根据情况进行修改，此时，便完成了贷款明细记录中选取函证对象。下拉公式小技巧：第1行公式写完后按”复制“，之后，按住SHIFT,按该列最后1行，再：选择性粘贴-公式，即完成下拉公式。
7、贷款明细记录选取”1“的记录，形成函证客户总表，之后通过”机构名称“，按支行形成SHEET，因为询证函需按支行发放；这个可以优化，好像可以自动生成。
四、询证函的制作（本段可理解为业务模型，即需要将EXCEL记录与WORD文档合并，且同一文档有多个记录）
1、对数据源的编辑：（指按支行形成的SHEET）
添加两个字段：判断一：使用IF函数，如该条记录为借款人最后一条记录，则显示0，否则显示1。判断二：使用COUNTIF函数，计算出该借款人在数据源中共有几条记录。当然，在添加这两个字段之前，要先对借款人之一字段进行排序。
2、对主文档的编辑 1)进行邮件合并，按菜单中“工具→信函与邮件→邮件合并”，之后按提示逐步进行：文档类型：信函；使用当前文档；收件人：使用现有列表；选择数据源，即选择主文档（本例即为“询证函模板”）要合并的数据源（本例即为“test.xls）。之后，逐一将光标停留在主文档中要插入数据的地方，之后选择“其他项目”，将数据插入主文档中。其中主文档中“编号”使用“插入→域→autonum”即可实现自动编号，当然也可以在数据源中编号，直接插入。在主文档表格中先插入2行，然后按ALT+F9,此时可以看到主文档中显示的域函数。之后便开始对表格中域函数进行编辑，以达到同一借款人多条贷款数据的合并。 2)在主文档表格中，选择第二行第一列，对其中的域函数进行编辑。按“邮件合并工具栏”中的“插入WORD域”→“下一条记录条件”，（如没有该按钮，可按菜单中“工具→信函与邮件→邮件合并→显示邮件合并工具栏”）域名选择“判断一”，比较条件选择“等于”，比较对象录入“1”。之后便逐步录入“IF”域函数，其中嵌套“SEQ”和“MERGEFIELD”函数。其中“SEQ”函数为插入一个序列号，后面可以为任意字符，但必须以字母开头，加上域开关“\C"为重复上一个序列号。在本例中该函数计算结果第二行为1，第二行为2，以此类推。在输入域函数时，记得域函数中的大括号是要按CTRL+F9，而不能通过键盘录入。第二行第一列编辑完成后，便编辑第二列，第三列，直至完成。具体可详见附件中的公式。（如看不到公式，按ALT+F9）。第二行编辑完成，便复制至第三行，第四行，直至全部。 3)按“邮件合并工具栏”中的“查看合并数据”按钮，（如没有该按钮，可按菜单中“工具→信函与邮件→邮件合并→显示邮件合并工具栏”），此时显示的数据是不对的，如编号未自动递增，同一借款人的多条记录也未合并。 4)按“邮件合并工具栏”中的“修正错误”按钮，根据需要选择一个，皆可。此时，生成的新文档便对了，即同一借款人的多条记录得到合并。共8个借款人，生成8页的文档。 5)如要对数据格式进行编辑，可添加域开关。如本例中的贷款数据要显示20,000.00元，即添加域开关“\#,##.00” 即可。 小技巧：多节文档中指定范围页码打印的问题。在打印时表示某一具体页码的完全方式是pnsn，其中前后两个n分别是节中的页和该页所在的节。如果想打印整节就用sn表示，如要打印第2节的内容，打印范围输入s2即可。 五、询证函目录的制作 这个简单，针对支行形成SHEET，重复第三单点”样本选取“中的1-4步即可。
| 
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2013-1-5 21:15
