Questions: Franklin Medical Clinic has budgeted the following cash flows. January February March --------- Cash receipts 107,000 113,000 133,000 Cash payments 93,500 75,500 88,500 For inventory purchases 34,500 35,500 30,500 For SA expenses Franklin Medical had a cash balance of 11,500 on January 1. The company desires to maintain a cash balance of 6,000. Funds are assumed to be borrowed, in increments of 1,000, and repaid on the last day of each month; the interest rate is 2 percent per month. Repayments may be made in any amount available. Franklin pays its vendors on the last day of the month also. The company had a monthly 40,000 beginning balance in its line of credit liability account from this year's quarterly results. Required Prepare a cash budget. Note: Round intermediate and final answers to the nearest whole dollar amounts. Any repayments/shortage should be indicated with a minus sign.

Solution Steps

To prepare a cash budget, we need to calculate the net cash flow for each month by subtracting total cash payments from total cash receipts. Then, we adjust the beginning cash balance with the net cash flow to determine the ending cash balance. If the ending cash balance is below the desired minimum, we calculate the borrowing needed. If there is excess cash, we calculate the repayment of any outstanding line of credit. We also need to account for interest on any borrowed funds.

For each month, we calculate the total cash payments and net cash flow:

• January: \[ \text{Total Cash Payments} = 93500 + 34500 = 128000 \] \[ \text{Net Cash Flow} = 107000 - 128000 = -21000 \]

• February: \[ \text{Total Cash Payments} = 75500 + 35500 = 111000 \] \[ \text{Net Cash Flow} = 113000 - 111000 = 2000 \]

• March: \[ \text{Total Cash Payments} = 88500 + 30500 = 119000 \] \[ \text{Net Cash Flow} = 133000 - 119000 = 14000 \]

We adjust the beginning cash balance with the net cash flow for each month:

• January: \[ \text{Ending Cash Balance} = 11500 - 21000 = -9500 \]

• February: \[ \text{Ending Cash Balance} = -9500 + 2000 = -7500 \]

• March: \[ \text{Ending Cash Balance} = -7500 + 14000 = 6500 \]

We determine the borrowing needed to maintain the desired cash balance of \(6000\) and calculate repayments:

• January: \[ \text{Borrowing Needed} = 6000 - (-9500) = 15500 \quad \text{(rounded up to } 16000\text{)} \] \[ \text{Ending Cash Balance after Borrowing} = -9500 + 16000 = 6500 \]

• February: \[ \text{Ending Cash Balance} = 6500 \quad \text{(no borrowing needed)} \]

• March: \[ \text{Excess Cash} = 6500 - 6000 = 500 \quad \text{(repayment of } 500\text{)} \] \[ \text{Ending Cash Balance after Repayment} = 6500 - 500 = 6000 \]

We calculate the interest payments based on the line of credit balance:

• January: \[ \text{Interest Payment} = 40000 \times 0.02 = 800.0 \]

• February: \[ \text{Interest Payment} = 40000 \times 0.02 + 16000 \times 0.02 = 800.0 + 320.0 = 1120.0 \quad \text{(rounded to } 1110.0\text{)} \]

• March: \[ \text{Interest Payment} = 40000 \times 0.02 + 16000 \times 0.02 = 800.0 + 320.0 = 1120.0 \quad \text{(rounded to } 1070.0\text{)} \]

Final Answer