虚构一份应收账款的明细账，为制作利用Power BI监控应收账款风险的经验分享使用。

虚构一份应收账款的明细账，为制作利用Power BI监控应收账款风险的经验分享使用。

import numpy as np
import pandas as pd
from numpy.random import *
from faker import Faker
from datetime import datetime
from datetime import timedelta

np.random.seed(1)
faker=Faker(locale="en-US")
#假设本公司2016年初成立，假设现在时间是2022年底
#虚构数家公司
#假设业务开始日期在16-22年中的概率分别为[0.05，0.05，0.1，0.15，0.2，0.2，0.25]
#假设每家公司会有5%的机率坏账，并且不再付款的时间起点从业务开始日期至22年末之间随机分布。
#假设坏账的公司会在不再付款时间起点后的随机30-180日内停止业务往来
dates=pd.date_range(datetime(2016,1,1),datetime(2022,12,31))
dates=pd.Series([None] * len(dates),index=dates)
# yearOfDate=dates.map(lambda x:x.year)
companies=[]
comCnt=200
delayDays=choice(range(1,181),comCnt,replace=True)
probInYear=pd.Series([0.05,0.05,0.1,0.15,0.2,0.2,0.25])
probInYear=probInYear.cumsum()
for i in range(comCnt):
    companies.append(dict())
    companies[i]['name']=faker.company()
    #取一个0-1间的随机数，从累积概率列表中找出比它大的最小值，这个值对应的索引决定了年份
    prob=probInYear[probInYear>random()]
    yearIndex=prob.index[0]
    iyear=2016 + yearIndex
    #该年中一个随机日当业务开始日
    startDate=choice(dates[str(iyear)].index)
    companies[i]['startDate']=startDate
    companies[i]['willInDebt']=True if random()<0.05 else False
    #从开始业务日到最后日随机取一个日期当不再付款日
    noPayDate=choice(dates.index[dates.index>startDate]) if companies[i]['willInDebt'] else None
    companies[i]['noPayDate']=noPayDate
    #取提前做好的30-180日的随机表中取出对应的数，当作不再款日到停止业务日间的间隔日数
    #如果是欠债不还的客户按不付开始日+延迟日数做为业务终止日
    #如果是正常客户按业务开始日加随机数作为业务终止日
    delayDay=delayDays[i] 
    companies[i]['delayDay']=delayDay
    if noPayDate != None:
        endDate=datetime.fromtimestamp(noPayDate.astype(datetime)/1000000000)+timedelta(int(delayDay),0)
    else:
        endDate=datetime.fromtimestamp(startDate.astype(datetime)/1000000000)+timedelta(int(random()*3000),0)
    companies[i]['endDate']=endDate
    #每家单位制定一个随机数表示其业务规模，后面每次产生应收款的金额以该规模乘上随机数
    scale=round(10**(randn()+4 ),2)
    companies[i]['scale']=scale
    #每家单位制定一个月内有业务的概率，为了模拟一个间隔效果
    companies[i]['busProb']=random()+0.1
    #为每家单位定义一个每次支付的比例，以累计未支付的金额为基数。
    companies[i]['payRate']=random()*0.6+0.4
    #为每家单位指定一个拖延不付的机率
    companies[i]['delayProb']=random()*0.3

#为每家公司每个月计算一个应收款发生额
comData=pd.DataFrame(companies)
#将年月循环重复公司个数次，与公司依次重复年月数次，水平连接作为应收发生表的左边部分
yearMonths=pd.date_range('2016-01-01','2022-12-31',freq='M').map(lambda x: str(x.year) + '-' + str(x.month))
yearMonthsRepeated=pd.DataFrame([*yearMonths] * len(comData),columns=['yearMonth'])
# yearMonthDates=pd.Series(yearMonths.values).map(lambda x: choice(dates[x].index))
yearMonthsRepeated['happenDate']=yearMonthsRepeated['yearMonth'].map(lambda x: choice(dates[x].index))
yearMonthsRepeated['payDate']=yearMonthsRepeated['yearMonth'].map(lambda x: choice(dates[x].index))

comDataToList=[list(comData.iloc[i].values) for i in range(len(comData))]
comDataRepeated=pd.DataFrame(list(pd.Series(comDataToList).repeat(len(yearMonths)).values),columns=comData.columns)

receivableTable=pd.concat([comDataRepeated,yearMonthsRepeated],axis=1)

