Full project

Icai icitss Project report on microsoft excel

1. 1

THE INSTITUTE OF CHARTER ED ACCOUNTANTS OF
INDIA






PROJECT REPORT ON MICROSOFT EXCEL











Submitted By:
Rajat Kumar
Roll No: 230-06
May 2019

Submitted To:
Mrs Suman Bala

2. 2

Contents
Serial No. Topics Page No.
1 Introduction 3
2 Details of Spreadsheet 4-9
3 Modifying Cells 10-11
4 Functions and Formulas 12-13
5 File Tab 14-15
6 Pivot Table 16
7 Macros 17
8 Use of excel in charted
accountancy profession
18-19
9 Use of Excel in Auditing 21-22
10 Excel in Data Analysis 23
11 Use of Excel in Taxation 24
12 Using IF Function 25-27
13 Use of Excel in Data Entry 28
14 Advantages 29
15 Disadvantages 29
16 Bibliography 30

3. 3

INTRODUCTION TO MS -EXCEL
o Microsoft Excel is a proprietary commercial spread sheet application.
It is a computer program used to create electronic spreadsheets.
o Excel is a convenient program because it allows user to create large
spreadsheets, reference information, and it allows for better storage of
information.
o It is an electronic spread sheet program that can be used for
programming, storing, organising and manipulating data.
o Excels operates like other Microsoft (MS) office programs and has
many functions and shortcuts similar to other MS programs.
o It features calculations, graphing tools, pivot tables and a macro
programming language.
o It has features of calculation, graphing tools and pivot tables etc.
o For MS EXCEL 2010, row numbers ranges from 1 to 1048576.
o In total =1048576 Rows.
o Columns range from A to XFD.
o Microsoft Excel has the basic features of all spread sheets, using a
grid of cells arranged in numbered rows and letter-named columns to
organize data manipulations like arithmetic operations.
o It has a battery of supplied functions to answer statistical, engineering
and financial needs. In addition, it can display data as line graphs,
histograms and charts, and with a very limited three-dimensional
graphical display



Submitted To:
Mrs Suman Bala

4. 4

Details of spreadsheet
 LAYOUT OF SPREADSHEET


 Workbook
Also called a spread sheet, the workbook is a unique file created by Excel XP.
 Title bar
The title bar displays both the name of the application and the name of the
spreadsheet.

 Menu Bar
The menu bar displays all of the menus available for use in Excel XP. The
contents of any menu can be displayed by left-clicking the menu name.

5. 5


 Toolbar
Some commands in the menus have pictures or icons associated with them. These
pictures may also appear as shortcuts in the toolbar.

 Column headings

Each Excel spreadsheet contains 256 columns. Each column is named by a letter
or combination of letters.
 Row headings

Each spread sheet contains 65,536 rows. Each row is named by a number.

6. 6


 Name box

This shows the address of the current selection or active cell.
 Formula Bar

The formula bar displays information entered—or being entered as you type—in
the current or active cell. The contents of a cell can also be edited in the formula
bar.
 Cell

A cell is an intersection of a column and row. Each cell has a unique cell address.
In the picture above, the cell address of the selected cell is B3. The heavy border
around the selected cell is called the cell pointer.
 Navigation Buttons And Sheet Tab

7. 7

Navigation buttons allow you to move to another worksheet in an Excel workbook.
They are used to display the first, previous, next, and last worksheets in the
workbook.
Sheet tabs separate a workbook into specific worksheets. A workbook defaults to
three worksheets. A workbook must contain at least one worksheet.
 Ribbon
The Ribbon replaces the menu & toolbars found in earlier versions of EXCEL.
Above the ribbon are a number of tabs, such as Home, Insert & Page layout.
Clicking on a Tab are number of groups that displays the commands located in this
section of the RIBBON.
 Representation of Ribbon

1. TABS: There are seven tabs across the top of the excel window.
2. GROUPS: Groups are sets of related commands, displayed on tabs.
3. COMMANDS: A command is a button, a menu or a box where you enter
information.

 Navigate within worksheets
To navigate within a workbook, you use the arrow keys, Page Up, Page Down, or
the Ctrl key in combination with the arrow keys to make larger movements. The
most direct means of navigation is with your mouse. Scroll bars are provided and

8. 8

