Excel 财务报表处理 #

┌─────────────────────────────────────────────────────────────┐
│                    财务应用场景                              │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  1. 财务计算：利息、折旧、现金流                            │
│  2. 报表编制：资产负债表、利润表                            │
│  3. 数据分析：趋势分析、比率分析                            │
│  4. 自动化：动态报表、自动更新                              │
│                                                             │
└─────────────────────────────────────────────────────────────┘

=PMT(rate, nper, pv, [fv], [type])

参数：
- rate: 每期利率
- nper: 总期数
- pv: 现值（贷款金额）
- fv: 未来值（默认0）
- type: 0=期末付款，1=期初付款

示例：
贷款100万，年利率5%，20年还清，每月还款：
=PMT(5%/12, 20*12, -1000000)
→ ¥6,599.56

=PV(rate, nper, pmt, [fv], [type])

计算未来现金流的现值

示例：
每年收到1万，连续10年，利率5%：
=PV(5%, 10, -10000)
→ ¥77,217.35

=FV(rate, nper, pmt, [pv], [type])

计算投资的未来值

示例：
每月存1000，年利率5%，存10年：
=FV(5%/12, 10*12, -1000)
→ ¥154,992.95

=RATE(nper, pmt, pv, [fv], [type], [guess])

计算利率

示例：
贷款10万，每月还2000，5年还清：
=RATE(5*12, -2000, 100000)*12
→ 7.42%（年利率）

=NPER(rate, pmt, pv, [fv], [type])

计算期数

示例：
贷款10万，每月还2000，利率5%：
=NPER(5%/12, -2000, 100000)/12
→ 4.53年

=SLN(cost, salvage, life)

参数：
- cost: 原值
- salvage: 残值
- life: 使用年限

示例：
设备原值10万，残值1万，使用5年：
=SLN(100000, 10000, 5)
→ ¥18,000/年

=DDB(cost, salvage, life, period, [factor])

示例：
=DDB(100000, 10000, 5, 1)  第1年折旧
→ ¥40,000

=SYD(cost, salvage, life, period)

示例：
=SYD(100000, 10000, 5, 1)  第1年折旧
→ ¥30,000

输入：
    A          B
1   贷款金额   1000000
2   年利率     5%
3   贷款年限   20

计算：
    A              B
1   月利率         =B2/12
2   总期数         =B3*12
3   月还款额       =PMT(B1, B2, -贷款金额)
4   总还款额       =月还款额*总期数
5   总利息         =总还款额-贷款金额

数据：
    A          B          C
1   年份       现金流     累计
2   0          -100000    =B2
3   1          20000      =C2+B3
4   2          30000      =C3+B4
5   3          40000      =C4+B5
6   4          50000      =C5+B6
7   5          60000      =C6+B7

净现值（NPV）：
=NPV(10%, B3:B7)+B2
→ ¥37,907.87

内部收益率（IRR）：
=IRR(B2:B7)
→ 20.28%

数据：
    A          B
1   原值       100000
2   残值       10000
3   使用年限   5

折旧表：
    A          B          C          D
1   年份       直线法     双倍余额   年数总和
2   1          =SLN($B$1,$B$2,$B$3)  =DDB($B$1,$B$2,$B$3,A2)  =SYD($B$1,$B$2,$B$3,A2)
3   2          ...        ...        ...
4   3          ...        ...        ...
5   4          ...        ...        ...
6   5          ...        ...        ...

数据：
    A          B          C
1   客户       金额       开票日期
2   客户A      10000      2024-01-01
3   客户B      20000      2023-12-01
4   客户C      15000      2023-11-15

账龄分析：
    A              B
1   账龄           金额
2   0-30天         =SUMIFS(B:B,C:C,">="&TODAY()-30)
3   31-60天        =SUMIFS(B:B,C:C,">="&TODAY()-60,C:C,"<"&TODAY()-30)
4   61-90天        =SUMIFS(B:B,C:C,">="&TODAY()-90,C:C,"<"&TODAY()-60)
5   90天以上       =SUMIFS(B:B,C:C,"<"&TODAY()-90)

规则：
销售额 <= 10000：提成 5%
销售额 <= 50000：提成 8%
销售额 > 50000：提成 10%

数据：
    A          B          C
1   员工       销售额     提成
2   张三       8000       =B2*IF(B2<=10000, 5%, IF(B2<=50000, 8%, 10%))
3   李四       30000      =B3*IF(B3<=10000, 5%, IF(B3<=50000, 8%, 10%))
4   王五       60000      =B4*IF(B4<=10000, 5%, IF(B4<=50000, 8%, 10%))

或使用 IFS：
=B2*IFS(B2<=10000, 5%, B2<=50000, 8%, TRUE, 10%)

使用 SUMIFS 创建动态汇总：

    A          B          C
1   部门       月份       销售额
2   销售       1月        =SUMIFS(数据!C:C, 数据!A:A, A2, 数据!B:B, B2)

使用表格和动态范围：

1. 将数据转换为表格（Ctrl+T）
2. 图表引用表格列
3. 数据更新时图表自动更新

应收账款超期预警：
条件格式 → 新建规则 → 使用公式

公式：
=C2<TODAY()-30  （开票日期超过30天）

设置红色填充