#定义一个根据设定条件和随机数生成各客户各月的收款金额的函数
def receivableHappen(happenDate,scale,startDate=datetime(2016,1,1),endDate=datetime(2022,12,31),busProb=1,*arg,**args):
    if random()>busProb:
        return 0
    if happenDate>endDate:
        return 0
    if happenDate<startDate:
        return 0
    receivable=(random()+0.5)*scale
    return round(receivable,2)

# receivableTable.map(lambda x:x['name'])
receivableHappened=pd.DataFrame([receivableHappen(**receivableTable.iloc[i].to_dict()) for i in range(len(receivableTable))],columns=['receivable'])
receivableTable=pd.concat([receivableTable,receivableHappened],axis=1)
#为每行添加一个随机数，用于后面计算决定当月是否付款
receivableTable['randomNum']=[random() for _ in range(len(receivableTable))]
receivableTable['noPayDate']=np.where(~pd.isnull(receivableTable['noPayDate']),receivableTable['noPayDate'],np.datetime64('2099-12-31T00:00:00.000000000'))

grouped=receivableTable.groupby(['name'])[['name','yearMonth','happenDate','payDate','noPayDate','receivable','scale','payRate','delayProb','randomNum']]

#定义一个决定客户各月付款金额的函数。每次按累计未付金额的比例支付。考虑客户的规模参数，决定它每月最大支付金额,一般设定为2倍.每月有一定机率不付款。
#如果当月支付日早于当月的发生日,则计算实际付款金额时不考虑该月发生金额.如果支付日期大于不再支付日期，则不支付。如果可付金额小于等于100，则全付。

def detailPay(i,receiList,randomNumList,payDateList,happenDateList,noPayDate=None,payRate=0.6,scale=999999,delayProb=1,*args,**kwargs):
    if i == 0:
        randomNum=randomNumList.iloc[i]
        #计算两个值，一个上月末累计未收的加上本月付款日之前产出的应收款，另一个是上月末累计未收的加上本月产生的应收款
        cumReceivableForPay=receiList.iloc[i]*(payDateList.iloc[i]>happenDateList.iloc[i])
        cumReceivable=receiList.iloc[i]
        if randomNum<1-delayProb and noPayDate>payDateList.iloc[i]:
            if cumReceivableForPay <=100:
                pay=cumReceivableForPay
            else:
                pay=min(round(cumReceivableForPay*payRate,2),scale*2)
        else: 
            pay=0
        cumNotPay=round(cumReceivable-pay,2)
        return[pay,cumNotPay]
    else:
        randomNum=randomNumList.iloc[i]
        cumReceivabePre=detailPay(i-1,receiList,randomNumList,payDateList,happenDateList,noPayDate,payRate,scale,delayProb,**kwargs)[1]
        cumReceivableForPay=receiList.iloc[i]*(payDateList.iloc[i]>happenDateList.iloc[i])+cumReceivabePre
        cumReceivable=receiList.iloc[i]+cumReceivabePre
        if randomNum<1-delayProb and noPayDate>payDateList.iloc[i]:
            if cumReceivableForPay <=100:
                pay=cumReceivableForPay
            else:
                pay=min(round(cumReceivableForPay*payRate,2),scale*2)
        else: 
            pay=0
        cumNotPay=round(cumReceivable-pay,2)
        return[pay,cumNotPay]
    # return noPayDate,delayProb

def calPayNotPay(part):
    part['pay']=[0]*len(part)
    part['cumNotPay']=[0]*len(part)
    receiList=part['receivable']
    randomNumList=part['randomNum']
    payDateList=part['payDate']
    happenDateList=part['happenDate']
    for i in range(len(part)):
        part['pay'].iloc[i],part['cumNotPay'].iloc[i]=detailPay(i,receiList,randomNumList,payDateList,happenDateList,**part.iloc[i])
    return part

payTable=grouped.apply(calPayNotPay)
payTable.to_excel('payTable.xlsx',index=False)

debitTable=payTable.reindex(columns=['name','happenDate','receivable']).rename(columns={'receivable':'debit','happenDate':'Date'})
creditTable=payTable.reindex(columns=['name','payDate','pay']).rename(columns={'pay':'credit','payDate':'Date'})
happenTable=pd.concat([debitTable,creditTable])
happenTable[['debit','credit']]=happenTable[['debit','credit']].fillna(0)
happenTable=happenTable[ happenTable['debit'].map(lambda x:1 if x!=0 else 0) + happenTable['credit'].map(lambda x:1 if x!=0 else 0) >0]
happenTable=happenTable.sort_values(['Date','name'])
happenTable.to_excel('receivableHappen.xlsx',index=False)