Python数据分析辅助审计工作

65 阅读 0 评论 43 点赞

我是靠谱客的博主俊秀小懒虫，最近开发中收集的这篇文章主要介绍Python数据分析辅助审计工作，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

摘要

审计中遇到管理费用年度内异常波动的问题，依据审计准则，可采用“询问+检查”或者“询问+实质性分析程序”；通常都是采用前一种，但是在时间有限的情况下，实质性分析程序也是可以做到的。

问题描述

一般来说，正常经营没有重大业务变动的公司，其管理费用每月的数额应当相差不大，如果出现波动较大的情况则可能是有某些较大的支出，如果超过了重要性水平则应当引起重视，将原因调查清楚。
我在审计中遇到了上述问题，具体是某上市公司的重要组成部分的管理费用的审计，为了照顾项目组紧张的时间，我执行了“询问相关人员+对管理费用发生额实施实质性分析程序”的审计程序。
问题的关键在于如何具体分析。

建立模型

导出的管理费用明细账的数据结构如下：
管理费用GL（记账凭证编号，日期，摘要，科目名称，借方金额，贷方金额）
将其整理成如下数据结构的：
管理费用GL2（记账凭证编号，科目1借方金额，科目2借方金额，……，科目n借方金额）
转换之后的管理费用GL2暂且称为“原始数据阵”。经过这样转换以后，保证了两个二维表金额合计相等，同时可以满足数据分析的要求——每行为一条记录，每列为一个字段，每列（每个科目的会计记录）都可以看做是该数据矩阵的一个变量（列向量）。
我的分析思路就是采用方差最大化法进行主成分分析，找到对原始数据矩阵的方差影响最大的第一主成分，第一主成分是原始数据阵中各变量的线性组合，最大的系数（最大的特征值）所对应的原始变量的方差对原始数据阵的方差影响最大。
考虑到很多科目之间是有相关性的，比如有职工薪酬就必然有社保啊公积金福利费之类的，所以再进行主成分分析之前，先要进行聚类分析，对原始变量聚类，缩减变量个数。为了使聚类之后的数据矩阵和原始数据矩阵总额相等（不然就不能反映这是管理费用的明细账了），要将聚类之后每个簇所包含的变量相加构造新的变量添加到聚类之后的数据阵中，一起进行主成分分析。

简单地说，将明细账转换为便于分析的数据矩阵，再采用明可夫斯基距离+ward法对变量进行层次聚类得到聚类数据阵，然后采用方差最大化发进行主成分分析，找到第1主成分的特征向量最大的几个分量对应的原始变量，就是引起管理费用年度内每月金额波动的最主要明细科目了。

分析过程

这里展示的只是实质性分析的过程，我上边也提到了，还应当结合询问程序，不过这就是一个审计问题了，在此不再详述。

本着讨论交流和学习的目的，把源代码分享出来，敬请大神斧正。

#欢迎留言交流#

# -*- coding: utf-8 -*-
"""
Created on Sun Jun  2 14:51:19 2019

@author: 一介貂蝉
"""
import numpy as np
import pandas as pd
import xlrd
import xlwt
import matplotlib as mpl
mpl.rcParams['font.sans-serif'] = ['FangSong']
from sklearn import preprocessing

#转换明细账为原始数据阵

dir=r'D:B.MyFilesCaseStudyGLofChewang2017.xlsx'
gl=pd.read_excel(dir,sheet_name='管理费用GL')


gl_good1=gl.loc[:,['具体科目','金额']]
gl_good2=gl_good1.drop(labels=['具体科目','金额'],axis=1,inplace=False)


names=gl.loc[:,'具体科目'].drop_duplicates()
na_list=names.reset_index(drop=True)
na_list=list(na_list)
gl_good2=pd.DataFrame(gl_good2,columns=na_list)

for i in na_list:
    a=gl_good1[gl_good1['具体科目']==i]
    gl_good2.loc[a.index,i]=a.loc[:,'金额']
print('原始数据阵的形状和金额合计：',gl_good2.shape,gl_good2.sum().sum())

good=gl_good2.fillna(0)

#聚类分析，将原始数据阵转换为聚类数据阵

import scipy
from scipy import cluster
from scipy.cluster import hierarchy
from scipy.cluster.hierarchy import *

a=scipy.spatial.distance.pdist(good.T, metric='minkowski')
z=linkage(a,method='ward')
f=dendrogram(z)
f=pd.Series(f)
lf=f['leaves']

li=list(lf)
li1=li[0:7:1]
li2=li[7::1]
g1=good.iloc[:,li1]
g2=good.iloc[:,li2]
print('验证将原始数据阵拆分为未聚类的g1和聚类的g2之和与原始数据阵金额总和是否相等：',g1.sum().sum()+g2.sum().sum())
oth_na=list(g2.columns)
print('g2中的科目合并为科目others：',oth_na)
g2.loc[:,'others']=g2.sum(axis=1)
oth=g2.loc[:,'others']
oth=list(oth)

g1.loc[:,'others']=oth
print('将合并科目others再合并到g1，得到聚类整理之后的数据阵g1的金额合计:',g1.sum().sum())
print(g1.head(3))

#主成分分析，找到管理费用异常波动的原因

sgm=g1.cov()
print('协方差矩阵：',sgm.shape)
tzz,tzxl=np.linalg.eigh(sgm)
tzxl=pd.DataFrame(tzxl)
tzz=pd.Series(tzz)
tzzsort=tzz.sort_values(ascending=False).round(6)

gxl=tzzsort/tzzsort.sum()
gxl_cu=gxl.cumsum()
gxl_cu=pd.DataFrame(gxl_cu,columns=['gxl_cu'])
tzz_se=tzzsort[gxl_cu[gxl_cu['gxl_cu']<0.92].index]
print('累计方差贡献率:',(tzz_se.cumsum()/tzzsort.sum()).round(3))
print('符合条件的特征值:',tzz_se.round(3))

tzxl_se=tzxl.iloc[:,tzz_se.index]
na=list(g1.columns)
tzxl_se.index=na

bdyy=tzxl_se.abs().idxmax(axis=0)
bdyy=pd.DataFrame(bdyy)
print('变动原因',bdyy)

data1=pd.read_excel(r'D:B.MyFilesCaseStudyExpenseofCWHL2017.xlsx')
data1.set_index(['月份'],inplace=True)
print('验证金额是否相等：',data1.sum().sum(),gl['金额'].sum(),good.sum().sum())
data1_oth=data1.loc[:,oth_na]
data1_oth_sum=pd.DataFrame(data1_oth.sum(axis=1),columns=['others'],index=data1_oth.index)

#将分析结果可视化展示

bdyy1=list(bdyy[0])
print('展示如下变量的折线图',bdyy1)

data1_bdyy1=data1.loc[:,bdyy1]
if 'others' in bdyy1:
    data1_bdyy1.loc[:,'others']=data1_oth_sum.loc[:,'others']
fi=data1_bdyy1

fi.plot.line(legend=True,table=True,figsize=(14,6),use_index=True,grid=True,sort_columns=True)
fi.plot.box(legend=True,figsize=(14,6),use_index=True,grid=True,sort_columns=True)
fi.plot.hist(legend=True,figsize=(14,6),use_index=True,grid=True,sort_columns=True)