Managerial Accounting - Master Budget.

Please Solve This. The second picture is of the template that is necessarry to follow. It should be according to the template

3 (20 pts) Master Budget with Supporting Schedules You have just been hired as a new management trainee by Earrings Unlimited, a distributor of earrings to outlets located in shopping malls across the country. Since you are well trained in budgeting, you have decided to ehensive budgets for the upcoming second quarter in order to show management the benefits that can e compr be gained from an integrated budgeting program. To this end, you have worked with accountiog and other areas to gather the information assembled below The company sells many styles of earrings, but all are sold for the same price-$ 10 per paL Actual sales of earrings for the last three months and budgeted sales for the next six months follow (in pairs of earrings): January (actual) February (actual)50,000 pairs March (actual) April (budget) May (budget) 20,000 pairs June (budget) July (budget) 60,000 pairs 40,000 pairs 60,000 pairs 75,000 pairs 110,000 pairs The concentration of sales before and during May is due to Mother's Day 10% of each month's sales are over-tecurter cash sales, while the remainder are credit sales 20% of credt sales are colected in the month of sale. An addtional 60% iscollected in the following month, and 15%is colected in the second month follow sale. Theren aning 5% is never Sufficient inventory should be on hand at the end of each month to supply 40% of the earings sold inthe lokweg month You are a merchandiser and you pay suppliers S5 for each pair of earrings (le.your cost of Goods Sol,50% of a month's purchases is paid for in the month of purchase the other half is paid for in the following month. Inventory at the end of March was 30,000 units or $150,000. Accounts payable at 3/31 was $165,000 Monthly operating expenses for the company are: Variable: Sales commissions are 5% of total sales each month-paid in the month of sale. Fixed Advertising$100,000 Rent Salaries Utilities Insurance Depreciation 18,000 106,000 7,000 3,000 14,000 The company plans to purchase $103,500 in new equipment during May and $60,000 in new equipment during June, both purchases will be for cash The company will pay dividends of $15.500 during April must maintain a minimum cash balance of $20,000. Cash Balance April 1t is $20,000. All borrowing will be done at the beginning of a month; any repayments will be made at the end of a month. The annual interest rate will be 12% Compute interest on whole months. Required: Using Excel: I Prepare a schedule of cash collections for the second quarter (Apri, May, and June) quarterly totals are not necessary for any of the schedules for this problem.) 2 Prepare a schedule of invenlory parchases in dollars for April, May, and June (oull need to do some of July as weol to get ending inventory for June) Apri, May, and June month 5 Compute the Accounts Receivable and Accounts Payable at 6/30