Question

II. WIth Excel Formulas if possible   .

Frontenac Landscaping Inc. is preparing its budget for the first months of 2019. The next step in the budgeting process is to prepare a cash receipts schedule and a cash payments schedule. To that end the following information has been collected. 10% of all sales are for Cash. Credit customers usually pay 20% of their fee in the month that service is provided, 60% the month after, 18% the second month after receiving service. The remaining 2% is written off in the third month following the service, Expected revenues are: October 2018, $100,000; November 2018, $120,000; December 2018, $150,000; January 2019, $130,000; February 2019, $180,000; March 2019, $190,000; April 2019, $200,000 Cash paid out for January purchases will be $60,000. Instructions Prepare the following schedule showing expected collections from clients for January and February of 2019. (b) Assuming that the beginning cash balance for January is expected to be $88,000, and that the only other (other than purchases, which are above) January expenses are the payroll of $90,000 and administrative expenses of $30,000, which includes $10,000 of depreciation. Cash expenses are paid within the same month. What is the ending January cash balance? Show work!!

Answer 1

a. Schedule showing the expected collections from clients for January and February 2019:

Particulars	October	November	December	January	February	March	April
Sales	$100,000	$120,000	$150,000	$130,000	$180,000	$190,000	$200,000
Cash Sales (10% of sales)	$10,000	$12,000	$15,000	$13,000	$18,000	$19,000	$20,000
Credit Sales (90% of sales)	$90,000	$108,000	$135,000	$117,000	$162,000	$171,000	$180,000
Collections from clients:
October	$18,000	$54,000	$16,200
	($90,000 * 20%)	($90,000 * 60%)	($90,000 * 18%)
November		$21,600	$64,800	$19,440
		($108,000 * 20%)	($108,000 * 60%)	($108,000 * 18%)
December			$27,000	$81,000	$24,300
			($135,000 * 20%)	($135,000 * 60%)	($135,000 * 18%)
January				$23,400	$70,200	$21,060
				($117,000 * 20%)	($117,000 * 60%)	($117,000 * 18%)
February					$32,400	$97,200	$29,160
					($162,000 * 20%)	($162,000 * 60%)	($162,000 * 18%)
Total				$136,840	$144,900

	January	February
a. Cash Sales	$13,000	$18,000
b. Expected Cash collections from Clients	$123,840	$126,900
c. Total Cash collections	$136,840	$144,900

b. Cash budget for the month of January:

Particulars	January
a. Beginning Cash balance	$88,000
b. Cash collections for January (as per above schedule)	$136,840
c. Cash Disbursements for January:
Purchases	$60,000
Payroll	$90,000
Administration expenses Excluding Depreciation	$20,000
Total Cash payments (sub-total c)	$170,000
d. Ending Cash balance for January (a + b - c)	$54,840