Ms office practical

Ms-office practical Step by Step Students and Staffs are very useful.

1. MANNAR THIRUMALAI NAICKER COLLEGE (Autonomous)
Pasumalai, Madurai-625 004.

PG & Research Department of Commerce




BONAFIDE CERTIFICATE


Name : Course:

Register Number :

College : Mannar Thirumalai Naicker College (Autonomous)


This is to certify that this record is a bonafide work done by the above
mentioned student.The certificate is awarded for the same.






Head –Department of Commerce Staff in-Charge


Submitted for University Practical Exams held on __________________
at Mannar Thirumalai Naicker College(Autonomous), Madurai.





Internal Examiner External Examiner

2. Table of Content


S.No. Particulars
Page
No.
Microsoft-OFFICE
MS-Word 1-13
1. Creating the Document using Paper & Document
Formatting
1
2. Creating the Table using the sub column or row
functions
6
3. Designing an advertisement 9
4. Designing an invitation for a Function using different
Format
12
MS-Excel 14-41
1. Making illustration in a Worksheet – Relative, Absolute &
Mixed addressing.
14
2. Creating worksheet using Chart by Gridlines, legends &
titles for axes
17
3. Creating Worksheet for Data Sort, Data Filter. 23
4. Creating a Worksheet for the Employee salary sheet 29
5. Creating a Worksheet using Pivot Table & Pivot charts 32
6. Creating a Worksheet using analytical tools – Goal Seek,
Solver and Scenarios.
36
MS-PowerPoint 42-52
1. Creating Power point presentation for Company Product by
using different features
42

3. 2. Creating Power point presentation for a Performance of a
company using Charts, tables.
47

4. Table of Content


S.No. Particulars
Page
No.
MS-Access 53-69
1. Creating Table for the Students using Access 53
2. Creating Queries for book depot using Access 57
3. Creating Form for the Employee details using Access 63
4. Creating Report for the Customer details using Access
forms
66

Tally 6.3
1. Creation of new company with opening balance/Ledger 70
2. Creation of new company Ledger accounts without
Opening Balance
77
3. Creating Inventory stock group 80
4. Preparation of Sales Invoice 84

5. Ms-Office --- Ms-Word 1/14


Ms-Office --- Ms-Word

Activity: I
Attribute 1:
To create a document and type an essay with the following Settings:
Steps:
1. In windows Operating system select the Program in start menu drag to Ms-
office and select MS-Word. Else select Run in start menu type
“WinWord”
2. After Word opens in the window select the File menu and open a New
document (Ctrl-N).
3. Type any document as per shown in the Figure 1.1

Fig 1.1 Word window typed with document
Attribute 2:
To Set Page Length 11
Steps:
1. In word to set the Paper size select the File menu and select the Page setup
in the menu. Else use shortcut Alt-F-U.
2. In Page setup Box select Paper tab as shown in the figure 1.2

6. Ms-Office --- Ms-Word 2/14
3. Then select the paper size as Letter which height will be 11” as per our
attribute
4. Then press ok button


Fig 1.2 Page setup box showing the Paper tab
Attribute 3:
To Set Top Margin 1.5, Bottom Margin 1.3, Left Margin 1, Right Margin 1
Steps:
1. In word to set the Paper size select the File menu and select the Page setup
in the menu. Else use shortcut Alt-F-U.
2. In Page setup Box select Margins tab as shown in the Figure 1.3.
3. Then Set the Top, Bottom, Left, Right Margins as per our attribute as
shown in the Figure 1.3.

7. Ms-Office --- Ms-Word 3/14

Fig 1.3 Page setup box showing the Margins tab

Attribute 4:
Header Margin 0.8 from the top edge of the Paper, Footer Margin 0.7 from the
bottom edge of the Paper
Steps:
1 In word to set the Paper size select the File menu and select the Page setup
in the menu. Else use shortcut Alt-F-U.
2 In Page setup Box select Layout tab.
3 Then Set the Header, Footer from edge option as per our attribute as
shown in the Figure 1.4.

8. Ms-Office --- Ms-Word 4/14

Fig 1.4 Page setup box showing the Layout tab
Attribute 5:
Define suitable header for Odd & even Pages.
Steps:
1. In word to set the Page odd or even select the File menu and select the
Page setup in the menu. Else use shortcut Alt-F-U.
2. In Page setup Box select Layout tab.
3. Tick out the check box with the “Different Odd & even Page” as shown in
the Figure 1.4
4. Finally Press Ok button then return to the Main document window press
View menu selects Header & footer, it will display the header and footer
of the page type the proper header & footer for the document as shown in
the Figure 1.5.

9. Ms-Office --- Ms-Word 5/14

Fig 1.5 Document with Header & Header footer tool bar
Attribute 6:
Show the Page number in Footer.
Steps:
1. Main document window press Insert menu selects Page number; it will
display Page numbers Window in the window select Position as Bottom of
Page (Footer) & Alignment as in the combo box as shown in the Figure
1.6.

Fig 1.6 Page number window

2. Press ok return to main window check out the Footer as shown in the figure
1.7

10. Ms-Office --- Ms-Word 6/14

Fig 1.7 Document with Footer showing Page number


Result:

Hence all the attributes as mentioned above are shown with the proper Outputs in
Ms-Word

11. Ms-Office --- Ms-Word 7/14
Activity: II
Attribute 2.1:
To create a Word document, using Table with Minimum of 5 Columns and 10
rows.
Steps:
1. In windows Operating system select the Program in start menu drag to Ms-
office and select MS-Word. Else select Run in start menu type
“WinWord”.
2. After Word opens in the window select the File menu and open a New
document (Ctrl-N).
3. Select Table (Alt-a) menu in the Menu bar, & select the Insert option. Ref.
Fig 2.1.

