ACFI3009 CONTEMPORARY ACCOUNTING ISSUES 2:

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 company’s 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:

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

   2. Add a column that contains the year extracted from the “Date”.

   3. Add a column that contains the month number extracted from “Date”.

   4. 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:

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

   2. An insight into OneOffice’s most popular products and those products which generate the most profit;

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

   4. Who are OneOffice’s 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);

   5. 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 OneOffice’s 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:

   1. Year

   2. Month

   3. Country

   4. State

   5. Product Category

   6. Sub Category

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

7. You need to submit the file containing your worksheet and dashboard to Canvas for the head of sales (Simon) to look at. Please use the Excel Files – Assessment Task 1 folder on Canvas and please make sure that your filename is as follows ‐ "ACFI3009 ‐ 2022 T3 ‐ Name Student number".