. It is the end of May and Aztec Company has collected the following information to prepare its purchases budget and its cash budget for the upcoming months. 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 product's 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 month's 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. I . Continued... This type of revolving credit loan carries an annual 12% interest rate. On May 31, the loan balance is $25,000, and the company's cash balance is $100,000. . Required: In an Excel or Google Sheets spreadsheet, do the following: 1. 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. 2. Prepare a schedule that shows the computation of budgeted ending inventories (in units) for April, May, June, and July. 3. 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. 4. Prepare a schedule showing the computation of cash payments for product purchases for June and July. 5. 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. I Upload your Excel spreadsheet or link to your Google Sheets file to the appropriate D2L Assignments dropbox by the due date
