Question

Utilizing the information provided by Juan Carlos, assist him to determine the monthly cash inflows and needs for his Café.

The following table provides a breakdown of the cash inflows and outflows for September through January. The September figures are provided, so all you have to do is to place them in the correct row in the chart. Credit and cash sales are projected to grow at 10% each month, which can be calculated by simply multiplying the previous month’s sales figure by 1.1 (26,500 x1.1= 29,150). The same calculation can be utilized for increased credit and cash purchases, but instead of multiplying by 1.1, you would multiply by 1.05 to account for the 5% increase.

	September	October	November	December	January
Credit Sales	$26,500	$29,150	$32,065	$35,272	$38,799
90% collected in next month	23,850	26,235	28.859	31,744	34,919
10% collected in two months	2,650	2,915	3,207	3,527	3,880
Cash Sales	13.500	14,850	16,335	17,969	19,765
Credit Purchases—paid next month	16,450	17,273	18,136	19,043	19,995
Cash Purchases	2,345	2,462	2,585	2,715	2,850
Payroll—32% of total sales	12,800	14,080	15,488	17,037	18,740
Service on Mortgage per Month	6,500	6,500	6,500	6,500	6,500
Monthly Insurance Payment	2,000	2,000	2,000	2,000	2,000

Develop a ninety-day cash forecast for the Cafe for November, December, and January. Assume a beginning cash balance of $5,000 for November (Use the example in the class for Theresa’s Catering as a guide).

Required:

1. The first item to consider is the beginning cash balance, which is $5,000 for the Cafe.
   
2. The first major section in the ninety-day cash forecast is cash receipts. Input the amount of cash sales for November, credit sales for October that were collectable in one month, and credit sales for September that were collectable in two months, and add them to get total cash available.
   
3. The next section, which is cash disbursements, includes all of the cash outflows. This section includes cash purchases for November, credit purchases from October that were payable in one month, payroll expenses (32% of monthly sales), monthly debt service payment, and monthly insurance premium payment. Now, total the cash disbursements for November.
4. In order to determine the ending cash balance, simply subtract total cash disbursements from total cash available. Now you can do the same calculations for December and January.
   

Analyze the forecasts and explain how the Cafe is expected to perform. Assume the Café’s proposed minimum monthly cash balance is $15,000. Does the cash forecast look optimistic?

Answer 1

Description				November	December	January
Sale-Quantity	a
Cash Sale	A			$    16,335	$ 17,969	$ 19,765
Credit Sale		$ 26,500	$ 29,150	$    32,065	$ 35,272	$ 38,799
Credit sale collection- next month-90%	B			$    26,235	$ 28,859	$ 31,745
Credit sale collection- next following month-10%	C			$      2,650	$    2,915	$    3,207
Monthly sale collection- Cash sale + Credit sale collection same month + Credit sale collection next month	D=A+B+C			$    45,220	$ 49,743	$ 54,716
Raw material payment- cash				$      2,585	$    2,715	$    2,850
Raw material payment- Next month- 20%				$    17,273	$ 18,136	$ 19,043
Total Payment				$    19,858	$ 20,851	$ 21,893
Opening cash				$      5,000	$    6,374	$    9,729
Budgeted Receipts	D			$    45,220	$ 49,743	$ 54,716
Budgeted Cash payments for AP				$   (19,858)	$ (20,851)	$ (21,893)
Payroll payment				$   (15,488)	$ (17,037)	$ (18,740)
Mortgage service				$     (6,500)	$   (6,500)	$   (6,500)
Montly insurance payment				$     (2,000)	$   (2,000)	$   (2,000)
Closing cash balance				$      6,374	$    9,729	$ 15,312
The firm will not have minimum cash balance for November and December however from January onwards, minimum balance can be maintained.