You are a CPA student working in the accounting department of Reynolds Liquor Distributors Ltd. (RLDL) for the year ended December 31, 2019. Your role requires you to prepare and analyze the annual financial statements of RLDL and prepare a commentary on the performance and financial position of the company for the current year.

RLDL is a wholesaler that purchases a wide variety of liquors and spirits from small distilleries in Canada, the US and Europe. It distributes its inventory to bars, restaurants, clubs and liquor stores in Alberta. RLDL was started by siblings Peter, Paul and Mary Reynolds in Alberta in 2010 and they own 90% of the common shares, shared equally. Since its founding, the company has negotiated sales contracts with the major retail liquor and restaurant chains in Alberta. Sales have increased steadily each year and the company appears quite profitable.

On January 2, 2018, the company expanded its operations by acquiring a state-of-the-art distribution warehouse in Okotoks for $5 million. Previously, the company rented a small warehouse in Nanton. The expansion was financed in part by issuing bonds totaling $3,000,000 par value to private investors on January 2nd, 2018. The bonds have a coupon rate of 8% and mature in 10 years. The investors? expected rate of return for this business was 9%. Interest payments for the bond are made quarterly on January 1, April 1, July 1 and October 1 each year. The remaining $2,000,000 of the acquisition price was financed by issuing 100,000 4% non-cumulative convertible preferred shares to private investors also on January 2, 2018.

RLDL needed additional funds in order to purchase new warehouse equipment and finance day-to-day operations. On July 1, 2019, RLDL borrowed $800,000 from BCR Bank. This installment loan has a 2 year term and carries interest at 7%. The terms of the loan provide for quarterly fixed principal payments plus interest, commencing on October 1, 2019. The bank may demand payment of the loan in full if the current ratio falls below 2.4 to 1.

In early July 2019, the owners hired a new sales manager, Dave Edwards, to spearhead a sales drive into British Columbia (BC). As of December 31, 2019 he has been successful in adding $950,000 to the company?s sales revenues from his work in BC. The sales target was $540,000 for the six months ended December 31, 2019 so management is very pleased with his performance. Dave receives remuneration as follows: a monthly base salary; every three months he also receives a bonus of 10% of total sales in British Columbia for the quarter. He was paid $30,000 in October 2019 for July, August and September sales. As of December 31, 2019, Dave had not yet received his bonus for October, November and December 2019 sales nor has it been accrued. Internal reports show that Alberta 2019 total sales and gross profit numbers were more or less identical to 2018. The gross profit margin for BC sales was 35% for the six months ended December 31, 2019.

The fourth sibling, Sam Reynolds, does not participate in the day-to-day running of the business and owns 10% of the common shares. In September, 2019, there was a disagreement among the four owners over the future direction of the company. Peter felt that RLDL should expand further, going into Saskatchewan and Manitoba in order to take advantage of economies of scale. Paul and Mary disagreed, preferring to build on the business in Alberta and BC. To finance this expansion, they wanted to issue 10,000 new shares to an outside investor. The discussion got so heated that Sam decided to leave the business rather than take sides. He refused to sell his shares to an outsider and insisted that the company repurchase and cancel them. On 15 October 2019 he was paid $60,000 cash for his shares. This was recorded as

DR Sam's Repurchased Shares Account $60,000

CR Cash $60,000

After a heated discussion about capital management, the Directors (Peter, Paul and Mary) declared the following dividends on December 24, 2019: common share dividend of $2 per share, to be paid January 5, 2020; full year preferred share dividend to be paid January 20, 2020. Peter thought that they should not declare any dividends and keep the cash in the business for the proposed expansion. However Mary is planning a round the world trip and needs the cash. Paul sided with Mary and the resolution passed. The shareholders have not yet given the dividend resolution to you to record.

The Income Statement, Balance Sheet and some brief ratio information for the 2018 financial year are provided in Exhibit 2 on pages 7-8. A soft copy Excel file of these statements has also been provided on Blackboard.

The?unadjusted trial balance as at December 31,?2019?is provided on an Excel spreadsheet sni

