Ratio Analysis Data

(1000 words)

You have access to three files in the assessment area on Canvas. One is a text file (COURSEWORK FILE 1.txt) which contains sales transactions from 20 stores (this is an enormous file and will take a while to load). The second is an excel file (COURSEWORK FILE 2.xlsx) which lists the states in which these stores are located. The third is needed for Task 5.

1. You need to make use of Power BI Desktop to import the two files, and then produce a table which shows “Years” for the various products across the columns, and “Regions” down the rows, with sales showing in the body of the table, and grand totals at the bottom of each column. Set up slicers for Division, Mall developer and Region.

2. Use Power BI to produce a dashboard that shows two graphics in addition to the table as follows:

a) an analysis of revenue by region for each year

b) a map showing revenue in the different countries/regions

3. Use Power BI to produce another dashboard showing the data in a different way on a separate page (it is up to you to be imaginative).

4. Write a report to senior management which explains revenue over the last 10 years and explains how each of the regions have contributed to overall sales during those 10 years. Include graphs and tables in your report.

5. The third file (COURSEWORK RATIO ANALYSIS DATA.xlsx) is a spreadsheet showing a summary of the accounts for Bakers Ltd a retailer for the last 2 years. You need to analyse these figures by calculating key ratios and by preparing relevant graphs.

6. Produce a report which summarises the company’s performance over the two years, includes relevant graphs/charts, and highlights any causes for concern that you think need investigating. (Comment on the nature of any investigations that you think should be undertaken).