Date

Customer ID

Customer age

Customer gender

Country

State

Product Category

Sub Category

Product ID

Product Name

Sales

Quantity

Discount

Profit

1/1/2015

CN00025711

31

F

FiJi

Mua

Office Supplies

Binders

OFF-BI-10002071

Fellowes Black Plastic Comb Bindings

12.35

7

0.7

-9.6408

1/1/2015

CN00025712

31

M

New Zealand

South Island

Office Supplies

Paper

OFF-PA-10004665

Advantus Motivational Note Cards

83.99

8

0.2

30.512

1/1/2015

CN00025710

64

F

New Zealand

South Island

Office Supplies

Paper

OFF-PA-10002586

Xerox 1970

12.1

3

0.2

4.1538

1/1/2015

CN00025712

31

M

New Zealand

South Island

Office Supplies

Binders

OFF-BI-10001617

GBC Wire Binding Combs

2.218

1

0.8

-3.2922

1/1/2015

CN00025712

31

M

New Zealand

South Island

Office Supplies

Envelopes

OFF-EN-10001990

Staple envelope

11.51

2

0

5.4592

1/1/2015

CN00025711

31

F

FiJi

Mua

Furniture

Furnishings

FUR-FU-10004963

Eldon 400 Class Desk Accessories, Black Carbon

28.15

4

0.2

7.82

1/1/2015

CN00025712

31

M

New Zealand

South Island

Office Supplies

Paper

OFF-PA-10000587

Array Parchment Paper, Assorted Colors

36.55

5

0

17.592

1/2/2015

CN00025713

31

M

United Kingdom

England

Office Supplies

Supplies

OFF-SU-10000898

Acme Hot Forged Carbon Steel Scissors with Nickel-Plated Handles, 3 7/8" Cut, 8"L

89.11

8

0.2

10.128

1/2/2015

CN00025713

31

M

United Kingdom

England

Furniture

Chairs

FUR-CH-10000225

Global Geo Office Task Chair, Gray

518.4

8

0.2

-97.056

The Scenario:

You have recently transferred into the role of Senior data analyst in OneOffice Ltd. You are stationed at their head office in Sydney. OneOffice has and continues to sell office equipment at discount prices online since 2015. OneOffice offers their products exclusively online and has no shopfronts which allows for it to save on selling costs to gain a competitive advantage. Simon is the head of sales and is interested in understanding the sales data for the products that OneOffice offers. Simon hopes to understand what customers like to purchase, as well as which products are most profitable to OneOffice, so that he can make decisions about stock volume levels, purchase discounts and marketing campaigns.

While this would have otherwise been a job for your boss, you had indicated during a previous supervision session that you are keen to take on higher responsibilities, in a bid to pave way for a promotion. As your boss in travelling abroad, he thought it an opportune time for you to complete the requested task with the assistance of the office clerk (this role in the current scenario will be played by Karen Handley and Jeremy Niass).

You have managed to extract the sales data from the companys accounting information system and have put these in an excel file (ACFI3009 2022 T3 Asst 1 Sales data base v20220903.xlsx). You decide that using Excel is the best way for you t o present the data. You are confident that the use of an Excel table will allow you to provide an appropriate interactive dash board, various pivot tables, charts, filters and slicers when presenting your information to Simon.

Requirements:

1. Using the dataset provided and at least 3 charts, 3 pivot tables, one filter and 4 slicers, create a one page DASHBOARD that summarises the sale of products year on year for Simon.

2. You will need to convert the dataset to an Excel table and then do some work on it for the analysis:

a. Format all the rows in your table to a height of 13.2.

b. Add a column that contains the year extracted from the Date.

c. Add a column that contains the month number extracted from Date.

d. Add a column that contains the month formatted in words e.g. Jan, Feb etc.

3. Simon has provided the following list of data which he is interested in viewing:

a. Information on the countries and regions which are most profitable and have the most sales activity;

b. An insight into OneOffices most popular products and those products which generate the most profit;

c. Identification of any products which may be poor performing or at risk of becoming obsolete;

d. Who are OneOffices best and worst customers are and an indication of which products they enjoy, including margins (this will help Simon to decide what products to discount);

e. A monthly analysis of sales to help Simon with stock and distribution staff levels. Simon would really appreciate it if this can also show him which products are more popular at different times of the year;

4. In addition to the above request, your discussions with Simon highlighted that he would like to extend OneOffices technology product offering. You decide to include an additional analysis regarding the technology category of products to help Simon understand what customers and stores would be ideal for the new offerings.

5. Simon would like to see the data represented in a variety of charts and pivot tables. He would also like to be able to vary the data that is on these charts using filters and or slicers to make it as interactive as possible. In particular, he would like to be able to slice the data by some of the following categories, as appropriate for the pivot tables you have selected:

a. Year

b. Month

c. Country

d. State

e. Product Category

f. Sub Category

6. Please make sure that your charts and other dashboard elements are appropriately labelled, i.e. with titles and chart labels.