Please show how you attained these numbers

Part 1: Information about the company: You were recently hired by Mainly Meats (MM) to be their bookkeeper. MM imports and sells a single product, the NOMAD Briefcase Grill & Smoker. One of your first tasks is to 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: Mainly Meats Balance Sheet March 31, 2021 Assets Liabilities and Equity Cash $ 10,000 Accounts payable $ 19,753 Accounts receivable 60,600 Taxes payable 3,500 Inventory 14,088 Total current liabilities 23,253 Prepaid insurance 9,000 Long term loan payable 50,000 Total current assets 93,688 Total liabilities 73,253 Property, plant and equipment 104,000 Common shares 22,000 Accumulated depreciation 47,000 Retained earnings 55,434 Net property, plant and equipment 57,000 Total shareholders equity 77,434 Total assets $ 150,688 Total liabilities and equity $ 150,688 1. MM sells the NOMAD for $600 each. Recent and forecasted sales (in units) are as follows: January (actual) 60 February (actual) 80 March (actual) 100 April 115 May 190 June 250 July 305 August 320 September 200 2. Management like to have enough NOMADs on hand to cover half of the next months expected sales. This policy was achieved in the first quarter of 2021 as the company had half of April projected sales on hand at the end of March. MM purchases the NOMAD for $245 per unit. Purchases are paid for 25% in the month of purchase and the remaining 75% the following month. 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.Fixed operating expenses are $19,000 every month, including $2,500 in depreciation. Included in fixed operating expenses is $1,000 for insurance, which is paid once a year in January. Variable operating expenses average $70 per NOMAD sold. 5.The company plans to pay a dividend of $5,000 in April. 6.MM will be purchasing a used minivan to be used for deliveries in May. The expected cost will be $10,000, paid with cash. 7.Interest is paid monthly on the long term debt at the rate of 5% per year. The remaining balance of the loan will be repaid in 2023. 8.Income taxes are estimated to be 22% of earnings before taxes. MM pays income tax instalments of $2,000 every month, this will reduce their total taxes payable. 9.The terms of the long term debt require MM to maintain a minimum cash balance of $5,000. A line of credit is available to cover any shortfall. Interest is paid monthly on the previous months line of credit balance at 7% per year. Any cash above the $5,000 balance at the end of the month will be used to repay any existing line of credit balance. Part 2: Required Use a spreadsheet application such as Excel to complete this assignment. Each student is to create their 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). When rounding, do not use the Round formula, instead just have no decimal places. The items in the budget should appear in the following order: 1. The balance sheet for March 31, 2021 (as given). 2. A cash receipts schedule for April, May and June. Check figure: Cash receipts for April should be 58,950 3. A purchases schedule in units for April, May and June. Check figure: April purchases in units should be 153 4. Cash payments for purchases for April, May and June. Check figure: April cash payment for purchases should be 29,094 5. A cash payments schedule for April, May and June. Check figure: April's total cash payments should be 59,852 6. A cash budget for April, May and June, including a calculation of cumulative loan at the bottom. Check figure At the end of May the cash balance should be 5,000 and the cumulative loan 4,457 7. The pro-forma income statements for April, May and June. You should also have a total column which totals all three months. i. Subtotals for EBIT and EBT should be included. ii. List all expenses separately (do not combine). iii. Show long-term and short-term interest separately. iv. Hint: Cost of goods sold is not the same thing as purchases. Check figure: April earnings after taxes should be 10,582 3 BUAD 195 Master Budget Project - Winter 2021 8. A pro-forma retained earnings schedule for the quarter ended June 30th. Check figure: Ending retained earnings should be 128,843 9. A pro-forma balance sheet at June 30th. Hint: Consider what will cause balances to change from the March 31 2021 balance sheet. Check figure: Total assets should be $ 271,449