You were recently hired by Vroom Vroom Ltd. to be their bookkeeper. Vroom Vroom has an arrangement with a supplier to build a modified Segway called the "Putt Putt" which is used on golf courses. prepare a budget and the pro-forma financial statements for the next quarter. You have been provided with the following financial information to assist you in your task: 1. Balance Sheet: Vroom Vroom Ltd. Balance Sheet March 31, 2021 Assets Cash $ 25,000 Accounts receivable 109,900 Inventory 14,400 Prepaid insurance 9,900 Property, plant and equipment 220,000 Accumulated depreciation (38,000) Total assets $ 341,200 Liabilities and Equity Accounts payable $ 27,456 Taxes payable 16,500 Long term loan payable 100,000 Common shares 22,000 Retained earnings 175,244 Total liabilities and equity $ 341,200 BUAD 195 Master Budget Project Summer I 2021 2. Vroom Vroom sells the Product: for $700 each. Recent and forecasted sales (in units) are as follows: January (actual) 100 February (actual) 190 March (actual) 140 April 150 May 290 June 290 July 300 August 340 September 250 3. All sales are on credit with 15% being collected in the month of sale, 65% being collected the month after sale and the remaining 20% being collected two months after the sale. 4. Management like to have enough Putt Putts on hand to cover 30% of the next months expected sales. This policy was achieved in the first quarter of 2021 as the company had 45 units on hand on March 31st. Vroom Vroom purchases the Putt Putt for $320 per unit. Purchases are paid for 40% in the month of purchase and the remaining 60% the following month. 5. Fixed operating expenses are $25,000 every month, including $3,000 in depreciation. Included in fixed operating expenses is $1,100 for insurance, which is paid once a year in January. Variable operating expenses average $70 per Putt Putt sold (paid in the month of sale). 6. The company plans to pay a dividend of $30,000 in May. 7. Vroom Vroom will be purchasing a used moving truck to be used for deliveries in April. The expected cost will be $31,000 paid with cash. 8. Interest is paid monthly on the long-term debt at the rate of 7% per year. The remaining balance of the loan will be repaid in 2023. 9. Income taxes are estimated to be 30% of earnings before taxes. Vroom Vroom pays income tax instalments of $5,000 every month. 10.The terms of the long-term debt require Vroom Vroom to maintain a minimum cash balance of $10,000. A line of credit is available to cover any shortfall. Interest is paid monthly on the previous months line of credit balance at 9% per year. Any cash above the $10,000 balance at the end of the month will be used to repay any existing line of credit balance. BUAD 195 Master Budget Project Summer I 2021 Required Use a spreadsheet application such as Excel to complete this assignment. Each student is to create his/her own Excel file, and complete the assignment individually. Use formulas wherever possible. Your spreadsheet should be formatted to show amounts to the nearest dollar (no cents). 1. The balance sheet for March 31, 2021 (as given). 2. A cash receipts schedule for April, May and June. Check figure: Cash collections/receipts from sales for April should be $106,050 3. An inventory purchases schedule in units for April, May and June. Check figure: April purchases in units should be 192 units 4. Cash payments for inventory purchases for April, May and June. Check figure: April cash payment for inventory purchases should be $52,032 5. A cash budget for April, May and June, including a calculation of cumulative loan at the bottom. Check figure: April's total cash outflows should be $120,015 Cash balance at the end of May should be $10,000 And the cumulative short-term loan $31,433 6. Pro-forma income statements for April, May and June. You should also have a total/"Q2" column which shows the income statement for the entire quarter. a. Subtotals for Gross Margin, EBIT, and EBT should be included. b. List all expenses separately (do not combine). c. Show long-term and short-term interest separately. d. Hint: Cost of goods sold is not the same thing as purchases. Check figure: April earnings after taxes should be $14,642 7. Pro-forma retained earnings schedule for the quarter ended June 30th (you do not need to complete monthly retained earnings schedules). Check figure: Ending retained earnings should be $249,764 8. Pro-forma balance sheet at June 30th. Hint: Consider what will cause balances to change from the March 31, 2021 balance sheet. You might want to reiterate the March balance sheet at this point for easy comparison. Check figure: Total assets should be $474,314