Allen Corporation prepares its master budget on a quarterly basis. The following data have been assembled to assist in preparation of the master budget for the fourth quarter of 2019:

1. As of September 30, 2019 (the end of the prior quarter), the company's general ledger showed the following account balances:

Debits Credits Cash $42.000 Accounts Receivable 98,000 Inventory 55,000 Plant and Equip (net) | 205,000 Accounts Payable $60,000 Short-term Notes Payable 10,000 Capital Stock 260,000 Retained earnings 70,000 $400.000 $400.000 b. At the end of each month, inventory is to be on hand equal to 55 percent of the following month's sales needs, stated at cost. C. Sixty percent of a month's inventory purchases are paid for in the month of purchase, the rest is paid for in the following month. d. Sales are 25 percent for cash and the rest on account. All sales on account are collected the month following sale. The accounts receivable on September 30 are a result of September credit sales. e. The company's gross profit rate is 42 percent of sales. f. Actual sales for September and budgeted sales for the next four months are as follows: September 2019 $180,000 October 2019 210,000 November 2019 270,000 December 2019 180,000 January 2020 140,000 g. Monthly expenses are budgeted as follows: salaries and wages, $25,000 per month; rent, $20,000 per month; advertising 2 percent of sales: depreciation, $15,000 per month; miscellaneous, 4 percent of sales; and utilities, $12,000 per month. h. During October, the company will purchase a new computer for $10,000 in cash. During December, other equipment will be purchased for cash at a cost of $30,000. Assume there will be no equipment purchases in November 2019. i. During November and December, the company will declare and pay $25,000 in cash dividends each month. Assume no dividends will be paid in October of 2019. . The company must maintain a minimum cash balance of $10,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. Borrowings and repayments of principal must be in multiples of $1,000. Interest is paid at the end of each month. The interest rate is 12 percent per annum. (Figure interest in whole months, e.g., 1/12, 2/12.) Required: Prepare an interactive budgeting spreadsheet. It should automatically update when changes are made to the input data, such as changes in sales forecasts, equipment purchases, etc. Spreadsheets Hints 1. Create a worksheet for inputs that includes all potential variables that can be changed. Label the worksheet tab as "inputs." 2. Create a worksheet for each of the different budgets. Label the tabs appropriately. The following budgets should be included: a. Sales Budget b. Inventory Purchases Budget c. Selling and Administrative Budget d. Cash Collections from Customers Schedule Cash Paid for Inventory Purchases Schedule e. f. Cash Budget g. Budgeted Income Statement h. Budgeted Balance Sheet a. be on a separate worksheet b. have a heading centered over the rest of the budget that includes the following: i. Name of Company ii. Name of Budget iii. Date: December 31, 2019" or "For the Quarter ended December 31, 2019" c. be prepared on the monthly basis with a total column for the quarter. The budgeted income statement and budgeted balance sheet should be quarterly (not monthly). 4. All worksheets should be interactive (i.e. all worksheet pages except the Input worksheet, should be formula-driven). Hints for Creating Interactive Cash Budget 1. To calculate borrowings to the nearest thousands, you may use the ROUNDUP function. To calculate repayments, you may use the ROUNDDOWN function. 2. 3. Each budget should: Debits Credits Cash $42.000 Accounts Receivable 98,000 Inventory 55,000 Plant and Equip (net) | 205,000 Accounts Payable $60,000 Short-term Notes Payable 10,000 Capital Stock 260,000 Retained earnings 70,000 $400.000 $400.000 b. At the end of each month, inventory is to be on hand equal to 55 percent of the following month's sales needs, stated at cost. C. Sixty percent of a month's inventory purchases are paid for in the month of purchase, the rest is paid for in the following month. d. Sales are 25 percent for cash and the rest on account. All sales on account are collected the month following sale. The accounts receivable on September 30 are a result of September credit sales. e. The company's gross profit rate is 42 percent of sales. f. Actual sales for September and budgeted sales for the next four months are as follows: September 2019 $180,000 October 2019 210,000 November 2019 270,000 December 2019 180,000 January 2020 140,000 g. Monthly expenses are budgeted as follows: salaries and wages, $25,000 per month; rent, $20,000 per month; advertising 2 percent of sales: depreciation, $15,000 per month; miscellaneous, 4 percent of sales; and utilities, $12,000 per month. h. During October, the company will purchase a new computer for $10,000 in cash. During December, other equipment will be purchased for cash at a cost of $30,000. Assume there will be no equipment purchases in November 2019. i. During November and December, the company will declare and pay $25,000 in cash dividends each month. Assume no dividends will be paid in October of 2019. . The company must maintain a minimum cash balance of $10,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. Borrowings and repayments of principal must be in multiples of $1,000. Interest is paid at the end of each month. The interest rate is 12 percent per annum. (Figure interest in whole months, e.g., 1/12, 2/12.) Required: Prepare an interactive budgeting spreadsheet. It should automatically update when changes are made to the input data, such as changes in sales forecasts, equipment purchases, etc. Spreadsheets Hints 1. Create a worksheet for inputs that includes all potential variables that can be changed. Label the worksheet tab as "inputs." 2. Create a worksheet for each of the different budgets. Label the tabs appropriately. The following budgets should be included: a. Sales Budget b. Inventory Purchases Budget c. Selling and Administrative Budget d. Cash Collections from Customers Schedule Cash Paid for Inventory Purchases Schedule e. f. Cash Budget g. Budgeted Income Statement h. Budgeted Balance Sheet a. be on a separate worksheet b. have a heading centered over the rest of the budget that includes the following: i. Name of Company ii. Name of Budget iii. Date: December 31, 2019" or "For the Quarter ended December 31, 2019" c. be prepared on the monthly basis with a total column for the quarter. The budgeted income statement and budgeted balance sheet should be quarterly (not monthly). 4. All worksheets should be interactive (i.e. all worksheet pages except the Input worksheet, should be formula-driven). Hints for Creating Interactive Cash Budget 1. To calculate borrowings to the nearest thousands, you may use the ROUNDUP function. To calculate repayments, you may use the ROUNDDOWN function. 2. 3. Each budget should