Question

Month	May	June	July	August	September	October	November	December	January
Sales	350	350	250	250	350	550	650	750	650
Collections
month 0: 80%*95%
month -1: 15%
month -2: 5%
Payments
Purchase - 70% of next month sales
Purchase payment - 50% of current month purchase
Purchase payment - 50% of last month purchase
Lease payment			10	10	10	10	10	10
construction			0	0	0	60	0	0
wages			30	30	40	50	70	80
other			5	5	5	5	5	5
taxes			0	0	30	0	0	50
Net Cash Flow
cumulative cash		50
Target Cash Flow			10	10	10	10	10	10
Surplus/Shortage
find out collections, month 1, month 2, purchase 70 % of next month sales, purchase payment 50% of current month purchase, purchase payment 50% of last month purchase payment, net cash flow, cumilative cash, surplus/shortage. please provide me an formulae so that i can do it in excel.

Answer 1

Please find below the calculations table with the formulas to be used in excel clearly below:-

For eg. sales in month May is 350, so collections from this sales in month 0 i.e. May month is 80% of 95% of 350 = 80% x 95% x 350 = 266; For month 1 i.e. June month, collections from sales in May is 15% of May month sales i.e. 15% x 350 =50; Similarly for month 2 i.e. July month, collections from sales in May is 5% of May month sales i.e. 5% x 350 =17

For purchase payments in any month for eg. May month, Purchase - 70% of next month sales i.e. purchase payment in May month for expected sales in June month of 350 is 70% of 350 = 70% x 350 = 245.

For purchase payments in May month, Purchase - 50% of current month purchase i.e. purchase payment in May month for sales in May month of 350 is 50% of 350 = 50% x 350 = 175.

For purchase payments in May month, Purchase - 50% of last month purchase i.e. purchase payment in May month for sales in April month of nil sales (value not available; hence considered to be zero) is 50% of 0 = 50% x 0 = 0.

Month	May	June	July	August	September	October	November	December	January
Sales --> (1)	350	350	250	250	350	550	650	750	650
Collections (Assuming total of 5% losses on monthly sales)
Month 0: 80%*95% --> (2) = 15% x 95% x (1)	266	266	190	190	266	418	494	570	494
Month 1: 15% --> (3) = 15% x 95% x (1)	0	50	50	36	36	50	78	93	107
Month 2: 5% --> (4) = 5% x 95% x (1)	0	0	17	17	12	12	17	26	31
Total Collections --> (5)= (2)+ (3)+ (4)	266	316	257	242	314	480	589	689	632
Payments
Purchase - 70% of next month sales --> (6) = 70% x (1)	245	175	175	245	385	455	525	455	0
Purchase payment - 50% of current month purchase --> (7) = 50% x (1)	175	175	125	125	175	275	325	375	325
Purchase payment - 50% of last month purchase --> (8) = 50% x (1)	0	175	175	125	125	175	275	325	375
Lease payment --> (9)			10	10	10	10	10	10
construction --> (10)			0	0	0	60	0	0
wages --> (11)			30	30	40	50	70	80
other --> (12)			5	5	5	5	5	5
taxes --> (13)			0	0	30	0	0	50
Total Payments (14)= (6)+ (7)+ (8) + (9)+ (10)+ (11) +(12)+ (13)	420	525	520	540	770	1030	1210	1300	700
Net Cashflow --> (14)	-154	-209	-264	-298	-457	-550	-621	-611	-68
cumulative cash --> (15)		50	-214	-511	-968	-1518	-2139	-2750	-2819
Target Cash Flow --> (16)			10	10	10	10	10	10
Surplus/Shortage --> (17) = (14) - (16)			-274	-308	-467	-560	-631	-621	-68