Share
Explore BrainMass

Cash Budget Analysis- missing data

A cash budget, by quarter, is given below for a retail company. The company requires a minimum cash balance of at least $5,000 to start each quarter.

Required: Fill in the missing amounts in the table below:

QUARTER
1 2 3 4 Year
Cash Balance, beginning..................$9 $? $? $? $?
Add collections from customers........... ? ? 125 ? 391
Total Cash available.....................85 ? ? ? ?

Less disbursements:
Purchase of inventory....................40 58 ? 32 ?
Operating expenses....................... ? 42 54 ? 180
Equipment purchases...................... 10 8 8 ? 36
Dividends.................................2 2 2 2 ?
Total Disbursements.......................? 110 ? ? ?

Excess (deficiency) of cash available
over disbursements........................(3) ? 30 ? ?

Financing:
Borrowings.................................? 20 -- -- ?
Repayments (including interest)............-- -- (?) (7) (?)

Total financing............................? ? ? ? ?

Cash balance, ending......................$? $? $? $? $?

*Interest will total $4,000 for the year.

Solution Preview

Please see the attachment. Please see the cell formula to know how the figures have been calculated.
I am giving the calculation details below
Quarter 1
Add collections = 85-9 = 76
Total disbursement = 85+3 = 88 (cash available +excess deficiency)
Operating Expense = 88-sum of others (88 is calculated above)
Borrowing = 8, so that minimum balance = 5
Total financing = 8
Cash balance ending = 5
Quarter 2
Cash balance beginning = 5 (the ending of last quarter)
Since borrowing is 20, so excess/deficiency should be -15 ...

Solution Summary

The solution explains how to calculate the missing data for the given cash budget

$2.19