ACF5320 Assignment 4

ASSESSMENT TASK:     Assignment 4

WEIGHTING:                   25%

COMPLETION:                Individual

GENERATIVE AI:           Generative AI tools can be used in this assessment task

In this assessment task, you can use generative artificial intelligence (AI) to assist you in anyway. Any use of generative AI must be appropriately acknowledged (see Learn HQ).

DUE DATE:                    11:55pm, Friday 17 May, 2024

OVERVIEW

The ACF5320 Business Analytics assignment involves two comprehensive tasks that integrate data analysis with strategic business decision-making using advanced analytical tools like Power BI, Tableau, and Excel. This assignment is designed to simulate real-world business scenarios where data-driven insights are crucial for informed decision-making.

OBJECTIVES

.     Develop Advanced Analytical Skills.

.     Enhance Decision-Making Capabilities.

.     Master Data Management and Visualization.

.     Cultivate Independent Analytical Thinking.

TASK ONE: DUPONT ANALYSES

Description of Data

The Excel file (DuPont_data.xlsx) contains financial data for companies in six industry groups. The data include fiscal years 2013-2015. The companies are traded on the NASDAQ stock exchange.

Important column descriptions:

.      Industry: Industry groupings as defined by Nasdaq.com. There are 6 in the sample.

.      Name: Name of the company.

.      Net income: The final net income of the company for each fiscal year.

.      Net revenue: This represents total sales.

.      Ticker: The unique ticker code used to identify each company on the NASDAQ.

.      Total assets: The total assets at the end of the fiscal year.

.      Total shareholder equity: The total shareholder equity at the end of the fiscal year.

.     Year: The fiscal year.

TASK ONE REQUIREMENTS

Task One Instructions

1)  Load the data into Power BI/Tableau.

a. Note that there are two sheets: Balance Sheet and Income Statement. You need to load both. In case you encounter any errors in the data upon loading, remove the error at the stage of data transformation  (not manually!).

b. Create a merged table containing data from both balance and income statement sheets. This will require merging based on ticker and year. [Hint: Use Inner Join to merge the tables].

2)   Now answer the following questions by creating dashboards in separate Power BI/Tableau

sheets. Rename each sheet to correspond to the question number. For example, the name of the sheet that displays the dashboard for Question 1.1 should be 'Dashboard 1.1'.

Task One Questions

1.1. What are the total assets for all companies for all years? (you want a single total) [Hint: Use ‘Card’ for visualisation.] (0.5 marks)

1.2. How many companies are included in the file? [Hint: Use ‘Card’ for visualisation.] (0.5 marks)

1.3. Show how many companies are there in each industry. [Hint: Use ‘Table’ for visualisation.] (1 mark)

1.4. Display the total sales by industry in 2013? [Hint: Use ‘Table’ for visualisation.] (1 mark)

1.5. Which company had the most sales (when considering all sales from 2013 to 2015)? [Hint: Use ‘Table’ for visualisation.] (1 mark)

1.6. In 2015, what industries were the best and worst performing based on ROE, Profit Margin, Asset

Turnover, and Financial Leverage? Given that there are some large outliers in this data set, please use median values for this analysis. [Hint: Create new columns to calculate ROE, Profit Margin, Asset Turnover,and Financial Leverage. Use clustered bar charts.] (1.5 marks)

1.7. Determine the industry that had the most improvement in ROE from 2013 to 2015. Again, use

median values. Show all industries in your analysis. Using the other calculations that makeup ROE, what caused this industry to have the best improvement in ROE? [Hint: Create new measures to calculate improvements in ROE, Profit Margin, Asset Turnover, and Financial Leverage. Use clustered column charts.] (1.5 marks)

1.8. Show the companies within each industry that have the highest to lowest median ROE for 2015. In

other words, show the companies in descending order of median ROE, such that the dashboard user  can navigate to view companies in each industry separately. [Hint: Use Slicer to enable filtering based on industry.] (2 marks)

TASK ONE SUBMISSION REQUIREMENTS

Submit a single Power BI (.pbix) or Tableau (.twbx) file that will contain all dashboards corresponding to the 8 questions in separate sheets. Name this file as follows: A4T1_Yourlastname_YourStudentID

TASK TWO: PISCINE SEAFOOD ANALYTICS PROJECT

This individual project needs to be entirely your own work, and the Teaching Team cannot solve any problems for you or tell you what to analyse. You need to make your own determinations regarding the  most important issues to examine, the analyses you wish to complete, and the visualisations that will be most useful. Note, we expect this submitted work to be unique as it can hardly repeat one-to-one other students’ submissions unless the work is not done independently.

Description of Data (A4_Task2_Data.xlsx)

Here is a brief description of the data and the related business:

Piscine is a seller of fresh seafood. Each Sunday evening, the purchasing manager of Piscine purchases  fresh seafood directly from the fishermen who catch the seafood. There are multiple types of seafood that Piscine sells in three quality grades. 'A' represents the highest quality and most expensive seafood, while 'C' represents the lowest quality. After purchasing seafood on Sunday evening, Piscine sells this seafood  throughout the following week (Monday through Sunday) at its storefront in the city.

Piscine employs a pricing model that applies a set profit margin to each product. That is, it increments the purchase price according to a set profit margin to determine the sale price. You can ignore all taxes for your analyses.

You have Piscine's purchases and sales data for a month.

The data is stored in an Excel spreadsheet that has three sheets: Purchases, Sales, and Margin:

Purchases Sheet

.      PurchaseDate: The day seafood was purchased. Seafood is always purchased on Sunday evenings.

.      PurchaseBatch: A unique ID for a weekly batch

.      Seller: The seller of the seafood.

.     Type: Type of seafood.

.      Quality: A (high), B (medium),or C (low).

.      PurchasePrice: The price per pound for the batch.

.      Quantity: Pounds of seafood in the batch.

.      ItemNumber: A unique ID number for a type and grade.

Sales Sheet

.      SaleDate: Date of sale.

.      PurchaseBatch: Same as PurchaseBatch from Purchases sheet.

.      SaleQuantity: Pounds of seafood sold.

Margin Sheet

.      ItemNumber: Same as Purchases sheet.

.     Type: Same as Purchases sheet.

.      Quality: Same as Purchases sheet.

.      SetProfitMargin: The profit margin that the business applies to a specific seafood type and quality grade.