Bravo Company is a bookstore that prepares its master budget on a quarterly basis. The following data has been assembled to assist in preparation of the master budget for the first quarter:

As of December $31,2023($the end of the prior quarter$),$ the company's general ledger showed the following account balances:

Debit Credit

Cash $44,000$

Accounts Receivable $184,000$

Inventory $60,000$

Building & Equipment $404,000$

Accumulated Depreciation $144,000$

Accounts Payable $93,000$

Capital shares $200,000$

Retained Earnings $255,000$

Totals $ $692,000$ $ $692,000$

Actual sales for December and budgeted sales for the next four months are as follows;

December $($actual$)$ $ $280,000$

January $380,000$

February $580,000$

March $275,000$

April $220,000$

Sales are $20\%$ for cash and $80\%$ on credit. All payments on credit sales are collected $50\%$ in the month of sale and $50\%$ in the month following the sale. The accounts receivable at December $31$st are a result of December credit sales.

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

Monthly expenses are budgeted as follows:

$-$ salaries and wages $ $25,000$ per month

$-$ advertising $ $75,000$ per month

$-$ shipping costs are $6\%$ of sales

$-$ depreciation is $ $12,000$ per month

$-$ other expenses are $4\%$ of sales

f$.$ At the end of each month, inventory is to be on hand equal to $30\%$ of the following month's sales needs, stated at cost$.$

g$.25\%$ of a month's inventory purchases is paid for in the month of purchase; the remaining $75\%$ is paid for in the following month.

h$.$ During February, the company will purchase a new copy machine for $ $1,500$ cash. During March, other equipment will be purchased for cash at a cost of $ $90,000.$

i$.$ During January, the company will declare and pay $ $120,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, $1/12,2/12,$ etc.$)$

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;

Sales budget

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.