Explore BrainMass

ABC Co Master Budget: Quarterly Cash Flows

You will be required to develop a schedule of quarterly cash flows.
The company wants to maintain minimum cash balance at the end of each quarter at >>>>> $150,000
Money can be borrowed or repaid in multiples of $5,000 at an interest rate of 10% annually.
Management does not want to borrow any more cash than is necessary and wants to repay as
promptly as possible. Loans may not extend beyond four quarters. Interest expense is computed
and paid when the principal is repaid. Assume that borrowing of funds takes place at the beginning
and repayments at the end of each of the quarters. Compute interest expense to the nearest dollar.

The following information is based on the cash effects of the operations in the Schedules you developed
in Part A of this case.

1 2 3 4

Collections from customers $1,250,000 $1,500,000 $1,600,000 $2,210,000
For direct materials $200,000 $350,000 $350,000 $542,000
For other costs and expenses $250,000 $200,000 $200,000 $170,000
For payroll $900,000 $950,000 $950,000 $1,092,000
For income taxes $50,000 $0 $0 $0
For machinery purchases $0 $0 $0 $200,000

NOTE: To develop the cash budget, you will need to refer to additional data from the
schedules you developed in Part A.


(A) Prepare the quarterly and annual cash budget for 2008. Show cash receipts
and disbursements including details of borrowings, repayments, and interest expense.
(check figures: 1st Quarter ending cash = $150,000; Year-end cash = $238,250)

(B) Prepare budgeted income statement, including effects of interest expense and
assumed income taxes for 2008 as >>>>>> $200,000
Check figure: Net Income >>>>>>>>>>>> $445,250

( C ) Prepare a budgeted year-end balance sheet. Start with last year's balance sheet.
Show supporting calculations for each account in the balance sheet.

NOTE: The schedules/statements should be prepared as linked spreadsheets,
except for financing and borrowing calculations.


Solution Summary

Your tutorial is attached in excel with all the cells linked. This required an analysis of accounts payable so that the cell would not have a formula so complicated that you could not easily follow the work and study and learn this process.