Accounting Information System Assignment: Excel, BI Tools Report

This assignment, prepared by a student, focuses on the application of an Accounting Information System (AIS). The first part involves an Excel-based case study, utilizing functions like "IF" and "VLOOKUP" to analyze payroll data and employee classifications. The second part provides a report on various Business Intelligence (BI) tools, such as QlikView, Tableau, and others, highlighting their significance in business decision-making. The report details the key features of BI tools, including executive dashboards, interactive reports with drill-down capabilities and trend analysis, and "What if" analysis for strategic planning. The assignment demonstrates how BI tools go beyond Excel, handling large datasets and providing valuable insights for growth, sales, and customer behavior understanding. References to relevant academic sources are also included.

Accounting
Information System

1
By student name
Professor
University
Date: 25 April 2018.
1 | P a g e

2
Executive Summary
A report has been prepared on the use of accounting information system in the business scenario.
An excel assignment has been prepared for the same and the case study has been solved in this
regard. Excel formulas like “IF” and “VLOOKUP” function has been used for solving the same.
In the 2nd part of the assignment, a report has been prepared on the commonly used business
intelligence tools that are usually being used for decision-making purposes.
2 | P a g e

3
Contents
Question 1 : Excel work...............................................................................................................................3
Question 2 : Report on Business Intelligence Tools.....................................................................................6
References...................................................................................................................................................8
3 | P a g e

4
Question 1 : Excel work
1.
a. Below is Table A as mentioned in the question. The range for the data is 30-80
TABLE: A
Classification Levels
Leve
l Hourly pay
1 30
2 40
3 50
4 60
5 70
6 80
b. Below is Table B.
TABLE: B
Pay Rate Table
Employee name Classification Level Hourly Pay
Paris Holton 5 70
Ricky Mortini 4 60
Jennifer Leepoz 2 40
Selina Geemak 2 40
Willard Smith 1 30
Russell Creak 6 80
Rafael Nooderly 4 60
Novak Djoker 1 30
Lara Bangle 3 50
Kath Hudson 6 80
4 | P a g e

