PART A: POWER BI

Part of this assignment requires you to complete tasks using Power BI Desktop. Please follow this link to download the program. It is a free to use program:

Power BI Download

Download the ‘Microsoft Power BI Desktop’ version or the ‘Microsoft Power BI Mobile’ version, depending on what device you will be working on.

BEFORE ATTEMPTING THE ASSIGNMENT

1. Download Power BI as discussed above.

2. Download the following file from Stream under the Assignment 2 files folder and save it on your device:

Data 115-112 assignment (S1 2020).xlsx

3. Watch the MP4 presentation “POWER BI training”.

4. You can attempt requirements (i) and (ii) on your own without the Adobe Connect session.

Assignment brief

You have been hired by an investment firm, Happy Returns as a financial analyst and your manager has asked you to analyse the financial statements of the top seven U.S. technology companies. Your support person has downloaded the financial statements of the companies and extracted and summarised the data in a spreadsheet. In the spreadsheet there are the following sheets:

1. 2018 financial data (all seven companies)

2. 2019 financial data (all seven companies)

3. Apple (detailed income statements and balance sheets)

4. Alphabet (detailed income statements and balance sheets)

5. IBM (detailed income statements and balance sheets)

6. Intel (detailed income statements and balance sheets)

7. Microsoft (detailed income statements and balance sheets)

8. Texas Instruments (detailed income statements and balance sheets)

9. Amazon (detailed income statements and balance sheets)

In the 2018 and 2019 financial data sheets, there are columns for the relevant Income Statement and Balance Sheet items required for calculating the ratios. 

Required:

Power BI:

(i) Load the ‘2018 financial data’ sheet and ‘2019 financial data’ sheet into Power BI. Use the information in the data sheets in Power BI to calculate the following ratios for the seven companies for 2019 (See Appendix A for the formulas):

1. Profit Margin

2. Gross Profit Percentage

3. Return on Total Assets

4. Return on Equity

5. Inventory Turnover

6. Number of Days in Selling Period

7. Accounts Receivable Turnover

8. Number of Days in Collection Period

9. Current Ratio

10. Quick Ratio

11. Debt Ratio

12. Interest Coverage

13. Sales % Increase

Use the ‘New Column’ function in the ‘Data view’ in Power BI to create a new calculated column for each of the ratios.

(ii) Present the 2019 ratios in relevant tables in your Power BI file to compare the seven technology complanies.

(iii) Create appropriate visualisations (graphs) in your Power BI file for the 2019 ratios to compare the seven technology companies.

Data 115-112 assignment (S1 2020)