Prepare in an excel spreadsheet the cash budget 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)

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: Rotate counterclockwise 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 labour-hours. The average wage rate is $16 per hour. 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. e. C. 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. 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 labour-hours. The average wage rate is $16 per hour. 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. g 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 e. h. Total fixed selling and administrative expenses per month are as follows: Advertising $300 Depreciation 9,000 Insurance 250 Salaries 4,000 Other 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 d Rotate counterclockwise 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. f. Cash budget January February March Quarter Cash balance, beginning Operating cash receipts: Cash sales From previous month's sales From 2nd previous month's sales Total Operating cash payments: Raw Materials Purchases: Current month Previous month Direct labour and MOH Variable selling and admin. Fixed selling and admin. Purchase of equipment Total Net operating cash inflows (outflows) Interest on notes payable Excess (deficiency) of cash available Borrowing Repayment Interest on bank loan Ending balance