Kent Institute - ACIS215: Excel Report on Accounting Systems

This report presents a comprehensive analysis of an Accounting Information System (AIS) implemented using Microsoft Excel. The assignment focuses on demonstrating the application of various Excel functions to manage and analyze accounting data. The report details the use of formulas such as VLOOKUP, TODAY, IF, SUM, and COUNTIF within spreadsheets named 'Project Database,' 'Subcontractors Database,' and 'Project Report.' These formulas are used to automate tasks like inserting customer names, calculating project statuses, estimating days, determining amounts, and generating reports. The report also explains the implementation of a pivot table for data visualization. References to relevant academic sources support the methodological approach. The report is designed to showcase the practical application of Excel in accounting, highlighting its utility in streamlining data management and enhancing decision-making processes within an organization.

Running head: ACCOUNTING INFORMATION SYSTEM IN EXCEL
Accounting Information System in Excel
Name of the Student:
Name of the University:
Author Note:

1ACCOUNTING INFORMATION SYSTEM IN EXCEL
Table of Contents
Explanations of Formulas used in excel.....................................................................................2
Reference....................................................................................................................................6

2ACCOUNTING INFORMATION SYSTEM IN EXCEL
Explanations of Formulas used in excel
1. To insert the name of the customer in the customer name column in the spreadsheet
named project database, the “VLOOKUP” function has been used (Kurniawan et al.
2017).
Syntax: =VLOOKUP(E6:E65,'Customers database'!$C$6:$D$450,2,FALSE)
2. To insert the today’s date in the today date column in spreadsheet named project
database, the “TODAY” function has been used.
Syntax: =TODAY()
3. To insert the status of the project in the status column in the spreadsheet named
project database, the “IF” function has been used.
Syntax: =IF(H6<I6, "Completed", "Running")
4. To insert the number of estimated days to complete the project in the number of
estimated days to complete column in the spreadsheet named project database, a
simple formula has been used.
Syntax: =H6-G6
5. To insert the number of remaining days to complete the project in the number of
remaining days column in the spreadsheet named project database, a simple formula
has been used.
Syntax: =H6-I6
6. To insert total quoted to the customer in the column “N” in the spreadsheet named
project database, a simple formula has been used.
Syntax: =M6+M6*70%
Similarly, column “O”, “P” and “Q” is formulated.

3ACCOUNTING INFORMATION SYSTEM IN EXCEL
7. To insert 4th instalment value in the column “R” in the spreadsheet named project
database, “IF” function has been used.
Syntax: =IF(J6="Running", N6*25%, 0)
8. To insert total receipt value in the column “S” in the spreadsheet named project
database, “SUM” function has been used.
Syntax: = SUM(O6:R6)
9. To insert outstanding amount and the profit in the column “T” and “U” in the
spreadsheet named project database, a simple formula has been used.
Syntax: Outstanding amount =N6-S6
And Profit =N6-M6
10. To insert starting date of 1st project corresponding to the subcontractor name in the
column “H” in the spreadsheet named subcontractor database, “VLOOKUP” function
has been used.
Syntax: =VLOOKUP(D8,'Project- Database'!$D$6:$H$65,4,FALSE)
Similarly the starting date for 2nd, 3rd and 4th projects’ starting date, ending date and
the status in the columns from “I” to “S” in the spreadsheet named subcontractor database is
formulated. The syntax for the respective columns are presented below:
I= VLOOKUP(E8,'Project- Database'!$D$6:$H$65,4,FALSE)
J= VLOOKUP(F8,'Project- Database'!$D$6:$H$65,4,FALSE)
K= VLOOKUP(G8,'Project- Database'!$D$6:$H$65,4,FALSE)
L= VLOOKUP(D8,'Project- Database'!$D$6:$H$65,5,FALSE)
M= VLOOKUP(E8,'Project- Database'!$D$6:$H$65,5,FALSE)
N= VLOOKUP(F8,'Project- Database'!$D$6:$H$65,5,FALSE)

4ACCOUNTING INFORMATION SYSTEM IN EXCEL
O =VLOOKUP(G8,'Project- Database'!$D$6:$H$65,5,FALSE)
P =VLOOKUP(D8,'Project- Database'!$D$6:$J$65,7,FALSE)
Q =VLOOKUP(E8,'Project- Database'!$D$6:$J$65,7,FALSE)
R =VLOOKUP(F8,'Project- Database'!$D$6:$J$65,7,FALSE)
S =VLOOKUP(G8,'Project- Database'!$D$6:$J$65,7,FALSE)
11. To insert quoted amount to the subcontractor for the 1st project corresponding to the
subcontractor name in the column “T” in the spreadsheet named subcontractor
database, a formula with “VLOOKUP” function has been used.
Syntax: =60%*(VLOOKUP('Subcontractors Database'!D8,'Project- Database'!
$D$6:$N$65,11,FALSE))
Similar formula is used in columns from “U” to “W” to insert quoted amount to the
subcontractor for the 2nd, 3rd and 4th project.
12. To insert paid to the subcontractor for the 1st project corresponding to the
subcontractor name in the column “Y” in the spreadsheet named subcontractor
database, a formula in “IF” function has been used.
Syntax: =IF(P8="Running",T8*50%,T8*90%)
Similar formula is used in columns “Z”, “AA” and “AB” to insert paid amount to the
subcontractor for the 2nd, 3rd and 4th project.
13. To insert number of running projects corresponding to the subcontractor name in the
column “I” in the spreadsheet named project report, the “COUNTIF” function has
been used (Willis 2016).
Syntax: =COUNTIF('Subcontractors Database'!P8:S8,"Running")

5ACCOUNTING INFORMATION SYSTEM IN EXCEL
14. The pivot table is an extraordinary function that presents the data in a cross-section
table to understand and visualise the statistics easily. Pivot table is used to show the
number of projects across city and state in the spreadsheet named pivot table.

6ACCOUNTING INFORMATION SYSTEM IN EXCEL
Reference
Kurniawan, Y., Karsen, M., vin Adiprasetyo, A., Juwitasary, H. and Tapia, D.F.C., 2017,
November. Accounting information systems implementation:(A case study approach).
In 2017 International Conference on Information Management and Technology
(ICIMTech) (pp. 294-299). IEEE.
Willis, V.F., 2016. A model for teaching technology: Using Excel in an accounting
information systems course. Journal of Accounting Education, 36, pp.87-99.