Fig 2.1 Document with Table menu Insert Option
4. Insert the number of rows and columns in the Insert Table window and
select the option “auto fit to window” as shown in the Fig 2.2.

Fig 2.2 Insert Table Window

12. Ms-Office --- Ms-Word 8/14
Attribute 2.2:
To insert the table with 2 Sub columns and 2 Sub Rows
Steps:
1. Here we take the College Score board for our Example.
2. To type the College name in the center merge the 5 columns. To merge the
Cells first of all select the cells, and go to table menu select the merge cells
option else right click may bring the same option as merge cells. Refer Fig
2.3
3. Insert the data’s using the same procedure continue the next lines.
4. If any Insertion, deletion needed select from the Table and Border tool bar
as shown in Fig 2.3
5. Student Name should be typed with the merged Rows, so same way select
the 2 rows below that column and merge the rows. Same for Sl.No. Reg
No.
6. After step 5 select the ‘Stud name’ & Reg.no’ columns, right click of the
mouse will show as Cell alignment option in that select center alignment,
or select this option from the Table & border tool bar. Refer Fig 2.4


Fig 2.3 Merge Cell option Fig 2.4 Cell Alignment option

7. If any deletion of the cells required then select the cell which want to delete
and go to the table menu select delete option. Refer Fig 2.1
8. Here after finishing the structure insert the proper data’s in the Table. Align
it with Cell alignment, merge or split using the Table border tool as per the
requirement. Fig 2.5

13. Ms-Office --- Ms-Word 9/14

Fig 2.5 Cells with proper data’s cell alignments
Result:

Hence all the attributes as mentioned above are shown with the proper Outputs in
Ms-Word

14. Ms-Office --- Ms-Word 10/14
Activity: III
Attribute 1:
To create a design an advertisement copy using as many word features as possible.
Steps:
1. Here we take the Fresher requirement advertisement board for our
Example.
2. Select the rectangle from the drawing tool bar draw the rectangle box as
shown in Fig 3.1

Fig 3.1 Word window typed with document
3. Type the advertisement title on top of the rectangle box FRESHERS
REQUIREMENT and select that title and select the format menu click the
FONT window.
3.1 select font, style and size as “times new roman”,”bold”,”30”.
3.2 select the font color “white”.
3.3 select the effects in “all caps”.
3.4 select the back ground color orange from fill color on drawing box.
3.5 select the drawing bar form toolbars draw the line below of the title
4. Else select the table option in table menu insert 3 rows and then inserts the
data as you needed.

15. Ms-Office --- Ms-Word 11/14

Fig 3.2 Word windows typed with advertisement using table
5. Select the text box type the text inside of the oval box following figure
shown figure 3.2 “assured”, ”job”, “openings”.
6. Draw the line near by oval box for the advertisement.
7. type the text programming in C,Java,J2ee &.Net and second line eligibility
:BE/.TECH/MCA/M/S.C.,/B.SC.,/BCA/Diploma (2005-2007)select that
word changing font ,font style and size followed steps 3,3.1,3.2,3.3 and fill
color “red” and “block”
8. Insert the text box type word “more than 1500 it companies tie-ups” and fill
color orange from bock ground and text color “white”.
9. Type the text training cum requirement program and below of the text draw
a line.
10. type the address in your advertisement design the font ,font style and size,
color as per showing Figure 3.3
11. Insert picture - Select the insert menu and click the command picture –clip
Art.
12. Type the company name and e-mail address in your advertisement design
the font ,font style and size, fill the color as per showing figure 3.3

16. Ms-Office --- Ms-Word 12/14

Fig 3.3 Word window typed with advertisement in full completed form

13. Finally give the proper color to the text as per the picture and give the final
check to the advertisement.
14. Now the full advertisement is created with the word document.
Result:
Hence all the attributes as mentioned above are shown with the proper Outputs in
Ms-Word

17. Ms-Office --- Ms-Word 13/14
Activity: IV
Attribute 1:
To create a document and type from the following invitation Model
Steps:
1 In windows Operating system select the Program in start menu drag to Ms-
office and select MS-Word. Else select Run in start menu type
“WinWord”
2 After Word opens in the window select the File menu and open a New
document (Ctrl-N).
3 Draw a text box in the document according to the invitation size like height
7.19” and width 6.19” in Format text box as shown in Fig 4.1

Fig 4.1 Format Text box window showing the Height and width of the TEXT box
4 In Colors and Lines tab make the style of the Line as double line refer Fig
4.2

Fig 4.2 Format Text box window showing the Style of the Line double

18. Ms-Office --- Ms-Word 14/14
5 Then type the Invitation mater as per the data then align the text with before
and after text and Font alignment as per the design using Format menu
Paragraph and Font refer Fig 4.3

Fig 4.3 Word document showing the Invitation of the College.
6 When the Invitation need to insert the bullet or numbering use Format
menu bullet and Numbering option
7 Another important thing is to insert the Logo or emblem use Insert menu
Picture or else use the Clip art option to insert any pictures as you like.

Result:
Hence all the attributes as mentioned above are shown with the proper Outputs
in Ms-Word

19. Ms-Office --- Ms-Excel 14/41
Ms-Office --- Ms-Excel

Activity: I
Attribute:
To make out an illustration in a worksheet explaining Relative cell addressing,
absolute addressing and mixed addressing.
Steps:
1. In windows Operating system select the Program in start menu drag to
Ms-office and select MS-excel. Else select Run in start menu type
“Excel”
2. After Word opens in the window select the File menu and open a New
Worksheet (Ctrl-N).
3. Type any Numerical data’s as per shown in the Figure 1.1

