1. It is time for Jung So Min Corp. to begin its annual budget preparation for its upcoming fiscal year, which ends on December 31, 2021. They are in need of temporary assistance in their accounting office, and have hired you to carry out the task of preparing the budgets. Jung So Min prepares quarterly budgets, and then from this information prepares an annual budget.

To assist you in this task, Jung So Min has gathered the following information:

A) Sales for the first quarter of 2021 are expected to be 6,200 units, and sales are expected to grow at a rate of 12% of the previous quarters sales for all of 2021 and 2022. Selling price is expected to remain constant at $50 per unit.

B) Based on past experience, sales are expected to be 30% cash sales, and 70% sales on account. Jung So Min expects that 5% of the sales on account will never be collected, but the other 95% of sales on account will be collected in full in the quarter following the quarter of sale. Accounts receivable are expected to be $180,000 at the start of the first quarter in 2021 and will be collected in full.

C) In order to meet expected demand at the beginning of the next quarter, Jung So Min wants ending inventory equal to 22% of the next quarters budgeted sales. Inventory on hand at the end of 2020 (the start of the first quarter for 2021) is expected to be 1,200 units.

D) Jung So Min is a wholesaler, and purchases its inventory from the manufacturer at a cost of $36 per unit. This is the cost paid in the last quarter of 2020 and cost is expected to remain constant throughout the next five quarters.

E) Jung So Min purchases from the manufacturer on account, and has three weeks to pay. Accordingly, Jung So Min expects to pay 70% of its inventory purchases in the quarter of purchase, and the remaining 30% in the quarter following the quarter of purchase. Jung So Min anticipates owing $50,000 to suppliers at the start of the first quarter in 2021.

F) Jung So Min incurs selling and administrative costs as follows:

i) Jung So Min pays its selling staff fixed salaries plus commission. The fixed salaries total $32,000 per quarter, and the commission is 5% of sales revenue. Salaries and commissions are paid in full in the quarter the costs are incurred.

ii) Advertising costs are fixed at $6,500 per quarter and are paid in cash.

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

iv) Property taxes for 2021 are expected to be $16,000; payment is due in the third quarter and Jung So Min intends to pay the entire years assessment during that quarter.

v) Utilities are expected to be constant at $3,500 per quarter.

vi) The annual insurance premium is expected to be $4,500 and will be paid in the first quarter.

G) Jung So Min expects to have an opening cash balance of $5,200 at the start of the first quarter of 2021.

H) Jung So Min has decided that it must maintain a minimum cash balance of $5,000. It has arranged a line of credit with the bank, but must borrow in $1,000 increments. The loan must also be paid in $1,000 increments. Jung So Mins policy is to make payments on the line of credit if there is sufficient cash available, again ensuring the cash balance is not below $5,000. Interest expense is calculated at a rate of 8% per year and is paid as accrued each quarter. It is expected that the line of credit balance advanced and outstanding at the end of 2020 will be $2,000.

I) Jung So Min has common shares with a book value of $100,000 outstanding at the end of 2020. It does not intend to issue or repurchase any common shares during the year. The retained earnings is expected to be $156,200 at the end of 2020. Dividends of $15,000 are expected to be declared and paid in the fourth quarter.

J) Jung So Min expects to have fixed assets with a net book value of $85,000 at the end of 2020. Equipment purchases are expected to be $38,000 in the first quarter, and $23,000 in the fourth quarter. Depreciation is calculated on a straight-line basis. The expected useful life remaining on the assets on hand at the end of 2020 is 5 years; the new assets are expected to be used for 10 years. Jung So Min begins depreciating new assets in the quarter after they are purchased.

K) Jung So Min pays corporate tax at a rate of 25%. No instalments are planned for 2021; the balance owning will be paid in full on filing the 2021 tax return in 2022.

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. Please make sure to use the rounding function in the software and present all numbers as whole numbers. Note that financial statements NEVER present pennies.

Required: Prepare the following on a single sheet in Excel:

Using a five-column format, with one column for each quarter and then one column for the year:

a) Prepare the sales budget.

b) Prepare the inventory purchases budget in units, and then multiply the required inventory purchases by the unit cost to determine the purchases in dollars.

c) Prepare the schedule of expected cash collections

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 year ended December 31, 2021. You do not need to worry about the quarterly income statements! (Beware!! The purchases budget is in units - you need to calculate the cost of goods sold in dollars.)

h) Prepare the budgeted balance sheet as at December 31, 2021.