The BCJ Company needs a master budget for the three months beginning April 1, 2020. The company retails widgets. The 2020 budget should be based on the following information. An ending minimum cash balance of $10,000 each month is required. Sales are forecasted at an average selling price of $8 per widget. Merchandise costs are $4 per widget. Currently, the company maintains an ending inventory balance equal to 20% of the next months projected cost of goods sold. Purchases during any given month are paid half in the month of purchase and half during the following month. Sales are 20% cash and 80% on credit (payable within 30 days), but experience has shown that 60% of monthly credit sales is collected in the current month, 40% in the next month.

Monthly operating expenses are as follows:

Wages and salaries $15,000

Insurance expired 150

Depreciation 1,200

Utilities 1,000

Advertising 300

Miscellaneous 500

Rent 400 per month+ 10% of monthly sales.

All operating expenses are paid as incurred, except insurance, depreciation, and rent. Rent of $400 is paid at the beginning of each month, and the additional 10% of sales is paid in the month following the sales. The company plans to buy some new equipment for $5,000 cash in June. Cash dividends of $1,500 are to be paid quarterly, beginning April 15. Dividends are declared on the 15th of the last month in the calendar quarter.

BCJ has an established line of credit with its bank, Third Fifth National. Money can be borrowed and repaid in multiples of $1,000, at an interest rate of 6% per annum. Management wants to minimize borrowing and repay rapidly. Interest is computed and paid when the principal is repaid. Assume that borrowing occurs at the beginning and repayments at the end of the months in question. Money is never borrowed at the beginning and repaid at the end of the same month. Compute interest to the nearest dollar.

Balance Sheet

March 31, 2020

Assets Liabilities

Cash $16,300 Accounts payable (inventory) $13,750

Accounts receivable (net) 19,200 Dividends payable 1,500

Inventory 4,000 Rent payable 6,000

Prepaid insurance 1,800 21,250

Land, Building, Equipment (net) 75,000

Stockholders' Equity

Capital stock ($1 par value) 54,400

Retained earnings 40,650

Total Liabilities & Stockholders' Equity $116,300

Total assets $116,300

Recent and forecasted sales:

January $45,000 February $50,000 March $60,000 April $40,000

May $50,000 June $70,000 July $60,000

Required: 1. Prepare a master budget, using Excel, and all supporting schedules (including sales) for the months April, 2020 through June, 2020.

2. Prepare the budgeted Income Statement and Statement of Cash Flows for the quarter ended June 30, 2020 and the budgeted Balance Sheet at June 30, 2020.

master budget B[1767] - Compatibility Mo... - Saved - Search Darren Johnson D X AutoSave on 20 = File Home Insert Design Layout Share Comments References Mailings Review View Help The BC Company needs a master budget for the three months beginning April 1, 2020. The company retails widgets. The 2020 budget should be based on the following information. An ending minimum cash balance of $10.000 each month is required. Sales are forecasted at an average selling price of $8 per widget. Merchandise costs are S4 per widget. Currently, the company maintains an ending inventory balance equal to 20% of the next month's projected cost of goods sold. Purchases during any given month are paid half in the month of purchase and half during the following month. Sales are 20% cash and 80% on credit (payable within 30 days), but experience has shown that 60% of monthly credit sales is collected in the current month, 40% in the next month. Monthly operating expenses are as follows: Wages and salaries Insurance expired Depreciation Utilities Advertising Miscellaneous Rent $15,000 150 1,200 1,000 300 500 400 per month+ 10% of monthly sales. All operating expenses are paid as incurred, except insurance, depreciation, and rent. Rent of $400 is paid at the beginning of each month, and the additional 10% of sales is paid in the month following the sales. The company plans to buy some new equipment for $5,000 cash in June. Cash dividends of $1,500 are to be paid quarterly, beginning April 15. Dividends are declared on the 15th of the last month in the calendar quarter. BCJ has an established line of credit with its bank, Third Fifth National. Money can be borrowed and repaid in multiples of $1,000, at an interest rate of 6% per annum. Management wants to minimize borrowing and repay rapidly. Interest is computed and paid when the principal is repaid. Assume that borrowing occurs at the beginning and repayments at the end of the months in question. Money is never borrowed at the beginning and repaid at the end of the same month. Compute interest to the nearest dollar Balance Sheet March 31, 2020 Assets Cash Accounts receivable (net) Inventory Prepaid insurance Land, Building, Equipment (act) $16,300 19.200 4.000 1,800 75,000 Liabilities Accounts payable (inventory) Dividends payable Rent payable S13,750 1.500 6,000 21.250 Stockholders' Equity Page 1 of 1 49 of 477 words 18 Focus Ee 100% Type here to search O (?A 6:36 PM 10/20/2020 O AutoSave on IS2 on FSU- master budget B[1767] - Compatibility Mo... - Saved - Search Darren Johnson D. X File Home Insert Design Layout Review View Share Comments References Mailings Utilities Advertising Miscellaneous Rent Help 1,000 300 500 400 per month+ 10% of monthly sales. All operating expenses are paid as incurred, except insurance, depreciation, and rent. Rent of $400 is paid at the beginning of cach month, and the additional 10% of sales is paid in the month following the sales. The company plans to buy some new equipment for $5,000 cash in June. Cash dividends of S1,500 are to be paid quarterly, beginning April 15. Dividends are declared on the 15th of the last month in the calendar quarter, BCJ has an established line of credit with its bank, Third Fifth National Money can be borrowed and repaid in multiples of $1.000, at an interest rate of 6% per annum. Management wants to minimize borrowing and repay rapidly. Interest is computed and paid when the principal is repaid. Assume that borrowing occurs at the beginning and repayments at the end of the months in question. Money is never borrowed at the beginning and repaid at the end of the same month. Compute interest to the nearest dollar. Balance Sheel March 31, 2020 Assets Cash Accounts receivable (net) Inventory Prepaid insurance Land, Building, Equipment (net) $16,300 19,200 4.000 1.800 75,000 Liabilities Accounts payable inventory) Dividends payable Rent payable $13.750 1,500 6,000 21,250 Stockholders' Equity Capital stock ($1 par value) Retained earnings Total Liabilities & Stockholders' Equity 54,400 40.650 S16,300 Total assets $16.300 Recent and forecasted sales: January $45.000 February $50.000 May 550.000 June $70,000 April $40,000 March July $60,000 S60,000 Required: 1. Prepare a master budget, using Excel, and all supporting schedules (including sales) for the months April, 2020 through June, 2020. 2. Prepare the budgeted Income Statement and Statement of Cash Flows for the quarter ended June 30, 2020 and , the budgeted Balance Sheet at June 30, 2020. Page 1 of 1 477 words Focus Ee 100% Type here to search O (?A 637 PM 10/20/2020 O