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