Prepare the following budgets for each month and total for the quarter in good form in excel with proper use of formulas and formatting: a. Prepare a Sales Budget and Schedule of Expected Cash Collections (10 points) What is the budgeted accounts receivable at the end of the quarter? b. Prepare a Merchandise Purchases Budget and Schedule of Expected Cash Disbursements (10 points) What is the budgeted accounts payable at the end of the quarter? c. Prepare a Cash Budget (20 points) 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 (10 points) e. Optional prepare a Budgeted Balance Sheet (10 extra points - only if the balance sheet balances and nothing is plugged) You have just been hired as a management trainee by the Pro-PPE Company, a nationwide distributor of personal protective equipment. 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 Sept 2020 Oct 2020 Nov 2020 Dec 2020 Jan 2021 Sales (units) 58,600 40,000 39,000 34,000 51,000 52,000 45,000 35,000 Feb 2021 March 2021 April 2021 Because of inconsistent sales projections and uncertainty related to future demand of face shields, ending inventories should be equal to 50% of the next month's sales in units. The face shields cost the company S3 cach 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 sale. The remaining 5% is treated as a bad debt. The company desires a minimum ending cash balance cach 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: $.20 per face shield Variable: Sales commissions Fixed: Wages and salaries Utilities Expired Insurance Depreciation Miscellaneous $43,000 $21,000 $2,200 $2,700 $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 Liabilities and Assets Stockholders' Equity Cash $38,000 Accounts payable $98,000 120,000 Dividends payable 69,000 Capital stock Accounts receivable (to be received in January) Inventory (23,000 units x $3/face shield) Prepaid insurance Fixed assets, net of depreciation 30,000 234,650 88,850 19,000 Retained earnings 205,500 Total assets Total liabilities and S451.500 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: a. Prepare a Sales Budget and Schedule of Expected Cash Collections (10 points) What is the budgeted accounts receivable at the end of the quarter? b. Prepare a Merchandise Purchases Budget and Schedule of Expected Cash Disbursements (10 points) What is the budgeted accounts payable at the end of the quarter? c. Prepare a Cash Budget (20 points) 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 (10 points) e. Optional prepare a Budgeted Balance Sheet (10 extra points - only if the balance sheet balances and nothing is plugged) You have just been hired as a management trainee by the Pro-PPE Company, a nationwide distributor of personal protective equipment. 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 Sept 2020 Oct 2020 Nov 2020 Dec 2020 Jan 2021 Sales (units) 58,600 40,000 39,000 34,000 51,000 52,000 45,000 35,000 Feb 2021 March 2021 April 2021 Because of inconsistent sales projections and uncertainty related to future demand of face shields, ending inventories should be equal to 50% of the next month's sales in units. The face shields cost the company S3 cach 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 sale. The remaining 5% is treated as a bad debt. The company desires a minimum ending cash balance cach 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: $.20 per face shield Variable: Sales commissions Fixed: Wages and salaries Utilities Expired Insurance Depreciation Miscellaneous $43,000 $21,000 $2,200 $2,700 $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 Liabilities and Assets Stockholders' Equity Cash $38,000 Accounts payable $98,000 120,000 Dividends payable 69,000 Capital stock Accounts receivable (to be received in January) Inventory (23,000 units x $3/face shield) Prepaid insurance Fixed assets, net of depreciation 30,000 234,650 88,850 19,000 Retained earnings 205,500 Total assets Total liabilities and S451.500 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