Itt_practice_manual_version3.pdf

Case Studies & Exercises for Hands-On Training

ITT Course Practice Manual

The Institute of Chartered Accountants of India

ITT Course Practice Manual

Page 1 of 149

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 ITT 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 ITT Section, 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

:

[email protected]

Website

:

www.icai.org

Edition

:

October, 2016

ISBN

:

978-81-8441-823-1

Sale Price

:

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

:

Repro India Ltd.

150/-

February / 2016 / P1878

ITT Course Practice Manual

Page 2 of 149

Table of Contents 1. Introduction to Practice Manual ................................................................................................................................................... 5 1. 2. 3.

Introduction ............................................................................................................................................................................. 5 ITT Course ............................................................................................................................................................................. 5 Practice Manual - Organisation .............................................................................................................................................. 5

2. MS – Word 2010 ............................................................................................................................................................................. 7 1. 2. 3. 4. 5.

Introduction ............................................................................................................................................................................. 7 Chapter 1 : Introduction to MS – Word 2010 .......................................................................................................................... 7 Chapter 2 :The Insert Tab .................................................................................................................................................... 17 Chapter 3 : Managing Documents ........................................................................................................................................ 24 Chapter 4 : Page Formatting, Printing and Mailing .............................................................................................................. 32

Assignments – MS Word 2010 ......................................................................................................................................................... 43 3. MS – Excel 2010 ........................................................................................................................................................................... 46 1. 2. 3. 4. 5. 6. 7.

Introduction ........................................................................................................................................................................... 46 Chapter 1 : Introduction to MS-Excel 2010........................................................................................................................... 46 Chapter 2 :Cell Referencing Range and Functions .............................................................................................................. 51 Chapter 3: Working with Worksheets, Charts, Macros and Hyperlinks ................................................................................ 63 Chapter 4: Consolidation of Data and Data Analysis ........................................................................................................... 70 Chapter 5: Data validation & Protection ............................................................................................................................... 78 Chapter 6: Pivot Tables Reports & Pivot Chart Reports....................................................................................................... 80

Assignments – MS Excel 2010......................................................................................................................................................... 83 4. MS – PowerPoint 2010 ................................................................................................................................................................. 88 1. 2.

Introduction ........................................................................................................................................................................... 88 Chapter 1 : Introduction to MS - PowerPoint 2010 ............................................................................................................... 88

Assignments – MS PowerPoint 2010 .............................................................................................................................................. 94 5. MS – Access 2010 ........................................................................................................................................................................ 95 1. 2. 3. 4. 5. 6.

Introduction ........................................................................................................................................................................... 95 Chapter 1 : Database Basics ................................................................................................................................................ 95 Chapter 2 : Building and Customizing a Database ............................................................................................................... 96 Chapter 3 : Filtering, Sorting and creating relationships .................................................................................................... 110 Chapter 4 : Working with Queries and Reports .................................................................................................................. 113 Chapter 5 : Advances in Database Design and Maintenance ............................................................................................ 124

6. MS – Office Utilities ................................................................................................................................................................... 130 1. 2. 3.

Introduction ......................................................................................................................................................................... 130 Chapter 1: MS Outlook 2010 .............................................................................................................................................. 130 Chapter 2: MS - Calendar .................................................................................................................................................. 131

7. Accounting Package.................................................................................................................................................................. 135 1. 2. 3. 4. 5.

Introduction ......................................................................................................................................................................... 135 Chapter 2: Introduction to Tally.ERP 9 ............................................................................................................................... 135 Chapter 3: Voucher Entry in Tally. ERP9 ........................................................................................................................... 138 Chapter 4: Voucher Entry in Tally. ERP9 ........................................................................................................................... 142 Chapter 5: Financial Analysis Tools in Tally.ERP9 ............................................................................................................ 149

ITT Course Practice Manual

Page 3 of 149

The Institute of Chartered Accountants of India 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.

ITT Course Practice Manual

Page 4 of 149

1. Introduction to Practice Manual 1. Introduction 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. Amazon started its operations by selling Books. Today, it sells most goods and services that we require, even in India. The old book shops have disappeared or are fast disappearing. Kodak, the pioneer in photography is no more. Nokia, the largest seller of mobile phones at one time, has been acquired. If we do not keep pace with the high pace of technological development and resultant obsolescence, we are neither competitive nor effective and hence lose relevance. 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. ITT Course The ITT 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, Office Automation using MS-Office (MSWord, MS-Excel, MS-PowerPoint, Databases, Office Utilities), Software for Chartered Accountants in Practice, Accounting Package and Web technologies & e-Filing.

3. Practice Manual - Organisation This Practice Manual aims to bring greater Hands-On Practical Training (HOT) as a part of the ITT Course being conducted at the ITT Centres of the Institute. This Practice Manual provides practical Illustrations, Exercises, and Case Studies to be covered as a part of the ITT Course on an all India basis by Students. The ITT 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 ITT 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:

ITT Course Practice Manual

Page 5 of 149

Name Symbol Resource Study Module I SM1 Study Material for ITT Course Module I Study Module II SM2 Study Material for ITT Course Module II New New Newly added Exercise & Case Studies Summary As a part of our ITT Course study, we shall be covering the Exercises and Case Studies as detailed in the following table, in the respective topics. Sr. No. Unit 1 Unit 2 Unit 3 Unit 4 Unit 5 Unit 6

Unit Name Exercise MS – Word 2010 41 MS – Excel 2010 42 MS – PowerPoint 2010 7 Databases 20 MS Outlook 8 Accounting Package 16 Total 134

Assignments 15 12 5 ---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.

ITT Course Practice Manual

Page 6 of 149

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. Unit 2 1 2 3 4

Chapter Title MS – Word 2010 Introduction to MS - Word 2010 The Insert Tab Managing Documents Page Formatting, Printing and Mailing Total

Total 14 10 9 8 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.

ITT Course Practice Manual

Page 7 of 149

        

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.

ITT Course Practice Manual

Page 8 of 149

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. 4. 5. 6. 7.

Change the font of third point to Script MT Bold and font size to 16 Using Format Painter tool, copy the formatting from third point and use it on second point. Sort the list alphabetically in ascending order. Leave two blank lines after the list. Define a new multilevel list style (with three levels) as I

…………………………….. 1. ………………………… a. ..…………………

8. Now create a sample list as: I.

II.

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 Economic systems 1. Market economy 2. Mixed economy 3. Planned economy 4. Traditional economy

Solution:

ITT Course Practice Manual

Page 9 of 149

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. ITT Course Practice Manual

Page 10 of 149

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:

ITT Course Practice Manual

Page 11 of 149

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.

ITT Course Practice Manual

Page 12 of 149

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.)

ITT Course Practice Manual

Page 13 of 149

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: 1. Create and save a new word document as exercise1.10.docx 2. Enter the following text in the document. Input TextAccountancy 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.

ITT Course Practice Manual

Page 14 of 149

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. 2. 3. 4. 5. 6.

Insert a page break between the two topics, using keyboard shortcut.(Ctrl+Enter) Change the orientation of the text on the second page, to Landscape. Remove the space and insert an optional hyphen between the words Direct and Tax. Insert line numbers in front of each line in the document. Line numbers should restart from 1 in the second page. Insert a watermark with the text “ICAI” written diagonally. 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

ITT Course Practice Manual

Page 15 of 149

Solution: Step-1 Step-2 Step-3 Step-4

Select a word/line or paragraph in which you want to increase character spacing. Go to Home Ribbon and open font dialog Box through Launcher Select Advanced Tab 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 The Institute of Chartered Accountants of India Branch Name: -------------------------Address: - ------------------------------------------------------------------------------Phone: -----------------------------------E-mail: -------------------------------------

No. ICAI/ITT/Reg. No. Mr/ Ms. ---------------------Address: ------------------------Phone: -_______________

Dated: _____________________

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 ITT Centres and timings and availability of seats at the ITT Centre:Name:Venue:-

ITT Centre Details Start Date End Date

Timings

---------------------------------------------------------------------------------------------------------------------------------------------------------------

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

ITT Course Practice Manual

Page 16 of 149

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 ITT Course Students available overleaf” for strict compliance in letter and spirit, to avoid delays in securing ITT 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: 1. Create and save a new word document as exercise2.1.docx 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. StudentID 1 2 4 5

Name Marks Shivani 75 Raj 80 Rashmi 67 Roshni 84

4. Enter detail of one more student (Student ID – 3) between the second and fourth student StudentID 1 2 3 4 5

Name Marks Shivani 75 Raj 80 Amar 72 Rashmi 67 Roshni 84

5. Delete all the table borders leaving only the external table boundaries. 6. Delete the detail of second student (Student ID – 2). StudentID 1 3 4 5 ITT Course Practice Manual

Name Marks Shivani 75 Amar 72 Rashmi 67 Roshni 84 Page 17 of 149

7. Sort the table by marks in descending order. StudentID 5 1 3 4

Name Marks Roshni 84 Shivani 75 Amar 72 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. StudentID 5 1 3 4

Name Roshni Shivani Amar Rashmi

Marks 84 75 72 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. StudentID 5 Roshni 1 4

Name 84 1 Shivani Rashmi

Marks Amar 72 75 67

12. Apply a table style to the table. StudentID Name Marks 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 5 84

ITT Course Practice Manual

Marks Raj

Page 18 of 149

Roshni 1

2 80 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 09.00 - 10.00 10.00 - 11.00 11.00 - 12.00 12.00 - 13.00 13.00 - 14.00 14.00 - 15.00 15.00 - 16.00

Monday Maths Maths Geography History Lunch English French

Tuesday

Wednesday Study Science Science Science Lunch French French

Thursday

Friday

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:   3.

Move the classes from the column Monday and paste them to Tuesday. Move the classes from the Wednesday column to Thursday.

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 09.00 - 10.00 10.00 - 11.00 11.00 - 12.00 12.00 - 13.00 13.00 - 14.00 14.00 - 15.00 15.00 - 16.00

Monday English Geography French French Lunch Maths History

ITT Course Practice Manual

Tuesday Maths Maths Geography History Lunch English French

Wednesday History History Geography Maths Lunch Science English

Thursday Study Science Science Science Lunch French French

Friday History Geography Study English Lunch Science Science

Page 19 of 149

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: 1. 2. 3. 4. 5. 6. 7.

Create and save a new word document as exercise2.5.docx Download picture of a computer from the internet and save it on desktop. Insert the downloaded picture and resize it. Replace the picture with a new one downloaded from internet or taken from sample pictures. Insert any picture from the Clip Art gallery. Fill the picture with some colour and apply a 3-D effect to it. Insert a star shape from the gallery. Enter the following text in the shape

ITT Course Practice Manual

Page 20 of 149

8. 9.

• Clip Art • WordArt • SmartArt Delete the picture inserted from the Clip Art gallery. 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 Application Systems Analysts Application Programmers DA/DBA Quality Assurance Professionals Security Professionals Computer Operators Application Systems Development Manager Information Processing Manager IS Manager

Exercise 2.7: Adding picture

Reporting to Application Systems Development Manager Application Systems Development Manager Information Processing Manager Information Processing Manager Information Processing Manager Information Processing Manager IS Manager IS Manager Top Management / Board of Directors

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.

ITT Course Practice Manual

Page 21 of 149

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 Mm555 Mm600 Mm800 Mm766 Mm755

East 560 670 630 490 890

West 640 340 550 720 440

