Question

Requirement#1:

Based on total 2015 trading commissions of $4,000,000, JEI management has forecasted the following monthly growth in trading commissions and monthly cash collection measures for 2016:

• Create a 12-month cash flow budget in Excel using the following assumptions:

• Annual 2015 commissions of $4,000,000 with forecasted monthly growth of 2%

• 45% of each month’s sales for cash; 25% collected the following month; 20% collected 2 months later; 8% collected 3 months later; and 2% never collected

• Initial cash balance of $300,000 Assuming an initial cash balance of $300,000, management has concluded that they will achieve an ending cash balance of $41,000,000.

Requirements#2:

Evaluate management’s conclusion but show calculations to support your decision. Your response should be addressed to: Director of Internal Audit – John Exchanging Integrated Inc.

Answer 1

	Month
	1	2	3	4	5	6	7	8	9	10	11	12	Total
Commission Receivable (4000000/12) growing by by 2% every month	$340,000	$346,800	$353,736	$360,811	$368,027	$375,387	$382,895	$390,553	$398,364	$406,331	$414,458	$422,747	$4,560,111
Estimated Collection (45% cash; 25% next month 20% collected 2 months later; 8% collected 3 months later; and 2% never collected)
1	$153,000	$85,000	$68,000	$27,200									$333,200
2		$156,060	$86,700	$69,360	$27,744								$339,864
3			$159,181	$88,434	$70,747	$28,299							$346,661
4				$162,365	$90,203	$72,162	$28,865						$353,595
5					$165,612	$92,007	$73,605	$29,442					$360,666
6						$168,924	$93,847	$75,077	$30,031				$367,880
7							$172,303	$95,724	$76,579	$30,632			$375,237
8								$175,749	$97,638	$78,111	$31,244		$382,742
9									$179,264	$99,591	$79,673	$31,869	$390,397
10										$182,849	$101,583	$81,266	$365,698
11											$186,506	$103,615	$290,121
12												$190,236	$190,236
Total	$153,000	$241,060	$313,881	$347,359	$354,306	$361,392	$368,620	$375,992	$383,512	$391,182	$399,006	$406,986	$4,096,297
Cash Budget	Month
	1	2	3	4	5	6	7	8	9	10	11	12
Commission Collection	$153,000	$241,060	$313,881	$347,359	$354,306	$361,392	$368,620	$375,992	$383,512	$391,182	$399,006	$406,986
Opening Cash	$300,000	$453,000	$694,060	$1,007,941	$1,355,300	$1,709,606	$2,070,998	$2,439,618	$2,815,610	$3,199,123	$3,590,305	$3,989,311
Closing Cash	$453,000	$694,060	$1,007,941	$1,355,300	$1,709,606	$2,070,998	$2,439,618	$2,815,610	$3,199,123	$3,590,305	$3,989,311	$4,396,297