1.   Complete the Excel file templates and submit it after renaming as follows: SurnameID

(Important: Do not change the file extension)

2.    Round to the nearest dollar value. DO not use decimals.

PART A

ASSIGNMENT BRIEF

Sey Ltd is a retail company based in Auckland. The company has a subsidiary, Dor Ltd, which complements the parent business.

Son, the Group Accountant, has asked you to draft the company’s group accounts. Son has emphasised that the company has a staff code of conduct, which requires staff to treat all company information as strictly confidential. The code permits reviewing reference material, conducting research, discussing, and seeking advice about accounting procedures with others but does not allow sharing financial information with anyone except authorised staff. You observed that this sounds similar to the ethics required of you in the past for your university assignments. Son warned that breaches of this code lead to disciplinary action and immediate dismissal.

Son provides you with the following information:

Consolidation accounting policies

The consolidated financial statements incorporate the financial statements of the subsidiary (Dor Ltd) of Sey Ltd (“Parent”) as at the reporting date. Sey Ltd and its subsidiary together are referred to in these financial  statements as the “Group” or the consolidated entity. The  subsidiary is included in the consolidated financial statements using the acquisition method of consolidation. It is fully consolidated from the date on which control is transferred to the Parent.

The Group recognises non-controlling interest at its proportionate share of subsidiary net identifiable assets.

The Subsidiary, Dor Ltd

On 31 December 2017, Sey Ltd acquired 80% of the shares in Dor Ltd.

On that date, the equity of Dor Ltd comprised:

At acquisition, the book value of the assets and liabilities of Dor Ltd were considered to be at fair value, except that there were brand names (considered to be part of net identifiable assets) that had a book value of zero and where Sey assessed the fair value to be $360,000. There has been no change to assessed value of these brand names since acquisition.

Goodwill impairment

At the most recent balance date (31 December 2021), the returns from Dor Ltd were not as high as expected. The directors of Sey Ltd considered that acquired goodwill had been impaired by $252,000.

Tax and Deferred Tax

Assume a tax rate of 28% wherever relevant.

Financial statements

Income statement for year end 31 December & Balance sheet as at 31 December 2021

Sey Ltd Dor Ltd

Sales                                                                                                         9,600,000        3,120,000

Cost of goods sold                                                                                 (4,800,000)     (1,440,000)

Operating expenses (incl. Interest, Depreciation & Impairment) (960,000) (480,000)

Operating profit                                                                                        3,840,000        1,200,000

Other income (incl. Dividends, Gains & Interest)                                      696,000           48,0000

Income Tax (1,440,000) (480,000)

Net Income                                                                                               3,096,000           768,000

Opening Retained earnings

Dividends paid

Closing retained earnings

4,320,000 960,000 7,416,000        1,728,000

(2,400,000) (432,000)

5,016,000        1,296,000

Share capital 4,800,000 1,920,000

Total equity                                                                                              9,816,000        3,216,000

Accounts Payable                                                                                     2,400,000        1,080,000

Non-current liabilities                                                                              2,400,000        1,950,000

Deferred tax 2,280,000 90,000

Total liabilities                                                                                         7,080,000        3, 120,000

Total liabilities and equity 16,896,000 6,336,000

Cash                                                                                                             240,000           288,000

Accounts Receivable                                                                                   480,000           720,000

Inventory                                                                                                     480,000           720,000

Other investments                                                                                     1,920,000

Investment in Dor Ltd (at Cost)                                                               4,200,000

Plant (net) 9,576,000 4,608,000

Total assets 16,896,000 6,336,000

-----------------------------------------------------------------------------------------------------------------------

Additional information:

Son tells you that the Group uses the perpetual inventory system. The profit on sales was 30%* for Sey Ltd and 25%* for Dor Ltd for all sales transactions including inter-group transactions (* = as a percentage of sales).

The following intra-group transactions occurred during the year 2021:

1.   Sey Ltd sold inventories to Dor Ltd at a price of $600,000

2.   Dor Ltd sold inventories to Sey Ltd at a price of $300,000

