Work to be done - dasdasdada PDF

Preview

Summary

dasdasdada...

Description

Dashbaord is able to give a collection of data on a single page and gives a brief snapshot of what is to be known with a simple glance. An interactive dashboard is more helpful as the user can change some of the key variables by themselves and get the data that they require or need rather than having to rely on others to provide them with the information. The dashboard is also able to present a large amount of data in a small space and allows the user to access a large amount of data without a lot of clutter and chaos. They can view only the relevant data that they require rather than having to sift through large charts and graphs. The data that is being accessed can be made relevant to the requirements of the user and it can provide an analysis of trend and direction in which the trend is going over time.

Instruction Only consider the sales data for 2019 which means that sales that were invoiced in 2019 are considered to be sales for that period. In order to do that, the invoices from 1st January 2019 till 31st December 2019 are taken into consideration while the sales before and after that period are taken away. This is dobe b first sorting the data in accordance to invoice date and then the sales before and after 2019 are excluded. Inserting pivot table Once this has been done, the first task is to calculate the sales

Interactive dashboard (with a drop down menu) Arrows for conditional formatting and show a trend Sparklines to show trends Monthly sales, in both dollars and quantity Percentage (or proportion) of total revenue contributed by each customer Top two best-performing products as measured by gross profit Average days outstanding 2019 total sales (by customer) (a validation rule for Column A, i.e. ‘Customer name’) The rule should require the valid customer name to be entered, according to the list of customers presented in the original data sheet. Print Preview Conditional formatting data bars or icons Sparklines

Slicers Excel 2016 Form controls/Active X controls Save your file as ACC_ACF2400__Dashboard.xlsx. The completed assignment must contain the following 9 worksheets:  Instruction (see Note 1 below)  Report (see Note 2 below)  Data ABS_Group  Data ABS_Group Working Version  Calculation worksheets (see Note 3 below): o (i) Cal1_MonthlySales o (ii) Cal2_ProportionTotalRev o (iii) Cal3_Top2_Pdt_GrossProfit o (iv) Cal4_Rev_AvgDaysOut o (v) Cal5_2019Sales_Customer Notes: 1. Prepare an Instruction worksheet that explains how to use your model. Instructions should be brief. Include the following information:  Your name and name of the company  Dashboard objective: Explain the purpose of the dashboard you created and based on your analysis of the data, identify one area of business improvement as recommendation to John Galsworthy  Indicate location and explain purpose of key sales analyses for 2019 to be presented at the management meeting: o Key Analysis 1: Monthly sales, in both dollars and quantity o Key Analysis 2: Percentage (or proportion) of total revenue contributed by each customer o Key Analysis 3: Top 2 best-performing products as measured by gross profit o Key Analysis 4: Average days outstanding by customer [HINT: Average Days Outstanding is the sum of days difference between Payment Receipt Date and Invoice Date divide by the count of number of invoices] o Key Analysis 5: 2019 total sales (by customer)  Reflection on your own learning experience while working on the assignment and preparing your dashboard (700-800 words). 2. Construct an interactive professional Dashboard in the Report worksheet (see marking rubric for further details). Note: Key Analysis 5 (Validation) does not need to be included in the dashboard. [Hint: PivotCharts and Slicers are copied from respective Calculation worksheets while Form Control is generally created in the Report worksheet.] 3. Perform all the necessary data processing for each of the key analyses in the Calculation worksheets provided (see above). Data processing may include PivotTables, PivotCharts, Excel Table, Slicers, formulae, Validation and so on. You need to decide on the appropriate type of PivotCharts, Charts to use, and Slicers and Form Control to include....