Randall Company is a merchandising company that sells a single product. The company’s inventories, production, and sales in units for the next three months have been forecasted as follows:
October
Beginning Inventory 10,000
Merchandise Purchases 60,000
Sales 60,000
Ending Inventory 10,000
November
Beginning Inventory 10,000
Merchandise Purchases 70,000
Sales 70,000
Ending Inventory 10,000
December
Beginning Inventory 10,000
Merchandise Purchases 35,000
Sales 40,000
Ending Inventory 5,000
Units are sold for $12 each. One fourth of all sales are paid for in the month of sale and the balance are paid for in the following month. Accounts receivable at September 30 totaled $450,000. Assume it will collected in full. Merchandise is purchased for $7 per unit. Half of the purchases are paid for in the month of the purchase and the remainder are paid for in the month following purchase. Selling and administrative expenses are expected to total $120,000 each month. One half of these expenses will be paid in the month in which they are incurred and the balance will be paid in the following month. There is no depreciation. Accounts payable at September 30 totaled $290,000. Cash at September 30 totaled $80,000. A payment of $300,000 for purchase of equipment is scheduled for November, and a dividend of $200,000 is to be paid in December. Minimum Cash Balance required is $10,000. If financing in required, company has a line of credit with their bank. Borrowing is done in $1,000 increments. Annual interest rate is 6%. Borrowing is done at the beginning of the month and paid back at end of month.
Required:
a. Prepare a Excel schedule of expected cash collections for each of the months of October, November, and December.
b. Prepare a Excel schedule showing expected cash disbursements for merchandise purchases and selling and administrative expenses for each of the months October, November, and December.
c. Prepare a cash budget for each of the months October, November, and December. Include a column for the Total 3 months ended Dec 31.