Question

Table 12 - Budgeted Statement of Cash Flows for Fiscal Year Beginning 12/1/2020 $ Quarter 1 $ 2,272,200 1,817,760 400,000 $ 2,217,760 Quarter 2 $ 2,272,200 1,817,760 454,440 $ 2,272,200 Quarter 3 $ 2,272,200 1,817,760 454,440 $ 2,272,200 Quarter 4 $ 2,272,200 1,817,760 454,440 $ 2,272,200 Annual 9,088,800 7,271,040 1,763,320 9,034,360 $ $ $ $ - $ Sales in the quarter 80% collected in this quarter 20% collected from last quarter Cash from sales Less: Almond purchases Direct labor Other ingredients Variable overheads Fixed factoy overhead Administrative costs Income taxes Total cash expenses before interest Cash flow from operations Beginning cash balance Less: minimum cash revenuews Cash available for operations New Short-term borrowings Repayment of loan and interest Outstanding loan balance Interest at 4% per quarter Capital expenditures Ending cash balance 188,030 449,350 473,600 544,667 100,000 175,000 128,660 2,059,307 158,453 2,000,000 200,000 1,800,000 188,030 449,350 473,600 544,667 100,000 175,000 128,660 2,059,307 212,893 200,000 200,000 188,030 449,350 473,600 544,667 100,000 175,000 128,660 2,059,307 212,893 200,000 200,000 - 564,090 1,348,050 1,420,800 1,634,000 400,000 700,000 514,639 6,581,579 2,452,781 1-09 S $ $ S S $ $ S $ 100,000 175,000 128,660 403,660 1,868,540 278,500 200,000 78,500 $ S $ S $ $ $ $ $ $ S 88,000 - 16,000 - $19,140 16,000 - S $ $

Need to calculate the ending cash balance for 4 quarters in the above spreadsheet.

Answer 1

	Quarter 1	Quarter 2	Quarter 3	Quarter 4	Annual
Sales in the quarter	$22,72,200	$22,72,200	$22,72,200	$22,72,200	$90,88,800
80% collected in this quarter	$18,17,760	$18,17,760	$18,17,760	$18,17,760	$72,71,040
20% collected from last quarter	$4,00,000	$4,54,440	$4,54,440	$4,54,440	$17,63,320
Cash from sales	$22,17,760	$22,72,200	$22,72,200	$22,72,200	$90,34,360
Less:
Almond purchases	$1,88,030	$1,88,030	$1,88,030	$0	$5,64,090
Direct labour	$4,49,350	$4,49,350	$4,49,350	$0	$13,48,050
Other ingredients	$4,73,600	$4,73,600	$4,73,600	$0	$14,20,800
Variable overheads	$5,44,667	$5,44,667	$5,44,667	$0	$16,34,001
Fixed factory overhead	$1,00,000	$1,00,000	$1,00,000	$1,00,000	$4,00,000
Administrative costs	$1,75,000	$1,75,000	$1,75,000	$1,75,000	$7,00,000
Income Taxes	$1,28,660	$1,28,660	$1,28,660	$1,28,660	$5,14,640
Total cash expenses before interest	$20,59,307	$20,59,307	$20,59,307	$4,03,660	$65,81,581
Cash flow from operations	$1,58,453	$2,12,893	$2,12,893	$18,68,540	$24,52,779
Beginning cash balance	$20,00,000	$21,58,453	$23,71,346	$25,84,239	$20,00,000
Less: Minimum cash reserve	$2,00,000	$2,00,000	$2,00,000	$2,00,000	$2,00,000
Cash available for operations	$19,58,453	$21,71,346	$23,84,239	$42,52,779	$42,52,779
New-short term borrowings	$0	$0	$0	$0	$0
Repayment of loan and interest	$0	$0	$0	$0	$0
Outstanding Loan Balance	$0	$0	$0	$0	$0
Interest at 4% per quarter	$0	$0	$0	$0	$0
Capital expenditures	$0	$0	$0	$0	$0
Ending Cash Balance	$21,58,453	$23,71,346	$25,84,239	$44,52,779	$44,52,779

Interest @ 4% per quarter will be charged if there is any borrowings. But here, there are no borrowings in any quarter because Total cash available (Beginning Balance + Cash flow from operations) is more than minimum cash reserve required of $200000 for all the quarters.