Learning Objectives

●   By completing this assignment, students will be able to perform data analysis and data visualisation using Excel, these include creating a structured range of data using a PivotTable, PivotChart, Excel formulas and functions. Also, students will be able to create an Excel application using macros.

●   This is an individual assignment, no group work will be permitted.

Submission Requirements

●   Your assignment should be submitted to Moodle/Turnitin.

●   The file names should contain Unit Code, assignment number and your student ID number, similar to the example: FIT1013A1_StudentID.xlsm where StudentID is your student ID.

Late Submissions:

●   The late-submission penalty is 10 percent of the available marks in that task, not the marks you        received. For instance, an assignment has 100 marks, and you submitted the assignment one day late and received 65 marks. In this case, the penalty is 10 marks deduction (10% of the total available     marks).

o    65 - (10% of 100) = 55 of 100 marks

Scenario1

SAF (Simon’s Amazon of Fashion) is a new start-up in Australia that buys designer dresses wholesale and rents them for only a fraction of the price of the dress. For example, SAF’s customers can wear an Armani gown that costs thousands of dollars for only $100. Your friend, Simon working in SAF, regularly creates reports about the business operations. Given the data file (FIT1013 A1_2022 Data.xlsx in Moodle), he would like you to use Excel functions and features to help him analyse the data and make the file more user-friendly for him in future analysis and data visualisations.

1.   Quick Analysis Using Excel Functions

To understand better about the data, you would like to do a quick analysis using Excel functions to get the No. of rentals, Sales amount in 2021 & 2022, similar to the following table. You should do this on a separate worksheet without messing up the original data.

Table 1: Quick Analysis

Due to the continuous occurrence of inflation in Australia, Simon decided to raise the rental price of all dresses in 2022 by 20%. Add a new column to reflect the price change for 2022. Design such as a way that Simon can update the percentage of price change.

Assuming all other variables are constant, what should the new percentage of price rise be, so that the total sales amount in 2022 will reach $10,000?

2.   Implement Filter and Sort

Copy the original data worksheet (Dress Rental) into a separate worksheet. With the headers from the given data file (i.e. Designer, DressName, Color, RentalPrice, Size) to allow selectively view the data dynamically, e.g. only show records from a certain year, a certain design, and so on. Also sort the data e.g.  sort by the Designer. For the selected data, show the total  Sales Amount in the last row. (For assessment purposes, implement the filter and sorting as shown in the following figure).

Figure 1: Selected and sorted data

3.   Applying Conditional Formatting

Simon wants to highlight certain values or make particular cells easy to identify on the sorted data. Based on the worksheet in task 2:

a.    Highlight the entire row in Pink if the size of the dress is greater than 10.

b.    Highlight the entire row in Green if the dress was rented in April.

4.   Create PivotTable and PivotChart

Once you have done the quick analysis on the data, you want to create a user-friendly worksheet for Simon that allows him to navigate and visualise the data easily. You will use a pivot table and a pivot chart to show his data so that he can quickly identify any trends or patterns from his data. He is not fussy about the types of charts, so you will decide that for him, but he knows what he wants to see, e.g. the number of rentals by designer, the total for each year according to dresses, etc.

First try to create separate charts to

i.   View the number of rentals by designer (e.g. use bar chart)

ii.   View the total rentals by year and dresses.

Suggest  if there  is  a better view to include all this information. You could think of presenting the information in one single PivotTable and a PivotChart that include designer, year and dress.                      To improve usability, you will create slicers that can be used to filter the data in pivot table and pivot chart.

5.   Advanced Functions

Simon also wanted to check if any of the dresses are being over rented or under rented. If a dress is being over rented, it will need to be taken for renewal and professionally cleaned. His calculation is based on the following table.

Table 2: Rental-Status table

He is asking you to create a new worksheet (similar to the following table), where he can input the         number of rentals (column 3, assuming the range is D4:D9), then a status will be shown automatically   in column 4. You need to use nested IF functions to construct an Excel formula in the Status column to  determine the status of the dress rental. These formulas can be copied to subsequent cells without           modifications. When the formula is copied to the rows with an empty record (i.e. no Number of rentals), it should show blank.

Table 3: Designer Dress Rental Status

The  VLOOKUP  function  can  be  used  to  achieve  the  above-mentioned outcome, but with a minor modification of Table 2. Create a new worksheet that consists of modified Table 2 and Table 3, and complete the formula in column 4 (Status) using the VLOOKUP function.

6.   Macros

Simon also wants the workbook to provide some automatic features based on the table in task 5.

a.    Copy the worksheet of task 5 into a separate worksheet named “Macro” .

b.    Create a button named “Create Chart” on the Marco worksheet.

c.    When  the  button  is  clicked,  a bar  chart will be automatically created on a new worksheet showing the No. of Rentals for each dress.

d.    Create a button named “Protect Sheet” on the Marco worksheet.

e.    When the button is clicked, it will create validation rules on the cell range D4:D9, so that only a number between 0 and 13 is allowed, otherwise, an error message will be displayed to the user.