Problem:
Baxter Bagels has requested that you prepare a cash budget for the month of June. The following information has been provided:
The beginning cash balance is $10,000. The budgeted sales for
March,April,May, and June are as follows:
March April May June
Cash Sales $14,000 $16,500 $15,500 $17,500
Sales on Acct 29,000 30,000 40,000 50,000
Total Sales 43,000 46,500 55,500 50,000
Sales on account are collected over a two-month period, with 70 percent being collected in the first month and the remainder being collected in the second month.
Inventory purchases are budgeted to be 60 percent of budgeted sales each month. The company pays for inventory purchases in the month following the purchase.
Selling and administrative expenses are budgeted to be 30percent of each month's sales. Of that amount,50percent is depreciation. Equipment costing $24,00 will be purchased in April for cash, and dividends will be paid in the amount of $12,500 in the same month.
The company wants to maintain a minimum cash balance of $10,000 and has set up a line of credit to cover any shortage. If the company must borrow, the loan will be made at the beginning of the month, and any repayment will be made at the end of the month of repayment.
Answers to following must be in EXCEL format.
A) Prepare a cash receipts budget for April, May, and June.
B) Prepare a cash disbursements budget for April, May, and June.
C) Using the preceding budgets, prepare a schedule of any financing needed for the months of April, May, and June.