Creating a Business Dashboard

A data set is supplied (see Excel file: Apple Australia Pty Ltd data.xlsx).

link of google drive (You can click 'open with google sheets' and work in this file) :

https://drive.google.com/file/d/1EOh9iVttk8mXQ5sL7BMSoDTwjIEvvZb_/view?usp=sharing

In your calculations, you must use the data provided in the four (4) data input worksheets (Employees, Products, Actual & Budget, and Quantity Sales).

General Instructions

After a competitive selection process, you were hired as a graduate accountant at Apple Victoria Pty Ltd. As part of your new role, you will work closely with the Finance Manager, Ana Pizarro. The company has 2 shops; one located in Melbourne CBD and one in Frankston. Management is considered the option of opening another shop in Geelong in the near future. Cognisant of your Excel skills, Ana assigned you the task of designing a one-page dashboard that will be used by senior management to monitor the performance of the two retail shops. Ana asked Domenico Pelizzari (Sales Manager) and Zhihao Zhen (Human Resources Manager) to provide guidance. Domenico mentioned that, in previous dashboards, two (2) ratios1 have been included. One of those ratios, Domenico further added, was sales growth. Ana Pizarro believes that you should include two (2) ratios (including sales growth).

To help you in the task, Zhihao and Domenico provided an Excel file with four (4) worksheets, namely, Employees, Products, Actual & Budget, and Quantity Sales. Because it is the first time you creat a dashboard for the company, you decided to follow Ana's advice. In other words, you need to include two (2) ratios in your dashboard. So, you would need to investigate which additional ratio you can use with the data provided. Ana did not specify which additional ratio to use so it gives you room for creativity. So, your first task is to decide how to define performance and then choose a ratio that represents it.

Specific Requirements

You must use different worksheets which should be grouped into four (4) different categories, namely, Instructions, Data Input, Calculation, and Report. Each group of worksheets (when applicable) must be named and labelled with a different tab colour:

1)In terms of the first group (Instructions), there must be only one (1) worksheet. Aim for Count1,000 (including Internal Memorandum) (+/- 10%) in total. Your 'internal memorandum' must be copied and pasted into the 'Instructions' worksheet. In the 'Instructions' worksheet, you must include the following information:

Your name and the name of the company.

Explain the purpose of the dashboard that you created and why you chose those 4 panels that you included in your dashboard.

Indicate the location (cells and worksheet) of: a) the Sales Growth (quantity) ratio (obtained using calculated fields - see below) (for example, worksheet YYY, cells ZZZ), b) your second ratio (you must include the ratio formula and also explain why you chose that ratio), c) the budget sales revenue variance (see part 4 below), d) at least two (2) Pivot Tables used (see part 4 below).

Internal Memorandum - You MUST include an internal memorandum that will be sent to the CEO Ana Pizarro (aim for count: 700-800 ):

In your internal memo, you MUST include two (2) interesting findings that you discovered when analysing the data [HINT: You must refer to the specific location of your finding; for example, see Panel XX dashboard OR Cells XX:XX Worksheet 'aaaa', etc).

Additionally, you MUST include two (2) recommendations to Management based on your analysis of the data. As suggested above, you could prepare the internal memorandum using Word and then copy and paste the image into Excel.

Finally, you MUST include one (1) recommendation to Management in relation to the way the data was organised and stored.