You have been hired by the consultancy firm, KDP Investments Ltd, as a financial analyst. Your manager, Andrew Jackson, asked you to analyse the financial statements of the top seven technology companies.
Assignment Task
PART A – Financial Statements Evaluation:
You have been hired by the consultancy firm, KDP Investments Ltd, as a financial analyst. Your manager, Andrew Jackson, asked you to analyse the financial statements of the top seven technology companies. You have asked your support person to download the financial statements of the companies and extract the data in a spreadsheet. In the spreadsheet there are the following sheets:
- 2021 (all seven companies)
- 2022 (all seven companies)
Required:
In Power BI:
Upload the data from the provided Excel file, ‘115.112 Data Assessment 3.xlsx’. Load the ‘2021’ and ‘2022’ sheets from this file. Use the information in the data sheets in Power BI to calculate the following ratios for the seven companies for 2022: (See Appendix A for the formulas, p. 8; Use the amounts as they are provided.):
(i)
- Profit Margin
- Gross Profit Percentage
- Return on Total Assets
- Return on Owners’ Equity
- Inventory Turnover
- Number of Days in Selling Period
- Accounts Receivable Turnover (Assume all sales are on credit)
- Number of Days in Collection Period
- Current Ratio
- Quick Ratio
- Debt Ratio
- Interest Coverage
- Sales % Increase
Tip: Use the ‘New Column’ function in the ‘Data view’ in Power BI to create a new calculated column for each of the ratios.
In the 2021 and 2022 financial data sheets, there are columns for the relevant Income Statement and Balance Sheet items required for calculating the financial analysis ratios. All amounts, for all seven companies, are presented in NZ dollars.
- Present the 2022 ratios in relevant tables in your Power BI file to compare the seven companies, using the ‘Report view’. The tables have to express profitability, efficiency, liquidity and financial gearing appropriately.
- Create appropriate graphs in your Power BI file for the 2022 ratios to compare the seven companies, using the ‘Report view’. The graphs have to express profitability, efficiency, liquidity and financial gearing
In a Word document:
- Mr Jackson further requested you to evaluate company C (writing a simple discussion) in terms of profitability by comparing the company’s 2022 results with its results for 2021. Include a comparison of the company’s 2022 results with the results of the other six companies for 2022, in terms of profitability. To justify your explanations, use relevant ratios from requirement (i) and the 2021 ratios of company C, as provided below:
PART B – Accounting Information for Māori Business:
- Discuss and compare the investment performance (Assets and net worth, Return on Assets, and Net Assest per Member) of each of two iwi, Ngāti Awa and Ngāti Pāhauwera. Include a comparison between Ngāti Awa and Ngāti Pāhauwera.
- References for requirement (v).