Fig 1.1 Excel document data given for Relative referencing
4. When cells are referenced by their position in the worksheet on the basis
of their position relative to the cell containing the formula, is called as
relative referencing
5. In above figures the total can be calculated by using a formula
=B3+C3+D3+E3+F3 in the cell G3
Absolute Referencing
6. Cell referencing in which references do not change when a formula is
copied to a different cell is called Absolute Referencing. A $ (dollar)
Sign is used before the parts of a formula.

20. Ms-Office --- Ms-Excel 15/41

Fig 1.2 Excel document data given for Absolute Referencing
7. When a formula containing absolute cell address is copied, only the
relative cell address is updated and the absolute cell address remains as
it is =B11*$C$11 where B11 can be changed and $C$11 unchanged.
Mixed Addressing
8. A combination of relative and absolute referencing is called mixed
referencing.
9. The $ Sign before column letter as well as row number makes it
absolute cell address
10. The $ Sign before row number or column letter makes it mixed cell
address. In that address part of the cell is relative and other is absolute.
11. Without any dollar sign, the cell address is relative cell address.
12. We can observe that in column B cell B5, contain the formula =A5*$E1
13. We can observe that relative part of the formula is updated and the
absolute part remains the same.
14. Hence refer the Fig 1.2,1.3

Fig 1.2 Excel document data given for Mixed Referencing

21. Ms-Office --- Ms-Excel 16/41

Fig 1.3 Excel document data given for Mixed referencing output

























Result:
Hence all the requirements as mentioned above are shown with the proper
Outputs using Ms-excel.

22. Ms-Office --- Ms-Excel 17/41
Activity: II
Attribute:
To create a Worksheet with necessary information and make out a suitable
chart showing gridlines, legends, and titles for axes.
Steps:
1. In windows Operating system select the Program in start menu drag to
Ms-office and select MS-excel. Else select Run in start menu type
“Excel”
2. After excel opens in the window select the File menu and open a New
Worksheet (Ctrl-N).
3. Type any Numerical data’s as per shown in the Figure 2.1

Fig 2.1 Excel document data given sample
3 For the above stockiest details we have to prepare a chart for the Product
sales. Select Insert menu then select Chart option.

23. Ms-Office --- Ms-Excel 18/41

Fig 2.2 Chart wizard showing the Chart type
4 First step is to select the chart type for example Bar chart, Pie-Chart or
Line chart etc., as shown in the Figure 2.2

Fig 2.3 Chart wizard showing the Data rage and Series
5. Second step is to select the Data range and series of the chart we have
selected 3
rd
and 4
th
column only for TV sales refer Fig 2.3
6. Then next step is the import step of giving Title, Axes, Gridline,
Legends, Data Label and Data Name. Refer Fig 2.4,2.5,2.6,2.7,2.8,2.9

24. Ms-Office --- Ms-Excel 19/41

Fig 2.4 Chart wizard with title Option

Fig 2.5 Chart wizard with Axes tab
7. Gridline tab is import that will show the grid in x axis and also in y axis
if we need we can display else we can remove the option as shown in
Fig.2.6.
8. In Legend option we can indicate the value of the color for comparison
of different Product refer Fig.2.7.

25. Ms-Office --- Ms-Excel 20/41

Fig 2.6 Chart wizard with Gridline tab

Fig 2.7 Chart wizard with Legend tab
9. In Data Label tab, we can give the values of the bar on the display or
give their separator which is easy for direct comparison refer Fig.2.8.
here we have selected the Values in Data label
10. Data table is the final tab in the Step 3 where we can display our full
data table in the chart itself. Else we can omit this step use it when
necessary. Refer Fig.2.9.

26. Ms-Office --- Ms-Excel 21/41

Fig 2.8 Chart wizard with Data label tab

Fig 2.9 Chart wizard with Data table tab
11. Final and Fourth steps in the Display of the excel chart sheet which is
Chart Location option. It asks for the two options whether to display in
new sheet or else display in existing sheet, this option is to select by the
user as per his/her requirement. Refer Fig.2.10.
12. In our example we select to display in the new sheet to display it
accurately. Finally press Finish button a new sheet appears with the
chart. Refer Fig.2.11

27. Ms-Office --- Ms-Excel 22/41

Fig 2.10 Chart Wizard with Chart Location Option

Fig 2.11 Full Chart Showing in the New sheet (Chart1)





Result:
Hence all the requirements as mentioned above are shown with the proper
Outputs using Ms-excel.

28. Ms-Office --- Ms-Excel 23/41
Activity: III
Attribute:
To create a Suitable worksheet with necessary information and use Data Sort to
display and use Data filters to satisfy five different criteria.
Steps:
1. After Excel opens in the window select the File menu and open a New
Worksheet (Ctrl-N).
2. Type any Numerical data’s as per shown in the Figure 3.1

Fig 3.1 Excel document data given sample
3. To satisfy the Data sort option we use the Data menu in excel and select
Sort option Fig 3.2
4. Then Select the Sort option with the column select the column name
given in the combo box as shown in Fig 3.3
5. We can sort the first column and then second column, we can use it by
as per our requirement.
6. Ascending or descending option is also given by as side of the Sort
option so we select the option as per user needs.
7. Finally the result of sort will be produced in the sheet we select for the
sorting.

29. Ms-Office --- Ms-Excel 24/41

Fig 3.2 Insert Data sort option Window

Fig 3.3 Sort options in Excel
8. We can use more options using the options command button, for
advanced sorting.
9. Next we want to filter the Data for clean report. For that select Data
menu and open the same option Filter and select the AutoFilter as
shown in Fig 3.4

30. Ms-Office --- Ms-Excel 25/41

