Check figures:

Cash disbursements at the end of the quarter = $966,100

Cash surplus at the end of the quarter = $230,900

(Note that due to varying assumptions made in the budgeting process, your numbers may be slightly different than mine. If you prepare a balance sheet that balances, then your budget works.)

Please show all formulas!! On a time crunch, due Nov.8 2020 Thank. you!!!

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: Sales (units) Month Sept 2020 Oct 2020 Nov 2020 Dec 2020 Jan 2021 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 $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 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: $.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 Assets Liabilities and Stockholders' Equity Cash $38,000 Accounts payable $98,000 Accounts receivable (to be received in January) Inventory (23,000 units x $3/face shield) 120,000 Dividends payable 30,000 69,000 Capital stock 234,650 Prepaid insurance 19,000 Retained earnings 88,850 Fixed assets, net of depreciation 205,500 Total assets Total liabilities and $451,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. $.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 Assets Liabilities and Stockholders' Equity Cash $38,000 Accounts payable $98,000 Accounts receivable (to be received in January) Inventory (23,000 units x $3/face shield) 120,000 Dividends payable 30,000 69,000 Capital stock 234,650 Prepaid insurance 19,000 Retained earnings 88,850 Fixed assets, net of depreciation 205,500 Total assets Total liabilities and $451,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) Please highlight your answers. NOTE: You will need to borrow and repay during the quarter. If needed, please clearly list out any assumptions you make. Formatting is as important as the correct answer for this project. Your spreadsheet should be in good form, presented clearly, and labeled appropriately. In your spreadsheet, you MUST use formulas to prepare the budget. Projects submitted without formulas will receive NO credit. Note that due to varying assumptions, every spreadsheet will look different. An excel spreadsheet is a work of art that should tell a story. Make it your own. 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: Sales (units) Month Sept 2020 Oct 2020 Nov 2020 Dec 2020 Jan 2021 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 $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 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: $.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 Assets Liabilities and Stockholders' Equity Cash $38,000 Accounts payable $98,000 Accounts receivable (to be received in January) Inventory (23,000 units x $3/face shield) 120,000 Dividends payable 30,000 69,000 Capital stock 234,650 Prepaid insurance 19,000 Retained earnings 88,850 Fixed assets, net of depreciation 205,500 Total assets Total liabilities and $451,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. $.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 Assets Liabilities and Stockholders' Equity Cash $38,000 Accounts payable $98,000 Accounts receivable (to be received in January) Inventory (23,000 units x $3/face shield) 120,000 Dividends payable 30,000 69,000 Capital stock 234,650 Prepaid insurance 19,000 Retained earnings 88,850 Fixed assets, net of depreciation 205,500 Total assets Total liabilities and $451,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) Please highlight your answers. NOTE: You will need to borrow and repay during the quarter. If needed, please clearly list out any assumptions you make. Formatting is as important as the correct answer for this project. Your spreadsheet should be in good form, presented clearly, and labeled appropriately. In your spreadsheet, you MUST use formulas to prepare the budget. Projects submitted without formulas will receive NO credit. Note that due to varying assumptions, every spreadsheet will look different. An excel spreadsheet is a work of art that should tell a story. Make it your own