It is time for Northland Corp. to begin its annual budget preparation for its upcoming fiscal year, which ends on December 31, 2022. They are in need of temporary assistance in their accounting office, and have hired you to carry out the task of preparing the budgets. Northland prepares monthly budgets, and then from this information prepares the monthly budget and an annual budget. You have been asked to begin the budget process by preparing the budget for the first three months of 2022, including the monthly budget. To assist you in this task, Northland has gathered the following information:

1) Sales for January 2022 are expected to be 7,000 units, and sales are expected to grow at a rate of 10% of the previous month's sales for all of 2022. Selling price is expected to remain constant at $50 per unit.

2) Based on past experience, it is expected that 30% of sales will be collected in the month the sale is made, 69% will be collected in the month following sale, and 1% will never be collected. Accounts receivable are expected to be $160,000 at the start of the first month in 2022; the expected bad debts on these amounts have already been recorded.

3) In order to meet expected demand at the beginning of the next month, Northland wants ending inventory equal to 25% of the next month's budgeted sales. Inventory on hand at the end of 2021 (the start of the first month for 2022) is expected to be 1,500 units.

4) Northland is a wholesaler, and purchases its inventory from the manufacturer at a cost of $32 per unit. This cost is unchanged from the last quarter of 2021, and is expected to remain constant throughout the next year.

5) Northland purchases from the manufacturer on account, and has three weeks to pay.Accordingly, Northland expects to pay 60% of its inventory purchases in the month of purchase, 25% in the month following purchase, and the remaining 15% in the second month following the month of purchase. Northland anticipates owing $102,000 to suppliers as of December 31, 2021. Of this, $22,000 is the amount owing from November purchases, and $80,000 is outstanding from December purchases.

6) Northland incurs selling and administrative costs as follows:

i) Northland pays its selling staff fixed salaries on a commission basis, at a rate of 8% of sales revenue. Commissions are paid in full in the month the costs are incurred.

ii) Advertising costs are fixed at $3,100 per month and are paid in cash.

iii) Office salaries are expected to be $21,000 per month, again paid in cash each month.

iv) Property taxes for 2022 are expected to be $18,000; payment is due February 28, and Northland intends to pay the entire year's assessment during that month. The 2021 property taxes were paid in full in 2021.

v) Utilities are expected to be constant at $3,800 per month.

vi) The annual insurance premium is expected to be $3,000 and will be paid March 1 for a policy ending February 28, 2023. The insurance premium paid in 2021 for coverage during the first two months of 2022 is $450.

vii) The annual audit is carried out in February, and the expected cost is $2,600, which will be paid in full in March.

7) Northland expects to have an opening cash balance of $4,500 at the start of the first month of 2022.

8) Northland has decided that it must maintain a minimum cash balance of $4,000. It has arranged a line of credit with the bank, but must borrow in $1,000 increments and make repayments in $1,000 increments as well. New borrowing occurs on the first day of the month the money is needed; repayments occur at the end of the month that excess cash is available. Interest expense is calculated at a rate of 6% per year and is paid each month even if the principal has not be repaid. Northland's policy is to make payments on the line of credit as soon as cash is available, as long as a minimum balance of $4,000 is maintained.There will be no outstanding balance on the line of credit at the beginning of 2022.

9) Northland has common shares with a book value of $150,000 outstanding at the end of 2021. It does not intend to issue or repurchase any common shares during 2022. The retained earnings is expected to be $90,950 at the end of 2021. Dividends of $15,000 are to be declared and paid in March.

10) Northland expects to have land with a cost of $50,000 plus buildings and equipment with a net book value of $80,000 at the end of 2021.Equipment purchases are expected to be $12,000 in January, and $9,000 in February. Budgeted depreciation is $1,200 for January and $1,320 for each subsequent month.

The income tax rate paid by Northland is 20% of accounting net income. Monthly instalments for 2022 will be $2,000. The 2021 balance owing will be fully paid in December 2021.

Note: Use of a spreadsheet program on the computer is required. Partial units cannot be sold or produced; please round up to the nearest whole unit using the @round function or manually retyping the whole number. Please also round to the nearest dollar in your calculations. A template has been provided which is formatted for zero decimal places.

