Excel建了个客户信用打分模型,一跑揪出3个高危客户,欠款280万

有个公司的应收账款坏账率连续两年上升，老板问内审负责人："能不能提前预警？别等客户跑路了才发现。"

内审负责人说："我们又没有大数据系统，怎么预警？"

后来他花了三天时间，用Excel搭了一个客户信用评分模型，把全量客户跑了一遍。排在前面的3个客户，总分都低于4分（满分10分）——三个月后，其中一个客户失联，欠款87万；另一个逾期坏账，欠款143万。

280万坏账，提前一个月就看到了信号。

别被"模型"两个字吓到。今天咱不聊机器学习、神经网络，就用Excel能做的方法，帮你搭一个实用的、能解释的风险预警工具。以客户信用评分模型为例，学完你就能举一反三，用到供应商风险评分、报销异常评分等场景。

模型到底是什么？就是一个打分系统

风险预警模型的本质很简单：基于历史数据，给每个评估对象打一个"风险分"，分数越高说明风险越大，优先关注。

你现在的工作方式：看报表，凭经验判断"这个客户好像不太对劲"，去查。模型的工作方式：系统自动算每个客户的分数，自动排序，你只查分数最高的那些。

模型的价值不是替代你的判断，是把你的注意力引导到最值得关注的地方。

建模三步法，一步步来

第一步：确定评估维度——从哪些角度看这个客户

客户信用风险通常从几个维度看：

还款意愿——客户想不想还钱？看历史逾期次数、逾期天数。

还款能力——客户有没有能力还钱？看资产负债率、现金流情况。

经营稳定性——客户会不会突然倒闭？看经营年限、收入波动性。

交易行为——跟我们的交易有没有异常？看采购量趋势、退货率。

外部负面信息——外部有没有不利信号？看涉诉记录、失信被执行。

第二步：选取具体指标——用什么数字来衡量

从每个维度里选2到3个可量化、可获得的指标。以下是一套我实战中用过且好用的方案。

还款意愿（权重30%）

- 过去12个月逾期次数：0次10分，1次6分，2次3分，3次以上0分

- 过去12个月最长逾期天数：0天10分，1到30天7分，31到60天3分，60天以上0分

还款能力（权重25%）

- 资产负债率（如果能拿到外部数据）：低于50%打10分，50%到70%打6分，70%以上打2分，拿不到数据给5分

- 过去12个月月均采购额变动趋势：稳定或增长10分，小幅下降6分，大幅下降2分

交易行为（权重25%）

- 退货率：低于2%打10分，2%到5%打6分，5%到10%打3分，10%以上0分

- 最近一次采购距今：30天内10分，30到60天6分，60到90天3分，90天以上0分

经营稳定性（权重20%）

- 合作年限：3年以上10分，1到3年6分，1年以下3分，新客户首单5分

- 经营异常记录（企查查或天眼查）：无异常10分，有异常已移除5分，有异常未移除0分

三条原则记住：指标不超过10个，多了维护麻烦、边际贡献小。权重加起来等于100%。每个指标的打分标准要清晰客观，确保不同人打分结果一致。

第三步：验证调优——这个分打得准不准

模型建好了不代表好用。你得拿历史数据验证——过去一年确实出了问题的客户，用这个模型打分，排在前列吗？

验证方法：取出过去12个月实际发生坏账或严重逾期的客户名单（假设有10个），用模型给所有客户打分排序。如果8个在Top 20%——模型有效。只有3个在Top 20%——需要调优，检查哪个维度没捕捉到风险。

调优策略：某个维度跟坏账结果相关性弱——降低权重甚至删除。遗漏了重要的风险信号——增加新指标。

Excel实操，手把手教你

你不需要编程，Excel完全够用。

准备数据表：客户ID、客户名称、逾期次数、最长逾期天数、资产负债率、退货率、最近采购日期、合作起始日期、是否合作中。

建一张评分标准表：把上面每个指标的打分条件列出来。然后用VLOOKUP函数自动根据条件匹配得分。

算总分：总分等于各指标得分乘以对应权重之和。逾期次数得分乘30%，逾期天数得分乘30%……全部加起来。

排序分级：

- 总分8分以上——A级，低风险，正常信用政策