Fig 3.4 Data Menu, Filter-Auto Filter selection.
10. After selecting the auto filter option a filter button will be appear in the
data heading as shown in Fig.3.5.

Fig 3.5 Data Heading with filter Option
11. We have to write the 5 Option to satisfy our attribute given in the aim of
the program as follows
1. Select LG product in 3 types (Audio, TV, and DVD) of product.
Drag the Product Name and select the LG in the drag down box
then data will be filtered as shown in Fig 3.5(a), 3.5(b)

31. Ms-Office --- Ms-Excel 26/41

Fig 3.5(a) Data Filter by Product Name LG

Fig 3.5(b) Data Filter by Product Name LG after Filter Display
2. Select the Blank spaces rows in Product id column. Select the
Blank in the drag down box then data will be filtered as shown in
Fig 3.5(c).

Fig 3.5(c) Data Filter by Product id Blank spaces
3. Select the Product Price above the Rs.6000/- so to satisfy the
requirement we have to select the Custom option in the Drag
down box in Product price column. A Custom auto filter
window will be appeared select the greater Option and 6000 as
shown in Fig 3.5(d). then the final result of the filter will be Fig
3.5(e)

32. Ms-Office --- Ms-Excel 27/41

Fig 3.5(d) Data Filter by Product Price Custom Auto filter

Fig 3.5(e) Data Filter by Product Price Custom Auto filter final Filtered data
4. Select the Product Discount with the same percentage for
example can be filtered all the 10% In Discount column select
10% in the drag down box then data will be filtered as shown in
Fig 3.5(f)

Fig 3.5(f) Data Filter by Product Discount Auto filter final Filtered data
5. Select the Product with the Top 10 for the we can filtered all the
top 10 In Product price column select Top10 option in the drag
down box then data will be filtered as shown in Fig 3.5(g), Fig
3.5(h)

33. Ms-Office --- Ms-Excel 28/41

Fig 3.5(g) Data Filter by Top10 Auto filter

Fig 3.5(h) Data Filter by Top 10 Product Price Auto filter final Filtered data



Result:
Hence all the requirements as Data sort and Data Filter above are shown with
the proper Outputs using Ms-excel.

34. Ms-Office --- Ms-Excel 29/41
Activity: IV
Attribute:
To Prepare Salary Bill in the worksheet showing the Basic Pay, DA, HRA,
Gross Salary, PF, TAX, and Net Salary.
Steps:
1. After Excel opens in the window select the File menu and open a New
Worksheet (Ctrl-N).
2. Type any Numerical data’s as per shown in the Figure 4.1 don’t enter
any DA, HRA, column leave as blank we have to use the Formula to
calculate the actions.

Fig 4.1 Excel window typed with Basic salary sheet for the employees
3. For example we take DA 5% & HRA 10% so we calculate the Gross
salary by the following formula.
DA =E4*(5/100) Where e4 is basic salary
HRA =E4*(10/100) where e4 is basic salary
Gross Salary = =E4+F4+G4
Where E4 is basic salary, F4 is DA, G4 is HRA.
Then Drag down it for all the rows in the E, F, and G column. Final
result will be as shown in Fig.4.2

35. Ms-Office --- Ms-Excel 30/41

Fig 4.2 Excel window typed with Basic salary sheet Gross salary calculated using
Formulas
4. For example we take PF 12% from Basic Salary and TAX 10% from
Gross Salary and calculate Net Salary, so here we apply following
formulas
PF =E4*(12/100) Where e4 is basic salary
TAX =H4*(10/100) where H4 is Gross salary
Net salary = H4-I4-J4
Where H4 is Gross Salary, I4 is PF & J4 is TAX
Then Drag down it for all the rows in the H, I, J and K column. Final
result will be as shown in Fig.4.3.

36. Ms-Office --- Ms-Excel 31/41

Fig 4.3 Excel sheet with full calculated Salary sheet using Formulas
Result:
Hence all the Salary sheet functions are calculated in the sheet and displayed
using proper Outputs using Ms-excel.

37. Ms-Office --- Ms-Excel 32/41
Activity: V
Attribute:
To Create, display and interact with data using Pivot tables and Pivot Charts in
excel Features.
Steps:
1. After Excel opens in the window select the File menu and open a New
Worksheet (Ctrl-N).
2. Type any Numerical data’s as per shown in the Figure 5.1

Fig 5.1 Excel window typed with Student status sheet
3. Open the workbook where you want to create the PivotTable report
4. On the Data menu, click PivotTable and PivotChart Report
5. Click PivotTable under What kind of report do we want to create?
6. Create a database as described in the above figure 5.1, ensuring that the
field names are in the first row.
7. Click in the header row of the database, hen select data, pivot table and
pivot chart report. You see the pivot table and pivot chart wizard shown
in the following figure.

38. Ms-Office --- Ms-Excel 33/41


8. The second step of the wizard allows specifying the cells containing the
database .since we already clicked in a header row, these cells are
selected automatically, so we can click next button.
9. In the third step of the wizard, select whether we wish the pivot table to
appear on a new sheet or on the existing sheet .to appear pivot table on a
new sheet, click finish button the blank pivot table as shown in the
following figure appears.

10. Drag the fields you wish to summarize to the appropriate area of the
pivot table .in our example, we should drag the rank to the row fields
area, and the year to the column filed area, and the name to the data

39. Ms-Office --- Ms-Excel 34/41
items areas. This creates a pivot table that shows the number of students
by year and rank.
11. To be able to switch between seeing all students or students by dept,
drag the dept field to the page field’s area. The completed pivot table as
shown below appears.

12. To see the students rank list by arts dept click down arrow button of B1
cell. Then select arts and click ok. It shows the arts dept rank list as
follows.