60 Required:

1) Using a four-column format, with one column for each month and then one column for the first quarter:

a) Prepare the sales budget.

b) Prepare the schedule of expected cash collections

c) Prepare the inventory purchases budget in units, and in dollars.

d) Prepare the schedule of expected cash disbursements - inventory purchases

e) Prepare the schedule of expected cash disbursements - operating expenses

f) Prepare the cash budget.

g) Prepare the budgeted income statement for the quarter ended March 31, 2022. You do not need to worry about the monthly income statements!

h) Prepare the budgeted balance sheet as at March 31, 2022 and December 31, 2021 together.Make sure the December 31, 2021 balance sheet balances, or December 2022 will not.

There are marks allocated for using linkages and formulas within Excel.

2. Although a firm uses a master budget for planning purposes, it also prepares a flexible budget.

Required: Copy the budget that you prepared for question 1, and paste it onto a new spreadsheet.I showed in class how to do this, so check the video if you cannot remember.

10 a)Identify four assumptions you made while preparing the master budget that may not represent what actually happens in the first three months of 2022. Change these assumptions. Every student should have a different answer!!! Explain your new assumptions and adjust the budget to reflect these changes. Make sure you go back and adjust the Cash Budget, specifically the borrowings and repayments, as these must be done manually.

Sales Budget	Jan. 2022	Feb. 2022	Mar. 2022	Quarter
Sales in units	7,000	7,700	8,470	23,170
Selling price / unit	50	50	50	50
Sales revenue in dollars	350,000	385,000	423,500	1,158,500
Schedule of Cash Collections	Jan. 2022	Feb. 2022	Mar. 2022	Quarter
November sales collected
December sales collected	160,000			160,000
January sales collected	105,000	241,500		346,500
February sales collected		115,500	265,650	381,150
March sales collected			127,050	127,050
Total cash collected	265,000	357,000	392,700	1,014,700
(feel free to reformat for alternate approach)
Purchases Budget	Jan. 2022	Feb. 2022	Mar. 2022	Quarter
Budgeted sales in units	7,700	8,470	9,317	25,487
Desired ending inventory	1,925	2,118	2,329	6,372
Total units needed
Beginning inventory	1,500	1,925	2,118
Required purchases in units
Purchase price per unit	32	32	32	32
Required purchases in dollars
Cash Disbursements Schedule	Jan. 2022	Feb. 2022	Mar. 2022	Quarter
for inventory purchases
November purchases paid
December purchases paid
January purchases paid
February purchases paid
March purchases paid
Total cash disbursements
Selling & Administrative Expense Budget	Jan. 2022	Feb. 2022	Mar. 2022	Quarter
Advertising
Bad debts
Depreciation
Insurance
Professional fees
Property taxes
Salaries
Sales commissions
Utilities
Total selling & admin expenses
Adjustments to determine cash outflows
Depreciation
Bad debt expense
Property taxes
Proprty taxes paid
Insurance
Insurance paid
Cash disbursements for selling & admin
Cash Budget	Jan. 2022	Feb. 2022	Mar. 2022	Quarter
Opening cash balance
Add receipts:
Cash collections from customers
Total cash available, before financing
Less disbursements:
For inventory purchases
Selling and administrative expenses
Equiment purchases
Dividends
Income tax instalments
Total disbursements
Excess (deficiency) of cash available over disbursements, before financing
Financing:
New borrowings
Repayments
Interest expense
Total financing
Cash balance, ending
Statement of income				Quarter
Sales revenue
Opening inventory
Purchases
Cost of goods available for sale
Ending inventory
Cost of goods sold
Gross profit
Selling and admin expenses
Operating income
Interest expense
Net income before income tax
Incom tax expense
Net income
Statement of Changes in Equity - Retained Earnings portion
Retained earnings, December 31, 2021
Net income
Subtotal
Dividends declared
Retained earnings, March 31, 2022
Balance Sheet		2022	2021
Cash
Accounts receivable
Inventory
Prepaid insurance
Prepaid properly taxes
Land
Buildings and equipment, net
Accounts payable
Bank loan payable
Income tax payable
Common shares
Retained earnings