ITT Practice Manual 210805 135213

Case Studies & Exercises for Hands - On Training
ICITSS-Information Technology
Practice Manual
Board of Studies
The Institute of Chartered Accountants of India
This Practice Manual has been compiled by faculty of Board of Studies with the objective to (a) provide greater
practical training as a part of the Information Technology Course through case studies and exercises, (b) bring
uniform reference material and coverage across the country as a part of the course, (c) enable students go
gain knowledge in the subject and (d) enable students to get hands-on practical exposure on use of computers.
All attempts have been made to compile relevant case studies and exercises to enable students to acquire
knowledge, skills and hands-on experience. This Practice Manual is also expected to serve as a source of
reference for day-to-day working on computers. In case students need any clarifications or have suggestions to
make further improvements in the material contained herein, they can write to Board of Studies.
All care has been taken to provide the material in a manner useful to the students. However, the material has
not been specifically discussed by the Council of the Institute or any of the Committees and the views
expressed herein may not be taken to necessarily represent the views of the Council or any of its Committees.
The information in this material has been contributed by various authors based on their expertise and research.
While every effort has been made to keep the information cited in this material error free, the Institute or its
officers do not take the responsibility for any typographical or clerical error which may have crept in while
compiling the information provided in this material. There are no warranties/ claims for ready use of this
material, as the material is for educational purpose. The Information provided in this material are subject to
changes in technology, business and regulatory environment. Hence students are advised to apply the content
using their professional judgement. Please visit Institute website for latest updates. All copyrights are
acknowledged. Use of specific hardware/ software in this material is not an endorsement by ICAI.
All rights reserved. No part of this publication may be reproduced, stored in a retrieval system or transmitted, in
any forms or by any means, electronic, mechanical, photocopying, recording or otherwise, without prior
permission, in writing, from the Institute.
©The Institute of Chartered Accountants of India
E-mail : bosnoida@icai.in
Website : www.icai.org
Edition : January,2018
ISBN : 978-81-8441-823-1
Sale Price : 150/-
Published by : The Publication Department on behalf of The Institute of Chartered Accountants of India,
‘ICAI Bhawan’, Post Box, No. 7100, Indraprastha Marg, New Delhi - 110 002, INDIA.
Printed by :
Table of Contents
1. Introduction to Practice Manual ............................................................................................................................... 5
1. Introduction ............................................................................................................................................................ 5
2. Information Technology Course ............................................................................................................................. 5
3. Practice Manual - Organisation.............................................................................................................................. 5
2. MS – Word 2010.......................................................................................................................................................... 7
1. Introduction ............................................................................................................................................................ 7
2. Chapter 1 : Introduction to MS – Word 2010 ......................................................................................................... 7
3. Chapter 2 :The Insert Tab .................................................................................................................................... 17
4. Chapter 3 : Managing Documents ....................................................................................................................... 24
5. Chapter 4 : Page Formatting, Printing and Mailing .............................................................................................. 32
Assignments – MS Word 2010 ..................................................................................................................................... 43
3. MS – Excel 2010 ....................................................................................................................................................... 46
1. Introduction .......................................................................................................................................................... 46
2. Chapter 1 : Introduction to MS - Excel 2010 ........................................................................................................ 46
3. Chapter 2 :Cell Referencing Range and Functions.............................................................................................. 51
4. Chapter 3: Working with Worksheets, Charts, Macros and Hyperlinks ................................................................ 62
5. Chapter 4: Consolidation of Data and Data Analysis ........................................................................................... 69
6. Chapter 5: Data validation & Protection ............................................................................................................... 78
7. Chapter 6: Pivot Tables Reports & Pivot Chart Reports ...................................................................................... 80
8. Chapter 7: Importing / Exporting Data ................................................................................................................. 82
9. Chapter 8: Multi - Dimensional Analysis of Data .................................................................................................. 82
10. Chapter 9: Dash Board Reporting in Excel .......................................................................................................... 84
Assignments – MS Excel 2010 ..................................................................................................................................... 85
4. MS – PowerPoint 2010 ............................................................................................................................................. 91
1. Introduction .......................................................................................................................................................... 91
2. Chapter 1 : Introduction to MS - PowerPoint 2010............................................................................................... 91
Assignments – MS PowerPoint 2010 .......................................................................................................................... 97
5. MS – Access 2010 .................................................................................................................................................... 98
1. Introduction .......................................................................................................................................................... 98
2. Chapter 1: Introduction to Microsoft Access 2010 ............................................................................................... 98
3. Chapter 2 : Working with Table and Form ........................................................................................................... 99
4. Chapter 3 : Working with Queries and Reports ................................................................................................. 113
6. Computer Assisted Audit Techniques (CAAT) .................................................................................................... 124
1. Introduction ........................................................................................................................................................ 124
2. Chapter : 3 Data Analysis Using IDEA............................................................................................................... 124
3. Chapter : 4 Advanced Data Analysis Using IDEA .............................................................................................. 126
7. Accounting Package .............................................................................................................................................. 128
1. Introduction ........................................................................................................................................................ 128
2. Chapter 2: Introduction to Tally.ERP 9 .............................................................................................................. 128
3. Chapter 3: Voucher Entry in Tally. ERP9........................................................................................................... 131
4. Chapter 4: Voucher Entry in Tally. ERP9........................................................................................................... 135
5. Chapter 5: Financial Analysis Tools in Tally.ERP9 ............................................................................................ 141
Board of Studies
USER GUIDE & DISCLAIMER STATEMENT
A: USER GUIDE
1. Please read the Disclaimer/ License Agreement, system requirements and installation instructions carefully before
proceeding.
2. All data contained in this disk is for personal use of Members and Students of the Institute of Chartered
Accountants of India (ICAI) to learn and develop competencies in better use of computers. This disk is
programmed to run on current computers.
3. The information contained herein is subject to change without notice & is not warranted to be error-free. If you find
any error, please report to us in writing.
4. Minimum System Requirements: Personal Computer/ Laptop running Windows 7/ 8/ 10 with minimum 1 GHz CPU,
2 GB RAM, 1024x768 24 Bit Colour Resolution, and 2 GB Free HDD. Antivirus software – updated.
5. Learning Resource: This resource has been provided for ACADEMIC PURPOSES only to enable ICAI members
and students to develop a better understanding of this field.
B: DISCLAIMER
1. The contents of this CD have not been specifically discussed by the Council of the ICAI or any of its Committees
and thus the views expressed herein may not necessarily represent the views of the Council or any of its
Committees. The views expressed in this CD module are those of the contributors only. Therefore, ICAI does not
take any responsibility of it.
2. Although the contents of this CD have been reproduced in as-is condition as received from the contributors, are
believed to be free of any virus or other defects as a training resource and all efforts have been made to keep the
disk virus free, ICAI does not accept responsibility for any disruption, damage and/ or loss of data or computer
system that may occur at time of using or in future this disk and the applications or data in it. It is the responsibility
of the recipient of this CD to scan the CD with effective antivirus software with latest virus signatures before using it
every time.
3. The ICAI does not accept any responsibility for any loss or damage, whether direct or consequential, arising in any
way by using the CD or its contents. ICAI's liability is limited to replacement of defective CD, provided the defect is
reported within 15 days of dispatch/ purchase from the ICAI.
4. While the content contributors and reviewers have put in their best efforts in the development of this CD, they
cannot be held responsible for any errors, omissions, and judgement whatsoever. Considering the emerging nature
of technologies and technical requirements, no representation or warranty is made with respect to the accuracy or
completeness of the contents/ coverage and any implied warranties of merchantability or fitness of tools/
techniques/ features demonstrated are specifically disclaimed.
5. Considering the above, members/ students of ICAI must satisfy themselves regarding the authenticity, reliability,
usefulness or otherwise of the software and facilities demonstrated for any application/ use or procurement. The
ICAI/ contributor/ vendor of these learning resources will not be responsible in any way for the result of any action
taken on the basis of the learning content or software contained in this CD and they shall not be liable for any loss
of profit or other financial or commercial damage, including, but not limited to special/ incidental/ consequential or
other damages whatsoever.
C: TRADEMARKS/ COPYRIGHTS
1. All product names/ logos/ Trademarks/ Copyrights/ Screen Shots/ Company Name/ Other Information referred to in
this CD belongs to or are the intellectual property of respective owners. Copyright and ownership of their products
is acknowledged.
2. No part of this product may be copied, reproduced, modified, distributed, displayed, stored in a retrieval system or
transmitted in any form by any means (electronic, mechanical, copying, recording or otherwise) for the purpose of
sharing with others without the prior written authorization from the ICAI.
1.
Introduction to Practice Manual
1. Introduction
ICITSS - Information Technology (IT) is revolutionising the way businesses work, survive and grow in the competitive
global village today. Governments, Businesses Enterprises and Individuals are fast changing the way they operate,
perform business operations and deliver goods and services. Online is the new mantra of today’s information sharing,
governance, and delivery of goods & services.
Professional Accounts have also started making greater use of IT for their activities including communications (e-Mail,
WhatsApp, SMS), Financial Analysis/ Reporting, Accounting, Taxation, Online Returns Filing etc. The survival and
growth of a dynamic profession of Chartered Accountancy depends, to a large extent, on the understanding of
Business Information Systems, Business Process Automation, Telecommunications and Networking to face the
emerging challenges in the globalized competitive business environment. Accountants today need to use and analyse
humongous data, generate relevant information and report findings.
2. ICITSS - Information Technology

The Information Technology Course aims to provide hands-on practical training on use of computers for the work of a
professional accountant that inter alia covers Computer Fundamentals, Operating Systems,CBS, Office Automation
using MS-Office (MS - Word, MS - Excel, MS - PowerPoint, MS - Access), CAAT and Accounting Package and e-
Filing.
3. Practice Manual - Organisation

This Practice Manual aims to bring greater Hands - On Practical Training (HOT) as a part of the Information
Technology Course being conducted at the Information Technology Centres of the Institute. This Practice Manual
provides practical Illustrations, Exercises, and Case Studies to be covered as a part of the Information Technology
Course on an all India basis by Students. The Information Technology Centres shall ensure that these practical
applications are duly covered as a part of the course delivery.
While this Practice Manual enumerates the practical Illustrations, Examples, Case Studies to be covered as a part of
the course, further detailed steps are available in the Study Material for the Course and work files are available in the
companion Course CD. The Practice Manual provides brief steps to complete the practical Hands - On Training (HOT),
the students are advised to refer to the detailed steps in the aforementioned resources.
This Practical Manual provides the practical illustrations, Examples and Case Studies and then the steps with solution,
such that students can first make an earnest effort to solve the problem at their level and then view steps and solution
thereof.
The Faculty Members are Information Technology Centres are requested to provide (a) changes/ updates required, (b)
views/ suggestions for incorporation and (c) practical Case Studies and exercises to be included in revised the revised
edition of this material, based on their experience in teaching this subject and use of this manual.
Note: The detailed steps and process of solving the Illustrations and Case Studies in this section have been covered in
the following resources:
Name Symbol Resource
Study Module I SM1 Study Material for IT Module I
Study Module II SM2 Study Material for IT Module II
New New Newly added Exercise & Case Studies
Summary
As a part of our Information Technology Course study, we shall be covering the Exercises and Case Studies as
detailed in the following table, in the respective topics.
Sr. No. Unit Name Exercise Assignments

1 MS – Word 2010 41 15
2 MS – Excel 2010 50 12
3 MS – PowerPoint 2010 7 5
4 Databases 11 --
5 CAAT 20 --
6 Accounting Package 16 --
Total 145 32
This Practice Manual aims to enable students to learn practical usage of Software covered as a part of the course
through Hands - On Practical exercises and Case Studies that follow the principle of learning by doing. It is envisaged
that students learn a large number of practical application skills, after doing a large number of exercises.
2.
MS – Word 2010
1. Introduction
The advent of Personal Computers in eighties saw the introduction of three very useful Office Productivity Software
including Lotus 1-2-3, dBase and Word Star that really changed the way computers were used. Soon thereafter,
Microsoft released its integrated Office Productivity Software named MS-Office with a unified interface, which included
MS - Word the most commonly used word processing programme today. The MS-Word enables us to develop
presentable letters, reports and the like.
As a part of our study on usage of MS - Word, we shall be covering the Illustrations and Case Studies as detailed in the
following table, in the respective chapters
Sr. Chapter Title Total
MS – Word 2010
1 Introduction to MS - Word 2010 14
2 The Insert Tab 10
3 Managing Documents 9
4 Page Formatting, Printing and Mailing 8
Total 41
While many of us are familiar with MS - Word and can undertake basic typing and a bit of work, there are many useful
features and facilities available in MS - Word that make our letters and reports better in terms of presentation. We shall
explore some of these very useful features as a part of our study of MS - Word using practical hands - on exercises in
this Practice Manual.
2. Chapter 1 : Introduction to MS – Word 2010

Exercise 1.1: Creating and Managing a Document SM1
In this exercise students are required to create a word document, enter text, format it into a presentable form, spell
check and print a report.
Solution:
 Create a new word document & save as exercise_1.1.docx
 Enter the following text in the document
Text for Input―Communication cannot be effective unless, the receiver listens with due attention or participation.
It further shows that communication is a joint responsibility of both the sender and the receiver. Participative listening is
an essential condition of effective communication. One cannot be an effective speaker unless one is a good listener. It
must, first of all, be made clear that listening is a deliberate effort. It is not the same as hearing. When we hear, we do
not have to make any effort. But for listening, we have to train our ears and ask ourselves why we want to listen to
somebody. While listening we must discriminate, evaluate, appreciate and react.
 Correct the spelling mistake in the paragraph.

 Delete this sentence from the paragraph: ―One cannot be an effective speaker unless one is a good listener.
 Display the spaces and end of paragraph.
 Change the word ―train in the paragraph to the word ―equip without using ―Find and Replace feature.
 Underline the word ―communication‖ in the first line.
 Change the italicized text in the paragraph from italics to bold.
 Right align the paragraph.
 Enter this sentence in the document such that it is between the first and second sentence: ―Communication
begins with listening.
 Close the document.
 Rename the document with the name exercise_1.docx
 Open Word 2010. Now open exercise_1.docx in it using keyboard shortcut.
Exercise 1.2: Securing Access to Word File New

In this exercise we shall learn how to give a password to a MS - Word File such that persons who know the password
can only open and work with the file.
Solution:
Step-1 Open exercise_1.1.docx
Step-2 Select option Encrypt with Password in Protect Document Tab/Group, Under File Menu.
Step-3 Click on Encrypt with Password, Encrypt Document window appears type password on it and click on ok button.
Confirm Password window appear, re-enter same password and click on ok.
Step-4 Save and close the file

Step-5 Open the same file again now it will ask the password to open.
Exercise 1.3: Creating Numbered and Bulleted Lists in MS-Word SM1
In this exercise we shall learn the process of creating Numbered and Bulleted Lists to better present our letters and
reports.
1. Open file exercise_1.3.docx from MS - Word folder.
2. Enter the following text:
―There are three different types of accounts:
Personal account
Real account
Nominal account
3. Change the font of third point to Script MT Bold and font size to 16
4. Using Format Painter tool, copy the formatting from third point and use it on second point.
5. Sort the list alphabetically in ascending order.
6. Leave two blank lines after the list.
7. Define a new multilevel list style (with three levels) as
I ……………………………..
1. …………………………
a. ..…………………
8. Now create a sample list as:
I. Accounting
1. Voucher
a. Contra Voucher
b. Payment Voucher
c. Receipt Voucher
2. Financial Statements
a. Cash Flow Statement
b. Fund Flow Statement
c. Ratio Analysis
d. Bank Reconciliation Statement
II. Economic systems
1. Market economy
2. Mixed economy
3. Planned economy
4. Traditional economy
Solution:
Exercise 1.4: Sorting Data New

While developing letters and reports in MS - Word, we at times provide tabular data. While doing so, we at times need
to arrange the data alphabetically or numerically, for the table to be more meaningful. In this exercise we shall learn
how to sort the data as per our requirements.
Solution:
Open file exercise_1.4.docx from MS - Word folder and then sort the data in ascending order on Course Column and
then on Student Name column.
Step 1: Select all the columns and click on sort icon on paragraph tab of home ribbon.
Step 2: Select Header row from My list has Group

Step 3: Select Field/Column Course and Ascending order then by Column is Student Name, click on ok button.
Exercise 1.5: Saving Files with New Name - Save As New

While working with a MS - Word file, we need to (a) save the file with a new name or (b) save the file with a version
number to retain the earlier content for reference, or (c) save the file with a new format – for example PDF. We shall
explore the use of Save As option, in this exercise.
Solution:
Open file exercise_1.5.docx from MS - Word folder and add some data then save as this document with the name as
exercise_1.5_2.docx
Step 1: Open the file exercise_1.5.docx from MS - Word folder.
Step 2: Add the following text to the bottom of the document:
Input Text-To receive your FREE evaluation copy it will take only a few seconds to complete and return the enclosed,
reply-paid slip. Alternatively, just give me a call on 01122222222.
The course will be invaluable for all professionals in your organisation needing to brush up on their skills.
Yours sincerely
Step 3: Save this file again, with Save As command and rename the file as exercise51.docx
Step 4: Close the file.
Exercise 1.6: Formatting Documents New
While working with documents in MS - Word, we need to format documents suitably, to make them presentable. Use
file exercise1.5.docx created in previous exercise and update file for formatting requirements to make it a presentable
letter, as detailed in the steps.
Solution:
Step 1: Open the file exercise_1.5.docx (created in the previous exercise)
Step 2: Reposition the insertion point at the top of the document and insert the following text:
Dr. Anuj and Mayur
ICAI
Plot No. 24 - 25
NOIDA, SECTOR - 62
Press the Enter key at the end of each line of the address two times to create two new lines before the salutation.
Step 3: Type the salutation as Dear Dr Anuj and Mayur and press the Enter key once.
Step 5: Reposition the insertion point at the end of each paragraph and insert a blank line.
Step 6: Reposition the insertion point at the end of the document and insert two blank lines
Step 7: At this point in the letter, type the sender’s name as Alok Gupta
Step 8: Select the text bite-size in the first paragraph and delete it. Replace it with the text manageable
Step 9: The last two paragraphs are in the wrong order. Reposition them so that the paragraph beginning - To receive
your free evaluation is the last paragraph.
Step 10: Save the file again with the name exercise_1.6.docx
Step 11: Close the file.
Solution:
Exercise 1.7: Formatting Documents – Font: Colour, Bold & Italics New
While working with documents in MS - Word, we often need to emphasise part of the document using different Colours,
Bold Type Face, Italics etc. In this exercise, we shall learn to undertake this work using exercise_1.6.docx created in
previous exercise, to format the file as instructions given in the solution.
Solution:
Step 1: Open the file exercise_1.6.docx created in a previous exercise.
Step 2: Select all the text and change its font to Arial 14pt.
Step 3: Format the heading: The Best Training Solutions so that it is bold and change the font colour to red.
Step 4: Select the text: Professional Survival Guide in the first paragraph and change the font to Arial Black, and italic.
Step 5: Format the text: constant in the second paragraph and make it italic.
Step 6: Select the third paragraph: The course will be invaluable and highlight the text using dark blue.
Step 7: Format the sender’s name Alok Gupta and make it italic.
Step 8: Format the capitalised text FREE in the last paragraph so that it is bold and underlined.
Step 9: Save the file with the name exercise_1.7.docx
Exercise 1.8: Formatting Paragraphs New

While working with documents, we need to set the paragraph formatting to meet our requirements. This includes setting
paragraph alignment, hanging indents (for numbered or bulleted lists), find and replace text, copying paragraph settings
etc. We shall learn these processes, in this exercise.
Solution:
Step 1: Open the file demo1.docx
Step 2: Hide the spaces and end of paragraph.
Step 3: Left-align the paragraph.
Step 4: Increase the character spacing of the paragraph by 1.8 points.
Step 5: Change the measurement units to centimetres.
Step 6: Increase the Right margin of page by 1 cm
Step 7: Create a negative indent of 1 cm on the text.
Step 8: Find and highlight all occurrences of the word –communication in this document
Step 9: Replace all odd occurrences of the word - communication with the word - conversation
Step 10: Change font of first sentence with Comic Sans MS font
Step 11: Change all white spaces with tabs, using Find and Replace feature.
Step 12: Copy the paragraph and paste it such that the same paragraph appears twice in the document.
Step 13: Add Outside Borders to the first paragraph.
Step 14: Print the document in Landscape orientation.
Hint
1. Click the Office Button, go to Word Options. On Advanced tab, go to Display.
2. From the Page Setup group of the Page Layout ribbon, click Margins. Select Custom Margins from the drop-down
list. Increase the value in the Right margin text box by 1.
3. In the Paragraph group on the Page Layout ribbon, click the down arrow in the Indent - Left box.
4. Select Find & Replace dialog box, Click on More >> Click in the Find what text box, click on Special button then
select option “White Spaces” (^W) Then click on Replace with text box, Click on Special button then select option
“Tab character”. (^t) Then click ok
Exercise 1.9: Style Sheet - Update a predefined style New
MS - Word provides a useful facility of defining styles for Characters and Paragraphs, such that all the required settings
can be applied by selecting the required Style. Yes, we can copy the formatting but what happens when you decide to
change some of the settings – for example Font or Font Size - You have to again copy/ paste the paragraph formatting.
In case you use styles, a change in style would apply to all paragraphs using that style. We shall explore this STYLE
facility in this exercise.
Solution:
1. Create and save a new word document as exercise1.9.docx
2. Change the style of the document to Emphasis.
3. Enter the following text in the document.
Input Text-There once was a man who had a sister, his name was Mr. Fister. Mr. Fister‘s sister sold sea
shells by the sea shore. Mr. Fister didn’t sell sea shells, he sold silk sheets. Mr. Fister told his sister that he sold six silk
sheets to six shieks. The sister of Mr. Fister said I sold six shells to six shieks too
4. Justify the complete text, using keyboard.
5. Enter the following text in the document after leaving a blank line.
Peter Piper picked a peck of pickled peppers, A peck
of pickled peppers Peter Piper picked. If Peter Piper
picked a peck of pickled peppers,
How many pickled peppers did Peter Piper pick?
6. Select the first paragraph and underline it.
7. Update the style Emphasis to match selection. (Now you can see that the poem also gets underlined.)
Solution:
Exercise 1.10: Creating a New Style and Modifying Styles SM1

We shall learn to define new styles for paragraphs and to modify the styles to meet our requirements, in this exercise.
Solution:
2. Enter the following text in the document.
Input Text-
Accountancy is the art of communicating financial information about a business entity to users such as
shareholders and managers. The communication is generally in the form of financial statements that show in
money terms the economic resources under the control of management. It is the branch of mathematical science
that is useful in discovering the causes of success and failure in business. The principles of accountancy are
applied to business entities in three divisions of practical art, named accounting, bookkeeping, and auditing.
3. Select all the text in the document.
4. Change the font to Algerian and font size to 11. Change character spacing to Expanded.
5. Save selection with name “Stylish” as a new quick style. Remove selection.
Modify the style (only for the current document) such that the font is changed to Verdana.
Solution:
Exercise 1.11: Indentation (First Line/Hanging/Negative Indent) New

While working with MS - Word documents, we often need to indent the first line of paragraphs to meet typical
requirements. In this exercise we shall learn to indent first line and to create a hanging indent.
Solution:
Open file Demo1.docx from MS - Word folder and use indentation as given below.
a) First Line Indentation
Solution:
Step-1 Open file Demo2.docx
Step-2 Place cursor at the beginning
and select all the paragraphs.
Step-3 Open Paragraph Dialog Box
(Through Paragraph Launcher)
Step-4 Select First Line Indent set by 1
inch from Indentation option
b) Hanging Indent
Step-1 Select second Paragraph
Step-2 Open Paragraph Dialog Box
(Through Paragraph Launcher)
Step-3 Select Hanging Indent set by 1
inch from Indentation option
Step-4 Click on Ok button
Exercise 1.12: Page Formatting New

We shall explore the option of page formatting in this exercise. Open exercise1.2.docx and perform operation given
below.
Solution:
1. Insert a page break between the two topics, using keyboard shortcut.(Ctrl+Enter)
2. Change the orientation of the text on the second page, to Landscape.
3. Remove the space and insert an optional hyphen between the words Direct and Tax.
4. Insert line numbers in front of each line in the document. Line numbers should restart from 1 in the second page.
5. Insert a watermark with the text “ICAI” written diagonally.
6. Change the background colour of the document to light blue.
Exercise 1.13: Changing Character Spacing New

We shall explore the option of changing Character Spacing in this exercise. Open exercise1.1.docx and perform
character spacing as given below in a paragraph
Solution:
Step-1 Select a word/line or paragraph in which you want to increase character spacing.
Step-2 Go to Home Ribbon and open font dialog Box through Launcher
Step-3 Select Advanced Tab
Step-4 Select Spacing option from Character Spacing and set value as Expanded - 2 pt
Exercise 1.14: Letter Formatting SM1

