Scribd
Upload
260 views4 pages

AIS655 Data Management Solutions

This document describes a case study involving a trading business that has experienced rapid growth. The business lacks an effective data management strategy and relies on Excel to analyze sales and other data. The accounting department is tasked with developing a data-driven approach. They create an entity relationship diagram and relational database. Various analyses are performed on the data including sales trends, customer profiling, and employee performance. These findings are presented in an interactive Excel dashboard and report. The dashboard and analyses will help the business with forecasting, decision-making, and ensuring long-term viability.

Uploaded by

faris ikhwan
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
260 views4 pages

AIS655 Data Management Solutions

This document describes a case study involving a trading business that has experienced rapid growth. The business lacks an effective data management strategy and relies on Excel to analyze sales and other data. The accounting department is tasked with developing a data-driven approach. They create an entity relationship diagram and relational database. Various analyses are performed on the data including sales trends, customer profiling, and employee performance. These findings are presented in an interactive Excel dashboard and report. The dashboard and analyses will help the business with forecasting, decision-making, and ensuring long-term viability.

Uploaded by

faris ikhwan
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

AIS655 Accounting System Analysis & Design MARCH - JULY 2021

FACULTY OF ACCOUNTANCY
UNIVERSITI TEKNOLOGI MARA

ACCOUNTING SYSTEM
ANALYSIS & DESIGN
AIS 655

PROBLEM BASED LEARNING

SEMESTER MARCH -JULY 2021

PREPARED BY

TENGKU FAIRUZ TENGKU EMBONG


MOHAMAD RIDHUAN MAT DANGI (Ts.)
SHAZALINA MOHAMED SHUHIDAN (Dr)

REVISED BY

NORHASNAH M. SAHEH

1|P a g e
AIS655 Accounting System Analysis & Design MARCH - JULY 2021

DATA MANAGEMENT AND ANALYSIS


You and your friends, Mohd Shukri and Mohd Syafie decided to start a business after
graduating from UiTM. Mohd Shukri has been appointed as the business’ General Manager,
with Mohd Syafie in charge of Sales and Marketing. Since you have an accounting background,
the business has appointed you as the Head of Accounting department which responsible to
oversee the business’ accounts, supervise your department staff, and advice the management
to adopt recent technological resources that suitable for later business improvement strategies
and future returns. Your first business was a small trading firm that sold few products from a
rented store in Puchong. Ten employees were hired, three sales representatives, two to stock
the shelves, three checkout clerks and two to develop and maintain the electronic storefront.
The business pursued a clicks-and-bricks strategy. The products can be purchased in-store or
delivered to customers after they place an order over the phone or through the e-mail. After a
few months, the products were also available for purchase on the business’ website.

After three years in operation, the business becomes very successful, with five stores and a
popular website. However, as the business grew, Mohd Syafie and his sales team faced
significant challenges due to the large volume of data stored and they are increasingly receiving
requests for different data analyses to better estimate sales over future periods. These requests
require the team combining disparate data sets.

Mohd Syafie perceives that the business’ data requirements are rapidly expanding and that he
must devise a strategy to address this challenge, or the business will face serious growth and
compliance issues. Mohd Syafie is currently using Microsoft Excel to analyse sales data from
accounting systems and other systems that track things like social media, Internet traffic, and
many other things. He has asked you and your accounting department team to assist him in
developing a data-driven approach plan for dealing with data management and analysis of the
business.

For the initial effort, you and your accounting team department reviewed the Excel files and
reports generated by the system and discovered a lack of structure in the data model created in
Excel. You specifically advise Mohd Syafie to create effective data models because a poorly
designed data model requires hours of manual labour to maintain and refresh the reporting
mechanisms.

The data was then restructured based on what you learned about data models in your bachelor
degree programme, resulting in the Entity Relationship Diagram (ERD) shown below.

2|P a g e
AIS655 Accounting System Analysis & Design MARCH - JULY 2021

Mohd Syafie is confident that your team’s plan will help the business make better data-driven
decisions. He is eager to put these changes into action and assist the business in becoming
more proactive rather than reactive with their data.

