Budget Assignment Part 1 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 Estimated 2022 November 10,000 units January 11,000 units December 12,000 units February 10,000 units March 13,000 units April 11,000 units | May 11,000 units 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. 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 requiress 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 labour-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: Factory Supervisor's salary $75,000 Factory Insurance 1,400 Factory rent 8,000 Depreciation of factory equipment 1,200 h. Total fixed selling and administrative expenses per month are as follows: Advertising $300 Depreciation 9,000 Insurance Salaries Other 250 4,000 14,550 1. 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 $80,000 Accounts Receivable 663,000 Inventory: Raw materials $20,250 Finished goods 82,500 102,750 Plant and Equipment 1,000,000 Less accumulated depreciation (100,000) 900,000 Total assets $1,745,750 Liabilities and Equity: Accounts Payable $60,000 6% Long term notes payable 900,000 Common Shares 735,000 Retained Earnings 50,750 Total liabilities and shareholders' equity $1,745, 750 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. . (1) Sales Budget January February March Sales Units 11000 10000 13000 Selling Price Per unit $65 $65 S65 Sales Budget 57.15,000 56,50,000 $8.45,000 (2) Production Budget January February March Budgeted Sales in units 11000 10000 13000 Add: Closing Units of Finished Goods 2000 2600 2200 Less : Opening Units of Finished Goods 2200 2000 2600 (11000*20%) Total Production of finished goods 10800 10600 12600 (3) Raw material Budget January February March Budgeted Sales in units 11000 10000 13000 Add: Closing Units of Finished Goods 2000 2600 2200 Less: Opening Units of Finished Goods 2200 2000 2600 (11000*20%) Total Production of finished goods 10800 10600 12600 For producing I unit, 5 kg of material is required Total raw material required Add: Closing unit of Raw Material Less: Opening unit of Raw Material 54000 13250 13500 53000 15750 13250 63000 13750 15750 Raw Material required 53750 55500 61000 Cost of Raw Material per unit S1.50 S1.50 SI SO Raw Material Purchase Budget 580.625 $83,250 $91,500 (4) Direct Labour Budget January February March Production of Finished Good 10800 10600 12600 For producing I unit, 1.25 labour hours is required Total Labor Hours 13500 13250 15750 Average wage rate is 168 Total direct labour Budget 52,16,000.00 $2,12,000.00 $2,52,000.00 (5) Manufacturing Overhead Cost January February March Total direct labour Budget $2,16,000 S2.12,000 $2,52,000 Variable Manufacturing Cost (50% of direct labour Cost $1,08,000 $1,06,000 S1,26,000 Fixed Overhead 585,600 S85,600 585,600 Total Manufacturing Overhead $1.93,600 $1.91,600 S2.11,600 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)