Question

Esquire Products Inc. expects the following monthly sales:

January	$	31,000	July	$	25,000
February		22,000	August		29,000
March		15,000	September		32,000
April		17,000	October		37,000
May		11,000	November		45,000
June		9,000	December		27,000
Total sales = $300,000

Cash sales are 40 percent in a given month, with the remainder going into accounts receivable. All receivables are collected in the month following the sale. Esquire sells all of its goods for $2 each and produces them for $1 each. Esquire uses level production, and average monthly production is equal to annual production divided by 12.

a. Generate a monthly production and inventory schedule in units. Beginning inventory in January is 15,000 units.

b. Prepare a cash receipts schedule for January through December. Assume that dollar sales in the prior December were $20,000

c. Prepare a cash payments schedule for January through December. The production costs ($1 per unit produced) are paid for in the month in which they occur. Other cash payments (besides those for production costs) are $7,700 per month.

d. Construct a cash budget for January through December using the cash receipts schedule from part b and the cash payments schedule from part c. The beginning cash balance is $3,000, which is also the minimum desired. (Negative amounts should be indicated by a minus sign.)

e. Determine total current assets for each month. Include cash, accounts receivable, and inventory. The accounts receivable for a given month is equal to 60 percent of that month's sales. Inventory is equal to ending inventory (part a) times the cost of $1 per unit.
  

Answer 1

	Monthly Production =150000/12=	12,500
			A	B	C	D=C/2	E=A+B-D
			Beginning Inventory(Units)	Production (Units)	Sales(Dollar)	Sales (Units)	Ending Inventory
		January	15000	12,500	$31,000	15,500	12,000
		February	12,000	12,500	$22,000	11,000	13,500
		March	13,500	12,500	$15,000	7,500	18,500
		April	18,500	12,500	$17,000	8,500	22,500
		May	22,500	12,500	$11,000	5,500	29,500
		June	29,500	12,500	$9,000	4,500	37,500
		July	37,500	12,500	$25,000	12,500	37,500
		August	37,500	12,500	$29,000	14,500	35,500
		September	35,500	12,500	$32,000	16,000	32,000
		October	32,000	12,500	$37,000	18,500	26,000
		November	26,000	12,500	$45,000	22,500	16,000
		December	16,000	12,500	$27,000	13,500	15,000
		TOTAL		150,000	$300,000	150,000
	Accounts Receivable End of December	$12,000	(20000*60%)
			A	B=A*40%	C	D=B+C
	CASH RECEIPT SCHEDULE		Sales(Dollar)	Receipts from Cash Sales	Receipts from Accounts Receivable	Total Cash Receipts
		January	$31,000	$12,400	$12,000	$24,400
		February	$22,000	$8,800	$18,600	$27,400
		March	$15,000	$6,000	$13,200	$19,200
		April	$17,000	$6,800	$9,000	$15,800
		May	$11,000	$4,400	$10,200	$14,600
		June	$9,000	$3,600	$6,600	$10,200
		July	$25,000	$10,000	$5,400	$15,400
		August	$29,000	$11,600	$15,000	$26,600
		September	$32,000	$12,800	$17,400	$30,200
		October	$37,000	$14,800	$19,200	$34,000
		November	$45,000	$18,000	$22,200	$40,200
		December	$27,000	$10,800	$27,000	$37,800
	CASH PAYMENT SCHEDULE		A=12500*$1	B	C=A+B
			Payment for Production	Other Cash Payments	Total Payments
		January	$12,500	$7,700	$20,200
		February	$12,500	$7,700	$20,200
		March	$12,500	$7,700	$20,200
		April	$12,500	$7,700	$20,200
		May	$12,500	$7,700	$20,200
		June	$12,500	$7,700	$20,200
		July	$12,500	$7,700	$20,200
		August	$12,500	$7,700	$20,200
		September	$12,500	$7,700	$20,200
		October	$12,500	$7,700	$20,200
		November	$12,500	$7,700	$20,200
		December	$12,500	$7,700	$20,200
	CASH BUDGET		A	B	C=A+B	D	E=C-D
			Beginning Balance	Cash Receipt	Total Cash Available	Cash Payment	Ending Balance
		January	$3,000	$24,400	$27,400	$20,200	$7,200
		February	$7,200	$27,400	$34,600	$20,200	$14,400
		March	$14,400	$19,200	$33,600	$20,200	$13,400
		April	$13,400	$15,800	$29,200	$20,200	$9,000
		May	$9,000	$14,600	$23,600	$20,200	$3,400
		June	$3,400	$10,200	$13,600	$20,200	($6,600)
		July	($6,600)	$15,400	$8,800	$20,200	($11,400)
		August	($11,400)	$26,600	$15,200	$20,200	($5,000)
		September	($5,000)	$30,200	$25,200	$20,200	$5,000
		October	$5,000	$34,000	$39,000	$20,200	$18,800
		November	$18,800	$40,200	$59,000	$20,200	$38,800
		December	$38,800	$37,800	$76,600	$20,200	$56,400