一、场景导入有个加入我们知识星球的星友让我帮忙整一个能够基于以下数据源(1-12月份的分月数据)自动生成应收账龄分析报告：

图片

我最好搭建好模型输出的报告如下：

图片

这次这个模型算是彻底地讲透所有账龄分析报告地模板(模型通用！只要理解了我这套模型逻辑---逻辑不复杂，但是需要很细心！)二、建模过程1、模型2大前提：1）先进先出法：按照先进先出法进行账龄数据的抵消；2）日期必须升序排列；我们分2个模型从难到易的演示搭建过程1个是没有销售退货和销售退款的情形(实务中较少，但是便于我们理解)1个是带销售退款和销售退货的情形(应收账款余额有正数和负数，正数为真正的应收，负数为预收！)2、没有销售退货和销售退款的模型这个模型相对比较简单步骤1：将每个客户的【期初余额】调整到【本期销售】中去，对账日期设置为'1-1'日；

图片

这个调整后逻辑就变得比较简单了否则维度太多，我一般是先想着降维，一定要降维打击！步骤2：计算每1行的【期末余额】(实务上这步骤实际可以不用，系统导致就有(建模时为了保证逻辑才加的))每期期末余额=截至当前【对账日】的累计销售额-截至当前【对账日】的累计回款额对应模型中公式如下：=SUMIFS(D:D,B:B,B2,A:A,'<='&A2)-SUMIFS(E:E,B:B,B2,A:A,'<='&A2)

图片

步骤3：计算出【全年累计销售额】和【全年累计回款额】、【年末余额】全年累计销售额==SUMIFS(D:D,B:B,B2)      全年累计回款额=SUMIFS(E:E,B:B,B2)      年末余额=全年累计销售额-全年累计回款额

图片

步骤4：计算截至本期的累计销售额和截至本期的累计回款额； 截至本期的累计销售额=SUMIFS(D:D,B:B,B3,A:A,'<='&A3)     截至本期的累计回款额=SUMIFS(E:E,B:B,B3,A:A,'<='&A3)

图片

步骤5：拆分出每1行的【期末余额】有多少金额是'沉淀'到【年末余额】

图片

应收的公式:

图片

预收的公式：

图片

步骤6：合并上一步【应收】和【预收】到一起        合并账龄金额=L3+M3

图片

步骤7：计算【账龄天数】和【账龄区间】先做好如下账龄天数与账龄区间的对照表

图片

【账龄天数】=DATEDIF(A3,$B$1,'D')           【账龄区间】=LOOKUP(O3,$R$4:$S$10)

图片

步骤8：生成账龄分析透视表(最好将源表变成超级表，然后生成数据透视表)，做一些图表分析就是文章最开始截图的效果：最大bug：如果有销售退货和销售退款就会有瑕疵！

图片

如上图的A公司(预收)和D公司(应收)账龄有问题，需要手动账龄中的负数进行调整！好了，我们直接上视频(点击视频观看)：这是核心搭建部分：

图片

这是数据透视部分：

图片

3、完美模型最终效果如下：

图片

全部正常，加入我的知识星球(下方扫码即可加入)可以获取完美版模板！好了今天分享就到这里了，加我的微信Txx3141即可获得本文对应的excel模板(带负数模板)：加入知识星球即可获得如下视频中的全部资料包(全是实战模板，涵盖经营分析、财务分析、财务报表、预算管理、成本管控等，扫码下方二维码即可加入，并获得所以菜单开发中的代码！)

图片

三、结语

今天分享就到这里了，希望本文对您有所启发！我们专注实战财税法知识的分享，融合财务、技术和业务三大知识体系，致力于数据建模、数据分析和数据可视化，我们价值观是：价值、快乐和哲思财税法，让我们一同进步！想要与群友就公众号文章进行沟通反馈的，欢迎加入本公众号专用的沟通交流群(加微信txx3141。群里会发部分源文件)！

本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请点击举报。