1
answer
0
watching
448
views
orchideel155Lv1
28 Sep 2019
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
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 |
Patrina SchowalterLv2
28 Sep 2019