work as they do in all Windows applications. Go ahead and try moving between
cells in your newly opened Excel document with your mouse and then the Page Up
and Page Down keys.

 Navigate between worksheets
To move to other Worksheets, you can Click their tab with the mouse at the bottom
of the screen (Sheet1, Sheet 2, or Sheet 3) or use the Ctrl key with the Page Up and
Page Down keys to move sequentially up or down through the worksheets. Go
ahead and switch between your 3 sheets using the different methods described.

 Insert, move, and rename worksheets
Worksheets are much like pages within a book; you peruse through them like you
flip the pages of a book. There are several ways to move and copy worksheets.
Right click on the sheet tab and choose Move or Copy. Select a new position in the
workbook for the worksheet or click the Create a copy checkbox and Excel will
paste a copy of that worksheet in the workbook. The same shortcut menu for the
sheet tab also gives you the option to insert, delete or rename a worksheet.
 Navigation keystrokes

9. 9

 Select and move worksheet cells
To select a large area of cells, select the first cell in the range, press and hold the
Shift key, and then click the last cell in the range. Once you have selected a range
of cells, you may move the cells within the worksheet by clicking and dragging the
selection from its current location to its new one. To do this, bring your cursor to
the side of the selection. When your cursor turns into 4 arrows pointing into
opposite directions click and hold on to the mouse and drag where ever you want
to locate it and let go of the mouse.
By pressing and holding the Ctrl key as you drag, Excel will leave the original
selection in its place and paste a copy of the selection in the new location.
To move between workbooks, use the Alt key while dragging the selection

 Range selection techniques

10. 10

Modifying Cells
 Understanding text, values, and formulas
Information entered into cells is categorized as text, values or formulas. Values
must be numbers, though they can be formatted to appear on the screen as
currency or a percentage.

 Editing cells and entering expressions
You can edit a cell by selecting the cell and then clicking in the formula bar or by
double-clicking the cell to open the cell in edit mode.
Telephone numbers or social security numbers that contain other characters (like a
dash or parentheses) are treated as text and cannot be used in calculations.
Arithmetic operators are used in formulas.
 Inserting worksheet rows and columns
You can insert one or many additional rows or columns within a worksheet with
just a few steps using the mouse or menu options.
• You can insert individual cells within a row or column and then choose
how to displace the existing cells.
• You can click the Insert menu and then select row or column, or right
click on a row or column heading or a selection of cells and then choose
Insert from the shortcut menu.

 The Insert dialog box
This figure depicts the Insert dialog box, which appears when you select a range of
cells, right click on the selection and then choose Insert from the shortcut menu.

11. 11

 Delete worksheet rows and columns
• To delete and clear cells, rows, or columns, you can use the Edit menu, or right
click on a heading or a selection of cells and choose Delete from the shortcut
menu.
• Clearing, as opposed to deleting, does not alter the structure of the worksheet or
shift uncleared data cells.
• What can be confusing about this process is that you can use the Delete key to
clear cells, but it does not remove them from the worksheet as you might
expect.

 Deleting cells and ranges of cells
When you choose to delete a selection, a dialog box similar to the Insert dialog box
pops up, except that the first two choices are to Shift cells left or Shift cells up.
 Resize worksheet rows and columns
• There are a number of methods for altering row height and column width using
the mouse or menus:
– Click the dividing line on the column or row, and drag the dividing line
to change the width of the column or height of the row
– Double-click the border of a column heading, and the column will
increase in width to match the length of the longest entry in the column
• Widths are expressed either in terms of the number of characters or the number
of screen pixels.

12. 12

Functions & Formulas
• You can easily calculate the sum of a large number of cells by using a function.
• A function is a predefined, or built-in, formula for a commonly used
calculation.
• Each Excel function has a name and syntax.
– The syntax specifies the order in which you must enter the different parts
of the function and the location in which you must insert commas,
parentheses, and other punctuation
– Arguments are numbers, text, or cell references used by the function to
calculate a value
– Some arguments are optional

 Excel’s arithmetic operators

Formulas are expressions that are used to calculate a value.
• An expression can contain one or more arithmetic operators, such as plus,
minus, divide, or multiply
When more than one arithmetic operator is present, the calculation must follow
order-of-precedence rules, which determine which operator is applied first, second
and so forth.
The chart below illustrates Excel’s order of precedence and shows sample
expressions and the result of each expression.