Offices generally have a set pattern in which letters are written. We shall learn to format a letter in a typical format,
to illustrate the process, as a part of this exercise.
Solution:
Create a new word document as exercise_1.14.docx and generate a letter format as given below
Print
Branch Name: --------------------------
Address: - --------------------------------
------------------------------------------------
Phone: ------------------------------------
E-mail: -------------------------------------
No. Dated: _____________________
ICAI/IT/Reg. No.
Mr/ Ms. ----------------------
Address: -------------------------
Phone: -_______________
Dear Student,
This has reference to your application for registration to the Information Technology Training course of 100 Hours. Your
IT Training shall commence as follows, considering your preferences for Information Technology Centres and timings
and availability of seats at the Information Technology Centre:-
Information Technology Centre Details
Name:- Start Date End Date Timings
-----------------------------------------------------------------------------------
Venue:-
-----------------------------------------------------------------------------
You are required to report for training at the aforesaid centre 10 minutes prior to scheduled starting of the session.
While every effort has been made to allot IT Training batches of the choice exercised by the student, there may be
difference in the timings actually allotted due to administrative exigencies.
In case you fail to attend the class on the very first day, the seat may be allotted to some other student in
waiting and your name may not be considered for immediate next batch due to limited seat availability.
You are required to study the “Guidelines for Information Technology Course Students available overleaf” for
strict compliance in letter and spirit, to avoid delays in securing Information Technology Course completion certificate.
You are required to bring (a) this letter (b) Colour Passport Size Photograph and (C) Identity Card issued by the
Institute or other photo identity issued by Government agency (Voter ID Card, Driving License, Passport)when
you report for training and in your visits to the IT Training Centre for classes and Module / Online Test.
Thanking you,
Yours faithfully,
(Name)
Chairman / DCO
Head / Convenor
3. Chapter 2 :The Insert Tab

Exercise 2.1: Creating and managing document SM1
In this exercise students are required to create a new word document, enter text, format it into a presentable form, spell
check and print a report.
Solution:
2. Insert a table for student data containing fields as StudentID, Name, Marks
StudentID Name Marks
3. Enter details of 4 students (Student ID – 1, 2, 4, 5) in the table.

1 Shivani 75
2 Raj 80
4 Rashmi 67
5 Roshni 84
4. Enter detail of one more student (Student ID – 3) between the second and fourth student
1 Shivani 75
2 Raj 80
3 Amar 72
4 Rashmi 67
5 Roshni 84
5. Delete all the table borders leaving only the external table boundaries.
6. Delete the detail of second student (Student ID – 2).
1 Shivani 75
3 Amar 72
4 Rashmi 67
5 Roshni 84
7. Sort the table by marks in descending order.
5 Roshni 84
1 Shivani 75
3 Amar 72
4 Rashmi 67
8. Convert this table to text with commas in place of column boundaries.
StudentID, Name, Marks
5, Roshni, 84
1, Shivani, 75
3, Amar, 72
4, Rashmi, 67
9. Convert back the text to table with three columns.
5 Roshni 84
1 Shivani 75
3 Amar 72
4 Rashmi 67
10. Merge the details of first two students of the table.
Student ID Name Marks
5
Roshni
84
1
Shivani
75
3 Amar 72
4 Rashmi 67
11. Split the merged cells in three columns.
5 84 Amar
Roshni 1 72
1 Shivani 75
4 Rashmi 67
12. Apply a table style to the table.
5 84 Raj
Roshni 2 80
1 Shivani 75
4 Rashmi 67
Note: This is just one of the table styles. Student may apply any of the available table styles.
13. Split the table leaving the last row in second table.
Student ID Name Marks
5 84 Raj
Roshni 2 80
1 Shivani 75
4 Rashmi 67
14. Insert a Quick Table – Tabular List in the document as shown below.
Note: This is just one of the predefined table. Student may choose any of the available quick tables.
Exercise 2.2: Creating a Time Table New
In this exercise we shall learn to update a table formatting to meet our requirements using an exercise file.
1. Open file exercise2.2.docx from MS - Word folder.
Class 1A Monday Tuesday Wednesday Thursday Friday
09.00 - 10.00 Maths Study
10.00 - 11.00 Maths Science
11.00 - 12.00 Geography Science
12.00 - 13.00 History Science
13.00 - 14.00 Lunch Lunch
14.00 - 15.00 English French
15.00 - 16.00 French French
The table contains an incomplete and inaccurate timetable for Class 1A. Your task is to complete the timetable to look
like the completed timetable shown below, using copy, cut and paste techniques.
2. The classes for the different days have been mixed up:
 Move the classes from the column Monday and paste them to Tuesday.
 Move the classes from the Wednesday column to Thursday.
3. Now copy the correct classes from the columns Tuesday and Thursday and paste them to make up the complete
timetable for Monday, Wednesday and Friday. Avoid typing any entries. The completed timetable should be as
shown below.
Solution:
Completed Timetable
Class 1A Monday Tuesday Wednesday Thursday Friday
09.00 - 10.00 English Maths History Study History
10.00 - 11.00 Geography Maths History Science Geography
11.00 - 12.00 French Geography Geography Science Study
12.00 - 13.00 French History Maths Science English
13.00 - 14.00 Lunch Lunch Lunch Lunch Lunch
14.00 - 15.00 Maths English Science French Science
15.00 - 16.00 History French English French Science
Exercise 2.3: Create a Student Registration form using Custom Table as shown below New
Solution:
For solution please refer Exercise_2.3.pdf file from MS-Word folder.
Exercise 2.4: Using SmartArt Graphics New
MS-PowerPoint and MS - Word have a great feature to graphically present data of Processes, Steps, Cycles and
Organization Charts and the like called SmartArt. In this exercise we shall explore the process of inserting SmartArt in
our documents created using MS - Word.
Generate a Process chart using SmartArt Graphics for Project Development Process. A project analyst doing Analysis
after that Design process of project start, after designing process software is developed by programmer and then
implementation is done by the team and last step is Evaluation.
Solution:
For solution please refer Exercise_2.4.pdf file from MS - Word folder.
Exercise 2.5: Illustrations SM1
In this exercise we shall learn to Insert picture, resize it and apply 3D effects for better presentation in the document we
are working.
Solution:
2. Download picture of a computer from the internet and save it on desktop.
3. Insert the downloaded picture and resize it.
4. Replace the picture with a new one downloaded from internet or taken from sample pictures.
5. Insert any picture from the Clip Art gallery.
6. Fill the picture with some colour and apply a 3-D effect to it.
7. Insert a star shape from the gallery. Enter the following text in the shape
• Clip Art
• WordArt
• SmartArt
8. Delete the picture inserted from the Clip Art gallery.
9. Insert a SmartArt graphic with the shape of Continuous Cycle showing the steps:
• Transactions, Posting, Ledger, Final Accounts, and Analysis.
Solution:
For solution please refer Exercise_2.5.pdf file from MS-Word folder.
Exercise 2.6: Organisation Chart SM1
We often need to generate Organization Chart to graphically depict the structure of our organisation. While many use
boxes and arrows, the SmartArt facility makes the process very easy, better presentable and easy. We shall explore
the process of generating Organisation Chart in this exercise.
Solution:
Step-1: Create and save a new word document as exercise2.6.docx
Step-2: Create an organization chart as per the following reporting hierarchy (using SmartArt graphics)
Designation Reporting to
Application Systems Analysts Application Systems Development Manager
Application Programmers Application Systems Development Manager
DA/DBA Information Processing Manager
Quality Assurance Professionals Information Processing Manager
Security Professionals Information Processing Manager
Computer Operators Information Processing Manager
Application Systems Development Manager IS Manager
Information Processing Manager IS Manager
IS Manager Top Management / Board of Directors
Exercise 2.7: Adding picture SM1

Insert and working with picture in word document.
Solution:
Step-1: Open new word document and type some text in it.
Step-2: Place the cursor under the text, and use Insert Picture, from Illustration Tab under Insert Ribbon. Your
Picture will appear to the left of the column or in the middle.
Step-3: Click anywhere on the Picture; the cursor will change, after which you can slide the Picture to where you want
it on the page. What you’re doing is moving the “anchor” usually the corner of the picture, which is connected
to a particular piece of text. That’s why you were advised to type some text first.
Step-4: Re-size the picture by placing the mouse pointer over one of the “sizing handles” around the picture. The
pointer will turn into a double-arrow, which you can use to enlarge or shrink the picture. Note that handles at
the corners preserve the aspect ratio (relation between height and width), while those on the sides, top and
bottom change the shape of the picture.
Step-5: Right-click on the picture. This produces a menu that lets you position or edit the figure (double-clicking the
picture puts you directly into Format or Edit, depending on the type of picture). Try out the options. You would
normally anchor a picture to a paragraph that described it, if text and picture are closely related. If not, the
best thing is to give it a caption (like “Figure 1”) and anchor it to the page. Check the Wrap Text Around Box
check-box unless you want text to continue over the picture. A common convention is to position large figures
at the head of a page to avoid breaking up the flow of the text.
Solution:
For solution please refer Exercise_2.7.pdf file from MS - Word folder.
Exercise 2.8: Adding Chart in Document SM1
It is often said that pictures/ graphs/ charts can convey what 1,000’s of words cannot. MS - Word enables us to add
pictures/ graphs/ charts in our documents to better communicate information. We shall learn the process of including
charts/ graphs in our document with the given data for this exercise.
Solution:
Step-1: Open new word document and save as exercise2.8.docx
Step-2: Create a Bar Chart for the data given as below, showing different bars for each region
Model East West North South

Mm555 560 640 840 750
Mm600 670 340 980 860
Mm800 630 550 330 730
Mm766 490 720 200 180
Mm755 890 440 590 300
Step-3: Give the command so that the graph shows the values along with each of the bars.
Step-4: Format the figures so as to show the numbers as 0.00. Insert the chart title above the chart as Region wise
Sales - Different Models
Step-5: Insert titles as the following
(a) X-Axis – Sales Figure
(b) Y-Axis – Model Name
Step-6: Change the graph to Stacked Column in 3-D

Step-7: Append the following data for the Central region and update the graph
Model Central
Mm555 900
Mm600 650
Mm800 320
Mm766 870
Mm755 670
Hint
Select the chart, right-click and select Add Data Labels.
Select the chart, right-click, select Format Data Labels and navigate to Number tab.
Exercise 2.9: Inserting Pictures and Hyperlinks SM1
Inserting pictures helps in presenting a better understanding of the topic. Hyperlinks in Word Documents help in
providing further details, which a user may like to view. In this exercise we shall learn the process of inserting pictures
and hyperlinking.
Solution:
Step-1: Create a new word document and save it as exercise_2.9.docx
Step-2: Insert a built-in equation of Binomial Theorem
Step-3: Insert a caption to this equation.
Step-4: Download and insert picture of a computer.

Step-5: Create a new word document and write description of a computer in it.
Step-6: Create hyperlink from this picture to the new document.
Step-7: Press Enter key repeatedly till the cursor is on second page.
Step-8: Write the text Google containing hyperlink to the website www.google.com
Step-9: Assign a bookmark with name search to the text written in previous instruction.
Step-10: Now go to the top of the document and go to bookmark named search
Step-11: Insert a cross-reference to the equation inserted in instruct
Step-12: Now go to the equation using the cross
Step-13: Create different headers for odd and even pages. Write text Odd in odd page header and Even in even page
header.
Note: The picture taken is just an example.
Hint: Select Insert caption from the Captions group in the References ribbon.
Exercise 2.10: Working with Text Box SM1
MS - Word has a facility to insert Text Boxes in our document and also offers different types of Text Boxes, to better
present our letter/ report. We shall explore these facilities in this exercise.
Solution:
Step-2: Insert a text box.
Step-3: Enter the following text in the text box:
Peter Piper picked a peck of pickled peppers
A peck of pickled peppers Peter Piper picked
If Peter Piper picked a peck of pickled peppers,
How many pickled peppers did Peter Piper pick?
Resize the text box accordingly such that all the text is not visible.
Step-4: Insert another text box and create a link from the first text box to the second. Now the extra text will
automatically move to the second text box.
Peter Piper picked a peck of If Peter Piper pickled a peck of
pickled peppers, pickled peppers,
A peck of pickled peppers Peter How many pickled peppers did
Piper picked. Peter Piper pick?
Step-5: Insert the name of the author of the document using Quick Parts feature.
Step-6: Enter the following paragraph in the document
Software has been targeted as a growth sector in India. Besides the industry‘s own potential for exports,
software also plays the crucial role in information technology (IT), use of which is fast becoming the key for
competitiveness in any industry. Hence, for the growth of IT industry as well as the other industries, a rapid
growth of software industry, both in terms of size and quality is absolutely essential.
Step-7: Insert Drop Cap (In margin) in the paragraph.
Step-8: Insert a signature line with your name and current date, to the document.
Step-9: Insert current date and time in the top-right corner of the document such that it changes dynamically whenever
the document is opened.
Step-10: Create a new embedded object (bitmap image) as icon.
Step-11: Write the formula of area and perimeter of circle as Area =___ and Perimeter =_____ (using Symbol option)
Step-12: Insert a WordArt with text Word Art and WordArt style 15. Rotate it left by 90
Step-13: Insert the following equation for finding the second derivative in the document.
Hints:
Enable Show sign date in signature line in Signature Setup dialog box. If Invalid signature is written in the signature
line, then right-click on the signature. Select Signature Details and click on Click here to trust this user’s identity. Click
close.
4. Chapter 3 : Managing Documents

Exercise 3.1: Table Index SM1
Many of our reports/ documents/ books developed using MS - Word are large and contain Table of Contents (ToC),
Figures, Authorities, Bibliography etc., which we may like to summarise at different parts of the document. Fortunately,
MS - Word has a facility of managing these activities. We shall learn to insert Table of Contents, Table of Figures,
Table of Authorities, Bibliography and Index in our sample file, as a part of this exercise.
Solution:
Step-1: Open the file exercise_3.1.docx from MS - Word folder.
Step-2: Apply heading styles in the chapter such that the following Table of Contents can be produced. Now insert a
Table of Contents such that it can be updated automatically with a single mouse click.
Step-3: Insert captions to the figures of keyboard, mouse, monitor and printer such that the following Table of Figures
can be produced. Now insert a Table of Figures.
Step-4: Insert four citations at different places in the chapter.

Step-5: Create a Table of Authorities after marking the citations.
Step-6: Insert a bibliography at the end of the chapter.
Step-7: Mark index entries for the words keyboard, printer, screen and sound in the chapter. (While marking, mark all
the entries of a given word.)
Step-8: Insert an index at the end of the chapter.
Step-9: Insert a footnote for Plotter heading with the text as “There are 2 types of plotters: Drum & Flatbed plotter.
Step-10: Now insert a subsequent footnote for the word speakers in section 1.2.4 with the text “A speaker is an electro-
acoustic transducer that converts an electrical signal into sound.”
Step-11: Now change the number format of the footnotes to A, B, C
Step-12: Delete the first footnote.
Note-
The Table of Authorities and bibliography are just samples. The student may insert any other citations also.
Hint
If all heading levels are not displayed in the Table of Contents, then change the number of heading levels so that all
displayed. Also modify the way heading levels are displayed in table of contents, if required, to match the style given.
Exercise 3.2: Language & Proofing Text SM1
We shall explore the facility of Spell Checking and Grammar, better vocabulary through Thesaurus/ Synonyms and
autocorrect facility in MS - Word, as a part of this exercise.
Solution:
Step-1: Open the file exercise3.2.docx
Step-2: Check the spelling mistakes using Spelling and Grammar option. Substitute with correct words wherever
required.
Step-3: Find the synonyms of the word comprehend using Thesaurus option.
Step-4: Specify that whenever IT is typed, it is replaced by Information Technology using AutoCorrect feature. Check
this by typing IT in the document (Press Enter or Spacebar after the text)
Step-5: Write the text. The four suits of playing cards are now inserting the symbols of all suits of playing cards, using
AutoCorrect feature.
Step-6: Add a comment to the previous sentence with the text club suit, diamond suit, heart suit, spade suit. Also
respond to this comment with the text Response
Step-7: Write this text into symbols - Cube root of 8 is 2.
Step-8: Check word count of the document. Verify the number of lines by counting manually also.
Step-9: Enable translation screen tip for translation to French.
Hint
Select Word Options from the Office Button. Click on Proofing and then go to AutoCorrect Options. In Math AutoCorrect
tab, select the check box Use Math AutoCorrect rules outside of math regions. Now to insert symbol of club suit, for
instance, write \clubsuit. Do the same for other suits. (Write \cbrt)
Solution:
Exercise 3.3: Spelling and Grammar Checking New

MS - Word enables us to correct spelling errors by offering options and also highlights grammar mistakes, to enable us
to write correct letters/ reports. We shall explore this facility along with changing of fonts, as a part of this exercise.
Open file exercise3.3.docx. There are many mistakes in this letter. See that some of the words are underlined with red
or green lines.
Solution:
Step-1: Red lines mean that the word is misspelled or that the computer does not know the word. Green lines mean
that there might be a grammar mistake. These lines are only there to help you when you are typing. They will
not be on the paper when you print your document.
Step-2: You will also change how the letter looks. Highlight the top name and address. Change the address font to
Arial and the font size to 14. Make it Bold.
Step-3: Click on the align centre button to move the address to the centre of the page.
Step-4: Highlight the date. Click on align right button.
Step-5: Click in front of “dear Leslie”
Step-6: Press the ENTER key. This moves the words down.
Step-7: Use the Enter key to make spaces between the lines as shown below:
Step-8: Highlight all the text below the top address. Change the font and font size to Arial 12pt.
Step-9: Highlight the words May 3rd, 4th, 5th, and 6th. Underline them.
Step-10: Move the cursor to the beginning of the document.
Step-11: Click on the Spelling and Grammar Check button.
Step-12: Word will look at your document for misspelled words. When it finds a word it doesn’t know, a pop up window
appears giving you several choices:
Step-13: For each mistake, either Change it or choose Ignore.

Step-14: Finish the spell check now.
Step-15: After you finish the spell check look for other words in your letter that might still be spelled wrong. Do you
see the word vesting? This word is not correct. It should be visiting. Spell check did not mark this mistake
because this is an English word but it is not correct in this sentence. Please change it now.
Step-16: Highlight the word sincerely at the bottom of your letter.
Step-17: Click on REVIEW, Click on THESAURUS.
Step-18: The Thesaurus helps you find words that mean the same thing or similar to the word you have highlighted.
Choose a new word to replace sincerely. If you see an arrow, click on the arrow, click on INSERT
Step-19: Click on the Print Preview button. You can see what the letter will look like when you print it on paper.
Your document should look like the one below.
Solution:
Exercise 3.4: Track Changes SM1

Many a times we have to update documents for corrections indicated by the Boss or contribute our suggestions/ views
in a document we are working on. For the Boss or Receiver, it is a tedious task to know what changes you have made
and to work further. Fortunately, MS - Word has the “Review” tab, which allows us to enable the Track Changes Mode,
Search for Changes Made and Accept/ Reject the changes, as you can see in the following image:
As a part of this exercise, you are required to create a document, share it with someone, the other person to edit the
document in Track Changes Mode, make some corrections and send it to you. You are thereafter required to review
changes and Accept/ Reject them, as per your requirement.
Solution:
Step-1: Create and save a new word document as execrcise_3.4.docx
Step-2: Enter the following text in the document
Input Text-
Online learning attempts to bridge the distance gap in learning. Online education exists to meet the needs of trainers
and students separated by physical distance. Physical location of the trainer or training institution becomes
immaterial to acquiring knowledge and skills. Online learning can be the primary or a supplemental means of
learning. Essentially by bridging this gap, online learning increases access to learning. Many in this category are
those who want to learn while working. They may be unable to attend on-campus classes. With online learning,
workers can keep their jobs and at the same time, develop their knowledge and skills. It could also be for the
development of new job skills or training for a new job.
Online learning makes education available to many who are outside the formal or traditional education system. It
therefore provides opportunities for individuals and corporate learners by increasing access to education and training.
Step-3: Enable Track Changes feature.
Step-4: Exchange seat with your immediate neighbour. Allow him/her to modify the text after changing the user name.
Step-5: View the changes done by your neighbour. Accept or reject each change.
Step-6: Now make the document read-only.
Hint
Click the Office Button. From the Prepare tab, select Mark as Final.
Solution:
Exercise 3.5: Comparing Documents SM1

While working in the office, we often create documents and send to our colleagues for required updates. These files
come back to us and we find it difficult to locate changes/ updates made by our colleague. Fortunately, MS - Word has
the facility of Comparing Documents in the Review Tab, as may be noted in the following snapshot:
In this exercise, you are required to create a document and share it with your colleague with a request to make some
changes and give it back to you. You are then required to compare your document with that updated by your colleague
to know the changes/ update made by him/ her.
Solution:
The term wireless networking refers to technology that enables two or more computers to communicate using
standard network protocols, but without network cabling. Although any technology that does this could be called
wireless networking, the term generally refers to wireless LANs (WLAN). A WLAN is a grouping of network
components connected by electromagnetic (radio) waves instead of cables.
A wireless LAN basically consists of: the network backbone; end-user devices such as data collection units, handheld
computers and laptops; wireless LAN access points; wireless cards; and software that will help you manage the
network.
A single access point can support a small group of users and can function within a range of less than 100 to several
hundred feet. Access points can connect WLAN to a wired LAN, allowing wireless computer access to LAN
resources, such as file servers or existing Internet Connectivity.
Step-3: Exchange seat with your immediate neighbour. Allow him/her to modify the text after saving the document with
a different name.
Step-4: Compare both the documents and view the changes made by your neighbour.
Step-5: Now combine the two documents. Save the combined document with a new name.
Step-6: Split the combined document in two parts.
Solution:
Exercise 3.6: Macro SM1

MS - Word has a facility to write a Macro which is a set of steps to undertake a job, like a program. We can write
Macros to undertake repeated steps in MS - Word. In this Exercise, we shall write a Macro to Insert Table with Column
heading Emp_ID, EMP_Name, Emp_Address, Emp_Phone_No and Emp_Basic_Salary with the help of Macro
Solution:
Step-2: Create a macro with the name Employee_Table such that whenever Ctrl+T is pressed, a table with 5 columns
and 10 rows gets inserted in the document. Column headings should be Emp_ID, EMP_Name, Emp_Address,
Emp_Phone_No and Emp_Basic_Salary.
Step-3: Now run the macro by pressing Ctrl+T. The table should get inserted.
Hint:
Start recording a macro with the key sequence Ctrl+T. Then insert the required table. (with the given headings) and
then stop recording.
Exercise 3.7: Themes and Margins SM1

MS - Word provides Themes to present our letter or report in a presentable manner.
Themes are available under Page Layout Tab, as depicted in the screenshot given
alongside this paragraph. A large number of Themes are available and more can be
downloaded from the Internet. A theme essentially defines Fonts, Colours and Effects
to be used in the document. It is possible to change these factors, in case required.
Many of the times, we need to change the layout of the document depending on the
size of paper (Generally A4) and content. In case content is less, we use larger font
and leave more margins at the top, bottom, left and right of the page. In case content is
more and we need to fit the content within designated number of pages, we reduce the
margins.
Solution:
Step-2: Create a new document theme (select theme colours, fonts and effects), save it with your name and use it in
the document.
A Theme is defined as a set of unified design elements that provides a look for Word document by using colour,
fonts, graphics and organizes an appropriate balance of colours. Themes have palette of 12 colours. Theme effects
are sets of lines, fills and special effects for shapes, graphics, charts, SmartArt, and other design elements.
Make the word Theme in the first sentence a hyperlink to the web page
http://www.word07.com/themes.htm.
The created theme must get reflected in the above entered text.
Step-4: Change the page setup as follows
Top Margins 2 cm
Left Margins 2 cm
Gutter 0.5 cm
Gutter Position Top
Exercise 3.8: Using Templates SM1
MS - Word has a large number of Templates available for typical requirements for creating Resume/ CV/ Bio Data,
Reports, Newsletters, Certificates, Letters, Fax Cover Sheets etc. These Templates provide the structure within which
we have to fill in our details, make minor adjustments and get beatifically designed documents. Some of these
templates are available on your computer and you can also download additional templates from Microsoft site.
In this exercise, Students are required to create a NEW document, select desired Resume template, fill in our details
and generate our CV/ Bio Data.
Solution:
Step-1: Now create your resume by filling in the details.
Note: This is just one of the several templates available in Word. Student can choose any of the predefined templates.
Hint: Click the Office Button, click on New. Choose any of the template and click on Create.
Exercise 3.9: Creating a New Template New
In the earlier exercise we had seen that MS - Word has many Ready-Made Templates that we can use to generate our
required report. What if we require a the Resume of people applying to our organisation to come in a particular format
with required details? Yes, we can create our own template and either send it to prospective candidates to fill or host
the template on our website for prospective candidates to fill. We can similarly create templates for the Purchase
Orders, Goods Inwards Note, Invoice, Vouchers etc. Templates bring standardisation of data/ information, better
presentation and compliance to requirements.
Students are required to adopt one of the ready-made templates for use by their organisation and save it as a template,
to be used for their organisation.
Solution:
Step-1: Select new option from File Menu
Step-2: Select My Template option from Available Template Group
Step-3: Select Blank Document from Personal Templates Tab and select Template in Create New Group then click on
ok button.
Step-4: A Template Document open, now select Page border option form Page Layout Ribbon. (as shown in figure)
select the border as your need and click on ok button.
Step-5: Type “Institute of Chartered Accountant of India” at first Line, Align it as Centre and underline it. At next line at
right side type Date and underline it.
Step-6: Save the file with name MyTemplate