INSTRUCTIONS AND REQUIREMENTS (PLEASE READ CAREFULLY)

PLEASE NOTE: Efficient completion of this assignment can depend on how well you work as a team including the level of individual effort and careful delegation of tasks to?each?team member.

PART A ? SPREADSHEET AND WORD DOCUMENT SETUP AND SHEET LABELLING

REQUIRED:

1. Label Sheet 1 ?Worksheet? and prepare a worksheet using the format already shown on the Excel spreadsheet.
2. Label Sheet 2 ?Journal Entries?, and?based on the information in the case and the additional information provided?record the appropriate?adjusting and correcting?entries in general journal format. Additional information is provided in Exhibit 1 on page 6.
3. Label Sheet 3 ?Financial Statements? and in this sheet you will prepare, using proper format, the 2019 Income Statement, Statement of Retained Earnings, Statement of Financial Position (Balance Sheet) and Statement of Cash Flows using the?indirect?method. You will present the 2018 comparatives with the 2019 income statement and balance sheet.
4. Label Sheet 4 ?Financial Statement Analysis calculations and workings?. Here you will present the vertical and horizontal analysis, the entire set of ratio calculations and any other relevant calculations used in your analysis.
5. Create an MS?Word file?and save under the same name as your Excel file. In it you will prepare a memo to the RLDL shareholders critically evaluating: (a) the key performance indicators of RLDL in 2019 compared to 2018 and (b) RLDL?s financial position as at December 31, 2019

PART B - ACCOUNTS PREPARATION

REQUIRED:

1. In the ?Worksheet? tab prepare a worksheet using the format already shown on the Excel spreadsheet. Review format of a worksheet in the ?Banff Corporation? activity and other materials on Blackboard from earlier in the semester.

When preparing your worksheet,?link directly to your adjusting journal entries?so that if later you need to correct an entry, Excel will automatically correct your worksheet.

Your worksheet should include income statement and balance sheet columns.

2. In the ?Journal Entries? tab record the appropriate?adjusting and correcting?entries in general journal format based on the information in the case and the additional information provided in Exhibit 1.?Show all relevant calculations on the same sheet close to the journal entries.
3. In the ?Financial Statements? tab you will prepare the 2019 Income Statement, Statement of Retained Earnings, Statement of Financial Position (Balance Sheet) and Statement of Cash Flows using the?indirect?method. Link the numbers in your Income Statement, Statement of Retained Earnings and Balance Sheet directly to the appropriate columns in the worksheet.?Don?t forget that your 2019 income statement and balance sheet should also present 2018 comparatives.

1. Calculate the liquidity, profitability and solvency ratios for 2019?that you consider relevant, along with 2018 comparatives. Make sure that calculations are consistent from year to year.

Where textbook formulas use averages, you should use year-end amounts only.

At a minimum you should present the following ratios for each year:

• Current ratio, receivables collections days, days sales in inventory, times interest earned, gross profit margin, profit margin

2. Prepare a vertical and horizontal analysis of the Income Statement and Balance Sheet.

NOTE: a maximum of THREE ratios per category will be graded.

4. In an MS Word file?prepare a memo to the shareholders of RLDL,?critically evaluating: (a) the performance of RLDL in 2019 compared to 2018 and (b) RLDL?s financial position as at December 31, 2019 (challenges and recommendations). Your memo?should include?or be supported by:

• Reference to case facts, the financial statements for 2019 and 2018 (income statement, statement of retained earnings, balance sheet and statement of cash flows), your ratio analysis and any other analysis calculations you consider relevant.
• Relevant headings, including, but not restricted to, profitability, liquidity, solvency, etc.
• A brief comparison of the key 2019 ratios to the prior year, providing?possible explanations?for the important variances where deemed relevant. You should indicate whether ratios have?worsened?or?improved.
• Appropriate analysis and?recommendations?to the shareholders as to how they can improve the company?s financial position.
• Brief reference to the?limitations?of your analysis and
• A brief description of?any additional information?which would have been useful to you in furthering your analysis.?

