1 / 4 100% Buad 195 Budget Project 2021 I Page 1 of 4 SHO LA You were recently hired by Manly 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: 1. Manly Meats Balance Sheet March 31, 2021 $ Cash Accounts receivable Inventory Prepaid insurance Total current assets Property, plant and equipment Accumulated depreciation Net property, plant and equipment Total assets $ 11,000 63,600 13,475 12,000 100,075 104,000 47.000 57,000 $ 157,075 Accounts payable Taxes payable Total current liabilities Long term loan payable Total liabilities Common shares Retained earnings Total shareholders equity Total liabilities and equity 19,294 3,500 22,794 50,000 72,794 22.000 62,281 84,281 157,075 $ 2. MM sells the NOMAD for $600 eachRecent and forecasted sales (in units) are as follows: 2. MM sells the NOMAD for $600 each. Recent and forecasted sales (in units) are as follows: January (actual) February (actual) March (actual) April May June July August September 60 80 100 110 190 250 300 320 200 Buad 195 Budget Project 2021 Page 2 of 4 3. 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 55 units on hand on March 31". 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 4. All sales are on credit with 10% being collected in the month of sale, 70% being collected the month after sale and the remaining 20% being collected two months after the sale. 4. All sales are on credit with 10% being collected in the month of sale, 70% being collected the month after sale and the remaining 20% being collected two months after the sale. 5. 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. 6. The company plans to pay a dividend of $5,000 in April. 7. MM will be purchasing a used minivan to be used for deliveries in May. The expected cost will be $10,000, paid with cash. 8. Interest is paid monthly on the long term debt at the rate of 6% per year. The remaining balance of the loan will be repaid in 2023. 9. Income taxes are estimated to be 25% of earnings before taxes. MM pays income tax instalments of $2,000 every month. 10. 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 8% 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. 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). Your instructor will tell you exactly what to submit and when, and will inform you as to penalties for late submission 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,200. 3. A purchases schedule in units for April, May and June. Check figure: April purchases should be 150 units. 4. A cash payments for purchases for April, May and June. Check figure: April cash payment for purchases should be $28,481 5. A cash payments schedule for April, May and June. Check figure: April's total cash payments should be $58,931. 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 should be $7,219 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. List all expenses separately (do not combine). ill. 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 59,075 8 A pro forma retained earnings schedule for the quarter onded June 30 Check figure: Ending retained earnings should be $131,495 A pro forma balance sheet at june 30 Hint: Consider what will cause balances to change from the March 31 2021 balance sheet Check figure Total assets should be $276,264 9