Part 1 (Cash Budget from Chapter 22)

Aztec Company has collected the following information to prepare its purchases budget and its cash budget.

Sales and Accounts Receivable Collections: Aztec sells its product for $180 per unit. All sales are on credit (Accounts Receivable). Its actual and budgeted sales follow: Month Units Dollars April (actual) 4,000 $720,000 May (actual) 2,000 $360,000 June (budgeted) 6,000 $1,080,000 July (budgeted) 5,000 $900,000 August (budgeted) 3,800 $684,000 Recent experience shows that credit sales are collected as follows: 20% collected in the month of the sale 50% collected in the month after the sale 28% collected in the second month after the sale 2% proves to be uncollectible

Purchases and Accounts Payable Payments: The products purchase price is $110 per unit. All purchases are made on account (Accounts Payable). Purchases made on account are paid as follows: 60% of purchases paid in the month of the purchase 40% of purchases paid in the month after the purchase

Budgeted Ending Inventory: The company has a policy of maintaining an ending monthly inventory of 20% of the next months unit sales plus a safety stock of 100 units. The April 30 and May 31 actual inventory levels are consistent with this policy.

Selling and Administrative Expenses: Selling and administrative expenses for the year are $1,320,000 and are paid evenly throughout the year in cash.

Cash Balance and Loan: The company has a policy of maintaining a minimum cash balance of $100,000. This minimum cash balance is maintained, if necessary, by borrowing cash from the bank at the end of each month. If the cash balance at the end of the month exceeds $100,000, the company repays as much of the loan as it can without going below the minimum. Continued

This type of revolving credit loan carries an annual 12% interest rate. On May 31, the loan balance is $25,000, and the companys cash balance is $100,000.

Required:

In an Excel spreadsheet, do the following: Prepare a schedule that shows the computation of cash collections of its credit sales (Accounts Receivable) in each of the months of June and July. Prepare a schedule that shows the computation of budgeted ending inventories (in units) for April, May, June, and July. Prepare the merchandise purchases budget for May, June, and July. Report calculations in units and also show the dollar amount of purchases for each month. Prepare a schedule showing the computation of cash payments for product purchases for June and July. Prepare a cash budget for June and July, including any loan activity and interest expense. Compute the loan balance at the end of each month.

Part 2 (Flexible Budget from Chapter 23)

Phoenix Companys 2017 master budget included the following fixed budget report. It is based on an expected production and sales volume of 15,000 units.

Phoenix Company Fixed Budget Report For Year Ended December 31, 2017 Sales

$3,000,000 Cost of Goods Sold:

Direct materials $975,000

Direct labor 225,000

Machinery repairs (variable cost) 60,000

Depreciation-Plant equipment (straight-line) 300,000

Utilities ($45,000 is variable) 195,000

Plant management salaries 200,000 1,955,000 Gross Profit

1,045,000 Selling Expenses:

Packaging 75,000

Shipping 105,000

Sales salary (fixed annual amount) 250,000 430,000 General and Administrative Expenses:

Advertising Expense 125,000

Salaries 241,000

Entertainment Expense 90,000 456,000 Income from Operations

$159,000

Required: In Excel, prepare flexible budgets for the company at sales volumes of 14,000 and 16,000 units.