财务预算分析全网最佳实践:从每月分析到每天分析

原文链接如下:

「链接」

掌握本文的方法，你就掌握了企业预算精细化分析的能力，全网首发。

数据模拟稍微有点问题，不要在意数据细节，先看下最终效果。

在编制财务预算或业务预算的过程中，通常预算的所有数据都是按月编制，格式一般如下所示：

很多公司也都是在月末对当月的预算数据进行对比分析，然而一个月都过去了，再对比分析，除了写上达成未达成的原因外，用处其实不是很大。

实际的数据是按天发生，如果能把预算数据分解至天，那么就可以每天对比所有预算数据的执行情况，及时进行问题定位，给出意见，这样预算管控才能发挥真正的作用，本文将手把手教你如何把预算数据分解至天，并按天进行分析！

本文预算分解采用平均法分解，如1月办公费的预算为31元，则每天的预算为31元/31天=1元/天。

先读取数据。

import pandas as pd
import numpy as np
df =pd.read_excel(r"C:\Users\voommvoomm\Desktop\预算分解.xlsx")
df.tail()

首先进行数据预处理：

1、填充缺失值

2、保留各预算科目的顺序，即增加索引列，方便后面进行排序操作

3、将科目和明细列拼接起来，以识别每一行数据代表哪个具体明细科目

df["明细"][26]="合计"
df2 = df.fillna(method="ffill")
df2 = df2.reset_index(drop=True)
df2["科目明细"] = df2["科目"]+"-"+df2["明细"]
df2["索引"] = df2.index
df2.tail()

接下来构建辅助函数，计算某月到底有多少天，注意闰年和2月天数的处理：

def get_last_day(year, month):
    flag = False
    if year%4 == 0 and year%100 != 0:
        flag = True
    if year%400 == 0:
        flag = True
    if month in [1,3,5,7,8,10,12]:
        return 31
    if month in [4,6,9,11]:
        return 30
    if month == 2 and flag == True:
        return 29
    if month == 2 and flag == False:
        return 28

因为要分解到每天，所以还需构建一个按天排列的日期表，最后将分解后的数据拼接进去，下面是生成日期列的函数：

import datetime
def create_assist_date(datestart = None,dateend = None):
    datestart=datetime.datetime.strptime(datestart,'%Y-%m-%d')
    dateend=datetime.datetime.strptime(dateend,'%Y-%m-%d')
    date_list = []
    date_list.append(datestart.strftime('%Y-%m-%d'))
    while datestart<dateend:
        datestart+=pd.Timedelta(days=+1)
        date_list.append(datestart.strftime('%Y-%m-%d'))
    return date_list

接下来生成2021年1月1日到2021年12月31日的时间序列，即预算日期序列：

df_date=pd.DataFrame(create_assist_date(datestart = "2021-01-01",dateend = "2021-12-31"),columns=["日期"])

生成日期序列之后，下一步就是构建笛卡尔积，将预算明细科目与日期放在一起，最后与原表进行数据拼接：

expense_details = np.unique(df2["科目明细"])
cross_result = [(date,details) for date in df_date["日期"] for  details in expense_details]
df3 = pd.DataFrame(cross_result,columns=["日期","科目明细"])
df3 = pd.merge(df3,df2[["科目明细","科目","明细","索引",]],on="科目明细")
df3.head()

拼接之后计算每个日期这个月对应的天数，构建一个函数计算：

def get_days(df3):
    m=0
    df3["天数"]=0
    for i in df3["日期"]:
        year = int(i[:4])
        month =int(i[5:7])
        df3["天数"][m]=get_last_day(year,month)
        m+=1
    return df3

df3 = get_days(df3)
df3.head()

再将原始表中的预算数据和科目明细字段提取出来进行处理：

df2_temp = df2.iloc[:,2:14]
df2_temp["科目明细"] = df2["科目明细"]
df2_temp.head()

因为需要将这个表与构建的日期表进行拼接，所以需要进行逆透视：

df2_temp = df2_temp.set_index(["科目明细"]).stack().reset_index()
df2_temp.head()

再对月份进行处理，将其变为当月的第一天，这样才好进行拼接：

df2_temp["日期"] = df2_temp["level_1"].apply(lambda x:x.strip("月"))
df2_temp["日期"] = df2_temp["日期"].apply(lambda x:x.rjust(2,"0"))
df2_temp["日期"] = df2_temp["日期"].apply(lambda x:"2021-"+x+"-01")
df2_temp = df2_temp.drop("level_1",axis = 1)
df2_temp.rename(columns={0:"预算"},inplace=True)
df2_temp.head()

