BUAD 195 MASTER BUDGET PROJECT Fall 2022 You are the sole shareholder and operator of a small incorporated business that purchases in-a-box security systems and re-sells them. You started your business five years ago. The following data have been assembled to assist in the preparation of the master budget for the first quarter (January, February and March) of 2023. As of December 31, 2022, your company had the following balance sheet: Company Name (Replace with your company name) BUAD 195 MASTER BUDGET PROJECT Fall 2022 Company Information 1. The company sells each system for $550. Actual sales for November were 200 units and for December were 220 units. Projected sales for January are 230 units, 250 for February, 250 for March, and 250 for April. 2. Sales are all on account and 40% of the cash for sales is collected in the month of sale, 20% is collected in the following month, and the remaining 40% is collected in the month after that. 3. The company purchases enough units each month to cover the current month's sales and maintain an ending inventory equal to 30% of the following month's projected sales. Each unit costs the company $375. Inventory purchases are paid for in the month following purchase. 4. The company is expected to incur and pay fixed operating expenses of $5,500 per month. 5. On August 1, 2022, the company paid $6,000 for one year's insurance coverage. Hint: think back to adjusting entries from Chapter 3 of BUAD111 Financial Accounting 1 for how to deal with this. 6. Variable operating expenses are projected to be 12% of sales and are paid in the month incurred. 7. Interest is paid monthly on the long-term loan at a rate of 6% per year. They are also required to make quarterly principal payments, the next is due at the end of March for $2,500. 8. Equipment costing $35,000 will be purchased for cash at the beginning of January. All equipment is depreciated on a straight-line basis over 10 years with no residual value. Hint: think back to adjusting entries from Chapter 3 of BUAD111 Financial Accounting 1 for how to deal with this. 9. You pay salaries totaling $3,000 each month. For simplicity, ignore all payroll tax implications. 10. You sell 1,000 additional common shares to your uncle for $2.00 per share at the beginning of February. 11. You will declare and pay a dividend of $6,000 at the beginning of February. 12. Income tax expense for this small business is calculated at 30% of the earnings before taxes. The company pays income tax installments of $600 per month 13. The company must maintain a minimum cash balance of $10,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 12% per year. Any cash above $10,000 available at month end is used to reduce any existing short-term loan. The interest for the short term debt should be calculated and shown separately from the long term debt. Both borrowings and repayments are assumed to occur at the beginning of the month. PART 1- DUE NOVEMBER 6 1. The balance sheet for December 31, 2022 (as given above). 2. A sales forecast in units and dollars. 3. A cash receipts schedule for January, February and March. 4. A purchases schedule in units for January, February and March. 5. A cash payments schedule for January, February and March. 6. A cash budget for January, February and March, including a calculation of cumulative loan at the bottom. PART 2 NOVEMBER 20 7. The pro-forma income statements for January, February and March. You should also have a total column which totals all three months. a) Subtotals for EBIT and EBT should be included. b) List all expenses separately (do not combine). c) Show long-term and short-term interest separately. Hint: Cost of goods sold is not the same thing as purchases. 8. A pro-forma retained earnings schedule for the quarter ended March 31 (not for each month). 9. A pro-forma balance sheet at March 31. You do not have to complete balance sheets for January or February. Hint: Consider what will cause balances to change from the December 31, 2022 balance sheet. a) Prepaid insurance will be the opening amount less the amount expensed on the income statement. b) Tax payable will be the opening balance plus total tax expense less total tax installments. MICROSOFT EXCEL Use Microsoft Excel to complete this assignment. Each student is to create their own Excel file, and complete the assignment individually. Wherever possible, the spreadsheet should use formulas and cell referencing. Format: - For numbers, use the Accounting format with zero decimal places. The spreadsheet should be formatted to show amounts to the nearest dollar (no cents). Do not use =ROUND to achieve this, use the 'Decrease Decimal' button in the number section of the Home ribbon: - If your ending balance sheet is out by $1, do not worry about it. - Marks will be awarded for a neat, well laid out and formatted 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. - Lines should be included to show where calculations occur, with double lines at the bottom of each schedule. See the balance sheet on the previous page. 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 December 31 , plus total tax expense, minus total tax installments. Cell referencing: As often as possible, cell referencing must be used - you will not get the check figures 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. Submissions: Late submissions will not be accepted. Your excel file is to be submitted into a drop box in the Ch 4A tile. BUAD 195 MASTER BUDGET PROJECT Fall 2022 You are the sole shareholder and operator of a small incorporated business that purchases in-a-box security systems and re-sells them. You started your business five years ago. The following data have been assembled to assist in the preparation of the master budget for the first quarter (January, February and March) of 2023. As of December 31, 2022, your company had the following balance sheet: Company Name (Replace with your company name) BUAD 195 MASTER BUDGET PROJECT Fall 2022 Company Information 1. The company sells each system for $550. Actual sales for November were 200 units and for December were 220 units. Projected sales for January are 230 units, 250 for February, 250 for March, and 250 for April. 2. Sales are all on account and 40% of the cash for sales is collected in the month of sale, 20% is collected in the following month, and the remaining 40% is collected in the month after that. 3. The company purchases enough units each month to cover the current month's sales and maintain an ending inventory equal to 30% of the following month's projected sales. Each unit costs the company $375. Inventory purchases are paid for in the month following purchase. 4. The company is expected to incur and pay fixed operating expenses of $5,500 per month. 5. On August 1, 2022, the company paid $6,000 for one year's insurance coverage. Hint: think back to adjusting entries from Chapter 3 of BUAD111 Financial Accounting 1 for how to deal with this. 6. Variable operating expenses are projected to be 12% of sales and are paid in the month incurred. 7. Interest is paid monthly on the long-term loan at a rate of 6% per year. They are also required to make quarterly principal payments, the next is due at the end of March for $2,500. 8. Equipment costing $35,000 will be purchased for cash at the beginning of January. All equipment is depreciated on a straight-line basis over 10 years with no residual value. Hint: think back to adjusting entries from Chapter 3 of BUAD111 Financial Accounting 1 for how to deal with this. 9. You pay salaries totaling $3,000 each month. For simplicity, ignore all payroll tax implications. 10. You sell 1,000 additional common shares to your uncle for $2.00 per share at the beginning of February. 11. You will declare and pay a dividend of $6,000 at the beginning of February. 12. Income tax expense for this small business is calculated at 30% of the earnings before taxes. The company pays income tax installments of $600 per month 13. The company must maintain a minimum cash balance of $10,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 12% per year. Any cash above $10,000 available at month end is used to reduce any existing short-term loan. The interest for the short term debt should be calculated and shown separately from the long term debt. Both borrowings and repayments are assumed to occur at the beginning of the month. PART 1- DUE NOVEMBER 6 1. The balance sheet for December 31, 2022 (as given above). 2. A sales forecast in units and dollars. 3. A cash receipts schedule for January, February and March. 4. A purchases schedule in units for January, February and March. 5. A cash payments schedule for January, February and March. 6. A cash budget for January, February and March, including a calculation of cumulative loan at the bottom. PART 2 NOVEMBER 20 7. The pro-forma income statements for January, February and March. You should also have a total column which totals all three months. a) Subtotals for EBIT and EBT should be included. b) List all expenses separately (do not combine). c) Show long-term and short-term interest separately. Hint: Cost of goods sold is not the same thing as purchases. 8. A pro-forma retained earnings schedule for the quarter ended March 31 (not for each month). 9. A pro-forma balance sheet at March 31. You do not have to complete balance sheets for January or February. Hint: Consider what will cause balances to change from the December 31, 2022 balance sheet. a) Prepaid insurance will be the opening amount less the amount expensed on the income statement. b) Tax payable will be the opening balance plus total tax expense less total tax installments. MICROSOFT EXCEL Use Microsoft Excel to complete this assignment. Each student is to create their own Excel file, and complete the assignment individually. Wherever possible, the spreadsheet should use formulas and cell referencing. Format: - For numbers, use the Accounting format with zero decimal places. The spreadsheet should be formatted to show amounts to the nearest dollar (no cents). Do not use =ROUND to achieve this, use the 'Decrease Decimal' button in the number section of the Home ribbon: - If your ending balance sheet is out by $1, do not worry about it. - Marks will be awarded for a neat, well laid out and formatted 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. - Lines should be included to show where calculations occur, with double lines at the bottom of each schedule. See the balance sheet on the previous page. 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 December 31 , plus total tax expense, minus total tax installments. Cell referencing: As often as possible, cell referencing must be used - you will not get the check figures 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. Submissions: Late submissions will not be accepted. Your excel file is to be submitted into a drop box in the Ch 4A tile