Question

1.    The financial staff at Exotic Foods Inc., a food importer, wholesaler, and distributor, has estimated the following sales figures for the first half of 2018:

Month	Sales	Other Expenses
January	$100,000	$3,000
February	$120,000	$3,200
March	$150,000	$3,500
April	$180,000	$3,800
May	$150,000	$3,500
June	$120,000	$3,200
July	$150,000	$3,500
August	$180,000	$3,800

Actual November and December 2017 sales were $200,000 and $90,000, respectively. Cash sales are 45% of the total and the rest are on credit. About 70% of credit sales are typically collected one month after the sale and 30% the second month. Monthly inventory purchases represent 50% of the following month’s sales. The firm pays 40% of its inventory purchases in cash and the remainder in the following month. Administrative wages are expected to be 10% of the month’s sales. Commissions to sales associates are estimated to be 15% of collectable sales, but the firm has decided to include a bonus of 5% more if the sales of the current month are higher than the previous one. A major capital expenditure of $40,000 is expected in April and a quarterly dividend of $20,000 will be paid to shareholders in March and June. Monthly long-term debt interest expenses and maintenance expenses are $4,000 and $1,500, respectively. Sales taxes are 5% of quarterly sales and must be paid in January, April, and July, starting in January with $19,000. The firm has an ending cash balance of $20,000 for December 2017.

a)    The firm wants to maintain a minimum cash balance of at least $15,000 with a maximum of $30,000, and will pay interest on its short-term borrowings of 5%. If the firm can earn an average rate of return of 3% on short-term investments, help the financial staff of Exotic Foods Inc. to prepare a cash budget that shows interest payments on borrowed/invested funds. Note that the firm must pay off any short-term loan outstanding before any cash surplus can be invested, and invested funds should be used instead of borrowing when needed.

*Need to create an Excel Cash Budget

Answer 1

EXOTIC FOODS INC.
Cash budget
for six months ending June 30, 2018
	January	February	March	April	May	June	Total
Beginning cash balance	20000	26150	28800	15500	15250	30433	20000
Collections from sales	112650	107350	130200	158550	161550	141450	811750
Sales tax (5% of sales)	5000	6000	7500	9000	7500	6000	41000
Cash available for disbursements	137650	139500	166500	183050	184300	177883	872750
Cash disbursements:
For purchases	54000	66000	81000	84000	69000	66000	420000
For operating expenses	33000	39200	48500	57800	41000	33200	252700
For interest	4000	4000	4000	4000	4000	4000	24000
For maintenance	1500	1500	1500	1500	1500	1500	9000
For sales tax	19000			18500			37500
For capital expenditure				40000			40000
For dividends			20000			20000	40000
Total disbursements	111500	110700	155000	205800	115500	124700	823200
Cash surplus / (deficit)	26150	28800	11500	-22750	68800	53183	49550
Minimum cash balance	15000	15000	15000	15000	15000	15000	15000
Excess / (shortfall)	11150	13800	-3500	-37750	53800	38183	34550
Financing:
Borrowing / repayment			4000	38000	-38000		4000
Interest					-367		-367
Total financing	0	0	4000	38000	-38367	0	3633
Ending Cash balance	26150	28800	15500	15250	30433	53183	53183

Sales Budget
	January	February	March	April	May	June	Total
Budgeted Sales	100000	120000	150000	180000	150000	120000	820000
Cash sales (45%)	45000	54000	67500	81000	67500	54000	369000
Credit Sales	55000	66000	82500	99000	82500	66000	451000
Collection budget
	January	February	March	April	May	June	Total
Cash sales	45000	54000	67500	81000	67500	54000	369000
Credit Sales
November sales	33000						33000
December sales	34650	14850					49500
January sales		38500	16500				55000
February sales			46200	19800			66000
March sales				57750	24750		82500
April sales					69300	29700	99000
May sales						57750	57750
June sales							0
Total collections	112650	107350	130200	158550	161550	141450	811750
Receivables	69850	82500	102300	123750	112200	90750	90750
Purchase budget
	January	February	March	April	May	June	Total
Budgeted sales	100000	120000	150000	180000	150000	120000	820000
Purchases	60000	75000	90000	75000	60000	75000	435000
(50% of next month's sales)
Payment for purchases:
December purchases	30000						30000
January purchases	24000	36000					60000
February purchases		30000	45000				75000
March purchases			36000	54000			90000
April purchases				30000	45000		75000
May purchases					24000	36000	60000
June purchases						30000	30000
Total payments for purchases	54000	66000	81000	84000	69000	66000	420000
Payables	36000	45000	54000	45000	36000	45000	45000
Operating expenses budget
	January	February	March	April	May	June	Total
Administrative wages (10% of sales)	10000	12000	15000	18000	15000	12000	82000
Sales commission (15% of sales)	15000	18000	22500	27000	22500	18000	123000
Bonus	5000	6000	7500	9000			27500
Other expenses	3000	3200	3500	3800	3500	3200	20200
Total operating expenses	33000	39200	48500	57800	41000	33200	252700