Question

Esquire Products Inc. expects the following monthly sales:

January	$	33,000	July	$	27,000
February		24,000	August		31,000
March		17,000	September		34,000
April		19,000	October		39,000
May		13,000	November		47,000
June		11,000	December		29,000
Total sales = $324,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 17,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,900 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

Basis the given annual sales budget, the following table is drawn

Sales price per unit = $2

Month	Unit	Sales per month (given)	Unit	Quantity sold per month (Sales per month/sales price per unit)
January	$	33000	qty	16500
February		24000		12000
March		17000		8500
April		19000		9500
May		13000		6500
June		11000		5500
July		27000		13500
August		31000		15500
September		34000		17000
October		39000		19500
November		47000		23500
December		29000		14500
Total		324000		162000

Therefore, total quantity sold in the year is expected to be 162000 units.

Therefore monthly production = Annual / 12 = 162000 / 12 = 13500 units produced per month.

Production & Inventory schedule

	January	February	March	April	May	June	July	August	September	October	November	December
Opening Inventory	17000	14000	15500	20500	24500	31500	39500	39500	37500	34000	28000	18000
(+) Monthly Production (computed above)	13500	13500	13500	13500	13500	13500	13500	13500	13500	13500	13500	13500
(-) Monthly sales (from above table)	(16500)	(12000)	(8500)	(9500)	(6500)	(5500)	(13500)	(15500)	(17000)	(19500)	(23500)	(14500)
Monthly Closing Inventory	14000	15500	20500	24500	31500	39500	39500	37500	34000	28000	18000	17000

Cash receipts in a month = 40% of sales of current month + 60% of sales of previous month

Cash receipts schedule:

	January	February	March	April	May	June	July	August	September	October	November	December
$ Sale of the month	33000	24000	17000	19000	13000	11000	27000	31000	34000	39000	47000	29000
(A) Cash sales = 40% of current month	13200	9600	6800	7600	5200	4400	10800	12400	13600	15600	18800	11600
(B) Accounts Receivables = 60% of sale of current month	19800	14400	10200	11400	7800	6600	16200	18600	20400	23400	28200	17400
(C) Cash receipt for Accounts receivable1	20000 * 0.6= 12000	19800	14400	10200	11400	7800	6600	16200	18600	20400	23400	28200
Total Cash receipts for month (A + C)	25200	29400	21200	17800	16600	12200	17400	28600	32200	36000	42200	39800

¹60% of sales of previous month = Accounts receivables of previous month

Cost of production = $1 * monthly production

Cash payment schedule:

	January	February	March	April	May	June	July	August	September	October	November	December
Monthly production	13500	13500	13500	13500	13500	13500	13500	13500	13500	13500	13500	13500
(A) Cost of production	13500	13500	13500	13500	13500	13500	13500	13500	13500	13500	13500	13500
(B) Other payments	7900	7900	7900	7900	7900	7900	7900	7900	7900	7900	7900	7900
Total Cash Payments (A + B)	21400	21400	21400	21400	21400	21400	21400	21400	21400	21400	21400	21400

Cash Budget

	January	February	March	April	May	June	July	August	September	October	November	December
Opening Cash balance (A)	3000	6800	14800	14600	11000	6200	(3000)	(7000)	200	11000	25600	46400
(+) Cash receipts (B)	25200	29400	21200	17800	16600	12200	17400	28600	32200	36000	42200	39800
(-) Cash payments (C)	(21400)	(21400)	(21400)	(21400)	(21400)	(21400)	(21400)	(21400)	(21400)	(21400)	(21400)	(21400)
Closing cash balance (A + B - C)	6800	14800	14600	11000	6200	(3000)*	(7000)*	200*	11000	25600	46400	64800

*Since, minimum cash balance requirement of $3000 is breached, Esquire shall be required to take a short term loan here.

Current Assets schedule:

	January	February	March	April	May	June	July	August	September	October	November	December
Cash	6800	14800	14600	11000	6200	(3000)	(7000)	200	11000	25600	46400	64800
Accounts Receivables	19800	14400	10200	11400	7800	6600	16200	18600	20400	23400	28200	17400
Inventory (Closing Inventory * $1)	14000	15500	20500	24500	31500	39500	39500	37500	34000	28000	18000	17000
Total Current Assets	12600	44700	45300	46900	45500	43100	48700	56300	65400	77000	92600	99200