Esquire Products, Inc

Esquire Products, Inc

Esquire Products, Inc. expects the following monthly sales: January 24,000 February 15,000 March 8,000 April 10,000 May 4,000 June 2,000 July 18,000 August 22,000 September 25,000 October 30,000 November 38,000 December 20,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 8,000 units. (Note: To do part a, you should work interms of units of production and units of sales.)
b. Determine a cash receipts schedule for January through December. Assume that $ sales in the prior December were $20,000. Work part b using dollars.
c. Determine 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,000 per month.
d. Construct a cash budget for January through December using the cash receipts schedule from part and the cash payments schedule from part c. The beginning cash balance is $3,000, which is also the minimum desired.

Solution

Esquire Products, Inc.

a.Production and inventory schedule in units

Beginning
Inventory / + / Production1 / – / Sales2 / = / Ending
Inventory
Jan. / 8,000 / + / 9,000 / – / 12,000 / = / 5,000
Feb. / 5,000 / 9,000 / 7,500 / 6,500
Mar. / 6,500 / 9,000 / 4,000 / 11,500
Apr. / 11,500 / 9,000 / 5,000 / 15,500
May / 15,500 / 9,000 / 2,000 / 22,500
June / 22,500 / 9,000 / 1,000 / 30,500
July / 30,500 / 9,000 / 9,000 / 30,500
Aug. / 30,500 / 9,000 / 11,000 / 28,500
Sept. / 28,500 / 9,000 / 12,500 / 25,000
Oct. / 25,000 / 9,000 / 15,000 / 19,000
Nov. / 19,000 / 9,000 / 19,000 / 9,000
Dec. / 9,000 / 9,000 / 10,000 / 8,000

1 $168,000 sales/$2 price = 84,000 units

84,000 units/12 months = 7,000 units per month

2 Monthly dollar sales/$2 = number of units

b.Cash Receipts Schedule (take dollar values from problem statement)

Jan. / Feb. / Mar. / Apr. / May / June
Sales (in dollars) / $24,000 / $15,000 / $ 8,000 / $10,000 / $4,000 / $2,000
40% Cash sales / 9,600 / 6,000 / 3,200 / 4,000 / 1,600 / 800
60% Prior month's sales / 12,000* / 14,400 / 9,000 / 4,800 / 6,000 / 2,400
Total receipts / $21,600 / $20,400 / $12,200 / $ 8,800 / $ 7,600 / $ 3,200
*based on December sales of $20,000

July

/ Aug. / Sept. / Oct. / Nov. / Dec.
Sales (in dollars) / $18,000 / $22,000 / $25,000 / $30,000 / $ 38,000 / $ 20,000
40% Cash sales / 7,200 / 8,800 / 10,000 / 12,000 / 15,200 / 8,000
60% Prior month's sales / 1,200 / 10,800 / 13,200 / 15,000 / 18,000 / 22,800
Total receipts / $ 8,400 / $19,800 / $23,200 / $27,000 / $33,200 / $ 30,800

c.Cash Payments Schedule: Constant production

Jan. / Feb. / Mar. / Apr. / May / June
9,000 units  $1 / $ 9,000 / $ 9,000 / $ 9,000 / $ 9,000 / $ 9,000 / $ 9,000
Other cash payments / $ 7,000 / $ 7,000 / $ 7,000 / $ 7,000 / $ 7,000 / $ 7,000
Total payments / $16,000 / $16,000 / $16,000 / $16,000 / $16,000 / $16,000
July / Aug. / Sept. / Oct. / Nov. / Dec.
9,000 units  $1 / $ 9,000 / $ 9,000 / $ 9,000 / $ 9,000 / $ 9,000 / $ 9,000
Other cash payments / $ 7,000 / $ 7,000 / $ 7,000 / $ 7,000 / $ 7,000 / $ 7,000
Total payments / $16,000 / $16,000 / $16,000 / $16,000 / $16,000 / $16,000

d.Cash Budget

Jan. / Feb. / Mar. / Apr. / May / June
Cash flow / $ 5,600 / $ 4,400 / ($ 3,800) / ($ 7,200) / ($8,400) / ($12,800)
Beginning cash / 3,000 / 8,600 / 13,000 / 9,200 / 3,000 / 3,000
Cumulative cash balance / 8,600 / 13,000 / 9,200 / 2,000 / (5,400) / (9,800)
Monthly loan or (repayment) /  0  /  0  /  0  / 1,000 / 8,400 / 12,800
Cumulative loan /  0  /  0  /  0  / 1,000 / 9,400 / 22,200
Ending cash balance / $8,600 / $13,000 / $9,200 / $3,000 / $3,000 / $3,000
July / Aug. / Sept. / Oct. / Nov. / Dec.
Cash flow / ($ 7,600) / $ 3,600 / $ 7,200 / $11,000 / $17,200 / $14,800
Beginning cash / 3,000 / 3,000 / 3,000 / 3,000 / 3,000 / 12,200
Cumulative cash balance / (4,600) / 6,600 / 10,200 / 14,000 / 20,200 / 27,000
Monthly loan or (repayment) / 7,600 / (3,600) / (7,200) / (11,000) / (8,000) /  0 
Cumulative loan / 29,800 / 26,200 / 19,000 / 8,000 /  0  /  0 
Ending cash balance / $3,000 / $3,000 / $3,000 / $3,000 / $12,200 / $27,000

e.Assets

Cash / Accounts Receivable / Inventory / Total Current
Jan. / $8,600 / $14,400 / $ 5,000 / $28,000
Feb. / 13,000 / 9,000 / 6,500 / 28,500
Mar. / 9,200 / 4,800 / 11,500 / 25,500
Apr. / 3,000 / 6,000 / 15,500 / 24,500
May / 3,000 / 2,400 / 22,500 / 27,900
June / 3,000 / 1,200 / 30,500 / 34,700
July / 3,000 / 10,800 / 30,500 / 44,300
Aug. / 3,000 / 13,200 / 28,500 / 44,700
Sept. / 3,000 / 15,000 / 25,000 / 43,000
Oct. / 3,000 / 18,000 / 19,000 / 40,000
Nov. / 12,200 / 22,800 / 9,000 / 44,000
Dec. / 27,000 / 12,000 / 8,000 / 47,000