Abstract—In the industry 4.0, the application of information
technology to schools is very important. Especially with kindergartens, management and organization is very complex without the support of information technology. In Viet Nam, the number of kindergartens that have access to school management software is very limited. Although there is some software support, but all of them are very expensive and not suitable for the needs of the school management. With 2 main methods are Data Validation and PivotTable in Microsoft Excel platform, building a simple and free management application for all schools is possible. In this paper, the author presents a way to develop a simple and free school management application to help all kindergartens have the opportunity to use information technology to manage the school.
Index Terms—Microsoft excel, kindergarten, tuition fees,
management school, attendance records, spreadsheet programming.
I. INTRODUCTION
For everyone, Microsoft Excel is no longer a strange concept. Microsoft Excel is a spreadsheet program which enables one to enter numerical qualities or information into the lines or sections of a spreadsheet, and to utilize these numerical passages for such things as counts, diagrams, and measurable examination (5). The Spreadsheet was and is a substantial sheet of paper with sections and lines that arranges information about exchanges for a specialist to look at. It spreads or demonstrates the majority of the costs, salary, charges, and other related information on a solitary sheet of paper for a supervisor to look at when settling on a choice (11). There are many programs with many purposes built by Microsoft Excel: The analytical processing shown focuses on the use of resources by students and impact on specific learning material, here non compulsory self-tests, on the final exam (3). Teaching business statistics with Microsoft Excel (6). Excel Competency for the Professional Accountant: Advanced Applications, Controls, and Audit Add-ins (7). One of the uses of Microsoft Excel that I would like explore is in managing student attendance and participation records. One of the uses of Microsoft Excel that I would like explore is in managing the kindergarten school. My current initiative involves the use of some functions of Microsoft Excel for support the rector can manage their job with the needed management at their school.
Manuscript received Nov, 2018.
Vo Hung Cuong, the school of information and communication technology, the University of Danang, Da Nang, Viet Nam, +84 905672025
II.METHODS
Microsoft excel tools and functions are reviewed in the paper to explore how it can be used collaboratively by rectors in managing the kindergarten school. To explore the use of tools and functions, 2 main methods were used, i.e. by: Data Validation in Excel (9) and Automatic formatting of PivotTable reports within a spreadsheet (10). In reviewing the application, the questions were put forward, what are the steps needed to apply the tools.
III. STEPSINMANAGINGTHEKINDERGARTEN
SCHOOL
The steps involved in managing the kindergarten school are detailed in the following section.
A. Pupil Information
Create a new sheet called Student Information, for the purpose of storing student information and for attendance, billing, data in this sheet only once and will link the data that Shared for all other sheets in the program.
[image:1.595.305.549.500.646.2]Step 1: In this sheet will create columns in row 3 with the title: ordinal number, class, full name, gender, year of birth, parent name, father’s phone number, mother’s phone number, address, avatar are shown in Figure 1.
Figure 1. Sheet Pupil Information
Step 2: All of these columns are filtered according to the search conditions faster (2).
Step 3: The value of class B4 will apply Data Validation functions to assign attribute values depending on the number of classes available at the school: At Data Validation select Allow: list; Source: Kindergarten class, Baby class, Missed class, large class in Figure 2. Do the same with the D4 value box "Gender", select Allow: list; Source: Male, Female.
Step 4: In the K4 cell image, do the following: Insert -> Picture -> Ok, then: enlarge the image size to fit the size of
Managing the kindergarten school using
Microsoft Excel
the K4 cell, apply drag Match the ALT button to get an image that fits into the size of the K4 cell. Next, select the properties of the image and choose Move and size with cells (4).
Figure 2. Assign a class value
B. Attendance
Create a sheet called Attendance, the purpose of the pupil's attendance record and the data sheet for the monthly tuition fee.
[image:2.595.64.269.87.252.2]Step 1: Design the attendance sheet as shown in Figure 3.
Figure 3. Sheet Attendance list
Step 2: Class information cells and pupil names are assigned by taking the values of the cells containing the class information and pupil names in the Student Information sheet in Figure 4..
Figure 4. Formulas for class information and student names Step 3: In this sheet, the most important function is to create a date function so that you can reuse the sheet for other months and years simply by changing the number of months and years. First, in cell E6, create a DATE (year, month, day) function to get the first year, month, and day as the first day.
[image:2.595.47.293.315.480.2]Step 4: Show number of days in the month. In cell E7, assign the value of cell E7 = E6, because the format of cell E6 is (year, month, day) should reformat cell E7 just get date value: Select Format Cells -> Custom -> Type : dd in Figure 5.
Figure 5. Format cells for date values
Step 5: The date format is similar to step 4 for the remaining cells from F7 to AI7. Next, assign values for days from F7 to AI7 by adding 1 more unit, for example: cell F7 = E7 + 1; Cell G7 = F7 + 1... to AI7 = AH7 + 1.
Step 6: Display day name in month. In cell E8 enter the formula as follows:
=IF(WEEKDAY(E7)=1,"CN",IF(WEEKDAY(E7)=2, "Thứ hai",IF(WEEKDAY(E7)=3,"Thứ ba",IF(WEEKDAY (E7)=4,"Thứ tư",IF(WEEKDAY(E7)=5,"Thứ năm", IF(WEEKDAY(E7)=6,"Thứ sáu",IF(WEEKDAY(E7)=7, "Thứ bảy","")))))))
With this formula, the date’s name will be displayed automatically by the date number in that month.
Step 7: Calculates the total number of school days. This will use the COUNTIF ($E11:$AI11,"x") function to count the number of school days, each school day with an "x".
Step 8: Pupils who attend school on Saturday will be charged a supplement. Therefore, at the seventh Study column will assign 1: yes; 0: no; Use the Data Validation in Figure 6 to add additional fees in the fee sheet.
Figure 6. Assigned to attend school on Saturday
C. Tuition fees
Create a sheet called Tuition fees, the purpose of which is to calculate all pupil payables for one month.
[image:2.595.324.526.493.654.2] [image:2.595.45.292.535.645.2]Figure 7. Sheet Tuition fees
[image:3.595.302.552.52.174.2]Step 2: Class information fields and pupil names are assigned by the values of the cells containing the class information and student names on the Attendance sheet in Figure 8.
Figure 8. Formulas for class information and student names Step 3: The tuition fee is determined by the formula: =IF(C4="Nhàtrẻ",550000,IF(C4="Bé",500000,IF(C4="Nhỡ ",500000,500000)))
To check which class will have the corresponding tuition fee, for example: Kindergarten will have tuition fees is 550000vnđ.
Step 4: Extracurricular courses are identified by the formula:
=R4*$R$2+S4*$S$2+T4*$T$2+U4*$U$2
[image:3.595.47.288.258.389.2]Used to calculate the total amount of extra tuition fees such as English, Aerobic, drawing, birthday. Here the prices are set and multiplied by the corresponding cell value of 1 or 0 (yes or no) in Figure 9.
Figure 9. Extra charge formula
Step 5: The table-money column is determined by the formula:
=diemdanh!AJ10*SUM(N4*$N$2,O4*$O$2,P4*$P$2, Q4*$Q$2)
This formula is used to calculate the amount of table-money per month by taking the total number of school days in the Attendance Sheet with the total number of meals a student uses such as breakfast, morning milk, lunch, snacks. Here the prices are set and multiplied by the corresponding cell value of 1 or 0 (yes or no) in Figure 10.
Figure 10. Formulas for table-money
Step 6: Additional charge formula attends school on Saturday
=80,000*diemdanh!D10
Of which: 80,000VND is the seventh day surcharge depending on the school. diemdanh!D10 is the value of the seventh Study Column in the Attendance Sheet is set of two values of 1 or 0 (yes or no).
Step 7: The total column is determined by the SUM function to compute the sum of the component money components, such as Tuition, Extracurricular courses, Surcharge, table-money, Saturday School, Last month's debt, Other.
D.Payment notification
Step 1: Design sheet Payment notification as shown in Figure 11.
Figure 11. Payment notification form Step 2: Formula to get months, years automatically:
=diemdanh!$V$5&"/"&diemdanh!$Y$5
Where: $V$ 5 is the month value taken in the Attendance Sheet, $Y$ 5 is the year value taken in the Attendance Sheet.
[image:3.595.338.505.366.527.2]Step 3: Create a list of automatic class names: Create a list of class names in the new Sheet called Field Info and then Create a name by using the Define Name option (8). With the information shown in Figure 12.
[image:3.595.47.288.547.667.2] [image:3.595.307.549.638.762.2]At the top of the cell, use Data Validation to create the class list, as shown in Figure 13.
Figure 13. Create a class list
Step 4: Create a pupil's name and last name automatically based on the class and list the pupil's name by grade in the Tuition fee sheet.
[image:4.595.362.486.173.354.2]Creates a list of first and last names of the Kindergarten class, Baby class, Missed class, large class with identifiers: tennhatre, tenbe, tennho, tenlon using the Define Name option in Figure 14.
Figure 14. Make a list full name of baby class Where: $D$44:$D$83 is a block address that contains the pupil's full name.
Next, create a list of student names taken from the built-in list and compare them with the class name. Use Data Validation to create a pupil name list as shown in Figure 15 with source:
[image:4.595.64.270.303.462.2]=IF(C5="Nhà trẻ",tennhatre,IF(C5="Bé",tenbe, IF(C5="Nhỡ",tennho,IF(C5="Lớn",tenlon,"chua"))))
Figure 15. Create a list of names in class
Step 5: Get Course Fees, Extracurricular course, Extra Fees, table-money, Saturday study, Other, Total from the
Tuition fee sheet by pupil's name using the formula at the Tuition fee cell:
=INDEX(hocphi!$D$4:$L$163,MATCH($C$6,hocphi!$D$ 4:$D$163,0),MATCH(hocphi!$E$3,hocphi!$D$3:$L$3,0)) Then copy the formula in Tuition fee cell to all the remaining cells and edit the corresponding address in the function MATCH(hocphi!$E$3,hocphi!$D$3:$L$3,0) change $E$3 to $F$3, $G$3, $H$3, $I$3, $J$3, $L$3.
Step 6: Create print button. Use the Developer tool to create a button in Figure 16.
Figure 16. Create print button
Then press ALT + F11 and enter the VBA code for the button in Figure 17:
Sub Button1_Click()
ActiveWindow.SelectedSheets.PrintPreview End Sub
Figure 17. Enter the VBA code for the Print button Step 7: Use the free Accounting Helper add-in to convert the money into text with the VND () function in Figure 18 (1).
[image:4.595.307.549.410.566.2] [image:4.595.48.291.538.746.2] [image:4.595.355.493.600.769.2]E. Invoice
[image:5.595.53.266.113.286.2]Make a copy of the Payment Notification Sheet, revise the sheet name to the Invoice. Revise all necessary information to form a receipt. All formulas, functions remain in Figure 19.
Figure 19. Invoice form
F. Menu
Creating a sheet with a simple interface makes it easier to navigate menus.
Step 1: Merge 4 cells into one cell, write the names corresponding to the sheet names and format as a button to link to, as shown in Figure 20.
Step 2: Use Hyperlink to link to the corresponding sheet.
Figure 20. Menu management
Step 3: At each worksheet, a back button is formatted and a hyperlink is added to the sheet menu.
G.Statistical reports collected tuition fees
[image:5.595.393.460.138.305.2]Statistics show the total number of students, the number of pupils who have paid tuition fees, the number of pupils who have not yet paid tuition fees, the total amount of pupil earnings, the unrealized amount in Figure 21.
Figure 21. Statistical report collected tuition fees
Step 1: Use the PivotTable to report the number of pupils who have been paid, the tuition fees and the total tuition fees collected (10). With Table/Range: hocphi!$A$3:$L$163, will get the full value in the tuition sheet including the subject line. Then, take the necessary headers to show in the statistics report such as: Paying, class, full name, total in Figure 22.
[image:5.595.55.550.351.541.2]Figure 22. PivotTable retrieves fields for reporting Step 2: Use PivotChart to generate a visual statistical report on the number of pupils who pay tuition fees as shown in Figure 23. With Table/Range: hocphi!$A$3:$L$163, will take the entire value in the sheet. Tuition fees including the subject line. Then, take the necessary headers to show in the statistics report such as: Paying, class, full name, total in Figure 23.
Figure 23. Use PivotTable to generate statistical reports
IV. CONCLUSION
In this paper, we recognize that Microsoft Excel will be a powerful tool for building free applications that effectively support managing in today's education. And it is clear that the cost of building an application with Microsoft Excel will be significantly reduced.
With the excellent support of Microsoft Excel functions, small organizations and schools can design an application that manages to meet their needs. With this article, the kindergaten school can create a management application with many functionalities such as:
- Student Information Management - Student Attendance Management - Automatic Tuition Fees
- Create a Payment notification form for tuition fees automatically
[image:5.595.46.292.630.775.2]The program was conducted at two premise centers - Da Nang City. The program has helped the school save a lot of money for purchasing preschool management software.
REFERENCES
[1] "CÔNG TY CỔ PHẦN BLUESOFTS." Bluesofts.net. N.p., 2018. Web.
29 Nov. 2018.
[2] "Excel 2010: Filtering Data." GCFGlobal.org. N.p., 2018. Web. 29
Nov. 2018.
[3] "Excel Named Range - How To Define And Use Names In Excel." Excel
tutorials, functions and formulas for beginners and advanced users - Ablebits.com Blog. N.p., 2018. Web. 29 Nov. 2018.
[4] "How To Lock Picture/Image To Or Inside Cell In
Excel?." Extendoffice.com. N.p., 2018. Web. 29 Nov. 2018.
[5] "What Is Excel". Mathematical And Statistical Models, 2018,
[6] Bell, Peter C. "Teaching business statistics with Microsoft
Excel." INFORMS Transactions on Education 1.1 (2000): 18-26.
[7] Brown, William C., and Byron Pike. "Excel competency for the
professional accountant: Advanced applications, controls, and audit
add-ins." AIS Educator Journal 5.1 (2010): 25-45.
[8] Dierenfeld, Helena, and Agathe Merceron. "Learning analytics with excel
pivot tables." (2012): 115-121.
[9] Jelen, Bill. "Data Validation in Excel." Strategic Finance 95.10 (2014):
52.
[10] Moise, Wesner P., Thomas P. Conlon, and Michelle Lee Thompson.
"Automatic formatting of pivot table reports within a spreadsheet." U.S. Patent No. 6,626,959. 30 Sep. 2003.
[11] Power, D. J., "A Brief History of Spreadsheets", DSSResources.COM,
World Wide Web,http://dssresources.com/history/sshistory.html, version 3.6, 08/30/2004. Photo added September 24, 2002.