Step-7: Close the file (MyTemplate) from File Menu.
Step-8: Select New option from File Menu and then select My Template as shown in figure below
Step-9: Click on My Templates option
Step-10: Select MyTemplate.docx and click on ok Button and check the result
5. Chapter 4 : Page Formatting, Printing and Mailing

Exercise 4.1: Mail Merge SM1
We often need to send the same letter or e-Mail to a large number of people wherein Name, Address, e-Mail id, amount
etc. changes for each of the recipient. This process is called “Mail Merge”. One such example is Confirmation of
Balances from Debtors and Creditors. Another example is Meeting Notice to members of a Committee/ Group/ Society.
Mail Merge is hence a very useful facility of MS - Word and hence there is a need to have a good working knowledge of
this facility. We can either print Mail Merge Letters or even send e-Mails, provided MS-Outlook has been configured on
your computer. One constraint of Mail Merge in MS-Word is that attachments cannot be directly sent along with e-Mails.
Students are required to Mail Merge the notice of forthcoming Information Technology examination to the Students of
the current batch of the Course.
Solution:
Step-1: Add the following contacts in Microsoft Office Outlook.
Name E-Mail Address City
Mayur Kumar mayurkumar@gmail.com Delhi
Vikas Arora vikasarora@gmail.com Chandigarh
Alok Gupta alokgupta@gmail.com Delhi
Anoj Khurana anojkhurana@gmail.com Delhi
Ankur Sehgal ankursehgal@gmail.com Kolkata
Rishi Sinha rishisinha@gmail.com Bangalore
Step-2: Create and save a new word document. Write the following e-mail in the document
Dear <student name>
The schedule for online examination of Information Technology Training is:
Date: (Here type date as current coming Sunday)
Time: 10:00 a.m. Venue: Lab - 1
You are required to be seated before the commencement of the examination. No late entries will be allowed.
Step-3: Change the font colour of the text to Red and font size to 14.
Step-4: Use the student name from Outlook contacts (take only those students in recipient list who are studying in
Delhi) and make it a mail merged field. Change its font colour to Blue.
Step-5: Send the e-mail to all the students who are studying in Delhi. The subject of the e-mail should be ―Schedule of
online examination.
Exercise 4.2: Envelopes SM1
While working in offices, we need to send Letters and Reports to clients/ vendors/ other stake holders, for which we
need to print the Name and Address of the recipient on the envelope in which the letter/ report has to be inserted. MS -
Word has a very useful facility to print envelopes, which we shall explore as a part of this exercise. Create an Envelope
and print address as per steps detailed in the solution.
Solution:
Step-1: Create and save a new word document.
Step-2: Create an envelope with the delivery address
ICAI,
A-29/4, Sec-61,
Noida
Step-3: Change the envelope size to Monarch.
Step-4: Print the envelope.
Exercise 4.3: Printing Envelop and Label in MS - Word New
We at times need to send a large number of letters (for example to the Debtors and Creditors, to confirm balances), for
which we need to print the addresses on Envelopes or Labels. This can be done through the Mail Merge facility. We
shall explore the process of printing Envelop/Label with Student Address for sending a letter to all students, as a part of
this exercise.
Solution:
Step-1: Go to Mailings menu in MS - Word
Step-2: Select Option Envelops through Start Mail Merge Tab
Step-3: Select Envelop Size from Envelop Dialog box

Step-4: Select Recipients List (Type New List/ Use Existing List)
Step-5: Select Data Source file from appropriate location (Exercise_4.3.xlsx from MS - Word folder)
Step-6: Select Excel Sheet in which Data have
Step-7: Place cursor at appropriate Location and insert fields from Insert Merge Fields.
Step-8: Click on Preview Result

Label Printing
Step-1: Select Label Option through Start Mail Merge Tab in Mailings Ribbon
Step-2: Select Label Size through Label Dialog Box (Select Label Vendors- A-ONE and Product Number is – A-ONE
26505)
Step-3: Select Recipients list (Use Existing List)
Step-4: Select Data Source file from appropriate location (Exercise_4.3.xlsx from MS - Word folder)
Step-5 Select Excel Sheet in which Data have and click on ok button
Step-6: Place Cursor at first column and insert field through Insert Merge Field. (Insert fields Name, Address, Contact
No, Email Id)
Step-7: Click on Update Label button, as you click on Update Label button all the rows and column automatically filled
with field name.
Step-8: Click on Preview Results

Exercise 4.4: Printing Payslips for Employees through Mail Merge New
Print Payslip for Employees of M/s. ABC Co. for the Month of April, 2016 in the following format using data available in
an MS - Excel File:
ABC Company Ltd.
Payslip for the month of April-2016
Employee ID 1006 Dept. Information Technology
Name Vikas Kumar Designation Asst. Manager
Earnings Amount (Rs) Deductions Amount (Rs)

Basic 15000 Employee PF 3500
Grade Pay 4500 TDS 750
Dearness Allowance 20000 Employee Welfare 150
HRA 7500
Transport Allowance 3500
Gross Earnings 50500 Total Deductions 4400
Net Pay 46100
Solution:
Step-1: Prepare a Payslip format as given above in MS Word.
Step-2: Go to Mailing Tab in MS Word 2010.If you want to use Excel Data Source format as currency, Date etc. as in
your Word Document then follow these steps.
Step-3: In Word, click File > Options > Advanced.
Step-4: Scroll to the General section, and select the option - Confirm file format conversion on open.
Step-5: Click OK.

Step-6: After you start your mail merge and choose the kind of merge you want to run, it’s time to connect to your data
list. Because Dynamic Data Exchange is turned on, you’ll receive a few extra prompts.
Step-7: In the mail merge document, click Mailings > Select Recipients > Use an Existing List.
Step-8: Select Data Source file from appropriate location (__________.xlsx from MSWord folder)
Step-9: Click MS Excel Worksheets via DDE (*.xls), and then click OK.
NOTE: If you don't see MS Excel Worksheets via DDE (*.xls), select the Show all check box.
Step-10: Click Entire Spread sheet, and then click OK.

Step-11: Click on Insert Merge Field and Select desire Field from the display List and place it at your Payslip Format
with match field.
ABC Company Ltd.
Payslip for the month of April - 2016
Employee ID «Empid» Dept. «Dept»
Name «Name» Designation «Designation»

Basic «Basic» Employee PF «PF»
Grade Pay «GradePay» TDS «TDS»
Dearness Employee
«DA» «EmpWel»
Allowance Welfare
HRA «HRA»
Transport
«TA»
Allowance
Gross
Gross Earnings «GrossEarnings» «GrossDeduction»
Deductions
Net Pay «NetPay»
Step-12: Click on Preview Results to show desire Data from the Data Source in your Payslip Format.
Step-13: Click on Preview Results to show desire Data from the Data Source in your Payslip Format.
ABC Company Ltd.
Payslip for the month of April-2016
Employee ID 1001 Dept. Information Technology
Name Anna Designation Manager

Basic 20,500 Employee PF 3,108
Grade Pay 5,400 TDS 5,051
Dearness Allowance 15,540 Employee Welfare 150
HRA 6,475
Transport Allowance 2,590
Gross Earnings 50,505 Gross Deductions 8,309
Net Pay 42,197
Step-14: To Print Payslip for all Employees Click on Finish Merge and Select Print Documents
Exercise 4.5: Change Measurement form Inches to Centimetre New
The Measurement of Page Size, Margins etc., can be given in Inches or Centimetres or Points. Some people are
comfortable in working with measurements using Inches, while others may prefer to measure in Centimetres. We shall
learn the process of changing the measurement medium from Inches to Centimetres, in this exercise.
Solution
Step-1: select Options from File menu
Step-2: Select Advanced from Options Window, go to Display section
Step-3: In Display section  Set Show measurement in units of to Centimetres as shown below in figure
Exercise 4.6: Header & Footer New

It is generally found that many of our letters/ reports/ documents run into a large number of pages and it hence
becomes important to number them. We shall learn to give Headers and Footers to our document, as a part of this
exercise.
Solution
Step-1: Select demo2.docx file and place cursor at the end of page.
Step-2: Select Next Page in Section Break (Page Layout Ribbon Page Setup Breaks Section Break Next
Page)
Step-3: Go to Header/Footer Section & type Header and Footer as per your requirement on page one. (Eg ICAI-DELHI)
Step-4: Then place the cursor at Header Section, Design Ribbon associated with header and footer is open / inserted at
your Ribbon Tab as shown below in figure
Step-5: Disable Link to Previous by Click on it. (Click on Link to Previous option to disable it)
Step-6: Type ICAI - Mumbai at header Section
Setp-7: Same steps apply for all other pages where you want a different header and footer at each page.
Exercise 4.7: Header & Footer SM1
Create and format a new word document as per contents and output given below
CONTENTS
INTERNET BASICS
LEARNING OBJECTIVES
During this chapter, we will learn:
� Definition of Internet
� History of Internet
� ARPANET Objectives
� Internet Connection Requirement
� Use of Internet
� General Use of Internet
� Sector wise Use of Internet
� Advantages of Internet
� Disadvantage of Internet
1.1 INTRODUCTION
In the present age, information including access to the Internet has become the basis for personal, economic
and political advancement. Whether you want to find the latest financial news, browse through library
catalogues, exchange information with colleagues or join in a lively political debate, the Internet is the tool that
will take you beyond telephones, faxes and isolated computers to a burgeoning networked information frontier.
Internet is also defined as “Information Superhighway”. The information superhighway was a popular term
used through the 1990s to refer to digital communication systems.
1.1.1 Definition of Internet

The Internet, sometimes called
simply “the Net,” is a
worldwide system of computer
networks - a network of
networks in which users at any
one computer can, if they have
permission, get information
from any other computer.
Solution
Exercise 4.8: Creating an Invitation Card SM1

We often need to send-out invitations or issue Certificates of Appreciation, for which we use MS-Word. Students are
required to create an invitation card for the Grand Opening of an Athletic Club in the following format.
Assignments – MS Word 2010
Assignment 1
Create a macro with the name Smiley assigned to the smiley face shaped button. Smiley shape button should be
added to “Customize Quick Access Toolbar”. Now click the smiley button. The smiley face should get inserted at cursor
location in the document.
Assignment 2
Create a document and type some text and Save the document as PDF file.
Assignment 3
To combine a number of documents into one long document you can insert the contents of a file into a section of
another document.
Assignment 4
Page numbers are an important part of any document as they help you to navigate your way through the pages. In
Word you can set your page numbers to run consecutively across the pages or, if you have used section breaks in the
document, to restart when you reach a new section. It is even possible to reformat the page numbers between sections.
For example you can have Roman numerals for an introductory section and Arabic numerals for the main part of the
document.
Assignment 5
Create a new document style with name MyFirstStyle. (Font Name – Arial, Size - 16, Style – Bold)
Assignment 6
Taking a screen shot, then Crop your screen shot, centring and resizing the screen shot and finally adding a picture
border to your screen shot.
Assignment 7
Create a Table with three columns with the help of Tab. First two columns are left aligned and third one is centre
aligned and last one is right aligned.
Assignment 8
Making Word Documents Read Only It is sometimes desirable to let readers know a document is final so they won't edit
the document.
Assignment 9
Create a new word document and type function “=RAND(10,10)” at first line and convert text in three columns.
Assignment 10
Creating Multi-level list & Smart Art graphic
Given below is the data pertaining to a Sales Department and its Hierarchy, based on this data, do the following:
Sales Supervisor Sales Director
Distribution Manager Sales Director
Business Manager Sales Director
Promotion supervisor Sales Director
Logistics supervisor Sales Director
Sales Engineer Sales Supervisor
Sales Representative Sales Supervisor
Distribution Executive Distribution Manager
Business Assistant Distribution Manager
Business Representative Business Manager
Distribution Executive Promotion supervisor
Logistics staff Logistics supervisor
Staffs Distribution Executive
Staffs Distribution Executive
a) Create a multilevel list of all designations depicting the hierarchical structure of Sales dept. with Sales Director
as the Main Level.
b) Change the font type to ALGERIAN and font size to 16.
c) Insert a Smart Art Graphic to depict Sales hierarchy graphically.
d) Insert a Header “Sales Hierarchy” and a Footer containing page number and align it to Centre.
e) Insert a Page Border.
f) Add a watermark “SALES DEPT.”
g) Under the File Tab, Click Print Preview to check the output of the document.
h) Finally copy the all content to the next page and the remove the formatting throughout.
Assignment 11: Creating a macro for customized page setup
Create a macro named, Customdoc. Once the macro starts running, do the following:
a) From the Page Layout tab, click Page Setup -> Size.
b) Select More Paper Sizes from the drop-down menu.
c) In the Page Setup dialog, select the Paper tab, click Paper Size, choose Custom from the drop-down list.
d) Enter a width size of 5.5 inches and a height size of 8.5 inches and click OK.
e) Again, from the Page Layout tab’s Page Setup group, click the Margin button.
f) Select Custom Margins from the drop-down list.
g) In the Page Setup dialog, choose the Margins tab.
h) From Pages > Multiple Pages, select Normal from the drop-down list. For Sheets per Booklet, select All.
i) Enter margins: Top = 1, Bottom = 1, Left = .5, Right = .5, Gutter = .3 and Gutter Position = Left.
j) For Orientation, choose Portrait, and click OK.
k) Next, select Paragraph under the Page Layout tab. Click the Indents and Spacing tab.
l) In the General panel, select Alignment = Justified; Outline Level = Body Text.
m) In the Indentation panel, select Left = 0; Right = 0; Special = First Line; by = .25.
n) In the Spacing panel, select before = 0; after = 0; Line Spacing = Multiple; at = 1.25. And click OK.
o) Enter a paragraph or any general text.
p) Lastly go to the View tab again, and click Stop Recording.
q) Open a new document and run this macro.
Assignment 12
Creating a Calendar of May 2016 as shown below using the various Table properties.
Assignment 13: Sending same letter to multiple recipients using Mail Merge
a. Using mail merge , send letter of confirmation to students informing them that they have been selected as
articles in your CA firm.
b. The data source has been provided in Excel in the assignments folder (Address list.xlsx).
c. Create the Merged letter with needed formatting and text.
Assignment 14: Document Formatting

A data entry operator has prepared a document having some sales information of the ABC Pvt. Ltd. But he has left it
unformatted. Open the new word document file and complete it by doing the necessary formatting.
a. Design a name and logo for the company using a graphic and custom style. Create custom styles for the
document’s heading.
b. Create a custom style for the Earnings table and add it to the table gallery.
c. Adjust the size of the Outlook chart.
d. Modify the colour, size, and position of the Forecast clip art.
e. Divide the document into the two columns.
Assignment - 15
The director of The Sports Connection would like a list of members of the Advisory Council to distribute to the council.\
Create the Advisory Council list using the handout and the following instructions:
a. Create a table with an appropriate number of rows and columns. Merge cells where needed.
b. Enter the information in the cells of the table. You may use an appropriate font and different font sizes.
c. Make sure the entire document fits on one page (change margins if necessary). Adjust column widths if
necessary.
d. Centre the table vertically and horizontally.
e. Choose a table design to make the table look attractive.
f. Save this document as ADVISORYCOUNCIL ROSTER.
3.
MS – Excel 2010
1. Introduction
The advent of Personal Computers in eighties saw the introduction of three very useful Office Productivity Software
including Lotus 1-2-3, dBase and Word Star that really changed the way computers were used. Lotus 1-2-3, Electronic
Spreadsheet Software soon emerged to be the most useful software for Accountants. Soon thereafter, Microsoft
released its integrated Office Productivity Software named MS-Office with a unified interface, which included MS -
Excel the most commonly used Electronic Spreadsheet Software (ESS) today. The ESS enables us to import or feed
required data and undertake analysis as per requirements.
The Workbook of Electronic Spreadsheet consists of Worksheets. Each Worksheet consists of Columns and Rows.
The intersection of a column and row is called a Cell, in which we can enter text, numbers or formulas, as per our
requirements. We can analyse the data in Cells and Worksheets, to generate our required reports – For example Trial
Balance and Final Accounts.
As a part of our study on usage of MS - Excel, we shall be covering the Illustrations and Case Studies as detailed in the
following table, in the respective chapters
MS – Excel 2010
1 Introduction to MS Excel 2010 2
2 Cell Referencing Ranges and Functions 18
3 Working with Worksheets, Charts, Macros and Hyperlinks 6
4 Consolidation of Data and Data Analysis 12
5 Data Validation and Protection 2
6 PivotTables Reports and PivotCharts Reports 2
7 Importing / Exporting Data 1
8 Multi-Dimensional Analysis of Data 3
9 Dashboard Reporting using Excel 1
Total 47
We shall study the use of MS - Excel Spreadsheet software for importing/ exporting/ consolidating of data from/ to
multiple formats as required and perform data analysis for assurance, collecting and evaluating evidence, performing
what if analysis, statistical analysis and reporting/ exporting to various formats such as XML etc. and presenting in form
of pivot tables, charts as relevant for business/ control issues of accounting, costing, budgeting, tax computations etc.
2. Chapter 1 : Introduction to MS - Excel 2010

Exercise 1.1: Build a Balance Sheet New
Step 1: Create a title page.
In cell D1, type ABC CONSULTING LIMITED
In cell D2, type BALANCE SHEET
In cell D3, enter today‘s date using formulae.
Notice that the headings do not fit in the cells. To make the cells accommodate the headings, we can widen the
columns so the headings will fit. Save the spread sheet as ―bal.xlsx.
Step 2: Enter data into the worksheet and navigate within the worksheet.
Type the following text in the appropriate cells.
CELL TEXT
B4 INPUT SECTION
B6 Cash in Bank
B7 Petty Cash
B8 Marketable Securities
B9 Accounts Receivable
B10 Allowance for Bad Debts
B11 Inventory
B12 Office Supplies
B13 Prepaid Insurance
B14 Land
B15 Buildings
B16 Accumulated Depreciation—Buildings
B17 Warehouse
B18 Accumulated Depreciation—Warehouse
B19 Trucks
B20 Accumulated Depreciation—Trucks
B21 Office Equipment
B22 Accumulated Depreciation—Office Equipment
B23 Accounts Payable
B24 Notes Payable, Short-Term
B25 Interest Payable
B26 Income Tax Payable
B27 Liability for Payroll Taxes
B28 Notes Payable, Long-Term
B29 ABC, Capital
Now, widen Column C to 14 characters.
Next, input the following numerical values in the appropriate cells.
CELL VALUE
C6 7115.15
C7 400
C8 5000
C9 24100
C10 615
C11 65000
C12 1480
C13 5135
C14 10000
C15 65000
C16 9750
C17 30000
C18 5400
C19 16000
C20 9000
C21 8700
C22 3915
C23 14190
C24 25000
C25 215
CELL VALUE
C26 300
C27 1800
C28 70000
C29 97745.15
These are INR values, so we must add INR signs and decimal points.
Next, add these titles in the appropriate cells for the Assets section of the Balance Sheet.
CELL TEXT
D4 ASSETS
D6 Current Assets
D15 Total Current Assets
D17 Noncurrent Assets
27 Total Noncurrent Assets
D28 Total Assets
Now, we will begin copying the account titles and numerical values from the input section into the actual balance sheet.
Point to cell B6 and copy the cell to D7.
Now, copy the titles in cells B7 through B13 to cells D8 through D14.
Next, copy B14 through B22 to D18 through D26.
To improve the appearance of our balance sheet, we need to edit a few cells.
Point to cell D7 and click the left mouse button, tap the F2 key to begin editing the cell. Use the arrow keys to move the
cursor in front of the words Cash in Bank. Insert three spaces in front of the words. Repeat this for cell D8 through D14.
Insert six spaces in front of Total Current Assets in cell D15.
Edit cells D20, D22, D24, and D26 by placing the word Less: at the front of each cell. For example, cell D20 should
read Less: Accumulated Depreciation—Buildings
Insert three spaces in front of the words in cells D18 through D26.
Insert six spaces in front of the words in cell D27
Step 3: Enter formula and copy cell contents and formula.
We have all of the titles in place for the Assets part of the balance sheet, now we must copy to the titles for the
Liabilities and Owner‘s Equity part.
Go to the following cells and type in the appropriate text
CELL TEXT
D31 LIABILITIES AND OWNER’S EQUITY
D33 Current Liabilities
D39 Total Current Liabilities
D41 Noncurrent Liabilities
D43 Total Noncurrent Liabilities
D44 Total Liabilities
D47 Owner‘s Equity
D49 Total Owner‘s Equity
D50 Total Liabilities and Owner‘s Equity
Next, copy B23 through B27 to D34 through D38.
Insert three spaces in front of the words in cells D34 through D38
Insert six spaces in front of Total Current Liabilities in cell D39.
Copy B28 to D42 and insert three spaces in front of the words in cell D42.
Insert six spaces in front of the words in cells D43 and D44.
Copy B29 to D48 and insert three spaces in front of the words in cell D48.
Insert six spaces in front of the words in cell D49.
Next we must copy the numerical values from the input section to the actual balance sheet. However, because we want
the numbers in our balance sheet to change when we change the numbers in the input section, we cannot copy them
the same way we copied text.
Step 4: Copying Numerical Values
Plus-Point Method
To use this method, simply type a plus sign (+) in the new cell, then point to the cell where the number currently is and
click the left mouse button. You will see the current cell address appear in the new cell. When you press enter, the
numerical value will appear in the new cell. Notice that if you move back onto the new cell, the original cell address is
still there.
In the left column below, the destination cell addresses are listed. These are the cells where the specified task will be
performed. In the right column below, the task is listed. For each row, this is the task we must perform on the
destination cell. For tasks we have not yet discussed, instructions on performing the task will follow the task description.
For example: In the first row, F7 is listed as the destination cell and the task is to Copy C6. This means that you must
copy C6 to F7. Using the Plus-Point Method, you would go to cell F7, then type a plus sign and point to cell C6. Using
the Type-In Method, you would go to cell F7, then type a plus sign and type ‘C6’.
Destination Cell Task
F7 Copy C6
F8 Copy C7
F9 Copy C8
E10 Copy C9
E11 Copy C10
Underline E11: Point to E11 and click the right mouse button. Select Format Cells using a left mouse click, and then
select the Border tab using a left mouse click. Point to the bottom of the cell within the box and click. A line should
appear along the bottom of that cell. Click on OK with a left mouse click. There should be a line along the bottom of cell
E11.
F11 Using the Plus-Point Method, subtract E11 from E10. Type a+, point to E10, type a -, point to E11. Press enter.
F12 Copy C11
F13 Copy C12
F14 Copy C13 and Underline F14
F15 Using AutoSum, add F7 through F14.
To use AutoSum: Point to F7, click and hold down the left mouse button and drag down until F7 through F15 are
e total of F7 through
F14 will be placed in F15.
F18 Copy C14
E19 Copy C15
E20 Copy C16 and Underline E20
E20 F20 Using the Plus-Point Method, subtract E20 from E19.
E21 Copy C17
E22 F22 Using the Plus-Point Method, subtract E22 from E21.
E23 Copy C19
F24 Using the Plus-Point Method, subtract E24 from E23.
E25 Copy C21
F26 Using the Plus-Point Method, subtract E26 from E25 and Underline F26
F27 Using AutoSum, add F18 through F26 and Underline F27
F28 Using the Plus-Point Method, add F15 and F27 and Double Underline F28
To Double Underline: Do this the same way you underline, except before clicking on the bottom of the cell to place the
line there, click on the double underline from the choices to the right of the cell within the box.
Cell Particulars
F34 Copy C23
F35 Copy C24
F36 Copy C25
F37 Copy C26
F39 Using AutoSum, add F34 through F38.
F43 Copy F42 Underline F43
F44 Using the Plus-Point Method, add F39 and F43 and Underline F44
F49 Copy F48 and Underline F49
F50 Using the Plus-Point Method, add F44 and F49 and Double Underline F50
We have successfully transformed our input section into a balance sheet in proper format.
Solution: For above, use Exercise_1.1.xlsx file from MS - EXCEL folder.
Exercise 1.2: Household Budget New
MS - Excel helps us to creating Budgets and undertakes calculations very conveniently. It also re-calculates values, in
case some of the data changes. We shall take a very simple budget for our household budget and see how MS - Excel
makes the job easier and useful, as a part of this exercise.
Step-1: Select MS - Excel application and create a new blank Excel workbook.
Step-2: In this blank workbook enter following data as given
Step-3: Select cell B12 & B17 and then click on the AutoSum button in the Editing group on the Home tab.
Step-4: Select cell D2 and type =, then click on the cell B12 and also Select cell D13 and type =, the click on the cell
B17 to put same formula here.
Step-5: Select Column A to D and click on Merge & Centre available in Alignment group of Home tab to make column
wider as shown in figure.
Step-6: Select the sheet as shown in below figure and go to Font group select Arial Narrow font and font size as 11.
Step-7: Save this file as My_Budget.xlsx

3. Chapter 2 :Cell Referencing Range and Functions

