Budget Project Winter 2021.xlsx BUAD 195 Master Budget Project ‐ Winter 2021 1 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 March 31, 2021 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 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 a er sale and the remaining 20% being collected two months after the sale. 4.Fixed opera ng expenses are $19,000 every month, including $2,500 in deprecia on. 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. BUAD 195 Master Budget Project ‐ Winter 2021 2 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 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 es mated 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: Angelee Dofeliz Angelee Dofeliz Angelee Dofeliz Angelee Dofeliz Angelee Dofeliz BUAD 195 Master Budget Project ‐ Winter 2021 3 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$ Part 3: Instructions Format: •For numbers, use the Accoun ng format with zero decimal places. •If your ending balance sheet is out by $1 or $2, do not worry about it, it’s just rounding error. •Marks will be awarded for a neat, well laid out and forma ed spreadsheet. It should be easy to follow and have a good flow to it. •Schedules should flow down a worksheet, not side by side. So, the opening balance sheet would be at the top, the cash receipts schedule below it. Feel free to use a vertical style balance sheet (instead of the horizontal one presented) so that all columns with labels are on the left, this will make formatting easier. •Lines should be included to show where calcula ons occur, with double lines at the bo om of each schedule. See the opening balance sheet. •Dollar signs should be used only at the top and bo om of columns in a schedule, not on every number. •If you are unsure of where $ and underlining should be used, look at sample financial statements from your text. Formulas: Many of the numbers in the schedules, most of the numbers in the income statement, and all of the numbers in the retained earnings statement and the closing balance sheet should be the result of formulas or referenced cells. For the pro‐forma balance sheet, some of the formulas will start with a reference to the opening balance sheet. For example, taxes payable will be the opening amount from March 31, plus total tax expense, minus total tax instalments. Cell referencing: Whenever possible, cell referencing must be used – you will not get the check figures provided unless you use cell referencing whenever possible. An efficient schedule design allows you to make changes in one area and have them update automatically in others. Due Date: March 19th at 11:59 pm, to be uploaded through a Moodle drop‐box. Late submissions will have a 15% per day deduction, 1 day starts 1 minute after the due date (it will show as late in Moodle). The file should be named as FirstNameLastName, e.g. maryannknoll.xlsx. Plagiarism: Each person is to create his/her own Excel file and do this assignment individually. You can help each other figure out formulas but no copying and absolutely NO SHARING OF SPREADSHEETS IN ANY WAY SHAPE OR FORM. At no point should you email/text or in any way electronically share your spreadsheet with another student. Assistance from the Professor: Before the due date, if you need help, you can send me your file by email (don’t upload on Moodle until final) and I’ll give you hints as to what is wrong. You can email me up to three times. And when we work on it in class, we can go into a breakout room and you can share your screen with me. When emailing your file for hints before the due date, the file should be named with your first and last name as noted above. Remember, it could take me up to 24 hours to respond, or more on a weekend. I may not be available on the night of the 19th, so if you choose to complete the budget at the last minute, you may not receive any help.