Transcribed Image Text:

EXHIBIT 2 The Income Statement and Balance Sheet for December 31, 2018 and selected 2018 ratios. Sales Cost of goods sold Gross Profit Reynolds Liquor Distributors Ltd. Income Statement Year ended December 31 Operating Expenses Sales and warehouse salaries expense Distribution & warehouse expenses Rent expense Insurance expense Office and administrative Depreciation expense Operating Profit Other expenses Interest expense Profit before tax Income tax expense Profit Ratio Analysis 2018 (partial) Current ratio Inventory turnover ratio days inventory (using year end values) Accounts receivable turnover days to collect (using year end values) Gross Profit margin $ $ 3.50 to 1 7.68 48 12.00 30.42 2018 5,700,000 (3,135,000) 2,565,000 45.0% 490,000 658,000 175,000 14,400 150,000 46,700 1,534,100 1,030,900 (252,741) 778,159 (116,724) 661,435 Reynolds Liquor Distributors Ltd. Balance Sheet as at December 31, Current Assets Cash Accounts receivable Inventory Prepaid insurance Non- Current Assets Buildings Accumulated depreciation Warehouse equipment Accumulated depreciation Office and storage equipment Accumulated depreciation Total Assets Current Liabilities Accounts payable Income tax payable Interest payable Non Current Liabilities Bonds payable Total Liabilities Shareholders' equity Common shares (40,000 shares) Preferred shares (100,000 shares) Retained earnings Total liabilities & shareholders' equity 5,000,000 (21,000) 545,000 (154,200) 250,000 (75,500) $ $ $ $ 2018 270,200 475,000 408,000 8,200 1,161,400 4,979,000 390,800 174,500 5,544,300 6,705,700 242,450 29,440 60,000 331,890 2,816,290 3,148,180 100,000 2,000,000 1,457,520 3,557,520 6,705,700 EXHIBIT 2 The Income Statement and Balance Sheet for December 31, 2018 and selected 2018 ratios. Sales Cost of goods sold Gross Profit Reynolds Liquor Distributors Ltd. Income Statement Year ended December 31 Operating Expenses Sales and warehouse salaries expense Distribution & warehouse expenses Rent expense Insurance expense Office and administrative Depreciation expense Operating Profit Other expenses Interest expense Profit before tax Income tax expense Profit Ratio Analysis 2018 (partial) Current ratio Inventory turnover ratio days inventory (using year end values) Accounts receivable turnover days to collect (using year end values) Gross Profit margin $ $ 3.50 to 1 7.68 48 12.00 30.42 2018 5,700,000 (3,135,000) 2,565,000 45.0% 490,000 658,000 175,000 14,400 150,000 46,700 1,534,100 1,030,900 (252,741) 778,159 (116,724) 661,435 Reynolds Liquor Distributors Ltd. Balance Sheet as at December 31, Current Assets Cash Accounts receivable Inventory Prepaid insurance Non- Current Assets Buildings Accumulated depreciation Warehouse equipment Accumulated depreciation Office and storage equipment Accumulated depreciation Total Assets Current Liabilities Accounts payable Income tax payable Interest payable Non Current Liabilities Bonds payable Total Liabilities Shareholders' equity Common shares (40,000 shares) Preferred shares (100,000 shares) Retained earnings Total liabilities & shareholders' equity 5,000,000 (21,000) 545,000 (154,200) 250,000 (75,500) $ $ $ $ 2018 270,200 475,000 408,000 8,200 1,161,400 4,979,000 390,800 174,500 5,544,300 6,705,700 242,450 29,440 60,000 331,890 2,816,290 3,148,180 100,000 2,000,000 1,457,520 3,557,520 6,705,700

Answer rating: 100% (QA)

I will provide a stepbystep guide on how to set up the spreadsheet and what information to include in the memo PART A SPREADSHEET AND WORD DOCUMENT SETUP AND SHEET LABELING 1 Spreadsheet Setup Label S... View the full answer