Question

Harry’s Carryout Stores has eight locations. The firm wishes to expand by two more stores and needs a bank loan to do this. Mr. Wilson, the banker, will finance construction if the firm can present an acceptable three-month financial plan for January through March. The following are actual and forecast sales figures:

Actual			Forecast			Additional Information
November	$300,000		January	$380,000		April forecast	$390,000
December	320,000		February	420,000
			March	400,000

Of the firm’s sales, 50 percent are for cash and the remaining 50 percent are on credit. Of credit sales, 35 percent are paid in the month after sale and 65 percent are paid in the second month after the sale. Materials cost 30 percent of sales and are purchased and received each month in an amount sufficient to cover the following month’s expected sales. Materials are paid for in the month after they are received. Labor expense is 40 percent of sales and is paid for in the month of sales. Selling and administrative expense is 15 percent of sales and is paid in the month of sales. Overhead expense is $30,500 in cash per month.

Depreciation expense is $10,500 per month. Taxes of $8,500 will be paid in January, and dividends of $4,500 will be paid in March. Cash at the beginning of January is $90,000, and the minimum desired cash balance is $85,000.

a. Prepare a schedule of monthly cash receipts for January, February, and March.
  

b. Prepare a schedule of monthly cash payments for January, February, and March.
  

c. Prepare a monthly cash budget with borrowings and repayments for January, February, and March. (Negative amounts should be indicated by a minus sign. Assume the January beginning loan balance is $0.)

  

Answer 1

Part (a)

Please see the table below. Please be guided by the second column titled “Linkage” to understand the mathematics. The last row highlighted in yellow is your answer. Figures in parenthesis, if any, mean negative values. All financials are in $.

Month	Linkage	November	December	January	February	March	April
Sales	A	$300,000	320,000	$380,000	420,000	400,000	$390,000
Cash sales	B = 50% x A	$150,000	$160,000	$190,000	$210,000	$200,000	$195,000
Credit Sales	C = 50% x A	$150,000	$160,000	$190,000	$210,000	$200,000	$195,000
Collection from credit sales	D = 35% x C(m-1) + 65% x C(m - 2)*		$52,500	$153,500	$170,500	$197,000	$206,500
Monthly cash receipts	E = B + D			$343,500	$380,500	$397,000

* Collection from credit sales = 35% x Credit sales of last month + 65% x Credit sales two months back. To explain, the figure under the head of January = 153,500 = 35% x 160,000 + 65% x 150,000

Part (b)

Cash payments	Linkage	November	December	January	February	March	April
Purchases	F = A x 30%	96,000	114,000	126,000	120,000	117,000	-
Payment on account of purchases	G = shift F above by 1 month		96,000	114,000	126,000	120,000	117,000
Labor expenses payment	H = 40% x A	120,000	128,000	152,000	168,000	160,000	156,000
Selling & admin expenses payment	I = 15% x A	45,000	48,000	57,000	63,000	60,000	58,500
Overhead expenses	J	30,500	30,500	30,500	30,500	30,500	30,500
Taxes	K			8,500
Dividends	L					4,500
Monthly cash payments	M = G + H + I + J + K			362,000	387,500	375,000

Part (c)

Month	Linkage	November	December	January	February	March
Cash receipts	A			$343,500	$380,500	$397,000
Cash payments	M			362,000	387,500	375,000
Opening balance in cash	N			$90,000	$85,000	$85,000
Likely closing balance without any external capital raise	O = N + A - M			71,500	78,000	107,000
Minimum Desired closing balance	P			85,000	85,000	85,000
Surplus / (Shortfall)	Q = O - P			(13,500)	(7,000)	22,000
Fresh borrowing	R = max (-Q, 0)			13,500	7,000	-
Repayment	S = max (Q , 0)					22,000
Actual closing balance	T = O + R - S			$85,000	$85,000	$85,000