3.   $60,000 remained owing by Sey Ltd at 31 December 2021 for the inventories sold to it by Dor Ltd.

4.   Sey Ltd’s inventories bought from Dor Ltd were:

a.   $120,000 at the beginning of 2021, and

b.   $60,000 at the end of 2021.

5.   Dor Ltd’s beginning and closing inventories bought from Sey Ltd for 2021 were:

a.   Beginning: $90,000, and

b.   Closing: $30,000.

6.   On 1 January 2021 Sey Ltd sold an item of plant to Dor Ltd for $435,000 when its carrying value in Sey Ltd’s accounts was $303,000 (cost of $506,000, accumulated depreciation of $203,000). This plant is assessed as having a remaining useful life of six years.

7.   The ‘other investment’ on Sey Ltd’s balance sheet is actually a 10-year loan to Dor Ltd.

8.   The terms of the 10-year loan to Dor Ltd require 5% annual interest payments. The loan was made on 1 January 2021 and interest was paid on 31 December 2021.

Required:

a.   Prepare the consolidation journal entries (include narrations) for Sey Ltd and its controlling entity for the period 31 December 2021. [Note: Provide your answers in “Sheet 1 (CJE)” of the Excel template provided]

(20 marks)

b.   Prepare the consolidated statement of profit or loss and other comprehensive income for the year ended 31 December 2021. You should consider the consolidation journal entries in “a” above. [Note: Provide your answers in “Sheet 2 (CIS)” of the Excel template provided]

(8 marks)

c.   Prepare the consolidated statement of financial position as at 31 December 2021. You should consider the consolidation journal entries in “a” above. [Note: Provide your answers in “Sheet 3 (CBS)” of the Excel template provided]

(12 marks) [TOTAL: 40 MARKS]

Part B: Financial Statement Analysis (60 marks)

PART B ASSIGNMENT INSTRUCTIONS

Objective of this assignment: Developing an analytics mindset

Having and using an analytics mindset is critical in accounting and business. This assignment focuses on developing all aspects of your analytics and critiquing mindset, namely the ability to:

•  Ask the right questions (the assignment questions illustrate what these may look like).

•  Apply appropriate data analytic techniques (Using Power BI, prepare appropriate tables and graphs).

•  Interpret and share the results with stakeholders (Prepare a report for Mr. Ye).

How to Approach this Assignment

1.       Please read all instructions before starting the assignment.

2.       This assignment requires you to complete tasks using Power BI Desktop. It is free for Microsoft Windows users. Mac users can useremote desktopwhich has Power BI installed.

3.       Attend the Power BI Zoom workshop on Wednesday, 24 August, 12.30pm – 2pm.

4.       Use the ‘Power BI Resources’ page provided on the 110.804 Stream site.

5.       Study the material on financial statement analysis to make sure you understand the concepts covered.

Before attempting the assignment

1.       Download  Power  BI  Desktop  OR  access  Power  BI  through  Massey’s  Flexible  Learning environment. See ‘Power BI Resources’ tab on Stream.

2.       Download the following file from Stream under the ‘Assessment’ tab and save it on your device:

110.804 Assignment 2 – Student Data.xlsx

3.       Access the material provided for Power BI training under the ‘Power BI Resources’ tab on Stream.

Submitting the Assignment files

1.       The assignment will result in 2 separate files: one Power BI file (.pbix) and one Word file (.docx).

2.       Submit both files in the provided submission box for Assessment 2.

3.       Follow the instructions “MBS Submitting Assessments to Stream Assignment boxes”, which is available under the Assessment tab on Stream. Stream records the submission date and time. See the Course Guide for policies re extensions and late submissions. Penalties will be applied to late submissions for which an extension has not been granted.

4.       Note that substantial marks could be lost if you fail to submit any of the required files as marks are allocated to the content of each of the files.

Naming Convention and backups

1.       Use the following naming convention for the files that you create: use your Last Name and Student ID number as the name of the file [Important: Do not change the file extension or you (and the marker) will not be able to use the file]. For example, if your Last Name is Smith and your   Student   ID   number   is   12345678;   your   files   will   be   Smith12345678.pbix   and Smith12345678.docx.

