*** 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 I Property, plant and equipment, net $ 44,000 229,500 157,500 14,400 322,700 Total Assets $768.100 LIABILITIES AND STOCKHOLDERS' EQUITY Accounts payable Dividends payable Interest payable Note payable Common stock, no par Retained earnings Total Liabilities and Stockholders' Equity $ 85,750 12,000 4,500 150,000 300,000 215,850 $768.100 REQUIREMENTS PART 2 1) Prepare a budgeted income statement for the quarter only using the contribution format. List each cost separately. 2) At the bottom of your worksheet, answer the questions below. Use operating income (income before interest and taxes) in your calculations. Round your answers to the nearest whole unit, dollar or one percent as necessary. Be sure to label your answers. a. What is the division contribution margin ratio? b. What is the quarterly budgeted break-even point in units? c. What is the margin of safety in units for the quarter? d. What is operating leverage for the quarter? e. If the division wants to earn income before interest and taxes of $350,000 for the quarter, how many units must it sell