Data has become one of the most important assets of modern business,

representing a new way for companies to create value for their stakeholders. The

information age requires the improvement of data management and analysis in

order for the company to be successful. Increasing reliance on data management

and analysis to support business strategies has contributed in achieving company

objectives.

The economic environment of today's businesses requires reliable and

efficient data management, coupled with the analysis of data to facilitate the

decision-making process. Data management and analysis emphasis is on what

companies can possibly contribute to the business success. Besides, it is

understandable that the duty of the accountant include managing data effectively.

The increment for data analytics are in line with the growth of the

organisation. Being the company accountant, you are consistently getting many

requests from the management team for various data analysis tasks. This intended

to facilitate the management for the better decisions making. For example,

management usually asks reports pertaining to the past performance, current

performance, and predicted trends for the future.

You and your team spend a great deal of time preparing these reports using

Microsoft Excel due to your experience and knowledge of the software. Working

alongside Microsoft Excel has helped you to evaluate and demonstrate your ability

to create creative solutions in terms of company performance. By recognizing

company's own strengths and weaknesses, it enables to boost the performance

and remain competitive in the industry.

AIS655 Advance Accounting Information System & Analysis OCTOBER 2020 - FEB 2021

3 | P a g e

You and your team are required to:

1. Create one dummy company which consists of the following data sets

(minimum 7 fields each).

Data model consist of the following data set.

i. Sales transactions (eg of fields: type, quantity) - 12 months (at least 10 Sales

transactions per month)

ii. Products specifications (eg of fields: type, cost) - at least 5 Products

iii. Customers information (eg of fields: name, phone number) - at least 10

Customers

iv. Branches information (eg of fields: name, location) - at least 3 Branches

v. Salesman information (eg of fields: name, phone number) - at least 5 Salesmen

(10 marks)

2. Based on the above data set, use the following functions and formulas:

a) Statistical Functions - At least 3 functions

b) Logical Functions - At least 3 functions

c) Lookup Functions - At least 2 functions

d) Conditional Formatting - At least 1 function

e) What If Analysis - At least 1 function

f) Pivot Table - At least 3 tables

g) Pivot Chart - At least 3 charts, compulsory 1 multiple chart

h) Slicers - At least 3

(20 marks)

3. By using the above functions and formulas, you are required to perform

the following analysis:

a) Sales Trend analysis

b) Product analysis

c) Customer analysis

d) Salesman analysis

e) Branch Analysis

f) Overall company performance

(20 marks)

4. Produce the summary of the analysis in the form of:

a) Excel Dashboard

(30 marks)

b) Report on the analysis of the company performance

(20 marks)