接下来进行拼接

len(df2_temp) 324
df4 = pd.merge(df3,df2_temp,on=["日期","科目明细"],how="left")
df4.head()

再按照索引和日期进行排序，用当月预算除以当月天数即获得每天的预算，最后缺失值向下填充即分解完毕：

df4 = df4.sort_values(["索引","日期"])
df4["预算_天"] = df4["预算"]/df4["天数"]
df4 = df4.reset_index(drop=True)
df4 = df4.fillna(method="ffill")
df4.head()

分解完之后，还需要按天求预算累积值，这样才能对比预算累积达成率。

先按照科目进行透视。

df4_byday = pd.pivot_table(df4,index=["日期"],columns="科目明细",values="预算_天", aggfunc={"预算_天":sum})
df4_byday = df4_pivot.reset_index()
df4_byday = df4_byday.drop(["index","天数"],axis=1)
df4_byday.tail()

透视完之后按日期进行累积叠加，并拼接原来的相关字段：

df4_cumsum_unpivot = df4_cumsum.set_index(["日期"]).stack().reset_index()
df4_cumsum_unpivot = df4_cumsum_unpivot.rename(columns={0:"累积预算"})
df4_cumsum_unpivot = pd.merge(df4_cumsum_unpivot,df2[["科目明细","索引","科目","明细"]],on="科目明细",how="left")
df4_cumsum_unpivot = df4_cumsum_unpivot.sort_values(["索引","日期"])
df4_cumsum_unpivot = df4_cumsum_unpivot.reset_index(drop = True)
df4_cumsum_unpivot = pd.merge(df4_cumsum_unpivot,df4[["科目明细","日期","天数","预算","预算_天"]],on=["科目明细","日期"],how="left")
df4_cumsum_unpivot = df4_cumsum_unpivot.rename(columns = {"预算_天":"每天预算"})
df4_cumsum_unpivot.head()

至此，预算数据才算真正处理完毕。

分解完毕后，再将预算数据与实际数据按天进行对比。

读取实际数据：

df_actual = pd.read_excel(r"C:\Users\voommvoomm\Desktop\实际数据.xlsx")
df_actual = pd.merge(df_actual,df2[["科目明细","明细"]],on = "明细")
df_actual.head()

实际发生数据也需要按天进行累积叠加求和：

df_actual = pd.read_excel(r"C:\Users\daimeijun\Desktop\实际数据.xlsx")
df_actual = pd.merge(df_actual,df2[["科目明细","明细"]],on = "明细")
df_actual_pivot = pd.pivot_table(df_actual,index=["日期"],columns="科目明细",values=["金额"], aggfunc={"金额":sum}).reset_index()
df_actual_cumsum = df_actual_pivot.set_index(["日期"]).cumsum().reset_index()
df_actual_cumsum_unpivot = df_actual_cumsum.set_index(["日期"]).stack().reset_index()
df_actual_cumsum_unpivot = df_actual_cumsum_unpivot.rename(columns = {"金额":"累积发生"})
df_actual_cumsum_unpivot = df_actual_cumsum_unpivot.sort_values(["科目明细","日期"]).reset_index(drop = True)
import time
df_actual_end = pd.merge(df_actual_cumsum_unpivot,df_actual[["科目明细","日期","金额"]],on=["科目明细","日期"],how = "left")
df_actual_end["日期"] = df_actual_end["日期"].apply(lambda x:x.strftime("%Y-%m-%d"))
df_actual_end.head()

实际发生数据构建好之后，接下来把预算数据与实际数据进行拼接：

df_end = pd.merge(df4_cumsum_unpivot,df_actual_end,on=["日期","科目明细"],how="left")
df_end = df_end.rename(columns={"金额":"按天发生"})
df_end["月份"] = df_end["日期"].apply(lambda x:x[5:7])
df_end.head()

至此，预算分解与预算对比的数据处理工作已完毕。数据模拟稍微有点问题，最后展示下前端效果：

这是针对其中一个收入项目的分析，满满的压迫感！现在就完成了从按月分析预算到按天分析预算的转换，并且给出了直截了当的分析建议，真正的业财融合。

最后总结下上述方法的作用:

1、从按月分析到按天分析，针对所有预算科目，发现任意预算科目及指标的蛛丝马迹

2、直接在大屏里告诉业务部门接下来应该完成多少任务，哪天数据出了问题，让业务部门压力山大