Exercise 2.1: Cell references (Relative Cell Reference) New
In MS - Excel worksheet data is arranged into Columns and Rows. Interaction of a Row and Column is Called a Cell,
For example A1, A2, B1, B2, C5 etc. A cell has a Name (for example A1, A2) and place to store data. Data stored in a
Cell can be referenced or used using the Cell Name.
While doing our computations and analysis using MS - Excel, we use formulae. The Formulae and Functions use data
stored in the Cells.
In life we have friends and relatives.
A) Calculate the Values according to the following steps using various calculated methods.
Q1:- Find the Total payment of organization of all employees at the end of year
Q2:- Find the Average Tax Rate of the organization Tax deducted to the employee
Q3:- Find the Tax of Each Individual Employee according to the months?
Q4:- Find the total payment of organization for the Income tax department
Solution: For above, use Exercise_2.1_A sheet from Exercise_2.1.xlsx file from MS - EXCEL folder.
Relative Cell Reference
Month Salary Tax Rate Output Tax
January 978489 10% 97848.9
February 449488 20% 89897.6
March 768048 10% 76804.8
April 917271 30% 275181.3
Relative Cell Reference
Month Salary Tax Rate Output Tax
May 776714 20% 155342.8
June 382921 30% 114876.3
July 201863 40% 80745.2
August 730144 20% 146028.8
September 142381 20% 28476.2
October 900174 40% 360069.6
November 276381 10% 27638.1
December 375378 20% 75075.6
Total Amount of Organisation at the End of Year 6899252 23% 1527985.2
B) Cell references (Absolute Cell Reference)
Calculate the values according to the following steps using various calculated methods through Absolute Cell
Reference:-
Q1:- Find the Total payment of Organization of all employees at the End of Year
Q2:- Find the Tax of Each Individual Employee according to the months and rate is fix that is C15?
Q3:- Find the total payment of Organization for the Income tax department
Solution: For above, use Exercise_2.1_B sheet from Exercise_2.1.xlsx file from MS - EXCEL folder.
Absolute Cell Reference
Month Salary Tax
January 978489 146773.35
February 449488 67423.2
March 768048 115207.2
April 917271 137590.65
May 776714 116507.1
June 382921 57438.15
July 201863 30279.45
August 730144 109521.6
September 142381 21357.15
October 900174 135026.1
November 276381 41457.15
December 375378 56306.7
Total Amount of Organisation at the End of Year 6899252 1034887.8
Tax Rate:- 15%
C) Cell references (Mixed Cell reference)
Calculate the values according to the following steps using various calculated methods through Mixed cell Reference.
Q1:- Find the output in to the Column D according to Tax rate C2 onwards Column Fix?
Q2:- Find the output in to the Column E according to Tax rate C2 onwards Row Fix?
Q3:- Find the output in to the Column F according to Tax rate C15 Column Fix?
Q4:- Find the output in to the Column G according to Tax rate C15 Row Fix?
Q5:- Which output is correct according to the cells of D, E, F, G column and type in Row 14 of respective cell (Correct
or Incorrect)?
Q6:- Type the Conclusion in the Following Blanks:
The Correct output columns are ____and ____.
The incorrect output columns are ____and ____.
Solution: For above, use Exercise_2.1_C sheet from Exercise_2.1.xlsx file from MS-EXCEL folder.
Mixed Cell Reference
Month Salary Tax Tax Column Tax Row Tax Column Tax Row Fix
rate Fix Output Fix Output Fix Output Output
January 978489 10% 97848.9 97848.9 146773.35 146773.35
February 449488 20% 89897.6 44948.8 0 67423.2
March 768048 10% 76804.8 76804.8 0 115207.2
April 917271 30% 275181.3 91727.1 0 137590.65
May 776714 20% 155342.8 77671.4 0 116507.1
June 382921 30% 114876.3 38292.1 0 57438.15
July 201863 40% 80745.2 20186.3 0 30279.45
August 730144 20% 146028.8 73014.4 0 109521.6
September 142381 20% 28476.2 14238.1 0 21357.15
October 900174 40% 360069.6 90017.4 0 135026.1
November 276381 10% 27638.1 27638.1 0 41457.15
December 375378 20% 75075.6 37537.8 0 56306.7
Total Amount of
Organisation at the End 6899252 23% Correct Incorrect Incorrect Correct
of Year
Tax Rate:- 15%
Exercise 2.2: Functions (Min, Max, Small, Large and If) New
1. Create the following range names
2. Use the range names you have created to calculate
Range Name Range
the following values
Budget C3:C22
Profit - subtract Budget from Gross
Gross D3:D22
Tickets Sold - divide Gross by Ticket Price
Profit E3:E22
MIN and MAX of Profit and Tickets Sold
Tickets Sold F3:F22
2nd MIN and 2nd MAX of Profit and Tickets Sold
Tickets Price H3
Logical Operation
If the profit was less than 100,000,000 then the movie is flop Otherwise the movie is a success
Use of Functions (Min, Max, Small, Large and IF)
Average
World Gross Tickets Flop or
Movie Budget ($) Profit ($) Ticket
($) Sold Not
Price ($)
Spider Man 3 258,000,000 887,436,184 629,436,184 177,487,237 Success 5
King Kong 207,000,000 553,080,025 346,080,025 110,616,005 Success
Superman Returns 204,000,000 391,081,192 187,081,192 78,216,238 Success
Spider Man 2 200,000,000 784,024,485 584,024,485 156,804,897 Success
Bajirao Mastani 200,000,000 1,835,400,000 1,635,400,000 367,080,000 Success
Jungle Book 180,000,000 748,806,957 568,806,957 149,761,391 Success
Wild Wild West 175,000,000 217,700,000 42,700,000 43,540,000 Flop
FAN 175,000,000 120,698,890 54,301,110 24,139,778 Flop
Water World 175,000,000 264,246,220 89,246,220 52,849,244 Flop
Speed – 1 170,000,000 433,058,296 263,058,296 86,611,659 Success
Polar Express 170,000,000 296,596,043 126,596,043 59,319,209 Success
Van Helsing 170,000,000 300,150,546 130,150,546 60,030,109 Success
Fast & Furious 160,000,000 733,012,359 573,012,359 146,602,472 Success
Shrek the Third 160,000,000 181,674,817 21,674,817 36,334,963 Flop
Alexander 155,000,000 167,297,191 12,297,191 33,459,438 Flop
Pearl Harbor 151,500,000 450,500,000 299,000,000 90,100,000 Success
Hulk 150,000,000 892,213,036 742,213,036 178,442,607 Success
Speed – 2 150,000,000 822,828,538 672,828,538 164,565,708 Success
Mission Impossible 150,000,000 397,501,348 247,501,348 79,500,270 Success
Troy 150,000,000 497,298,577 347,298,577 99,459,715 Success
Highest 1,635,400,000 367,080,000

Lowest 12,297,191 24,139,778
Second Highest 742,213,036 178,442,607
Second Lowest 21,674,817 33,459,438
Exercise 2.3: Round Function and Format Option New
The Round Function rounds a number to a specified number of digits.
Syntax: ROUND (number, num_digits) where number is the number to be rounded and num_digits specifies
the number of digits to which the Number is to be rounded
ROUNDUP (number,num_digits) here a real number is rounded up depending on the second argument num_digits.
If num_digits > 0, then number is rounded up to the specified number of decimal places. If num_digits = 0, then number
is rounded up to the nearest integer.
ROUNDDOWN (number,num_digits) here a real number is rounded down depending on the second argument
num_digits.
If num_digits > 0, then number is rounded down to the specified number of decimal places.
If num_digits = 0, then number is rounded down to the nearest integer.
Using the Format Cells option:
The Number tab of the Format cells dialog box has the options Number, Currency, and Accounting categories which
allow decimal places configuration for the selected number of cells. We can specify up to 30 decimal places.
Note: - Create the excel file same as output
Hint: To show the formula in a cell the user use ‘ sign like ‘=Round(A3,0)
Exercise 2.4: Change Case & Trim New

The Text functions UPPER, PROPER, LOWER and TRIM are used to convert text from uppercase to lowercase or
from lowercase to proper case to make it more readable and presentable. We shall learn to use these functions in this
exercise.
Solution: For above, use Exercise_2.4.xlsx file from MS-EXCEL folder.
Exercise 2.5: If Function New

The logical IF function is used to set a particular value in a cell based on a conditional expression. This function can be
used to conduct conditional tests on values and formulas.
Syntax IF (logical_test,value_if_true,value_if_false)
 Logical_test - is an expression that can be evaluated to TRUE or FALSE.
 Value_if_true - is the value that is returned if logical_test is TRUE.
 Value_if_false - is the value that is returned if logical_test is FALSE.
Exercise 2.6: Min & Max Function New

The Min and Max functions returns the smallest number in a set of values in a range. Syntax: MIN (number1,
number2,…..)
Exercise 2.7: Validation of a given PAN number New

The PAN number is composed of characters and digits, the first five of which are alphabets, the next four are numbers
and the last tenth is an alphabet as depicted in the following image:
We shall learn the process of validating that this pattern is in fact followed, in this exercise.
 Length of the PAN number = LEN (C3)
 Name the vectors: ALPHA –I6:I31 NUMERIC –K6:K14
 Second Verification Composition formulae are:
 First alphanumeric digit of the PAN number = LEFT (PAN number, 1)
 Right most alphanumeric digit of the PAN number = RIGHT (PAN number, 1)
 To verify the alphanumeric digit of the PAN number belongs to the ALPHA vector =
 LOOKUP (=LEFT (PAN number, 1), ALPHA, ALPHA)
 To verify the result of the lookup function = ISERROR (=LOOKUP (=LEFT (PAN number, 1), ALPHA, ALPHA))
Exercise 2.8: Find the rank of an Information Technology student New

Find the rank of all the students based on the given data. In exercise2.8.xlsx
 Round the average marks to the nearest integer down.
 If average marks and marks in module1, module2, project and online examination are all more than 60, then the
student can be declared as Information Technology qualified.
 Grade is assigned as
Average Marks Grade
>=70 a
>=60 b
>=50 c
<50 d
 Use Rank () function for computing rank of Information Technology qualified students. If a student has failed, just
write n/a.
Exercise 2.9: Using functions to perform calculations on the table New
Step 1: Prepare a report on the subscription rates and % saving up to 10 years under given conditions.
 There are 4 issues per month.
 Each issue costs Rs.15.
 For a subscription >= 2 years, the discount is 15% on the yearly cost.
 For a subscription >=5 years, the discount is 18% on the yearly cost.
 For a subscription more than 7 years, the discount is 22% on the yearly cost.
Year/s No. of Issues You Pay You Save % Saving
1
2
Step 2: Share of trade for modern self-service outlets in the year 2001, 2002, 2003 in different countries:
Number of outlets counted % Increase in % Increase in
Country
2001 2002 2003 2002 2003
Singapore 81,000 83,000 86,000
Malaysia 42,289 45,900 49,898
Philippines 43,212 43,532 40,233
India 2,321 4,673 7,900
Sri Lanka 3,799 4,234 9,676
Calculate the shown columns and highlight the countries where the trade has a significant increase of more than
25% in the year 2002 and 2003.
Step 3: The advertisement revenue of leading newspapers in Mumbai:
Brand Name Hindustan Times Indian Express The Hindu The Times of India
Adidas 41,700 95,343 7,302 28,800
Britannia 37,69,241 1,17,488 23,799 99,480
Samsung 19,29,550 7,84,055 1,33,639 13,48,832
MRF 2,38,470 58,874 11,641 1,41,764
 Calculate the total advertisement revenue for the newspapers.
 Calculate the total advertisement expenditure made by a brand.
 Show the % of expenditure made by a brand with different newspapers.
Solution:
For above, use Exercise_2.9.xlsx file from MS - EXCEL folder.
Exercise 2.10: Implement the formulae for calculating VAT New
Calculate the VAT based on the given data. In exercise2.10.xlsx
 Calculate VAT and Net Sales (Sales + VAT) using the relative cell references with the formula. E.g. (=C4 * C24)
 Also use Excel built in functions for calculating Total Sales, Average Sales, Maximum Sales, and Minimum Sales.
 Calculate VAT and Net Sales (Sales + VAT) using the absolute cell references with the formula. E.g (=C4 * C$24)
Exercise 2.11: To compute the Net Tax payable for an individual New
Mr. X a resident assessed, his date of birth is 2nd Jan 1980. He is a govt. employee. He resides in Madras. He receives
the following emoluments during the previous year ending March 31, 2010
Basic salary Rs. 3,00,000 per annum
House rent allowance Rs. 50,000 per annum
Dearness allowance 20% of basic salary
Commission 2% of basic salary as per contract
Entertainment allowance Rs. 10,000 per annum
Medical allowance Rs. 16,000 per annum
City compensation allowance Rs. 6,000 per annum
Rs. 35,000. He contributes Rs. 35,000 in
He paid house rent
recognized provident fund, Subscription to
NSC, VIII issue Rs. 15,000.
Tax Deducted at source Rs. 2,500 per month
Solve the above problem using the following format:
Name Hemant Bhatia
DOB 12-12-1991 Age
Gender M
Salary Information
Basic Pay
DA
HRA
City Compensation Allowance
MA
Commission
Entertainment allowance
Any other Allowances
Gross Income Rs.
Deductions:
Provident Fund
NSC, VIII issue
House Rent Paid
Total Deductions
Net Taxable Income Rs.
 Implement the formulas for calculating Gross income, Total Deductions, Net taxable income.
 Calculate the tax liability of Mr. X in the given format using function if ( ) wherever applicable.
