Question
DATA MANAGEMENT AND ANALYSIS Data has become one of the most important assets of modern business, representing a new way for companies to create value
DATA MANAGEMENT AND ANALYSIS
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)
Step by Step Solution
There are 3 Steps involved in it
Step: 1
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-text-image.webp)
Get Instant Access with AI-Powered Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-subtext-image.webp)
Step: 3
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-subtext-image.webp)
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started