新浪博客

VBA编程技术在审计实务中的应用

2019-03-06 14:50阅读：

http://blog.sina.cn/dpool/blog/u/1402671703

[摘要] 本文结合审计项目，以实际案例阐述如何利用VBA编程技术创新审计方法，提高审计工作效率，降低审计风险，切实发挥审计职能作用，更好地为企业治理和管理决策服务。
[关键词] VBA；数据处理；审计实务
一、VBA编程技术简介
Visual Basic for Applications(VBA)是Visual Basic的一种宏语言，是微软开发出来在其桌面应用程序中执行通用的自动化(OLE)任务的编程语言。主要能用来扩展Windows的应用程序功能，特别是Microsoft Office软件。由于微软Office软件的普及，人们常见的办公软件
Office软件中的Word、Excel、Access、Powerpoint都可以利用VBA语言，使这些软件的应用更加高效。
Excel 是微软办公套装软件的一个重要的组成部分，它可以进行各种数据的处理、统计分析和辅助决策操作，广泛应用在众多领域。Excel提供的VBE环境，提供了VBA代码设计环境，可对Excel表格中的数据进行处理。本案例使用的是Excel2007版。
二、利用VBA编程技术开展审计工作背景
对某企业开展产品价格折让政策执行情况管理效益审计项目时，审前调查阶段发现价格折让销售业务所涉及的相关数据较完整齐全，但数据量大，总数据量近25万条，如果按照常规审计中人工查找分析方法进行审计，面对如此大量数据不仅审计人员力量不足，而且工作量庞大；若采取抽样审计的方法，抽样率10%时，抽样数据量也达两万多条，按时完成审计任务难度很大。因此迫切需要创新审计方法，提高审计工作效率，经过审慎考虑，最终决定尝试采用大数据思维方式，利用Excel提供的VBE环境，使用VBA语言，编制程序代码，对全部数据进行加工处理，在提高工作效率的同时，由总体性分析取代抽样分析，力求更全面、更深刻地掌握审计业务实质，有效把握审计风险。
三、VBA编程技术应用于审计实务的具体做法
（一）梳理价格折让业务流程，确定数据间的逻辑关系
价格折让业务流程大致如下：每月价格管理部门依据ERP系统销售订单完成情况，计算客户计划完成相关数据，销售部门据此编制客户月度考评明细表，确定客户是否达标，计算客户应享受价格折让金额等，并向价格管理部门提报客户折让价格申请表，价格管理部门据此编制折让价格文件，折让价格文件经审批后由销售部门在ERP系统中通过执行折让销售订单方式完成对客户折让兑现工作。根据这一流程主线，数据关系是：ERP销售订单数据和客户计划完成数据产生达标客户应享受折让金额数据，折让价格申请表产生折让价格文件，执行折让价格文件的销售订单产生了折让销售订单数据。理清数据关系对于下一步VBA编程设计获取审计疑点十分必要。
（二）整理中间表数据、进行数据格式化
首先针对客户折让考评结果数据和折让价格文件数据，按照月度将分散的电子版数据进行汇总，由于这些数据来自各销售部门，相关客户名称、产品牌号、订单日期、订单数量等信息是否准确，需要进行大量数据格式化和规范化工作。客户应享受折让金额数据和折让价格文件这两个重要的数据中间表，是向客户价格折让的目标数据；其次，自ERP系统导出销售订单数据和客户计划完成数据，整理成适合VBA语言处理的格式化中间表数据，这两个中间表数据是客户价格折让的结果数据。
VBA程序处理的对象是这些中间表数据，中间表数据是格式化后的原始数据，继承了原始数据的有效属性。
（三）进行VBA代码编程，处理数据，确定审计疑点
以处理中间表数据为计算基础，以数据间的逻辑关系为主线，确定查找审计疑点的VBA编程思路。
1.在客户月度考评结果明细表审查环节，需要以ERP实际订单数据为依据，通过循环方式逐条核查前后两期的客户上半月和月度销量、上半月和月度销售计划执行率、客户月均销量、客户级别与折让标准、达标客户应享受折让额度等相关数据。审计人员在VBA程序中设置了的代码用于获取相关审计疑点，例如：在判断客户上半月销量数据的填报数据是否与ERP中的实际数据一致时，通过以下VBA代码设计，有效确定审计疑点。
If fgsst.Cells(j, 'Q').Value = dqxl Then '当ERP订单中客户上半月实际销量等于该表中填报数据时
fgsst.Cells(j, 'Q').Interior.ColorIndex = 4 '数据一致，设置成浅绿色，视同没有问题
Else
fgsst.Cells(j, 'Q').Interior.Color = 65535 '数据不一致，定位审计疑点， 将颜色设置成黄色
Set rng3 = pzdelete(fgsst.Cells(j, 'Q')) '若该表上半月实际销量处有批注，执行删除批注子程序
Set rng2 = zrpz(fgsst.Cells(j, 'Q'), CStr(dqxl), '前半月销量应为：') '执行增加批注子程序，增加提示批注，将正确数据提示出来