Required:

a) Based on the ERD prepared above, design a set of relational tables to store the data.
i. Identify the tables of the entities and field/attributes in accordance with good data
model design.
ii. Identify the primary key for each table.
iii. Identify any needed foreign keys.
(10 Marks)
b) Describe the relationship between the entities in the ERD above.
(5 Marks)

c) Demonstrate the soundness of your design by entering dummy data in each table.
(at least 12 months of data)
(10 Marks)

Recently, the General Manager of the business is also requesting to implement a proper data
management and analysis to identify the business strengths and weaknesses related to the
sales and product performance, customer management and employees achievement. This is
essential to understand and detect the underlying causes of short-term changes in revenue
growth and performance of the business. Therefore, your team has been instructed again to
facilitate Mohd Syafie’s team in estimating future performance of the business, forecasting
future sales revenue, profiling customer behavior, preferences and buying habits, understanding
the sales patterns as well as performing analysis to identify the employees’ efficiency at driving
sales for the business.

This special request is needed after Mohd Syafie’s team successfully improved the data
structure, whereas you and the accounting department team has been asked to assist Mohd
Syafie to prepare a series of data analyses that highlight how the company has performed in the
past, how it is currently performing, and how it will perform in the future. In addition, the General
Manager also wants the accounting department to advice Mohd Syafie to prepare a
performance report and automate the production of these reports so that he is being provided
with up-to-date visualisations of the business at any time for future planning, coordinating and
predicting the sales performance.

Mohd Syafie knows how critical these analyses are to the success of the business performance
which can significantly improve the decision-making process. He also knows that the ways in
which the data is presented leads to decisions based on the actual data of the business. Mohd
Syafie is seeking you and department team to discuss which data should be analysed and how
the results should be presented.

You and Mohd Syafie carefully reviewed the company's data and designed a dynamic
presentation. You proposed creating a dashboard from Excel as a visual interface that provides
at-a-glance views into key measures relevant to the company's performance.

3|P a g e
AIS655 Accounting System Analysis & Design MARCH - JULY 2021

Required:

d) Based on the data set created in requirement (c) above, perform the following analysis:
i. Sales trend analysis (RM) by month.
ii. Product trend analysis (quantity) by age group.
iii. Sales analysis by branch.
iv. Customer analysis by product.
v. Salesman analysis by product.
vi. Profit analysis by branch
vii. Other relevant analysis
(25 Marks)

e) Prepare a report based on the answers from (a) to (d) above and include the following
findings.
i. The greater detail explanation of the analysis in requirement (d) above.
ii. Describe how the current economic situation affects the performance of the
company.
iii. Explain any strategic decisions made by the company to ensure its long-term viability
in the current economic environment.
iv. Identify several key highlights for the past, current and future performance of the
business from the analysis results obtained.

(To begin the preparation of the report, you, Mohd Syafie and both department teams are
required to portray the business profile and activities (e.g.: business information, products,
employees’ details, organizational levels and chart, etc.) which are relevant to be
described)
(20 Marks)

f) Create an Interactive Excel Dashboard to visualize the above analysis.


(20 Marks)

g) Based on what you have learned about data analysis and presentation, as well as your
Internet research, justify in the report the type of visualization you chose to effectively
present the data analysis from requirement (d) above in the Dashboard.
(10 Marks)

TOTAL MARKS: 100


END OF QUESTION

4|P a g e

Common questions

Powered by AI

A well-designed data model is crucial for effective reporting mechanisms as it provides a structured framework that facilitates easy data retrieval and analysis. Poorly designed data models result in excessive manual labor required to maintain and refresh reporting mechanisms, leading to inefficiencies and possible errors. A robust data model, as learned in academic programs, allows for automated, accurate, and timely reporting, which is essential for data-driven decision-making. This structured approach helps businesses anticipate trends and adapt strategies proactively .

