Based on this information, construct the expected cash collection for the quarter April, May, June

UNIVERSITY OF NEW BRUNSWICK Faculty of Management ADM 2223 - Managerial Accounting Master Budget Assignment Due Thursday April 9 at 6:00 PM Bart and Lisa operate a manufacturing operation in the New Brunswick. They make a single product. In order to help ensure a successful year they have engaged you to prepare a complete master budget for the upcoming quarter. Lisa is a qualified engineer but not overly sophisticated in financial matters. Bart handles the design and marketing of their company's products. You have gathered the following information as of March 31, 2020: B&L Manufacturing Balance Sheet March 31. 2020 Cash Accounts Receivable Inventory Net Capital Assets Total Assets $ 60,000 624.000 302.587 1.180.000 2.166,587 Accounts Payable 72.000 Common Stock 200.000 Shareholder's Equity (SE) 1.894.587 Total Liabilities and SE 2.166.587 Inventory is made up of the following balances: Raw Materials 28.800 6,400 units WIP S100.000 Finished Goods S173.787 4.800 units Recent and Projected Sales February March April May June July August S360.000 480.000 480,000 480.000 360.000 420.000 600.000 600,000 Units 6.000 8.000 8.000 8.000 6,000 7,000 10.000 10.000 September Credit accounts are collected 60% in the month following the sale and 10% in the next month following. There are no bad debts Collections are current: the March AR balance made up of uncollected amounts from February and March sales. You may meno changes to the WIP balances. Each unit produced requires $9.00 of raw materials (representing 2 units of material with puretinse cost of $4.50 per unit) and $20.00 of direct labour (2 hours of direct labour are required for each unit). There is a minimum of 5.000 labour hours per month regardless of how much time is worked. All labour is paid at $10 per hour fe, no overtime is paid. Overhead is applied on the basis of direct labour hours. Variable overhead is estimated to tel.50 per direct labour hour. The desired finished goods inventory is 60% of the next month's sales. The desired raw materials inventory is 40% of the next month's production requirements. All purchases are paid in the month following the purchase and AP is current Fixed manufacturing overhead is estimated at $30,000 per month including total depreciation per month for manufacturing assets of $5.000, Salaries, wages and commissions average 10% of sales, all other administrative expenses excluding depreciation is 595 of sales. Fixed selling and administrative expenses for rent property taxes, depreciation and other items are $50.000 per month Depreciation is $3.000 per month There is a planned acquisition of a new machine in April for S150,000 which will be paid for in April A dividend of S100,000 will be paid in June Any borrowing the company makes are effective at the beginning of the month and all repayments are made at the end of the month. Ignore interest for the purposes of this assignment. The company does not want to begin a month with less than $30,000 in beginning cash. Income taxes can be ignored. Required: Prepare monthly budgeted income statements and cash flow projections for the quarter (April, May & June) along with all the supporting budgets and a Balance Sheet as of June 30, 2020). Each budget component should be prepared on a separate sheet. This must be prepared using Excel (DO NOT USE ANOTHER SPREADSHEET PROGRAM) and submitted electronically via email. PLEASE INCLUDE THE NAMES OF ALL GROUP MEMBERS ON THE FIRST SHEET OF YOUR EXCEL FILE. AS PER COURSE OUTLINE. THIS ASSIGNMENT MUST BE DONE IN TEAMS OF 2.3 HINT: If everything is correct you should get the following numbers: Cost per unit of Finished Goods: $36.21 Total Assets: $2.237,384 Net Income: $175,477