Budget Assignment Part 1 I Transcona Manufacturing operates in Winnipeg and produces and distributes a special type of chemical compound called Compound WX which is an essential product used in home construction. The information below about Transcona's operations has been assembled to assist budget preparation. The company is preparing its master budget for the first quarter of 2022. The budget will detail each month's activity and the activity for the quarter in total. The master budget will be based on the following information: a. Selling price is $65 per unit in 2021 and will not change for the first two quarters of 2022. Actual and estimated sales are as follows: Actual 2021 November December 10,000 units 12,000 units Estimated 2022 January February March April May b. The company produces enough units each month to meet that month's sales plus a desired inventory level equal to 20% of next month's estimated sales. Finished goods inventory at the end of 2021 consisted of 2,200 units at a variable cost of $37.50 each. 11,000 units 10,000 units 13,000 units 11,000 units 11,000 units c. The company purchases enough raw materials each month for the current month's production requirement and 25% of next month's production requirements. Each unit of product requires 5 kilograms of raw material at $1.50 per kilogram, which are purchased from suppliers overseas. There were 13,500 kilograms of raw materials in inventory at the end of 2021. Transcona pays 40% of raw material purchases in the month of purchase and the remaining 60% in the following month. d. Each unit of finished product requires 1.25 tabour-hours. The average wage rate is $16 per hour. e. Variable manufacturing overhead is 50% of direct labour cost. f. Credit sales are 60% of total sales. Transcona collects 50% of the credit sales during the first month following the month of sale and 50% during the second month. Fixed overhead costs (per month) are as follows: 8. Factory Supervisor's salary Factory Insurance Factory rent Depreciation of factory equipment Advertising Depreciation Insurance Salaries Other $75,000 1,400 8,000 1,200 h. Total fixed selling and administrative expenses per month are as follows: $300 9,000 250 4,000 14,550 i. Variable selling and administrative expenses consist of $4 for shipping and 10% of sales for commissions. j. The company will acquire assets on January 2 for use in the sales office at a cost of $200,000, which will be paid at the end of January 2022. The monthly depreciation expenses on the additional capital assets will be $6,000. Transcona records a full month of depreciation on assets acquired in the month of purchase. k. The balance sheet as at December 31, 2021 is as follows: Assets: Cash Accounts Receivable Inventory: Raw materials Plant and Equipment Less accumulated depreciation Total assets Liabilities and Equity: Accounts Payable 6% Long term notes payable Common Shares Retained Earnings Total liabilities and shareholders' equity Finished goods $20,250 82,500 1,000,000 (100,000) $80,000 663,000 Additional information is as follows: All cash payments except purchases of raw materials are made monthly as incurred. All borrowings occur at the beginning of each month, and all repayments occur at the end of the month. Borrowings and repayments may occur in any amount. All interest on borrowed funds is paid at the end of each month at a rate of 0.5% per month. A minimum cash balance of $15,000 is required at the end of each month. 102,750 900,000 $1,745,750 $60,000 900,000 735,000 50,750 $1,745,750 Required part A: 1. Prepare in an excel spreadsheet the following budgets for each of the three months of 2022 (at end of this document you can find templates to assist you; note of course that you will need to re-create them in your excel spreadsheet): a. Sales budget. b. Production budget. c. Raw materials purchases budget. d. Direct labour and manufacturing overhead budget. e. Selling and administrative budget f. Cash budget 2. Prepare a budgeted contribution format income statement for each of the first three months of 2022 and a budgeted balance sheet as at March 31, 2022. Required part B: Transcona Manufacturing has used essentially the same budget parameters for the last four years of operations. The company was able to maintain operations throughout 2021. Sales were robust, although production volumes were lower than budgeted because of high worker absenteeism due to COVID-19 outbreaks. The company has found that significant deviations between actual and budgeted results are becoming more and more frequent. Looking ahead to the budget for 2022 for Transcona Manufacturing, what budget assumptions would you recommend the company reconsider, and why? How do you think they could affect the budget and cash flow of the company? (It is not necessary to make up new assumptions and/or re-do the budget: we just want you to consider WHICH items may need attention). a. Sales Budget Sales in units Unit sales price Sales in dollars b. Production Budget Sales in units Add: Desired ending finished goods inventory Finished goods requirements Less: Beginning finished goods inventory Production requirements January January February February March March c. Raw materials purchases budget Units of production required Units of raw materials required (5 kg per unit) Add Desired ending inventory Total requirements Less Beginning inventory Raw materials to purchase: In Units (kilograms) In Dollars d. Direct labour and Overhead budget Units of production required Direct labour hours required (1.25 per unit) Direct labour cost ($16/hr) Manufacturing overhead: Variable (50% of DL) Fixed Total manufacturing overhead Total cost of direct labour and manufacturing overhead Less non cash portion (eg. Depn) Cash outlay for DL and MOH January January February February March March e. Selling & administrative budget Sales in units Variable: Shipping ($4 per unit) Sales commissions (10% of sales) Total variable s&a Fixed selling and admin Less non cash portion (eg. Depn) Cash outlay for fixed selling and admin January February March Budget Assignment Part 1 I Transcona Manufacturing operates in Winnipeg and produces and distributes a special type of chemical compound called Compound WX which is an essential product used in home construction. The information below about Transcona's operations has been assembled to assist budget preparation. The company is preparing its master budget for the first quarter of 2022. The budget will detail each month's activity and the activity for the quarter in total. The master budget will be based on the following information: a. Selling price is $65 per unit in 2021 and will not change for the first two quarters of 2022. Actual and estimated sales are as follows: Actual 2021 November December 10,000 units 12,000 units Estimated 2022 January February March April May b. The company produces enough units each month to meet that month's sales plus a desired inventory level equal to 20% of next month's estimated sales. Finished goods inventory at the end of 2021 consisted of 2,200 units at a variable cost of $37.50 each. 11,000 units 10,000 units 13,000 units 11,000 units 11,000 units c. The company purchases enough raw materials each month for the current month's production requirement and 25% of next month's production requirements. Each unit of product requires 5 kilograms of raw material at $1.50 per kilogram, which are purchased from suppliers overseas. There were 13,500 kilograms of raw materials in inventory at the end of 2021. Transcona pays 40% of raw material purchases in the month of purchase and the remaining 60% in the following month. d. Each unit of finished product requires 1.25 tabour-hours. The average wage rate is $16 per hour. e. Variable manufacturing overhead is 50% of direct labour cost. f. Credit sales are 60% of total sales. Transcona collects 50% of the credit sales during the first month following the month of sale and 50% during the second month. Fixed overhead costs (per month) are as follows: 8. Factory Supervisor's salary Factory Insurance Factory rent Depreciation of factory equipment Advertising Depreciation Insurance Salaries Other $75,000 1,400 8,000 1,200 h. Total fixed selling and administrative expenses per month are as follows: $300 9,000 250 4,000 14,550 i. Variable selling and administrative expenses consist of $4 for shipping and 10% of sales for commissions. j. The company will acquire assets on January 2 for use in the sales office at a cost of $200,000, which will be paid at the end of January 2022. The monthly depreciation expenses on the additional capital assets will be $6,000. Transcona records a full month of depreciation on assets acquired in the month of purchase. k. The balance sheet as at December 31, 2021 is as follows: Assets: Cash Accounts Receivable Inventory: Raw materials Plant and Equipment Less accumulated depreciation Total assets Liabilities and Equity: Accounts Payable 6% Long term notes payable Common Shares Retained Earnings Total liabilities and shareholders' equity Finished goods $20,250 82,500 1,000,000 (100,000) $80,000 663,000 Additional information is as follows: All cash payments except purchases of raw materials are made monthly as incurred. All borrowings occur at the beginning of each month, and all repayments occur at the end of the month. Borrowings and repayments may occur in any amount. All interest on borrowed funds is paid at the end of each month at a rate of 0.5% per month. A minimum cash balance of $15,000 is required at the end of each month. 102,750 900,000 $1,745,750 $60,000 900,000 735,000 50,750 $1,745,750 Required part A: 1. Prepare in an excel spreadsheet the following budgets for each of the three months of 2022 (at end of this document you can find templates to assist you; note of course that you will need to re-create them in your excel spreadsheet): a. Sales budget. b. Production budget. c. Raw materials purchases budget. d. Direct labour and manufacturing overhead budget. e. Selling and administrative budget f. Cash budget 2. Prepare a budgeted contribution format income statement for each of the first three months of 2022 and a budgeted balance sheet as at March 31, 2022. Required part B: Transcona Manufacturing has used essentially the same budget parameters for the last four years of operations. The company was able to maintain operations throughout 2021. Sales were robust, although production volumes were lower than budgeted because of high worker absenteeism due to COVID-19 outbreaks. The company has found that significant deviations between actual and budgeted results are becoming more and more frequent. Looking ahead to the budget for 2022 for Transcona Manufacturing, what budget assumptions would you recommend the company reconsider, and why? How do you think they could affect the budget and cash flow of the company? (It is not necessary to make up new assumptions and/or re-do the budget: we just want you to consider WHICH items may need attention). a. Sales Budget Sales in units Unit sales price Sales in dollars b. Production Budget Sales in units Add: Desired ending finished goods inventory Finished goods requirements Less: Beginning finished goods inventory Production requirements January January February February March March c. Raw materials purchases budget Units of production required Units of raw materials required (5 kg per unit) Add Desired ending inventory Total requirements Less Beginning inventory Raw materials to purchase: In Units (kilograms) In Dollars d. Direct labour and Overhead budget Units of production required Direct labour hours required (1.25 per unit) Direct labour cost ($16/hr) Manufacturing overhead: Variable (50% of DL) Fixed Total manufacturing overhead Total cost of direct labour and manufacturing overhead Less non cash portion (eg. Depn) Cash outlay for DL and MOH January January February February March March e. Selling & administrative budget Sales in units Variable: Shipping ($4 per unit) Sales commissions (10% of sales) Total variable s&a Fixed selling and admin Less non cash portion (eg. Depn) Cash outlay for fixed selling and admin January February March Budget Assignment Part 1 I Transcona Manufacturing operates in Winnipeg and produces and distributes a special type of chemical compound called Compound WX which is an essential product used in home construction. The information below about Transcona's operations has been assembled to assist budget preparation. The company is preparing its master budget for the first quarter of 2022. The budget will detail each month's activity and the activity for the quarter in total. The master budget will be based on the following information: a. Selling price is $65 per unit in 2021 and will not change for the first two quarters of 2022. Actual and estimated sales are as follows: Actual 2021 November December 10,000 units 12,000 units Estimated 2022 January February March April May b. The company produces enough units each month to meet that month's sales plus a desired inventory level equal to 20% of next month's estimated sales. Finished goods inventory at the end of 2021 consisted of 2,200 units at a variable cost of $37.50 each. 11,000 units 10,000 units 13,000 units 11,000 units 11,000 units c. The company purchases enough raw materials each month for the current month's production requirement and 25% of next month's production requirements. Each unit of product requires 5 kilograms of raw material at $1.50 per kilogram, which are purchased from suppliers overseas. There were 13,500 kilograms of raw materials in inventory at the end of 2021. Transcona pays 40% of raw material purchases in the month of purchase and the remaining 60% in the following month. d. Each unit of finished product requires 1.25 tabour-hours. The average wage rate is $16 per hour. e. Variable manufacturing overhead is 50% of direct labour cost. f. Credit sales are 60% of total sales. Transcona collects 50% of the credit sales during the first month following the month of sale and 50% during the second month. Fixed overhead costs (per month) are as follows: 8. Factory Supervisor's salary Factory Insurance Factory rent Depreciation of factory equipment Advertising Depreciation Insurance Salaries Other $75,000 1,400 8,000 1,200 h. Total fixed selling and administrative expenses per month are as follows: $300 9,000 250 4,000 14,550 i. Variable selling and administrative expenses consist of $4 for shipping and 10% of sales for commissions. j. The company will acquire assets on January 2 for use in the sales office at a cost of $200,000, which will be paid at the end of January 2022. The monthly depreciation expenses on the additional capital assets will be $6,000. Transcona records a full month of depreciation on assets acquired in the month of purchase. k. The balance sheet as at December 31, 2021 is as follows: Assets: Cash Accounts Receivable Inventory: Raw materials Plant and Equipment Less accumulated depreciation Total assets Liabilities and Equity: Accounts Payable 6% Long term notes payable Common Shares Retained Earnings Total liabilities and shareholders' equity Finished goods $20,250 82,500 1,000,000 (100,000) $80,000 663,000 Additional information is as follows: All cash payments except purchases of raw materials are made monthly as incurred. All borrowings occur at the beginning of each month, and all repayments occur at the end of the month. Borrowings and repayments may occur in any amount. All interest on borrowed funds is paid at the end of each month at a rate of 0.5% per month. A minimum cash balance of $15,000 is required at the end of each month. 102,750 900,000 $1,745,750 $60,000 900,000 735,000 50,750 $1,745,750 Required part A: 1. Prepare in an excel spreadsheet the following budgets for each of the three months of 2022 (at end of this document you can find templates to assist you; note of course that you will need to re-create them in your excel spreadsheet): a. Sales budget. b. Production budget. c. Raw materials purchases budget. d. Direct labour and manufacturing overhead budget. e. Selling and administrative budget f. Cash budget 2. Prepare a budgeted contribution format income statement for each of the first three months of 2022 and a budgeted balance sheet as at March 31, 2022. Required part B: Transcona Manufacturing has used essentially the same budget parameters for the last four years of operations. The company was able to maintain operations throughout 2021. Sales were robust, although production volumes were lower than budgeted because of high worker absenteeism due to COVID-19 outbreaks. The company has found that significant deviations between actual and budgeted results are becoming more and more frequent. Looking ahead to the budget for 2022 for Transcona Manufacturing, what budget assumptions would you recommend the company reconsider, and why? How do you think they could affect the budget and cash flow of the company? (It is not necessary to make up new assumptions and/or re-do the budget: we just want you to consider WHICH items may need attention). a. Sales Budget Sales in units Unit sales price Sales in dollars b. Production Budget Sales in units Add: Desired ending finished goods inventory Finished goods requirements Less: Beginning finished goods inventory Production requirements January January February February March March c. Raw materials purchases budget Units of production required Units of raw materials required (5 kg per unit) Add Desired ending inventory Total requirements Less Beginning inventory Raw materials to purchase: In Units (kilograms) In Dollars d. Direct labour and Overhead budget Units of production required Direct labour hours required (1.25 per unit) Direct labour cost ($16/hr) Manufacturing overhead: Variable (50% of DL) Fixed Total manufacturing overhead Total cost of direct labour and manufacturing overhead Less non cash portion (eg. Depn) Cash outlay for DL and MOH January January February February March March e. Selling & administrative budget Sales in units Variable: Shipping ($4 per unit) Sales commissions (10% of sales) Total variable s&a Fixed selling and admin Less non cash portion (eg. Depn) Cash outlay for fixed selling and admin January February March Budget Assignment Part 1 I Transcona Manufacturing operates in Winnipeg and produces and distributes a special type of chemical compound called Compound WX which is an essential product used in home construction. The information below about Transcona's operations has been assembled to assist budget preparation. The company is preparing its master budget for the first quarter of 2022. The budget will detail each month's activity and the activity for the quarter in total. The master budget will be based on the following information: a. Selling price is $65 per unit in 2021 and will not change for the first two quarters of 2022. Actual and estimated sales are as follows: Actual 2021 November December 10,000 units 12,000 units Estimated 2022 January February March April May b. The company produces enough units each month to meet that month's sales plus a desired inventory level equal to 20% of next month's estimated sales. Finished goods inventory at the end of 2021 consisted of 2,200 units at a variable cost of $37.50 each. 11,000 units 10,000 units 13,000 units 11,000 units 11,000 units c. The company purchases enough raw materials each month for the current month's production requirement and 25% of next month's production requirements. Each unit of product requires 5 kilograms of raw material at $1.50 per kilogram, which are purchased from suppliers overseas. There were 13,500 kilograms of raw materials in inventory at the end of 2021. Transcona pays 40% of raw material purchases in the month of purchase and the remaining 60% in the following month. d. Each unit of finished product requires 1.25 tabour-hours. The average wage rate is $16 per hour. e. Variable manufacturing overhead is 50% of direct labour cost. f. Credit sales are 60% of total sales. Transcona collects 50% of the credit sales during the first month following the month of sale and 50% during the second month. Fixed overhead costs (per month) are as follows: 8. Factory Supervisor's salary Factory Insurance Factory rent Depreciation of factory equipment Advertising Depreciation Insurance Salaries Other $75,000 1,400 8,000 1,200 h. Total fixed selling and administrative expenses per month are as follows: $300 9,000 250 4,000 14,550 i. Variable selling and administrative expenses consist of $4 for shipping and 10% of sales for commissions. j. The company will acquire assets on January 2 for use in the sales office at a cost of $200,000, which will be paid at the end of January 2022. The monthly depreciation expenses on the additional capital assets will be $6,000. Transcona records a full month of depreciation on assets acquired in the month of purchase. k. The balance sheet as at December 31, 2021 is as follows: Assets: Cash Accounts Receivable Inventory: Raw materials Plant and Equipment Less accumulated depreciation Total assets Liabilities and Equity: Accounts Payable 6% Long term notes payable Common Shares Retained Earnings Total liabilities and shareholders' equity Finished goods $20,250 82,500 1,000,000 (100,000) $80,000 663,000 Additional information is as follows: All cash payments except purchases of raw materials are made monthly as incurred. All borrowings occur at the beginning of each month, and all repayments occur at the end of the month. Borrowings and repayments may occur in any amount. All interest on borrowed funds is paid at the end of each month at a rate of 0.5% per month. A minimum cash balance of $15,000 is required at the end of each month. 102,750 900,000 $1,745,750 $60,000 900,000 735,000 50,750 $1,745,750 Required part A: 1. Prepare in an excel spreadsheet the following budgets for each of the three months of 2022 (at end of this document you can find templates to assist you; note of course that you will need to re-create them in your excel spreadsheet): a. Sales budget. b. Production budget. c. Raw materials purchases budget. d. Direct labour and manufacturing overhead budget. e. Selling and administrative budget f. Cash budget 2. Prepare a budgeted contribution format income statement for each of the first three months of 2022 and a budgeted balance sheet as at March 31, 2022. Required part B: Transcona Manufacturing has used essentially the same budget parameters for the last four years of operations. The company was able to maintain operations throughout 2021. Sales were robust, although production volumes were lower than budgeted because of high worker absenteeism due to COVID-19 outbreaks. The company has found that significant deviations between actual and budgeted results are becoming more and more frequent. Looking ahead to the budget for 2022 for Transcona Manufacturing, what budget assumptions would you recommend the company reconsider, and why? How do you think they could affect the budget and cash flow of the company? (It is not necessary to make up new assumptions and/or re-do the budget: we just want you to consider WHICH items may need attention). a. Sales Budget Sales in units Unit sales price Sales in dollars b. Production Budget Sales in units Add: Desired ending finished goods inventory Finished goods requirements Less: Beginning finished goods inventory Production requirements January January February February March March c. Raw materials purchases budget Units of production required Units of raw materials required (5 kg per unit) Add Desired ending inventory Total requirements Less Beginning inventory Raw materials to purchase: In Units (kilograms) In Dollars d. Direct labour and Overhead budget Units of production required Direct labour hours required (1.25 per unit) Direct labour cost ($16/hr) Manufacturing overhead: Variable (50% of DL) Fixed Total manufacturing overhead Total cost of direct labour and manufacturing overhead Less non cash portion (eg. Depn) Cash outlay for DL and MOH January January February February March March e. Selling & administrative budget Sales in units Variable: Shipping ($4 per unit) Sales commissions (10% of sales) Total variable s&a Fixed selling and admin Less non cash portion (eg. Depn) Cash outlay for fixed selling and admin January February March Budget Assignment Part 1 I Transcona Manufacturing operates in Winnipeg and produces and distributes a special type of chemical compound called Compound WX which is an essential product used in home construction. The information below about Transcona's operations has been assembled to assist budget preparation. The company is preparing its master budget for the first quarter of 2022. The budget will detail each month's activity and the activity for the quarter in total. The master budget will be based on the following information: a. Selling price is $65 per unit in 2021 and will not change for the first two quarters of 2022. Actual and estimated sales are as follows: Actual 2021 November December 10,000 units 12,000 units Estimated 2022 January February March April May b. The company produces enough units each month to meet that month's sales plus a desired inventory level equal to 20% of next month's estimated sales. Finished goods inventory at the end of 2021 consisted of 2,200 units at a variable cost of $37.50 each. 11,000 units 10,000 units 13,000 units 11,000 units 11,000 units c. The company purchases enough raw materials each month for the current month's production requirement and 25% of next month's production requirements. Each unit of product requires 5 kilograms of raw material at $1.50 per kilogram, which are purchased from suppliers overseas. There were 13,500 kilograms of raw materials in inventory at the end of 2021. Transcona pays 40% of raw material purchases in the month of purchase and the remaining 60% in the following month. d. Each unit of finished product requires 1.25 tabour-hours. The average wage rate is $16 per hour. e. Variable manufacturing overhead is 50% of direct labour cost. f. Credit sales are 60% of total sales. Transcona collects 50% of the credit sales during the first month following the month of sale and 50% during the second month. Fixed overhead costs (per month) are as follows: 8. Factory Supervisor's salary Factory Insurance Factory rent Depreciation of factory equipment Advertising Depreciation Insurance Salaries Other $75,000 1,400 8,000 1,200 h. Total fixed selling and administrative expenses per month are as follows: $300 9,000 250 4,000 14,550 i. Variable selling and administrative expenses consist of $4 for shipping and 10% of sales for commissions. j. The company will acquire assets on January 2 for use in the sales office at a cost of $200,000, which will be paid at the end of January 2022. The monthly depreciation expenses on the additional capital assets will be $6,000. Transcona records a full month of depreciation on assets acquired in the month of purchase. k. The balance sheet as at December 31, 2021 is as follows: Assets: Cash Accounts Receivable Inventory: Raw materials Plant and Equipment Less accumulated depreciation Total assets Liabilities and Equity: Accounts Payable 6% Long term notes payable Common Shares Retained Earnings Total liabilities and shareholders' equity Finished goods $20,250 82,500 1,000,000 (100,000) $80,000 663,000 Additional information is as follows: All cash payments except purchases of raw materials are made monthly as incurred. All borrowings occur at the beginning of each month, and all repayments occur at the end of the month. Borrowings and repayments may occur in any amount. All interest on borrowed funds is paid at the end of each month at a rate of 0.5% per month. A minimum cash balance of $15,000 is required at the end of each month. 102,750 900,000 $1,745,750 $60,000 900,000 735,000 50,750 $1,745,750 Required part A: 1. Prepare in an excel spreadsheet the following budgets for each of the three months of 2022 (at end of this document you can find templates to assist you; note of course that you will need to re-create them in your excel spreadsheet): a. Sales budget. b. Production budget. c. Raw materials purchases budget. d. Direct labour and manufacturing overhead budget. e. Selling and administrative budget f. Cash budget 2. Prepare a budgeted contribution format income statement for each of the first three months of 2022 and a budgeted balance sheet as at March 31, 2022. Required part B: Transcona Manufacturing has used essentially the same budget parameters for the last four years of operations. The company was able to maintain operations throughout 2021. Sales were robust, although production volumes were lower than budgeted because of high worker absenteeism due to COVID-19 outbreaks. The company has found that significant deviations between actual and budgeted results are becoming more and more frequent. Looking ahead to the budget for 2022 for Transcona Manufacturing, what budget assumptions would you recommend the company reconsider, and why? How do you think they could affect the budget and cash flow of the company? (It is not necessary to make up new assumptions and/or re-do the budget: we just want you to consider WHICH items may need attention). a. Sales Budget Sales in units Unit sales price Sales in dollars b. Production Budget Sales in units Add: Desired ending finished goods inventory Finished goods requirements Less: Beginning finished goods inventory Production requirements January January February February March March c. Raw materials purchases budget Units of production required Units of raw materials required (5 kg per unit) Add Desired ending inventory Total requirements Less Beginning inventory Raw materials to purchase: In Units (kilograms) In Dollars d. Direct labour and Overhead budget Units of production required Direct labour hours required (1.25 per unit) Direct labour cost ($16/hr) Manufacturing overhead: Variable (50% of DL) Fixed Total manufacturing overhead Total cost of direct labour and manufacturing overhead Less non cash portion (eg. Depn) Cash outlay for DL and MOH January January February February March March e. Selling & administrative budget Sales in units Variable: Shipping ($4 per unit) Sales commissions (10% of sales) Total variable s&a Fixed selling and admin Less non cash portion (eg. Depn) Cash outlay for fixed selling and admin January February March