13. Like the above step we may also see the student rank list by science
department.
14. Then main criteria is to create the Pivot chart so after the data prepared
in pivot table press chart option in Pivot tool bar
15. We can use the chart preview as per the department or as per the year in
the combo selection box as shown in below figure.

40. Ms-Office --- Ms-Excel 35/41

16. Option of selection is shown below figure

Result:
Hence all the Pivot table properties are done finally chart is prepared for the
following data using Ms-excel.

41. Ms-Office --- Ms-Excel 36/41
Activity: VI
Attribute: 1
To create a worksheet with necessary information and use a set of analytical
tools such a Goal Seek.
Steps:
1. After Excel opens in the window select the File menu and open a New
Worksheet (Ctrl-N).
2. Type the following data as per showing figure 6.1

Fig 6.1 Excel window typed with GOAL SEEK
3. AFTER enter the data you select D6 column.
4. Now you go to formula bar to type the formula
= (A6*C6/100) +A6/B6.
5. Now select the d6 column and select the tools menu and click the goal
seek. Now opening a goal seek window you enter the data from the
following
To set cell d6
To value 1000
By changing cell c6 as per shown figure 6.2

Fig 6.2 Excel window typed with GOAL SEEK window
Finally click ok button.
6. Now appearing new value in excel work sheet and open a goal seek
status window as per shown figure 6.3

42. Ms-Office --- Ms-Excel 37/41

Fig 6.3 Excel window typed with GOAL SEEK STATUS WINDOW
After execution click Ok Button.
Attribute: 2
To create a worksheet with necessary information and use a set of analytical
tools such a Scenarios in MS excel
Steps:
1. After Excel opens in the window select the File menu and open a New
Worksheet (Ctrl-N).
2. To enter the data from following figure 6.2.1(Basic ,da )

Fig 6.2.1 Excel window typed with scenarios
3. Now click DA value d6 column and enter the formula b6*c6 as per
shown figure 6.2.2. Now get a DA value.

Fig 6.2.2 Excel window typed with (scenarios) da value formula
4. Select the column form a6:b6
5. Select the tools menu and click scenarious. Now open a scenarious
manager window. As per shown fig 6.2.3.after open a scenarios manager
click add and enter the scenarious name A

43. Ms-Office --- Ms-Excel 38/41



Fig 6.2.3 Scenarios Manager Window
6. After enter the name click ok button now open a scenario value window.
Enter the value for changing cell in 2 values 0.2 and click ok button.
from the following fig.6.2.4.

44. Ms-Office --- Ms-Excel 39/41
Fig 6.2.4 Excel Scenarios values formula
7. Now open a scenario manager window and click summary option and
open a scenario summary window and select the result cells c6 and click
ok button as per fig 6.2.5


Fig 6.2.5 Scenario Summary
8. Now create scenario summary and you got a value from following figure
6.2.6

45. Ms-Office --- Ms-Excel 40/41

Fig 6.2.6 Scenario Summary
Attribute: 3
To create a worksheet with necessary information and use a set of analytical
tools such a Solver in MS excel
Steps:
1. After Excel opens in the window select the File menu and open a New
Worksheet (Ctrl-N).
2. Enter the data as per the figure

3. In Solver we can give multiple conditions but solver if not installed by
default, we have to install it form tools- Add Ins-Select solver check
Box-Ok
4. E8 should contain the formula for profit (SALES-PRODUCTION).
5. In our condition we have to increase the Profit, so to increase the profit
say 500 by modify more than one cell we use the tool solver.

46. Ms-Office --- Ms-Excel 41/41
6. Tool-Solver-follow as per the figure

7. Set Target cell select the profit cell address.
8. In equal to set a value of say 500.
9. By changing the cell column select both the production and sale value
cells.
10. After that if we need a constraints we can add it as
11. Add constraints – Select the production value cell specify some >/</=
value
12. Add constraints Select the sale value cell specify some >/</= value
13. Then click the solver Final solver amount we be printed in the screen,
output can be verified.
Result:
Hence all the necessary information and analytical tools Such as Goal Seek,
Solver and Scenarios are done and verified using proper data’s in ms-excel.

47. Ms-Office --- Ms-Power Point 42/52
Ms-Office --- Ms-Power Point

Activity: I
Attribute:
Create a power point presentation for promoting sales of your company’s
product. It should contain slides covering profile of the company, product
features, different offers, payment modes and contact address.
Steps:
1. In windows Operating system select the Program in start menu drag to
Ms-office and select Power Point. Else select Run in start menu type
“Powerpnt”
2. After power point opens in the window select the File menu and open a
Blank presentation (Ctrl-N).
Type any Company document as an example shown in the Figures.

Figure 1.1 Power point Design the Company Profile

Figure 1.2 Designing the Product Features Slide

48. Ms-Office --- Ms-Power Point 43/52

Figure 1.3 Power Point Slide Design with Contact Address of the company

Figure 1.4 showing the Front design of Power-point
3. Now we go For the Design tip of the Slide Mainly to insert the text we
should insert the Text box so Select Insert-Text Box form Menu.
Finally type data for the slide
4. We can change color click font color option from drawing tool bar. Now
appearing color window as per shown Figure 1.5, Change as per choice.

Figure 1.5 Color window

49. Ms-Office --- Ms-Power Point 44/52
5. Next step is to set the background design for the slide so to insert the
background press the option Format-Slide Design option. Select the
Design as wise shown in the Figure 1.6

Figure 1.6 Showing selection of Design for the Slide
6. Else we want to set the Plain Background select Format-Background
option where we can change the color for the Slide Background refers
Figure 1.7.