North South 840 750 980 860 330 730 200 180 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

ITT Course Practice Manual

Page 22 of 149

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-1: Create and save a new word document as exercise2.10.docx Step-2: Insert a text box. ITT Course Practice Manual

Page 23 of 149

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.

ITT Course Practice Manual

Page 24 of 149

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.

ITT Course Practice Manual

Page 25 of 149

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 electroacoustic 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. NoteThe 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 grammer 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.

ITT Course Practice Manual

Page 26 of 149

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:

ITT Course Practice Manual

Page 27 of 149

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 TextOnline 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:

ITT Course Practice Manual

Page 28 of 149

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: Step-1: Create and save a new word document as exercise3.5.docx Step-2: Enter the following text in the document 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:

ITT Course Practice Manual

Page 29 of 149

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-1: Create and save a new word document as exercise3.6.docx 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-1: Create and save a new word document as exercise3.7.docx Step-2: Create a new document theme (select theme colours, fonts and effects), save it with your name and use it in the document. Step-3: Enter the following text 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

ITT Course Practice Manual

Page 30 of 149

Top Margins Left Margins Gutter Gutter Position

2 cm 2 cm 0.5 cm 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. ITT Course Practice Manual

Page 31 of 149

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 eMails. Students are required to Mail Merge the notice of forthcoming ITT examination to the Students of the current batch of the Course. Solution: Step-1: Add the following contacts in Microsoft Office Outlook Name Mayur Kumar Vikas Arora Alok Gupta Anoj Khurana Ankur Sehgal Rishi Sinha

ITT Course Practice Manual

E-Mail Address [email protected] [email protected] [email protected] [email protected] [email protected] [email protected]

City Delhi Chandigarh Delhi Delhi Kolkata Bangalore

Page 32 of 149

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. MSWord 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 ITT Course Practice Manual

Page 33 of 149

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.

ITT Course Practice Manual

Page 34 of 149

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)

ITT Course Practice Manual

Page 35 of 149

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.

ITT Course Practice Manual

Page 36 of 149

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:

Employee ID Name

ABC Company Ltd. Payslip for the month of April-2016 1006 Dept. Information Technology Vikas Kumar Designation Asst. Manager

Earnings Basic Grade Pay Dearness Allowance HRA Transport Allowance Gross Earnings Net Pay

Amount (Rs) 15000 4500 20000 7500 3500 50500 46100

Deductions Employee PF TDS Employee Welfare

Amount (Rs) 3500 750 150

Total Deductions

4400

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.

ITT Course Practice Manual

Page 37 of 149

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. Employee ID Name

ABC Company Ltd. Payslip for the month of April - 2016 «Empid» Dept. «Name» Designation

Earnings Basic Grade Pay Dearness Allowance HRA Transport Allowance

Amount (Rs) «Basic» «GradePay»

Gross Earnings

«GrossEarnings»

Net Pay

«DA»

Deductions Employee PF TDS Employee Welfare

«Dept» «Designation» Amount (Rs) «PF» «TDS» «EmpWel»

«HRA» «TA» Gross Deductions «NetPay»

«GrossDeduction»

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.

ITT Course Practice Manual

Page 38 of 149

ABC Company Ltd. Payslip for the month of April-2016 Employee ID 1001 Dept. Name Anna Designation Earnings Basic Grade Pay Dearness Allowance HRA Transport Allowance Gross Earnings Net Pay

Amount (Rs) 20,500 5,400 15,540 6,475 2,590 50,505 42,197

Information Technology Manager

Deductions Employee PF TDS Employee Welfare

Amount (Rs) 3,108 5,051 150

Gross Deductions

8,309

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.

ITT Course Practice Manual

Page 39 of 149

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

ITT Course Practice Manual

Page 40 of 149

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.

ITT Course Practice Manual

Page 41 of 149

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.

ITT Course Practice Manual

Page 42 of 149

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: Designation Sales Supervisor Distribution Manager Business Manager Promotion supervisor Logistics supervisor

ITT Course Practice Manual

Reporting to Sales Director Sales Director Sales Director Sales Director Sales Director

Page 43 of 149

Designation Sales Engineer Sales Representative Distribution Executive Business Assistant Business Representative Distribution Executive Logistics staff Staffs Staffs

Reporting to Sales Supervisor Sales Supervisor Distribution Manager Distribution Manager Business Manager Promotion supervisor Logistics supervisor Distribution Executive 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.

ITT Course Practice Manual

Page 44 of 149

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.

ITT Course Practice Manual

Page 45 of 149

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 Sr. Unit 2 1 2 3 4 5 6

Chapter Title Total MS – Excel 2010 Introduction to MS Excel 2010 2 Cell Referencing Ranges and Functions 18 Working with Worksheets, Charts, Macros and Hyperlinks 6 Consolidation of Data and Data Analysis 12 Data Validation and Protection 2 PivotTables Reports and PivotCharts Reports 2 Total 42

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.

ITT Course Practice Manual

Page 46 of 149

CELL B4 B6 B7 B8 B9 B10 B11 B12 B13 B14 B15 B16 B17 B18 B19 B20 B21 B22 B23 B24 B25 B26 B27 B28 B29

TEXT INPUT SECTION Cash in Bank Petty Cash Marketable Securities Accounts Receivable Allowance for Bad Debts Inventory Office Supplies Prepaid Insurance Land Buildings Accumulated Depreciation—Buildings Warehouse Accumulated Depreciation—Warehouse Trucks Accumulated Depreciation—Trucks Office Equipment Accumulated Depreciation—Office Equipment Accounts Payable Notes Payable, Short-Term Interest Payable Income Tax Payable Liability for Payroll Taxes Notes Payable, Long-Term ABC, Capital

Now, widen Column C to 14 characters. Next, input the following numerical values in the appropriate cells. CELL C6 C7 C8 C9 C10 C11 C12 C13 C14 C15 C16 C17 C18 C19 C20 C21 C22

ITT Course Practice Manual

VALUE 7115.15 400 5000 24100 615 65000 1480 5135 10000 65000 9750 30000 5400 16000 9000 8700 3915

Page 47 of 149

CELL C23 C24 C25 C26 C27 C28 C29

VALUE 14190 25000 215 300 1800 70000 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. ITT Course Practice Manual

Page 48 of 149

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 F14 will be placed in F15. F18 E19 E20 E20 E21 E22

Copy C14 Copy C15 Copy C16 and Underline E20 F20 Using the Plus-Point Method, subtract E20 from E19. Copy C17 Copy C18 and Underline E22

ITT Course Practice Manual

Page 49 of 149

E22 E23 E24 F24 E25 E26 F26 F27 F28

F22 Using the Plus-Point Method, subtract E22 from E21. Copy C19 Copy C20 and Underline E24 Using the Plus-Point Method, subtract E24 from E23. Copy C21 Copy C22 and Underline E26 Using the Plus-Point Method, subtract E26 from E25 and Underline F26 Using AutoSum, add F18 through F26 and Underline F27 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 F34 F35 F36 F37 F38 F39 F42 F43 F44 F48 F49 F50

Particulars Copy C23 Copy C24 Copy C25 Copy C26 Copy C27 and Underline F38 Using AutoSum, add F34 through F38. Copy C28 and Underline F42 Copy F42 Underline F43 Using the Plus-Point Method, add F39 and F43 and Underline F44 Copy C29 and Underline F48 Copy F48 and Underline F49 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

ITT Course Practice Manual

Page 50 of 149

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 Solution: For above, use Exercise_1.2.xlsx file from MS-EXCEL folder.

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 ITT Course Practice Manual

Page 51 of 149

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 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?

ITT Course Practice Manual

Page 52 of 149

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. Month

Salary

January February March April May June July August September October November December Total Amount of Organisation at the End of Year Tax Rate:-

978489 449488 768048 917271 776714 382921 201863 730144 142381 900174 276381 375378

Tax rate 10% 20% 10% 30% 20% 30% 40% 20% 20% 40% 10% 20%

6899252

23%

Mixed Cell Reference Tax Column Tax Row Fix Output Fix Output 97848.9 97848.9 89897.6 44948.8 76804.8 76804.8 275181.3 91727.1 155342.8 77671.4 114876.3 38292.1 80745.2 20186.3 146028.8 73014.4 28476.2 14238.1 360069.6 90017.4 27638.1 27638.1 75075.6 37537.8 Correct

Tax Column Fix Output 146773.35 0 0 0 0 0 0 0 0 0 0 0

Tax Row Fix Output 146773.35 67423.2 115207.2 137590.65 116507.1 57438.15 30279.45 109521.6 21357.15 135026.1 41457.15 56306.7

Incorrect

Correct

Incorrect

15%

Exercise 2.2: Functions (Min, Max, Small, Large and If) 1. Create the following range names Range Name Range Budget C3:C22 Gross D3:D22 Profit E3:E22 Tickets Sold F3:F22 Tickets Price H3

New

2. Use the range names you have created to calculate the following values Profit - subtract Budget from Gross Tickets Sold - divide Gross by Ticket Price MIN and MAX of Profit and Tickets Sold 2nd MIN and 2nd MAX of Profit and Tickets Sold

Logical Operation If the profit was less than 100,000,000 then the movie is flop Otherwise the movie is a success Solution: For above, use Exercise_2.2.xlsx file from MS-EXCEL folder. Use of Functions (Min, Max, Small, Large and IF) Movie

Budget ($)

Spider Man 3 King Kong Superman Returns

258,000,000 207,000,000 204,000,000

ITT Course Practice Manual

World Gross ($) 887,436,184 553,080,025 391,081,192

Profit ($) 629,436,184 346,080,025 187,081,192

Tickets Sold

Flop or Not

177,487,237 Success 110,616,005 Success 78,216,238 Success

Average Ticket Price ($) 5

Page 53 of 149

Spider Man 2 Bajirao Mastani Jungle Book Wild Wild West FAN Water World Speed – 1 Polar Express Van Helsing Fast & Furious Shrek the Third Alexander Pearl Harbor Hulk Speed – 2 Mission Impossible Troy

200,000,000 200,000,000 180,000,000 175,000,000 175,000,000 175,000,000 170,000,000 170,000,000 170,000,000 160,000,000 160,000,000 155,000,000 151,500,000 150,000,000 150,000,000 150,000,000 150,000,000

784,024,485 1,835,400,000 748,806,957 217,700,000 120,698,890 264,246,220 433,058,296 296,596,043 300,150,546 733,012,359 181,674,817 167,297,191 450,500,000 892,213,036 822,828,538 397,501,348 497,298,577

Highest Lowest Second Highest Second Lowest

584,024,485 1,635,400,000 568,806,957 42,700,000 54,301,110 89,246,220 263,058,296 126,596,043 130,150,546 573,012,359 21,674,817 12,297,191 299,000,000 742,213,036 672,828,538 247,501,348 347,298,577

1,635,400,000 12,297,191 742,213,036 21,674,817

156,804,897 367,080,000 149,761,391 43,540,000 24,139,778 52,849,244 86,611,659 59,319,209 60,030,109 146,602,472 36,334,963 33,459,438 90,100,000 178,442,607 164,565,708 79,500,270 99,459,715

Success Success Success Flop Flop Flop Success Success Success Success Flop Flop Success Success Success Success Success

367,080,000 24,139,778 178,442,607 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) Solution: For above, use Exercise_2.3.xlsx file from MS-EXCEL folder.

ITT Course Practice Manual

Page 54 of 149

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.