13. 13


 Math and Statistical functions
This chart shows some commonly used math and statistical functions and a description of what they do.


 Define functions, and functions within functions
The SUM function is a very commonly used math function in Excel. A basic
formula example to add up a small number of cells is =A1+A2+A3+A4, but that
method would be cumbersome if there were 100 cells to add up. Use Excel's SUM
function to total the values in a range of cells like this: SUM(A1:A100).

14. 14

 Saving & Closing Excel
To Save the Excel document, SELECT the third icon from the left on the Toolbar
(it is supposed to look like a floppy disk). If you prefer, SELECT File on the Menu
Bar and then choose Save As from the menu. You will arrive at the same menu if
you choose the Save icon, or go through the File menu. Now, choose the Save As
commands. For this particular exercise, save the file as Atm_mass.xls on your Z
drive so you can have access to the saved file at another time.



There are two common methods to close a file. In the course of closing the
program, any file you have open will be closed. Or you can close a file without
closing the program. These two actions are represented by the two X's in the upper
right corner. The X in the very top right (in the Title Bar) will close the program,
Microsoft Excel. If you have not saved the file since you have made any changes,
it will ask you if you wish to save the file. The other X (in the Menu Bar or the File
Title Bar) will close the file, but not the program. It will prompt you to save the file
you have been working on.

15. 15

 REPRESENTATAION OF FILE TAB








 File button contains
To save a document.

To save a new document or save document
another place.

To open the existing document.

To close a document.

To show all the recent open files.

To open new worksheet.

To print the open document.

To save and send the document

In this we can change the settings of MS
excel.

To exit the MS Excel.

16. 16

 PIVOT TABLE
• PivotTable is a very powerful analysis tool built into MS-EXCEL. It helps in
analysing & summarizing large collections of data. Such data can be derived
from various sources.
• For creating a PivotTable, choose the PivotTable command from the Data
menu. The PivotTable wizard appears and takes you through the process of
creating PivotTable
• Step1: Specify the Data Source: We also specify whether we want to create a
PivotTable or a PivotChart report. Next to specify the data range.
• Step2: Define the Data Range: Ensure that the correct range of cells has been
included. Click the next button to specify the location of the PivotTable
• Step3: Choose the location of the PivotTable. Select New worksheet and
click Finish.

17. 17

 Macros
• If you perform a task repeatedly in Microsoft Excel, you can automate the
task with a macro. A macro is a series of commands and functions that are
stored in a Visual Basic module and can be run whenever you need to
perform the task.
• When you record a macro, Excel stores information about each step you take
as you perform a series of commands.
• You then run the macro to repeat, or "play back," the commands.
• There are two types of MACROS : Global Macros and Individual Macros.
• After you record a macro, you will usually run it in Microsoft Excel. To
interrupt the macro before it completes the actions you recorded, press ESC.
There are numerous ways in which a macro can be run:
1. from VB Environment
2. Through a shortcut key

18. 18

USE OF EXCEL IN
CHARTERED ACCOUNTANCY PROFESSION

1. Excel In Corporate Financial Statements
A firm’s financial health is summarized in three key financial reports.
(1) the income statement
(2) the balance sheet
(3) cash flow statement
Excel makes it possible to link cell entries in one worksheet to cell entries in
another so that changing data values on either worksheet changes related entries on
the other.
ANALYSIS OF FINANCIAL STATEMENTS
• Common-size income statements and balance sheets: These convert dollar
values to percentages of sales and assets. Common-size financial statements
are used for “vertical analysis” to see how items on financial statements are
related to sales and assets.
• Financial ratios: These analyze such characteristics as a firm’s liquidity, its
use of money, its ability to pay expenses, its profitability, and its market
value. Comparing a firm’s financial ratios with those for other companies in
the same industry helps gauge the performance of the company’s managers.

19. 19