Figure 1.7 Back Ground Color window-with Fill Effects option
7. After Giving the Design Main operation is to insert the Picture so to
insert select Insert-Picture-Clipart or File. After selecting the Picture
insert into the slide align as per the requirement.
8. More Pictures are available in Clipart template if we want to insert the
company logo, employee photo etc such thing can be inserted from the
file selection option
9. Give the appropriate picture according to the slide heading content.

50. Ms-Office --- Ms-Power Point 45/52

Figure 1.7 Insert Clip Art option–Clip Art Pictures are displayed.
10. Finally all the simple slide design is completed at last to attract the
viewers we can add animation to the slide. To animate select menu Slide
show–Custom animation option.
11. After choosing the animation we can give any animation for the data,
text, picture, & slide. Every animation can be given as per the designers
view of point for example we can give emphasis while text enter the
show else we can give exit glow when text exit the show.
12. Give the animation for words also which will attract the viewer for
example we can move every word by word. And slide transactions also
given to the slide refer Figure 1.8

Figure 1.7 Custom animation-Word animated using Blinds.
13. We can also use the Sound for the slide to insert select Custom
Animation-blind options (Right click)-Effect options–sound option.
As shown in the Figure 1.9.
14. Finally review all the design of the slide using the slide show option in
the custom animation if any correction needed in timing give according
to the slide transition.

51. Ms-Office --- Ms-Power Point 46/52


Figure 1.8 Custom animation-
Slide Transition Window
Figure 1.9 Custom animation-Effect Option–Sound
selection Window
15. Finally all the process completed view the slide show by pressing F5 or
Slide show–View show.
16. Save the file in the filename.*.ppt format else we want to display only
the slide show Save the file as PowerPoint show *.pps extension.


Result:

Hence all the attributes as mentioned above are shown with the proper Outputs
in Ms-PowerPoint using all the tools required.

52. Ms-Office --- Ms-Power Point 47/52
Activity: II

Attribute:
Create a power point presentation to explain sales performance of your
company over a period of five years. Insert suitable picture from Clip art. Use
suitable animation features. It should contain slides covering profile of the
company, year wise sales, and graph with gridlines, legends and titles for axes.
Steps:
1. In windows Operating system select the Program in start menu drag to
Ms-office and select Power Point. Else select Run in start menu type
“Powerpnt”
2. 2. After power point opens in the window select the File menu and open
a Blank presentation (Ctrl-N).
Type any Company document as an example shown in the Figures.

Figure 2.1. Power Point Slide Design with Company Introduction

53. Ms-Office --- Ms-Power Point 48/52

Figure 2.2 Company Awards details
3. For the Design tip of the Slide Mainly to insert the text we should insert
the Text box so select Insert-Text Box form Menu. Finally type data for
the slide
4. We can change color click font color option from drawing tool bar. Now
appearing color window, Change as per choice.
5. Next step is to set the background design for the slide so to insert the
background press the option Format-Slide Design option.
6. Else we want to set the Plain Background select Format-Background
option where we can change the color for the Slide Background refers
Figure 1.7.
7. After Giving the Design Main operation is to insert the Picture so to
insert select Insert-Picture-Clipart or File. After selecting the Picture
insert into the slide align as per the requirement.
8. More Pictures are available in Clipart template if we want to insert the
company logo, employee photo etc such thing can be inserted from the
file selection option
9. Give the appropriate picture according to the slide heading content.


Figure 2.3 Insert Clip Art option–Clip Art Pictures are displayed.
10. Finally all the simple slide design is completed at last to attract the
viewers we can add animation to the slide. To animate select menu Slide
show–Custom animation option.
11. After choosing the animation we can give any animation for the data,
text, picture, & slide. Every animation can be given as per the designers
view of point for example we can give emphasis while text enter the
show else we can give exit glow when text exit the show.

54. Ms-Office --- Ms-Power Point 49/52
12. Give the animation for words also which will attract the viewer for
example we can move every word by word. And slide transactions also
given to the slide refer Figure 1.8
13. We can also use the Sound for the slide to insert select Custom
Animation-blind options (Right click)-Effect options–sound option.
14. review all the design of the slide using the slide show option in the
custom animation if any correction needed in timing give according to
the slide transition
15. Main action in this Attribute is to insert the Table and Graph for the
presentation, so to insert the Table Select Insert-Table-choose the no of
row and columns.

Figure 2.4 Insert Table in the Slide.

Figure 2.5 Insert Table in the Slide.
16. After inserting the data in to the table align the table using the Tables
and Borders toolbar.
17. Give any animation for the Table also using the Custom animation
effects.
18. Next step of the Attribute is to insert the Graph Chart in the slide show
using the sales for the five years

55. Ms-Office --- Ms-Power Point 50/52
19. Select the Insert-Chart-continue as per the selection of the table. A
separate worksheet will be appears in the slide input the data in to the
sheet and form the chart using Chart option

Figure 2.6 Insert Chart in the Slide.

Figure 2.7 Insert Chart – Alter the Chart using Chart Options
20. By using the Chart option we can give Gridlines, Axes, Legends, and
Data label, table.

56. Ms-Office --- Ms-Power Point 51/52

Figure 2.8 Chart Final Outputs in the Slide
21. At last review the Chart in the Slide as shown in the Figure 2.8.
22. In advance we can use 3d chart animation by right clicking the chart else
we can change the chart type using Chart menu, select any type of chart.

Figure 2.9 Chart Formatting using 3D animation

Figure 2.9(a) Chart Formatting using 3D animation
23. For Chart also we can give the animation by using the Custom
animation. According to the designer point of view.

57. Ms-Office --- Ms-Power Point 52/52
24. Finally correct all the data to be furnished up to end of the Slide check
proper outputs using slide show.


