根据收费标准和招生进度编制收入预算表

楼主 lrlxxqxa	一、概述：实际工作中，“预算”一定是大家耳熟能详的字眼了，无论哪个行业，哪个企业都离不开预算的编制。我们需要针对每个责任中心，预测并计算在未来的一定时期内经营、资本、财务等各方面的收入、支出、现金流的总体趋势，从而制定保证目标达成的具体计划和实施方案。本文结合一个实例来阐述这个问题。二、实例：上图是某早教中心的收入预算汇总表。其中已知各种参报课时的收费标准，以及对应的月度招生进度（绿色），要根据要求编制该早教中心主营收入和辅助收入的月度收入和现金流。说明及要求： 1、不同课时收费标准位于A27:B32单元格区域 2、7月1日以后招收的新生按照新收费标准，O26:O31单元格区域。老生保持不变。 3、每个学生一周消耗一课时，每月4课时； 4、现金流按照收付实现制，当月收取的都纳入计算范围； 5、收入按照权责发生制，只按照学生当月消耗的课时费确认； 6、需要考虑学生课时满后的置换问题，比如24课时学生从1月到6月，7月起不再计算收入。 7、半日班学生一次缴费2680为一季度学费，每月确认收入要除以3； 8、需要考虑7月1日提费后，新老混合收费问题。三、解决方案：课时费公式：E8输入 =IF(E$5="收入",SUMPRODUCT($B$27:$B$31/$A$27:$A$31OFFSET($B$27:$B$31,,1,,1+IF(1,INT((COLUMN(A1)-1)/2)))(2+INT((COLUMN(A1)-1)/2)-COLUMN(INDIRECT("c1:c"&1+INT((COLUMN(A1)-1)/2),))<=$A$27:$A$31/4))4+(COLUMN(A1)>=13)SUMPRODUCT($Q$27:$Q$31/$A$27:$A$31OFFSET($I$27:$I$31,,,,MAX(1,1+INT((COLUMN(A1)-13)/2))))4,SUMPRODUCT($B$27:$B$31OFFSET($B$27:$B$31,,1+IF(1,INT((COLUMN(A1)-1)/2))))+(COLUMN(A1)>=13)SUMPRODUCT($Q$27:$Q$31OFFSET($B$27:$B$31,,1+IF(1,INT((COLUMN(A1)-1)/2))))) 向右填充公式半日班收入：E10输入 =IF(E$5="收入",SUMPRODUCT($B$32/3OFFSET($B$32,,1,,1+IF(1,INT((COLUMN(A1)-1)/2)))(2+INT((COLUMN(A1)-1)/2)-COLUMN(INDIRECT("c1:c"&1+INT((COLUMN(A1)-1)/2),))<=3)),SUMPRODUCT($B$32OFFSET($B$32,,1+IF(1,INT((COLUMN(A1)-1)/2))))) 向右填充公式收入预算汇总表lr.rar
2楼 lrlxxqxa	四、思路解析 1、首先观察表格结构，收入及现金流预算部分每月占据两列，依次向右排列；而绿色区域的招生进度是每月只占一列，依次向右排列。这就需要在公式向右填充的时候，月度收入引用的招生进度按照每两个月向右偏移一列来计算。这个问题可以通过 =1+INT((COLUMN(A1)-1)/2) 向右填充解决问题。图示如下 2、收入的计算：不同的收费标准除以对应的课时，即每课时的收费标准，即公式中的$B$27:$B$31/$A$27:$A$31这部分；将其再乘以缴费期间的学生人数，就得到了上一次课的收入。学生的累计数可以通过Offset函数的偏移来实现，即OFFSET($B$27:$B$31,,1,,1+IF(1,INT((COLUMN(A1)-1)/2)))，其中需要注意的是如果用INT((COLUMN(A1)-1)/2)直接作为OFFSET的第4参数，则会返回错误，这里我们使用IF（1, ）的形式来实现Offset的单元格区域引用偏移。由于每个学生每周上一次课，月消费4次，所以再乘以4. 到这里可以得出，不考虑课时缴费周期的前提下的收入数 $B$27:$B$31/$A$27:$A$31OFFSET($B$27:$B$31,,1,,1+IF(1,INT((COLUMN(A1)-1)/2))) 如果考虑不同课时的缴费期间，就需要增加一个参数，来判断绿色区域中的招生人数，是否还在缴费期间内，如果缴费期满，则不应再继续确认收入在当月。我们可以利用indirect函数的r1c1格式，在公式向右填充的过程中，产生一个引用区域递增的数组，及INDIRECT("c1:c"&1+INT((COLUMN(A1)-1)/2),)，再配合column函数提取列数可以看出随着公式的拖拉，公式返回的数组元素逐渐增多，并呈递增。利用2+INT((COLUMN(A1)-1)/2)-COLUMN(INDIRECT("c1:c"&1+INT((COLUMN(A1)-1)/2),))则可以返回数据元素增减增多并呈递减的数组，如下收费标准/课时数=缴费月数，也就是公式中的$A$27:$A$31/4 将上图数组作为条件参数，与缴费月数相比较，判断学生的缴费是否期满，如果期满则不计入收入，如果不满则纳入收入。公式中的条件参数如下 (2+INT((COLUMN(A1)-1)/2)-COLUMN(INDIRECT("c1:c"&1+INT((COLUMN(A1)-1)/2),))<=$A$27:$A$31/4) E8单元格公式的刷黑部分按F9可以看到返回的数组是{TRUE;TRUE;TRUE;TRUE;TRUE} 说明1月的5种课时下招生学生都处于缴费期内，全部计入收入。当定位在7月收入的Q8单元格时，再看这个条件参数，刷黑按F9，返回的数组就是{TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE;TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE;FALSE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE;FALSE,FALSE,FALSE,FALSE,FALSE,TRUE,TRUE;FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE} 通过下图可以清晰的看出，不同课时下学生在不同月份的缴费期间报24课时的学生，在7月份已不计入收入；报10课时和4课时的计费同理。至此，在7月1日前没有涨高收费标准的前提下，收入公式可以确定为 SUMPRODUCT($B$27:$B$31/$A$27:$A$31OFFSET($B$27:$B$31,,1,,1+IF(1,INT((COLUMN(A1)-1)/2)))(2+INT((COLUMN(A1)-1)/2)-COLUMN(INDIRECT("c1:c"&1+INT((COLUMN(A1)-1)/2),))<=$A$27:$A$31/4))4
3楼 lrlxxqxa	再来看当涉及提高收费标准后的，新生按照新标准，老生按照原标准收费的计算方法。思路：由于在前面已经利用offset计算出按月累积的学生人数，并结合条件参数排除超出缴费期间的学生不再计入收入。所以在提费后，只需要重新计算对于新生额外新增的收费就可以了。为了查看清晰并方便计算，将新收费标准较原收费标准的增加额度列示在Q27:Q31单元格区域要计算出提价后针对新生新增的收入，就要确定新费标准下的新生累计人数。思路依然是利用offset偏移，即公式中的这部分 OFFSET($I$27:$I$31,,,,MAX(1,1+INT((COLUMN(A1)-13)/2))) 利用max函数限制返回数组的最小值为1，避免offset向左（即1月方向）进行区域偏移。由于需要在E8输入一个公式向右填充，需要加入一个条件参数判断提费节点，即(COLUMN(A1)>=13)，当COLUMN(A1)=14时即7月位置，开始采用新收费标准。所以，完整计算针对新生较老生多缴纳的费用公式为 (COLUMN(A1)>=13)SUMPRODUCT($Q$27:$Q$31/$A$27:$A$31OFFSET($I$27:$I$31,,,,MAX(1,1+INT((COLUMN(A1)-13)/2))))4 至此，学生收入思路为原收费+提费后新收费，确认公式为 SUMPRODUCT($B$27:$B$31/$A$27:$A$31OFFSET($B$27:$B$31,,1,,1+IF(1,INT((COLUMN(A1)-1)/2)))(2+INT((COLUMN(A1)-1)/2)-COLUMN(INDIRECT("c1:c"&1+INT((COLUMN(A1)-1)/2),))<=$A$27:$A$31/4))4+(COLUMN(A1)>=13)SUMPRODUCT($Q$27:$Q$31/$A$27:$A$31OFFSET($I$27:$I$31,,,,MAX(1,1+INT((COLUMN(A1)-13)/2))))*4
4楼 lrlxxqxa	现在来看现金流的计算。现金流较之收入计算比较容易，不必考虑学生一次性缴费后，按照课时排布分期确认收入的问题，即按照收取当期算入现金流。公式为 SUMPRODUCT($B$27:$B$31OFFSET($B$27:$B$31,,1+IF(1,INT((COLUMN(A1)-1)/2))))+(COLUMN(A1)>=13)SUMPRODUCT($Q$27:$Q$31OFFSET($B$27:$B$31,,1+IF(1,INT((COLUMN(A1)-1)/2)))) 公示前部分是原费基础上的现金流，即SUMPRODUCT($B$27:$B$31OFFSET($B$27:$B$31,,1+IF(1,INT((COLUMN(A1)-1)/2)))) 后部分是提费后比原费增加的现金流收入。 IF(1,INT((COLUMN(A1)-1)/2))依然是为了返回一个每两列递加1的数组，再传递给offset函数作为第3参数，确定向右偏移的列数。上图中公式所在单元格位于7月，所以1+IF(1,INT((COLUMN(N1)-1)/2))返回{7} 即偏移到7月当月缴费的各档学生人数。分别确定了收入和现金流的计算方法，利用if判断位置，完成课时费的公式如下： =IF(E$5="收入",SUMPRODUCT($B$27:$B$31/$A$27:$A$31OFFSET($B$27:$B$31,,1,,1+IF(1,INT((COLUMN(A1)-1)/2)))(2+INT((COLUMN(A1)-1)/2)-COLUMN(INDIRECT("c1:c"&1+INT((COLUMN(A1)-1)/2),))<=$A$27:$A$31/4))4+(COLUMN(A1)>=13)SUMPRODUCT($Q$27:$Q$31/$A$27:$A$31OFFSET($I$27:$I$31,,,,MAX(1,1+INT((COLUMN(A1)-13)/2))))4,SUMPRODUCT($B$27:$B$31OFFSET($B$27:$B$31,,1+IF(1,INT((COLUMN(A1)-1)/2))))+(COLUMN(A1)>=13)SUMPRODUCT($Q$27:$Q$31*OFFSET($B$27:$B$31,,1+IF(1,INT((COLUMN(A1)-1)/2)))))
5楼 lrlxxqxa	开始计算半日班收入。半日班学生一次交费为一季度学费，所以确认收入时需要除以3，确认现金流时则当月缴纳的全部算入。在收入的计算中，依然需要考虑学生缴费后的收入确认期间问题。即1月缴费的学生在1、2、3月分别确认缴费的1/3，2月缴费的学生在2、3、4月分别确认缴费的1/3，以此类推。公式中这部分 2+INT((COLUMN(G1)-1)/2)-COLUMN(INDIRECT("c1:c"&1+INT((COLUMN(G1)-1)/2),)) 就作为条件参数，判断学生的缴费期间是否超出按F9后返回{4,3,2,1}，将其与3比较，如果小于或等于3，则在缴费期间，需要确认收入。上图中公式所在单元格为K10，处于4月，所以1月的学生缴纳的学费已超出缴费期间，不再算入收入。使用每月当期新增人数合计的收入乘以条件参数，即可得到当月的现金流。 SUMPRODUCT($B$32/3OFFSET($B$32,,1,,1+IF(1,INT((COLUMN(A1)-1)/2)))(2+INT((COLUMN(A1)-1)/2)-COLUMN(INDIRECT("c1:c"&1+INT((COLUMN(A1)-1)/2),))<=3)) 半日班学生的现金流计算比较简单，直接按照当月学生新增人数的收入为准 SUMPRODUCT($B$32OFFSET($B$32,,1+IF(1,INT((COLUMN(A1)-1)/2)))) 有了半日班学生的收入和现金流，利用if整合为一个公式 =IF(E$5="收入",SUMPRODUCT($B$32/3OFFSET($B$32,,1,,1+IF(1,INT((COLUMN(A1)-1)/2)))(2+INT((COLUMN(A1)-1)/2)-COLUMN(INDIRECT("c1:c"&1+INT((COLUMN(A1)-1)/2),))<=3)),SUMPRODUCT($B$32OFFSET($B$32,,1+IF(1,INT((COLUMN(A1)-1)/2))))) 至此，课时费和半日班的公式全部确定。
6楼 pcwmmn	我喜欢啊，请继续支持你
7楼 csq518519	极品啊，挺一个。
8楼 fnnlj	我来了~~~嘿嘿~~ 888wa
9楼 amylee	收藏了，慢慢啃。谢谢分享！
10楼 hwy_29	谢谢分享
11楼 chrischowlin	好复杂啊，先看看吧
12楼 272779357
13楼老糊涂	学习