THE TIME VALUE OF MONEY
• Most valuable things concerned are: rate of interest, discount rate, cost of
capital.
• FV(rate, number of periods, payment, present value, type) Computes the
future value of a series of equal payments and/or a present value after a
specified number of periods at a specified rate of interest.
• PV(rate, number of periods, payment, future value, type) Computes the
present value of a series of equal payments and/or a future value after a
specified number of periods at a specified rate of interest.
• NPV(rate, value1, value2,…) Computes the present value of a series of
future cash flows (value1, value2, …) at a specified rate of interest.
• PMT(rate, number of periods, present value, future value, type) computes
the value of a series of equal payments equivalent to a given present value or
future present value for a specified number of periods at a specified rate of
interest.
• PPMT(rate, period, number of periods, present value, future value, type)
• IPMT(rate, period, number of periods, present value, future value, type)
These two functions compute the amounts of a payment to principal (PPMT)
and to interest (IPMT) during a given period.
• CUMPRINC(rate, number of periods, present value, start period, end
period, type)
• CUMIPMT(rate, number of periods, present value, start period, end period,
type)
• NPER(rate, payment, present value, future value, type)
• RATE(number of periods, payment, present value, future value, type, guess)

20. 20

2. Use Of Excel In Auditing

 The auditing tools can help in detecting problems in
worksheet formulae.
• Excel supplies an Auditing Toolbar which helps in finding
errors on the worksheet.
• Using an auditing toolbar can also help one to understand the relationships
amongst cell references, formulas and data.

TYPES OF AUDITING TOOLS
• Trace Precedents shows what goes into the cells calculation.
• Trace Dependents shows where the audited cell is used.
• Trace Errors shows where an error statement arises.
• Remove duplicate
• Advanced filter








Data validation
Formula auditing tool
Error checking
Track changes

21. 21

Excel in Auditing – Implementation

• First obtain the data from the entity being audited. While Excel is limited to
65,536 rows, data sets can often be summarized down to a "spread sheet
size," allowing Excel to be used for analysis purposes, example- ratio
analysis of financial statements.
• Create statistical samples from the data. For example, you can select a
sample of paid invoices to test that represents the entire population of the
paid invoices.
• Export your sample into an Excel spread sheet. In excel you can run a
variety of tests on your sample and set up templates to be used on other
similar audits.
• Run the tests on your sample data. The results of analytical and detailed
testing can be recorded in the Excel spread sheets with the data to which it
applies.
• Form conclusions from your testing and analysis and reference your Excel
spread sheets as documentation. Excel spread sheets can be used as lead
sheets to organize your work papers with supporting detail lined up behind
each spread sheet.

22. 22

COMMON AUDIT TECHNIQUEs

• Convert the tested field (say invoice number)
to numeric
• Sort the records based on the tested field
• Copy down the formula throughout the record range
• AutoFilter the records for finding values which do not evaluate to 1 (0 will
imply a duplicate record, a value more than 1 will imply a gap)
• In case of a single product entry, one can consider the extent to which
discount is permitted by using the Data Analysis Tool pack to find the
correlation between quantity and the billed amounts.
• Draw random samples from the population by using the Sampling Tool.

23. 23

3. USE OF EXCEL FOR DATA ANALYSIS
There are three ways by which a chartered accountant can analyze data. These are
as follows:
I Data Tables
II Scenarios
III Goal Seek



 DATA TABLES
• A data table is a range of cells that shows how changing one or two variables
in your formulas will affect the results of those formulas.
• Data tables provide a shortcut for calculating multiple results in one
operation and a way to view and compare the results of all the different
variations together on your worksheet.

24. 24

4. USE OF EXCEL IN TAXATION


• Examining methods of computing income tax in excel using tax tables.
• Specifically, we use LOOKUP functions, SUM, MAX, MIN,
SUMPRODUCT function and then we use data validation and the
INDIRECT function to make it easy to pick the desired tax table, such as
single or married filing joint.
• Most excel users are familiar with the sum, min and max functions in excel.
You can use these functions with if statement also for computing income
tax, you have to provide a range of cells to the function to derive a result.
• We can use if function also for calculating income tax as per slab rates in
Microsoft excel.

25. 25

USING IF FUNCTION
• IF you want to calculate income tax, education cess and secondary higher
education cess as per slab for income tax of AY 2018-19
• Additional Tax: “No Surcharge”, Education Cass : 2% of the Income Tax
Amount, Secondary Higher Education Cess: 1% of the Income Tax Amount.
• Step 1: Prepare following sheet in excel as per given below.