25. Correct the Errors if any at last, Save the file in the filename.*.ppt
format else we want to display only the slide show Save the file as
PowerPoint show *.pps extension.

58. Ms-Office --- Ms-Power Point 53/52



Result:

Hence all the attributes as mentioned above are shown with the proper Outputs
in Ms-PowerPoint using all the tools such as chart, table and picture etc.

59. Ms-Office --- Ms-Access 53/69
Ms-Office --- Ms-Access

Activity: I
Attribute:
Create a table for storing marks of 10 students. The fields of table should
satisfy the following constrains.
RegNo. Name, Mark1, Mark2, Mark3, Test Average Assignment, Seminar,
Total Marks (Test Average + Assignment + Seminar). The Field ‘Mark1’,
‘Mark2’, ‘Mark3’ should not allow the user to enter a mark greater than 25
and should display proper message in such case. Similar constraints for the
field ‘Assignment’ are 5 Marks and for the field ‘seminar’, it is 10 Marks.
Steps:
1. In windows Operating system select the Program in start menu drag to
Ms-office and select MS-Access. Else select Run in start menu type
“msaccess”
2. After database opens in the window select the Blank database as per the
Figure 1.1 Cltr-N

Fig 1.1 Access Excel document data given sample
3. Then give the database name as per the data we going to use in the table.
4. Take the Design view of the table as per the Figure 1.2.
5. Create the table data as per the attribute given for example Regno.,
mark1, mark2…etc.
6. Main attribute given that Marks should not exceed value 25 so to satisfy
the constraint we have to use the Validation rule in table creation as
shown in the Figure 1.3 then give the Validation Text also, Same way
the Assignment and Seminar marks constraints should be satisfy the <=5
& <=10 respectively.

60. Ms-Office --- Ms-Access 54/69

Fig 1.2 Access Table document Design view

Fig 1.3 Access Table Design view with Constraints
7. After creating the table enters the data in the table using insert value,
mark1, mark2…etc. Refer Fig 1.4

61. Ms-Office --- Ms-Access 55/69

Fig 1.4 Access Table view with Constraints
8. Next select the queries tab write the queries for Test average, Total
marks. Create queries as shown in Fig 1.5.

Fig 1.5 Selecting Queries Design View
9. Create the queries using the Design view insert the Table as shown
Click the Show Table properties in the Figure 1.6

Fig 1.6 Show Table view in query

62. Ms-Office --- Ms-Access 56/69

Fig 1.6(a) Show Update view in queries
10. Insert the Table and insert the value we want to execute the queries for
example Total mark Write In the Update to field as (Testavg +
assignment + seminar) then save the query using proper name and
execute(Run) it for the data base.
11. Finally view the table then all the Data’s are updated according to the
Constraints.













Result:
Hence all the requirements as mentioned above are shown with the proper
Outputs using Ms-Access Queries and Tables.

63. Ms-Office --- Ms-Access 57/69
Activity: II
Attribute:
Create a table for showing names of authors of at least 10 different books, title
of books, the prices of these books, names of publishers and year of
publication. Also create select, Action and Cross-tab queries to display the
records from this table meeting the criteria used in these queries.
Steps:
1. In windows Operating system select the Program in start menu drag to
Ms-office and select MS-Access. Else select Run in start menu type
“MS-Access”
2. After database opens in the window select the Blank database as per the
Figure 1.1 Cltr-N.
3. Create an two table as Book & Book1 table where Book contain Bookid,
Title, Publisher Name, Price, Copyright year. Then for table Book1 with
data Bookid, author where Bookid is a Primary key to connect the two
tables.
4. Here we take some queries to satisfy our constraints such as:
 Select all data from Book & Book1 table for display
 Input the Bookid and display the two table data merged display.
 Create a cross tab query using the publisher Name. (Using
Wizard).
5. Select the query option in the Access as shown in the Figure 2.1.

Fig 2.1 Showing Query Selections in Access
6. To insert the query we use the wizard option in the queries option. This
follows the steps as per the figure.
7. In Query wizard Next step is to select the Table name, Detail query,
Style and finally give the name for the form in the field.
8. Whether we need to open the data directly select the respective option,
else if we need to alter the design select that option which would allows
as modifying the design of the Query.

64. Ms-Office --- Ms-Access 58/69

Fig 2.2 Query Wizard–selection of Table

Fig 2.3 Query Wizard–Detail summary Table
9. Then create the Query to satisfy the attribute “to select all the data from
the Book and Book1”, so for that write the query in SQL View as
SELECT Books.BookID, Books.Title, Books.PurchasePrice,
Books.PublisherName, Books.CopyrightYear
FROM Books;
SELECT Books1.BookID, Books1.Author
FROM Books1;

65. Ms-Office --- Ms-Access 59/69

Fig 2.4 Query Wizard–Query Name & selection of Action
10. Next option for the Query to Link the table as Book & Book1 so for that
SQL View Query is
SELECT Books.BookID, Books.Title, Books.PurchasePrice,
Books.PublisherName, Books.CopyrightYear, books1.Author
FROM Books, books1
WHERE (((Books.BookID)=[books1].[bookid] And
(Books.BookID)=[id]));
11. Else simply we want to create thro the wizard in a normal Design view
below the column Bookid in criteria write simply as [books1].[bookid]
And [id]. Refer the Figure 2.5

Fig 2.5 Query Wizard–Linking Two tables
12. Execute the Query Bookid will be asked by the query enter your book id
then all the details of the book from the two tables will be published in
the sheet.
13. Finally we want to use the Cross tab Query it is very easy to use by the
wizard, so follow the Figure where we want to display the NO OF
Publisher.

