代做ACFI3425 Advanced Business Intelligence代写C/C++编程

日期：2024-03-31 06:26

Faculty of Business and Law

Assignment Brief

Module Title	Advanced Business Intelligence	Assignment Number	2
Module Code	ACFI3425	Assignment Title	CK2 Database
Module Leader	Louis Ellam	Assignment Weighting	50%

Assignment Release Date:	26/01/24
Submission Date/Time:	22/04/24 12:00pm (noon)

Assessment Information - What you need to do

This assignment is to be completed individually. This coursework requires you to import data into Excel, automate reports and utilise your experience in using power query and macros. The requirements are outlined inAppendix Aon pages 5-6 of this document. You should read all requirements before completing the assignment. You must submit an Excel Macro-Enabled Workbook (with a .xlsm file extension) using the link provided on LearningZone.  You are only permitted to make a single submission. Please email   [email protected]if there are any issues when making your submission.

Criteria for Assessment - How you will be marked

A Grade Form. showing the mark allocation is provided inAppendix Bon page 7 of this document. Further information on the University mark descriptorscan be found here. This assignment is designed to assess the following subject-specific learning outcomes: ?    Systematic understanding of the theoretical and practical basis of financial modelling. ?    Assess organisations’ reporting needs and deploy accurately a suitable financial model. ?    Critically evaluate different financial scenarios and apply scenario management to facilitate informed decision-making. ?    Apply presentation and visualisation methods for efficient and dynamic reporting.

Assessment Details The requirements are outlined in the appendices of this document. There is no word count for this assignment.

Appendix A - Requirements

Assume it is January 2024. You area business analyst working for US division of Office Staples Corporation, a leading office supply retail company. In your role as a business analyst, you have been asked to prepare a Sales Dashboard to gain further insights on how the business is performing. You have sourced the relevant data for 2019-2022 and expect to receive the data for 2023 in the upcoming week. At this stage, you are required to complete the following tasks in preparation for when the 2023 data arrives.

1.   Create a new Excel Macro-Enabled Workbook with a worksheet named ‘ Data’ and complete the following:

a)   Download the data provided on LearningZone into a new folder on your computer. Import the data into Excel as a single dynamic table.

b)   Sort the data by Order Date from oldest to newest, then by Order ID from smallest to highest, then by Product Sub-Category from A to Z.

2.   Create new worksheet named ‘Tables’ . Create the following PivotTables.

a)   Sales by Year and Product Category

b)   Sales and the Percentage of Sales by Month, filtered by Years and Product Category.

c)   Sales and the Percentage of Sales by Product Sub-Category, filtered by Years and Product Category.

All PivotTables should be in tabular form. with no grand totals shown. Create Slicers for Years   and Product Category, and link the Slicers to the PivotTables for 2b) and 2c). Ensure any items with no data are hidden on the Slicers.

3.   Create a PivotChart for each PivotTable. Ensure that you use suitable chart types. Each chart should have an appropriate title with the axes displaying the correct units. Remove gridlines and hide all field buttons for each chart.

4.   Create a macro that automates the creation of a table displaying the following statistics for

Sales by Month, using the values shown on the PivotTable for 2b). The macro should clear any existing table before creating a new one.

a)   Minimum

b)   Maximum

c)   Mean (average)

d)   Standard deviation

5.   Create a macro that automates the creation of a table displaying the highest three values for Sales by Product Sub-Category, using the values shown on the PivotTable for 2c). The macro  should clear any existing table before creating a new one.

This document is for De Montfort University use and should not be passed to third parties or posted on any website.

You are not permitted to use any Worksheet Functions for Tasks 6-8, but you are permitted to use the output produced from any of the proceeding tasks. Ensure that you integrate debugging techniques while writing your Excel VBA code.

6.   Create a macro that countshow many times the Sales by Month, as shown on the PivotTable     for 2b), exceeds the monthly average. The macro should provide a VBA message box displaying the result.

7.   Create a macro that highlights a row on the PivotTable for 2b) to show the month with the lowest Sales. Highlight the row by setting the Interior.ColorIndex property to 3.

8.   Create a macro that highlights three rows on the PivotTable for 2c) to show the three Product    Sub-Categories with the highest Sales. Highlight the rows by setting the Interior.ColorIndex to    4. The macro should include a loop that terminatesassoon as the third month with the highest Sales has been identified.

9.   Create a new worksheet named ‘ Dashboard’ . Move the Slicers and PivotCharts onto the

Dashboard. Include a copy of the PivotTables for 2b) and 2c). Create a button for the macros for 6-8. Ensure that the Dashboard is interactive. The Dashboard should be titled and

formatted appropriately.

10. Create a new worksheet named ‘Information’ that describes the overall purpose of the

Dashboard, and provides clear instructions on how the Dashboard should be updated when the 2023 data becomes available.