• Step 2: Type the formula to calculate income tax amount in cell
C2 =IF(B2<$B$15,”Nil”,IF(B2<=$B$16,(B2-
200000)*$C$16,IF(B2<=$B$17,(B2-500000)*$C$17+30000,(B2-
1000000)*$C$18+130000)))
• Step 3: Type given formula to calculate education cess @ 2% on income tax
in cell D13: =IF(C2=”Nil”,0,C2*2%)
• Step 4: Type given formula to calculate S.H.Education Cess @ 1% on
income tax in cell E13: =IF(C2=”Nil”,0,C2*1%)
• Step 5: Now calculate total of tax amount in cell F13: =SUM(C2:E2)
• Step 6: Copy formula of income tax, education cess and S.H. Education Cess
by drag and drop method in given range C2:F7

26. 26

• Step 7: Finally you get total tax amount as per given income tax slab of AY
2013-14

Multiple criteria SUM, MIN, and MAX formulas
=SUM (C2:C6) would add up all values in cells C2 through C6
=MIN (C2:C6) would return the smallest value within cells C2 through C6
=MAX (C2:C6) would return the largest value within cells C2 through C6
As shown in Figure, these formulas return 225,000, 29,000, and 70,000,
respectively

27. 27

USE OF IF STATEMENT WITH SUM
• You’ll notice that there’s an IF statement within the SUM. This allows us to
specify multiple criteria, which we separate with an asterisk. Since the IF
statement is contained within a SUM function, Excel adds up the values that
meet both criteria.
={SUM(IF(($A$2:$A$6=F$2)*($B$2:$B$6=$E3),$C$2:$C$6))}
• We can apply the exact same technique with the MIN and MAX functions.
Cell G3 in Figure 1 contains this formula:
=MAX(IF(($A$2:$A$6=G$2)*($B$2:$B$6=$E3),$C$2:$C$6))
• Cell H3 in Figure 1 contains this formula:
=MIN(IF(($A$2:$A$6=H$2)*($B$2:$B$6=$E3),$C$2:$C$6))

28. 28

5. USE OF EXCEL IN DATA ENTRY
• Information entered into a cell is understood either as a text entry, a value or
a formula.
• Functions are also treated equivalent to formulae.
• Dates, time and percentages are stored as numbers (values).
• It is important that particular information is stored in the correct format.
 TEXT
• Text entries or labels can contain any combination of letters, numbers and
spaces.
• A label which is too long for the width of a cell floats across the cells to its
right / left / both (depending on the alignment of the cell) as long as those
cells do not contain any information

 FUNCTIONS
A cell can also derive its value through functions.
Functions are processes, which have been defined and standardized by Excel.
A complete list of functions can be found at Insert -> Function.
A function takes in certain standard arguments, undertakes the evaluation process
and returns a particular result.

29. 29

 Advantages
ADVANTAGES of Excel are wide and varied; here are the main advantages:
• Easy and effective comparisons - With the powerful analytical tools
included within Excel you have the ability to analyse large amounts of data.
• Powerful analysis of large amounts of data - With powerful filtering, sorting
and search tools you are able to easily narrow down the criteria that will
assist in your decisions.
• Working Together - With the advent of the Excel Web App you can now
work on spreadsheets simultaneously with other users.
• Microsoft Excel Mobile Apps - With the advent of the tablet and the smart
phone it is now possible to take your worksheets to a client or a meeting
without having to bring along your Laptop.
• Sent through Emails - Excel can be shared through email and viewed by
most Smart phones which makes it more convenient to use.
• Secure - Excel files can be password protected for extra security.
 Disadvantages
• Non relational – The idea that you can link data by common elements
(such as employee ID, Stock Number, etc.) greatly assists in the
reduction of data volume, and the ability to analyse data.
• Scalability – Excel 2010 has introduced larger spreadsheets and binary
formats, however using large amounts of data on the average PC will
slow down and often freeze the PC.
• Viruses - Viruses can be attached to an Excel file through macros.
• Slow Execution - Using only one file can make the file size very big and
as a result the program might run slowly.
• Hard to Use - Although there are training programs, it is still hard to use
and some users might not get the hang of it.

30. 30

 BIBLIOGRAPHY
• Information Technology Training Module by Institute of Chartered
Accountants of India (ICAI)
• www.google.com
• www.Wikipedia.org
• www.quora.com