1656224696842.jpg租赁模板.xlsx 会用到租赁台账的业务场景 Excel函数具体的财务应用--制作租赁台账 - 图2 登记台账的意义

    • 直观 , 清晰;
    • 好记性不如烂笔头;
    • 提高效率,降低出错率;

    :::info 台账结构简单介绍

    1. 基础信息 : 包括出租人,承租人,地址,租赁期,租赁金额等关键字段信息;
    2. 财务信息 : 摊销起始月份,每期摊销额,累计摊销额,剩余待摊金额;
    3. 账账核对 : 财务账上数据与台账一致性检查; ::: :::info 摊销方法
      直线法 :::

    主要讲解台账中蓝色字体部分,即公式逻辑

    1. 序号

    这里用到的函数是row函数,有两种用法

    • row() : 表示公式所在这一行的行数

    image.png

    • row(c5) : 表示当前单元格显示c5所在的行的行数

    image.png
    那么了解函数的原理之后,就可以来写下台账的第一个公式了

    1. 第一种方法:=row()-2 '单元格所在行数-2,即为1
    2. 第二种方法:=row(a1) '直接取A1所在的行数
    1. 租赁天数和租赁月数

    这里用到的是一个隐藏函数datedif,它的参数是(起始日期,终止日期,”y/m/d”),意思是计算两个日期之间相隔的年数,月数或天数,一般会在公式后+1

    • y表示年
    • m表示月
    • d表示日

    在租赁合同中,通常会签署为当日至次年当日的前一天表示为一整年,如:2022年1月1日-2023年1月1日-1即2022年12月31日
    以2022年1月1日-2022年12月31日为例,datedif计算出来的年月日分别为
    image.png
    可见datedif计算出来的并不是真实的年月日数,需要人为的+1

    :::info 可能有同学会问: 日期本质上是数字,为什么不用两个数字之间的差值来计算呢?
    确实,但是两个数值的计算只能算日期的差值,如果要算月,年,是无法直接算出的。 :::

    1. 月租金

    这里用到的是一个简单的if和round嵌套,就不展开了,用年租金/月数保留两位小数

    1. 摊销首月

    这里用到的是一个不那么常用的函数eomonth,和if的嵌套, 先判断租赁日是否>15号,一般来讲,起租日在15日以后,不会在当月摊销成本
    用一张图来解释下eomonth的用法
    image.png

    1. 每月摊销额

    也是if和round的嵌套公式

    • 判断发票类型,如果是专票,就/(1+税率)计算不含税总租金,反之/1,也就是不需要剔除税金;
    • 用不含税年租金/月数,保留2位小数;
    1. 累计已摊销

    这里是ifdatedififerrortoday的多重嵌套

    • 用datedif计算当前日期(today)和合同起租日之间相差的月数(A),记得+1;
    • 如果datedif报错,会返回null,这时候要用iferror强制把返回值改成0;

    累计已摊销的计算逻辑,就是以每期摊销额*A

    1. 剩余待摊金额

    就是以不含税年租金-累计已摊销,计算出剩余待摊金额,没什么特别的,就不展开了;

    1. 账面发生额

    这一列是需要从账上取数了,目的是为了后两列的check,不展开了;

    1. 账实差异

    年租金不含税总额-剩余待摊金额-账面发生额=0,即表示财务账上和台账数据保持一致,否则表示账务处理错误,需要调整成本;

    1. 类型

    虽然制作台账用到的公式整体上并没有什么难度,但是涉及到嵌套,导致公式比较长,理解起来需要时间,所以加了这一列来帮助台账使用人快速定位账上记的成本上多了还是少了

    最后,公式讲完了,是时候讲一下数据验证了,不同的产品/版本可能叫法不一样,不过不影响使用
    image.png
    主要用到的是序列这个用法,用来制作一些下拉列表

    image.png image.png image.png

    image.png
    至此 , 一份租赁台账模板就制作完成了。
    效果图
    1656227493248.jpg

    我只是抛砖引玉 , 如果大家有更好的制作模板的方法或者思路(如结合超级表),欢迎留言交流