Scenario 1 for Assignment 1, Commercial Part: Analysing Warehouse Sales for an Australian Fashion Brand

Welcome, students, to your role as management professionals at a renowned Australian fashion brand. In this assignment, you will immerse yourselves in a real-world scenario as you take charge of analysing the sales data from the recent warehouse sales event, which featured products that did not sell during the previous season.

Your task is to demonstrate your analytical skills using Microsoft Excel, as well as your understanding of essential concepts such as revenue, margin, and margin percentage. You will also create data visualisations, including Excel charts, to showcase the sales patterns over a four-week period. These visualisations will serve as a foundation for your upcoming presentation to your reporting manager.

Scenario Details:

Your Role:

You have been appointed as a member of the management team responsible for analysing the sales data from the recent warehouse sales event. Your goal is to gain insights into the performance of the products during the four-week sales period. Ultimately, your hard work and analysis will culminate in a presentation to your reporting manager. You will have the opportunity to showcase your analytical skills and demonstrate a deep understanding of the sales data.

Assignment Tasks:

Data Analysis in Excel:

1. Begin by downloading the sales data sheet and opening the document in Excel software. The dataset includes information on products, their selling prices, quantities sold, and other relevant details.

2. Calculate the total quantities sold and revenue generated during the four weeks of the warehouse sales.

3. Compute the margin for each product. Margin is the difference between the selling price and the cost price.

4. Determine the margin percentage for each product using the formula: (Margin / Selling Price) and changing the format of your cell to reflect percentages.

5. Aggregate and summarise the data to identify the top styles and highest margins using conditional formatting. You can highlight top 3 and least 3 in both revenue and margin categories.

Excel Charts:

Create multiple Excel charts to visualise the data effectively as instructed in the excel data sheet. Consider using different chart types such as bar charts, line charts, and pie charts to represent various aspects of the data.

Overall presentation of the Analysis:

Organise your findings and charts in a clear and concise manner within the excel sheets of the file. You can introduce new sheets or use the suggested sheets (already incorporated in the data file) to organise the data while clearly labelling the sheets by using renaming skill.

Prepare notes or annotations to explain your observations and insights from the data.

Ensure that your data visualisations are well-labelled and easy to understand.

Key Takeaways:

This assignment is designed to give you hands-on experience in analysing real-world sales data, utilising Excel for data manipulation and visualisation, and preparing for a professional presentation. It is an excellent opportunity to develop your analytical and communication skills, which are vital for success in a management role.

Best of luck with your assignment, and make sure to bring your A-game to the reporting manager's meeting!

Scenario 2 for Assignment 1, Commercial Part: Improving Data Analysis in a Garment Manufacturing House

Welcome to your new role as junior planners at a garment manufacturing house, where you are entrusted with an exciting and challenging task. In this scenario, you have just joined the team and discovered that the data file provided to you is not dynamic, making it time-consuming to extract essential information. Your goal is to use Excel as a powerful tool to analyse the data and present your findings dynamically, ultimately streamlining the data analysis process for your team.

Scenario Details:

Your Role:

You have recently been hired in a junior planning role at the garment manufacturing house. You are responsible for managing and analysing data related to the garments the company produces for various designers. Your mission is to transform. the way the team accesses and interacts with data, making it more efficient and dynamic.

At the end of the month, you will participate in a team meeting that will focus on the annual performance and future projections for the range. Your prepared dynamic Excel report will be crucial in presenting the company's achievements, as well as the strategies for reaching growth targets in 2025 and 2026. Your dynamic data analysis and visualisations will play a pivotal role in guiding the discussion.

Assignment Tasks:

Data Transformation in Excel:

1. Import the provided data file into Excel.

2. Identify areas where the data analysis process can be improved. Focus on turning static data into a dynamic and interactive format.

3. Use basic Excel formulas to extract relevant information and create dynamic reporting system (use the format provided in the excel file).

4. Implement lookup formulas to quickly retrieve specific information from the data sheets.

Annual Performance Report:

Prepare a dynamic Excel report that contains the information required for 2025, 2026 and 2027 in the provided Excel File.

1. Extract and analyse growth targets mentioned in the Excel data sheet for 2026 and 2027.

2. Calculate and report dynamic projections for the upcoming years based on the provided growth targets and historical data.

3. Utilise Excel charts to visually represent the annual performance, showing trends and patterns.

Key Takeaways:

This assignment is an opportunity to enhance your data analysis skills using Excel and to demonstrate your ability to turn static data into a dynamic, interactive tool for the team. By creating a dynamic Excel report, you will significantly reduce the time required to extract essential information from data sheets and provide valuable insights for the company's future. Your work will contribute to informed decision-making and strategy development within the garment manufacturing house.

Best of luck with your assignment! Your dynamic data analysis will undoubtedly make a positive impact on the organisation's planning and decision-making processes!!

Important-

Which files are you submitting for Assignmnet 1_Commercial Part?

Excel file for Scenario 1 and 2 (excel format only)