The document proposed several methods for analyzing sales data, which include performing a sales trend analysis by month, product trend analysis by age group, sales analysis by branch, customer analysis by product, salesman analysis by product, and profit analysis by branch. Additionally, it included other relevant analyses to provide a comprehensive overview of the company's performance. These analyses were intended to highlight past, present, and projected business performance, aiding in strategic decision-making .

The proposed data-driven approach aligns with modern accounting practices by emphasizing the importance of structured, accurate data and its analysis. This approach not only supports accountability and transparency but also ensures that financial reporting is timely and aligns with organizational goals. By integrating advanced data analysis and visualization tools, accounting professionals can provide detailed insights into business operations, identify cost-saving opportunities, and effectively monitor financial health. This alignment enhances overall business performance by marrying traditional accounting with strategic data management practices .

Data visualization significantly enhances the effectiveness of performance reports by transforming complex data into easily interpretable visual formats, such as graphs and charts. This approach facilitates quicker comprehension of critical metrics, empowers decision-makers with intuitive insights, and facilitates real-time analysis across various dimensions. By using visual tools, stakeholders can promptly identify trends, outliers, and patterns, allowing for more informed discussions and strategic decisions. Furthermore, these visualizations in performance reports promote transparency and accessibility, thereby strengthening the overall communication strategy within a business .

Based on the proposed data analyses, strategic decisions could include optimizing supply chain operations, enhancing marketing campaigns, focusing on high-margin products, and tailoring customer engagement strategies. The analyses could also inform decisions on store expansions or closures, workforce optimization, and identifying new market opportunities. By utilizing trend analyses and performance metrics, businesses can align operational strategies with market demand, thereby ensuring resource efficiency and competitive advantage. These strategic decisions would support the long-term viability and growth of the business in a dynamically changing economic environment .

An interactive Excel Dashboard plays a critical role in data visualization by providing a visual interface that allows users to view and analyze key performance indicators at a glance. This type of dashboard is instrumental in transforming complex data sets into understandable and actionable insights, facilitating faster and more accurate decision-making processes. It consolidates various data analyses into a single view, which helps stakeholders monitor the business's past and current performance and forecast future trends effectively .

Data analysis facilitates a deep understanding of sales patterns and customer behavior by identifying trends, patterns, and anomalies in the sales data. Through systematic analysis, businesses can gain insights into customer preferences, buying habits, and demographic influences on product sales. This understanding allows businesses to optimize their marketing strategies, inventory management, and customer engagement initiatives. By analyzing sales trends and customer behavior, businesses can forecast future sales, tailor products to meet customer needs better, and enhance customer satisfaction .

As the business expanded, significant challenges arose, particularly related to data management due to the large volume of data generated. The sales team, led by Mohd Syafie, faced difficulties in managing this data and responding to requests for various data analyses. Recognizing the growth and compliance issues this could cause, a strategy was proposed to develop an effective data-driven approach. This involved creating structured data models, which were intended to streamline data management processes, improve efficiency, and support better decision-making. The plan included transitioning from Excel to more sophisticated data structures, like Entity Relationship Diagrams (ERD), to enhance the reporting and analytical capabilities of the company .

Identifying business strengths and weaknesses through data management and analysis is crucial for strategic planning and sustainable development. Effective data analysis reveals areas of strong performance, enabling businesses to capitalize on their existing advantages. Conversely, it also highlights weaknesses or inefficiencies that require improvement. By understanding these aspects, businesses are better equipped to make informed decisions that improve operational efficiency, strategic allocation of resources, and ultimately enhance competitive positioning in the market. This level of insight is essential for long-term business viability and success .

The business planned to transition to proactive decision-making by leveraging structured data models and implementing a data management plan. By restructuring the data and utilizing tools like Entity Relationship Diagrams (ERD), the company aimed to improve its data analysis capabilities. This approach was expected to provide timely and actionable insights into business performance, helping the management to anticipate challenges and opportunities rather than reacting to them after they occur. Implementing dashboards for key measures was another proposed strategy to enable quick at-a-glance insights, thereby facilitating strategic planning and forecasting .

You might also like