Question

Joe’s Products Ltd is trying to plan for its cash flows. On Jan 1, 2016 it has $1000 in the bank. On average, 30% of sales are collected at the time of sale and 65% are collected the following month. Bad debt amounts to 5% of sales. Bad debts are not collected. On average, 40% of purchases each month are paid at the time of purchase and 60% are paid the following month.   Monthly Sales and Purchases are provided below.

	2015	2016
	Dec	Jan	Feb	Mar
Sales	$    1,500	$    2,500	$    1,500	$    3,500
Purchases	$    2,500	$    2,000	$    1,250	$    3,500

How much cash will be on hand at the end each of January, February and March respectively?

Answer 1

Calculation of sales amount realised in cash:

Particulars	Dec	Jan	Feb	Mar
Sales	1,500	2,500	1,500	3,500
Amount collected in the month of sale @30% (A)	(1500*30%) =450	(2500*30%) =750	(1500*30%) =450	(3500*30%) =1050
Amount collected in next month @65% (B)		(1500*65%) =975	(2500*65%) =1625	(1500*65%) =975
Total amount collected (A+B)		1725	2075	2025

*Note: Bad debts should be ignored since it is not collectable.

Calculation of amount paid on purchases:

Particulars	Dec	Jan	Feb	Mar
Purchases	2500	2000	1250	3500
Paid in the month of purchase @40% (A)	2500*40% =1000	2000*40% =800	1250*40% =500	3500*40% =1400
Paid in the next month of purchase @60% (B)		2500*60% =1500	2000*60% =1200	1250*60% =750
Total amount paid (A+B)		2300	1700	2150

Calculation of cash in hand at the end of each month:

Particulars	Jan	Feb	Mar
Cash at bank (A)	1000	425	800
Sales collected (B)	1725	2075	2025
Purchases paid (C)	2300	1700	2150
Cash in hand (D) (A+B-C)	425	800	675