These days’ computers hold an important spot in our lives. Today one can’t think about doing any work without computer. The impact of information technology in various aspects of accounting profession and practice has been pronounced over the last two decades in India, an auditor who does not understand computer based accounting system is bound to face many problem in course of his profession because today mostly all the accounting work is done on computers, so auditors are required to posses good knowledge about computer based accounting. A working knowledge of information technology is a basic requirement of “CHARTERED ACCOUNTANT” today. Hence, the knowledge acquired by the student through “INFORMATION TECHNOLOGY TRAINING” will be very helpful. This ITT program provides us all the Basic Knowledge of computer and its various software like MS-Excel, MS-World, data base management system, basic programming in Visual basic, web technology and software Tally. I heartily thank our instructors, Shashank Sir and Satish Sir, for their immense support, during the entire training and for the successful completion of this project work.
The Institute of Chartered Accountants of India (ICAI) has provided a 100 Hours Compulsory Information Technology Training which has been framed by modifying the old curriculum of Training. It is framed in order to get the practical knowledge about its application in various areas of professional knowledge.
It focuses on application software relevant for Accounting and Auditing. In addition, knowledge of spread sheets, data base, Visual basic language, management systems, digital signatures and authentication of electronic records, web technology and systems security maintenance are strengthened.
As the part of IT Training course a project has been incorporated in the curriculum. The project is actually to develop software to automate a task or set of tasks. A student could select the task (i.e. the topic) himself. The project is included in the curriculum to test and develop skills of student in actual work situation.
WHAT IS COMPUTER? A computer is an electronic device that manipulates data according to a list of instructions. The first devices that resemble modern computers date to the mid-20th century (1940–1945), although the computer concept and various machines similar to computers existed earlier. Early electronic computers were the size of a large room, consuming as much power as several hundred modern personal computers (PC). Modern computers are based on tiny integrated circuits and are millions to billions of times more capable while occupying a fraction of the space today, simple computers may be made small enough to fit into a wristwatch and be powered from a watch battery. Personal computers, in various forms, are icons of the Information Age and are what most people think of as "a computer"; however, the most common form of computer in use today is the embedded computer. Embedded computers are small devices that are used to control other devices—for example; they may be found in machines ranging from fighter aircraft to industrial robots, digital cameras, and children's toys. The word “Computer” originates from the word “compute” which means to calculate. Earlier computer was considered as a calculating device that can perform arithmetic operation at enormous speed. But now a day, amazingly 85% of work is done in computer even of non-numerical nature like music Composing, Resume sorting, Weather forecasting etc. Computer not only processes data and provides the user required information but it is also able to perform the subsequent steps on the basis of processed information. It performs four basic operations. • • • •
Input Processing Output Storage
Input This is the process of entering data and programs in to the computer system. Computer is an electronic device like any other machine which takes as inputs raw data and performs some processing giving out processed data. Therefore, the input devices take data from users to the computer in an organized manner for processing. An input device lets you communicate with a computer. You can use input devices to enter information and issue commands. A keyboard, mouse, scanner, digital camera, touch pads and joystick are examples of input devices.
• Some Common Computer Input Devices: • Keyboard • • •
Used to type data into the computer Most common input device today Has special keys for giving the computer commands o Commands tell the computer to do something, like save the file o These special keys are called command or function keys
• Pointing devices • •
Pointing devices move some object on the screen and can do some action Common pointing devices o Mouse - most common pointing device
o
Track ball - basically an upside down mouse
o
Joystick
o
Bar code reader - it is used in supermarkets, bookshops etc. It is photoelectric scanners that read the bar code printed on a product container.
o
Game controller
• Scanner A scanner allows you to scan documents, pictures, or graphics and view them on the computer. You can also use software to edit the items you scan. • • •
Used to put printed pictures and text into a computer Converts an image into dots that the computer can understand To scan text, optical character recognition (OCR) software is needed
• Digital Camera • •
Used to take electronic pictures of an object The pictures taken by a digital camera can be used directly by a computer
• Microphone • •
Used to put sound into a computer Need sound recording software
• CD-ROM/DVD-ROM • Can be used to put both sound and images into a computer
• Use a laser to read a Compact Disk (CD) or a DVD disk
• Video Capture Card • • •
Usually it is placed inside the computer's case Used to put video into a computer Need a video source, either a video camera or video recorder.
Processing The task of performing operations like arithmetic and logical operations is called processing. The Central Processing Unit (CPU) takes data and instructions from the storage unit and makes all sorts of calculations based on the instructions given and the type of data provided. It is then sent back to the storage unit.
• • • •
Central Processing Unit (C P U) At the heart of the digital computer, there exists the central processing unit [CPU]. All sorts of processing are initiated, carried out & completed in CPU. It is a CPU of a computer, which determines the class &the quality of the computer.
CPU consists of three major parts. • Memory Unit • Arithmetic & Logic Unit • Control unit
Memory Unit The memory unit is the part of the computer that instructions for processing. Although it is closely associated with the CPU, in actual fact it is
separate from it. Memory associated with the CPU is also called primary storage, primary memory, main storage, internal storage and main memory. When we load software from a floppy disk, hard disk or CD-ROM, it is stored in the main memory. There are following type of computer memory inside the computer:1) Internal memory(primary memory) 2) External memory(secondary memory) Internal memory(primary memory)
(a)
RAM: RAM stand for Random Access Memory. This is really the main
store and is the place where the programs and software we load gets stored. When the CPU runs a program, it fetches the program instructions from the RAM and carries them out. (b) ROM: ROM stand for Read Only Memory. The CPU can only fetch or read instructions from Read Only Memory. ROM comes with instructions permanently stored inside and these instructions cannot be over-written by the computer’s CPU. (c) CACHE MEMORY: Caching is a technology based on the memory subsystem of our computer. The main purpose of a cache is to accelerate our computer keeping the price of the computer low. Caching allows us to do our computer tasks more rapidly. (d) FLASH MEMORY: Flash memory is used for easy and fast information storage in such devices as digital cameras and home video game consoles. It is used more as a hard drive than as RAM. External memory(secondary memory) Secondary memory is a permanent memory. When you work on a computer and want to keep the data and information safe for future use, you have to use the secondary memory. RAM or ROM are of no use for this purpose. So the secondary memory is used to store the documents, files or pictures on the computer for future use. Some of such devices are floppy disk, hard disk, magnetic tape, CD, etc. Floppy disk Floppy disk is made of a plastic film coated with a ferromagnetic material and covered with a flexible plastic jacket. Data is stored in concentric rings. These rings are known as “tracks. Magnetic tape A magnetic type is used for storing a large amount of data. A magnetic tape is a long, magnetic, coated, plastic strip. It is housed in a plastic jacket. It is a common storage device on most of the mainframe computers and some personal computers. It is an optical circular plate
made of plastic. Cd-rom stands for compact disk read only memory. It needs a cd rom drive to run. CD-RW Cd-rw stands for compact disc rewritable. These discs are used for read and write data and information repeatedly. You can write your data or information on a cd-rw and after using it you can erase it from a cd rw. Now it is blank again, you can now again write your data or information onto it. Optical disks are more expensive and less reliable than magnetic media. In addition, the disk access time for re writable optical disks is slow as compared to magnetic media. Arithmetic-Logic Unit (A.L.U) The ALU contains the electronic circuitry that executes all arithmetic & logical operations. Arithmetic operations Ex: +, -, *, %.Logical operations Ex: Compare, True, False, =, >, < etc. Control Unit The control unit contains electronic circuit that uses electrical signals to direct the entire computer system to carry out, or execute, stored program instructions; rather it directs other parts of the system to do so. It should communicate both the ALU & Memory.
Output This is the process of producing results from the data for getting useful information. An output device displays information on a screen, creates printed copies or generates sound. A monitor, printer, and speakers are examples of output devices.
Some Common Output Devices: • Monitors and Displays •
Shows the processed information on a screen o A monitor uses a Picture Tube like a television with the image displayed on the front of the tube, which is called the screen.
o
• •
Displays are flat and use plasma, LCD, active-matrix, or some other technology.
Monitors used to be called Cathode Ray Tubes (CRTs) because of the picture tube, which is a large vacuum tube. A monitor or display produces a soft copy. When the device is turned off the information goes away.
•
Printers
• • •
Printers produce a hard copy The information is printed on paper and can be used when the device is off. It is also called a printout
•
Dot-matrix printers (impact printer)
o o o o o
•
Uses metal pins to strike an inked ribbon to make dots on a piece of paper. Can see the dots that make up the letters or images. Lowest print quality of all of the printers. Very low in cost per page to use. Rarely used today because of the poor print quality, but still used in business to print multi-part forms.
Ink jet printers (non-impact printer)
o
Use drops of magnetic ink to produce dots on a page to produce text or images.
o o
o o
•
The print quality is almost the same as laser printers. Problems with the ink The ink is very expensive The ink is water soluble and will run if the paper gets wet Highest cost per page of all the printers For producing color documents, it has the highest quality at a reasonable price.
Laser printers (non-impact printer)
o
o o o o
How the laser printer produces an image A laser or LEDs make dots on a light sensitive drum Toner (very tiny particles of plastic) stick to the drum where the dots where made Paper is pressed against the drum and the toner is placed on the paper The paper is heated and the toner melts into the paper Produces the highest quality printout For black and white printouts, very low cost per page Printout is permanent Color laser printers are still fairly expensive.
• Speakers •
used to output sound
Storage The process of saving data and instructions permanently is known as storage. Data has to be fed into the system before the actual processing starts. It is because the processing speed of Central Processing Unit (CPU) is so fast that the data has to be provided to CPU with the same speed. Therefore the data is first stored in the storage unit for faster access and processing. This storage unit or the primary storage of the computer system is designed to do the above functionality. It provides space for storing data and instructions. The storage unit performs the following major functions: • •
All data and instructions are stored here before and after processing. Intermediate results of processing are also stored here.
CLASSIFICATION OF COMPUTER Computer can be classified in following categories. • • • •
Micro Mini Mainframe Super computer
MICR0 COMPUTER Microcomputers are the smallest digital computers and are commonly known as Personal Computers (PC). These computers use microprocessors as their CPU. They can be used on the network or as standalone systems. These computers have high processing power and are slowly beginning to replace mini and mainframe computers. There are two models of microcomputers, the desktop and the portable model.
MINI COMPUTER They are more powerful than micro and they can support several users. They have larger RAM and backing storage capacity and can process data more quickly. Mini computers were basically developed for use in process control system. PDP-8, PDP-11 and VAX 7500 are its examples.
MAIN FRAME These are very large computers with a very high capacity of main store. They are able to accept any high level language. For they can process large amount if data very quickly, they are used by big companies, bank etc. They can be linked into a network with smaller departmental computer, microcomputer or with each other. They act as host of large national and international communication networks, handling hundred of users. IBM4381, ICL 39 are its example.
SUPER COMPUTER They are the most powerful computers. They use several processors working simultaneously. Complex scientific application like weather forecasting requires a large amount data to be manipulated with in a very short time. CRAY XMP-24, PARAM-10,000. PARAM can perform one hundred million calculations in a second. It can forecast of 15 days before whether in four minutes and five second.
Elements of the computing process • • • • •
Hardware Software Data People Procedure
Hardware A personal computer is made up of computer hardware, multiple physical components onto which can be loaded into a huge number of software that performs the functions of the computer. Basically hardware refers to those components of a computer system which can not only be seen but can be touched as well. Though a PC comes in many different form factors, a typical personal computer consists of a case or chassis (cabinet) in a tower shape (desktop) and the following parts: o Motherboard o Power supply
o o o o o o o
Video display controller Removable media devices Hard disk drives Sound card Modem Input devices Output devices
Software Computer software provides instruction that tell the computer how to operate. Computer software or just software is a general term used to describe a collection of computer programs, procedures and documentation that perform some tasks on a computer system. Software refers to those components of a computer which cannot be touched. There are two types of software as follows…
SYSTEM SOFTWARE APPLICATION SOFTWARE
• SYSTEM SOFTWARE
It includes the computer programs that run or assist to run a computer system itself. System software designed to control the operation of a computer system. System software consists of:-
(a) OPERATING SYSTEM (b) UTILITIES
• APPLICATION SOFTWARE
Application software, also known as application package, is a set of one or more programs designed to carry out operations for a specified application.
Operating Systems
Operating Systems run the computer and provide us with an interface with which to issue commands to the computer system. There are many varieties of operating systems. The differences between them stem from the hardware they were originally designed to run on and who engineers and manufactures the software. IBM Produced a number of operating systems including OS/360 for mainframes and OS/2 for desktop and personal computers. To sell their Personal Computers (PC's), IBM contracted with Microsoft to install a variation of "Quick and Dirty DOS" on all IBM PC's sold. This "Quick and Dirty DOS" became Microsoft DOS. Microsoft is the most well know manufacturer of operating systems today. Microsoft got their lead by making an exclusive deal with IBM in 1981 to include their variation of "Quick and Dirty DOS" operating system on every "IBM Personal Computer" microcomputer sold. Ever since that time, the vast majority of Intel-processor based computers have shipped with Microsoft operating systems on them. The most recent version is Microsoft Windows 7. AT&T produced what has become the System V version of the UNIX operating system designed to run on Digital Equipment Corporation computers (PDP-11). LINUX or "Linus Torvald's UNIX" was inspired by UNIX, but written for the Intel processor. Apple Computer Corporation produces MacOS and OS X exclusively for their Macintosh computers (which run on Motorola processors). Sun produces Solaris UNIX for their Sparc (their own chip design) and Intel based Sun platforms. HP produces HP-UX for their computers.
CLASSIFICATION OF OPERATING SYSTEM Operating systems a can be classified as follows:-
MULTI-USER- Multi-user operating systems allow two or more users to run programs at the same time.
MULTIPROCESSING- Multiprocessing refers to a computer system’s ability to support more than one process at the same time. Multiprocessing operating systems enable several processes to run concurrently. MVS and UNIX are two of the most widely used multiprocessing systems.
MULTITASKING- Multitasking allows more than one program to run concurrently. Multitasking is the ability to execute more than one task at the same time, a task being a program. There are two basic type of multitasking:(a) Preemptive (b) Cooperative
MULTITHREADING- Multithreading allows different parts of a single program to run concurrently. Multithreading is the ability of an operating system to different parts of a program, called threads, simultaneously.
execute
REAL-TIME- Real
time operating systems are systems that respond to input immediately. This category includes operating systems designed substantially for the purpose of controlling and monitoring external activities with timing constraints. Some examples of real-time operating system are C Executive, CCP, CTRON , and DESERT etc.
Data Data refers to a collection of information or facts usually collected as the result of experience, observation or experiment, or processes within a computer system. This may consist of numbers, words, or images, particularly as measurements or observations of a set of variables. Data are often viewed as a lowest level of abstraction from which information and knowledge are derived.
People Computers were designed by people and occasionally require maintenance by people. Most computers require people, who are called users
Procedures Procedures are the steps that the user has to follow to accomplish a specific computer related task.
TALLY INTRODUCTION TO TALLY ***************************************************** Tally is a versatile accounting a Package. Which provides user for voucher entry transaction of related Business? Under this package user has to pass voucher entry and complete all other works like. Ledger posting Profit and Loss & Balance Sheet stock summary done by tally package. So it is most beneficial and time saving for user. The advantage of this package is simplicity, flexibility and scalability.
Process of financial Accounting Package The document used in Financial Accounting Package is
Voucher
Journal
Ledger
Rules for Accounting 1-Assets – It is real accounts. It is increasing then becomes debit if decreasing then cr. 2-Liabilities- It is also real account. If it is increasing then becomes credit if decreasing then debit. 3-Expenses –It is nominal accounts. What are the business expenses during the year? 4-Income –It is also nominal accounts. What the Business earn during the period. Basic Steps in Accounts Compilation, documents used and Report.
Transaction
Recording Voucher
Classifying Journal Entry
Posting Ledger accounts
Consolidating Trial Balance
Report Generation P/L
B/S
The topic is interpreted as either an accounting package accessible through a windowing interface or one that runs on Microsoft windows operating system. Either way, Tally fits the description. All screen in tally are viewed as windows processing the following-A title bar that displays the name of the application -A tally icon to the left that pulls down the menu comprising restore, move, size, minimum and maximum menu item. The visible advantage of the accounting package is simplicity, flexibility, speed, scalability, and real time access to data across location and even with other applications.
Types OF ACCOUNTS Debit
Personal accounts The receiver
Real accounts What comes in
Nominal accounts Expenses and losses
Credit
The giver
What goes out
Incomes and gains
TALLY START UP -Click on Start>Programs>Tally 9 -Double click on the Tally9 Icon on the desktop. The Welcome screen,’ Tally-Power of simplicity’ appears while the software loads. When the Tally start up screen appears, you are ready to explore Tally.
SETTING UP A NEW COPMANY Company A company is basically a file where transaction relating to a particular company are entered.
Creating a company
The company Info screen has five options: 1- Select Company ,to select an existing company from the list of company already created 2- Create Company is to create new one. 3- Backup is to take a backup of stored data. 4- The option Restore is to extract information from the backup device. 5- The last option quit is to exit from Tally.
Selecting Create Company option brings us to the screen given below:
Each of the field in the Company Creation screen is explained below.
Name: It is mandatory to enter the name if the company. Mailing Address: Fill in the Company address details. It is Optional to enter the Mailing address of the Company.
State: Select the appropriate state from the list of state. Pin code: Enter the Pin code of the specified address. This field is optional.
E-mail Address: E-mail address will be used to e-mail document, reports and data from Tally. This field is optional.
Currency Symbol: Tally displays Rs. by default. This cannot be modified at the time of the creation of the Company. Maintain: The field is used to specify which section of Tally your company would like to use. Tally displays two options –Account only and Accounts with Inventory.
Financial year: This field is used to specify the financial year for the company. Tally Vault Password (if any): Tally vault is a facility to keep the protected using a password. Once the Password is set, data is maintained permanently in an encrypted form. This field is optional.
Use security control: Tally has a range of security controls, which can define the authority of various users to access the information and to enter/alter/delete/the vouchers. This field is optional.
BASE CURRENCY INFORMATION
The completed Company Creation screen appears shown.
Tally will take you to the “Gateway of Tally” screen that appears as shown.
F11: FEATURES F11: feature is used to modify the various features of a company. The company features are specific to the current company only and thus each company may have different features activated.
The company features screen is as shown.
F12: Configure
Master Configuration: Master Configuration is used to set the details that should appear in the accounts masters. This is available in the accounts and inventory masters screens through the F12: Configure button and can be modified as per requirement.
Set the Master Configuration as shown: GO to Gateway of Tally >F12: Configure>Accts/Inv Info
Voucher Configuration Go to Gateway of Tally>f12: configure> voucher entry.
Ledgers: Tally provides a set of 28 pre-defined groups under which the ledger can be classified -Primary groups – 15
-Sub groups – 13 Out of the pre-determined primary groups, -Nine primary groups appear in the Balance sheet, which are capital in nature. -Six primary groups appears in the Profit and loss Account, while are revenue in nature.
All the 13 sub-groups are classified under the nine primary groups that are capital in nature and they appear in the Balance sheet.
Tally has pre-defined ledgers for Cash (under cash –in –hand group) and for the Profit and Loss account (under primary group).
Create single ledger: GO to gateway of Tally >Accounting Info >Ledger >Single Ledger>Create to bring up the Ledger creation screen. Ledger A/c can also be created from the Voucher screen by using the shortcut key Alt + C
Alter Ledger: You can alter ledger details using either single or multiple modes. You are not allowed to delete a ledger from multiple modes. Press key Alt + D to delete ledger under single alteration modes.
Display ledger: You can display ledger in either single or multiple modes. Under multiple modes you can display all ledgers by choosing “All items” in “Under groups” option. You can also specify the particular group of the ledger you want to display.
FUNCTIONS Accounting Vouchers: Tally is pre-programmed with a variety of accounting vouchers, each designed to perform a specific job. The standard vouchers are
• Contra Voucher • Payment Voucher
• Receipt Voucher • Journal Voucher • Sales Voucher Contra Voucher: Go to Gateway of Tally>Accounting Voucher>F4: Contra to display the Contra Voucher.
Payment voucher: Gateway of Tally > Accounting Voucher >F5: Payment display the Payment Voucher.
Receipt Voucher: Gateway of Tally > Accounting Voucher > F6: Receipt display the Receipt Voucher.
Sales Invoice: Go to Gateway of Tally > Accounting Voucher > F8: Sales display the Sales Invoice.
Financial Reports in Tally:
Balance sheet: The balance sheet is a financial statement that indicates the financial strength of the company at any given point. It lists the assets and liabilities of a company. Go to gateway of Tally > Balance sheet.
Profit and loss Account: The Profit and loss A/c is a financial Statement that describes the operational results of the company or it’s earning capacity for a given period. It lists the
company incomes and expenditure. No special processing is required to produce a profit and loss A/c in Tally. Go to Gateway of Tally > Profit and Loss A/c.
Cash/Bank Books: To view the Cash/Bank summary Go to Gateway of Tally > Display>Accounts Books >Cash/Bank Books.
Ledger: To view the Ledger summary Go to Gateway of Tally > Display > Accounts Books > ledger >Select ledger A/c
Group summary: The group summary statement shows the closing balances of the accounts of a selected group for a specified period up to the current date. Go to Gateway of Tally > Display > Accounts books > Group summary.
Group voucher: The group Voucher feature lists all vouchers entered in any ledger of the selected group. To view group voucher. Go to Gateway of Tally > Display > Group voucher > Select group.
Journal registers: Journal registers report display all the journal vouchers that have been passed. To view journal register details Go to Gateway of Tally > Display > Accounts books > journal register.
Day book: The day book s a list of all transaction for a particular day. Day book, by default, uses the current data. To view Day book Go to Gateway of Tally > Display > Day book.
List of Accounts: List of accounts display the existing chart of accounts , listed as group , in alphabetical order. To view list of accounts
Go to Gateway of Tally > display > List of Accounts.
Financial analysis tool; Cash flow / Fund flow statement: Cash flow and fund flow statement help the management in the decision making process.
Ratio Analysis: We can analyze the accounts of our company using ratio analysis. Ratio analysis is useful for management in the decision-making process. We can even analyze different outstanding of parties using Receivables turnover ratio etc. We can display the age-wise statement in Tally based on the receivables or payables. The age wise analysis can be done either on bill date or due date. Press F6 key in the display of bills Receivable or Bills payable report. Go to Gateway of Tally > Ratio analysis
List of function keys
KE FUNCTIONALITY Y
AVAILABILITY
F2
To change the date.
At almost all screens in tally.
F4
To select the Contra voucher
At accounting voucher creation and alteration screen
To view the list of Groups.
At the trial balance, cash\bank summary, group summary and group voucher screens.
To view the list of ledgers.
At the ledger voucher screens
To change voucher type.
At the journal register and daybook screens.
To select the payment voucher
At accounting voucher creation and alteration screens.
F5
To switch between grouped At the trial balance, cash/bank summary and group and ledger-wise display. summary screens. F6
To select the receipt voucher.
At accounting voucher creation and alteration screens.
F7
To select the journal voucher.
At accounting creation and alteration screens
To view summary.
the
monthly At the ledger voucher screen.
To switch between group At the group summary and group voucher screens. summary and group vouchers. F1 0
Navigate between accounting At the reports screens, Trial Balance, account books reports. and daybook screens.
F1 1
To select the feature screen.
At almost all screens in Tally
F1 2
To select the configure screen.
At almost all screens in Tally.
Special function Key Combination ALT+F1
To close a company.
At all menu screens.
To view a detail report.
At almost all report screens
To explore a line into its details.
At almost all screens.
ALT+F2
To change a period.
At almost all screens.
ALT+F3
To select the company info menu.
At gateway of tally screens.
ALT+C
To create a ledger at a voucher At accounting voucher and alteration screen. screen.
ALT+N
To add multiple column to a reports
At all report, trial balance, cash bank book and Journal register screen.
ALT+P
To print report.
At all report screen.
CTRL+Q
To quite a screen.
At almost all screen.
EXAMPLE
Transaction for April 2009
1- Opening balances:Ram material Dvd drives L.G dvd drives 125 pcs @ Rs.2800 Samsung dvd drives 200 pcs @ Rs.2400 Hard disk drives 20 gb H.D.D 150 pcs @ Rs.1900 40 gb H.D.D 240
pcs @ Rs.2700
80 gb H.D.D 380 pcs @ Rs.3600 Moniters Crt moniters 75 pcs @ Rs.5600 Flat screen monitor 145 0cs @ Rs.9700 Ram chips
1 gb 125 pcs @ Rs.520 512 mb 350 pcs @ Rs.460 Cabinets 225 pcs @ Rs.750 Keyboard 450 pcs @ Rs.270 Mouse 250 @ Rs.250 Finished goods High end computers 100 pcs @ Rs. 32000 Medium level computers 20 pcs @ Rs. 25000 Entry level computers 70 pcs @ Rs. 18000 Current assets Cash Rs.200000 Bank Rs.250000 Creditors Arvind tech mart Rs.250000 Sigma enterprises Rs.13000 Mukul tech hub Rs.48000 Debtors Gupta tradeRs. Rs.2500 Ankush transport Rs.700 Icai Rs.85000 Nikhil Rs.18000 Heena Rs.7000 Rupali Rs.5500 Fixed assets Furniture Rs.150000, depreciation @ 15% Generator Rs.75000, depreciation @ 10%
Building Rs.800000, depreciation @ 20% Investments Fixed deposits Rs.400000 RNRL shares Rs.80000 ONGC shares Rs.65000 Secured loan Loan from bank 765000 8% debentures 1518000 Current liabilities Sales tax payable 265000
2- Transactions entered:i. ii. iii. iv.
Cheque received from ICAI Rs. 85000. Order fro 3 high end computers received from heena order no 258 Cheque received from rupali 5500. 3 high end computers sold to heena as per her order Discount allowed 2% Sales tax 7%
v. vi. vii. viii. ix. x. xi. xii. xiii. xiv. xv. xvi.
xvii.
xviii. xix. xx.
Order received from ICAI for 18 entry level computers, 40 medium level computers, and 10 high end computers. Paid through cheque outstanding wages Rs 7800 and outstanding salaries 2500 Payment of salaries 8000 in cash Printing expenses 5000 Order from rupali fro 2 high end computers, 3 medium level computers, 1 entry level computers Rs. 15000 withdrawn from bank for personal use Goods sold to rupali as per her order Cash received from heen 7000, ankush 700, gupta trader 2500 Goods sold to ICAi as per order discount allowed 5% and sales tax 7% Payment received from ICAI Sales tax payable paid Rs 20000 Cash deposited in bank Rs. 100000
Payment made through cheque Arvind tech mart Rs. 200000 Sigma technologies Rs. 10000 Mukul tech hub 45000 Sales tax paid through cheque 50000 Printing expenses Rs. 2000 through cheque Received from Rupali Rs. 100000 Heena Rs. 100000
Nikhil Rs. 18000
xxi. xxii.
Outstanding sales tax paid Rs. 65000 Order placed with mukul tech hub for lg dvd drives 125 pcs @ Rs. 2800, ram chips 1GB 150 pcs @ Rs. 525 and flat screen 80 pcs @ 9700, order no 125.
xxiii. xxiv.
xxv. xxvi.
Salaries paid in cash Purchased from mukul tech hub as per order order no 125 Discount received 4%
Sold 2 high end , 4 entry level and 1medium level computer for cash, discount allowed 6% Debit note issued to mukul tech hub for 30,1 GB ram chips, 75 l.g drives, 10 flat screens
xxvii.
Order from rupali order no.288 for 10 high end compters, 2 medium level computers, 5 entry level computers
xxviii.
Goods sold to rupali as per her order
xxix.
Credit note issued to rupali for 5 high end computers, 1 medium level computers, 3 entry level computers
xxx.
Depreciation on furniture, generator and building.
Ms Excel
To start Excel 2007 from the Start Menu Click on the Start button, point to All Programs then Microsoft Office and click on Microsoft Office Excel 2007.
EXCEL’ is a Window based ‘spreadsheet’, created by Microsoft Corporation, US. It includes all the standard features of a spreadsheet package like automatic recalculations,
graphs and functions. It also includes several advanced utilities like the facility to include other objects within a spreadsheet and pivot tables and form designing. Excel can be used for a variety of applications. It is mostly used to automate financial statements, business forecasts, inventory control and accounting. The package spreadsheet provides statistical, financial and scientific functions. Therefore, Excel can be used in many scientific, accounting and engineering applications to analyze numeric data. Excel is currently the most popular Window spreadsheet.
Spreadsheet originally existed in the physical world as a piece of paper that you could actually write on. These paper spreadsheets were used mainly by Accountants to help organize data in a logical and easy to view manner. However, the spreadsheets of the Information Age reside on your computer, though they still have the basic form of the old spreadsheets.
Starting Excel: To invoke Excel choose any of the following three methods: Click on – Start button – Programs menu – select MS Excel. Click on – Start button – Programs menu – MS Office – MS Excel
Excel Window:
The Spreadsheet: The spreadsheet (or worksheet) contains or will contain all the data that we enter. When we start up Excel, we are having a blank spreadsheet like the one we have below:
Parts of the Spreadsheet: Nearly every part of the spreadsheet has its own term and it is quite useful to know our spreadsheet vocabulary to make using Excel that much easier. There are three basic parts of an Excel Spreadsheet:
Column - The vertical segments that you see on the spreadsheet are called columns. Here “A”,”B”,……………………. ,“ J” Excel uses letters to represent columns and numbers to represent rows. This may be a little confusing at first, but with practice and a little effort this will become second nature to us.
Row - The horizontal segments are referred to as rows. Excel uses numbers to label rows. Here 1, 2, 3, 4……..., 16 are rows.
Cell - Each box that is created from a row and column intersecting is referred to as a cell.
Excel Basics: Create a New Workbook Before we can start learning to use Excel, we need to create a new workbook that will store our data. To create a new Excel workbook follows these steps: Open Microsoft Excel With the mouse, go to the Excel menu bar and left-click at “New”.
Excel Functions: Using our soccer goal Excel spreadsheet from the previous lessons, let's use Excel functions to figure out what the total number of goals scored by our team. Open up the soccer goal spreadsheet and follow these steps: In
cell
A9
enter
the
text
Total
and
make
it
bold
Select cell A9 and locate the formula bar (it's right under the Excel menu bar) and notice that it contains the content of cell B9, which just so happens to be nothing! Let's fix that: -
Notice the little fix to the left of the formula bar. This is the insert function button.
Left-click the fix button to bring up the insert function popup
Using the Excel "Search for a function" feature type in the text "Sum" and click “Go”.
Make sure SUM is selected and click OK to bring up the Function Arguments Window.
Before we go any further let's make sure to understand what we have done so far. First we selected a cell that we wanted to have our total goal count appear in. Next we inserted a function and chose the SUM function. We've done quite a lot and the only thing that remains is to select which numbers we want to sum up. So let's finish this up! Move the Function Arguments Window so we can see the goals scored. We want to sum up all the goals scored, so we need to select all the cells that contain goals scored. Starting at the top of the list, Tim's goals, left-click and hold in Cell B2 then drag down to cell B7, Jason's goals and finally release left-click. Our screen should now look like this:
Click OK and admire your work.
Merging Cell: Excel 2000 allows merging data in one cell with adjacent cell to form a big cell. For merging cell follow the following steps. Enter the title in the upper left all of the range to enter multiple titles. Select the range in which the title has to be placed. Click cell from the format menu. The format cell dialog box appears. Click the Alignment cell. Click the merge cell check box and then click ok.
Inserting and Deleting Cells
Follow the following Steps. Select the area where the new cell is to be inserted. Chose cell from Insert menu. The Insert dialog box appears. Select shift cell down or shift cell Right and then click OK. To delete the cells completely follow the following steps. Select the cell or range of cells to be deleted. Chose delete from the edit menu. The delete dialog box appears. Select shift cell left or shift cell up and then click Ok.
Pivot table: Pivot Table is a powerful tool for data analysis. A Pivot table summarizes the column of information in a database in relation ship to each other. Pivot table are excellent tool for working with data in three-dimensional manner.
Steps for creating Pivot Table: Select data menu. Select Pivot Table.
Select the external data source option. Press the next button. Press finishes button and Then Ok.
CELL REFERENCING: A reference identifies a cell or a range of cells on a worksheet and tells MS-Excel where to look for the values or data we want to use in a formula. With references, we can use data contained in different parts of a worksheet in one formula or use the value from one cell in several formulas. We can also refer to cells on other sheets in the same workbook, and to other workbooks. References to cells in other workbooks are called links. Referencing is of three types:
Relative Cell References-This is the most widely used type of cell reference in formulas. Relative cell reference is basic cell references that adjust and change when copied or when using AutoFill.
Absolute Cell References- Situations arise in which the cell references must remain the same when copied or when using AutoFill. Dollar ($) signs are used to hold a column and/or row reference constant.
Mixed Cell References- We can also create a mixed reference, making part of a cell address absolute and part relative, by locking in either the column or the row. Use mixed references when we want to copy a formula down and across and to have a reference change relatively in one direction but not in the other. For example, E$5 will remain E$5 when copied down because the row reference is absolute, but it can change to F$5, G$5 and so on when copied across because the column reference is relative. Using Passwords/Protecting Worksheets: If the workbook contains confidential information; a password can be applied to it. This will allow only users with the password to open and view the workbook. To protect a workbook following steps are to be followed: Open the workbook and select Save As from the File menu. In the Save As dialog box, click the Tools button to display the drop-down menu. Select General Options. Enter the password in the Password to open box. Enter the password in the Password to modify box. Click OK. In the Confirm Password dialog box, reenter the password for verification.
Click OK.
Specify a file name and click Save. If prompted, click Yes to replace the old version of the file with the new password protected version. Inserting A Chart: Select over the text we want to make our chart with
Click Insert --> Chart--> & Select the type of chart we want.
Confirm or change your data range. Update the Chart Options.
Visual basic Creating the Project First thing to do is to create a Directory where you will store all your VB Projects. Call it VBApps, for example. Then start VB. The first screen will ask whether you want to open a new project or an existing one - it's obviously a new one and it will be a Standard EXE. Then, maximize all the windows (it's easier to work with - some of the examples in the tutorial had to be reduced for the sake of the presentation). Now, save your project. It will first ask you to save the form - call it Score.frm - and then the Project - call it Scorebrd.vbp.
The Tool Bar The tool bar contains the tools which are frequently used. Tool bar contains does not contain all the tool tools it contains some important tools used for making the project.
The tool box Tool box contains the controls that are used on a form. A control is an object such as a button, label or grid.each control appears as a button in the tool box. It contains the tool used for creating and editing the form.
Code Window In this window, user is provided with a kind of white sheet where te user can write his codes for the desired program.
Form designer window This window is where user designs the forms that make up the user interface
Properties window
A form, and each control on it, has a set of properties which control its characteristics Such as size, position and color.
Project explorer window
To change the color, just click anywhere on the form, go to the properties window, find the property called Back Color and change it to the standard Window background (teal) or to any color you want in the palette. To get a control you go to the Toolbox, click on the control you want, come back to the Form and click and drag the control to the size and position you want. Position the controls somewhat like in the diagram below.
The Label This is probably the first control you will master. It is used to display static text, titles and screen output from operations. The important properties to remember: • • • • •
Caption - the text that is displayed in the label BackColor and ForeColor - colors of the background and the text BackStyle - Opaque or Transparent - whether the background is visible or not Font - font and size of text Alignment - text centered, left or right
•
Multiline- True or False - if True, you can have several lines of text, delimited by in the label - by default, it is set to False
Frame Picture Box When you want to group several controls together - name and address, for example - you use a Frame. The frame back color can be the same as the form's and only the frame borders will be obvious, or it can be a different color and stand out. You create the frame before the controls. When you create controls in a frame, they are tied to the frame and move with it. The frame caption is the text that appears at the top of the frame you use it to define the group. The Picture Box is like a Label with a picture in it instead of text. The Picture property determines the name of the file, .BMP or .GIF that will be displayed. It can be used for a company logo, etc.
Textbox Command Button The Textbox is like a Label but, it is used to input data into the program. The data typed in is in the Text property of the control. When the program is Run, only the controls that can be manipulated will be activated. For example, if the form contains 3 Labels, 3 Textboxes and 3 Buttons, when it is Run, the cursor will not stop at the labels. When the user hits the Tab key, the cursor will go to the first Textbox or Button - not necessarily the first one on the form but, the first one that was created. That is called the Tab order and you have to specify it. On the form there is only one control at any given time that has the cursor on it - it is said to have Focus. If you type data, the control with Focus will receive it. You change the Focus with Tab or by clicking on a different control.
The Text Box
The text box is the standard control that is used to receive input from the user as well as to display the output. It can handle string (text) and numeric data but not images or pictures. String in a text box can be converted to a common numeric data by using the function Val (text).
The List Box The function of the list box is to present a list of items where the user can click and select the items from the list. In order to add items to the list, we can use the Additem method.
The Label The label is very useful control for Visual Basic, as it is not only used to provide instructions and guides to users, it can also be used to display outputs. One of its most important properties is Caption, Using the syntax label. Caption, it can display text and numeric data.
The Combo Box The function of combo box is also to present list of items where the user can click and select the items from the list. However, the user needs to click on the small arrowhead on the right of the combo box to see the items which are presented in a drop-down list. In order to add items to the list, we can also use the Additem method.
Using If….Then….Else Statement To effectively control the VB program flow, we shall use the If….Then….Else statement together with conditional operators and logical operators. If <> Then Expressions Else Expressions End If
Select Case Statement If you have a lot of conditional statements, using If….Then….Else could be very messy. For multiple conditional statements, it is better to use Select Case.
For….Next Loop The format isFor counter = start Number to end Number (Step increment/decrement) One or more VB statements Next
Do While Loop The format areDo while <> Block of one or more VB statements Loop Do Block of one or more VB statements Loop While <> Do Until <> Block of one or more VB statements Loop Do Block of one or more VB statements Loop Until <>
Practical Problem
Creating a basic calculator using visual basic
Coding:Dim a As Double Dim b As Double Dim c As String Dim d As Double Dim e As Double
Codes for addition button Private Sub Command4_Click() b = Text1.Text c = "+" Text1.Text = "0" End Sub
Codes for subtractions button
Private Sub Command16_Click() b = Text1.Text Text1.Text = "0" c = "-" End Sub
Codes for division button Private Sub Command18_Click() b = Text1.Text Text1.Text = "0" c = "/" End Sub
Codes for multiplication button Private Sub Command17_Click() b = Text1.Text Text1.Text = "0" c = "*" End Sub
Codes for percentage button Private Sub Command1_Click() g = Text1.Text Text1.Text = (b * g / 100) End Sub
Codes for equals to button Private Sub Command6_Click() d = Text1.Text Select Case c Case Is = "+" e=d+b Case Is = "-" e=b-d
Case Is = "*" e=b*d Case Is = "/" e=b/d End Select Text1.Text = e End Sub
Codes for exit button Private Sub Command2_Click() Unload Me End Sub
Codes for “c.e” button Private Sub Command7_Click() Text1.Text = "0" Text1.SetFocus End Sub
Codes for 1 button Private Sub Command3_Click() a = Text1.Text Select Case a Case Is = 0 Text1.Text = "1" Case Else Text1.Text = a & "1" End Select End Sub
Codes for 2 button Private Sub Command5_Click() a = Text1.Text Select Case a
Case Is = 0 Text1.Text = "2" Case Else Text1.Text = a & "2" End Select End Sub
Codes for 3 button Private Sub Command14_Click() a = Text1.Text Select Case a Case Is = 0 Text1.Text = "3" Case Else Text1.Text = a & "3" End Select End Sub
Codes for 4 button Private Sub Command8_Click() a = Text1.Text Select Case a Case Is = 0 Text1.Text = "4" Case Else Text1.Text = a & "4" End Select End Sub
Codes for 5 button Private Sub Command9_Click() a = Text1.Text Select Case a Case Is = 0 Text1.Text = "5"
Case Else Text1.Text = a & "5" End Select End Sub
Codes for 6 button Private Sub Command10_Click() a = Text1.Text Select Case a Case Is = 0 Text1.Text = "6" Case Else Text1.Text = a & "6" End Select End Sub
Codes for 7 button Private Sub Command11_Click() a = Text1.Text Select Case a Case Is = 0 Text1.Text = "7" Case Else Text1.Text = a & "7" End Select End Sub
Codes for 8 button Private Sub Command12_Click() a = Text1.Text Select Case a Case Is = 0 Text1.Text = "8" Case Else Text1.Text = a & "8"
End Select End Sub
Codes for 9 button Private Sub Command13_Click() a = Text1.Text Select Case a Case Is = 0 Text1.Text = "9" Case Else Text1.Text = a & "9" End Select End Sub
Codes for 0 button Private Sub Command15_Click() a = Text1.Text Select Case a Case Is = 0 Text1.Text = "0" Case Else Text1.Text = a & "0" End Select End Sub