- 总分6到7.9分——B级，中等风险，适度收紧信用

- 总分4到5.9分——C级，较高风险，加强监控、降低额度

- 总分低于4分——D级，高风险，暂停赊销、催收账款

用条件格式让D级客户自动标红，用数据透视表生成按风险等级的客户汇总。打开Excel一眼就能看到哪些客户亮红灯。

同一套框架，换个场景就能用

客户信用评分只是一个例子。"维度-指标-权重-打分"的框架，能套到内审的很多场景。

供应商风险评分：从依赖性（采购额占比）、质量（来料合格率）、交付（准时交货率）、合规（资质有效期、失信记录）、价格（变动频率、跟市场价偏差）五个维度打分。采购审计进场前先跑一遍，高风险供应商优先查。

报销异常评分：从单据特征（金额是否整数、发票是否连号）、人员特征（报销频次、单人占比）、时间特征（周末节假日比例、月末集中度）、内容特征（招待费差旅费占比是否异常高）四个维度打分。我帮一个团队搭过这个模型，一跑揪出两个员工连续半年每月末报销整数金额差旅费，查实是虚报。

审计对象选择评分：从财务重要性（资产占比、收入占比）、上次审计（距今时间、发现问题数量和严重程度）、风险信号（管理层变动、业务模式变化）、经营表现（收入利润波动率、预算偏差）四个维度打分。年度审计计划排优先级，直接拿分数说话。

从"一次性模型"到"持续监控"

建模型只是第一步。更大的价值在于把它变成持续的监控机制。

第一阶段（手动）：每季度从系统导数据，在Excel里跑一遍模型，生成风险排序。大多数人从这里开始就够了。

第二阶段（半自动）：用Excel Power Query连接数据源，一键刷新数据，自动更新评分。

第三阶段（自动化）：在Power BI或公司数据平台中固化模型，设定自动刷新频率，异常自动推送告警。

第四阶段（智能化）：基于长期积累的数据，引入逻辑回归、决策树等方法优化权重和阈值。

建议：别一上来就追求第四阶段。99%的内审团队从第一阶段开始，扎扎实实跑一年，积累经验，再考虑自动化。一个简单但准确率70%的模型，远好过一个复杂但从未被真正使用过的模型。

三条铁律，碰了就翻车

铁律一：模型是辅助，不是替代。 模型告诉你这些客户风险高值得关注，但最终判断还是你的专业判断。模型说低风险不代表绝对安全。我见过一个客户各项指标都正常——逾期0次、退货率1.5%、合作5年——结果老板卷款跑路了。模型看不到的东西，你得用脑子看。

铁律二：模型需要定期"年检"。 业务环境在变，风险模式在变。每年至少做一次模型准确性回顾——去年的有效指标，今年可能已经失效。去年逾期次数是好指标，今年如果公司调整了信用政策放宽了账期，逾期次数的区分度就下降了。

铁律三：模型逻辑要能解释清楚。 如果模型给了一个客户D级评分，你要能说出为什么——是哪个维度拉低了分数？模型的"可解释性"甚至比"准确性"更重要。你跟老板汇报"这个客户风险高"，老板问"为什么"，你总不能说"系统说的"。

对老板你就这么说："我用Excel搭了个客户信用评分模型，全量客户跑了一遍，3个客户评分为D级高风险，建议暂停赊销、启动催收。模型逻辑和打分明细在这里，您可以看看。"

对业务部门你就这么说："这几个客户在信用评分里排名靠后，主要是回款和退货指标拉低了分数。你们帮我看看背后的业务原因是什么？如果是有特殊原因，我们可以在模型里做调整。"

风险预警模型不是数据科学家的专利——核心不是算法复杂度，是你对业务风险的理解有多深。你对业务越理解，选出的指标就越有区分度；审计经验越丰富，评分标准就越贴近实际。数据分析工具只是把你的专业经验"数字化"而已。

工具是死的，人是活的。别搞反了。

往期回顾：第31篇《财务数据关联分析》讲了三张表的钩稽关系，这篇是从"分析"到"预警"的进阶。

福利领取：关注公众号，回复"信用评分模板"，领取可编辑的Excel版客户信用评分模型（含公式、条件格式和示例数据），填上你的数据就能跑。