*** Please answer part II in full with excel formulas/explanation of each value. Part I is completed and listed below for your reference. *** PART I Premier Ties is a division of Menswear, International. The division has an exclusive franchise to act as the nationwide distributor of a designers silk ties. Sales of this product have grown so rapidly over the past few years that the division vice president has decided to implement quarterly budgeting and planning procedures, starting April 1the beginning of the second quarter in the companys fiscal year. The following data has been assembled for the division: Recent and forecasted sales in units are as follows: January (actual) 20,000 February (actual) 24,000 March (actual) 28,000 April 35,000 May 45,000 June 60,000 July 40,000 August 36,000 September 32,000 The increase in sales before and during June reflects the increased activity surrounding Fathers Day. Premier prices this product at 70% over cost. All sales are on credit, net 15 days. Historically, 25% of sales have been collected in the month of sale, 50% in the following month, and the remaining 25% in the second month following the sale. Uncollectible accounts have been insignificant. Accounts receivable at the beginning of the second quarter is $229,500. The division tries to maintain an inventory equal to 90% of the next months sales in units. The ties cost the company $5 each. Premier pays for 50% of its purchases in the month of purchase and the remaining 50% in the month after purchase. Accounts payable for purchases at the beginning of the second quarter is $85,750. Premier pays all other cash operating costs in the month in which they are incurred. The division pays quarterly salaries of $66,000 plus a $0.50 commission per unit sold. Other quarterly operating expenses consist of: utilities, $42,000; insurance, $7,200; depreciation, $45,000; miscellaneous, $9,000. PART I 1. Prepare a sales budget in units and dollars by month and for the quarter 2. Prepare a schedule of cash collections from sales by month and for the quarter 3. Prepare a purchases budget in units and dollars by month and for the quarter 4. Prepare a schedule of cash payments for purchases by month and for the quarter 5. Prepare a selling and administrative expense budget in dollars by month and for the quarter. List each expense separatelyyou may assume that quarterly fixed expenses are incurred uniformly during the quarter. ADDITIONAL INFO:

ADDITIONAL INFORMATION Sterling plans to purchase land for $25,000 cash in June. The company also declares dividends of $12,000 per quarter. The dividend is declared in the last month of the quarter and paid in the first month of the following quarter. At the end of each quarter, Sterling makes a tax payment equal to 20% of budgeted income before tax. For purposes of this project, you may assume that the payment is debited to income tax expense. At the beginning of the year, Sterling borrowed $150,000 on a five-year 12% note payable with interest to be paid annually at December 31. The division's balance sheet at March 31 is as follows: STERLING TIES BALANCE SHEET At March 31 ASSETS Cash Accounts receivable Inventory Prepaid insurance Property, plant and equipment, net $ 44,000 229,500 157,500 14,400 322.700 Total Assets $768,100 LIABILITIES AND STOCKHOLDERS' EQUITY LIABILITIES AND STOCKHOLDERS' EQUITY Accounts payable Dividends payable Interest payable Note payable Common stock, no par Retained earnings $ 85,750 12,000 4,500 150,000 300,000 215.850 Total Liabilities and Stockholders' Equity $768,100 REQUIREMENTS PART 2 PART 4 1) The balance sheet information provided in the project data has been entered on a spreadsheet in your workbook. Prepare a comparative budgeted balance sheet at June 30 next to the first quarter balance sheet. If you cannot get your second quarter balance sheet to balance, enter the amount necessary to get it to balance as a separate line item and label this amount "plug. ADDITIONAL INFORMATION Sterling plans to purchase land for $25,000 cash in June. The company also declares dividends of $12,000 per quarter. The dividend is declared in the last month of the quarter and paid in the first month of the following quarter. At the end of each quarter, Sterling makes a tax payment equal to 20% of budgeted income before tax. For purposes of this project, you may assume that the payment is debited to income tax expense. At the beginning of the year, Sterling borrowed $150,000 on a five-year 12% note payable with interest to be paid annually at December 31. The division's balance sheet at March 31 is as follows: STERLING TIES BALANCE SHEET At March 31 ASSETS Cash Accounts receivable Inventory Prepaid insurance Property, plant and equipment, net $ 44,000 229,500 157,500 14,400 322.700 Total Assets $768,100 LIABILITIES AND STOCKHOLDERS' EQUITY LIABILITIES AND STOCKHOLDERS' EQUITY Accounts payable Dividends payable Interest payable Note payable Common stock, no par Retained earnings $ 85,750 12,000 4,500 150,000 300,000 215.850 Total Liabilities and Stockholders' Equity $768,100 REQUIREMENTS PART 2 PART 4 1) The balance sheet information provided in the project data has been entered on a spreadsheet in your workbook. Prepare a comparative budgeted balance sheet at June 30 next to the first quarter balance sheet. If you cannot get your second quarter balance sheet to balance, enter the amount necessary to get it to balance as a separate line item and label this amount "plug