ACC 208 Summer II 2016, Master Budget Assignment, Points=25General Information: This assignment requires you to integrate your knowledge of Financial Accounting (ACC 207), budgeting (from ACC 208) and Excel. It will take you several hours to complete. Do not start this assignment at the last minute. Only one grade per team will be awarded. No late submissions will be accepted.Required format:1. Electronic submissiona. You are expected to use formulas (formulae)/links/functions in completing this assignment. Fifteen points will be deducted if you simply enter the numbers in the correct space, even if your solution is correct!b. Format:i. Each budget must appear on a different sheet (tab). The title of each budget should appear as the name of the sheet (tab) on which it appears.ii. You must use appropriate headers for each schedule.iii. Use 11 point, Time-New Roman fontPrepare and present all reports in a neat and professional manner. Editing is a very important part of your workand requires a significant amount of time. Plan ahead so you have enough time to complete the assignment.Budget Assignment DataCoaches Company distributes womens purses in the Midwest. The following information was gathered to prepare the budget for the second quarter. Purses are budgeted to sell for an average price of $250. Unit sales are expected to be as follows:March 3,900 April 4,200 May 4,700 June 4,300 July 4,100 Sales are made for cash and on credit. The following collection pattern is used to estimate monthly cash collections:Cash sales 40%Credit sales month of sale 32%Credit sales month after sale 24%Uncollectible 4%Total 100% The company maintains an inventory of 20 percent of the following months sales. The company expects to have 840purses on hand on March 31. Coaches pays an average of $200per purse. The company pays for 75 percent of its purchases in the month of purchase and the remaining 25 percent in the month after purchase. The following monthly selling and administrative expenses are planned for the quarter, though salaries will have a one-time $10,000 increase in May and advertising will decrease by $10,000 in June.Depreciation $ 8,000 Rent $ 38,000 Advertising $ 30,000 Salaries $ 80,000 On June 30, the company plans to purchase $20,000 of new office equipment. However, no additional depreciation will be recorded in the second quarter. Coaches wants to maintain a minimum cash balance of $30,000. An open line of credit at a local bank allows the company to borrow up to $100,000 per quarter in $1,000 increments. All borrowing is done at the beginning of the month, and all repayments are made at the end of a month in $1,000 increments. Accrued interest is paid only when principal is repaid. The interest rate is 12 percent per year. Accrued expenses from the first quarter will be paid in April. Coaches income tax rate is 20 percent and is accrued each month. The March 31 balance sheet is budgeted as follows: March 31stCash $ 30,000 Accounts Receibable 234,000 Inventory 168,000 Plant & Equipment 450,000 Accumulated Depreciation (180,000)Total Assets $ 702,000 Accounts Payable $ 198,000 Accrued Expenses 54,000 Common Stock 325,000 Retained Earnings 125,000 Total Liabilities and Owners Equity $ 702,000 Requireda) Prepare all components (sales budget, cash collections budget, purchases budget, cash disbursements for purchases budget, selling and administrative budget, and cash budget) of Coaches master budget for the second quarter (1 April – 30 June); include calculations for each of the three months and for the quarter.b) Prepare a pro-forma income statement for the second quarter.c) Prepare a pro-forma balance sheet as of June 30.