You have just been hired. The company has an exclusive arrangement for the distribution of face shields for medical use and sales have grown so rapidly over the last year that it has become necessary to add new members to the management team. You have been given responsibility for all planning and budgeting. Your first assignment is to prepare a master budget for the next quarter, starting January 1. You are eager to make a favorable impression on the president and have assembled the information below.

Recent and forecasted sales in units are as follows:

Month	Sales (units)
Sept 2020	58,600
Oct 2020	40,000
Nov 2020	39,000
Dec 2020	34,000
Jan 2021	51,000
Feb 2021	52,000
March 2021	45,000
April 2021	35,000

Because of inconsistent sales projections and uncertainty related to the future demand for face shields, ending inventories should be equal to 50% of the next month's sales in units. The face shields cost the company $3 each to manufacture. Face shields are sold for $10 each.

Purchases are paid for as follows: 50% in the month of purchase and the remaining 50% in the following month. All sales are on credit, with no discount, and payable within 15 days. The company has found, however, that only 25% of a month's sales are collected by month-end. An additional 60% is collected in the following month, and the remaining 10% is collected in the second month following the sale. The remaining 5% is treated as a bad debt. The company desires a minimum ending cash balance each month of $25,000. The company has an agreement with a bank that allows the company to borrow in increments of $1,000 at the beginning of each month, up to a total loan balance of $100,000. The interest rate on these loans is 1% per month, and for simplicity, we will assume that interest is NOT compounded. Assume any loan repayments and interest are paid to the bank only if the company can meet the minimum cash balance of at least $25,000.

The company's projected monthly operating expenses are given below:

Variable:

Sales commissions

$.20 per face shield

Fixed:

Wages and salaries

$43,000

Utilities

$21,000

Expired Insurance

$2,200

Depreciation

$2,700

Miscellaneous

$3,000

All operating expenses are paid during the month, in cash, with the exception of noncash expenditures of depreciation and expired insurance. Land will be purchased for cash in January for $40,000. To expand production, factory machinery will be purchased for $200,000 to be paid in February. The company's most recent balance sheet on December 31, 2020 is given below:

Balance Sheet as of December 31, 2020

Assets

Liabilities and Stockholders' Equity

Cash

$38,000

Accounts payable

$98,000

Accounts receivable (to be received in January)

120,000

Dividends payable

30,000

Inventory (23,000 units x $3/face shield)

69,000

Capital stock

234,650

Prepaid insurance

19,000

Retained earnings

88,850

Fixed assets, net of depreciation

205,500

Total assets

$451,500

Total liabilities and stockholders' equity

$451,500

• Accounts receivable at the end of December are all expected to be received in January.
• All accounts payable at the end of December are expected to be paid in January.

• The company declared dividends of $30,000 at the end of the year, payable in January.

Prepare the following budgets for each month and total for the quarter in good form in excel with proper use of formulas and formatting please also include excel formulas used: a. Prepare a Sales Budget and Schedule of Expected Cash Collections

• What is the budgeted accounts receivable at the end of the quarter?

b. Prepare a Merchandise Purchases Budget and Schedule of Expected Cash Disbursements

• What is the budgeted accounts payable at the end of the quarter?

c. Prepare a Cash Budget

• How much will the company need to borrow during the quarter?
• How much will the company repay during the quarter?

d. Prepare a contribution margin Budgeted Income Statement

e. Prepare a Budgeted Balance Sheet (only if the balance sheet balances and nothing is plugged)