Question

Create the following budgets for each quarter in the year with the data provided below. Each budget will show results for each quarter and a total for the year.

1. Sales budget
2. Cash collections budget
3. Cash budget Data

a. The budgeted unit sales are 500 units, 700 units, 800 units, and 1,000 units for quarters 1-4, respectively. The budgeted selling price for the year is $50 per unit.
b. All sales are on credit. 50% percent of all credit sales are collected in the quarter of the sale, 35% are collected in the next quarter, and the remainder is collected in the quarter after. The following data is provided to complete the cash collections budget:

1. Q1 cash collection from prior year Q4 sales: $9,000
2. Q1 cash collection from prior year Q3 sales: $2,000
3. Q2 cash collection from prior year Q4 sales: $1,000

c. The company plans to maintain a minimum cash balance at the end of each quarter of $60,000. If the cash balance is below $60,000, the company will borrow money from the bank to arrive at a $60,000 balance. All borrowings take place on the first day of the quarter and have a 12-month maturity, an interest rate of 2.5% per quarter, and interest paid quarterly. The following data is provided to complete the cash budget:

1. Beginning cash balance: $70,000
2. Q1 cash disbursement: $26,000
3. Q2 cash disbursement: $41,250
4. Q3 cash disbursement: $34.000
5. Q4 cash disbursement: $38.000

Answer 1

1. Sales Budget:

Quarter 1 Quarter 2 Quarter 3 Quarter 4 Total

Budgeted unit sales 500 700 800 1,000 3,000

Sales revenue $25,000 $35,000 $40,000 $50,000 $150,000

2. Cash collections budget:

Quarter 1 Quarter 2 Quarter 3 Quarter 4 Total

50% sales quarter $12,500 $17,500 $20,000 $25,000 $75,000

35% next quarter 9,000 8,750 12,250 14,000 44,000

15% quarter after 2,000 1,000 3,750 5,250 12,000

Total collections $23,500 $27,250 $36,000 $44,250 $131,000

3. Cash budget data:

Quarter 1 Quarter 2 Quarter 3 Quarter 4 Total

Beginning balance $70,000 $67,500 $60,325 $62,150 $70,000

Total collections $23,500 $27,250 $36,000 $44,250 $131,000

Bank borrowing 0 7,000 0 0 7,000

Total cash available $93,500 $101,750 $96,325 $106,400 $208,000

Cash disbursement $26,000 $41,250 $34,000 $38,000 $139,250

Interest expense 0 175 175 175 525

Ending balance $67,500 $60,325 $62,150 $68,225 $68,225

Minimum cash 60,000 60,000 60,000 60,000 60,000

Excess (Deficit) $7,500 $325 $2,150 $8,225 $8,225

Step-by-step explanation:

a) Data and Calculations:

Budgeted selling price per unit = $50

Quarter 1 Quarter 2 Quarter 3 Quarter 4 Total

Budgeted unit sales 500 700 800 1,000 3,000

Sales revenue $25,000 $35,000 $40,000 $50,000 $150,000

Cash collections:

50% sales quarter $12,500 $17,500 $20,000 $25,000 $75,000

35% next quarter 9,000 8,750 12,250 14,000 44,000

15% quarter after 2,000 1,000 3,750 5,250 12,000

Total collections $23,500 $27,250 $36,000 $44,250 $131,000

Minimum cash balance at the end of each quarter = $60,000

Interest rate for borrowing to reach minimum cash requirement = 2.5% per quarter

1. Beginning cash balance: $70,000

2. Q1 cash disbursement: $26,000

3. Q2 cash disbursement: $41,250

4. Q3 cash disbursement: $34.000

5. Q4 cash disbursement: $38.000