/

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:

• a. 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.
• j. 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.)

2 / 2

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.