5
2. Payroll data is being shown below:
Employee name Hours worked Regular Hours Overtime Hours Hourly Pay Base Amount Over tIme Total Pay
Jennifer Leepoz 41 36 5 40 1,440 425 1,865
Kath Hudson 52 36 16 80 2,880 1,360 4,240
Lara Bangle 40 36 4 50 1,800 340 2,140
Novak Djoker 37 36 1 30 1,080 85 1,165
Paris Holton 45 36 9 70 2,520 765 3,285
Rafael Nooderly 44 36 8 60 2,160 680 2,840
Ricky Mortini 52 36 16 60 2,160 1,360 3,520
Russell Creak 34 34 0 80 2,720 0 2,720
Selina Geemak 30 30 0 40 1,200 0 1,200
Willard Smith 39 36 3 30 1,080 255 1,335
Total 414 352 62 19,040 5,270 24,310
1,360Maximum Overtime
TABLE: C
Payroll Data
As per the above table, the maximum overtime earning of 1360 has been earned by 2
employees, namely Kath Hudson and Ricky Mortini.
3. The two other functions that can be included in the case study like the one mentioned
above with respect to the decision making is as follows:
1. Customers who are likely to default: This data can be derived using the formula
receivable days or the daily sales outstanding ratio on customer basis, which is
(Accounts receivables/Average sales per day), or (Accounts receivables/(Annual
Sales/365 days). This will help in understanding the no. of days in which a particular
customer is paying off for the sales effected (Belton, 2017). Post the calculation of
this, “IF function” can be applied to know which of the customers are beyond 180 or
360 days and thus the receivables is at risk.
2. Employees who are putting in long hours: This can be computed by using two
formulas. First the average working hours of all the employees needs to be worked
over for a couple of months using the formula “AVERAGE” for the total set and then
the average needs to be worked out for each employee for the given set of months.
Post this, “IF” function can be used to find if employee average is greater than group
average for how many employees (Bromwich & Scapens, 2016). In this way, those
employees who are putting in long hours of work can be found out.
5 | P a g e

6
Question 2 : Report on Business Intelligence Tools
There are numerous business intelligence tools, which are active in the market and are being
used by the businesses to help in the decision-making. These tools generally combine all the
internal department data as well as the external data from the third party systems like the emails
and the social media channels and summarizes them to give meaningful information that gives
the companies insight on the overall growth, the sales and the customer behaviour. Some of these
tools include QlikView, SAS, Tableau, Sisense, Actuate Business Intelligence and Reporting
Tools (BIRT), Domo, Good dada, Clear Analytics, Ducen, Jaspersoft, Looker, Microsoft BI
platform, Oracle BI, SAP business intelligence, SAP NetWeaver BW, etc (Choy, 2018).
These tools are just more than the excel sheets and are very effective in handling the voluminous
data and giving out various results. Three main features of the commonly used business
intelligence tools are mentioned below:
1. Executive Dashboards: The personalized dashboards have become inevitable in current
business scenario as it gives a bird’s eye view to the company’s management. It not only
gives real time data but it lowers the response time as it is easily understandable and can
be circulated on a regular or scheduled basis once a particular format has been finalised
(Alexander, 2016). It also helps in exception reporting which warrants the executives to
take necessary action in case of unexpected events and scenarios. The personalization of
the dashboards also helps in removing irrelevant information and focusing on the key
areas only.
2. Interactive Reports: These reports convert the data into the information and knowledge.
These interactive reports throw a lot of relevant information and helps in better decision
making. Some of the key advantages of these reports are that the users are able to:
i. Drill down in the reports and see the extensive details, which are not possible in
the summary.
ii. Conduct slice and dice on the data doing OLAP analysis (Mun, 2018).
iii. Use conditional formatting on the data and throw the exceptions through
highlighting them.
6 | P a g e

7
iv. Using of the regression analysis and the moving averages in the data to find out
the trend of the data (Goldmann, 2016).
v. It also enables the time zooming function, which enables the scanning of large
sets of data and thereby understanding the anomalies in the data.
3. “What if” analysis: This analysis helps the businesses to analyse the potential impact if at
all any variable or parameter is being changed. This thus helps in critical business
decisions before they are actually being taken (Bizfluent, 2017). With this, businesses can
avoid the hit and miss approach and can plan for the business targets and what will be the
actual result in case a particular is being changed. This is thus a strategic and critical
planning tool.
Besides these functions, it also serves other functions like location intelligence, which is the
ability to map, visualize and present the data in the geographical format, which helps the
company to get the locational view like sales per area or region (Erik & Jan, 2017). One another
function is metadata layer, which makes the process of reporting easy, eliminates the need of
SQL and coding giving the requisite business reports, and results in business language. Users
directly get the interactive data without having to deal with the complex underlying database.
The last one feature is the ranking reports, which gives order or ranking to the given specific
information and helps the management in knowing the top five values or bottom five values so
that the relevant decision can be taken. It shows the best and worst performing areas of the
business (Farmer, 2018). For example, it can help in extracting the top selling products region
wise or even sales people wise.
7 | P a g e

8
References
Alexander, F., 2016. The Changing Face of Accountability. The Journal of Higher Education, 71(4), pp.
411-431.
Belton, P., 2017. Competitive Strategy: Creating and Sustaining Superior Performance. London: Macat
International ltd.
Bizfluent, 2017. Advantages & Disadvantages of Internal Control. [Online]
Available at: https://bizfluent.com/info-8064250-advantages-disadvantages-internal-control.html
[Accessed 07 december 2017].
Bromwich, M. & Scapens, R., 2016. Management Accounting Research: 25 years on. Management
Accounting Research, Volume 31, pp. 1-9.
Choy, Y. K., 2018. Cost-benefit Analysis, Values, Wellbeing and Ethics: An Indigenous Worldview Analysis.
Ecological Economics, p. 145.
Erik, H. & Jan, B., 2017. Supply chain management and activity-based costing: Current status and
directions for the future. International Journal of Physical Distribution & Logistics Management, 47(8),
pp. 712-735.
Farmer, Y., 2018. Ethical Decision Making and Reputation Management in Public Relations. Journal of
Media Ethics, pp. 1-12.
Goldmann, K., 2016. Financial Liquidity and Profitability Management in Practice of Polish Business.
Financial Environment and Business Development, Volume 4, pp. 103-112.
Mun, K. a. S. I., 2018. A close look at the role of regulatory fit in consumers’ responses to unethical firms..
s.l.:s.n.
8 | P a g e