2107AFE Accounting Information System Project: Excel, Database & Video

This project showcases a student's understanding of Accounting Information Systems, focusing on the application of MS Excel and database design principles. Part 1 utilizes Excel to analyze sales data from 2012 and 2013, employing functions to calculate variances, create pivot charts for country-wise and product-wise sales forecasting. The analysis includes the use of VLOOKUP, IF functions, and different referencing techniques to predict future sales trends. Part 2 involves database design for a charity service scenario, outlining business rules, and constructing an Entity Relationship Diagram (ERD) to represent entities like clients, emergency contacts, charities, funds, and sponsors. A table schema is also provided, detailing the structure of each relation with primary and foreign keys. The project demonstrates the integration of data analysis and database design to provide insights into business operations and decision-making.

Running head: ACCOUNTING INFORMATION SYSTEM
ACCOUNTING INFORMATION SYSTEM
Name of the Student
Name of the University
Author Note

1ACCOUNTING INFORMATION SYSTEM
Table of Contents
Introduction......................................................................................................................................2
Part 1- MS Excel..............................................................................................................................2
Part 2 – Database Design.................................................................................................................5
Business Rules.............................................................................................................................5
ERD.............................................................................................................................................5
Table Schema...............................................................................................................................6
References........................................................................................................................................8

2ACCOUNTING INFORMATION SYSTEM
Introduction
Accounting Information system is system that collects, store and process finance and
accounting related data which is later analyzed for decision makings. It is generally a computer
based tracking of the business activities combining with the information systems (Fitrios, 2016).
The information can be stored in a flat file based system such as excel or documents. However,
the information systems can also store data in the schema or structured based databases. This
report intends to discuss the handling of sales data in excel using several functions along with
database design for a scenario. The report also includes Sales forecast calculation based on the
past or recent data.
Part 1- MS Excel
In this Part, MS excel is used for analyses and process sales data of 2012 and 2013 using
excel functions. First the evaluation starts with the calculating difference between the actual sale
and predicted sale for each data (Samonas, 2015). It helps in determining the accuracy of the
prediction. Second work sheet explains the sales data country wise with the help of the pivot
chart and vlookup (Jelen, 2019). Pivot chart is created to see the data country wise and then with
the help of vlookup the forecast of the next year’s sale has been done for the countries.

3ACCOUNTING INFORMATION SYSTEM
Figure 1: Country Wise sale forecast
Source: created by the author
Next the analysis of the forecast has been done overall and product wise. The third sheet
of the workbook carries two pivot chart and pivot table explaining overall and product wise sale.
With the help of ‘IF’ function, absolute reference, relative reference and filters the analysis has
been done (Belvadi, 2017). Format of the cell has been selected as list type in order to input more
than one criteria. The respective pivot charts for the pivot tables represent the sale variance
between products and different years.
Figure 2: Overall sales forecast for upcoming year.
Source: created by author

4ACCOUNTING INFORMATION SYSTEM
Figure 3: Product Wise Sales Forecasting
Source: created by the author.
The last forecasting which is the product wise forecasting includes state filters and
subtotal of their respective sales, by adding up all the values. The forecasting shows increment
which is a god sign for the upcoming sales (Kadry, 2015). The above pivot chart is a bar graph
representing the forecasting for each type of product for upcoming years. The excel work
contains original data in the first sheet and 2 analysis sheet based on the functions.

5ACCOUNTING INFORMATION SYSTEM
Part 2 – Database Design
Business Rules
Business rules of can be identified from the business process of any organizations. It
helps in the modelling the system design and understanding it better (Wang et al., 2017). The
business rules for the charity service scenario has been described below:
i. A client can have one or more emergency contacts.
ii. An emergency contact can be related to one and only one client.
iii. Each client is registered with one and only one charity.
iv. Each charity can have one or more client registered.
v. Each charity can have receive funds from one or more sponsors or sources.
vi. Each sponsors or sources can grant funds to one or many charities.
ERD
Entity Relationship Diagram represents the real word object as entities, attributes and
relationship between them. ERD can be developed with the help of the business rule of the
system or organization. It helps in structuring the database and developed on the basis of the
ERD. The below ERD is developed for the charity service scenario (Frantiska, 2018). The first
step of ERD involves the identification of the Entities which are client, emergency contact,
charity, funds and sponsors. The attributes are also shown in the ERD with appropriate key. The
relationship between the charity and sponsors is many-to-many. Hence, the relation is
decomposed into third normal form by removing the transitive functional dependency. It is done
by introducing a table where the primary keys of the charity and sponsor tables are composite
primary key in the new table named funds. B doing this, each of the tuple can be unique from

6ACCOUNTING INFORMATION SYSTEM
each other. Both the attributes are also the foreign keys if their respective tables. The below ERD
is developed on the draw.io platform. The entity emergency contact is a weak entity. The table
has the foreign key client id referenced from the clientID to emergency contact.
Figure 4: Entity Relationship Diagram
Source: created by the author
Table Schema
Table schema represents the structure of the each relation (table) which is generated with
the help of the Entity Relationship diagram (Chytil et al., 2014). It is represented by the table
name along with the attribute names inside the parenthesis. The Primary key of the table is Bold
and underlined. Similarly the foreign keys in the table is shown with the italics. As there is no
primary key emergency contact details. Hence the entity can be defied as the week entity in the
system.
Client (ClientID, Name, PhoneNumber, Email, ABN)
EmergencyContact (ClientID, Name, PhoneNumber)

7ACCOUNTING INFORMATION SYSTEM
Charity(ABN, Name, ContactName, ContactPhone)
Funds (ABN, SponsorID, ReceivedDate)
Sponsor (SponsorID, Name, URL)
References
Belvadi, M. (2017). Excel tips for handling and prepping large spreadsheets of collections data.
Chytil, M., Polák, M., Nečaský, M., & Holubová, I. (2014). Evolution of a Relational Schema
and Its Impact on SQL Queries. In Intelligent Distributed Computing VII (pp. 5-15).
Springer, Cham.
Fitrios, R. (2016). Factors that influence accounting information system implementation and
accounting information quality. International Journal Of Scientific & Technology
Research, 5(4), 192-198.
Frantiska, J. (2018). Entity-relationship diagrams. In Visualization Tools for Learning
Environment Development (pp. 21-30). Springer, Cham.
Jelen, B. (2019). Power Excel 2019 with MrExcel: Master Pivot Tables, Subtotals, Charts,
VLOOKUP, IF, Data Analysis in Excel 2010–2013. Tickling Keys, Inc..
Kadry, S. (2015). Monte Carlo Simulation Using Excel: Case Study in Financial Forecasting.
In The Palgrave Handbook of Research Design in Business and Management (pp. 263-
289). Palgrave Macmillan, New York.
Samonas, M. (2015). Financial forecasting, analysis, and modelling: a framework for long-term
forecasting. John Wiley & Sons.

8ACCOUNTING INFORMATION SYSTEM
Wang, W., Indulska, M., Sadiq, S., & Weber, B. (2017, September). Effect of linked rules on
business process model understanding. In International conference on business process
management (pp. 200-215). Springer, Cham.