Somerset merchandising company is preparing monthly budgets for the first quarter of 20XX. The data used to prepare the budgets are as follows 1. Somerset has the following projected unit sales (at $18 per unit) for seven months of operations after December: December 28,000 January, 20XX30,000 February28,000 March25,000 April30,000 2. To have adequate inventory on hand, Somerset has a policy to maintain the ending Inventory at 25 percent of the following month's projected cost of goods sold. 3. The firm expects to collect 30 percent of the sales in the month of sale. 70 percent of the sales are expected to be collected in the first month following sale. 4. Somerset pays for 40 percent of its purchases to suppliers in the month of purchase and 60 percent in the following month. Purchase cost of each unit is $3. 5. Monthly selling and administrative (S&A) expenses are estimated as follows: Sales salary $30,000 Administrative salary 15,000 Advertising and promotion 8,000 Depreciation of S&A assets 12,000 Rent 6,000 Property taxes 2,000 Other operating expenses 11,000 Rent cost is prepaid prior to the beginning of a 12-month rental period. The rent cost for 20XX is prepaid in July of last year. Property taxes are paid semiannually on March 31 and September 30 for the preceding six-month period. For example, the property taxes for the six-month period from April to September will be paid on September 30. The remaining selling and administrative expenses are expected to be paid at the end of each month. 6. Somerset expects to have cash balance of $85,000 as of December 31 Required: Prepare the following budgets for the first quarter of 20XX: a. Sales budget b. Purchase budgets c. Selling and administrative budget d. Cash receipts budget e. Cash disbursements budget f. Cash budget g. Budgeted income statement for the first quarter Spreadsheet: Use the provided template (Budgeting project_template.xls) to prepare the spreadsheet. modify the template when necessary