2.       Once you have started working on an assignment you should make backups. We recommend you give each backup a new version number. (This allows you to go back to earlier versions)

ASSIGNMENT BRIEF

You have been hired by the consultancy firm, Kiwi International Ltd, as a financial analyst. Your manager, Sam Ye, has asked you to analyse and compare the financial statements of three selected retail companies: Walgreens, CVS, and Best Buy. You have requested your technical support person to download financial statement data for all retail companies from 2000 – 2020 into two sheets, ‘financial’ and ‘industry  classification’ .  In the ‘financial’  sheet, there  are  columns  for the relevant  Income Statement and Balance Sheet items, as well as other market data, required for your analysis. The second sheet, ‘industry classification’, provides information relating to the Standard Industry Code (SIC), industry, and sector of each company. The two sheets are related through SIC. Load the data from the 110.804 Assignment 2 – Student Data Retail.xlsx file into Power BI.

When you inspect the dataset, you should note that your technical support person has not been consistent in the capturing of the data for ‘amortisation & depletion’, where some of the figures are coded as positive and the others as negative. Further, you should notice that while all the financials are in $000, market capitalisation is $. In your calculations of ratios, you need to take into account these variations. However, you are required to use the amounts as they are provided and apply the appropriate data transformation techniques.

PART A (Calculation and presentation of ratios using Power BI)

Required:

(i)      In Power BI Desktop, use Power Query ‘computed columns’ for profitability and market-based ratios and Dax ‘calculated columns’ for the remaining ratios in Appendix A, for the years 2000

– 2020. [Note: Use both ‘net income’ and EBITDAfor the profitability and market-based ratios. See notes under Appendix A] (12 marks)

(ii)     Present the results for each ratio, for the assigned companies as well as industry averages, in a

separate table/matrix on a separate page of your Power BI file ( .pbix) i.e., you should have one table per page. Each table/matrix should include column and/or row averages.

(10 marks)

(iii)    Create one appropriate visualisation (graph/chart) in your Power BI file for each ratio to enhance

the communication of the above results for the five companies. Present each visualisation on the

page created in (ii) above for the ratio concerned. (8 marks)

Sub-total: 30 marks

PART B (Memorandum on comparison with industry averages)

Required:

Mr Ye is particularly interested in the performance of the companies over the years in comparison with their industry. In a short memorandum (maximum 600 words), summarise the results from Part A by briefly comparing the averages for each ratio for the five companies in (ii) to the industry averages. The industry ratios are the averages of the ratios of all the companies in the dataset. Your explanation should address the following:

(iv)    What do the ratios reveal about the profitability of the companies and do ‘net income’ and

EBITDA provide different insights? (8 marks)

(v)     What  does  the  price-earnings  ratio  and  book-to-market  ratio  indicate  for  share  valuation (over/under) and/or investors’ expected growth? (6 marks)

(vi)    The efficiency of the management of accounts receivable and inventories.

(vii)   The liquidity and financial flexibility/stability of the companies.

(6 marks)

(6 marks)

(viii)  Which company shares you recommend buying (best performing) and which you recommend

selling (worst performing)? Include the reasons for your recommendations. (4 marks)

Sub-total: 30 marks

[TOTAL: 60 MARKS]

Presentation:

•       See Appendix B for a sample (image) of what a Power BI page in a report should look like for requirements (ii) and (iii).

•       Answers to Part B should be concise but should convey important information in a well-reasoned manner, supported by the data and your analysis, where appropriate.

•       Your memorandum for (iv) to (viii) should be no more than 600 words (+/- 10%).

•       You must use the template provided for the memorandum. See ‘Assessment 2’ folder under the ‘Assessment’ tab. Failure to adhere to the word limit and/or use the template provided will lead to mark deductions. The reduction of marks for exceeding the word limit would be as follows: thefirst 20 words no reduction, and then 1 mark would be deducted per 5 additional words.