ACCT 2101 Chapter : BlueInc CashBudget
Get access
Related Documents
Related Questions
ABC Company, an office supplies specialty store, prepares its master budget on a quarterly basis. | ||||||||||||
The following data have been assembled to assist in preparing the master budget for the first quarter. | ||||||||||||
a. | As of December 31 (the end of the prior quarter), the company's general ledger showed the following account balances: | |||||||||||
Debits | Credits | |||||||||||
Cash | $48,000 | |||||||||||
Accounts receivable | 195,000 | |||||||||||
Inventory | 45,600 | |||||||||||
Buildings and equipment (net) | 350,000 | |||||||||||
Acccounts payable | $79,800 | |||||||||||
Common stock | 450,000 | |||||||||||
Retained earnings | 108,800 | |||||||||||
$638,600 | $638,600 | |||||||||||
b. | Actual sales for December and budgeted sales for the next four months are as follows: | |||||||||||
December (actual) | January | February | March | April | ||||||||
$260,000 | $380,000 | $410,000 | $280,000 | $210,000 | ||||||||
c. | Sales are collected as follows: | |||||||||||
25% | collected in cash at the time of the sale | |||||||||||
75% | on credit and collected in the month following sale | |||||||||||
The accounts receivable at December 31 are a result of December credit sales. | ||||||||||||
d. | The company's gross margin as a percent of sales is | 40% | ||||||||||
In other words, cost of goods sold is 60% of sales. | ||||||||||||
e. | Monthly expenses are budgeted as follows: | |||||||||||
Salaries and wages | $35,000 | per month | ||||||||||
Advertising | $50,000 | per month | ||||||||||
Shipping | 4% | of sales | ||||||||||
Other expenses | 3% | of sales | ||||||||||
Depreciation, including depreciation on new assets acquired during the quarter, | ||||||||||||
will be | $42,000 | for the quarter. | ||||||||||
All selling and administrative expenses, except depreciation, are paid in cash in the month they are incurred. | ||||||||||||
f. Each month's ending inventory should equal | ||||||||||||
20% | of the following month's cost of goods sold | |||||||||||
g. Inventory purchases are paid for as follows: | ||||||||||||
50% | in the month of the purchase | |||||||||||
with the remaining balance paid in the following month. | ||||||||||||
h. During February, the company will purchase a new copy machine for | ||||||||||||
$2,100 | cash. | |||||||||||
During March, the company will purchase other equipment for | ||||||||||||
$76,000 | cash. | |||||||||||
i. | Cash dividends paid in January will be | $35,000 | ||||||||||
j. | Management wants to maintain a current cash balance of | $20,000 | ||||||||||
The company has an agreement with the local bank that allows the company to | ||||||||||||
borrow in increments of $1,000 at the beginning of the month. | ||||||||||||
The monthly interest rate on the loan is | 1% | |||||||||||
For simplicity, assume that the interest is not compounded. | ||||||||||||
The company would, as far as it is able, repay the loan plus any accumulated interest at the end of the quarter. | ||||||||||||
Required: Using the data above, complete the following statements and schedules for the first quarter using the formats given below. | ||||||||||||
To receive full credit, all amounts below must be entered as formulas or cell references, except for the financing section of the cash budget. | ||||||||||||
Submissions using formulas or cell references for the financing section may receive up to 3 points extra credit, | ||||||||||||
dependent upon the degree to which the formulas can be used for any and all possible scenarios. (Hint: IF statements.) | ||||||||||||
1. | Schedule of expected cash collections | January | February | March | Quarter | |||||||
Cash sales | ||||||||||||
Credit sales | check figure: | |||||||||||
Total cash collections | Total cash collections = | |||||||||||
$1,055,000 | ||||||||||||
2a. | Merchandise purchases budget | January | February | March | Quarter | |||||||
Budgeted cost of goods sold | ||||||||||||
Desired ending inventory | ||||||||||||
Total needs | ||||||||||||
Beginning inventory | ||||||||||||
Required purchases | ||||||||||||
2b. | Schedule of expected cash disbursements for merchandise purchases | |||||||||||
January | February | March | Quarter | |||||||||
December purchases | ||||||||||||
January purchases | check figure: | |||||||||||
February purchases | Total cash disbursements | |||||||||||
March purchases | for purchases = | |||||||||||
Total cash disbursements for purchases | $621,600 | |||||||||||
3. | Cash budget | January | February | March | Quarter | |||||||
Beginning cash balance | ||||||||||||
Cash collections | ||||||||||||
Total cash available | ||||||||||||
Cash disbursements: | ||||||||||||
Inventory purchases | ||||||||||||
Selling & admin. expenses | ||||||||||||
Equipment purchases | ||||||||||||
Cash dividends | ||||||||||||
Total cash disbursements | ||||||||||||
Excess (deficiency) of cash | ||||||||||||
Financing: | ||||||||||||
Borrowing | ||||||||||||
Repayment of principal | ||||||||||||
Interest | ||||||||||||
Total financing | check figure: | |||||||||||
Ending cash balance | Mar. 31 cash balance = | |||||||||||
$37,650 | ||||||||||||
4. | Prepare an absorption costing income statement for the quarter ended March 31 in the space below. | |||||||||||
check figure: | ||||||||||||
Net income = | ||||||||||||
$55,350 | ||||||||||||
5. | Prepare a balance sheet as of March 31 in the space below. | |||||||||||
check figure: | ||||||||||||
A = L + SE = | ||||||||||||
$658,950 |
Hancock Company, a merchandisingcompany, prepares its master budget on a quarterly basis. Thefollowing data have been assembled to assist in preparation of themaster budget for the second quarter.
a. As of December 31(the end of the prior quarter), the companyâs balance sheet showedthe following account balances:
Cash | $ 6,700 | |||
Accounts receivable | 36,900 | |||
Inventory | 11,130 | |||
Buildings and equipment (net) | 120,000 | |||
Accounts payable | $ 32,880 | |||
Common stock | 100,000 | |||
Retained earnings |
| 41,850 | ||
$174,730 | $174,730 | |||
b. Actual andbudgeted sales are as follows:
December (actual) | $61,500 |
January | $79,500 |
February | $88,800 |
March | $89,400 |
April | $58,100 |
c. Sales are 40% forcash and 60% on credit. All payments on credit sales are collectedin the month following the sale. The accounts receivable atDecember 31 are a result of December credit sales.
d. The companyâs grossmargin percentage is 30% of sales. (In other words, cost of goodssold is 70% of sales.)
e. Each monthâs endinginventory should equal 20% of the following month's budgeted costof goods sold.
f. One-quarter of a monthâsinventory purchases is paid for in the month of purchase; the otherthree-quarters are paid for in the following month. The accountspayable at December 31 are the result of December purchases ofinventory.
g. Monthly expenses areas follows: commissions, $12,150; rent, $2,650; other expenses(excluding depreciation), 8% of sales. Assume that these expensesare paid monthly. Depreciation is $2,550 for the quarter andincludes depreciation on new assets acquired during thequarter.
h. Equipment will beacquired for cash: $3,830 in January and $8,100 in February.
i. Management would like to maintaina minimum cash balance of $5,000 at the end of each month. Thecompany has an agreement with a local bank that allows the companyto borrow in increments of $1,000 at the beginning of each month,up to a total loan balance of $50,000. The interest rate on theseloans is 1% per month, and for simplicity, we will assume thatinterest is not compounded. The company would, as far as it isable, repay the loan plus accumulated interest at the end of thequarter.
Required:
Using the data above, complete thefollowing statements and schedules for the second quarter:
1. Schedule of expected cashcollections:
January | February | March | Total | |||
Cash sales | $31,800.00 | |||||
Credit sales | 36,900.00 |
|
|
| ||
Total collections | $68,700.00 |
|
|
| ||
2. a. Merchandise purchasesbudget:
January | February | March | Total | |||
Budgeted cost of goods | $55,650.00 | * | $62,160.00 | |||
Add desired ending inventory | 12,432.00 | â | ||||
Total needs | 68,082.00 | |||||
Less beginning inventory | 11,130.00 |
|
|
| ||
Required purchases | $56,952.00 |
|
|
| ||
*$79,500.00 sales à 70% =$55,650.00. | ||||||
â $88,800.00 Ã 70% Ã 20% =$12,432.00. |
b. Schedule of expected cashdisbursements for merchandise purchases:
January | February | March | Total | ||
December purchases | $32,880.00 | * | $32,880.00 | ||
January purchases | 14,238.00 | $42,714.00 | 56,952.00 | ||
February purchases | 0.00 | ||||
March purchases | 0.00 |
|
|
| |
Total cash disbursements forpurchases | $47,118.00 |
|
|
| |
*Beginning balance of the accountspayable. |
3. Schedule of expected cashdisbursements for selling and administrative expenses:
January | February | March | Total | |
Commissions | $12,150.00 | |||
Rent | 2,650.00 | |||
Other expenses | 6,360.00 |
|
|
|
Total cash disbursements forselling | $21,160.00 |
|
|
|
4. Cash budget:
January | February | March | Total | |
Cash balance, beginning | $ 6,700.00 | |||
Add cash collections | 68,700.00 |
|
|
|
Total cash available | 75,400.00 |
|
|
|
Less cash disbursements: | ||||
For inventory | 47,118.00 | |||
For operating expenses | 21,160.00 | |||
For equipment | 3,830.00 |
|
|
|
Total cash disbursements | 72,108.00 |
|
|
|
Excess (deficiency) of cash | 3,292.00 | |||
Financing | ||||
Etc. |
5. Prepare an incomestatement for the quarter ending March 31 as shown in Chapter7.
6. Prepare a balancesheet as of March 31.