Tax
Surcharge
Education Cess
Higher Education
Cess
TOTAL TAX
Less: TDS
NET TAX
Use the following Tax slab for Tax calculations
Net Income Range Income Tax rate Surcharge Education Cess Higher Education Cess
Up to Rs 1,60,000 Nil Nil Nil Nil`
Rs. 1,60,000 – 10% of (Total Income –Rs.
Nil 2% of Income Tax 1% of Income Tax
Rs.3,00,000 1,60,000)
Rs. 3,00,000 – Rs. Rs. 14,000 +20% of (Total
5,00,000 Income –Rs. 3,00,000)
Rs. 5,00,000 – Rs. Rs. 54,000 +30% of (Total
10,00,000 Income –Rs. 5,00,000)
Rs. 2,04,000 +30% of (Total 2% of (Income Tax + 1% of (Income Tax +
Above Rs. 10,00000 10% of Income
Income -Rs10,00,000) Surcharge Surcharge
Solution:
Exercise 2.12: Payroll Assignment New

Create a Payroll for the month of March 2016
Emp. No. Employee Name Basic Salary Joining Date
1 P SUSHMA Rs. 15380.00 17/031975
2 P SUSHMA Rs. 6,700.00 17/03/2000
3 P. JANAKI Rs. 8,980.00 15/06/1973
4 CHHAYA Rs. 8,980.00 15/06/1978
5 ASHA SACHDEV Rs. 7,800.00 25/06/1967
 Insert a new column "Dearness Allowance" at the end and calculate it as 35.33% of Basic Salary.
 Insert a new column "House Rent Allowance (HRA)" and calculate it as 13% of Basic Salary.
 Insert two more columns such as Spl. Allowance (Rs. 2000.00), PF deduction (12% of Basic Salary).
 Last column is of Net Salary which is Basic Salary + DA +HRA+ Spl. Allowance - PF deduction.
 Sort the database in ascending order of basic salary.
 Format Emp. No, Emp. Name, etc. in italics.
 Save this sheet as "MAR"
 Format Joining Date as "dd.mm.yy"
 Save the file as "Payroll".
 Display the current date and time.
 Try the following functions.
a) Now() b) Today() c) Lower() d) Left() e) Proper()
Solution:
Exercise 2.13: Employees Data Using Various Function New
Solve the following Questions?
Enter the Values in a Basic Pay according to the slab rate of 200000 to 1000000 or more and calculate the
Q1:-
values according to following instructions
A HRA = 0.35 * Basic Pay
B DA = 0.09 * Basic Pay
C TA=.04*Basic Pay
D Total Salary = Basic Pay + HRA + DA + TA
Q2:- Calculate the Tax Slab Rate According to the following Table
Income Slabs Tax Rates
A Where the taxable income does not exceed Rs. 2,50,000/- NIL
Where the taxable income exceeds Rs. 2,50,000/- but 10% of amount by which the taxable income
B
does not exceed Rs. 5,00,000/-. exceeds Rs. 2,50,000/-
Where the taxable income exceeds Rs. 5,00,000/- but Rs. 25,000/- + 20% of the amount by which
C
does not exceed Rs. 10,00,000/-. the taxable income exceeds Rs. 5,00,000/-
Rs. 125,000/- + 30% of the amount by which
D Where the taxable income exceeds Rs. 10,00,000/-.
the taxable income exceeds Rs. 10,00,000/-
Q3. Write the Values of Tax without decimal in L Column?
Q4. Find the Total Basic Salary in a J24 column according to the “EEMP”?
Q5. Calculate the Values which are less than 0 according to the column M?
Q6. Find the Grading of Tax Payer according to the following Table in column N
a) BASIC SALARY Less than 200000
b) T.A. Less than 10000
c) D.A. Less than 250000
d) HRA Less than 100000
e) TOTAL SALARY Less than 500000
f) TAX CALCULATOR Less than 50000
g) Then Minimum Tax Payer otherwise Maximum Tax Payer
Q7. According to the Q6 if any condition is true then result is Min otherwise Max. Find the Value in O Column?
Q8. Find the Total Basic Salary counting in J35 column according to the “NEMP”?
Q9. Find the Average salary according in J36?
Exercise 2.14: Annual Report New
Create Company‘s Annual Report as given below
Name Job Salary Joining Date
Prumi Analyst 12000 31/12/99
Suman Officer 12000 02/11/98
Sushma System Admin 15000 03/10/97
Chayya Manager 12000 04/05/00
The heading (i.e. Company’s Annual Report) should be centred. Format the table. Insert one column at the end and
give the title as "Bonus". Bonus is 15% of salary. Calculate the average salary of the company. Save this sheet as
Annual Report. Sort the database in ascending order of Salary. Rename the sheet as Salary Report and display the
current date and time.
Try the following functions.
a) Upper() b) Sqrt() c) Sum() d) Max() e) Min()
Exercise 2.15: Equated Monthly Instalment (EMI) statement New
We often need to compute EMI for loans taken for buying household goods, cars and home loans for given amount of
loan, period and interest rate. Fortunately, MS - Excel has a very useful function called PMT to help us undertake this
work. We shall learn to compute EMI using this function in this exercise.
Syntax: PMT (rate,nper,pv,fv,type) - Calculates the payment for a loan based on constant payments and a constant
interest rate.
Rate is the interest rate for the loan.
Nper is the total number of payments for the loan.
Pv is the present value, or the total amount that a series of future payments is worth now (Principal).
Fv is the future value, (Cash balance) to attain after the last payment is made. If fv is omitted, it is assumed to be 0
(zero), that is, the future value of a loan is 0.
Type: is the number 0 (zero) payment due at the beginning of the loan period or number 1(one) indicates payments due
at the end of the loan period.
Syntax: PPMT (rate, per, nper, pv, fv, type) - Returns the payment on the principal for a given period for an
investment based on periodic, constant payments and a constant interest rate.
Rate is the interest rate per period.
Per specifies the period and must be in the range 1 to nper.
Syntax: IPMT (rate, per, nper, pv, fv, type) - Returns the interest payment for a given period for an investment based
on periodic, constant payments and a constant interest rate.
Rate is the interest rate per period.
Per is the period for which you want to find the interest and must be in the range 1 to nper.
Limit sanctioned Rs.13925000
Total monthly instalments 48
Rate of interest 11.25% p.a.
EMI =PMT (11.25%/12,48,13925000)
Principal =PPMT(11.25%/12,(first instalment-cell reference),48,13925000)
Interest =IPMT(11.25%/12,(first instalment-cell reference),48,13925000)
Solution:
Instalment Due date for Payment Instalment (Rs) Principal (Rs) Interest (Rs)
1 1-Nov-07 3,61,592 2,31,045 1,30,547
2 1-Dec-07 3,61,592 2,33,211 1,28,381
3 1-Jan-08 3,61,592 2,35,397 1,26,194
4 1-Feb-08 3,61,592 2,37,604 1,23,988
5 1-Mar-08 3,61,592 2,39,832 1,21,760
6 1-Apr-08 3,61,592 2,42,080 1,19,512
7 1-May-08 3,61,592 2,44,350 1,17,242
and so on
47 1-Sep-11 3,61,592 3,54,906 6,686
48 1-Oct-11 3,61,592 3,58,233 3,358
Total 1,73,56,416 1,39,25,000 34,31,406
Exercise 2.16: Calculation of EMI, Maturity Amount New
Calculate EMI of a loan with annual interest rate of 6%, 20-year duration (240 months), a present value of Rs.150,000
(amount borrowed) and a future value of 0
Solution:
Exercise 2.17: Sum if Function New
Calculate the hours and cost using Sumif function according to the week.
Hour 40 65 25
Cost 2072 3765 1613
Exercise 2.18: Prepare Dice Using Functions New
Convert the 3*3 range and format the range
Convert this range into font style Wingdings or Webdings
After converting range into above font style, users press any key in this range only symbols are produced.
Now user use n or l for dice symbols
Create your Logic Using IF and Or functions for preparing the Output according to the solution
And Rand between function for generating the 1 to 6 Digits in a excel sheet automatically and then press F9 for
Random numbers.
Solution:
4. Chapter 3: Working with Worksheets, Charts, Macros and Hyperlinks

Exercise 3.1: Calculate Total Marks and Percentage New
Use percentage format to show percentage with % symbol.
Names Roll No. English Maths Science
Kunal 4 64.00 100.00 89.00
Akanksha 8 64.00 86.00 87.00
Kishore 11 65.00 96.00 68.00
Akash 15 65.00 56.00 89.00
Manish 7 94.00 42.00 65.00
Sunil 2 68.00 35.00 98.00
Mansi 9 21.00 76.00 98.00
Sanjay 5 44.00 88.00 56.00
Rajeev 14 55.00 67.00 47.00
Saurabh 13 45.00 87.00 37.00
Rajesh 15 55.00 85.00 25.00
Akash 12 29.00 29.00 95.00
Vishal 6 84.00 46.00 23.00
Deepa 10 35.00 66.00 45.00
Exercise 3.2: Conditional Formatting New
By using Exercise_3.2.xlsx file from MS - EXCEL folder perform following steps for output:-
Step1:- Insert an empty column in front of your table.
Step2:- Insert function Count Blank in empty cell to find out the empty cell in a row.
Step3:- Fill the data through relative cell reference in A column.
Step4:- Apply conditional format rule that is Format all cell based on their value.
Step5:- Select Icon sets.
Step6:- Change Icon style according to the output
Step7:- Reverse Icon Order
Step8:- Check show icon only
Step9:- Apply Rules to find out the data cell blank, row blank and show the output as per given below icon:-
Cross for Blank Row
! Used for Cell Blank in a Row
Tick nothing is blank in a row.
Exercise 3.3: Charts New
a) 3-D Clustered Column Chart
Create a 3-D Column Chart comparing sales data for men and women according to the following instructions:-
 A chart title and category and value axis label have been added
 The gridlines and background colour have been removed
 The legend has been moved
 The category axis label Manufacturer has been rotated slightly
Solution: For above, use Exercise_3.3_A sheet in Exercise_3.3.xlsx file from MS - EXCEL folder.
b) Pie Chart
 Create a Pie chart to compare the favourite films data for 15-25 year olds only (be careful not to include any
unnecessary blanks rows or columns in your selected data).
 Format this chart so that in this pie chart, the Titanic slice "exploded" and each segment labelled
Solution: For above, use Exercise_3.3_B sheet in Exercise_3.3.xlsx file from MS-EXCEL folder.
c) Clustered Cylinder Chart

Cylinder comparing income by category for March:
 Now format this chart so that it looks something like the one shown below - see how close you can get.
Solution: For above, use Exercise_3.3_C sheet in Exercise_3.3.xlsx file from MS - EXCEL folder.
Formatting Chart
Copy the above Clustered Cylinder Chart and Format it like shown below
d) Bar Chart
Prepare Bar Chart of Gold Silver Bronze of top 5 Countries according to colour and show the output given below:-
Solution: For above, use Exercise_3.3_D sheet in Exercise_3.3.xlsx file from MS - EXCEL folder.
e) 3 D Cone Chart
Draw 3-D Cone chart showing the City and the Population
Solution: For above, use Exercise_3.3_E sheet in Exercise_3.3.xlsx file from MS - EXCEL folder.
f) Plot a graph (Column) showing the average sales of different commodities in a shop.
Commodity Jan’04 Feb’04 March’ April’04
Shampoo 10,899 13,999 45,677 19,343
Face Cream 90,898 1,23,455 9,89,234 14,56,787
Hair oil 5,684 10,891 16,878 89,343
Toothpaste 23,456 1,23,456 79,898 23,23,450
Bath Soaps 9,00,123 8,45,670 15,11,300 19,33,710
Liquid Soaps 23,345 91,800 10,817 16,750
Solution: For above, use Exercise_3.3_F sheet in Exercise_3.3.xlsx file from MS - EXCEL folder.
g) The sampling viewership count for the year 2016 has resulted in the following graph. The sampling total was
1,25,89,214 people. Based on the given data calculate
 Number of people who watched Sahara Samay.
 The average viewership for the two best channels.
Solution: For above, use Exercise_3.3_G sheet in Exercise_3.3.xlsx file from MS - EXCEL folder.
Number of people who watched Sahara Samay - 1384813.54
The average viewership for the two best channels – 3524979.92
Exercise 3.4: Working with Hyper Link New
Open New excel sheet and type Google in a cell. Prepare Screen Tip as www.google.com. Make Hyperlink in a word
Google through web pages. Note after clicking on Google, Google search engine is activated in preferable web browser
Exercise 3.5A: Create Macro for Student Marks Calculation New
Step-1: Prepare a student’s mark sheet details as shown in below table in MS Excel.
Step-2: To start off with first we need to record a Macro. Navigate to View tab and click Macros drop down button and
click on Record Macro.
Step-3: Replace the name Macro1 to new name as “Formula” then click on Ok button.
Step-4: To calculate the following fields of Mark sheet put the formulas as given below then macro recorder running at
the back-end recording all the actions.
For Total =SUM (C2:F2) [Select range from Module-1 to Online Test field]
For Percentage = (Total)/5 [Select the value of Total]
For Status = IF (AND (C2>=25, D2>=25, E2>=50, F2>=180),"Pass”, “Fail")
For Grade = IF (I2>=90,"A", IF (I2>=80,"B", IF (I2>=70,"C", IF (I2>=60,"D","No Grade"))))
Step-5: After applying all the formulas you will drag down the cross symbol to the end of the respective columns, the
formula will be applied to all corresponding cells.
Solution: For above, use Exercise_3.5A.xlsx file from MS - EXCEL folder.
Exercise 3.5B: Create Macro to convert currency into word using user defined function New
Step-1: Prepare Employees sheet details as shown in figure in MS - Excel.
Step-2: Press Alt+F11, It will open Microsoft Visual Basic for Application window as shown in figure
Step-3: Click on Insert Menu and select Module.
Step-4: A new window open and then paste the code available in the notepad file “Exercise_3.5B”
Step-5: Go to your Excel sheet clicking on Excel icon shown in figure
Step-6: Go to your Excel sheet clicking on Excel icon shown in figure then apply the function Amount in words field,
Excel show you the user defined function as “ConvertCurrencyToEnglish()” as shown in below figure.
Solution: For above, use Exercise_3.5B.xlsx file from MS - EXCEL folder.
5. Chapter 4: Consolidation of Data and Data Analysis

Exercise 4.1: Sorting New
Sort the data according to cell colour based on First Name
 In Primary Position:- Red In Secondary Position:-Yellow
After Sorting according to the cell colour Sort the data According to the Font colour based on City
 In Primary Position:- Red In Secondary Position:-Green
Check how many sorting levels are available in Excel 2010 and type the output in a cell D23?
Exercise 4.2: Sorting New
Question the below answer and take a snap shots of each and every answer and paste in a excel sheet
Q1:- Find those employees whose 2nd Character is A?
Q2:- Find those employees whose DOB is 27/08/2003 according to the Location AHMEDABAD?
Q3:- Find those employees whose DOB is in between 1/1/2000 to 1/1/2015?
Q4:- Find those employees whose Basic Pay is greater than 5000 according to the Location AHMEDABAD?
Q5:- Find cell which are blank according to DA?
Q6:- Find those employees whose Employee name used U in anywhere?
Q7:- Find those Locations whose value does not end with R?
Q8:- Filter the record according to the month of January of any year?
Hint: - Solve above questions through Text Filter, Number Filter & Date Filter
Exercise 4.3: Consolidation New
 AB Pvt. Ltd. provides monthly training to its staff and for the same, the accounts executive maintains the expenses
incurred for training in three separate sheets as, transportation, books & stationary and eateries. Now, the
executive wants to find the total expenses incurred on monthly basis.
 For getting the total expenses on monthly basis, he can liberally use the consolidate feature of Excel 2010
(Consolidate by Position).
 Create 03 worksheets each as a)Transportation, b)Books & Stationary and c)Eateries
 Create a main worksheet in your workbook that uses the same column and row header names as the other
worksheets. Named Total
Expenses-
 On the Total worksheet Expenses worksheet, select the cells where you want to consolidate the data from your
other worksheets.
 Click on the Data tab, and then click the Consolidate button in the Data Tools tab.
 Click on the Data tab, and then click the Consolidate button in the Data Tools tab.
 On the Consolidate dialog box, click the highlighted button next to the References field.
 Click the Transportation worksheet tab to display it.
 On the Transportation worksheet, select all the data that you want to merge into the Total Expenses worksheet. for
this exercise, just select the Costs data.
 On the Consolidate dialog box, click the Add button to add the Transportation data you just selected. This step
ensures that the Transportation data you just selected will be merged into the Total Expenses worksheet.
 Repeat above steps to consolidate the other worksheets into the Total Expenses worksheet.
 Once you have added all the worksheets you want to merge into the Total Expenses worksheet, click the OK
button on the Consolidate dialog.
AB Pvt. Ltd
Training Expenses 2016
Month Cost
Jan 600
Feb 165
Mar 590
Apr 225
May 210
Jun 320
Jul 140
Aug 170
Sep 230
Oct 380
Nov 160
Dec 250
Exercise 4.4: Consolidating data from multiple workbooks. (Consolidation by Category) New
1. Open all three workbooks.
2. Open a blank workbook. On the Data tab,
click Consolidate.
3. Choose the Sum function to sum the
data.
4. Click in the Reference box, select the
range A1:E4 in the district1 workbook,
and click Add.
5. Repeat step 4 for the district2 and
district3 workbook.
6. Check Top row, Left column and Create
links to source data.
7. Click OK.
Solution: For above, use Exercise_4.4.folder from MS - EXCEL folder.

Quarter 1 Quarter 2 Quarter 3 Quarter 4
Coffee 10300 14830 14240 7330
Milk 8500 16500 16500 8500
Tea 2700 4800 1800 4500
Exercise 4.5: To compute the consolidate sales and employee wise commission New
Consider the following sales for the month of June:
DATE EMPLOYEE PRODUCT SALES COMMISSION
02-06-2016 Kunal A Rs. 1,800.00 Rs. 90.00
02-06-2016 Sahil A Rs. 1,500.00 Rs. 75.00
03-06-2016 Namit A Rs. 2,500.00 Rs. 125.00
05-06-2016 Raman A Rs. 3,500.00 Rs. 175.00
06-06-2016 Kamal A Rs. 4,500.00 Rs. 225.00
06-06-2016 Ziya A Rs. 5,800.00 Rs. 290.00
07-06-2016 Suneet A Rs. 6,500.00 Rs. 325.00
07-06-2016 Kamal B Rs. 1,900.00 Rs. 95.00
07-06-2016 Sahil B Rs. 4,800.00 Rs. 240.00
07-06-2016 Ziya B Rs. 2,400.00 Rs. 120.00
08-06-2016 Kunal B Rs. 6,500.00 Rs. 325.00
08-06-2016 Namit B Rs. 6,800.00 Rs. 340.00
08-06-2016 Sumit B Rs. 9,500.00 Rs. 475.00
08-06-2016 Suneet B Rs. 4,800.00 Rs. 240.00
08-06-2016 Sumit C Rs. 1,800.00 Rs. 90.00
09-06-2016 Kamal C Rs. 2,300.00 Rs. 115.00
09-06-2016 Suneet C Rs. 2,100.00 Rs. 105.00
10-06-2016 Kunal C Rs. 3,500.00 Rs. 175.00
11-06-2016 Kunal A Rs. 6,500.00 Rs. 325.00
11-06-2016 Ziya C Rs. 3,500.00 Rs. 175.00
 Create the sales for the month of May, June and July on different Excel sheets using the above sample data, using
the Consolidate option on the Data ribbon.
 Compute the consolidated sales (product wise) and commission (Employee wise) on a different sheet.
Exercise 4.6: Scenario Manager Exercise New
Given below is a case study of the comparison of best-case and worst-case scenarios for sales in a coffee shop,
based on the number of cups of coffee sold in a week. Use the What-if-analysis tool to predict the worst case and best
case scenarios in MS Excel using Scenario Manager.
COFFEE SALES SCENARIO
Price per regular coffee Rs. 1.25 T otal revenue ?
Cups need to sell 100
Subtotal ? Regular cups ?
Price per premium latte Rs. 2.00 Premium cups ?
Cups need to sell 175 T otal cups ?
Subtotal ? Constraints
Price per premium mocha Rs. 2.25 Max cups 500
Cups need to sell 100 Max Premium 350
Subtotal ? Max mocha 125
Similarly add a worst case scenario In the Scenario Values dialog box, type 50, 40, and 30 in the boxes for the variable
cells,
COFFEE SALES SCENARIO
Price per regular coffee Rs. 1.25 T otal revenue Rs. 700.00
Cups need to sell 100
Subtotal Rs. 125.00 Regular cups 150
Price per premium latte Rs. 2.00 Premium cups 350
Cups need to sell 175 T otal cups 500
Subtotal Rs. 350.00 Constraints
Price per premium mocha Rs. 2.25 Max cups 500
Cups need to sell 100 Max Premium 350
Subtotal Rs. 225.00 Max mocha 125
To create a scenario report, follow these steps

 Activate the worksheet containing the scenario or scenarios you want to use for your report.
 Click Scenarios on the Tools menu to display the Scenario Manager dialog box.
 Click the Summary button to open the Scenario Summary dialog box.
Scenario Summary
Current Values Best Case Worst Case
Changing Cells:
$B$6 100 150 50
$B$10 175 225 40
$B$14 100 125 30
Result Cells:
$E$5 Rs. 700.00 Rs. 918.75 Rs. 210.00
Notes: Current Values column represents values of changing cells at
time Scenario Summary Report was created. Changing cells for each
scenario are highlighted in gray.
Exercise 4.7: Solver New
Given below is a case study of Coffee Sales of 3 different types of coffee. Use Solver in MS - Excel to predict the
maximum total sales possible for all the three if the number of cups sold for each vary depending on the constraints
given and using Solver predict the maximum total sales.
Exercise 4.8: Descriptive Statistics New

 Use Descriptive Statistics tool to find the Statistical information from the test scores of a group of 15 students.
Use DataData Analysis Descriptive Statistics
Mean 35.4
Standard Error 1.566919939
Median 37
Mode 40
Standard Deviation 6.068654829
Sample Variance 36.82857143
Kurtosis -0.660896066
Skewness -0.122309966
Range 21
Minimum 25
Maximum 46
Sum 531
Count 15
Exercise 4.9: Lookup Functions New
For the given below phone book data, implement the following queries
PHONEBOOK
Name Phone City Country
A 12345678 City1 Country1
C 87654321 City2 Country2
F 12365487 City3 Country3
R 32145678 City4 Country4
T 52413876 City5 Country5
G 74185263 City6 Country6
1 Use lookup function to find out the city of F.
2 Use lookup function to find out the country of R.
3 Use Array option in lookup function to find out the country of R.
4 Use Array option in lookup function to find out the phone number of T.
5 Check if lookup function is case sensitive. e.g.: try to lookup the phone number of “A” and “a”
6 Try to lookup the city of B (B not available in the list).
7 Try to lookup the city of E (E not available in the list).
8 Try to lookup the city of G (G not available in the list).
9 Lookup the name of the person having the phone number 12345678
10 Use Array function in Lookup to look up the name of the person having the phone number 12345678
11 Lookup the name of the person having the phone number 12355678 (not available in the list).
12 Lookup the name of the person having the phone number 74185500
Name the vector ranges to be used in the lookup function respectively.
Vector A3:A8 = Name Vector B3:B8 = Phone
Vector C3:C8 = City Vector D3:D8 = Country Array = Phonebook
Here the lookup function refers to the Name vector (highlighted in blue border) and the City vector (highlighted in green
border).
Exercise 4.10: V Lookup Function New

a) Calculation using V Lookup Function
 Item
 Price
 In stock
And also calculate the data in a second sheet without opening a first sheet.
Calculation using V Lookup
Stock List Enquiry System
Code Item Price No. in Stock Code A18
A34 Armchair 89.99 5 Item Bookcase
A16 Stool 21 7 Price 112.5
C39 Sofa 312 0 In stock 40
D80 Dining Table 199.99 12
A18 Bookcase 112.5 40
F26 Shelves 45 9
G36 Rocking Chair 87.99 13
A17 Lamp 32.99 8
B45 Kitchen 231 34
b) Using V Lookup function
Part No. Units of Measurement Rate Category Location Reorder Level Reorder Qty Supplier
111 Nos 6500 A Stores 1 2000 100 A
222 Kgs 80 C Stores 2 500 75 H
333 Meters 20 C Stores 3 500 60 Y
444 Kgs 300 B Stores 1 900 40 B
555 Kgs 750 B Stores 1 800 40 C
666 Nos 55 C Stores 2 500 50 E
777 Nos 90 C Stores 1 500 30 G
From the above given Part List data prepare a purchase order sheet as shown in the output.
Hints
1. Name the vector name Part List for the data array.
2. Part No: Set the validation condition as shown in the Data Validation dialog box.
3. The VLookup formula in the respective columns are
• Unit Rate =VLOOKUP(B17,Partlist,3)
• Units =VLOOKUP(B17,Partlist,2)
• Location =VLOOKUP(B17,Partlist,5)
• Supplier=VLOOKUP(B17,Partlist,8)
• Order Qty=VLOOKUP(B17,Partlist,7)
• Total Amount=Order Qty * Unit Rate
Solution:
Exercise 4.11A: H Lookup Function (with Exact Match) New
Step-1: Prepare a student’s mark sheet with percentage as shown in table in MS - Excel.
Roll Name Module-1 Module-2 Project Online Test Total Percentage Grade Remark
1 Anna 25 25 20 180 ? ? ? ?
2 Antonio 30 25 65 180 ? ? ? ?
3 Thomas 40 40 70 200 ? ? ? ?
4 Christina 50 40 90 220 ? ? ? ?
5 Martin 40 40 90 280 ? ? ? ?
6 Francisco 30 30 70 270 ? ? ? ?
7 Ming-Yang 45 40 85 280 ? ? ? ?
8 Elizabeth 50 50 100 300 ? ? ? ?
9 Sven 30 20 40 160 ? ? ? ?
10 John 30 40 90 240 ? ? ? ?
Step-2: Prepare a student’s Grade Table with percentage as shown in table in MS - Excel.
Percentage 50 60 70 80 90 100
Grade E D C B A A+
Verbal description Failure Marginal Average Good Very Good Excellent
Step-3: Select Grade Table as shown in figure and type Gradetable in Name Box in Excel and press Enter.
Step-4: Click in Grade column and type HLOOKUP Function, Select Percentage value of first student as shown in
figure. The HLOOKUP formula for the respective columns is:
Q10. Grade =HLOOKUP (H2, Gradetable, 2, False) where HLOOKUP will only find an exact match from the
Gradetable.
Q11. Remark= HLOOKUP(H2,Gradetable,3,False)
Exercise 4.11B: H Lookup Function (with Closest Match) New

Step-1: Prepare a student’s mark sheet with percentage as shown in table in MS - Excel.
Step-2: Click in Grade column and type HLOOKUP Function, Select Percentage value of first student as shown in
figure. The HLOOKUP formula for the respective columns is:
Q12. Grade =HLOOKUP (H2, Gradetable, 2, True) where HLOOKUP will only find an approximate match from the
Gradetable.
Q13. Remark= HLOOKUP(H2,Gradetable,3,True)
6. Chapter 5: Data validation & Protection

Exercise 5.1: Data Validation New
a) Data Validation using Dependent lists – Indirect () Function
 To create dependent lists using data validation, create the following named ranges on sheet 1.
Name Range Address

Course A1:A4
Course Tracker B1:B3
Java C1:C5
DotNet D1:D3
CSharp E1
Adobe F1:F3
 On the Second sheet, select a cell E5.

 On the Data tab, click Data Validation.
 The 'Data Validation' dialog box appears.
 In the Allow box, click List.
 Click in the Source box and type =Course. Give the Input any message one course and click Ok.
 Next, repeat the same steps to create one more list for Course Track in cell E7.
 Next, in the cell E9 now, in the allow box, click list and in the Source box type =INDIRECT($E$21)
 On the first worksheet create a table of Course and its corresponding fees.
 On the second worksheet use vlookup () function to look up the fees to be deposited based on the course selected
in the cell E9.
Select the Type of Course Java

Select the Course Track 2 Years
Select the specific course JSP
Total Fees to be Deposited 2000
b) Data Validation using Dependent lists – Indirect () Function

 Create Annual Department Budget using data validation, as shown in the figure below
 Use Data Validation to restrict data so that the, give the required error alerts also to prompt the user if they enter
values out of the limit.
o
Training Programme not to exceed Rs.100000
o
New Hardware not to exceed Rs.200000
o
New Software not to exceed Rs.325000
o
Magazines & Periodicals not to exceed Rs.5000
o
Conveyance not to exceed Rs.50000
o
Telephone Expenses not to exceed Rs.40000

Find the Repairs & Maintenance cost such that the Total of all the expenses does not exceed Rs. 750000
Annual Department Budget
Training Programme 100000
New Hardware 200000
New Software 325000
Repair and Maintenance Work 30000
Magazines and Periodicals 5000
Conveyance 50000
Telephone Expenses 40000
Total 750000
Exercise 5.2: Adding Security New
 Enter a password to open the spreadsheet & workbook structure.
 Change the save option of the file using the following steps
 Change the save Auto recover Information every 5 minutes.
 Change the default file location to C: \Users\....\Desktop.
 Enable background error checking.
 Change the dictionary language to English (Hindi)
 Enable Show Developer tab in the Ribbon.
Solution:
Protect the sheet and enter new password in password to unprotect password text box. Also protect the workbook
structure. For this above use File MenuOptionsProofing, Save & Customize Ribbon tabs.
7. Chapter 6: Pivot Tables Reports & Pivot Chart Reports

Exercise 6.1: Pivot Table & Chart New
Pivot Tables and Charts are very useful tools in MS - Excel to create summary reports and charts for large data to get
meaningful insights. We shall learn to make use of these tools in this exercise.
Create a 2-Dimensional Pivot Table from the field list in the given manner as provided in the Exercise6.1.xlsx in MS -
Excel folder.
 From the pivot chart, find out which is the maximum exported product?
 Also find out the country which does the maximum exports.
 Try filtering the data of the pivot table by changing the Country.
 Create a pivot chart for the pivot table. The pivot chart should look like this.
 Next, filter this pivot chart using the Country filter to only show the total amount of each product exported to the
United States.
 Insert Slicer on the pivot table, for the column -> Category to show only the fruit exported to each country.
 Insert Slicer on the pivot table, for the column -> Category to show only the fruit exported to each country.
Exercise 6.2: Pivot Table New

The Sales data is provided in the Exercise6.2.xlsx in MS - Excel folder. Based on the data -
a) Create a Pivot Table for month wise Total sales.
b) Based on above Pivot Table create a Pivot Chart for Total sales of all salesman.
c) Find which salesperson has sold the lowest in January?"
Solution: For above, use Exercise6.2.xlsx file from MS - EXCEL folder.
a)
b)
c)
8. Chapter 7: Importing / Exporting Data

Exercise 2.1: Importing Database Files into MS - Excel
Import Books Table from Books2010 Database file into an Excel.
Import BooksinJuly Query from Books2010 Database file into another Excel sheet.
Note – Books2010.mdb database is available in EXCEL folder.
9. Chapter 8: Multi - Dimensional Analysis of Data

Exercise 8.1: Create Pivot Table
The data is provided in the Exercise8.1.xlsx in MS - Excel folder. Based on the data -
a) Make a pivot table showing expenditure as
Expense Type - Wise
Card Type - Wise
Output:
Sum of AmountColumn Labels
Row Labels Gold Platinum Signature Silver Grand Total
Bills 181626949 179086607 182347174 188560954 731621684
Entertainment 130008348 135824899 134038192 144117748 543989187
Food 153230987 164326811 156260557 167567165 641385520
Fuel 149464614 147011753 147328270 174054947 617859584
Grocery 121023610 132473959 139972422 150818927 544288918
Travel 31189896 24253693 27499036 26312986 109255611
Grand Total 766544404 782977722 787445651 851432727 3188400504
Exercise 8.2: Expense Type wise - Show Amount and Percentage for each Expense Type wise in single Table using
data is provided in the Exercise8.1.xlsx in MS - Excel folder.
Output:
City (All)
Row Labels Sum of Amount Percentage

Bills 731621684 22.95%
Entertainment 543989187 17.06%
Food 641385520 20.12%
Fuel 617859584 19.38%
Grocery 544288918 17.07%
Travel 109255611 3.43%
Grand Total 3188400504 100.00%
Exercise 8.3: Make a report Year Wise/Quarter- Wise putting Expenses in columns using data is provided in the
Exercise8.1.xlsx in MS - Excel folder.
Output:
Sum of Amount Column Labels

Row Labels Bills Entertainment Food Fuel Grocery Travel Grand Total
2013 111862069 83994251 94585149 89717970 81313764 15353606 476826809
Qtr4 111862069 83994251 94585149 89717970 81313764 15353606 476826809
2014 437484784 328799554 388597732 376223044 326463111 65532848 1923101073
Qtr1 107676411 78615137 94362051 95852944 79811302 14275240 470593085
Qtr2 110889913 75255816 96600236 86945932 80779945 19403043 469874885
Qtr3 109369384 91177512 96853552 101958778 77724565 11568302 488652093
Qtr4 109549076 83751089 100781893 91465390 88147299 20286263 493981010
2015 182274831 131195382 158202639 151918570 136512043 28369157 788472622
Qtr1 113868095 80310136 97915755 102092589 84439173 16106193 494731941
Qtr2 68406736 50885246 60286884 49825981 52072870 12262964 293740681
Grand Total 731621684 543989187 641385520 617859584 544288918 109255611 3188400504
10. Chapter 9: Dash Board Reporting in Excel
Exercise 9.1: Make a Dashboard report
Showing City Wise, Card Type, Expense Type and Gender - using data is provided in the Exercise9.1.xlsx in MS -
Excel folder.
1. Create Slicer for Card Type, Expense Type and Gender wise.
2. Create relationship between slicer so that your charts change with a single click.
Output:
Assignments – MS Excel 2010
Assignment 1: Basic Printing Format
 Open Excel Document and fill the column 15 and Row 50 by entering the series in a cells using fill option.
 Click Ctrl+P for print, you find that 6 Pages are used if you print directly.
 Convert these 6 Pages into 3 pages
 Let say you are printing 3 copies of these document Set the settings to print this document page wise rather
than set wise.
 Set the page margin to A4 and Narrow.
 Set the printer setting to print on both side and print the document only selected text.
 Question arise is this document print answer is no reason because your printer is not selected if printer is not
selected then select the printer first and then set the above settings and your output is come into a sheet of
paper.
Assignment 2: Create Calendar Using Numerical Values and Formatting.
 Prepare the following data in a Excel sheet
April-16
Sunday Monday Tuesday Wednesday Thursday Friday Saturday
42461 42462
42463 42464 42465 42466 42467 42468 42469
42470 42471 42472 42473 42474 42475 42476
42477 42478 42479 42480 42481 42482 42483
42484 42485 42486 42487 42488 42489 42490
 Convert the above data into date using format and produce the output according to the Computer Calendar
 After creating the output copy the output and reconvert the date into above data.
Assignment 3: Text Functions
Find the Data according to the first name and last name as per given below:
First Name Last Name Upper Lower Proper Concatenate Left Right Mid E-mail
Aakash Dixit ? ? ? ? ? ? ? ?
Arun Joshi ? ? ? ? ? ? ? ?
Bharat Shetty ? ? ? ? ? ? ? ?
Chetan Dalvi ? ? ? ? ? ? ? ?
Deep Chhaya ? ? ? ? ? ? ? ?
Geeta Darekar ? ? ? ? ? ? ? ?
Heena Godbole ? ? ? ? ? ? ? ?
Indu Shah ? ? ? ? ? ? ? ?
Kabir Vora ? ? ? ? ? ? ? ?
Kunal Shah ? ? ? ? ? ? ? ?
Meera Lalwani ? ? ? ? ? ? ? ?
Neha Joshi ? ? ? ? ? ? ? ?
Pooja Gokhale ? ? ? ? ? ? ? ?
Sonia Sasan ? ? ? ? ? ? ? ?
 Upper according to the First name
 Lower according to the First name
 Proper according to the First name
 Concatenate of First name and Last Name using space in between first and last name.
 First 3 letters according to First Name
 Last 3 letters according to Last Name
 3 letters from 2nd character according to First Name
 Email using first character of first name and last name using dot and @Gmail.com in last.
Assignment 4: Calculate the maturity value of yearly Rs 24000 at the interest rate of 8% for 10 year duration.
Assignment 5: FV
Calculate the Future value as per the given data
Future value
Present Value Rs. 1,000,000.00
Annual deposit Rs. 100,000.00
ROI 11.00%
Period ( in years) 20.00
Future Value ?
Assignment 6: IRR
Using the IRR Function find out the solution which project is better for investment Input1 or Input2 according to the
following data:
IRR
Years Input 1 Input 2
Year 1 Rs. -5,000,000.00 Rs. -9,000,000.00
Year 2 Rs. 1,300,000.00 Rs. 3,600,000.00
Year 3 Rs. 1,900,000.00 Rs. 3,600,000.00
Year 4 Rs. 2,600,000.00 Rs. 3,600,000.00
IRR ? ?
Assignment 7: Macro
Create a Macro to convert text into Lower Case, Upper Case and Proper Case using user defined function.
Assignment 8: PMT and Goal Seek
By using following data, calculate PMT using PMT function. Your output is 790.79
Then determine the interest rate required in B3 in order to make the payment in B4 equal Rs. 900 using goal seek.
PMT and Goal Seek

Loan Amount $100,000
Term in Months 180
Interest Rate 5.00%
Payment ?
Assignment 9: Scenario Management
Create a excel file according to the given below data and create three Scenarios Low Cost, Competitive, High Cost.
Monthly Income Statement

Units Sold 1,200
Revenue Price per Unit $100
?
Units Produced ?
Variable Material Cost per Unit $26
Expenses Total Material Cost ?
Manufacturing Cost per Unit $15
Manufacturing Expenses ?
Total Variable Expense ?
Leasing $5,000
Salary and benefits $45,000
Fixed
Advertising $5,000
Expenses
Administration $2,500
Total Fixed Expense ?
Total Expenses ?
Summary
Operating Income ?
Changing Cell Low Cost Competitive High Cost

Unit_Sold 1400 1300 1200
Price_per_unit 80 90 100
Material_Cost_per_Unit 22 24 26
Assignment 10: Solver
By using the following data, using Solver Prepare 3 Criteria’s as below:
1) The Production Level value must be greater than equal to zero
2) The Parts Required range value are less than or equal to Parts on Hand range
3) The Production Level value must be integers. No fractional Widget
Set Target Cell: Total Profit and changing cell Production Level.
Red Blue Green Yellow Orange
Models
Widget Widget Widget Widget Widget
Profit per Unit $95 $88 $60 $42 $30
Production
0 0 17 28 61 Total Profit ?
Level
Widget Parts
- Widget Components Required for Each Model - Parts on Hand
Components Required
A 5 0 6 3 0 200 ?
B 3 12 3 5 0 200 ?
C 6 5 5 3 0 200 ?
D 8 3 2 3 1 200 ?
E 12 8 1 0 3 200 ?
F 10 2 2 0 2 200 ?
G 15 0 5 0 0 200 ?
Assignment 11: VLookup Functions
Prepare sheet1 by using following data and rename the sheet as Voters.
Prepare sheet2 by using following data and rename the sheet as Party Voters.
PARTY CODE NAME
1 Green
2 Reform
3 Whig
4 Islamic Political Party of India
5 Rock & Roll
6 Natural Law
7 Middle Class Party
8 Humanist
9 Pragmatic
10 Congrous Party
11 Parliament Party
12 United Conscious Builders of the Dream Party
13 The Egalitarian Party
14 The Humanitarian Party
15 Hindostan University Party
16 God, Truth & Love Party
17 Superhappy Party
18 Working Families Party
A Democratic
B Republican
C Decline to State
D India Independent
E Citizen Party
F Communist
G Conservative
H Environmentalist
I Ind. Progressive
J Liberal
K Peace & Freedom
L Prohibition
M New Economy
N Socialist
O Socialist Labor
P Pot Party
Q Libertarian
R India National Socialist
S Poor People’s Party
T Free
U National
V Constitution Party
W Vision
X Puritan
Y Federal
Z Misc.
Now show the Political Parties in a Voter sheet using Vlookup Function.
Assignment 12: Data Validation and Conditional Formatting
Prepare file by using the following data
Sales person Region Account Order Amount
Ram Prasad North 29386 ₹ 925.00
Shyam Choubey East 74830 ₹ 875.00
Ankit Jain South 90099 ₹ 500.00
Manoj Kumar East 74830 ₹ 350.00
Gauri Shankar North 82853 ₹ 400.00
Krishan Jain West 72949 ₹ 850.00
Pinki Kumari South 90044 ₹ 1,500.00
Seema West 82853 ₹ 550.00
Manorama Das West 72949 ₹ 400.00
Bally South 55223 ₹ 235.00
Bablu South 10354 ₹ 850.00
Ankit North 50192 ₹ 600.00
Anup South 27589 ₹ 250.00
Shyam Bang West 67275 ₹ 400.00
Prepare list of region using data validation in any cell of E column. According to the list if we select any region then
highlight all the row according to that region and that are applied on all regions according to the list.
4.
MS – PowerPoint 2010
1. Introduction
We were recently informed of a placard stating “In God we Trust, others have to bring data/ analysis” in the board room
of a leading IT company. As managers, we have to highlight major points of a proposal/ activity/ topic/ subject to sell an
idea, impress salient features, teach etc. MS - PowerPoint is the software that enables us to highlight salient points in
the form of slides.
While many of us have made presentations as a part of our school/ college projects or office work, we are sure that you
would learn some new techniques and features to be a more effective and efficient presenter, after undertaking the
case studies and exercises in this section.
We shall be covering the following Illustrations and Case Studies as a part of our study on using MS - PowerPoint for
respective chapters of this topic:
MS – PowerPoint 2010
1 MS - PowerPoint 2010 7
Total 7
2. Chapter 1 : Introduction to MS - PowerPoint 2010

Exercise 1.1: Creating and managing presentation New
Solution: For above, use Exercise_1.1.pdf file from MS Power point folder.
 Create a new PowerPoint presentation.

 Save the presentation with the name “My
first presentation”.
 Insert the text on Title Slide: - “Basics of

Computer” in Title text box and
Presenter Name: “<Your name>” in
subtitle text box.
 Insert a new slide by pressing Ctrl+M

and type the text in it as shown in picture
 Prepare at-least 2 slides on each topic.

a. Input Devices
b. Output Devices &
c. Storage Media
 Provide Headings for topic into each
slide.
 Use Smart Art to explain your points.

For
A) Output Devices &
B) Storage Media slides
 Hyperlink all the topics to the Contents
slide (Slide no -2) with its description
slide.
 Apply different transition on slides.

 Apply different animations on the text
and images in your presentation.
 Save and run your presentation file.
Exercise 1.2: Use of Slide Master New

 Create a new PowerPoint presentation file and save as “Slide Master.pptx”.
 Go to View Tab and perform the following steps
Solution:-
Step 1: Click on Slide Master button.
Step 2: Select Slide No -1 slide from the

navigation pane.
Step 3: Insert clip art picture and name in
Footer area as shown in figure.
Step 4: Then select Two Content Layout

from navigation pane and modify it as shown
in figure.
Note: In footer box select “Footer” check box
and opt “Apply to all” option.
Close the Master view.
Save and run your presentation file.
Exercise 1.3: Uses of Animation New

 Open exercise1 3.pptx.
 Apply animation effect “Motion Path – Custom Path”- for the given objects (Cars) with interval of 10 seconds &
sequence of parking the cars at home as 1) Red, 2) Green, 3) Blue & 4) Yellow car.
 Set timings for some objects for display while presentation.
 Save the changes and run your presentation.
Solution: - For above refer to exercise1.3.pptx file in MS Powerpoint folder.
Exercise 1.4: Uses of Action Buttons New
 Open Exercise 1 “My first presentation”.
 Insert “Home” action button on

Computer Information (slide number 3)
and link it to Contents (slide no 2)
 Copy the action button from slide no -3

and paste the same on rest of the
slides.
 Now run the presentation, click on

action button on each slide and see the
result.
 Save and close your file
Exercise 1.5: Setup different slide size and Colour theme for output devices New
 Open Exercise1.1_My_Presentation.
 Go to Design Tab Click on Page Setup
Option
 In Page Setup option change Slides size

for to On-screen Show (16:9) option,
click ok and also apply different colour
theme.
 Save as this file with name as “PPT for
Projector”.
 Run this presentation and view the result.
 Now again in Page Setup option change

Slides size for to Banner option, click
ok and also apply different colour theme.
 Save as this file with name as “Banner”.
 Run this presentation and view the result.
Exercise 1.6: Organise slides into sections New
 Open Exercise1.1_My_Presentation
 Select “Input Devices” slide and Go

to
Home Tab  Slides Group 
Section button Add Section
 After clicking on “Add Section” it will

display the result as shown in
figure. (Here use Slide Sorter view)
 Select the Untitled Section and right

click and select “”Rename Section”
option. Rename as “Input Devices
Section”
 Select “Output Devices” slide and

Normal View Slide Sorter View
Rename as “Output Devices
Section”
 Select “Storage Devices” slide and
Rename as “Storage Devices
Section”
 After renaming these sections see
the same in left navigation pane in
Normal view or Slide Sorter view.
Exercise 1.7: Setup custom slide show New

 Open Exercise1.1_My_Presentation
 Go to Slide Show Tab  Start

Slide Show Group  Custom Slide
show
 Create a new Custom Show for

Input Devices only.
 Same way create a new Custom

Show for Output Devices only.
 Go to Setup Slide Show Select

Custom Show
 Then select “Input Devices”, click ok.
Now run your presentation and view
the result.
 Now select “Output Devices”, click
ok and run your presentation and
view the result.
 Save & close your presentation file.
Assignments – MS PowerPoint 2010
Assignment 1: Creating and managing presentation
1. Create a new PowerPoint presentation with the name “Operating System” and insert the following slides:-
a. Definition of Operating System
b. Functions of Operating System
c. Types of Operating System
2. Enter relevant text and images for given topics.
3. Use SmartArt in types of Operating System slide.
4. Apply different transition on slides.
5. Apply different animations on the text and images in your presentation.
6. Save and close your file.
Assignment 2: Uses of Slide Master

1. Open Slide Master View and delete “Comparison, Title only, Content with Caption & Picture with Caption
Layout”
2. Insert any picture from clipart on “Two Content Layout”.
3. Apply “Door” Transition on “Blank Layout”.
4. Apply “Style 7” background on “Two Content Layout”.
Assignment 3: Catch me if you can.
1. Insert a Smiley Shape and set the action so that nobody is able to click on it. (Use at least 5 slides)
Solution: - Please refer MS-PowerPoint folder and Smiley.ppsx
Assignment 4
Prepare a presentation for launching a new product for your company.
Assignment 5
Open your Product launch presentation and setup slides size to 9.5” width and 10.8” Height.
5.
MS – Access 2010
1. Introduction
MS Access 2010 is a part of the MS Office 2010 suite of office productivity software and is primarily geared to manage
data as a Database. This compilation provides practical exercises and case studies on use of MS Access using Case
Studies and simple examples to help students to have a practical hands - on training (HOT) on the use of database.
Unit MS - Access
1 Introduction to Microsoft Access 2010 1
2 Working with Table and Form 8
3 Working with Queries and Reports 2
Total 11
2. Chapter 1: Introduction to Microsoft Access 2010

Exercise 1.1: Creating Database without using a Template New
In this exercise students are requested to create a database, Forms and Reports for managing books in a Library.
The library at a college ABC recently received a large collection of new books and would like to keep better track of the
titles. The library is still using an antiquated method for cataloguing most of its books. Students are asked to create the
database (using Design View) named Books Details containing the following fields:-
Field Name Type Field Size
Catalogue Number AutoNumber Long Integer
Title Text 70
Author Text 25
Copyright Date Date/Time
Publications Text 25
The database will make search, tracking, and inventory functions much easier.
Solution: For above, use books details.accdb file from MS Access folder.
Step 1 - Start MS Access, select New and click on Blank Database, provide file name as Books Details.
Step 2 – Save Table name as Books Details and then add all the fields as given
Step 3 – In Books Details table enter 5 records and also check add new record option as shown in below figure.
3. Chapter 2 : Working with Table and Form

Exercise 2.1: Create Customer Database New
Students are requested to create Customer Database with 3 tables and fields as given below. After Creating these
tables form a relationship between them accordingly.
Customer Table
Cust_id Number Integer (Primary Key)
Cust_Name Text 30
Address Text 25
City Text 15
State Text 15
Pincode Number Long Integer
Transactions Table
Trans_Id AutoNumber Long Integer (Primary Key)
Cust_Id Number Integer
Transaction_Type Text 7
Date of Transaction Date/Time
Time of Transaction Time
Transaction Amount Number Long Integer(4 decimal places)
Accounts Table
Cust_Id Number Integer
Account Number Number Long Integer (Primary Key)
Account Type Text 7
OpeningDate Date/Time
Balance Number Long Integer (4 decimal places)
Solution:
Step 1: Open MS Access and create a new database, save this file as “Customer Details”
Step 2: Create New Table by using Table Design view as shown in below figure.
Step 3: After creating these 3 new tables save it as per the given names.
Step 4: Now select Database Tools menu and click on Relationships.
Step 5: From Show Tables window add all the 3 tables for crating relationships between them
Step 6: Now create relationships between Accounts and Customer table.

Step 7: Now create relationships between Customer and Transaction table.
Step 8: We created relationships between Accounts, Customer and Transaction table as shown below.
Step 9: Add 5 records in all these 3 tables.

Exercise 2.2: Create ITT Students Database New
Students are requested to create a Database for students in ITT Course.
Solution: For above use ITT Students Database from MS Access folder.
Step1: Open MS Access Database and create a blank database name as ITT Student.
Now create 3 Tables as shown below in ITT Student Database as given below.
Centre_Code Text 5
Centre_Name Text 15
Centre_Region Text 5
POU_Address Text 50
Student_ID Number Long Integer
Batch_Start_Date Date/Time Medium Date
Batch_Completion_Date Date/Time Medium Date
Certificate_Issue_Date Date/Time Medium Date
ITT Centre

Student_ID Numb Long Integer
er (Primary Key)
Scheme_Enrol Text 15
FirstName Text 25
LastName Text 25
Gender Text 5
DOB Date/ Medium Date
Time
Father_Name Text 30
Permanent_Address Text 200
Mailing_Address Text 100
Landline_Number Text 30
Mobile_Number Text 10
Email Text 30
ICAI_Student Table

Student_ID Number Long Integer
Module1 Number Long Integer
Module2 Number Long Integer
Project Number Long Integer
Attendance Number Long Integer
OLT Number Long Integer
Marksheet
Exercise 2.3: Create a Form for ITT_Students. New

Select the table ICAI_Student then click on Create and select “Form” from Forms Group as shown in the below figure.
Exercise 2.4: Create a Form using Form Design New
Create Form for Marksheet and Add the Following textboxes in the form of Student_ITTResult and calculate these field
values using following formulas.
Field Name Type
Total Marks Obtained Number
Percentage in Aggregate Number
Result Status Text
Hints:
Total Marks Obtained = Marks in Module I + Marks in Module II + Marks in Online Examination
Percentage in Aggregate = (Total Marks/500)*100
Result Status = If Percentage in Aggregate > 59, Result status is ―Pass‖, else ―Fail‖.
Also in the property sheet, the above three fields should be disabled, so that no data can be entered through user in
these fields.
Step-1: Click on Create and then select “Form Design” from Forms Group.
Step-2: Now select Field List and from Marksheet Table add all the fields on Details section in Form.
Step-3: After addition of all the fields in Form now select Design Tab and select Textbox Control from the Controls
group, place the selected textbox control at Form Footer section as shown in below figure.
Step-4: Double Click on Total Marks Textbox, display Property Sheet, Choose Control Source form Property Sheet, It
will display Expression Builder type formula as
=[Module1]+[Module2]+[Project]+[OLT] and Click on OK.
Step-5: Repeat Step-3 & Step-4 to Calculate the other Fields(Percentage in Aggregate, Result Status) using following
Formulas.
Percentage in Aggregate=[Total]/5
ResultStatus=IIf([Module1]<25,"FAIL",IIf([Module2]<25,"FAIL",IIf([Project]<50,"FAIL",IIf([OLT]<180,"FAIL","PAS
S"))))
Output:
Exercise 2.5: Create a Form using Form Design New

Create a form that will display information of the customers.
Solution: For above use Customer Details Database from MS Access folder.
A) Step 1: Select Create Menu and then click on Form Design.
Step 2: In form design right click on form and select Form Header & Footer. So it will add Form Header and Footer on
form.
Step 3: Now from Form Design Tools group select “Add Existing Fields”. Select Field List and select Customer Table.
Step 4: In form header add a label control and type “Customer Details” in it. Then from the Field List select “Cust_Id
field and drop it in Details section of the form. In the same way drop all the fields of Customer table into Detail section.
Then in Form Footer add Text box control and type “=Now()” function in it that will display current date & time in it.
Step 5: Save this form as “Customer_Form” and view it in Form view.
Exercise 2.6 : Creating Form by Form wizard and add Command Button Control on it New
Create a form based on books details table. Perform below operations on it
a) Add New Record button on it for adding new record in the table
b) By using New Record button add 2 new records in the table.
Solution: For above, use books details.accdb file from MS Access folder.
Step 1 – Start MS Access, open book details database and click on Create  Form as shown in below figure
Step 2 – The resultant Form will be displayed as shown in below figure
Step 3 – Now select form in design view, then in from Form Design Tools select Command Button and add it in Form
Footer section.
Step 4 – Now by using Command Button Wizard from Categories select “Record Operations” and then from Actions
select “Add New Record” and click Next
Step 5 – Now click on Text and type “Add New Record” and click Next.
Step 6 – Now type “New Record” and click Finish.
Step 7 – Now select Form View and click on “New Record” button. It will display form for adding new record.
Step 8 – Now add 2 records and save and close the Form.
Exercise 2.7: Create a Form using Form Wizard New
Create following forms
A) Main form - Customer and Sub-form - Accounts
B) Transactions Form
Solution:
B) Step 1: Select Create Menu and then click on Form Wizard.
Step 2: Now select Customer table, add all the fields then select Accounts table and add all the fields. Click Next.
Step 3: Now here to view data by Customer and then select Form with subform(s) options. Click Next.
Step 4: Now here select layout option Datasheet. Click Next.

Step 5: Now here add titles for the form. Click Finish.
Step 6: The resultant form with subform will display as follows.

Design View Form View
C) Step 1: Select Create Menu and then click on Form Wizard.

Step 2: Now select Transactions table and add all the fields. Click Next
Step 3: Now here select layout option Columnar. Click Next.
Step 4: Now here add titles for the form. Click Finish.
Step 5: The resultant form will display as follows.

Exercise 2.8: Create Reports for ITT Students database (ICAI_Students, ITT Center, Marksheet table) New
Step-1: Select the ICAI_Students table and click on Create Tab, Then select Report in Reports group as shown in
below figure.
Resultant reports will be displayed as shown in below figures.

4. Chapter 3 : Working with Queries and Reports
Exercise 4.1: Create Queries as given in below table. New
Sr. Type of Query Query Name/Mode To Display/Criteria
Student’s Registration Number, First Name, Last Name,
Query - 1 (Using Simple
A Select Query Scheme Enrolled In, Centre Code, Centre
Query Wizard)
Name, Centre Region, Centre Address.
Query-2 (Using Query
B Select Query Centre Name, Scheme Enrolled, Result
Design)
C Crosstab Query Query-3/Using Query-2 Using Crosstab query display centre wise Result.
Action Query
Using Append Query create a Backup table of ICAI_Students
D Append Query Query-4
records
Using Delete Query delete all records of CPT scheme
E Delete Query Query-5
students form BackupTable.
Using Make Table Query create new table for students details
F Make Table Query Query-6
with their respective centers.
Using Update Query for Delhi-Gazipur students.instead of
G Update Query Query-7 NIRC centre region should be displayed as “Northern”
Note- Use New1 Table here.
Select Query
Step-1: To create above queries click on Create Tab and Select Query Wizard from Query Group as shown in figure
below.
Step-2: Display Wizard dialog box Select Sample Query Wizard & click on OK as shown in Fig.
Step-3: Select ICAI_Students table and Select required fields Sample Query Wizard as shown in Fig.
Step-4: Select ITTCentre table and Select required fields and Click on Next Button as shown in Fig.
Step-5: Type the name of the Query as required and click on Finish Button as shown in the below figure.
Output:
A. Select Query
Step-1: To create above queries click on Create Tab and Select Query Design from Query Group as shown in figure
below.
Step-2: By Clicking Query Design, Display Show Table dialog box as shown in the figure & Select required Tables from
the dialog box.
Step-3: By adding all tables drag Centre_Name field from ITTCentre table & Scheme_Enrol, Result from ICAI_Students
and ITTResult table respectively as show in in the Figure.
Step-4: Click on Run command to see the result.
Output:
B. Crosstab Query
Step-1: To create above queries click on Create Tab and Click Query Wizard & Select Crosstab Query Wizard form
New Query dialog box and click on OK as shown in figure below.
Step-2: Select Query-2 from Crosstab Query Wizard & click on Next button as shown in figure below.
Step-3: Select Centre_Namefield display as row heading as shown in figure below.
Step-4: Select Result field display as Column heading as shown in figure below.
Output:
C. Append Query
Step-1: Create a blank table name as BackupTable of same structure of ICAI_Students, copy ICAI_Students table
using(CTR+C) and paste(CTR+V) a dialog box appeared as shown in figure and select Structure Only Paste Option
and change table name as BackupTable.
Step-2: Add all fields of ICAI_Students table in Query Design & Select Append Query from Query type and select
BackupTable then click on Run Command form the Design Tab as shown in figure.
Output:
D. Delete Query
Step-1: Select Delete Query Command from Design Tab available Query Tools, Select BackupTable in Query Design
add Scheme_Enrol field with CPT criteria as shown in figure.
Step-2: Select Run Command and click on yes to delete desired records.
Output: It deleted all records of CPT Scheme
E. Make Table Query

Step-1: To create New Table for students details with their respective centers, click on Create Tab and Select Query
Design from Query Group as shown in figure below.
Step-2: To create New Table for students details first select Query Type as “Make Table” and then give the new table
name as “New1 Table”.
Step-2: Now select ICAI_Students and ITTCenters Table form Show Table window. Then select Student_Id,
FirstName, Lastname, Centre_Name and Centre_Region fields from these table. Then click on Run button. It will
display confirmation message and create a new table.
F. Update Query
Step-1: To update the students details with their Name and Centre_region here make use of Update Query. Click on
Create Tab and Select Query Design from Query Group as shown in figure below.
Step-2: Select Query Type as “Update” and then select New1 Table from Show Table window. Now select fields
Centre_Name and Centre_Region.
For Centre_Name criteria is “Delhi-Gazipur” and for Centre_Region Update to should be “NORTHERN”
Step-3: Now click on Run button and it will display the confirmation of updating 10 rows for selected criteria field.
Step-4: Close and save the query and check the result in New1 Table.
Exercise 4.2: Create Students Details Report. New

Step-1: Click on Create Tab, Then select Report Wizard in Reports group as shown in below figure.
Step-2: Now select fields Student_Id, FirstName, LastNameand Email from ICAI_Students Table then Centre_Name,
Centre_Region from ITTCentre Table and Total, Percentage and Result from Marksheet Table. Click Next.
Step-3: Now add Grouping Levels as Centre_Region, Centre_Name and Student_Id. Click Next.
Step-4: Then sorting should be on students First name. Click Next.
Step-5: Select Layout type as “Block”. Click Next.
Step-6: Type the Report Name as “Students Details”. Click Finish.

The reultant Report will be displayed as below
6.
Computer Assisted Audit Techniques (CAAT)
1. Introduction
Computer Assisted Audit Techniques (CAAT) or General Audit Software enables us to import data and analyse it for
our audit requirements. CAAT software generally has the following tools built in, to facilitate our audit:
1. Data Statistics
2. Data Extraction
3. Data Explore -Gaps, Duplicates, Benford Analysis, Statistics
4. Data Categorisation - Summarise, Aging, Stratification, Pivot Table Reports, Charts
5. Data Relationship - Join, Append, Compare
6. Sampling – Random, Systematic, Stratified Random
We shall learn to use CAAT software for the aforementioned typical audit requirements in this section.
Sr. No. Chapter Title Total
Computer Assisted Audit Techniques (CAAT)
1 Data Analysis Using IDEA 12
2 Advanced Data Analysis Using IDEA 8
Total 20
Note: All the exercise related files are available CAAT folder.
Revision Case Studies-
2. Chapter : 3 Data Analysis Using IDEA

Exercise 3.1: Importing MS Access s data file into IDEA SM2
We need to import a file into CAAT Tools before we can analyse it for typical requirements. Importing structured data
from MS - Excel file is very easy.
Note - In this exercise students are required to import sample.xlsx file from EXCEL folder into CAAT folder.
For Result please refer to Page No 186 of Study Module –II.
Exercise 3.2: Summarization SM2
We can easily view summary of data in files available in CAAT Tools. In this exercise we shall compute Summary or
Total of Sales Transactions in the SAMPLE.XLS file imported in earlier exercise. Students are required to compute
Sales Transactions total by INV_NO and generate a list of outstanding sales as well as to identify the number of
INV_NO and the Sales per INV_NO.
Note – For above use Detailed Sales sheet from sample.xlsx file in CAAT folder.
Exercise 3.3 Statistics SM2
CAAT Tools enable us to view Statistics of data in files imported or available to have a better understanding of details
available for numeric fields. In this exercise, students are required to select Sample Customer Database and generate
Statistics. Please note the generated Statistics including Net Value, Number of Records, Average, Max, Min, Standard
Deviation, Variance etc.
Book talks about Customer Database?
Note – For above use Customer.mdb database from CAAT folder.
Exercise 3.4 Systematic Sampling SM2
The Standard on Auditing SA-530 on Audit Sampling applies when the auditor has decided to use audit sampling in
performing audit procedures. It deals with auditor’s use of statistical and non-statistical sampling when designing and
selecting the audit sample, performing tests of controls and tests of details and evaluating the results from the sample.
The Appendix 4 of SA-530 on Sample Selection Methods enumerates following principal methods of sample selection:
a) Random Selection (applied through random number generators)
b) Systematic Selection
c) Monetary Unit Sampling
d) Haphazard Selection
e) Block selection
Extract Systematic Record Sample of 100 Records from Detailed Sales sheet from Sample.xlsx file in IDEA with every
10th Record.
Exercise 3.5 Random Sampling SM2
Extract Random Record Sample of 10 Records from Detailed Sales sheet from Sample.xlsx file in IDEA.
Exercise 3.6 Stratification SM2
Stratification is the process of totalling values falling in ranges. Aging analysis of invoices less than 30 days, upto 90
days, upto 180 days is an example of Stratification.
Using Sample Sales Transaction file in IDEA, stratify sales by Customer ID for values upto 10000, 20000, 30000,
40000 and 50000.
Exercise 3.7 Sorting SM2
CAAT Tools enable us to easily sort data for our analysis and reporting requirements. Sorting can be on multiple fields
for example first by country, then by region (East/ West/ North/ South) and then alphabetically by name. Each sorting is
referred to as KEY. Sort the Sales Transactions file in IDEA by Transaction Date.
Exercise 3.8 Duplicate Detection SM2
CAAT tools enable us to detect duplicates, for example duplicate invoices. Students are required to find Duplicate
Invoices in the Detailed Sales sheet from Sample.xlsx file based upon the field named INV_NO.
Exercise 3.9 Gap Detection SM2
CAAT tools enable us to detect gaps, for example missing invoices. Students are required to find Missing Invoices (Gap
Detection) in the Detailed Sales sheet from Sample.xlsx file based upon the field named INV_NO.
Exercise 3.10 Aging Analysis SM2
Aging Analysis is a form of Stratification wherein grouping is done based upon the date of invoice. Students are
required to undertake Aging analysis of the Detailed Sales sheet from Sample.xlsx file in IDEA by taking the date of
processing as 20011/09/10.
Exercise 3.11 Data Extraction SM2
CAAT Tools enable us to extract data from a work file based upon our requirements, for further analysis/ reporting.
Students are required to EXTRACT all records from Detailed Sales sheet from Sample.xlsx file in IDEA where the
SALES_PLUS_TAX>=65000.
Exercise 3.12 Benford’s Law SM2
Use Detailed Sales sheet from Sample.xlsx file and perform Benford’s Law function on SALES_PLUS_TAX field.
3. Chapter : 4 Advanced Data Analysis Using IDEA

Exercise 4.1: Key Value
Import file sales register.xls into IDEA. Extract all the sales invoices product wise and region wise using Key value
option. Extracted files can be used for conducting further analytics such as month wise and day wise product sales and
& month wise and day wise region sales using field statistics option.
Note – For above exercise import sales register.xls file into IDEA in CAAT folder.
For Result please refer to Page No 233 of Study Module –II
Exercise 4.2: Top Records
Extract top two records per customer type wise in the already imported file sales register in exercise 4.1 above. IDEA
also offers bottom two records option as well.
Primarily top record function is used to perform Relative Size Factor (RSF) analysis. Import Payment.xls file into IDEA
and identify top 2 vendor payment amount wise and save the result in a new file and give name RSF analysis.
Note – For above exercise import Vendor payments.xls file into IDEA in CAAT folder.
For Result please refer to Page No 235 of Study Module –II
Exercise 4.3 Benford Law Analysis
Import file account details.xls and perform Benford analysis first digit test to conform whether the data meets the
Benford test and can be used for performing further analytics.
Note – For above import account details.xls file into IDEA in CAAT folder..
For Result please refer to Page No 250 of Study Module –Il
Exercise 4.4 using @ Advance Functions
Apply following functions to imported files and analyse the results
a) Find differences in days between invoice date and pay date in payment file. Use @age () function
b) Find whether any payment is made on weekends in payment file. Use @day() function and/or @dow()
c) Import supplier master file and remove alphabets and special characters from MOB_NO field using
@justnumbers() function. Use the same function on vendor ID filed and see the results
Note – For above import suppliers master.xls file into IDEA in CAAT folder..
For Result please refer to Page No 252 of Study Module –IV
Exercise 4.5 Statistics – correlation & Trend Analysis
Import production.xls file and find out correlation between overtime production and overtime hours. Analyse the finding
as to whether there is a weak or strong correlation between production numbers and overtime payments. similarly
perform trend analysis including forecast on production in units.
Note – For above import production.xls file into IDEA in CAAT folder..
Exercise 4.6 Import .pdf / .prn files
Import sales register.pdf file into IDEA. For additional practice import annexure 1.prf file in CAAT folder..
Exercise 4.7 Append Boolean and Multistate fields
Use account details file and append Boolean field give name as ‘check status’, multistate field and give field name
‘audit status’ and editable character field and give name ‘audit remarks’
Note – For above import production.xls file into IDEA in CAAT folder..
Exercise 4.8 Join Database
Use customer details and account details. Join both the databases using “join” option and “visual connector” option
using key field “CUSTNO”.
7.
Accounting Package
1. Introduction
Accounting is perhaps one of the first applications computerised by businesses and enterprises as it is (a) statutory
requirement, (b) relatively well defined requirements which are common across industries, trades and institutions, (c) is
a pain point, till computerised and the fact that (d) readymade software applications are already available and (e) can
be started with little coding, requirement gathering, and implementation. Moreover, accounting is one of the core areas
for professional accountants.
Most business entities, intuitions, charitable and educational entities generally implement a suitable Accounting
Systems through Accounting Packages like Tally/ Busy etc., to name a few; or as a part of an ERP implementation like
SAP, Oracle, Navision etc.
As a part of our study of Accounting Software, we shall take Tally as Software to learn how to create accounts, pass
vouchers, and generate Trial Balance and Final Accounts. While we are sure that some of you have already had an
exposure to this software, we are also confident that students shall learn some useful tips and tricks in better use of
this software through the practical exercises and case studies covered in this Practice Manual.
Accounting Package
1 Principles of Accounting --
2 Introduction to Tally.ERP 9 3
3 Voucher Entry in Tally.ERP 9 4
4 Generating Reports in Tally.ERP 9 8
5 Financial Analysis Tools in Tally.ERP 9 1
Total 16
2. Chapter 2: Introduction to Tally.ERP 9

Exercise 2.1: Payroll Exercise New
Create the following Employee Masters:
Employee Name Department Date of Joining
Ajay Administration 1-4-2016
Vijay Sales 1-5-2016
Manish Purchase 1-5-2016
Ashu Research and Development 1-6-2016
Gagan Purchase 1-4-2016
Murari Sales 1-6-2016
Note: - Fill the General Info, Payment Detail and Statutory Detail of overall Employees.
Create the following Units Create the following Attendance Types
Name Type Symbol Name Under Attendance/ Production Type Period Type
Hour Simple Hrs Present Primary Attendance/ Leave with pay Days
Minutes Simple Mins Absent Primary Leave without pay Days
Hrs of 60 Mins Compound Hrs of 60 mins Overtime Primary Production Hrs of 60 Mins
Create the following Pay Heads:-
Calculation
Affect Name to
Pay Head Pay Head Use for Calculation Period/
Under Net Appear in Pay
Name Type Gratuity Type Production
Salary Slip
Type
Earnings for Indirect On
Basic Pay Yes Basic Yes Months
Employees Expenses Attendance
Earnings for Indirect As Computed Current earning
DA Yes DA No
Employees Expenses Value Total
House Rent Earnings for Indirect As Computed on Specified
Yes HRA No
Allowance Employees Expenses Value formula
Earnings for Indirect
Conveyance Yes Conveyance No Flat Rate Months
Employees Expenses
Earnings for Indirect On
Overtime Pay Yes Overtime No Overtime
Employees Expenses Production
Earnings for Indirect As User
Variable Pay Yes Variable Pay No ---------
Employees Expenses Defined Value
Employees PF Employees
Current As Computed on Specified
Contribution Statutory Yes EPF @ 12% ----------
Liabilities Value formula
@12% Deductions
Hint:-
 Basic pay is Based on Present
 DA is under Normal Rounding Method & Based on Current earning Total & the percentage of DA is 10%
 HRD is under Normal Rounding Method & based on Specified formula & i.e.:-Basic + DA and percentage is 8%
 PF should be as per the below picture
Create the following Salary Detail:-

Employee Name Pay Head Rate Effective Date
Ajay Basic 1000 1-4-2016
Vijay Basic 2000 1-5-2016
Manish Basic 1500 1-5-2016
Ashu Basic 3000 1-6-2016
Gagan Basic 1200 1-4-2016
Murari Basic 1000 1-6-2016
Note: - Also enter the Details of Pay Head in a Salary Detail of all employees.
After completing the Payroll Info now we are doing the entries in Payroll Master which are given below:-
Attendance/ Production Types Vijay Ajay Ashu Murari
Present Days 26 22 15 17
Attendance/ Production Types Vijay Ajay Ashu Murari
Overtime hours 900 Min (15hr x 60min) 720 Min (12hr x 60min)
Note:-
 Overtime Rate 100 Rs/- per Hour.
 Above attendance/ production table is only for first month, rest month and employees are present for 30 days.
 Enter the entries in Payroll Master of overall employees through Auto Fill.
 Find the output till 31 July 2016 of all the employees.
Output:-
Basic Pay:-454000 DA:45400 HRA:39952 Overtime:2700 PF:4800
Exercise 2.2: Enter the transactions and prepare Order Processing New
Order Processing Exercise
Order
Date Party Particulars Stock Item Qty Rate Value
Due on
Purchase Order Processing
Khare Khote & Order placed for
01.04.16 01.05.16 Onida 21" TV 50 10,000 500,000
Co. purchases
Kavita Sudha & Order placed for
02.04.16 02.05.16 LG 300 Ltr. Fridge 40 13,000 520,000
Co. purchases
Bhatia Saxena & Order placed for
02.04.16 02.05.16 Videocon 5 Ltr. Washing Machine 30 15,000 450,000
Co. purchases
Receipt Processing
Khare Khote &
01.05.16 Goods Received Onida 21" TV 50 10,000 500,000
Co.
Kavita Sudha &
02.05.16 Goods Received LG 300 Ltr. Fridge 15 13,000 195,000
Co.
Bhatia Saxena &
01.06.16 Goods Received Videocon 5 Ltr. Washing Machine 18 13,000 234,000
Co.
Purchase
Khare Khote &
01.05.16 Purchases recorded Onida 21" TV 50 10,000 500,000
Co.
Kavita Sudha &
02.05.16 Purchases recorded LG 300 Ltr. Fridge 15 13,000 195,000
Co.
Sales Order Processing
Arun Manoharan Order recorded for
01.04.16 01.05.16 Onida 21" TV 30 12,000 360,000
& Co. sales
Tanker & Order recorded for
02.04.16 02.05.16 LG 300 Ltr. Fridge 25 14,500 362,500
Paniwala sales
Order recorded for
02.04.16 Gupta & Gupta 02.05.16 Videocon 5 Ltr. Washing Machine 20 16,500 330,000
sales
Delivery Processing
Arun Manoharan
01.05.16 Goods delivered Onida 21" TV 30 12,000 360,000
& Co.
Tanker &
02.05.16 Goods delivered LG 300 Ltr. Fridge 15 14,500 217,500
Paniwala
01.06.16 Gupta & Gupta Goods delivered Videocon 5 Ltr. Washing Machine 5 14,500 72,500
Sale
Arun Manoharan Sales Invoice
01.05.16 Onida 21" TV 30 12,000 360,000
& Co. Raised
Tanker & Sales Invoice
02.05.16 LG 300 Ltr. Fridge 15 14,500 217,500
Paniwala Raised
Output:-
Balance Sheet Total:- Closing Stock:- Purchase Order Outstanding:- Sales Order Outstanding:-
1379000 729000 800000 392500
Exercise 2.3: Security Controls SM2
ABC Ltd. has installed multi-user Tally Gold software. There are 9 employees in accounts department. The names
of employees, their designation and responsibilities are as follows:
Name of
Sr. Designation Responsibilities
Employee
Overall responsibility of accounts and finance function, reporting to top
1 Aditya, C.A. Finance Manager
management, statutory compliances, etc.
Getting data entered by accounts executive and operators, compilation of data,
2 Shahrukh Accounts Manager supervision over operators, Preparation of VAT, TDS, Service Tax Challans and
Returns, Providing information to Finance Manager.
Getting data entered by accounts executive and operators, compilation of data
3 Saif Accounts Manager supervision over operators, Preparation - of VAT, TDS, Service Tax Challans and
Returns, Providing information to Finance Manager.
4 Rani Accounts Executive Data Entry, Checking of data, Providing information to Accounts Manager.
5 Priti Accounts Executive Data Entry, Checking of data, Providing information to Accounts Manager.-
6 Bipasha Accounts Executive Data Entry, Checking of data, Providing information to Accounts Manager,-
7 Kareena Accounts Executive Data Entry, Checking of data, Providing information to Accounts Manager.-
8 Jony Data Entry Operator Data Entry
9 Rajpal Data Entry Operator Data Entry
Following are the Directors of the company
Shri Yash Chopra, Director Accounts & Finance

Shri Subhash Ghai , Director Marketing
Shri Rajkumar Santoshi, Director Operations
Shri Vidhu Vinod Chopra, Director
3. Chapter 3: Voucher Entry in Tally. ERP9

Exercise 3.1: Recording Financial Transactions New
M/s. ABC & Company started business in 2015. Create a company in the name of M/s. ABC & Co. and pass
necessary vouchers to record the following transactions.
Sr. Date Particulars
1 01-04-15 Salary paid by cash Rs. 1,000
2 02-04-15 Cash Sales for Rs. 1,000 Tea 200 kgs @ Rs. 50.00
3 01-05-15 Cash drawn from IB Cheque No. 031267 Rs. 75,000/-
4 01-06-15 Cash deposited in CB Rs. 20,000/- using 1000 Rs Currency.
5 02-06-15 Payment through Central Bank Cheque No. 609606 for Auditor fees Rs. 1,000/-
6 01-07-15 Payment through bank [IB] Cheque No. 0314195 for purchase of Sugar SS 50 Kg’s @ Rs.125
7 02-07-15 Bank Pay [IB] Cheque No. 0314195 Sugar-SS 50 Kg’s @ Rs. 125
8 01-08-15 Cheque deposited in IB for sales of Tea 400 kg’s @ Rs. 50
9 02-08-15 Cash Sales of Tea 200 kg’s @ Rs.50
10 31-08-15 Cash Sales of Ghee 100 kgs @ Rs. 2600.50
11 01-09-15 Cash received as ‘Commission’ Rs 1500
12 02-09-15 Cash sales of Sugar-SS 50 Kg’s @ Rs. 125 & Ghee 150 kg’s @ Rs. 2600.50
13 01-10-15 Enter the details here as a sales order: Vimal Nath & Co. likes to order Ghee-S 500 kg’s @ Rs. 3200.
14 02-10-15 Cash received as ‘Commission’ Rs 2000
15 31-10-15 Do invoice entry for the sales order dated 01/10/2015
16 01-11-15 Cash sales of Ghee-S 200 kgs @ Rs. 3200
17 01-11-15 Enter the following as sales order: Alpha Industries would like to order Ghee 100 kg’s @ Rs. 2600.50
18 02-11-15 Do invoice entry for the sales order
19 01-12-15 Purchase by Mehta Industries Sugar-SS 50 Kg’s @ Rs.125
20 02-12-15 Raise a credit note for the same
21 02-12-15 Purchase by Lal Agencies Ghee 150 kg’s @ Rs. 2600.50
22 31-12-15 Prepare credit note for rejection of Ghee 50 kg’s @ Rs. 2600.50 by Lal Agencies
23 31-12-15 Sales to Alpha Industries Ghee 300 kg’s @ Rs. 2500.50
24 31-12-15 Sales to Vimal Nath & Co. Ghee 600 kg’s @ Rs. 2500.70
25 01-01-16 Cash receipt of rent Rs. 1,000
26 01-01-16 Cash receipt of interest on investment Rs. 1,500
27 02-01-16 Sales to Vimal Nath & Co. Ghee 300 kg’s @ Rs. 2600.50; Sugar- SS 50 Kg’s @ Rs.125
28 31-01-16 Sales to Alpha Industries Ghee 150 kg’s @ Rs. 2600.50
29 01-02-16 First purchase to Abha Industries 3,000 kgs Ghee @ Rs. 1500
30 01-02-16 Credit note for Vimal Nath Ghee 400 kg’s @ Rs.2600.50
31 01-02-16 Gave Purchase order to Bimanlal Agencies Sugar-SS 50 Kg’s @ Rs. 110
32 02-02-16 Sales to Acrobat 1,50 kgs Ghee Rs.2550
33 02-02-16 Mehta Industries gave Purchase order of Sugar-SS 50 Kg’s @ Rs. 125
34 01-03-16 Purchase from Bimanlal Agencies Ghee-S 800 kg’s @ Rs.1500.50
35 01-03-16 Purchase by Mehta Industries Ghee 150 kg’s @ Rs. 26.50
36 01-03-16 Cash Sales of Ghee 500 kgs @ Rs. 2650.70
37 01-03-16 Cash Sales of Sugar-SS 100 Kg’s @ Rs. 125
38 02-03-16 Purchase from Bimanlal Agencies according to the order
39 02-03-16 Above Entry Carriage Inward Rs. 250 through cash
40 02-03-16 Cash Sale of Scrap wastages Rs. 5,000
If quantity is in Negative then purchase rates for Sugar-SS @ 110, Tea @ 40 and make sure that the
41 31-03-16 Closing stock must be Nil. If any pending then use Sales rates for Ghee @ 2600.50, Ghee-S @ 3300, Tea
@ 50 and Sugar-SS @ 130.
Output:-
Balance Sheet Total: Profit & Loss A/c: Bank Credit IB: Bank Debit CB: Cash:
10306075 4600175 61250 19000 5061275
Exercise 3.2: Recording Financial Transactions and Depreciation SM2
M/s. Evergreen Traders is running a business from 1.4.2015 and keeps his books of accounts in Tally with
accounts only. You are required to record the following transactions in the books of M/s Evergreen Traders:
Sr. No. Date Particulars
1 1.4.2015 Received cash as Capital for Rs. 2,00,000/-
2 2.4.2015 Cash deposited in B.O.I. for Rs. 1,00,000/-
3 2.4.2015 Credit Purchases from Charles Co. as per Inv. No. 12 for Rs. 75,300/-
4 2.4.2015 Credit purchases from James Co. as per Inv. No. 253 for Rs 50,500/-.
5 1.5.2015 Credit purchases from Donold Co. as per Inv. No. 325 for Rs. 60,000/-
6 2.5.2015 Purchase Return from Charles Co. for Rs. 10,000/-
7 2.5.2015 Purchase Return from Donold Co. for Rs. 25,000/-
8 1.6.2015 Cash purchases from Victor Co. for Rs. 35,600/-
9 1.6.2015 Credit Sales to James Traders as per Inv. No. 001 Rs. 1,25,000/-
10 1.6.2015 Credit sales to Victor Traders as per Inv. No. 002 Rs. 1,75,000/-
11 1.6.2015 Credit Sales to Smith Traders as per Inv. No. 003 for Rs. 75,000/-
12 2.6.2015 Cash sales as per Inv. No. 004 for Rs. 1,00,000/-
13 2.6.2015 Returns from Victor Traders for Rs. 25,000/-
14 1.7.2015 Returns from Smith Traders for Rs. 15,000/-
Payments made by Cash
Furniture Rs. 25,000/-
15 2.7.2015
Salaries Rs. 50,000/-
Wages Rs. 30,000/-
Sr. No. Date Particulars
Rent Rs. 25,000/-
Electricity charges Rs. 12,000/-
Electricity Deposit Rs. 10,000/-
16 31.7.2015 Stationeries purchased from Wilson co. for Rs. 2,000/- on credit
Depreciate Furniture @ 10% using Straight Line Method.
Output:-
Profit & Loss Account: 127100 Balance Sheet Total: 479900
Exercise 3.3: Recording Multiple Financial Transactions in Vouchers New
M/s. Bansal Motors started a business from 1/4/2015 and entered into following transactions which need to be
recorded in its books:
1 1.4.2015 Received capital by cash Rs. 20,00,000
2 2.4.2015 Cash deposited in ICICI bank Rs. 12,00,000
Purchase order given to Raj Motors (Order No. 101)
Kinetic Honda - 5 nos. @ Rs. 35,000/- each
3 1.5.2015
TVS –Victor - 3 nos. @ Rs. 46,800/- each
Yamaha - 5 nos. @ Rs. 42,900/- each
Purchase the stocks from Raj Motors
4 2.5.2015
Purchase order given to Naveen Motors (Order no: 102)
Pulsar - 5 nos. @ Rs. 50,000/- each
5 31.5.2015 Hero Honda Passion - 5 nos. @ Rs. 48,500/- each
Hero Honda Ambition - 5 nos. @ Rs. 46,700/- each
TVS –Scooty - 10 nos. @ Rs. 24,000/- each
Purchase the stocks from Naveen motors
Hero Honda Passion - 5 nos. @ Rs. 48,500/- each
6 1.6.2015
(-) Cash Discount 5% on total purchase.
Sales order given by RS Motors Company(Order No 201)
7 1.6.2015 Kinetic Honda - 2 nos. @ Rs. 39,900/- each
Credit sales to RS Motor Company
8 2.6.2015
(+) TNGST 7% on total sales
Credit sales to KJ Motors
9 1.7.2015 Hero Honda Passion - 2 nos. @ Rs. 50,000/- each
Credit sales to JJ Motors
Yamaha - 1 no. @ Rs. 45,000/-
10 1.7.2015
Cash sales to Sheeba Motors
11 1.7.2015
(-) Cash discount 3%
12 1.7.2015 Received cash from RS Motors - Rs. 1,25,000
13 1.7.2015 Received cheque from KJ Motors - Rs. 2,25,000
14 2.7.2015 Paid cheque to Raj Motors - Rs. 2,50,000
15 31.7.2015 Paid cheque to Naveen Motors - Rs. 1,00,000
16 1.8.2015 Paid cheque to JJ Motors - Rs. 1,50,000
Payments made by cash Rs.
Telephone charges - 5,000
Salary - 10,000
17 31.3.2016
Stationery - 1,000
Furniture - 20,000
Electricity Charges - 2,500
Output:-
Balance Sheet Total: 3020277 Closing Stock: 594100 Profit & Loss A/c: 62671 Bank: 925000 Cash: 64812
Exercise 3.4: Depreciation New
Best & Co. started a business of Home appliances from 1.4.2016 and maintains the accounts in Tally with Inventory.
1 1.4.2016 Received capital by cash Rs. 7,00,000/-
2 1.4.2016 Cash deposited in P.N.B. Rs. 3,00,000/-
Credit purchases from Blue Hills as per Inv. No. 12
Samsung color LCD 10 Nos. @ Rs. 25,000/- each
Usha Ceiling fan 100 Nos. @ Rs. 8,00/- each
3 1.4.2016
Usha Table fan 100 Nos. @ Rs. 1,000/- each
Bajaj Mixi 100 Nos. @ Rs. 1,500/- each
Bajaj Grinder 100 Nos. @ Rs. 2,000/- each
Cash Purchases Blue Hills as per Inv. No. 16
4 1.4.2016
Samsung color LCD 1 Nos. @ Rs. 24,000/-
Credit sales to Rose Traders as per Inv. No. 001
5 2.4.2016 Usha Table fan 70 Nos. @ Rs. 1,500/- each
Plus PGST 4% on total sales
Cash Sales as per Inv. No. 002
6 2.4.2016 Usha Table fan 15 Nos. @ Rs. 1,500/- each
Plus 4% PGST on total sales
7 2.4.2016 Axis Bank Cheque No. 1233123 issued to Blue Hills 70% of Purchase value
8 2.4.2016 Received Cheque No. 0000786 of Bank of India from Rose Traders 80% of Sale value
Payments made by cash
Paid to Petty cash Rs. 3,000/-
Computer Rs. 25,000/-
Salaries Rs. 32,000/-
Wages Rs. 23,500/-
9 2.4.2016
Carriage Inwards Rs. 12,500/-
Carriage Outwards Rs. 7,500/-
Telephone Deposit Rs. 15,000/-
Rent Rs. 10,000/-
Electricity charges Rs. 5,800/-
Payments made by Petty Cash
Conveyance Rs. 700/-
10 2.4.2016 Staff Welfare Rs. 500/-
Postages Rs. 300/-
Stationery Rs. 700/-
11 2.4.2016 Depreciate Computers @ 25%.
Output:-
Balance Sheet Closing Stock: Profit & Loss A/c: Bank: Cash:
Total:1056168.18 129318.18 87568.18 336400 411100
4. Chapter 4: Voucher Entry in Tally. ERP9

Exercise 4.1: Recording Financial Transactions using Accounts only New
Create a Company with “Your Name @ Automobiles” and create the following Ledgers:
Ledgers Under Group
Capital A/c Capital A/c
Building A/c Fixed Assets
Air Conditioner A/c Fixed Assets
Furniture & Fixtures Fixed Assets
Vehicles A/c Fixed Assets
Computer A/c Fixed Assets
Machine A/c Fixed Assets
Telephone Bill A/c Indirect Expenses
Electricity Bill A/c Indirect Expenses
Salary A/c Indirect Expenses
Depreciation A/c Indirect Expenses
Commission Received A/c Indirect Income
Rent received A/c Indirect Income
SBI A/c Bank A/c
PNB A/c Bank A/c
Voucher Entry
Sr. Receipt Voucher Sr. Payment Voucher
1 Receiving cash Rs. 30,00,000 with capital A/c 5 Building purchase worth Rs.1,00,000 with cash.
2 Receiving Rs. 60,00,000 with capital A/c in PNB bank. 6 Furniture purchase worth Rs. 50,000 with cash
3 Commission Received Rs. 2,00,000 7 Air Conditioner purchase worth Rs. 1,00,000 with cash.
4 Rent Received Rs. 50,000 with cash 8 Vehicles purchased of Rs. 5,00,000 with PNB.
9 Computer purchased of Rs. 50,000 with PNB.
10 Telephone bill paid by PNB Rs. 5,000.
11 Electricity bill paid with cash Rs. 10,000.
Output:-
Net profit: 235000 Cash: 2990000 Bank: 5445000 Balance Sheet Total: 9235000
Exercise 4.2: Recording Financial Transactions in Books of Accounts New
Delhi Garments have started a business of readymade garments from 1.4.2015. Following are the transactions entered
during 2015-2016, which need to be recorded in their books.
Sr. Date Particulars Amount V. Type
1 1.4.2015 Capital introduced in business by proprietor in Cash 1,00,000 Receipt
2 2.4.2015 Cash deposited in SBI, Ramdas peth for opening current account no. 5,000 Contra
Sr. Date Particulars Amount V. Type
521
3 1.5.2015 Cash deposited in SBI current account no. 521 80,000 Contra
4 1.5.2015 Garments purchased from R.D. Distributors on credit 40,500 Purchase
5 2.5.2015 Sold garments to Mr. J.N. Shah on cash 6,800 Sale
6 2.5.2015 Purchased garments from Milton Dresses by paying Cash 9,500 Purchase
7 1.6.2015 Sold garments to S.M. Bakare on credit 26,800 Sale
8 2.6.2015 Cash withdrawn from SBI current account no.521 20,000 Contra
9 1.7.2015 Paid vide ch. no. 636251 to R.D. Distributors 21,000 Payment
10 2.7.2015 Received vide ch. no. 995588 of Bank of India from S.M. Bakare 15,000 Receipt
11 1.8.2015 Paid for purchase of files for office 540 Payment
12 2.8.2015 Purchased furniture on credit from Vishal Furnitures 40,200 Journal
Paid to ICICI Life Insurance towards annual premium of proprietor vide
13 1.9.2015 6,200 Payment
ch. no. 636252
Office taken on rent, paid towards office deposit to Mr. M.C. Patel vide
14 2.9.2015 25,000 Payment
ch. no. 636253
Amount transferred from account no. 220 maintained with SBI
15 1.10.2015 75,000 Receipt
Ramdaspeth current account no. 521
16 2.10.2015 Cash received from S.M. Bakare 5,000 Receipt
17 1.11.2015 Rent paid 4,000 Payment
18 2.11.2015 Paid cash to Vishal Furnitures 10,000 Payment
19 2.12.2015 Sales made in cash 25,000 Sale
Loan taken from Mr. M.M. Joshi vide ch. no. 253652 of Bank of
20 2.12.2015 1,00,000 Receipt
Rajasthan
21 1.1.2016 Salary paid vide ch. no. 636254 10,000 Payment
22 2.1.2016 Octroi paid in cash 1,250 Payment
23 2.2.2016 Advance tax paid 5,000 Payment
24 1.3.2016 Amount deposited in savings account of proprietor vide ch. no. 636255 10,000 Payment
25 31.3.2016 Provision to be made for telephone expenses 2,348 Journal
26 31.3.2016 Interest @ 12% p.a. to be credited to M.M. Joshi Journal
27 31.3.2016 Charge depreciation on furniture @ 10% p.a. for one year Journal
Output:-
Balance sheet Total:- 314848 Bank:- 182800 Cash:-41510 Loss:-17558
Hints:-
 Single Bank a/c is created in books of Delhi Garments.
 Personal Entries are recorded in a Drawing A/c or Capital A/c
 No Direct Entry is entered in a P&L A/c
Exercise 4.3: Recording Financial Transactions in Books of Accounts with Accounts Only New
Journalise the following transactions in the books of AK Traders during financial year 2016-17
Date Entries
1.4.2016 Commenced business with Rs 20000, Paid into the bank Rs 10000.
1.5.2016 Bought furniture for Rs 900.
31.5.2016 Bought goods from Krishan for Rs 4000 for cash.
1.6.2016 Sold goods for Rs 1700.
1.7.2016 Paid telephone expenses Rs 400
2.8.2016 Purchase goods for Rs 1000 from Gupta & Sons.
1.9.2016 Paid Rs 100 for advertisement by cheque.
1.10.2016 Purchased a typewriter for Rs 8000 on credit from Yogesh
1.11.2016 Sold goods to Raghav for Rs 2900.
1.12.2016 Withdraw Rs 350 from the bank for private use.
31.12.2016 Sold goods to ram for Rs 900 for cash
1.1.2017 Received cash from Raghav Rs 2850 , discount allowed Rs 50/-
31.1.2017 Paid into bank Rs 2500
1.2.2017 Issued a cheque for Rs 300 in favour of the landlord for rent for the month of April
1.3.2017 Issued a cheque in favour of Gupta & Sons for Rs 950 in full settlement of their account.
31.3.2017 Paid salaries to staff Rs 2000.
Output:-
Trial Balance:-33550 Cash:- 5650 Bank:- 10800 P & L Account :- 10300 Balance Sheet Total:-27650
Exercise 4.4: Prepare AB Furniture books according to the following entries New
Journalise the following transactions in the books of AB Furniture during Financial Year 2015-16
Date Entries
1.4.2015 Goods for personal use(Sale price 1000,cost price Rs 800)
2.4.2015 Goods costing Rs 500 distributed as free samples.
1.5.2015 Goods costing Rs 500 given as charity
2.5.2015 Goods stolen (Sale price Rs 500, Cost price Rs 400)
1.6.2015 Furniture costing Rs 500 was used in furnishing the office.
Purchased machinery for Rs 5000 from Gaurav and gave him a cheque for the amount. Paid
2.6.2015
installation charges Rs 100.
1.7.2015 Paid Rs 975 to Krishan on his account for Rs 1000.
2.7.2015 Received a cheque of Rs 975 from Pinki in full settlement of his account for Rs 1000.
1.8.2015 Paid Rs 975 to Chetan in full settlement of an account for Rs 1000.
1.8.2015 Bank intimated that Pinki cheque returned unpaid.
Received first and final divided of Rs 60 Paisa in the rupee from the official received of Mr. Alok who
1.9.2015
owed Rs 1000.
2.9.2015 Rs. 125 owed by Sanjay written off as bad debt.
1.10.2015 Received cash for a bad debt written off last year Rs 50.
2.10.2015 Submitted a claim for breakage of goods to Ram who admitted Rs 100.
1.11.2015 Receive a claim from Anuj for defects on goods supplied to them: claim admitted Rs 150
2.11.2015 Bank Charges interest Rs 20.
1.12.2015 Allowed interest on capital @ 6% P.A for one month,(Capital Rs 10000)
2.12.2015 Charged interest on drawings Rs 500.
1.1.2016 Charged depreciation on furniture Rs 100.
2.1.2016 Rent due to landlord Mr. Manjeet Rs 80.
1.2.2016 Salary due to an employee Mr Ajay Rs 500.
1.3.2016 Wages outstanding Rs 100.
31.3.2016 Paid rent in advance Rs 100.
Output:-
Balance Sheet Total :-7070 Profit & Loss Account:- 375
Exercise 4.5: Inventory Management SM2
Note: Below entries are entered only Date wise rather than Table wise otherwise Tally transfers the profit or Loss
wrongly.
Enter opening stock in Inventory Master in Tally as follows:
Name of Item Group Quantity Unit Rate Value Godown
Onida 21" Onida 7 No. 8,920 62,440.00 Rohini
Onida 25" Onida 5 No. 13,440 67,200.00 Pitampura
Samsung 25" Samsung 9 No. 12,500 112,500.00 ITO
Samsung 29" Samsung 3 No 24,500 73,500.00 Rohini
Gold Metals 512.368 Gm 1,280 655,831.04 ITO
Silver Metals 6.892 Kg 19,240 132,602.08 ITO
Copper Metals 5.690 Kg 5,500 31,295.00 Rohini
Raymond Eleganza Cloth 540.22 Mtr. 467 252,282.74 Rohini
Raymond Royal Cloth 890.88 Mtr. 612 545,218.56 Pitampura
Total 1,932,869.42
Enter following Purchase and Sales transactions in Tally:
Date Party Stock Item Qty Unit Rate Value Godown
Purchases
01/04/2015 Onida Electronics Ltd. Onida 21" 10 No. 9,000 90,000.00 ITO
01/05/2015 Onida Electronics Ltd. Onida 12 No. 15,500 1,86,000.00 Rohini
02/06/2015 Samsung India Pvt. Ltd. Samsung 25" 8 No. 16,800 1,34,400.00 Pitampura
02/07/2015 Samsung India Pvt. Ltd. Samsung 29" 20 No. 22,400 4,48,000.00 ITO
01/08/2015 BPL Electronics Ltd. BPL 21" 23 No. 11,200 2,57,600.00 Pitampura
01/09/2015 Cash BPL 25" 32 No. 18,500 5,92,000.00 ITO
01/10/2015 Raymond Ltd. Raymond Eleganza 890 Mtr 450 4,00,500.00 ITO
02/11/2015 Raymond Ltd. Raymond Royal 1,020 Mtr 630 6,42,600.00 ITO
01/12/2015 Cash Gold 250 Gm 1,390 3,47,500.00 Rohini
01/01/2016 Cash Silver 2.380 Kg 19,880 47,314.40 Pitampura
01/02/2016 Raymond Ltd. Raymond Star 650 Mtr 730 4,74,500.00 Pitampura
01/03/2016 Cash Milk 120 Ltr 18.50 2,220.00 ITO
02/03/2016 LG India Ltd. LG 21" 25 No. 8,900 2,22,500.00 Rohini
Sales
1/04/2015 Pardesi Trading Co. Onida 21" 12 No. 9,500 1,14,000.00 Rohini
02/05/2015 Quality Electronics Onida 25" 13 No. 16,500 2,14,500.00 Pitampura
02/06/2015 Quality Electronics Samsung 25" 10 No. 17,770 1,77,700.00 ITO
01/07/2015 Cash Samsung 29" 15 No. 24,580 3,68,700.00 ITO
01/08/2015 Ajay Vijay & Co. BPL 21" 12 No. 13,500 1,62,000.00 Pitampura
01/09/2015 Noor & Ali & Co. BPL 25" 10 No. 21,000 2,10,000.00 ITO
01/10/2015 Milton Dresses Raymond Eleganza 250 Mtr 510 1,27,500.00 Rohini
01/11/2015 Milton Dresses Raymond Royal 150 Mtr 680 1,02,000.00 Rohini
02/12/2015 Laxmi Parvati & Co. Gold 150 Gm 1,420 2,13,000.00 ITO
01/01/2016 Cash Silver 1 Kg 20,500 20,500.00 Pitampura
01/02/2016 Hakim Munim & Co. Raymond Star 200 Mtr 800 1,60,000.00 Pitampura
02/03/2016 Cash Milk 50 Ltr 21.00 1,050.00 ITO
02/03/2016 Cash LG 21" 10 No. 9,800 98,000.00 Rohini
Manufacturing Journal Entries
31/03/2016 Gold ring manufactured by using 10 Gm gold, 4 Gm silver & 1 Gm copper. Total weight of ring was 15 Gm.
Transfer Entries
31/03/2016 Raymond Royal transferred 500 Mtr from ITO to Rohini godown.
31/03/2016 3.5 Kg silver transferred from ITO to Pitampura godown.
Output:-
Balance Sheet Total :-5037086.90 Closing Stock:-4057171.30 Gold Ring:-13985.02
Inventory Management
1-Apr-2015 to 31-Mar-2016
Outwards Closing Balance
Particulars Gross
Quantity Rate Value Consumption % Qty Rate Value
Profit
BPL 21" 12 no. 13500 162000 134400 27600 17.04 11 no. 11200 123200
BPL 25" 10 no. 21000 210000 185000 25000 11.9 22 no. 18500 407000
Copper 0.001 kg 5500 5.50 5.50 5.689 kg 5500 31289.50
602.368
Gold 160.000 gms 1418.13 226900 205900 21000 9.26 1323.83 797431.04
gms
Gold Ring 15.000 gms 932.33 13985.02
LG 21" 10 no. 9800 98000 89000 9000 9.18 15 no. 8900 133500
Milk 50 litr 21 1050 925 125 11.9 70 litr 18.50 1295
Onida 21" 12 no. 9500 114000 107040 6960 6.11 5 no. 9080 45400
Onida 25" 13 no. 16500 214500 174720 39780 18.55 4 no. 19620 78480
Raymond 1180.22
250.00 mtrs 510 127500 116750 10750 8.43 454.18 536032.74
Eleganza mtrs
1760.88
Raymond Royal 650.00 mtrs 641.54 417000 409500 7500 1.80 620.89 1093318.56
mtrs
Raymond Star 200.00 mtrs 800 160000 146000 14000 8.75 450.00 mtrs 730 328500
Samsung 29" 15 no. 24580 368700 336000 32700 8.87 8 no. 23187.50 185500
19520.3
Silver 4.504 kg 87919.52 86917.04 1002.48 1.14 8.268 kg 19392.77 160339.44
2
Samsung 25" 10 no. 17770 177700 125000 52700 29.66 7 no. 17414.29 121900
Grand Total 2365275.02 2117157.54 248117.48 10.49 4057171.30
Liabilities as at 31-Mar-2016 Assets as at 31-Mar-2016

Capital Account Current Assets 4859386.90
Loans (Liability) Closing Stock 4057171.30
Current Liabilities 2856100.00 Sundry Debtors 1480700.00
Sundry Creditors 2856100.00 Cash-in-hand -500784.40
Profit & Loss A/c 248117.48
Opening Balance
Current Period 248117.48
Diff. in Opening Balances 1932869.42
Total 5037086.90 Total 5037086.90
Exercise 4.6: Inventory Management in the books of Gold Silver Traders New
We have started a business of Gold Silvers trading.
Following are the transaction related to this segment of the business.
1. Capital introduced of Rs.10, 00,000/- on 01.04.16 (Cash).

2. 02.04.16 Loan taken from Mr. Raj Khanna of Rs. 9,50,000/- Ch.362585, of Bank of Maharastra,Shankar Nagar
Branch.
3. 02.04.16 Loan taken from Mr. Kamalkant of Rs. 8,80,000/- Ch. No.586985 of Bank of Baroda, South Ambazari
Road Branch.
Sr Following transactions are Purchases Following transactions are Sales

1 100gm Silver purchased on 02.04.16 at Rs.20/- per gram. 10gm silver sold on 02.04.16 at Rs. 20,66/-
100gm Gold purchased from M/s Ashu on 02.06.16 @Rs. 10,000 100gm Gold sold on 02.06.16 to Devarshi Alankar @ Rs.
2
per 10 gm. 10,980/- per 10gm.
2 Kg, Silver purchased from M/s.Chawla on 02.06.16 @Rs. 1500gm silver sold on 02.06.16 Gupta Jewellers @ Rs.
3
20,000 per Kg. 30,000/- per Kg.
1500gm silver purchased from Radheshyam & co. on
4 2000gm Silver sold on 02.07.16 @ Rs. 20,680/- per Kg.
01.07.16@Rs. 19,900 per Kg.
150gm Gold purchased from Mahakali Traders on 02.08.16 @Rs.
5 150gm Gold sold on 31.08.16 @ Rs. 10,965/- per 10gm.
9980 per 10gm.
Silver 2.5 Kg. Silver purchased from M/s. Chawla on 01.09.16 @ 2200gm Silver sold to Bhagwati Jewellers on 02.09.16
6
Rs. 19,990/- Per Kg. @Rs. 20,670/-per Kg.
250gm gold sold to Devarsh Alankar on 02.010.16 @ Rs.
7 300gm Gold purchased on 02.10.16 @ Rs. 9980/- per 10gm
10,980/- per 10gm
150gm Gold purchased from M/s.Ashu on 01.11.16 @Rs 9,985/- 200gm Gold sold to Gupta Jewellers on 01.11.16 @ Rs
8
per 10gm. 10,985/- per 10gm.
1500gm Silver purchased on 02.12.16 @Rs. 19,990/- per
9 1890gm Silver sold on 01.12.16 @Rs. 20,700/- per 1000g.
1000gm.
Sr Following transactions are Purchases Following transactions are Sales
10 3 Kg Silver purchased on 01.01.17 @Rs.20, 000/- per Kg. 2500gm silver sold on 02.01.17 @Rs 20,690/- per Kg.
11 500 gm Gold purchased on 01.02.17@ Rs. 10,000/-per 10gm. 450gm gold sold on 02.02.17 @Rs. 10,990/- per 10gm.
12 1500gm silver purchased on 02.03.17 @Rs. 19,990 per Kg. 2000gm silver sold on 31.03.17 @ Rs. 20,695/- per Kg.
 By Default method is applied in closing stock change it into FIFO method.

Output:-
Balance Sheet Total: Closing Stock according to Default Closing Stock according to FIFO
3487756.13 Method: 49953.13 Method: 50000
Exercise 4.7: Enter the transactions & prepare Sale & Purchase Book New
Date Entries
Bought following products from Yog Raj
100 bags Sugar @ Rs 100 per bag
April 1,
20 bags Rice @90 per bag
2016
30 bags Barley @40 per bag
25 bags Sugar @80 per bag
April 1,
Sold 20 Rice Bags @ Rs 100
2016
Sold following products to Bhandari
April 2, 25 bags Barley @48 per bag
2016 50 bags Sugar @120 per bag
Bought following products from Ravi
July 1,
2016
70 bags Maize @30 per bag
20 bags Wheat @80 per bag
Sold following products
August 1, 100 bags Sugar @70 per bag
2016 100 bags Maize @40 per bag (Here stock is negative and rest stock avail through market from Mr
Ajay on Temporary Basis Pass the memorandum entry).
Bought following products from Ashwani
Sep 1, 30 bags Sugar @150 per bag
2016 20 bags Ghee @150 per bag
Sold to Pakistani
Nov 1,
2016
5 bags Barley @50 per bag
Dec 1, 20 bags Wheat @100 per bag
2016 20 bags Ghee @200 per bag
Feb 1,
2017
Final settlements with Mr. Ajay pay Rs 900 on 31.3.2017
Output:-
Balance Sheet Total:-39625 P & L Account:-7375 Closing Stock:-1425
Exercise 4.8 Create ledgers along with proper grouping in Tally.ERP 9 and check the Balance Sheet SM2
D. C. Garments. Trial Balance as On 31st March 2016
Sr. Ledger Name Description Debit Credit
1 Capital Owner's money invested in business 10,40,670
2 SBI Car Loan Loan taken for purchase of car against mortgage of car 6,70,540
3 T. Asin Loan taken from friend for business without any security 4,00,000
Loan taken for working capital against security of Stock and
4 SBI Cash Credit 5,43,990
Debtors
Our supplier, we have purchased dresses from this company on
5 Kareena Fabrics Ltd. 97,200
credit and money is still payable
6 VAT Payable( 2015-16) VAT for the year payable to Government 15,230
7 BSNL Office telephone bill payable to BSNL for March 2015 2,540
8 Office Office place owned by and used by business 16,03,280
9 Land Land at wardha road, purchased as investment 8,65,000
10 A.B. Corp Shares Amount invested in shares of the company 50,000
11 Reliance Mutual Fund Amount invested in mutual fund 25,000
12 Sohail Khan Temporary loan given to brother without any security 75,000
13 Telephone Deposit Deposit kept with BSNL for office telephone connection 5,000
Our customer, we have sold goods to her on credit and money
14 S. Mallika 92,900
is still receivable
15 SBI Current Account Current account in the name of business maintained with SBI 34,290
16 Cash 19,700
Total 27,70,170 27,70,170
Output:-
Balance Sheet Total: - 27,70,170
5. Chapter 5: Financial Analysis Tools in Tally.ERP9

Exercise 5.1: Prepare Cash Book and Bank Book as per the following entries. New
Sr. No Entries
1 Balance of cash in hand Rs 1400. Overdraft Rs 5000
2 Invested further capital of Rs 10000 out of which Rs 6000 was deposited in the bank.
3 Sold goods for cash Rs 3000.
4 Collected from debtors of last year Rs 8000; discount allowed to them Rs 200.
5 Purchased goods for cash Rs 5500.
6 Paid Ram, a creditor Rs 2500; discount allowed Rs 65.
7 Commission paid to an agent Rs 530
8 Office Furniture purchased from AK Rs 200
9 Rent paid Rs 50
10 Electricity charges paid Rs 10
11 Drew cheque for personal use Rs 700
12 Cash sales Rs 2500
13 Collection from Atul Rs 4000: Deposited in the bank next day
14 Drew from bank for office use Rs 500
15 Drew a cheque for petty cash Rs 150
16 Dividend received by cheque Rs 50, Deposited in the bank same day
17 Commission received by cheque Rs 230
18 Drew from the bank for salary of the office staff Rs 1500 and salaries paid
19 Paid the salary of the manager by cheque Rs 500
20 Deposited cash in the bank Rs 10000.
Output:-
Cash:-810 Bank:-11930 Balance Sheet Total:- 9300

ITT Practice Manual 210805 135213

Uploaded by

Copyright:

Available Formats

ITT Practice Manual 210805 135213

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

ITT Practice Manual 210805 135213

Uploaded by

Copyright:

Available Formats

Case Studies & Exercises for Hands - On Training

©The Institute of Chartered Accountants of India

2. ICITSS - Information Technology

3. Practice Manual - Organisation

Sr. No. Unit Name Exercise Assignments

2. Chapter 1 : Introduction to MS – Word 2010

 Correct the spelling mistake in the paragraph.

Exercise 1.2: Securing Access to Word File New

Step-4 Save and close the file

Exercise 1.4: Sorting Data New

Step 2: Select Header row from My list has Group

Exercise 1.5: Saving Files with New Name - Save As New

Exercise 1.8: Formatting Paragraphs New

Exercise 1.10: Creating a New Style and Modifying Styles SM1

Exercise 1.11: Indentation (First Line/Hanging/Negative Indent) New

Exercise 1.12: Page Formatting New

Exercise 1.13: Changing Character Spacing New

Exercise 1.14: Letter Formatting SM1

3. Chapter 2 :The Insert Tab

3. Enter details of 4 students (Student ID – 1, 2, 4, 5) in the table.

Exercise 2.7: Adding picture SM1

Model East West North South

Step-6: Change the graph to Stacked Column in 3-D

Step-4: Download and insert picture of a computer.

4. Chapter 3 : Managing Documents

Step-4: Insert four citations at different places in the chapter.

Step-6: Insert a bibliography at the end of the chapter.

Exercise 3.3: Spelling and Grammar Checking New

Step-13: For each mistake, either Change it or choose Ignore.

Exercise 3.4: Track Changes SM1

Exercise 3.5: Comparing Documents SM1

Exercise 3.6: Macro SM1

Exercise 3.7: Themes and Margins SM1

Step-6: Save the file with name MyTemplate

5. Chapter 4 : Page Formatting, Printing and Mailing

Step-3: Select Envelop Size from Envelop Dialog box

Step-6: Select Excel Sheet in which Data have

Step-8: Click on Preview Result

Step-3: Select Recipients list (Use Existing List)

Step-8: Click on Preview Results

Earnings Amount (Rs) Deductions Amount (Rs)

Step-5: Click OK.

Step-10: Click Entire Spread sheet, and then click OK.

Earnings Amount (Rs) Deductions Amount (Rs)

Earnings Amount (Rs) Deductions Amount (Rs)

Exercise 4.6: Header & Footer New

1.1.1 Definition of Internet

Exercise 4.8: Creating an Invitation Card SM1

Assignment 14: Document Formatting

2. Chapter 1 : Introduction to MS - Excel 2010

Step-7: Save this file as My_Budget.xlsx

3. Chapter 2 :Cell Referencing Range and Functions

Highest 1,635,400,000 367,080,000

Exercise 2.4: Change Case & Trim New

Exercise 2.5: If Function New

Exercise 2.6: Min & Max Function New

Exercise 2.7: Validation of a given PAN number New

Exercise 2.8: Find the rank of an Information Technology student New

Exercise 2.12: Payroll Assignment New

4. Chapter 3: Working with Worksheets, Charts, Macros and Hyperlinks