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 Transconas 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 months activity and the activity for the quarter in total. The master budget will be based on the following information:

1. 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

2. The company produces enough units each month to meet that months sales plus a desired inventory level equal to 20% of next months estimated sales. Finished goods inventory at the end of 2021 consisted of 2,200 units at a variable cost of $37.50 each.

3. The company purchases enough raw materials each month for the current months production requirement and 25% of next months 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.

4. Each unit of finished product requires 1.25 labour-hours. The average wage rate is $16 per hour.

5. Variable manufacturing overhead is 50% of direct labour cost.

6. 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.

7. Fixed overhead costs (per month) are as follows:

8. Total fixed selling and administrative expenses per month are as follows:

November December

10,000 units 12,000 units

January February March April May

11,000 units 10,000 units 13,000 units 11,000 units 11,000 units

Factory Supervisors salary Factory Insurance Factory rent Depreciation of factory equipment

$75,000 1,400 8,000 1,200

Advertising Depreciation Insurance Salaries Other

$300 9,000 250 4,000 14,550

9. Variable selling and administrative expenses consist of $4 for shipping and 10% of sales for commissions.

10. 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.

11. The balance sheet as at December 31, 2021 is as follows: Assets:

    Liabilities and Equity:

    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.

Cash Accounts Receivable Inventory: Raw materials Finished goods Plant and Equipment Less accumulated depreciation Total assets

$20,250 82,500 1,000,000 (100,000)

$80,000 663,000 102,750 900,000 $1,745,750

Accounts Payable 6% Long term notes payable Common Shares Retained Earnings Total liabilities and shareholders equity

$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).

1.

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

February

March

January

February

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

f. Cash budget

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

January

February

March

Quarter

Sales revenue Less variable expenses:

Cost of goods sold Selling and administrative Total variable expenses

Contribution margin Less fixed expenses:

Manufacturing overhead Selling and administrative Total fixed expenses

Operating income (loss) Less interest expense Net income (loss)

Budgeted Income Statement For January, February and March 2022

January

February

March

Quarter

Budgeted Balance Sheet As at March 31, 2022

(note: you may want to also have comparative figures for Dec 31, 2021, but its not necessary for grading)

Cash Accounts receivable Inventory: Raw materials

Finished goods Property and equipment, net

Total assets

Accounts payable Bank loan 6% Long-term Note payable Common shares Retained earnings (see below) Total liabilities and Shareholders equity

Retained earnings at March 31: Balance, January 1 Add net income Balance, March 31