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 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 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 S0% of the credit sales during the first month following the month of sale and 50% during the second month 8. 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 250 Salaries 4,000 14,550 Other 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 $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. . 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 January February March Sales in units Unit sales price Sales in dollars b. Production Budget January February March Sales in units Add: Desired ending finished goods inventory Finished goods requirements Less: Beginning finished goods inventory Production requirements c. Raw materials purchases budget January February March 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 January February March 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 e. Selling & administrative budget January February March 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