Question

During the last week of August, Oneida Company’s owner approaches the bank for a $102,000 loan to be made on September 2 and repaid on November 30 with annual interest of 12%, for an interest cost of $3,060. The owner plans to increase the store’s inventory by $60,000 during September and needs the loan to pay for inventory acquisitions. The bank’s loan officer needs more information about Oneida’s ability to repay the loan and asks the owner to forecast the store’s November 30 cash position. On September 1, Oneida is expected to have a $4,000 cash balance, $123,200 of net accounts receivable, and $100,000 of accounts payable. Its budgeted sales, merchandise purchases, and various cash disbursements for the next three months follow.

Budgeted Figures*	September		October		November
Sales	$	230,000	$	455,000	$	410,000
Merchandise purchases		225,000		220,000		196,000
Cash payments
Payroll		19,500		21,900		24,900
Rent		11,000		11,000		11,000
Other cash expenses		34,100		29,400		20,600
Repayment of bank loan						102,000
Interest on the bank loan						3,060

*Operations began in August; August sales were $160,000 and purchases were $120,000.

The budgeted September merchandise purchases include the inventory increase. All sales are on account. The company predicts that 23% of credit sales is collected in the month of the sale, 47% in the month following the sale, 19% in the second month, 7% in the third, and the remainder is uncollectible. Applying these percents to the August credit sales, for example, shows that $75,200 of the $160,000 will be collected in September, $30,400 in October, and $11,200 in November. All merchandise is purchased on credit; 50% of the balance is paid in the month following a purchase, and the remaining 50% is paid in the second month. For example, of the $120,000 August purchases, $60,000 will be paid in September and $60,000 in October.

Required:
Prepare a cash budget for September, October, and November. (Round your final answers to the nearest whole dollar.)

Answer 1

Solution Required: Cash budget for September, October, and November Oneida Company Cash Budget For September, October and November September $4,000 October November Beginning cash balance Cash receipts $109,500 $117,850 $363,050 $128,100 $243,150 Cash receipts from sales (WN-1) Bank loan $102,000 $234,100 $0 Total cash available Cash payments: Merchandise purchases (WN-2) $352,650 $480,900 $222,500 $24,900 $11,000 $20,600 $102,000 $60,000 $172,500 $21,900 $11,000 $29,400 Payroll $19,500 $11,000 Rent Other cash expenses $34,100 $0 Repayment of bank loan Interest on the bank loan $0 $3,060 Total cash payments Ending cash balance $124,600 $109,500 $384,060 $96,840 $234,800 $117,850 WN-1: Calculation of cash receipts from sales Collection in the month of November 30, Accounts Total sales Uncollectible September October November August Receivable Credit sales from: $11,200 $75,200 $160,000 $6,400 $36,800 $30,400 $0 August September $230,000 $9,200 $52,900 $108,100 $43,700 $16,100 $118,300 $299,300 October $455,000 $18,200 $104,650 $213,850 November $410,000 $16,400 $94,300 $128,100 $1,255,000 $36,800 $243,150 $363,050 Totals $50,200 $433,700 WN-2: Calculation of cash payments for merchandise Paid in the month of November 30, Total Accounts September October November August Purchases Payable Purchases from: August September $120,000 $225,000 $220,000 $60,000 $60,000 $112,500 $0 $112,500 $110,000 October $110,000 $196,000 $306,000 November $196,000 $172,500 $761,000 $60,000 $222,500 Totals $0 Note: There is a discrepency in the question. The purchases of August month is $120,000. The purchases is paid 50% in the first month following the purchase and balance 50% is paid in the second month following the purchase, hence the accounts payable as at September 1 should be $120,000. However as per the question, it is $100,000. The solution is prepared assuming accounts payable as at September 1 as $120,000.