Solution: For above, use Exercise_2.5.xlsx file from MS-EXCEL folder.

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,…..) Solution: For above, use Exercise_2.6.xlsx file from MS-EXCEL folder.

ITT Course Practice Manual

Page 55 of 149

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))

Solution: For above, use Exercise_2.7.xlsx file from MS-EXCEL folder.

ITT Course Practice Manual

Page 56 of 149

Exercise 2.8: Find the rank of an ITT 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 ITT qualified.  Grade is assigned as Average Marks Grade >=70 a >=60 b >=50 c <50 d  Use Rank () function for computing rank of ITT qualified students. If a student has failed, just write n/a. Solution: For above, use Exercise_2.8.xlsx file from MS-EXCEL folder.

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: Country Singapore Malaysia Philippines India Sri Lanka

Number of outlets counted % Increase in % Increase in 2002 2003 2001 2002 2003 81,000 83,000 86,000 42,289 45,900 49,898 43,212 43,532 40,233 2,321 4,673 7,900 3,799 4,234 9,676

Calculate the shown columns and highlight the countries where the trade has a significant increase of more than ITT Course Practice Manual

Page 57 of 149

25% in the year 2002 and 2003. Step 3: The advertisement revenue of leading newspapers in Mumbai: Brand Name Adidas Britannia Samsung MRF

Hindustan Times Indian Express The Hindu The Times of India 41,700 95,343 7,302 28,800 37,69,241 1,17,488 23,799 99,480 19,29,550 7,84,055 1,33,639 13,48,832 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) Solution: For above, use Exercise_2.10.xlsx file from MS-EXCEL folder.

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 House rent allowance Dearness allowance Commission Entertainment allowance Medical allowance City compensation allowance He paid house rent NSC, VIII issue Tax Deducted at source

Rs. 3,00,000 per annum Rs. 50,000 per annum 20% of basic salary 2% of basic salary as per contract Rs. 10,000 per annum Rs. 16,000 per annum Rs. 6,000 per annum Rs. 35,000. He contributes Rs. 35,000 in recognized provident fund, Subscription to Rs. 15,000. Rs. 2,500 per month

Solve the above problem using the following format: Name DOB Gender Salary Information

ITT Course Practice Manual

Hemant Bhatia 12-12-1991 M

Age

Page 58 of 149

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 Up to Rs 1,60,000 Rs. 1,60,000 – Rs.3,00,000 Rs. 3,00,000 – Rs. 5,00,000 Rs. 5,00,000 – Rs. 10,00,000 Above Rs. 10,00000

Income Tax rate Nil 10% of (Total Income –Rs. 1,60,000) Rs. 14,000 +20% of (Total Income –Rs. 3,00,000) Rs. 54,000 +30% of (Total Income –Rs. 5,00,000) Rs. 2,04,000 +30% of (Total Income -Rs10,00,000)

Nil

Surcharge Nil

Education Cess

