Question

Given the following information, construct the firm's cash budget for January, February, and March.

1. 45 percent of sales for credit and are collected after thirty days; the rest of sales are for cash.
2. Sales are December: $40,000, January: $43,000, February: $38,000, March: $35,000, and April: $33,000.
3. Monthly fixed expenses include salaries, rent, and loan repayments and are estimated to be $9,000.
4. Variable disbursements include material and wages and are estimated to be 75 percent of next month sales.
5. A tax payment of $4,500 is due in January.
6. The initial cash is $3,000.
7. The company likes to maintain a cash balance of $3,000.

Use Excel for this assignment.

Answer 1

Dear Sir/Mam,

Cash Budget     (All Amounts in Dollar)
Opening Cash Balance (A)	3000	3000	7850
Receipts:
Sales	41350	40750	36650
Total Receipts (B)	41350	40750	36650
Payments:
Monthly Fixed Expense	9000	9000	9000
Material & Wages	28500	26250	24750
Tax Payment	4500	0	0
Total Payment ( C )	42000	35250	33750
Balance D=(A+B-C)	2350	8500	10750
Loan Taken*	650	0	0
Loan Paid	0	650	0
Closing Balance	3000	7850	10750

Working Note:

* Loan is taken because in this said company is maintaining $3000 Cash Balance

Particulars	December	January	February	March	April
Sales	$40,000	$43,000	$38,000	$35,000	$33,000
Sales Amount Collected
- Current Month i.e. 45%	18000	19350	17100	15750	14850
- Next month i.e. 55%		22000	23650	20900	19250
Total		41350	40750	36650	34100
Material & Wages i.e 75% of Next Month		28500	26250	24750

I hope this clear your doubt.

Do give a thumbs up if you find this helpful.