organized in excel please

1) Kat Co. is preparing its master budget for the quarter ended June 30 Sales Information The company sells a single product for $30 each. Sales are split between 30% cash and 70% on credit (A/R). All credit sales are collected in the following month. The Accounts Receivable balance on March 31 is $12,000 Budgeted sales are as follows: April May June July Unit sales 800 700 1100 1000 Product Cost and Inventory Information The product cost is $12 per unit. The desired ending inventory is 30% of the next month's sales in units. Inventory in units for March 31: 300 units Purchases are paid 60% in the month of the purchase and 40% the next month. The Accounts Payable Balance on March 31 is $8,000 Operating Expense Information Commission (10% of Sales) Shipping (2% of Sales) Office Salaries-$2000 per month Rent Expense $6500 per month Depreciation Expense - $2000 per month Prepare the following budgets for the months of April, May and June (2nd Qtr Total Budget) (You must use excel to format the budgets) a) Sales Budget b) Table of Cash Receipts c) Merchandise Purchase Budget d) Table of cash disbursement for Purchase of merchandise (Product Cost) e) Table of cash disbursements for selling and administrative expenses THE Nichtreems masne each aning nut 2) Cristian Manufacturing, Inc's static budget for the year is shown below: Sales (20,000 units) $1,400,000 Cost of Goods Sold: Direct Material $140,000 Direct Labor 300,000 Overhead (includes $3 per unit Variable cost) 165,000 605,000 Gross Profit 795,000 Selling Expenses Sales Commissions (Variable) 40,000 Rent (Fixed) 90,000 Insurance (Fixed) 45,000 General and administrative expenses Salaries (fixed) 190,000 Rent (Fixed) 60,000 Depreciation (Fixed) 70,000 495,000 $ 300,000 Prepare a flexible budget for Cristian Manufacturing Inc. that shows a detailed budget for its static budget and its actual sales volume of 30,000 units. Use the contribution margin approach. 20,000 units 30,000 units Sales Variable Cost: Direct Materials Direct Labor Variable OH Sales Commission Total Variable Cost Contribution Margin Fixed Cost: Fixed OH Rent Expense-Selling Fixed Cost: Fixed OH Rent Expense-Selling Insurance Expense Salaries Expense Rent Expense - General Depreciation Expense Total Fixed Cost Income from Operations