heet1 label BS June 30 Replicate the September 30 balance sheet on this worksheet. Sheet2 label Collections Calculate and prepare the budgeted cash collections for July, August, September, and the total cash disbursement for the quarter ended September 30th. Sheet3 label Purchases Calculate and prepare the budgeted merchandise purchases for July, August, September, and the total merchandise purchases for the quarter ended September 30th. Sheet4 label Disbursements Calculate and prepare the budgeted cash disbursements for merchandise purchases for July, August, September, and the total cash disbursements for the quarter ended September 30th. Sheet5 label BS Sept. 30th Calculate and prepare the budgeted net operating income statement and balance sheet for the quarter ended September 30th.
Applying Excel: Master Budgeting
Beech Corporation is a
|
June Corporation Balance Sheet June 30 |
|
|
Assets |
|
|
Cash |
$ 90,000 |
|
|
136,000 |
|
Inventory |
62,000 |
|
Building + equipment, net of |
210,000 |
|
Total assets |
$498,000 |
|
Liability and |
|
|
Accounts payable |
$ 71,100 |
|
Common stock |
327,000 |
|
|
99,900 |
|
Total liability and stockholder’s equity |
$498,000 |
Beech’s managers have made the following assumptions and estimates:
Estimated sales for July, August, September, and October will be $210,000, $230,000, $220,000, and $240,000 respectively.
All sales are credit and all credit sales are collected. (Note: there are no cash sales). Thirty-five percent (35%) the month’s credit sales are collected in the month the sales are made, and the remaining 65% is collected the following month. All of the June 30th accounts receivable will be collected in July.
The budgeted cost of goods sold is always 60% of sales and the ending merchandise inventory is always 30% of the following month’s cost of goods sold.
All purchases are on account. Forty percent (40%) of all purchases are paid for in the month of purchase and 60% are paid for in the following month. All of June accounts payable to suppliers will be paid during October.
Selling and administrative expenses are budgeted at $78,000, exclusive of depreciation. These expenses will be paid in cash. Depreciation is budgeted at $2,000 for the month.
On Microsoft Excel
Sheet1 label BS June 30
Replicate the September 30 balance sheet on this worksheet.
Sheet2 label Collections
Calculate and prepare the budgeted cash collections for July, August, September, and the total cash disbursement for the quarter ended September 30th.
Sheet3 label Purchases
Calculate and prepare the budgeted merchandise purchases for July, August, September, and the total merchandise purchases for the quarter ended September 30th.
Sheet4 label Disbursements
Calculate and prepare the budgeted cash disbursements for merchandise purchases for July, August, September, and the total cash disbursements for the quarter ended September 30th.
Sheet5 label BS Sept. 30th
Calculate and prepare the
Trending now
This is a popular solution!
Step by step
Solved in 7 steps with 12 images