End If
VBA编程技术在审计实务中的应用

以上截图为某具体结果情况。
在VBA代码编程过程中，使用工作表函数，可以减少代码编程工作量，起到事半功倍的作用。以下是在满足六个条件情况下，通过调用工作表sumifs()函数，计算某客户上半月销量和的VBA程序代码。
dqxl = Application.WorksheetFunction.SumIfs(ddst.Range('H2:H' & lngd), _ '求和列
ddst.Range('B2:B' & lngd), fgsst.Cells(j, 'C'), _ '客户
ddst.Range('R2:R' & lngd), moy, _ '月份
ddst.Range('Q2:Q' & lngd), '2017', _ '年度
ddst.Range('T2:T' & lngd), 'Y', _ '通过日期确定是否上半月
ddst.Range('U2:U' & lngd), xspd, _ '产品大类
ddst.Range('J2:J' & lngd), xsdwmc) '销售部门
2.在审查折让价格文件执行效果时，由于ERP销售订单数据既包含折让销售订单，也包括非折让销售订单，因此需要将折让价格文件中的相关客户名称、产品牌号、折让单价、订单数量等关键数据在ERP销售订单数据中进行定位查找，查找到相关结果后需确认满足条件的订单次数和订单总销量，再与价格文件规定的要素比较，通过确认是否执行折让单价、是否多次折让、是否折让金额相同、是否超过规定数量等情况的判断，达到获取审计疑点的目的。以下为通过ERP系统订单销量与价格文件规定的销量进行对比后确定审计疑点的相关VBA代码设计思路。
For Each rng In rngFindData '遍历折让价格文件中客户区域
For Each rngD In rngData '遍历ERP订单数据库中客户区域
If rng.Value = rngD.Value Then '客户名称相同
If rng.Offset(0, 7).Value = rngD.Offset(0, 10).Value Then '价格相同
If rng.Offset(0, -2).Value = rngD.Offset(0, 3).Value Then '牌号相同
date_start = DateValue(rng.Offset(0, 8).Value) '价格文件规定生效范围的起初日期
date_end = DateValue(rng.Offset(0, 9).Value) '价格文件规定生效范围的截止日期
date_data = DateValue(rngD.Offset(0, 12).Value) 'ERP订单数据中的结算日期
If date_data >= date_start And date_data <= date_end Then '判断ERP订单数据的
结算日期在价格文件规定的有效范围
Str = Str & CStr(rngD.Offset(0, 15).Value) & ';' '获取ERP订单信息
sxl = sxl & CStr(rngD.Offset(0, 5).Value) & ';' '获取ERP销量信息
xl = xl + Val(rngD.Offset(0, 5).Value) '满足条件的订单销量求和
num = num + 1 '满足条件的订单发生次数
If xl > 0 Then '满足条件的订单销量和大于零
If xl =rng.Offset(0, 2).Value Then '订单销量之和等于价格文件规定折让销量
rng.EntireRow.Interior.ColorIndex = 8 '正确执行折让价格，设成浅蓝色
rngD.Offset(0, 8).Value = '折让订单' '在ERP订单中识别出折让销售订单属性
Else
If xl<> rng.Offset(0, 2).Value Then 'ERP订单销量不等于规定数量
rng.EntireRow.Interior.ColorIndex = 38 '确定为审计疑点，同时将
价格文件整行设置成粉色
End If
End If
End If
End If
End If
End If
End If
Next rngD

Next rng
VBA编程技术在审计实务中的应用

从以上截图看，发现该客户价格文件规定销售数量应为30吨，VBA程序计算结果为33吨，确定为审计疑点。
程序设计是按照月份为计算频次进行疑点筛查，将某月所有销售部门折让价格文件执行环节的检查可以在3分钟左右时间内完成，对比人工排查方式，极大地提高了工作效率。

3.将客户月度考评结果明细表和折让价格文件执行后确定的折让订单数据进行表格化，生成直观的各年度客户价格折让兑现表（参见表一），据此再生成按客户统计的各年月折让兑现总表（参见表二），分析客户的各年度兑现情况，为分析疑点和判断问题提供依据。观察表二数据，发现该客户在2016年7月在折让申请额度为“零”时向其兑现价格折让6440.00元，确定为审计疑点。 </a>

我的更多文章

下载客户端阅读体验更佳

APP专享