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

a. Actual sales for June and budgeted sales for the next four months are as follows:

June $200,000

July 175,000

August 160,000

September 150,000

October 140,000

b. Sales are 30 percent for cash and the rest on account. All sales on account are collected the month following sale. The accounts receivable on June 30 are a result of June credit sales.

c. Forty 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. During July, the company will purchase a new computer for $3,000 in cash. During August, other equipment will be purchased for cash at a cost of $22,000. Assume there will be no equipment purchases in September 2019.

e. During July and September, the company will declare and pay $12,000 in cash dividends each month. Assume no dividends will be paid in August of 2019.

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

Debits:

Cash $20,000

Accounts Receivable 45,000

Inventory 55,000

Plant and Equip (net) 120,000

_______

$240,000

Credits:

Accounts Payable $40,000

Short-term Notes Payable 15,000

Capital Stock 150,000

Retained earnings 35,000

_______

$240,000

g. The company's gross profit rate is 45 percent of sales.

h. Monthly expenses are budgeted as follows: salaries and wages, $15,000 per month; depreciation, $18,000 per month; advertising, 1 percent of sales; rent, $12,000 per month; utilities, 3 percent of sales; and miscellaneous, $16,000 per month.

i. At the end of each month, inventory is to be on hand equal to 40 percent of the following month's sales needs, stated at cost.

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.)

Required:

Prepare an interactive budgeting spreadsheet.

Create a worksheet for:

f. Cash Budget

g. Budgeted Income Statement

h. Budgeted Balance Sheet

Each budget should 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).