You are the sole shareholder and operator of a small incorporated business that purchases compact global positioning systems (GPS) and sells them to retailers. The following data have been assembled to assist in the preparation of the master budget for the first quarter (January, February and March) of 2022. As of December 31, 2021 your company had the following balance sheet: Your Company (make up a name) Balance Sheet December 31, 2021 Cash Accounts receivable Inventory Prepaid insurance Total current assets Equipment Accumulated depreciation Net equipment Total assets $ 8,000 11,780 15,960 8,400 44,140 16,000 6,000 10,000 $ 54,140 Accounts payable $ 22,470 Taxes payable 1,800 ST loan payable 2,400 Total current liabilities 26,670 LT loan payable 10,000 Total liabilities 36,670 Common shares 16,000 Retained earnings 1,470 Total liabilities and equity $ 54,140 * 1,000 common shares outstanding A. The company sells each system for $380. Actual sales for November 2021 were 100 units and actual sales for December 2021 were 104. It is expected that sales will increase by 4% per month until May 2022. B. 75% of the cash for sales is collected in the month of sale, 20% is collected in the following month, and the remaining 5% is collected in the month after that. For simplicity, all sales taxes will be ignored. C. The company purchases enough units each month to cover the current month's sales and maintain an ending inventory equal to 70% of the following month's projected sales. Each unit costs the company $210. Inventory purchases are paid for in the month following the purchase. D. The company is expected to incur fixed operating expenses of S2,400 per month and variable operating expenses equal to 10% of sales for the month. Operating expenses are paid for in the month incurred. E. Monthly interest is paid on the long-term loan at a rate of 6% per annum. At the end of March, a payment of $1,000 will be made on the principal of the loan. F. On August 1, 2021, the company paid $14,400 for one year's insurance coverage. G. Equipment costing $6,000 will be purchased and paid for at the beginning of January. All equipment (existing and new) is depreciated on a straight-line basis over 15 years with no residual value. H. The company will issue 100 additional common shares and sell them to your uncle for $20.00 per share at the end of March 2022 1. The company pays salaries totalling $8,000 each month. For simplicity, ignore all payroll tax implications. J. The company will declare and pay a dividend of $3,000 at the beginning of March 2022. K. Income tax expense for this small business is calculated at 28% of the earnings before taxes. The company pays income tax instalments of $1,000 per month. L. The company must maintain a minimum cash balarce of $8,000. A short-term loan is available to cover any shortfall. Interest is paid monthly on the previous month's loan balance at a rate of 5% per annum. Any cash above $8,000 available at month end is used to reduce any existing short-term loan. Required: Use Microsoft Excel to complete this assignment. Each student is to create his/her own file from scratch, and complete the assignment individually. Use formulas wherever possible. The spreadsheet should be formatted to show amounts to the nearest dollar (no cents). The items in the budget should appear in the following order: 1. The balance sheet for December 31, 2021 (as given above). 2. A cash receipts schedule for January, February and March Hint: use the given percentages, with the Round function (explained below), to avoid a rounding error. Check figure: Cash receipts for January should be $40,584. 3. A purchases schedule in units for January, February and March. Check figure: January purchases should be 110 units. 4. A cash payments schedule for January, February and March. Check figure: January's total cash payments should be $44,034. 5. A cash budget for January, February and March, including the balance of the short-term loan balance at the end of each month (presented below the cash budget). Check figure: At the end of January the cash balance should be $8,000 and the short-term loan balance should be $971 at the end of March. 6. The pro-forma income statements for January, February and March with a fourth column for totals. Subtotals for EBIT and EBT should be included. List all expenses separately (do not combine) and show long-term and short-term interest separately. Hint: Cost of goods sold is not the same thing as purchases. Cost of goods sold is based on sales. Check figure: January's earnings after taxes should be $1,781. 7. A pro-forma retained earnings schedule for the quarter ended March 31 (not for each month). Check figure: Ending retained earnings should be $4,943. 8. A pro-forma balance sheet at March 31. You do not have to complete balance sheets for January or February. Hints: Consider what will cause balances to change from the December 31, 2021 balance sheet. Prepaid insurance will be the opening amount less the amount expensed on the income statement Tax payable will be the opening balance plus total tax expense less total tax instalments. Check figure: Total assets should be $59,432. You are the sole shareholder and operator of a small incorporated business that purchases compact global positioning systems (GPS) and sells them to retailers. The following data have been assembled to assist in the preparation of the master budget for the first quarter (January, February and March) of 2022. As of December 31, 2021 your company had the following balance sheet: Your Company (make up a name) Balance Sheet December 31, 2021 Cash Accounts receivable Inventory Prepaid insurance Total current assets Equipment Accumulated depreciation Net equipment Total assets $ 8,000 11,780 15,960 8,400 44,140 16,000 6,000 10,000 $ 54,140 Accounts payable $ 22,470 Taxes payable 1,800 ST loan payable 2,400 Total current liabilities 26,670 LT loan payable 10,000 Total liabilities 36,670 Common shares 16,000 Retained earnings 1,470 Total liabilities and equity $ 54,140 * 1,000 common shares outstanding A. The company sells each system for $380. Actual sales for November 2021 were 100 units and actual sales for December 2021 were 104. It is expected that sales will increase by 4% per month until May 2022. B. 75% of the cash for sales is collected in the month of sale, 20% is collected in the following month, and the remaining 5% is collected in the month after that. For simplicity, all sales taxes will be ignored. C. The company purchases enough units each month to cover the current month's sales and maintain an ending inventory equal to 70% of the following month's projected sales. Each unit costs the company $210. Inventory purchases are paid for in the month following the purchase. D. The company is expected to incur fixed operating expenses of S2,400 per month and variable operating expenses equal to 10% of sales for the month. Operating expenses are paid for in the month incurred. E. Monthly interest is paid on the long-term loan at a rate of 6% per annum. At the end of March, a payment of $1,000 will be made on the principal of the loan. F. On August 1, 2021, the company paid $14,400 for one year's insurance coverage. G. Equipment costing $6,000 will be purchased and paid for at the beginning of January. All equipment (existing and new) is depreciated on a straight-line basis over 15 years with no residual value. H. The company will issue 100 additional common shares and sell them to your uncle for $20.00 per share at the end of March 2022 1. The company pays salaries totalling $8,000 each month. For simplicity, ignore all payroll tax implications. J. The company will declare and pay a dividend of $3,000 at the beginning of March 2022. K. Income tax expense for this small business is calculated at 28% of the earnings before taxes. The company pays income tax instalments of $1,000 per month. L. The company must maintain a minimum cash balarce of $8,000. A short-term loan is available to cover any shortfall. Interest is paid monthly on the previous month's loan balance at a rate of 5% per annum. Any cash above $8,000 available at month end is used to reduce any existing short-term loan. Required: Use Microsoft Excel to complete this assignment. Each student is to create his/her own file from scratch, and complete the assignment individually. Use formulas wherever possible. The spreadsheet should be formatted to show amounts to the nearest dollar (no cents). The items in the budget should appear in the following order: 1. The balance sheet for December 31, 2021 (as given above). 2. A cash receipts schedule for January, February and March Hint: use the given percentages, with the Round function (explained below), to avoid a rounding error. Check figure: Cash receipts for January should be $40,584. 3. A purchases schedule in units for January, February and March. Check figure: January purchases should be 110 units. 4. A cash payments schedule for January, February and March. Check figure: January's total cash payments should be $44,034. 5. A cash budget for January, February and March, including the balance of the short-term loan balance at the end of each month (presented below the cash budget). Check figure: At the end of January the cash balance should be $8,000 and the short-term loan balance should be $971 at the end of March. 6. The pro-forma income statements for January, February and March with a fourth column for totals. Subtotals for EBIT and EBT should be included. List all expenses separately (do not combine) and show long-term and short-term interest separately. Hint: Cost of goods sold is not the same thing as purchases. Cost of goods sold is based on sales. Check figure: January's earnings after taxes should be $1,781. 7. A pro-forma retained earnings schedule for the quarter ended March 31 (not for each month). Check figure: Ending retained earnings should be $4,943. 8. A pro-forma balance sheet at March 31. You do not have to complete balance sheets for January or February. Hints: Consider what will cause balances to change from the December 31, 2021 balance sheet. Prepaid insurance will be the opening amount less the amount expensed on the income statement Tax payable will be the opening balance plus total tax expense less total tax instalments. Check figure: Total assets should be $59,432