Carter Company is a bookstore that prepares its master g$.$ One$-$half of a month's inventory purchases is

paid for in the month of purchase; the other half is

paid for in the following month.

h$.$ During February, the company will purchase a

new copy machine for $$1,700$ cash. During

March, other equipment will be purchased for cash

at a cost of $$84,500.$

i$.$ During January, the company will declare and

pay $$45,000$ in cash dividends.

j$.$ The company must maintain a minimum cash

balance of $$30,000.$ An open line of credit is

available at a local bank for any borrowing that

may be needed during the quarter. All borrowing

is done at the beginning of a month and all

repayments are made at the end of a month.

Borrowing and repayments of principal must be in

multiples of $$1,000.$ Interest is paid only at the

time of repayment of principal. The annual

interest rate is $12\%.($Figure out interest on whole

months, example, $\frac{1}{12},\frac{2}{12},$ etc.$).$

The question states that borrowing occurs at the

beginning of the month and loan repayments are

made at the end of the month. In addition, it says

borrowing is in multiples of thousands of dollars.

So$,$ if you calculated that you needed $$3,565,$ then

you should borrow a full $$4,000.$ And, when you

repay the loan, pay the loan off in full thousand

dollar increments.

Required:

In Excel, use the data provided in this question to

complete the following statements and schedules

for the first quarter. Make sure you have $4$

columns of numbers $-$ January, February,

March and then a total for the Quarter. Also

ensure you have the proper title with the

appropriate $3$ lines at the top of the following;

Schedule of expected cash collections.

Inventory purchases budget

Schedule of cash disbursements for purchases

Schedule of cash disbursements for operating

expenses Required:

In Excel, use the data provided in this question to

complete the following statements and schedules

for the first quarter. Make sure you have $4$

columns of numbers $-$ January, February,

March and then a total for the Quarter. Also

ensure you have the proper title with the

appropriate $3$ lines at the top of the following;

Schedule of expected cash collections.

Inventory purchases budget

Schedule of cash disbursements for purchases

Schedule of cash disbursements for operating

expenses

Cash budget

Income Statement $($this can be in total and

individual month information is not required$).$

Balance Sheet $($this will be the March ${31}^{st}$ balance

so only one column of numbers required.

budget on a quarterly basis. The following data has

been assembled to assist in preparation of 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:

Debit Credit

Cash $48,000$

Accounts Receivable $224,000$

Inventory $60,000$

Building & Equipment $370,000$

$($net of Accumulated Depr$)$

Accounts Payable $93,000$

Capital shares $,500,000$

Retained Earnings $,{109}_{,}{000}_{?}$

Totals $,$$$702,000,$$$702,000$

b$.$ Actual sales for December and budgeted sales for

the next four months are as follows;

c$.$ Sales are $20\%$ for cash and $80\%$ on credit. All

payments on credit sales are collected in the month

following sale. The accounts receivable at

December ${31}^{st}$ are a result of December credit

sales.

d$.$ The company's gross margin is $40\%$ of sales.

e$.$ Monthly expenses are budgeted as follows:

salaries and wages $$27,000$ per month

advertising $$70,000$ per month

shipping costs are $5\%$ of sales

depreciation is $$14,000$ per month

other expenses are $3\%$ of sales

f$.$ At the end of each month, inventory is to be on

hand equal to $25\%$ of the following month's sales

needs, stated at cost$.$