Windows is one of the best operating system used around. This is because most of programs are made for Windows and Windows XP is the best choice because it has the reliability of windows 2000 and a very user friendly interface which makes it the best choice for a business.
Linux is another operating system that is the most reliable OS and a choice for professional users and developers around the world. It is based on a command-line interface and has many shells (Graphical User Interface) which makes it look no worse than and Windows or Mac. The only disadvantage is that many steps have to be taken to run a windows based program.
HARDWARE REQUIREMENTS
· The Bank will have a total of 5 computers in a network of 4 clients and 1 server computer. All computers will use Windows XP.
· they will run on an Intel Pentium 4 processor of 1.4 GHz, which will be an optimal speed for running several applications at the same time.
· 128 -256 MB RAM is usually the required memory for running few applications at the same time
· 50x CD-ROM / CD-R is advantageous as back up CDs need to be produced each day and with the fast pace of technology
·15 to 20 GB Hard Disk Space is good enough for a banking system because files do not take up lot of space.
·17 inch monitors can be used on all computers as well as well as 1 projector for presentations. LCD Screens can also be used even though they are quite expensive there is no impact on users’ eyes which can increase the working hours of employees.
·3 black and white laser printers can be used for fast hard copies, while 1 color laser printer can be used to create colorful and attractive forms.
·An ordinary keyboard can be used as well as optical mouse because ball mouse causes inconvenience while drawing and he ball has to be cleaned frequently.
SOFTWARE USED
MS Excel is the most popular spreadsheet software around the world. It consists of a user friendly interface and a set of functions which makes it easier to carry out calculations and avoid repetitions. It also includes graphs which is a necessity in a banking business.
3S is another accounting software which doesn’t include a spreadsheet but has many accounting functions like Cash Books, General Ledger, etc. The Disadvantage of using this program is it needs training which can raise costs of the bank. It is also too complicated for ordinary people and has fewer functions than Excel.
Tally is a very simple and complete business solution. It doesn’t require any training but doesn’t have all functions Excel has. It is easy to learn, configure and use tally. Tally is not a computerized spreadsheet package but it is accounts on computers.
JUSTIFICATION OF CHOOSING MS EXCEL
Microsoft Excel has a user friendly interface which makes it quite easy to interact with the user. It is a general purpose electronic spreadsheet used to collect and organize business data. It can be used to manage complex calculations. Microsoft Excel is a spreadsheet package which displays information in the form of a table which consists of rows and columns.
Charts and graphs can be easily created with Excel. These graphs are a very important in a banking business to show the monthly profits, spending costs, etc. It has various options which can help improve the look of the spreadsheet by changing colors, font sizes and styles. Ms excel has a vast variety of calculation options and functions which are the main advantages. These decrease the chance of errors, help perform complex calculations and access data from different sheets. For Example VLOOKUP can be used to have an updated list of wages so that when an employee’s code is entered information about him is given immediately. Whenever this information is changed in the ‘Wages’ sheet (for example if the employee gets a rise) the amount will be immediately updated in the VLOOKUP sheet. It can also be used in other spreadsheets like the loan department spreadsheet to give us an updated list of clients and the information about their loans. The SUM function can be used to calculate the totals of profits or losses of the business. COUNTIF can be used to calculate the number of transactions that occur during a month. SUMIF can be used to calculate the sum of particular transactions given a criteria range. Other functions like MAX and MIN can be used to show the highest or lowest profits made by the bank while the IF function can be used to calculate taxes and other variable costs. Excel also includes functions guide which can help the employees learn the functions without education and when errors are made in formulae Excel can point them out so that the employee will know where he/she went wrong. Excel is also comparably easy to use and requires no training.
COLLECTING DATA
I as a system analyst need to collect information by interviewing accountants and other employees of the bank .This is an interview with Mr. Dover the chief accountant of the business.
Me:- good morning sir
Mr. Dover: - good morning
Me “- Mr. DOVER, how long have you been working for this Bank?
Mr. DOVER:-well, I have been working for approximately 4 years
Me: “How many employees do u have currently working in the bank?”
Mr. Dover : “We have 15 employees and are going to employ few computer engineers who can maintain the computers after computerization.”
Me: “What solutions are u looking for?”
Mr. Dover: “Well, I want my business to be more efficient and I want my clients to be happy with our service. I get many complains about my business most of them are either errors done by the accountants or because of the time they have to wait until their account can be processed.”
Me: “What precautions do you take before giving someone a loan or a credit card account?”
Mr. Dover: “First, that person or business is examined very carefully. Information like the salary of the person or the incomes of a business are very important. Second we check the financial status of the person/business and if all these are perfect then information like phone number, PO box, etc is collected and stored and loan or credit card account is given.”
Me:- Could you explain to me in detail of how your accounting department functions?
Mr. DOVER: - Calculations are done manually by the use of calculators. Clients are first given a questionnaire and information is collected about their business, the loan requested, company profits and other information which is all written in forms and stored.
Me:- what problems does your department face?
Mr. DOVER:-the problems that occur most of the time are of errors in calculations. Calculations are done manually so many errors can occur which can cause a great loss to the business. Other problems are that 1 accountant has to be checking the forms and amounts so that errors can be corrected but lot of time is wasted on each client so it’s hard to keep the bank going. There is also a problem of repetitions and file loss. Many Files are handwritten and can be easily lost and have to be filled again so there is a waste of time.
Me:-what is your opinion about computerization? Do you think it will help the Bank and how?
Mr. DOVER:-well, I think that computerization is the best solution to the problems we are currently facing. Using computers our bank will have easy access to e-mail and internet so clients can access our bank on-line. Calculations will be done more quickly using spreadsheet packages. Working conditions will be improved because of less handwritten work so employees will be satisfied with their work.
Me:-Thank you for your time Mr. DOVER .I hope computerization will solve all the problems your business is facing and help you have high profits.
Mr. DOVER:-It was my pleasure.
Interview with Mr. Kent who is an accountant in the loan department.
Me: “Good afternoon Sir”
Mr. Kent: “Good afternoon”
Me: “How many years have you been working in this bank?”
Mr. Kent: “About 3 years now.”
Me: “What exactly do u do?”
Mr. Kent: “Well, my job is to calculate the rate of interest for the loans and calculate the profits of the loan department.”
Me: “Do u use any particular formulas in your calculations?”
Mr. Kent: “I use the formula to calculate the rate amount.”
Me: “Do u think computerization is the best solution and why?”
Mr. Kent: “I have been asked this question a plenty of times. Well my answer is, yes I do think computerization is the best solution because it has many advantages. Our business can earn high incomes if it is organized and customers will be happy with our fast service”
Me: “Thank you for the information.”
Mr. Kent: “You are welcome”
Mr. Tom Show – a book keeper
Me: “Good evening sir”
Mr. Show: “good evening”
Me: “Mr. Show what exactly is your job?”
Mr. Show: “I work along with the cashiers who collect data and I store that data in particular files.”
Me: “Are u satisfied with your work?”
Mr. Show: “Honestly, it is very difficult because there are many records which I have to sort out and store. I have to very accurate so that I don’t mess up the files. This is why I have to work at a very slow pace and I have to check the files for any errors after they are sorted.”
Me: “Then I think you should be looking forward toward computerization more than others, is that right?”
Mr. Show: “Yes indeed, computerization will save a lot of time for me as well as for other employees. Accounts will be processed with ease and at a fast pace and correction is not required because I heard that it is automatic.”
Me: “Well you are right computerization does offer auto error correction.”
Mr. Show: “Best wishes for you. I hope the new system will bring our bank on top.”
Me: “I hope that too. Thanks for your cooperation.”
Mr. Show: “Good luck”
Now that I have collected all information I need about the bank and the way it works I can start designing the new system but first I have to make a list of worksheets which will be there in the bank.
THE PERFORMANCE OF THE NEW SYSTEM
These are the sheets that I am planning to make:
- A wages worksheet containing employee information and salary.
- A loan department worksheet containing loan transactions and clients who have received a loan.
- Deposit sheets which contain fixed deposits and Current Deposits.
- A Credit Card and a Debit card sheets which will contain information about debit and credit card accounts.
- A General Expenses worksheet containing expenses like electricity and rent.
- A Profit n Loss account worksheet containing all incomes and losses and which calculates the monthly profit.
- And Lookup sheets which make finding information about employees or clients available when a roll no. is input.
These are the advantages that the new system will have:
- Forms can be produced quickly and with high quality graphics because of the laser printer. These forms will be very attractive and will help the user fill them out.
- Calculations will be done with ease and there will no fear of human errors because of auto spell correction and other features of a spreadsheet package. Formulae can be also used to make them even easier and avoid repetitions. The results will be displayed with out any delay on monitors and can be also printed out. Employees will be happy with their work because they will be using computers which are easier than making calculations using calculators.
- Storing will be much simpler because computer files can be easily stored and accessed any time with no delay. These files are also safe because unlike paper files they cannot be lost unless if deleted by user. The spreadsheets and forms with clients’ details can be stored on the hard disk, a floppy or a CD-R\RW.
- Security is one of the main aspects every business considers. Viruses are known as programs which can damage your software (by erasing your data) and even damaging your hard disk. His is why anti-virus software has to be used to protect the computer from such things. Norton Anti-virus 2002 is a well known anti-virus software which helps the computer be safe by checking the e-mail for viruses as well as disks (HDD, FDD, CD) Another danger is hackers who can access files on the server and erase data. A firewall has to be installed which will block the access from any computer. Norton Personal Firewall and Zone Alarm are well known programs to solve these problems. Backup of files and passwords are necessary in case of crashes and unauthorized access.
Information can be printed out, or shown on the monitors .It can be also displayed through projectors which help in presentations .These presentations may include charts and graphs made with MS Excel, Drawings, Animated Presentations made with PowerPoint, etc…
These are the formulas I’ m going to use in my worksheets:
- Nested IF – for conditions which require more than 1 IF. Example: the condition that if the employees department is “Accountant” then salary = particular amount.
- VLOOKUP which will allow information to be output when a roll no. is input. This will be useful in my lookup worksheets where the information about a client or an employee will be given from the roll no. input.
- SUM which I can use to calculate totals
- Other formulas like MIN, MAX if required.
These are the functions of Excel I’m going to use:
- Validation which is a powerful function. I can use it to correct user input errors like length of text so that if a number or text is entered and exceeds the validation amount then an error will be reported. I can also use this function to create list boxes with multiple choices for example in the wages worksheet I can create a list box containing all departments instead of typing all of them manually.
- Charts which can help me observe the bank’s profits
- Other functions like Sorting and auto filter .
This is a data flow diagram for the bank:
After analyzing the problem of the current system I have to design a spreadsheet for the new system. I’ll have to make it as user friendly and easy to use as possible. To ensure that my spreadsheet will be easy to use and fast to access I made a menu using the form menu in Ms Excel. This menu allows fast access to any section of my spreadsheet. I made it using the Macro function in MS Excel. The Macro function in Excel consists of a Visual Basic Code which can either be edited manually within Visual Basic or by Recording Marcos in Excel itself. It is a very important function because it allows programming within MS Excel. First I made 11 buttons which can be found in the forms toolbar. Then I clicked on “Record Macro” on the Visual Basic toolbar, clicked on the Loans worksheet and pressed “Stop Recording”. Same way I made 11 Macros all going to different sheets of my workbook. Then I assigned the Macros to the buttons by right clicking on them and selecting “Assign Macro and then choosing the right Macro for the particular button. After I have my menu ready I can start working out my worksheets.
The Wages Worksheet
The next worksheet I have designed is the Wages worksheet. It includes all information about the Employees currently working in the bank.
The first column has serial numbers for all employees so that later in my employee lookup sheet
I can find information about any employee by typing his/her serial number.
In the second column I have the names of employees currently working in the bank.
The third column is the section column which includes the particular department. This column is important because the salary of a particular employee depends on what department he is. For ease of use I can use validation in this column so that Excel produces a pull down Menu using validation so that I can choose the department from a list instead of typing it again and again. Validation is a very important function because it prevents errors of input.
The next column is the Basic Salary which will include salaries of employees. It includes a formula which will output the salary depending on the department input in the “Section” column.
The formula is “=IF(C7="Accountant";5000;IF(C7="Assistant";7000;IF(C7="Manager";15000;IF(C7="Cachier";3500;IF(C7="Computer Engineer";2500;IF(C7="Trading Department";4000;IF(C7="Head Accountant";8000;IF(C7="Book Keeper";4000;))))))))”
It uses the IF function which can output an amount depending on the Department. For example is the value in Department is “Accountant” then salary will be 5000 DHS. I have formatted the cells by setting a custom value of # ##0, 00" DHS" which will add a DHS after each value I input.
The next 2 columns is House Allowance and Transport Allowance which includes the same formula with different values for different departments.
The last column in the Wages worksheet is “Total Salary” in which I use the SUM function which will calculate the basic salary + transport allowance +house allowance.
The Loans Department Worksheet
The Loan Department is the section of the bank which lends money to businesses and individuals. To insure the return of the money lent Banks make a clear examination of the person and records data such us his salary, telephone No, Po. Box and Passport Copy so that in case the loan is not returned appropriate actions ca be taken.
The first column of this worksheet is S. No which is also used in the last worksheet which is Client Lookup where using this number information can be found about a particular customer.
The second column includes the Names of clients.
The third column includes the date of the loan request that is the date when a particular client had taken a loan during this month.
The 4th and 5th columns include the amount the client had requested and the time he had requested it for.
The 6th column includes the interest rate which depends on the size of the company or the current job status of an individual, the amount requested and the time the amount had been requested for. The interest rate may differ so there are no particular formulae for these cells.
The 7th column includes the Amount lent to the person after an I interest had been applied to it and subtracted from the total. This formula represents the Amount lent:
“=D6 - (D6*F6*E6)/12”
D6 id the total, F6 is the time (years) and E6 is the interest rate. I have formatted the cells to “percentage so that division by 100 is not necessary. This amount is divided upon 12 months to find the value per month.
The next column shows the amount kept as revenue to the bank. The formula is:
“= (D6*F6*E6)/12”
The last 2 column show the contact information of the clients like their telephone number and PO Box. I applied custom cell formatting and used validation for the telephone column. I formatted the cell to ‘ “050” 0 ’ so that when a number is input 050 is automatically added to it so that employees don’t have to type 050 each time they input a number.
I used maximum length validation check so that any value less or more than 7 digits will be rejected.
Fixed Deposits
The Fixed Deposits section of the bank includes the Deposits of clients for a particular time.
The first column of this worksheet is S. No which is also used in the last worksheet which is Client Lookup where using this number information can be found about a particular customer.
The second column includes the Names of clients.
The third column includes the date of the Deposits that is the date when a particular client had deposited money during this month.
The next 3 columns show the amount deposited, time (measured in days) and the rate of interest. The rate of interest is variable depending on the information the bank had collected from the client .This interest rate is offered to people and companies so that they invest but these cannot take the amount deposited until the date specified.
The next 2 columns show the extra monthly Deposit and the Total Deposit. I used a formula to calculate The Extra monthly deposit which is:
“ =(D6*F6)/12”
D6 is the amount deposited while F6 is the rate of interest and 12 is the number of month which help me calculate the amount deposited per month.
The Total monthly deposit is the sum of the amount deposited and the extra amount and contains the following formula:
“ =SUM(D10+G10) “
The last 2 columns show the contact information of the clients like their telephone number and PO Box. I applied custom cell formatting and used validation for the telephone column. I formatted the cell to ‘ “050” 0 ’ so that when a number is input 050 is automatically added to it so that employees don’t have to type 050 each time they input a number.
I used maximum length validation check so that any value less or more than 7 digits will be rejected.
Current Deposits
The current deposits section of the bank handles deposits of clients without fixed time unlike the Fixed Deposits.
The columns are similar to the Fixed Deposit but don’t include the extra deposit, total deposit or the rate of interest.
General Expenses
The General Expenses section includes monthly expenses such as rent, electricity and water, etc…
It consists of 3 columns which are S. No, Expenditure and Amount. The only formula used is SUM to calculate the total General Expenses.
“ =SUM(C5:C11)”
Other Services
The other Services section includes Credit Cards, Debit Cards and other services offered by the bank.
Credit Cards
In order to open a credit card account clients have to go through lots of investigation by the bank. Lots of data must be collected like the salary, job profile, etc…
The first column of this worksheet is S. No.
Next is the name and then Date.
The 4th column includes “Job Profile” which shows the current job of the client.
The next column shows the “Financial Status” of the person which is the person’s personal income.
Then comes “Credit Limit” which is the amount given by the bank which can be spent using a credit card. This amount varies and depends mainly on the bank and on the job and Financial Profile of the client. For example a manager with 25000 DHS salary gets a higher limit than other clients with lower salary.
The next column is “Request Status” which has 2 values either “approved” or “rejected” and validation option “list” had been used so that these options can be selected instead of typed manually.
The next 2 columns are “Rate of interest” which is fixed to 21% yearly or 1.8% monthly by the bank and “Amount Withdrawn” which shows the amount the client had withdrawn during this month.
The next column is “Profit”.
I used a formula in “Profit” to calculate the interest which is a profit to the bank:
“ =I8*H8”
I8 is the interest rate (formatted as %) and H8 is the amount withdrawn.
The last column is “Service Charge” which is an amount charged by the bank per month when using a credit card.
There are 2 types of services:
Gold – This has a limit of 15000 DHS and more which is 450 DHS yearly.
Silver - everything less than 15000 DHS which is 250 DHS yearly.
I’ve used this formula:
“ =IF(F8>15000;INT(400/12);INT(250/12))”
This formula creates a condition that if credit limit is more than 15000 DHS than 33 DHS is the amount (450/12) displayed and if it is not more than 15000 DHS than it displays 20 DHS (250/12).
I have used validation to make a list box in the “Request Status” column which includes “APPROVED” and “REJECTED”. This was done by selecting the “list” option from the validation window and selecting the range of values which wil be in the list.
Debit Cards
Debit cards are held by people who deposited money into the bank.
Debit cards include columns like S. No, Name, Credit, Debit, Overcharge, Number of ATM withdrawals and their Amount.
Credit is the amount deposited into the bank by particular clients and Debit is the amount withdrawn from these people’s accounts’.
The column “Overcharge” is the overcharge amount the client has to pay is he/she has withdrawn less than 3000 DHS. The formula I used is:
“ =IF((C36-D36)<3000;30;"NO") ”
C36-D36 is the amount the person has after withdrawing (Credit – Debit) and if this amount is less than 3000 than an overcharge of 30 DHS is taken if the amount is greater than 3000 then the output is “NO”.
The last 2 columns show the number of ATM withdrawals and the charge for them.
In this bank 4 free withdrawals are allowed and if more are made each is charged 2 DHS. I’ve used an IF formula to calculate the amount (which is in the amount column):
“ =IF(F26 >4;(F26 - 4)*2;0) ”
This formula is a condition that if the amount of withdrawals is more than 4 then that amount is subtracted from 4 (so that the4 free withdrawals aren’t counted) and multiplied by 2 DHS, and if the number is than 4 then the charge is 0.
The last part of Other Services is L/C and Currency Exchange revenue.
Profit and Loss account.
The Profit and Loss account (Pnl) is the worksheet that calculates monthly profit or loss of the business.
It consists of an Incomes section which includes all Incomes the Bank had made and Expenses. The Expenses Total is subtracted from the Incomes total to find a value which if positive is a profit and if negative is a loss.
The only formula used in this is “ =E13-E19” which calculates the profit or loss.
The last worksheet of my workbook is “lookup”.
It consists of 4 sections.
- Employee lookup which uses this type of formulae for Name, Department, etc. to find all information about an employee from the No. entered : “=VLOOKUP(C4;Wages!A6:G26;2;TRUE)”
-
Loan Department Client lookup which also uses VLOOKUP to find information about clients in the loan department. Example:
“ =VLOOKUP(C12;Loans!A6:J26;2;TRUE) “
- Fixed Deposits which uses VLOOKUP to find out information about clients in the fixed deposit section.
- Current Deposits which also uses VLOOKUP to find out information about clients in the current deposit section.
I have made this sheet so that a client or employee information can be easily found without the need of going through the other sheets and searching for him/her.
MENU
To assign a macro to a button I right-clicked on a button and selected “Assign Macro”. Then click on “Record”.
Type a name for the new macro and
Click on “OK”
WAGES
Then the worksheet which will be accessed by clicking on the button should be selected and “Stop Recording” chosen from the Macro menu.
All 11 buttons should have 11 Macros assigned to them which will take the user to different sections of the worksheet.
Cells which have amounts of money can be formatted so that “DHS” is added in front. This can be done by right-clicking on the cell and selecting “Format Cells”.
In “Custom” I typed # ##0.00” DHS”. This adds “DHS” to every amount entered.
LIST BOX
To make the work easier I made a list of departments for each employee so that by selecting a different department the salary is affected.
This can be done by selecting validation from “Data” menu.
Then under “Allow” “list” should be chosen and a range selected. This range will include all values in the list.
This is the kind of list box that can avoid repetitions.
LOAN DEPARTMENT
I formatted the telephone no. row so that “050” would be added automatically because all clients are requested for their mobile numbers. This can be done by typing “050 ”0 in the custom formatting window.
I used validation on the telephone no. row so that numbers with length more than 7 digits will not be accepted.
I also formatted the date row to “Date Category” and selected ##.##.## so that if a 6 digit number is entered (120303) it will be converted to (12.03.03).
CREDIT CARDS
I have used validation to make a list box in the “Request Status” which includes “APPROVED” and “REJECTED”.
After selecting “list” from the Validation windows a range should be selected.
This is the list box I get when I click on the status.
Lookups
I have included 3 lookup sheets in my workbook:
- employee lookup.
- loan department lookup
- deposits lookup.
This is how I made each of them:
After selecting the range I get a list box:
This way whenever I choose a serial no. from the list I get the information about an employee.
The other 2 lookup sheets were made the same way using VLOOKUP.
I have found the new system very effective because calculations are done more quickly than before. The new system offers everything from a menu to a profit and loss account which make it an excellent accounting package for the bank.
- The menu gives easy and efficient access to different sections of the worksheet.
- The List Box feature made by validation is very useful for multiple inputs. It saves time by avoiding repetitions.
- The Cell Formatting feature which allows amounts and dates to be formatted that is amounts to have the currency in them and dates to be in DD.MM.YY format.
- The Validation feature also allows error correction for example the “text length” option allows the cell to have a particular text length which if exceeded will return an error.
This are the comments by Bellamy Cox an accountant who was the first person to test the new system:
Mr. Cox: “I think the new system is just brilliant!!! I have had a lot of problems with the previous system, mostly because of input errors, lots of repetition and file storage. Now with this new computer based solution our bank can get the highest revenues in its history because people no longer have to wait because calculations are done in seconds. No Doubt this is the ONLY solution that is the most effective.”
All staff share good thoughts about the new system. But there are still things that I will have to improve like forms so that information can be entered in the forms instead of into the worksheets and automatic percentage calculators which can calculate the percentage using the given data.
These are the goals that I have achieved by using excel:
- A menu which allows easy access.
- Lookup sheets that allow me to find any information I need about an employee or a client.
- Easy input (using list boxes, etc), error correction (validation).
- A Profit n Loss account which is automatically updated when any value is changed.
- Cell formatting for better organized accounts.
It has been a pleasure for me to use my knowledge of Excel in a business and I have learnt a lot by helping the National Bank of Dubai.