66. Ms-Office --- Ms-Access 60/69
14. Create New query select the Cross tab query wizard option in the
selection list. Continue the selection as table name, Row heading (least
3), Column heading, and Calculate value.

Fig 2.5 Cross tab query Wizard

Fig 2.5 Cross tab query Wizard – Table selection
15. Finally give the name for the query.
16. Whether we need to View the data directly select the respective option,
else if we need to alter the design select that option which would allows
as modifying the design of the Query.

67. Ms-Office --- Ms-Access 61/69

Fig 2.5 Cross tab query Wizard – Row Value selection

Fig 2.5 Cross tab query Wizard – Column Value selection

Fig 2.6 Cross tab query Wizard – Value Count selection

68. Ms-Office --- Ms-Access 62/69

Fig 2.7 Crosstab Wizard–Name & Operation Selection
17. Finally give the name for the query in the wizard step.
18. After execute the query the final data output will be as Figure 2.8.

Fig 2.8 Crosstab Wizard–Output
Result:
Hence all the requirements as mentioned above are shown with the proper
Outputs using Ms-Access Queries & Cross tab query.

69. Ms-Office --- Ms-Access 63/69
Activity: III
Attribute:
Create a Form to enter the data directly in to this form. The fields required are:
Basic pay, DA, HRA, Gross Sal, PF, Income tax and net salary.
Steps:
1. In windows Operating system select the Program in start menu drag to
Ms-office and select MS-Access. Else select Run in start menu type
“MS-Access”
2. After database opens in the window select the Blank database as per the
Figure 1.1 Cltr-N.
3. Create an Employee detail table Using Basic pay, DA, HRA, Gross Sal,
PF, Income tax and net salary as per the guidance of Activity I.
4. For creating the Form in Access easy way to handle the data is using the
wizard option more than using design view.
5. Click the Wizard option in Form and follow the steps by selecting the
table name and other options as per the Figure.

Fig 3.1 Form Wizard–selection of Table
6. In form wizard Next step is to select the Layout, Style and finally give
the name for the form in the field.
7. Whether we need to input the data directly select the respective option,
else if we need to alter the design select that option which would allows
as modifying the design of the form.
8. Finally give finish option. A new form is created for the Employee
details which would help as for enter the data in the form directly.
9. Mainly we use the Form entry option which will be very useful for the
end user of the database.

70. Ms-Office --- Ms-Access 64/69

Fig 3.2 Form Wizard–selection of Layout

Fig 3.3 Form Wizard–selection of Style

Fig 3.4 Form Wizard–Title name & selection wizard
10. At last Form is displayed in the screen as per the design of the
developer as shown in the figure we can directly insert the data and
update the data in the table.

71. Ms-Office --- Ms-Access 65/69

Fig 3.5 Form Output–End user entry
11. If any calculation like DA, HRA, and Net-salary etc are calculated using
the queries where these queries are linked in the command button for
example “DA Updating”, “Net Salary”.
12. Hence all the Rows are updated in the table and form is displayed as per
our attribute.



Result:
Hence all the requirements as mentioned above are shown with the proper
Outputs using Ms-Access Forms & Queries.

72. Ms-Office --- Ms-Access 66/69
Activity: IV
Attribute:
Create a Report that displays the customer name, address, Phone number, Item
code, Product quantity of the customers whose orders have been pending for
over a month.
Steps:
1. In windows Operating system select the Program in start menu drag to
Ms-office and select MS-Access. Else select Run in start menu type
“MS-Access”
2. After database opens in the window select the Blank database as per the
Figure 1.1 Cltr-N.
3. Create a Customer detail table Using Customer name, address, and
Phone number, Item code, Product quantity as per the guidance of
Activity I.
4. Then create the Query to satisfy the attribute “to select the pending
Order below one month”, so for that write the query in SQL View as
SELECT Customers4.CustomerID, Customers4.CustomerName,
Customers4.BillingAddress, Customers4.City,
Customers4.StateOrProvince, Customers4.PostalCode,
Customers4.PhoneNumber, Customers4.ProductID,
Customers4.Quantity, Customers4.Date
FROM Customers4
WHERE (((Customers4.Date)<=CDate("30-jan-08")));
5. Else we want to create thro the wizard in a normal Design view below
the column Date in criteria write simply as <=CDate("30-jan-08").
Refer the Figure 4.1

Fig 4.1 Query Design View–Date criteria
6. After writing the query Execute the query that will display the data’s as
per our attribute.
7. Now comes to create the Report easy way to handle the data is using the
wizard option more than using design view.
8. Click the Wizard option in Form and follow the steps by selecting the
Query name and other options as per the Figure.

73. Ms-Office --- Ms-Access 67/69

Fig 4.2 Report Wizard–selection of Query
9. In form wizard Next step is to select the Level, Sorting order, Layout,
Style and finally give the name for the form in the field.
10. Whether we need to View the data directly select the respective option,
else if we need to alter the design select that option which would allows
as modifying the design of the Report.
11. Finally give finish option. A new Report is created for the Customer
details which would help as to View the data in the Report directly.

Fig 4.3 Report Wizard–selection of Levels of Data

74. Ms-Office --- Ms-Access 68/69

Fig 4.4 Report Wizard–Sorting Order

Fig 4.5 Report Wizard–Layout Order

Fig 4.6 Report Wizard–Style Selection

75. Ms-Office --- Ms-Access 69/69

Fig 4.6 Report Wizard–Name & Operation Selection

12. At last Report is displayed in the screen as per the design of the
developer as shown in the figure.

Fig 4.7 Report Output







Result:
Hence all the requirements as mentioned above are shown with the proper
Outputs using Ms-Access Reports & Queries.