Higher Education Cess Nil`

Nil

2% of Income Tax

1% of Income Tax

Nil

2% of Income Tax

1% of Income Tax

Nil

2% of Income Tax

1% of Income Tax

10% of Income

2% of (Income Tax + Surcharge

1% of (Income Tax + Surcharge

Solution: For above, use Exercise_2.11.xlsx file from MS-EXCEL folder.

ITT Course Practice Manual

Page 59 of 149

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: For above, use Exercise_2.12.xlsx file from MS-EXCEL folder. 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 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/Q1:-

Q3. Q4. Q5. Q6.

Write the Values of Tax without decimal in L Column? Find the Total Basic Salary in a J24 column according to the “EEMP”? Calculate the Values which are less than 0 according to the column M? 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

ITT Course Practice Manual

Page 60 of 149

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? Solution: For above, use Exercise_2.13.xlsx file from MS-EXCEL folder. Exercise 2.14: Annual Report

New

Create Company‘s Annual Report as given below Name Job Prumi Analyst Suman Officer Sushma System Admin Chayya Manager

Salary Joining Date 12000 31/12/99 12000 02/11/98 15000 03/10/97 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 Total monthly instalments Rate of interest EMI

ITT Course Practice Manual

Rs.13925000 48 11.25% p.a. =PMT (11.25%/12,48,13925000)

Page 61 of 149

Principal Interest

=PPMT(11.25%/12,(first instalment-cell reference),48,13925000) =IPMT(11.25%/12,(first instalment-cell reference),48,13925000)

Solution: For above, use Exercise_2.15.xlsx file from MS-EXCEL folder. Instalment 1 2 3 4 5 6 7 and so on 47 48

Due date for Payment Instalment (Rs) Principal (Rs) Interest (Rs) 1-Nov-07 3,61,592 2,31,045 1,30,547 1-Dec-07 3,61,592 2,33,211 1,28,381 1-Jan-08 3,61,592 2,35,397 1,26,194 1-Feb-08 3,61,592 2,37,604 1,23,988 1-Mar-08 3,61,592 2,39,832 1,21,760 1-Apr-08 3,61,592 2,42,080 1,19,512 1-May-08 3,61,592 2,44,350 1,17,242 1-Sep-11 1-Oct-11 Total

3,61,592 3,61,592 1,73,56,416

Exercise 2.16: Calculation of EMI, Maturity Amount

3,54,906 3,58,233 1,39,25,000

6,686 3,358 34,31,406 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: For above, use Exercise_2.16.xlsx file from MS-EXCEL folder.

Exercise 2.17: Sum if Function

New

Calculate the hours and cost using Sumif function according to the week. Solution: For above, use Exercise_2.17.xlsx file from MS-EXCEL folder. 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:

ITT Course Practice Manual

Page 62 of 149

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 Kunal Akanksha Kishore Akash Manish Sunil Mansi Sanjay Rajeev Saurabh Rajesh Akash Vishal Deepa

Roll No. 4 8 11 15 7 2 9 5 14 13 15 12 6 10

English Maths 64.00 100.00 64.00 86.00 65.00 96.00 65.00 56.00 94.00 42.00 68.00 35.00 21.00 76.00 44.00 88.00 55.00 67.00 45.00 87.00 55.00 85.00 29.00 29.00 84.00 46.00 35.00 66.00

Science 89.00 87.00 68.00 89.00 65.00 98.00 98.00 56.00 47.00 37.00 25.00 95.00 23.00 45.00

Solution: For above, use Exercise_3.1.xlsx file from MS-EXCEL folder.

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 ITT Course Practice Manual

Page 63 of 149

By using Exercise_3.2.xlsx file from MS-EXCEL folder perform following steps for output:Tick nothing is blank in a row. Solution: For above, use Exercise_3.2.xlsx file from MS-EXCEL folder.

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 ITT Course Practice Manual

Page 64 of 149

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

ITT Course Practice Manual

Page 65 of 149

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 Shampoo Face Cream Hair oil Toothpaste Bath Soaps Liquid Soaps

Jan’04 10,899 90,898 5,684 23,456 9,00,123 23,345

Feb’04 13,999 1,23,455 10,891 1,23,456 8,45,670 91,800

March’ 45,677 9,89,234 16,878 79,898 15,11,300 10,817

April’04 19,343 14,56,787 89,343 23,23,450 19,33,710 16,750

Solution: For above, use Exercise_3.3_F sheet in Exercise_3.3.xlsx file from MS-EXCEL folder.

ITT Course Practice Manual

Page 66 of 149

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 Solution: For above, use Exercise_3.4.xlsx file from MS-EXCEL folder. 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.

ITT Course Practice Manual

Page 67 of 149

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

ITT Course Practice Manual

Page 68 of 149

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.

ITT Course Practice Manual

Page 69 of 149

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? Solution: For above, use Exercise_4.1.xlsx file from MS-EXCEL folder.

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 Solution: For above, use Exercise_4.2.xlsx file from MS-EXCEL folder. 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

ITT Course Practice Manual

Page 70 of 149

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.

Solution: For above, use Exercise_4.3.xlsx file from MS-EXCEL folder. 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.

ITT Course Practice Manual

Page 71 of 149

Solution: For above, use Exercise_4.4.folder from MS-EXCEL folder. Coffee Milk Tea

Quarter 1 Quarter 2 Quarter 3 Quarter 4 10300 14830 14240 7330 8500 16500 16500 8500 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.

Solution: For above, use Exercise_4.5.xlsx file from MS-EXCEL folder. 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 Cups need to sell Subtotal Price per premium latte Cups need to sell Subtotal Price per premium mocha Cups need to sell Subtotal

ITT Course Practice Manual

Rs. 1.25 100 ? Rs. 2.00 175 ? Rs. 2.25 100 ?

T otal revenue Regular cups Premium cups T otal cups Constraints Max cups Max Premium Max mocha

? ? ? ? 500 350 125

Page 72 of 149

Solution: For above, use Exercise_4.6.xlsx file from MS-EXCEL folder.

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.

Cups need to sell

1.25

T otal revenue Rs. 700.00

100

Subtotal

Rs. 125.00

Price per premium latte

Rs.

Cups need to sell

Regular cups

150

2.00

Premium cups

350

175

T otal cups

500

Subtotal

Rs. 350.00

Price per premium mocha

Rs.

Cups need to sell Subtotal

Constraints

2.25

Max cups

500

100

Max Premium

350

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.

ITT Course Practice Manual

Page 73 of 149

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.

Solution: For above, use Exercise_4.7.xlsx file from MS-EXCEL folder.

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

Solution: For above, use Exercise_4.8.xlsx file from MS-EXCEL folder. 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

ITT Course Practice Manual

Page 74 of 149

Exercise 4.9: Lookup Functions

New

For the given below phone book data, implement the following queries Name A C F R T G 1 2 3 4 5 6 7 8 9 10 11 12

PHONEBOOK Phone City 12345678 City1 87654321 City2 12365487 City3 32145678 City4 52413876 City5 74185263 City6

Country Country1 Country2 Country3 Country4 Country5 Country6

Use lookup function to find out the city of F. Use lookup function to find out the country of R. Use Array option in lookup function to find out the country of R. Use Array option in lookup function to find out the phone number of T. Check if lookup function is case sensitive. e.g.: try to lookup the phone number of “A” and “a” Try to lookup the city of B (B not available in the list). Try to lookup the city of E (E not available in the list). Try to lookup the city of G (G not available in the list). Lookup the name of the person having the phone number 12345678 Use Array function in Lookup to look up the name of the person having the phone number 12345678 Lookup the name of the person having the phone number 12355678 (not available in the list). 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 C3:C8 = City

Vector B3:B8 = Phone Vector D3:D8 = Country

Array = Phonebook

Solution: For above, use Exercise_4.9.xlsx file from MS-EXCEL folder. 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.

ITT Course Practice Manual

Page 75 of 149

Solution: For above, use Exercise_4.10A.xlsx file from MS-EXCEL folder. Calculation using V Lookup Stock List Code Item Price No. in Stock A34 Armchair 89.99 5 A16 Stool 21 7 C39 Sofa 312 0 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

Enquiry System Code A18 Item Bookcase Price 112.5 In stock 40

b) Using V Lookup function Part No. 111 222 333 444 555 666 777

Units of Measurement Nos Kgs Meters Kgs Kgs Nos Nos

Rate 6500 80 20 300 750 55 90

Category A C C B B C C

Location Reorder Level Reorder Qty Supplier Stores 1 2000 100 A Stores 2 500 75 H Stores 3 500 60 Y Stores 1 900 40 B Stores 1 800 40 C Stores 2 500 50 E Stores 1 500 30 G

From the above given Part List data prepare a purchase order sheet as shown in the output.

Hints 1. 2. 3.

Name the vector name Part List for the data array. Part No: Set the validation condition as shown in the Data Validation dialog box. 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

ITT Course Practice Manual

Page 76 of 149

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 1 2 3 4 5 6 7 8 9 10

Name Module-1 Module-2 Project Online Test Total Percentage Grade Remark Anna 25 25 20 180 ? ? ? ? Antonio 30 25 65 180 ? ? ? ? Thomas 40 40 70 200 ? ? ? ? Christina 50 40 90 220 ? ? ? ? Martin 40 40 90 280 ? ? ? ? Francisco 30 30 70 270 ? ? ? ? Ming-Yang 45 40 85 280 ? ? ? ? Elizabeth 50 50 100 300 ? ? ? ? Sven 30 20 40 160 ? ? ? ? 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)

ITT Course Practice Manual

Page 77 of 149

Solution: For above, use Exercise_4.11A.xlsx file from MS-EXCEL folder.

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)

Solution: For above, use Exercise_4.11B.xlsx file from MS-EXCEL folder.

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.

ITT Course Practice Manual

Page 78 of 149

Name Course Course Tracker Java DotNet CSharp Adobe         

Range Address A1:A4 B1:B3 C1:C5 D1:D3 E1 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.

Solution: For above, use Exercise_5.1A.xlsx file from MS-EXCEL folder.

Select the Type of Course Select the Course Track Select the specific course Total Fees to be Deposited

Java 2 Years JSP 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 o o o o

Training Programme not to exceed Rs.100000 New Hardware not to exceed Rs.200000 New Software not to exceed Rs.325000 Magazines & Periodicals not to exceed Rs.5000 Conveyance not to exceed Rs.50000

ITT Course Practice Manual

Page 79 of 149

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

Solution: For above, use Exercise_5.1B.xlsx file from MS-EXCEL folder. 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 MSExcel 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.

Solution: For above, use Exercise_6.1.xlsx file from MS-EXCEL folder.

ITT Course Practice Manual

Page 80 of 149



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)

ITT Course Practice Manual

Page 81 of 149

b)

c)

ITT Course Practice Manual

Page 82 of 149

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 42461 42463 42464 42465 42466 42467 42468 42470 42471 42472 42473 42474 42475 42477 42478 42479 42480 42481 42482 42484 42485 42486 42487 42488 42489

 

Saturday 42462 42469 42476 42483 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 Aakash Arun Bharat Chetan Deep Geeta Heena Indu Kabir Kunal Meera Neha Pooja Sonia  

Last Name Dixit Joshi Shetty Dalvi Chhaya Darekar Godbole Shah Vora Shah Lalwani Joshi Gokhale Sasan

Upper ? ? ? ? ? ? ? ? ? ? ? ? ? ?

Lower ? ? ? ? ? ? ? ? ? ? ? ? ? ?

Proper ? ? ? ? ? ? ? ? ? ? ? ? ? ?

Concatenate ? ? ? ? ? ? ? ? ? ? ? ? ? ?

Left ? ? ? ? ? ? ? ? ? ? ? ? ? ?

Right ? ? ? ? ? ? ? ? ? ? ? ? ? ?

Mid ? ? ? ? ? ? ? ? ? ? ? ? ? ?

E-mail ? ? ? ? ? ? ? ? ? ? ? ? ? ?

Upper according to the First name Lower according to the First name

ITT Course Practice Manual

Page 83 of 149

     

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: Years Year 1 Year 2 Year 3 Year 4 IRR

Rs. Rs. Rs. Rs.

IRR Input 1 -5,000,000.00 1,300,000.00 1,900,000.00 2,600,000.00 ?

Rs. Rs. Rs. Rs.

Input 2 -9,000,000.00 3,600,000.00 3,600,000.00 3,600,000.00 ?

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.

Revenue

ITT Course Practice Manual

Monthly Income Statement Units Sold Price per Unit

1,200 $100 ? Page 84 of 149

Units Produced Material Cost per Unit Total Material Cost Manufacturing Cost per Unit Manufacturing Expenses Total Variable Expense Leasing Salary and benefits Advertising Administration Total Fixed Expense Total Expenses Operating Income

Variable Expenses

Fixed Expenses Summary

Changing Cell Unit_Sold Price_per_unit Material_Cost_per_Unit

Low Cost 1400 80 22

? $26 ? $15 ? ? $5,000 $45,000 $5,000 $2,500 ? ? ?

Competitive 1300 90 24

High Cost 1200 100 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. Models Profit per Unit Production Level Widget Components A B C D E F G

Red Blue Green Yellow Orange Widget Widget Widget Widget Widget $95 $88 $60 $42 $30 0

0

17

28

61 Total Profit

- Widget Components Required for Each Model 5 3 6 8 12 10 15

0 12 5 3 8 2 0

6 3 5 2 1 2 5

3 5 3 3 0 0 0

?

Parts on Hand 0 0 0 1 3 2 0

200 200 200 200 200 200 200

Parts Required ? ? ? ? ? ? ?

Assignment 11: VLookup Functions Prepare sheet1 by using following data and rename the sheet as Voters.

ITT Course Practice Manual

Page 85 of 149

Prepare sheet2 by using following data and rename the sheet as Party Voters. PARTY CODE 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

NAME Green Reform Whig Islamic Political Party of India Rock & Roll Natural Law Middle Class Party Humanist Pragmatic Congrous Party Parliament Party United Conscious Builders of the Dream Party The Egalitarian Party The Humanitarian Party Hindostan University Party God, Truth & Love Party Superhappy Party Working Families Party Democratic Republican Decline to State India Independent Citizen Party Communist Conservative Environmentalist Ind. Progressive Liberal Peace & Freedom Prohibition New Economy Socialist Socialist Labor Pot Party Libertarian India National Socialist Poor People’s Party Free National Constitution Party Vision Puritan Federal Misc.

Now show the Political Parties in a Voter sheet using Vlookup Function.

ITT Course Practice Manual

Page 86 of 149

Assignment 12: Data Validation and Conditional Formatting Prepare file by using the following data Sales person Ram Prasad Shyam Choubey Ankit Jain Manoj Kumar Gauri Shankar Krishan Jain Pinki Kumari Seema Manorama Das Bally Bablu Ankit Anup Shyam Bang

Region North East South East North West South West West South South North South West

Account 29386 74830 90099 74830 82853 72949 90044 82853 72949 55223 10354 50192 27589 67275

Order Amount ₹ 925.00 ₹ 875.00 ₹ 500.00 ₹ 350.00 ₹ 400.00 ₹ 850.00 ₹ 1,500.00 ₹ 550.00 ₹ 400.00 ₹ 235.00 ₹ 850.00 ₹ 600.00 ₹ 250.00 ₹ 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.

ITT Course Practice Manual

Page 87 of 149

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: Sr. Unit 1 Total

Chapter Title MS – Powerpoint 2010 MS - PowerPoint 2010

Total 7 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: “” 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

ITT Course Practice Manual

Page 88 of 149

 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.

ITT Course Practice Manual

Page 89 of 149

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)

ITT Course Practice Manual

Page 90 of 149

 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

ITT Course Practice Manual

Page 91 of 149

 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 Rename as “Output Devices Section”  Select “Storage Devices” slide and Rename as “Storage Devices Section”

Normal View

Slide Sorter View

 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.

ITT Course Practice Manual

Page 92 of 149

 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.

ITT Course Practice Manual

Page 93 of 149

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.

ITT Course Practice Manual

Page 94 of 149

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. Sr. Unit 1 2 3 4 5

Chapter Title MS-Access

Total

Database Basics Building & Customizing a Database Filtering, Sorting and Creating Relationship Working with Queries and Reports Advances in Database Design & Maintenance Total

1 8 4 2 5 20

2. Chapter 1 : Database Basics 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 Catalogue Number Title Author Copyright Date Publications

Type AutoNumber Text Text Date/Time Text

Field Size Long Integer 70 25 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.

ITT Course Practice Manual

Page 95 of 149

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 : Building and Customizing a Database 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 Field Name Cust_id Cust_Name Address City State Pincode

Type Number Text Text Text Text Number

Field Size Integer (Primary Key) 30 25 15 15 Long Integer

Transactions Table Field Name Trans_Id Cust_Id Transaction_Type Date of Transaction Time of Transaction Transaction Amount

ITT Course Practice Manual

Type AutoNumber Number Text Date/Time Time Number

Field Size Long Integer (Primary Key) Integer 7 Long Integer(4 decimal places)

Page 96 of 149

Accounts Table Field Name Cust_Id Account Number Account Type OpeningDate Balance

Type Number Number Text Date/Time Number

Field Size Integer Long Integer (Primary Key) 7 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

ITT Course Practice Manual

Page 97 of 149

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.

ITT Course Practice Manual

Page 98 of 149

Field Name Centre_Code Centre_Name Centre_Region POU_Address Student_ID Batch_Start_Date Batch_Completion_Date Certificate_Issue_Date

Type Text Text Text Text Number Date/Time Date/Time Date/Time

Field Size 5 15 5 50 Long Integer Medium Date Medium Date Medium Date

ITT Centre

Field Name Student_ID Scheme_Enrol FirstName LastName Gender DOB Father_Name Permanent_Address Mailing_Address Landline_Number Mobile_Number Email

Type Numb er Text Text Text Text Date/ Time Text Text Text Text Text Text

Field Size Long Integer (Primary Key) 15 25 25 5 Medium Date 30 200 100 30 10 30

ICAI_Student Table

Field Name Student_ID Module1 Module2 Project Attendance OLT

Type Number Number Number Number Number Number

Field Size Long Integer Long Integer Long Integer Long Integer Long Integer Long Integer

Marksheet Exercise 2.3: Create a Form for ITT_Students.

New

Solution: For above use ITT Students Database from MS Access folder. Select the table ICAI_Student then click on Create and select “Form” from Forms Group as shown in the below figure.

ITT Course Practice Manual

Page 99 of 149

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 Total Marks Obtained Percentage in Aggregate Result Status

Type Number Number 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. Solution: For above use ITT Students Database from MS Access folder. 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.

ITT Course Practice Manual

Page 100 of 149

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"))))

ITT Course Practice Manual

Page 101 of 149

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.

ITT Course Practice Manual

Page 102 of 149

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

ITT Course Practice Manual

Page 103 of 149

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

ITT Course Practice Manual

Page 104 of 149

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.

ITT Course Practice Manual

Page 105 of 149

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.

ITT Course Practice Manual

Page 106 of 149

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

ITT Course Practice Manual

Form View

Page 107 of 149

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.

ITT Course Practice Manual

Page 108 of 149

Exercise 2.8: Create Reports for ITT Students database (ICAI_Students, ITT Center, Marksheet table)

New

Solution: For above use ITT Students Database from MS Access folder. 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.

ITT Course Practice Manual

Page 109 of 149

4. Chapter 3 : Filtering, Sorting and creating relationships Exercise 3.1: Apply Filter

New

In this exercise students are requested to apply the filter on Salary table with Basic Salary >=Rs. 28000 Solution: For above please refer to Employee database file. Step-1:Select and open Salary Table. Step-2:Now select Basic Pay column and then click on Filter Greater Than option as shown in below figure.

button. Then select Number Filters and click on

Step-3: See the result after applying the desired filter.

Exercise 3.2: Sort the Records

New

In this exercise students are requested to sort the branches in ascending order of their inauguration date. ITT Course Practice Manual

Page 110 of 149

Solution: For above please refer to Employee database file. Step-1:Select and open Branch Table. Step-2:Now select “Branch_Inaugration_Date” column and then click on Home button. Then select Ascending option.

Step-3: See the result as shown in below figure. Before Sorting

After Sorting

Exercise 3.3: Hide the Records

New

In this exercise students are requested to hide employee details such as Date of Joining, Bank Account No & Passport No. (Please refer to Org_Employees_Detail) Solution: For above please refer to Employee database file. Step-1: Select and open Org_Employees_Detail table. Step-2: Now select “emp_DOJ, Emp_Bank_Acc_No & Emp_Passport_No” column and then click on Home button. Then select More  Hide Fields from Record group.

Step-3: See the result as shown in below figure. Before Hiding Data

ITT Course Practice Manual

After Hiding Data

Page 111 of 149

Exercise 3.4: Create Relationship

New

In this exercise students are requested to create the relationship between Person Table and Drivers Licence table from sample database file. Field Name Person_id Title First_Name Last_Name

Type Number Text Text Text

Field Size Integer (Primary Key) 10 25 25

Field Name Type Field Size Person_id Number Integer (Primary Key) License_No Text 25 Issue_date Date/Time Expiry_date Date/Time

Solution: For above please refer to Sample database file. Step 1: Open Sample Database file. Now click on Database Tools  Relationships

Step 2: From Show Table add both the table for creating relationship.

Step 3: Now from Person Table drag field Person_id onto Drivers_License table Person_id field. After that in Edit Relationship window select checkboxes “Enforce Referential Integrity, Cascade Update Related Fields & Cascade Delete Related Records” and see the result as shown in below figure.

ITT Course Practice Manual

Page 112 of 149

5. Chapter 4 : Working with Queries and Reports Exercise 4.1: Create Queries as given in below table. Sr.

Type of Query

Query Name/Mode Query - 1 (Using Simple Query Wizard)

A

Select Query

B

Select Query

C

Crosstab Query Action Query

D

Append Query

Query-4

E

Delete Query

Query-5

F

Make Table Query

Query-6

G

Update Query

Query-7

Query-2 (Using Query Design) Query-3/Using Query-2

New To Display/Criteria Student’s Registration Number, First Name, Last Name, Scheme Enrolled In, Centre Code, Centre Name, Centre Region, Centre Address. Centre Name, Scheme Enrolled, Result Using Crosstab query display centre wise Result. Using Append Query create a Backup table of ICAI_Students records Using Delete Query delete all records of CPT scheme students form BackupTable. Using Make Table Query create new table for students details with their respective centers. Using Update Query for Delhi-Gazipur students.instead of 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.

ITT Course Practice Manual

Page 113 of 149

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 Fig.

ITT Course Practice Manual

Page 114 of 149

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.

ITT Course Practice Manual

Page 115 of 149

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.

ITT Course Practice Manual

Page 116 of 149

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.

ITT Course Practice Manual

Page 117 of 149

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.

ITT Course Practice Manual

Page 118 of 149

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.

ITT Course Practice Manual

Page 119 of 149

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.

ITT Course Practice Manual

Page 120 of 149

Step-4: Close and save the query and check the result in New1 Table.

Exercise 4.2: Create Students Details Report.

New

Solution: For above use ITT Students Database from MS Access folder. 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.

ITT Course Practice Manual

Page 121 of 149

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.

ITT Course Practice Manual

Page 122 of 149

Step-6: Type the Report Name as “Students Details”. Click Finish.

The reultant Report will be displayed as below

ITT Course Practice Manual

Page 123 of 149

6. Chapter 5 : Advances in Database Design and Maintenance Exercise 5.1: Object Dependency

New

In this exercise students are requested to find object dependency according to given below Salary table. Solution: For above please refer to Employee database file. Click Database Tools  Object Dependency

Exercise 5.2: Database Documenter

New

In this exercise students are requested to document all the tables from Employee database. Solution: For above please refer to Employee database file. Step 1: Click Database Tools  Database Documenter

Step 2: In Documenter window select Salary Table and click Ok. Step 3: It will display the result for salary table as shown in below figure. Step 4: In the same way student may select all other tables from the Employee database in the Documenter window and check the result.

ITT Course Practice Manual

Page 124 of 149

Step 5: Now we use Current Database Tab in the Documenter window and click on Relationship option. Click Ok.

Step 6: It will display the result as shown in below figure.

ITT Course Practice Manual

Page 125 of 149

Exercise 5.3: Analyse Performance

New

In this exercise students are requested to analyse the performance of relationship from Employee database. Solution: For above please refer to Employee database file. Step 1: Click Database Tools  Analyze Performance

Step 2: In Performance Analyzer window, Click on Current Database Tab, then select Relationships option. Click Ok. Step 3: In Performance Analyzer window, now it will display the result as shown in below figure.

Exercise 5.4: Exporting the data into other format

New

In this exercise students are requested to export the salary details of all the employees in excel format. Solution: For above please refer Salary table from Employee database file. Step 1: Click External Data  select Excel from Export group. Step 2: In Export – Excel Spreadsheet window specify the destination file name and format. From specify Export Option select “Export Data with Formatting and Layout”. Click Ok.

ITT Course Practice Manual

Page 126 of 149

Step 3: If you want to save the exporting steps then click on Save Export steps. Click Close.

ITT Course Practice Manual

Page 127 of 149

Exercise 5.5: Use of SQL Commands

New

In this exercise students are requested to calculate the gross salary of all the employees by using SQL view in Query Design mode. Solution: For above please refer Emp_Personel_Details and Salary table from Employee database file. Step 1: Click Create and then select Query Design.

Step 2: In Query Design select Emp_Personel_Details and Salary table.

Step 2: Change the view to SQL view and then type the below code in SQL Editor window. SELECT Emp_Personel_Details.Emp_Id, Emp_Personel_Details.emp_firstname, Emp_Personel_Details.emp_lastname, [Salary]![basic_pay]+[Salary]![HRA]+[Salary]![DA]+[Salary]![TA]+[Salary]![Children_Education_Allowance] AS Gross_Salary FROM Emp_Personel_Details INNER JOIN Salary ON Emp_Personel_Details.Emp_Id = Salary.employee_id; ITT Course Practice Manual

Page 128 of 149

After writing above code then click on Run

ITT Course Practice Manual

button and verify the result as shown in below figure.

Page 129 of 149

6. MS – Office Utilities 1. Introduction Outlook is an E-mail software program by Microsoft that enables users to send and receive e-mail on their computer. Sr. Unit 1 2

Chapter Title MS-Office Utilities MS-Outlook 2010 MS-Calendar

Exercise

Total 4 4

Total

4 4 8

2. Chapter 1: MS Outlook 2010 Exercise 1.1: Use Microsoft Outlook to add / create email account with creating contacts  

Configure your email account by using Add Account option. Create the following contacts:Sr. No. Name E-mail Id 1 CA. Rajat Kapoor [email protected] 2 Dr. Rahul Jain [email protected] 3 Mr. Arun Tripathi [email protected] 4 Mr. Raj Kumar [email protected] 5 Mr. Varun Jain [email protected] 6 Ms. Beenita Chugh [email protected] 7 Ms. Charu Verma [email protected] 8 Ms. Jyoti Arora [email protected] 9 Ms. Mona Sharma [email protected] 10 Ms. Teena Kapoor [email protected]

New

Contact No 1236478904 2365478902 3654789024 5678941235 3418520963 3420963741 3698520147 2587410369 1447856930 1275632140

Exercise 1.2: Compose an email for schedule a meeting.

New

Write the following text:Subject: Team Meeting Tomorrow at 10am Dear All As time is tight, I would like to schedule a meeting tomorrow morning at 10am in the Board Room to understand more about the status of our participation in the upcoming I.T. Expo. I apologies for the short notice. However, could you please clear your schedule and make yourself available for the meeting? Thank you in advance. I look forward to seeing you all tomorrow. Regards < Your Name> Add the following members and send the mail:Mr. Raj Kumar, Ms. Charu Verma, Ms. Mona Sharma, Ms. Teena Kapoor, Mr. Varun Jain, Mr. Arun Tripathi

ITT Course Practice Manual

Page 130 of 149

Exercise 1.3: Create a new task    

New

Create a new task on “ITT Project” Provide the starting and ending date to task. Set High Importance to task. Save and close your task.

Exercise 1.4: Add signature line to your E-mail.

New

Create your signature line. File menu Options  Mail Signatures.

3. Chapter 2: MS - Calendar Exercise 2.1: Create a new personal calendar and view calendars side by side by side or overlaid

New

1. Chose calendar option Manage calendar groupOpen Calendar Create new blank calendar.

ITT Course Practice Manual

Page 131 of 149

2. Provide name to your calendar. 3. Add new calendar and view it side-by-side. Solution:

Exercise 2.2: Setup Appointment and share it with others

New

1. Go to Calendar option Choose New Appointment command

2. Set the appointment for coming Monday with CA. Rajat Kapoor regarding Budget in Conference Hall

ITT Course Practice Manual

Page 132 of 149

3. Save your appointment. 4. Forward this appointment to CA. Rajat Kapoor. 5. Send this appointment to Ms. Jyoti Arora also; add her e-mail id in BCC (from Options Tab). Solution:

Exercise 2.3: Share your calendar through E-mail.

New

1. Go to Calendar Share Group E-mail Calendar. 2. Add the following recipientsSr. No. Name 1. Dr. Rahul Jain 2. Mr. Arun Tripathi 3. Mr. Varun Jain 4. Ms. Beenita Chugh 5. Ms. Charu Verma Solution:

Exercise 2.4: Share your calendar event through SMS

New

1. Create a new appointment and write the following text:CIRCULAR A farewell function in honour of Ms. Rama Sharma, Assistant Secretary on her retirement from the services of the Institute is scheduled to be held on 31st May, 2016 at 4:30 PM at Auditorium, ITO office, New Delhi. All are requested to attend the function. 2. Save and close the appointment. ITT Course Practice Manual

Page 133 of 149

3. Select your appointment from the calendar. 4. Forward this as text message to the following recipients:Sr. No. 1 2 3 4 5

Name Ms. Teena Kapoor Ms. Beenita Chugh Ms. Mona Sharma Mr. Varun Jain Mr. Raj Kumar

E-mail Id [email protected] [email protected] [email protected] [email protected] [email protected]

Contact No 1275632140 3420963741 1447856930 3418520963 7895641235

Solution:

Note: For sending this SMS first you have to configure your account in outlook.

ITT Course Practice Manual

Page 134 of 149

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. Sr. Unit 7 1 2 3 4 5 Total

Chapter Title Total Accounting Package Principles of Accounting -Introduction to Tally.ERP 9 3 Voucher Entry in Tally.ERP 9 4 Generating Reports in Tally.ERP 9 8 Financial Analysis Tools in Tally.ERP 9 1 16

2. Chapter 2: Introduction to Tally.ERP 9 Exercise 2.1: Payroll Exercise

New

Create the following Employee Masters: Employee Name Ajay Vijay Manish Ashu Gagan Murari

Department Administration Sales Purchase Research and Development Purchase Sales

Date of Joining 1-4-2016 1-5-2016 1-5-2016 1-6-2016 1-4-2016 1-6-2016

Note: - Fill the General Info, Payment Detail and Statutory Detail of overall Employees. Create the following Units Name Type Symbol Hour Simple Hrs Minutes Simple Mins Hrs of 60 Mins Compound Hrs of 60 mins

Create the following Attendance Types Name Present Absent Overtime

Under Primary Primary Primary

Attendance/ Production Type Attendance/ Leave with pay Leave without pay Production

Period Type Days Days Hrs of 60 Mins

Create the following Pay Heads:-

ITT Course Practice Manual

Page 135 of 149

Pay Head Name

Pay Head Type

Basic Pay DA House Rent Allowance Conveyance Overtime Pay Variable Pay Employees PF Contribution @12%

Under

Earnings for Employees Earnings for Employees Earnings for Employees Earnings for Employees Earnings for Employees Earnings for Employees Employees Statutory Deductions

Indirect Expenses Indirect Expenses Indirect Expenses Indirect Expenses Indirect Expenses Indirect Expenses Current Liabilities

Affect Net Salary

Name to Appear in Pay Slip

Use for Gratuity

Yes

Basic

Yes

Yes

DA

No

Yes

HRA

No

Yes

Conveyance

No

Yes

Overtime

No

Yes

Variable Pay

No

Yes

EPF @ 12%

----------

Calculation Period/ Production Type

Calculation Type On Attendance As Computed Value As Computed Value

Months Current earning Total on Specified formula

Flat Rate

Months

On Production As User Defined Value As Computed Value

Overtime --------on Specified formula

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 Ajay Vijay Manish Ashu Gagan Murari

Pay Head Basic Basic Basic Basic Basic Basic

Rate Effective Date 1000 1-4-2016 2000 1-5-2016 1500 1-5-2016 3000 1-6-2016 1200 1-4-2016 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 Present Days

ITT Course Practice Manual

Vijay 26

Ajay 22

Ashu 15

Murari 17

Page 136 of 149

Attendance/ Production Types Overtime hours

Vijay 900 Min (15hr x 60min)

Ajay

Ashu 720 Min (12hr x 60min)

Murari

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 Date

Party

Purchase Order Processing Khare Khote & 01.04.16 Co. Kavita Sudha & 02.04.16 Co. Bhatia Saxena & 02.04.16 Co. Receipt Processing Khare Khote & 01.05.16 Co. Kavita Sudha & 02.05.16 Co. Bhatia Saxena & 01.06.16 Co. Purchase Khare Khote & 01.05.16 Co. Kavita Sudha & 02.05.16 Co. Sales Order Processing Arun Manoharan 01.04.16 & Co. Tanker & 02.04.16 Paniwala 02.04.16

Gupta & Gupta

Delivery Processing Arun Manoharan 01.05.16 & Co. Tanker & 02.05.16 Paniwala 01.06.16 Gupta & Gupta Sale Arun Manoharan 01.05.16 & Co. Tanker & 02.05.16 Paniwala

ITT Course Practice Manual

Order Processing Exercise Order Particulars Stock Item Due on Order placed for purchases Order placed for purchases Order placed for purchases

New Qty

Rate

Value

01.05.16

Onida 21" TV

50

10,000

500,000

02.05.16

LG 300 Ltr. Fridge

40

13,000

520,000

02.05.16

Videocon 5 Ltr. Washing Machine

30

15,000

450,000

Goods Received

Onida 21" TV

50

10,000

500,000

Goods Received

LG 300 Ltr. Fridge

15

13,000

195,000

Goods Received

Videocon 5 Ltr. Washing Machine

18

13,000

234,000

Purchases recorded

Onida 21" TV

50

10,000

500,000

Purchases recorded

LG 300 Ltr. Fridge

15

13,000

195,000

01.05.16

Onida 21" TV

30

12,000

360,000

02.05.16

LG 300 Ltr. Fridge

25

14,500

362,500

02.05.16

Videocon 5 Ltr. Washing Machine

20

16,500

330,000

Goods delivered

Onida 21" TV

30

12,000

360,000

Goods delivered

LG 300 Ltr. Fridge

15

14,500

217,500

Goods delivered

Videocon 5 Ltr. Washing Machine

5

14,500

72,500

Onida 21" TV

30

12,000

360,000

LG 300 Ltr. Fridge

15

14,500

217,500

Order recorded for sales Order recorded for sales Order recorded for sales

Sales Invoice Raised Sales Invoice Raised

Page 137 of 149

Output:Balance 1379000

Sheet

Total:-

Closing 729000

Stock:-

Purchase 800000

Order

Outstanding:-

Sales Order 392500

Outstanding:-

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 Employee

Sr.

Designation

1 Aditya, C.A.

Finance Manager

2 Shahrukh

Accounts Manager

3 Saif

Accounts Manager

4 5 6 7 8 9

Accounts Executive Accounts Executive Accounts Executive Accounts Executive Data Entry Operator Data Entry Operator

Rani Priti Bipasha Kareena Jony Rajpal

Responsibilities Overall responsibility of accounts and finance function, reporting to top management, statutory compliances, etc. Getting data entered by accounts executive and operators, compilation of data, 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 supervision over operators, Preparation - of VAT, TDS, Service Tax Challans and Returns, Providing information to Finance Manager. Data Entry, Checking of data, Providing information to Accounts Manager. Data Entry, Checking of data, Providing information to Accounts Manager.Data Entry, Checking of data, Providing information to Accounts Manager,Data Entry, Checking of data, Providing information to Accounts Manager.Data Entry 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. 1 2 3 4 5 6 7 8 9 10 11

Date 01-04-15 02-04-15 01-05-15 01-06-15 02-06-15 01-07-15 02-07-15 01-08-15 02-08-15 31-08-15 01-09-15

Particulars Salary paid by cash Rs. 1,000 Cash Sales for Rs. 1,000 Tea 200 kgs @ Rs. 50.00 Cash drawn from IB Cheque No. 031267 Rs. 75,000/Cash deposited in CB Rs. 20,000/- using 1000 Rs Currency. Payment through Central Bank Cheque No. 609606 for Auditor fees Rs. 1,000/Payment through bank [IB] Cheque No. 0314195 for purchase of Sugar SS 50 Kg’s @ Rs.125 Bank Pay [IB] Cheque No. 0314195 Sugar-SS 50 Kg’s @ Rs. 125 Cheque deposited in IB for sales of Tea 400 kg’s @ Rs. 50 Cash Sales of Tea 200 kg’s @ Rs.50 Cash Sales of Ghee 100 kgs @ Rs. 2600.50 Cash received as ‘Commission’ Rs 1500

ITT Course Practice Manual

Page 138 of 149

Sr. 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40

Date 02-09-15 01-10-15 02-10-15 31-10-15 01-11-15 01-11-15 02-11-15 01-12-15 02-12-15 02-12-15 31-12-15 31-12-15 31-12-15 01-01-16 01-01-16 02-01-16 31-01-16 01-02-16 01-02-16 01-02-16 02-02-16 02-02-16 01-03-16 01-03-16 01-03-16 01-03-16 02-03-16 02-03-16 02-03-16

41

31-03-16

Particulars Cash sales of Sugar-SS 50 Kg’s @ Rs. 125 & Ghee 150 kg’s @ Rs. 2600.50 Enter the details here as a sales order: Vimal Nath & Co. likes to order Ghee-S 500 kg’s @ Rs. 3200. Cash received as ‘Commission’ Rs 2000 Do invoice entry for the sales order dated 01/10/2015 Cash sales of Ghee-S 200 kgs @ Rs. 3200 Enter the following as sales order: Alpha Industries would like to order Ghee 100 kg’s @ Rs. 2600.50 Do invoice entry for the sales order Purchase by Mehta Industries Sugar-SS 50 Kg’s @ Rs.125 Raise a credit note for the same Purchase by Lal Agencies Ghee 150 kg’s @ Rs. 2600.50 Prepare credit note for rejection of Ghee 50 kg’s @ Rs. 2600.50 by Lal Agencies Sales to Alpha Industries Ghee 300 kg’s @ Rs. 2500.50 Sales to Vimal Nath & Co. Ghee 600 kg’s @ Rs. 2500.70 Cash receipt of rent Rs. 1,000 Cash receipt of interest on investment Rs. 1,500 Sales to Vimal Nath & Co. Ghee 300 kg’s @ Rs. 2600.50; Sugar- SS 50 Kg’s @ Rs.125 Sales to Alpha Industries Ghee 150 kg’s @ Rs. 2600.50 First purchase to Abha Industries 3,000 kgs Ghee @ Rs. 1500 Credit note for Vimal Nath Ghee 400 kg’s @ Rs.2600.50 Gave Purchase order to Bimanlal Agencies Sugar-SS 50 Kg’s @ Rs. 110 Sales to Acrobat 1,50 kgs Ghee Rs.2550 Mehta Industries gave Purchase order of Sugar-SS 50 Kg’s @ Rs. 125 Purchase from Bimanlal Agencies Ghee-S 800 kg’s @ Rs.1500.50 Purchase by Mehta Industries Ghee 150 kg’s @ Rs. 26.50 Cash Sales of Ghee 500 kgs @ Rs. 2650.70 Cash Sales of Sugar-SS 100 Kg’s @ Rs. 125 Purchase from Bimanlal Agencies according to the order Above Entry Carriage Inward Rs. 250 through cash 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 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: 10306075

Profit & Loss A/c: 4600175

Bank Credit IB: 61250

Bank Debit CB: 19000

Cash: 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. 1 2 3 4 5 6 7 8 9 10 11

Date 1.4.2015 2.4.2015 2.4.2015 2.4.2015 1.5.2015 2.5.2015 2.5.2015 1.6.2015 1.6.2015 1.6.2015 1.6.2015

ITT Course Practice Manual

Particulars Received cash as Capital for Rs. 2,00,000/Cash deposited in B.O.I. for Rs. 1,00,000/Credit Purchases from Charles Co. as per Inv. No. 12 for Rs. 75,300/Credit purchases from James Co. as per Inv. No. 253 for Rs 50,500/-. Credit purchases from Donold Co. as per Inv. No. 325 for Rs. 60,000/Purchase Return from Charles Co. for Rs. 10,000/Purchase Return from Donold Co. for Rs. 25,000/Cash purchases from Victor Co. for Rs. 35,600/Credit Sales to James Traders as per Inv. No. 001 Rs. 1,25,000/Credit sales to Victor Traders as per Inv. No. 002 Rs. 1,75,000/Credit Sales to Smith Traders as per Inv. No. 003 for Rs. 75,000/-

Page 139 of 149

Sr. No. 12 13 14

Date 2.6.2015 2.6.2015 1.7.2015

15

2.7.2015

16

31.7.2015

Particulars Cash sales as per Inv. No. 004 for Rs. 1,00,000/Returns from Victor Traders for Rs. 25,000/Returns from Smith Traders for Rs. 15,000/Payments made by Cash Furniture Rs. 25,000/Salaries Rs. 50,000/Wages Rs. 30,000/Rent Rs. 25,000/Electricity charges Rs. 12,000/Electricity Deposit Rs. 10,000/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: Sr. 1 2

Date 1.4.2015 2.4.2015

3

1.5.2015

4

2.5.2015

5

31.5.2015

6

1.6.2015

7

1.6.2015

8

2.6.2015

9

1.7.2015

ITT Course Practice Manual

Particulars Received capital by cash Rs. 20,00,000 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 TVS –Victor - 3 nos. @ Rs. 46,800/- each Yamaha - 5 nos. @ Rs. 42,900/- each Purchase the stocks from Raj Motors Kinetic Honda - 4 nos. @ Rs. 35,000/- each TVS –Victor - 3 nos. @ Rs. 46,800/- each Yamaha - 5 nos. @ Rs. 42,900/- each Purchase order given to Naveen Motors (Order no: 102) Pulsar - 5 nos. @ Rs. 50,000/- each 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 Pulsar - 5 nos. @ Rs. 50,000/- each Hero Honda Passion - 5 nos. @ Rs. 48,500/- each Hero Honda Ambition - 3 nos. @ Rs. 46,700/- each TVS –Scooty - 7 nos. @ Rs. 24,000/- each (-) Cash Discount 5% on total purchase. Sales order given by RS Motors Company(Order No 201) Kinetic Honda - 2 nos. @ Rs. 39,900/- each TVS –Victor - 2 nos. @ Rs. 52,200/- each Credit sales to RS Motor Company Kinetic Honda - 2 nos. @ Rs. 39,900/- each TVS –Victor - 2 nos. @ Rs. 52,200/- each (+) TNGST 7% on total sales Credit sales to KJ Motors Pulsar - 2 nos. @ Rs. 53,000/- each Hero Honda Passion - 2 nos. @ Rs. 50,000/- each

Page 140 of 149

Sr.

Date

10

1.7.2015

11

1.7.2015

12 13 14 15 16

1.7.2015 1.7.2015 2.7.2015 31.7.2015 1.8.2015

17

31.3.2016

Particulars Yamaha - 2 nos. @ Rs. 45,000/- each (+) TNGST 7% on total sales Credit sales to JJ Motors Yamaha - 1 no. @ Rs. 45,000/Hero Honda Ambition - 3 nos. @ Rs. 48,100/- each (+) TNGST 7% on total sales Cash sales to Sheeba Motors TVS –Scooty - 3 nos. @ Rs. 25,100/- each (-) Cash discount 3% (+) TNGST 7% on total sales Received cash from RS Motors - Rs. 1,25,000 Received cheque from KJ Motors - Rs. 2,25,000 Paid cheque to Raj Motors - Rs. 2,50,000 Paid cheque to Naveen Motors - Rs. 1,00,000 Paid cheque to JJ Motors - Rs. 1,50,000 Payments made by cash Rs. Telephone charges - 5,000 Salary - 10,000 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. Sr. 1 2

Date 1.4.2016 1.4.2016

3

1.4.2016

4

1.4.2016

5

2.4.2016

6

2.4.2016

ITT Course Practice Manual

Particulars Received capital by cash Rs. 7,00,000/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 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 Samsung color LCD 1 Nos. @ Rs. 24,000/Credit sales to Rose Traders as per Inv. No. 001 Samsung color LCD 7 Nos. @ Rs. 30,000/- each Usha Ceiling fan 70 Nos. @ Rs. 1,000/- each Usha Table fan 70 Nos. @ Rs. 1,500/- each Bajaj Mixi 70 Nos. @ Rs. 2,000/- each Bajaj Grinder 70 Nos. @ Rs. 2,500/- each Plus PGST 4% on total sales Cash Sales as per Inv. No. 002 Samsung color LCD 2 Nos. @ Rs. 30,000/- each Usha Ceiling fan 15 Nos. @ Rs. 1,000/- each Usha Table fan 15 Nos. @ Rs. 1,500/- each Bajaj Mixi 15 Nos. @ Rs. 2,000/- each Bajaj Grinder 15 Nos. @ Rs. 2,500/- each Plus 4% PGST on total sales

Page 141 of 149

Sr. 7 8

Date 2.4.2016 2.4.2016

9

2.4.2016

10

2.4.2016

11

2.4.2016

Particulars Axis Bank Cheque No. 1233123 issued to Blue Hills 70% of Purchase value 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/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/Staff Welfare Rs. 500/Postages Rs. 300/Stationery Rs. 700/Depreciate Computers @ 25%.

Output:Balance Sheet Total:1056168.18

Closing Stock: 129318.18

Profit & Loss A/c: 87568.18

Bank: 336400

Cash: 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 Capital A/c Building A/c Air Conditioner A/c Furniture & Fixtures Vehicles A/c Computer A/c Machine A/c Telephone Bill A/c Electricity Bill A/c Salary A/c Depreciation A/c Commission Received A/c Rent received A/c SBI A/c PNB A/c

Under Group Capital A/c Fixed Assets Fixed Assets Fixed Assets Fixed Assets Fixed Assets Fixed Assets Indirect Expenses Indirect Expenses Indirect Expenses Indirect Expenses Indirect Income Indirect Income Bank A/c Bank A/c

Voucher Entry Sr. 1 2 3 4

Receipt Voucher Receiving cash Rs. 30,00,000 with capital A/c Receiving Rs. 60,00,000 with capital A/c in PNB bank. Commission Received Rs. 2,00,000 Rent Received Rs. 50,000 with cash

ITT Course Practice Manual

Sr. 5 6 7 8

Payment Voucher Building purchase worth Rs.1,00,000 with cash. Furniture purchase worth Rs. 50,000 with cash Air Conditioner purchase worth Rs. 1,00,000 with cash. Vehicles purchased of Rs. 5,00,000 with PNB.

Page 142 of 149

Sr.

Receipt Voucher

Sr. 9 10 11

Payment Voucher Computer purchased of Rs. 50,000 with PNB. Telephone bill paid by PNB Rs. 5,000. 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. 1

Date 1.4.2015

2

2.4.2015

3 4 5 6 7 8 9 10 11 12

1.5.2015 1.5.2015 2.5.2015 2.5.2015 1.6.2015 2.6.2015 1.7.2015 2.7.2015 1.8.2015 2.8.2015

13

1.9.2015

14

2.9.2015

15

1.10.2015

16 17 18 19

2.10.2015 1.11.2015 2.11.2015 2.12.2015

20

2.12.2015

21 22 23 24 25 26 27

1.1.2016 2.1.2016 2.2.2016 1.3.2016 31.3.2016 31.3.2016 31.3.2016

Particulars Capital introduced in business by proprietor in Cash Cash deposited in SBI, Ramdas peth for opening current account no. 521 Cash deposited in SBI current account no. 521 Garments purchased from R.D. Distributors on credit Sold garments to Mr. J.N. Shah on cash Purchased garments from Milton Dresses by paying Cash Sold garments to S.M. Bakare on credit Cash withdrawn from SBI current account no.521 Paid vide ch. no. 636251 to R.D. Distributors Received vide ch. no. 995588 of Bank of India from S.M. Bakare Paid for purchase of files for office Purchased furniture on credit from Vishal Furnitures Paid to ICICI Life Insurance towards annual premium of proprietor vide ch. no. 636252 Office taken on rent, paid towards office deposit to Mr. M.C. Patel vide ch. no. 636253 Amount transferred from account no. 220 maintained with SBI Ramdaspeth current account no. 521 Cash received from S.M. Bakare Rent paid Paid cash to Vishal Furnitures Sales made in cash Loan taken from Mr. M.M. Joshi vide ch. no. 253652 of Bank of Rajasthan Salary paid vide ch. no. 636254 Octroi paid in cash Advance tax paid Amount deposited in savings account of proprietor vide ch. no. 636255 Provision to be made for telephone expenses Interest @ 12% p.a. to be credited to M.M. Joshi Charge depreciation on furniture @ 10% p.a. for one year

Amount 1,00,000 5,000

V. Type Receipt Contra

80,000 40,500 6,800 9,500 26,800 20,000 21,000 15,000 540 40,200

Contra Purchase Sale Purchase Sale Contra Payment Receipt Payment Journal

6,200

Payment

25,000

Payment

75,000

Receipt

5,000 4,000 10,000 25,000

Receipt Payment Payment Sale

1,00,000 10,000 1,250 5,000 10,000 2,348

Receipt Payment Payment Payment Payment Journal Journal 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

ITT Course Practice Manual

Page 143 of 149

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 1.4.2016 1.5.2016 31.5.2016 1.6.2016 1.7.2016 2.8.2016 1.9.2016 1.10.2016 1.11.2016 1.12.2016 31.12.2016 1.1.2017 31.1.2017 1.2.2017 1.3.2017 31.3.2017

Entries Commenced business with Rs 20000, Paid into the bank Rs 10000. Bought furniture for Rs 900. Bought goods from Krishan for Rs 4000 for cash. Sold goods for Rs 1700. Paid telephone expenses Rs 400 Purchase goods for Rs 1000 from Gupta & Sons. Paid Rs 100 for advertisement by cheque. Purchased a typewriter for Rs 8000 on credit from Yogesh Sold goods to Raghav for Rs 2900. Withdraw Rs 350 from the bank for private use. Sold goods to ram for Rs 900 for cash Received cash from Raghav Rs 2850 , discount allowed Rs 50/Paid into bank Rs 2500 Issued a cheque for Rs 300 in favour of the landlord for rent for the month of April Issued a cheque in favour of Gupta & Sons for Rs 950 in full settlement of their account. 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 1.4.2015 2.4.2015 1.5.2015 2.5.2015 1.6.2015 2.6.2015 1.7.2015 2.7.2015 1.8.2015 1.8.2015 1.9.2015 2.9.2015 1.10.2015 2.10.2015 1.11.2015 2.11.2015 1.12.2015 2.12.2015 1.1.2016 2.1.2016

Entries Goods for personal use(Sale price 1000,cost price Rs 800) Goods costing Rs 500 distributed as free samples. Goods costing Rs 500 given as charity Goods stolen (Sale price Rs 500, Cost price Rs 400) 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 installation charges Rs 100. Paid Rs 975 to Krishan on his account for Rs 1000. Received a cheque of Rs 975 from Pinki in full settlement of his account for Rs 1000. Paid Rs 975 to Chetan in full settlement of an account for Rs 1000. 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 owed Rs 1000. Rs. 125 owed by Sanjay written off as bad debt. Received cash for a bad debt written off last year Rs 50. Submitted a claim for breakage of goods to Ram who admitted Rs 100. Receive a claim from Anuj for defects on goods supplied to them: claim admitted Rs 150 Bank Charges interest Rs 20. Allowed interest on capital @ 6% P.A for one month,(Capital Rs 10000) Charged interest on drawings Rs 500. Charged depreciation on furniture Rs 100. Rent due to landlord Mr. Manjeet Rs 80.

ITT Course Practice Manual

Page 144 of 149

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 Onida 21" Onida 25" Samsung 25" Samsung 29" Gold Silver Copper Raymond Eleganza Raymond Royal

Group Onida Onida Samsung Samsung Metals Metals Metals Cloth Cloth

Quantity 7 5 9 3 512.368 6.892 5.690 540.22 890.88

Unit No. No. No. No Gm Kg Kg Mtr. Mtr.

Rate 8,920 13,440 12,500 24,500 1,280 19,240 5,500 467 612 Total

Value 62,440.00 67,200.00 112,500.00 73,500.00 655,831.04 132,602.08 31,295.00 252,282.74 545,218.56 1,932,869.42

Godown Rohini Pitampura ITO Rohini ITO ITO Rohini Rohini Pitampura

Enter following Purchase and Sales transactions in Tally: Date Purchases 01/04/2015 01/05/2015 02/06/2015 02/07/2015 01/08/2015 01/09/2015 01/10/2015 02/11/2015 01/12/2015 01/01/2016 01/02/2016 01/03/2016 02/03/2016 Sales 1/04/2015 02/05/2015 02/06/2015 01/07/2015 01/08/2015 01/09/2015 01/10/2015 01/11/2015 02/12/2015 01/01/2016 01/02/2016

Party

Stock Item

Qty Unit

Rate

Value

Godown

Onida Electronics Ltd. Onida Electronics Ltd. Samsung India Pvt. Ltd. Samsung India Pvt. Ltd. BPL Electronics Ltd. Cash Raymond Ltd. Raymond Ltd. Cash Cash Raymond Ltd. Cash LG India Ltd.

Onida 21" Onida Samsung 25" Samsung 29" BPL 21" BPL 25" Raymond Eleganza Raymond Royal Gold Silver Raymond Star Milk LG 21"

10 No. 12 No. 8 No. 20 No. 23 No. 32 No. 890 Mtr 1,020 Mtr 250 Gm 2.380 Kg 650 Mtr 120 Ltr 25 No.

9,000 15,500 16,800 22,400 11,200 18,500 450 630 1,390 19,880 730 18.50 8,900

90,000.00 1,86,000.00 1,34,400.00 4,48,000.00 2,57,600.00 5,92,000.00 4,00,500.00 6,42,600.00 3,47,500.00 47,314.40 4,74,500.00 2,220.00 2,22,500.00

ITO Rohini Pitampura ITO Pitampura ITO ITO ITO Rohini Pitampura Pitampura ITO Rohini

Pardesi Trading Co. Quality Electronics Quality Electronics Cash Ajay Vijay & Co. Noor & Ali & Co. Milton Milton Laxmi Parvati & Co. Cash Hakim Munim & Co.

Onida 21" Onida 25" Samsung 25" Samsung 29" BPL 21" BPL 25" Dresses Raymond Eleganza Dresses Raymond Royal Gold Silver Raymond Star

12 No. 13 No. 10 No. 15 No. 12 No. 10 No. 250 Mtr 150 Mtr 150 Gm 1 Kg 200 Mtr

9,500 16,500 17,770 24,580 13,500 21,000 510 680 1,420 20,500 800

1,14,000.00 2,14,500.00 1,77,700.00 3,68,700.00 1,62,000.00 2,10,000.00 1,27,500.00 1,02,000.00 2,13,000.00 20,500.00 1,60,000.00

Rohini Pitampura ITO ITO Pitampura ITO Rohini Rohini ITO Pitampura Pitampura

ITT Course Practice Manual

Page 145 of 149

02/03/2016 02/03/2016

Cash Cash

Milk LG 21"

50 Ltr 10 No.

21.00 9,800

1,050.00 98,000.00

ITO 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

Particulars BPL 21" BPL 25" Copper

Quantity

Rate

Inventory Management 1-Apr-2015 to 31-Mar-2016 Outwards Gross Value Consumption Profit 162000 134400 27600 210000 185000 25000 5.50 5.50

Closing Balance %

Qty

17.04 11.9

12 no. 10 no. 0.001 kg

13500 21000 5500

160.000 gms

1418.13

226900

205900

21000

9.26

10 no. 50 litr 12 no. 13 no.

9800 21 9500 16500

98000 1050 114000 214500

89000 925 107040 174720

9000 125 6960 39780

9.18 11.9 6.11 18.55

250.00 mtrs

510

127500

116750

10750

8.43

Raymond Royal

650.00 mtrs

641.54

417000

409500

7500

1.80

Raymond Star Samsung 29"

200.00 mtrs 15 no.

800 24580 19520.3 4.504 kg 2 10 no. 17770 Grand Total

160000 368700

146000 336000

14000 32700

8.75 8.87

11 no. 22 no. 5.689 kg 602.368 gms 15.000 gms 15 no. 70 litr 5 no. 4 no. 1180.22 mtrs 1760.88 mtrs 450.00 mtrs 8 no.

87919.52

86917.04

1002.48

1.14

177700 2365275.02

125000 2117157.54

52700 248117.48

29.66 10.49

Gold Gold Ring LG 21" Milk Onida 21" Onida 25" Raymond Eleganza

Silver Samsung 25"

Liabilities Capital Account Loans (Liability) Current Liabilities Sundry Creditors Profit & Loss A/c Opening Balance Current Period Diff. in Opening Balances Total

as at 31-Mar-2016

2856100.00

2856100.00

Assets Current Assets Closing Stock Sundry Debtors Cash-in-hand

Rate

Value

11200 18500 5500

123200 407000 31289.50

1323.83

797431.04

932.33 8900 18.50 9080 19620

13985.02 133500 1295 45400 78480

454.18

536032.74

620.89

1093318.56

730 23187.50

328500 185500

8.268 kg

19392.77

160339.44

7 no.

17414.29

121900 4057171.30

as at 31-Mar-2016 4859386.90 4057171.30 1480700.00 -500784.40

248117.48 248117.48 1932869.42 5037086.90

Total

Exercise 4.6: Inventory Management in the books of Gold Silver Traders

5037086.90

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). ITT Course Practice Manual

Page 146 of 149

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 1 100gm Silver purchased on 02.04.16 at Rs.20/- per gram. 100gm Gold purchased from M/s Ashu on 02.06.16 @Rs. 10,000 2 per 10 gm. 2 Kg, Silver purchased from M/s.Chawla on 02.06.16 @Rs. 3 20,000 per Kg. 1500gm silver purchased from Radheshyam & co. on 4 01.07.16@Rs. 19,900 per Kg. 150gm Gold purchased from Mahakali Traders on 02.08.16 @Rs. 5 9980 per 10gm. Silver 2.5 Kg. Silver purchased from M/s. Chawla on 01.09.16 @ 6 Rs. 19,990/- Per Kg. 7 300gm Gold purchased on 02.10.16 @ Rs. 9980/- per 10gm 8 9 10 11 12



150gm Gold purchased from M/s.Ashu on 01.11.16 @Rs 9,985/per 10gm. 1500gm Silver purchased on 02.12.16 @Rs. 19,990/- per 1000gm. 3 Kg Silver purchased on 01.01.17 @Rs.20, 000/- per Kg. 500 gm Gold purchased on 01.02.17@ Rs. 10,000/-per 10gm. 1500gm silver purchased on 02.03.17 @Rs. 19,990 per Kg.

Following transactions are Sales 10gm silver sold on 02.04.16 at Rs. 20,66/100gm Gold sold on 02.06.16 to Devarshi Alankar @ Rs. 10,980/- per 10gm. 1500gm silver sold on 02.06.16 Gupta Jewellers @ Rs. 30,000/- per Kg. 2000gm Silver sold on 02.07.16 @ Rs. 20,680/- per Kg. 150gm Gold sold on 31.08.16 @ Rs. 10,965/- per 10gm. 2200gm Silver sold to Bhagwati Jewellers on 02.09.16 @Rs. 20,670/-per Kg. 250gm gold sold to Devarsh Alankar on 02.010.16 @ Rs. 10,980/- per 10gm 200gm Gold sold to Gupta Jewellers on 01.11.16 @ Rs 10,985/- per 10gm. 1890gm Silver sold on 01.12.16 @Rs. 20,700/- per 1000g. 2500gm silver sold on 02.01.17 @Rs 20,690/- per Kg. 450gm gold sold on 02.02.17 @Rs. 10,990/- per 10gm. 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: 3487756.13

Closing Stock according to Default Method: 49953.13

Exercise 4.7: Enter the transactions & prepare Sale & Purchase Book Date April 1, 2016 April 1, 2016

Sold 20 Rice Bags @ Rs 100

July 1, 2016

New

Entries Bought following products from Yog Raj 100 bags Sugar @ Rs 100 per bag 20 bags Rice @90 per bag 30 bags Barley @40 per bag 25 bags Sugar @80 per bag

April 2, 2016

Closing Stock according to FIFO Method: 50000

Sold following products to Bhandari 25 bags Barley @48 per bag 50 bags Sugar @120 per bag 20 bags Sugar @90 per bag Bought following products from Ravi 50 bags Sugar @25 per bag 30 bags Rice @70 per bag 70 bags Maize @30 per bag 20 bags Wheat @80 per bag

ITT Course Practice Manual

Page 147 of 149

Date August 1, 2016 Sep 1, 2016 Nov 1, 2016 Dec 1, 2016 Feb 1, 2017

Entries Sold following products 100 bags Sugar @70 per bag 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 30 bags Sugar @150 per bag 20 bags Ghee @150 per bag 30 bags Rice @60 per bag Sold to Pakistani 35 bags Sugar @170 per bag 5 bags Barley @50 per bag 20 bags Wheat @100 per bag 20 bags Ghee @200 per bag 40 bags Rice @100 per bag

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 1 2 3

Sr.

Ledger Name Capital SBI Car Loan T. Asin

4

SBI Cash Credit

5

Kareena Fabrics Ltd.

6 7 8 9 10 11 12 13

VAT Payable( 2015-16) BSNL Office Land A.B. Corp Shares Reliance Mutual Fund Sohail Khan Telephone Deposit

14

S. Mallika

15 16

SBI Current Account Cash

Description Owner's money invested in business Loan taken for purchase of car against mortgage of car Loan taken from friend for business without any security Loan taken for working capital against security of Stock and Debtors Our supplier, we have purchased dresses from this company on credit and money is still payable VAT for the year payable to Government Office telephone bill payable to BSNL for March 2015 Office place owned by and used by business Land at wardha road, purchased as investment Amount invested in shares of the company Amount invested in mutual fund Temporary loan given to brother without any security Deposit kept with BSNL for office telephone connection Our customer, we have sold goods to her on credit and money is still receivable Current account in the name of business maintained with SBI

Debit

Credit 10,40,670 6,70,540 4,00,000 5,43,990 97,200 15,230 2,540

16,03,280 8,65,000 50,000 25,000 75,000 5,000 92,900

34,290 19,700 Total 27,70,170 27,70,170

Output:Balance Sheet Total: - 27,70,170

ITT Course Practice Manual

Page 148 of 149

5. Chapter 5: Financial Analysis Tools in Tally.ERP9 Exercise 5.1: Prepare Cash Book and Bank Book as per the following entries. Sr. No 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

New

Entries Balance of cash in hand Rs 1400. Overdraft Rs 5000 Invested further capital of Rs 10000 out of which Rs 6000 was deposited in the bank. Sold goods for cash Rs 3000. Collected from debtors of last year Rs 8000; discount allowed to them Rs 200. Purchased goods for cash Rs 5500. Paid Ram, a creditor Rs 2500; discount allowed Rs 65. Commission paid to an agent Rs 530 Office Furniture purchased from AK Rs 200 Rent paid Rs 50 Electricity charges paid Rs 10 Drew cheque for personal use Rs 700 Cash sales Rs 2500 Collection from Atul Rs 4000: Deposited in the bank next day Drew from bank for office use Rs 500 Drew a cheque for petty cash Rs 150 Dividend received by cheque Rs 50, Deposited in the bank same day Commission received by cheque Rs 230 Drew from the bank for salary of the office staff Rs 1500 and salaries paid Paid the salary of the manager by cheque Rs 500 Deposited cash in the bank Rs 10000.

Output:Cash:-810 Bank:-11930 Balance Sheet Total:- 9300

ITT Course Practice Manual

Page 149 of 149