. 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 months 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 months 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 years 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. Northlands 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.

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.