Bangalore University

Computer Lab
OFFICE MANAGEMENT TOOLS

Course Description:
This course is a hands-on introduction to the personal computer and application software. Give students
an in-depth understanding of why computers are essential components in business, education and society.
Provide hands-on use of Office applications Word, Excel, Access and PowerPoint. Provide foundational
or “computer literacy” curriculum that prepares students for life-long learning of computer concepts and
skills. Topics include types of data, data cleaning, recoding and sorting, data visualization, summarizing
data and an introduction to analysis of relationships between variables.

Evaluation Scheme :
 25 Marks – Internal Assessment
o Internal Test [ Word (5M ) + Excel (5M) + Power Point (5M) + Access (5M) ] = 20 Marks
o Record = 5 Marks
o Total = 20 + 5 = 25 Marks
 25 Marks – Examination
o Execution of ONE Problem from PART-A => 10 Marks
o Execution of ONE Problem from PART-B => 10 Marks
o Viva-verse = > 5 Marks
o Total => 25 Marks

PART-A – [ WORD + POWER POINT ]

WORD:
1. Create and Design an Advertisement with :
(a) page border, content border and add patterns
(b) write beautiful text in it and then change the font, style, color, and size of each sentence.
(c) design the caption of the Advertisement using Word Art.
(d) Insert images

 Open MS Office  MS Word 2016  File New Document.

 Type the require contents in the document area about 4 pages in paragraphs & with pictures.
 Click the Design tab from Menu bar and insert the page border.
 Click the Home tab from Menu bar select Borders and click the Borders and Shading option.
 Click the Design tab from Menu bar in the Page Background click Background. Point to the background
that you want, and then do one of the following:
 To add the color or pattern to only the current page, click the color or gradient you want.
 To add the color or pattern to all pages, right-click the color or gradient and then click Apply to All Pages.
 Click the Home tab from Menu bar and do the changes to the paragraphs:
 Change the font.
 Change the font style.
 Change the font colour.
 Change the font size.
 Click Insert tab from Menu bar and insert the following:
 WordArt
 Picture
  Click on save from file tab and specify the name of the document and also choose the drive/folder to be
saved.
 Preview & Close the document.

2. Create and Design PROJECT REPORT template

(e) Index Page
(f) Front Page
(g) Certificate
(h) Create links between Index page to Front page and Certificate

 Open MS Office  MS Word 2016  File New Document.

 Type the require contents in the document area about 3 pages in paragraphs.
 Click Reference tab from Menu bar Select insert index option.
 Click Insert tab from Menu bar Select the Cover Page option.
 Click Page Layout tab from Menu bar Page Setup group Select Orientationthen
choose Landscape. Click Design tab from Menu bar Select Page BordersSelect either
a Style or Art, assign a size and color, then select the Box icon. Choose Ok to see the result. Click
Insert tab from Menu bar Select Text Box option  then right click and customize the appearance of
the font styles, sizes, and colors as desired. Save the changes to the custom template.
 Select text or an item, or click where you want to insert a bookmark. Click Insert tab from Menu
bar Bookmark option and then type a name in Bookmark name sectionClick Add.
Select the text to which you want to apply a heading style.Click Home tab from Menu bar Select any
one Font Style.
Select the text or object you want to use as a hyperlink. Right-click and then click HyperlinkUnder
Link toclick Place in This Document In the list select the heading or bookmark that you want
to link to.
 Click on save from file tab and specify the name of the document and also choose the drive/folder to be
saved.
 Preview & Close the document.

3. Create and Design

(i) Document with Drop cap (write 1 paragraph)
(j) Insert the watermark with company name in the document
(k) Organization chart of a company
(l) Add Quick part – Document property insert company – company address – email – fax – phone

 Open MS Office  MS Word 2016  File New Document.

• Type the require contents in the document area about in 1 paragraph.
 Click Insert tab from Menu bar Select the Drop cap option Dropped. (To create a drop cap that fits
within your paragraph). Click Insert tab from Menu bar Select the Drop cap option In margin(To
create a drop cap that is in the margin, outside of your paragraph).
 Click Design tab from Menu bar Watermark optionCustom WatermarkText Watermark.
 Click Insert tab from Menu barChart option Select the any one of the chart.
 Click Insert tab from Menu barQuick PartsDocument property and insert the following options:
 Company
 Company address
 Company email
 Company fax
 Company phone
 Click on save from file tab and specify the name of the document and also choose the drive/folder to be
saved.
  Preview & Close the document.

4. Mail merge
(m) Create 5 student record in Excel sheet (Regno, studname, mark-1, mark-2, mark-3, total, grade)
(n) Create a marksheet template (college name, department name, marksheet table, signature)
(o) Use header and footer to create College name – Department name – Page number
(p) Mail merge excel with marksheet template.

 Open MS Office  MS Excel  File  New Blank Worksheet.

 Select few columns and few rows at the beginning  right click  Format cells  click select
alignment tab  Tick merge cells options  ok  Type the heading.
 Enter the column headings & enter at least 10 data’s of the columns manually like Regno, studname,
mark-1, mark-2, mark-3, total, grade. Rename the sheet1.
Total=SUM(C3:E3)
Grade=IF(F3>=255,"A",IF(F3>=225,"B",IF(F3>180,"C",IF(F3>=120,"D","FAIL"))))

 After writing each formula, select the cell and drag to the entire column to apply.
 Click on save from file tab and specify the name of the Excel and also choose the drive/folder to be
saved.
 Preview & Close the Excel.

 Open MS Office  MS Word 2016  File New Document.

 Type College name and Department name
 Click Insert tab from Menu barTableSelect rows=2 and columns =7.
 Click Insert tab from Menu barSignature LineMicrosoft Office Signature LineSigner details.
 Click Insert tab from Menu bar and insert the following:
 Headers & Footers (College name and department name).
 Page Number
 Click Mailings tab from Menu barMailingsSelect RecipientsUse Existing ListSelect Excel sheet
name: Markssheet and click on open. The fields along with student details will be considered as recipients if
need we can edit the recipients based on your requirements. Select Insert Merge Fieldinsert the fields in the
table one after anotherPreview Results to preview your document.
 Click on complete the merge and click edit individual letters opens popup window of the merge a
new document, where you can make changes to all the letter if needed. Select all in merge records and
click ok.
 Save the mail merge document.

Note: The cell name can vary from excel to excel.

 5. Explore File->options
(q) Change office theme (File->options->General -> office theme-> Dark gray-> ok).
(r) Change space representation (File->options->display->check tab character & spaces)
(s) Check spelling & grammar proofing (File->options->proofing->spelling & grammar)
(t) Quick access tool (File->options->Quick access->find) inserts search icon in quick access tool

 Open MS Office  MS Excel  File  Options and Select General option for working with word in
that personalize your copy of Microsoft Officechange the office theme color from white to gray.
 File  Options and Select Display optionSelect Tab character and space.
 File  Options and proofing optionSelect Spelling and grammar.
 File  Options and Quick Access ToolbarSelect find option and click ok.

POWERPOINT :
6. Create a Power point Presentation that explains:
(a) Dream Project / Idea
(b) An activity you enjoy
(c) An event in your life that changed you or impacted your life.
(d) My Graduation Day

Slides Requirements:

1. First Slide: Title/Introduction

Create a title. Give it a larger and non-default font. Make it look important.
o
Include the following subtitles:
o
 Your full name. Make the text smaller than the title. Make it centered.
 Your Student ID number. Make it italicized.
 The current date.
2. Second Slide: Picture and Text
o Insert a picture.
o Insert a text box, with a couple sentences describing the picture.
3. Third Slide: Chart
o Insert a chart that illustrates your ratings of a specific topic.
o Insert a text box, with a couple sentences describing the chart.
4. Fourth Slide: List
Make a list, with sub-bullets. For example:
o
 Here is the first item of the list.
 Here is the second item of the list.
 Here is the third item of the list.
 Here is the first sub-bullet of the third item.
 Here is the second sub-bullet of the third item.
 Here is the fourth item of the list.
o Sub-bullets are created by hitting the [Tab] key on your keyboard before writing a new
item. To reverse a sub-bullet, hit [Shift]+[Tab] on your keyboard before writing a new
item.
5. Extra Effort
 Check for spelling or grammar mistakes.
 Use slide transitions.
 Use animations, with good timing.
  Insert a hyperlink.
 Insert a table.
 Insert multimedia (without going over 20MB file size limit).
 Be impressive and creative!

 Open MS Office  MS PowerPoint Presentation  File  New PowerPoint Presentation.

Slide 1:

 Give the title Increase the font size and use non-default font style for title.
 Give your Name, Student ID number and current date in subtitle
 After typing Your name and change the font size such that it must be less then font size of title and
alignmentcenter.
 After typing Student ID Number change font style to italic.
 Click Insert tab from Menu barSelect Date and Time option and select the format.

Slide 2:

 Click on New Slide in Home Tab and type the contents in the sub title and text area.
 Repeat the previous steps to Insert Slides.
 Click on Insert tab to insert pictures and photos to the slides.
 Click on Insert tab to insert Text box and Type few sentences related to the picture.

Slide 3:

 Click on New Slide in Home Tab and type the contents in the sub title and text area.
 Repeat the previous steps to Insert Slides.
 Click on Insert tab to insert chart based on your rating on the topic.
 Click on Insert tab to insert Text box and Type few sentences related to the chart.

Slide 4:

 Click on New Slide in Home Tab and type the contents in the sub title and text area.
 Repeat the previous steps to Insert Slides.
 Click on Home tab to insert bullets based on the topic and for fourth point add sub-bullets from home
tab.

Slide 5:

 Click on New Slide in Home Tab and type the contents in the sub title and text area.
 Repeat the previous steps to Insert Slides.
 Click on Review tab and select spelling to check spelling and grammar.
 Select the slide to apply transitions from menu bar.
 Apply transitions for all the slides.
 For each transitions select duration and tick on mouse click.
 Click on the text area to apply animations from menu bar in the slide.
 Apply animations for each slides.
 For each animation select duration and select start on click.
 Click on Insert tab to insert hyperlink based on the topic.
 Click on Insert tab to insert Table based on your rating on the data.
 Click on Record tab and select video and start recording the video(video size is 20MB)
 Click on slide show tab in menu bar and click from beginning or F5 key in the keyboard.
  Click on save from file tab and specify the name of the excel and also choose the drive/folder to be
saved.
 Close the PowerPoint.

PART-B – [ EXCEL + ACCESS ]

EXCEL :
1. Create a worksheet and enter the following data as below :
Reg.no Name Mark-1 Mark-2 Mark-3 Total Percentage Grade
101
102
103
104
105

(a) Compute the total marks and percentage of each student by entering appropriate formula.
(b) Compute grade based on the following criteria :
a. Percentage >=90 Grade = ‘A’
b. Percentage >=80 && Percentage < 90 Grade=’B’
c. Percentage >=70 && Percentage < 80 Grade=’C’
d. Percentage >=60 && Percentage < 70 Grade=’D’
e. Percentage < 60 Grade = ‘E’

(c) Apply conditional formatting for Grade. Indicate each grade with different font color.
(d) Draw a Pie chart depicting % of Grade.

 Open MS Office  MS Excel  File  New Blank Worksheet.

 Select few columns and few rows at the centre of the beginning  right click  Format cells  click
select alignment tab  Tick merge cells options  ok  Type the heading.
 Enter the column headings & enter at least 10 data’s of the columns manually like sl.no, names, reg no,
marks of different subjects, total, percentage, result, class.
 Rename the sheet1.
 In Home tab change the font size, font, font colour, format the cells by using alignment (move the table
to Column A if needed).
 Enter the following formula to calculate the respective values.
Total = SUM(D5:G5)
Percentage = H5/4
Result = IF(AND (D5>=35, E5>=35,F>=35,G>=35),”PASS”, “FAIL”)
Class =IF(J5=”PASS”, IF(I5>=75,”DISTINCTION”, IF(I5>=60,”FIRST CLASS”, IF(I5>50,”SECOND
CLASS”, IF (I5>=35,”PASS”)))),”FAIL”)
 After writing each formula, select the cell and drag to the entire column to apply.
 Go to Insert  chart  bars  select the chart type  custom type  select the type  next  select
column  next  under title give the heading at the chart title  under axis choose the primary axis
 under legends choose the placement  under data labels choose value  next  select the place
chart  finish.
 Click on save from file tab and specify the name of the excel and also choose the drive/folder to be
saved.
  Preview & Close the excel.

2. A Company records the details of total sales (in Rs.) sector wise and month wise in the followingformat :
Sector / Month Jan Feb Mar April
Sector 30 12000 17000 14000 15000
Sector 22 14000 18000 15000 16000
Sector 23 15000 19000 16000 17000
Sector 15 16000 12000 17000 18000

(a) Enter the data in a worksheet and save it as sector.xls

(b) Use appropriate formula, calculate total sale for each sector & each month
(c) Create a 3-D column chart to show sector wise data for all four months.
(d) Create a 3-D pie chart to show sales in Jan in all sectors.

 Open MS Office  MS Excel  File  New Blank Worksheet.

 Type the contents of the table in each cell
 Format the table by selecting appropriate borders.
 Center align the column headers and right align the amounts. For description the alignment should be
left.
 For calculating ‘Total Sales’ use “=SUM” formula in the far-right column after April and name the
column as “Total Sales”
 Go to Insert  chart 3D chartinsert the chart of your choice (sector wise data for all four months)
 Go to Insert  chart 3D chartinsert the pie chart of your choice for only Jan month.
 Click on save from file tab and specify the name of the excel and also choose the drive/folder to be
saved.
 Preview & close the excel.

3. Create PETTY CASH Expenses details

Dates Postage Coffee/Tea Cleaning Stationery
January 13.5 11 14 15.75
7.65 2.5 14 17.38
19.38 3.45 14 20.75
9.23 4.15 17 9.5
11.68 2.17 17 12.45

(a) Create the above table and save the worksheet as cash.xls
(b) Insert new row 1 and type the heading: PETTY CASH EXPENSES.
(c) Copy the data series down the first column
(d) Add a new row Totals and enter a formula to calculate total postage costs. Copy this formula across
the row to produce totals for the other items.
(e) Adjust column widths and format the column headings to bold, font size 14. Format all the numerical
data to currency (₹).
(f) Save as “Office.xls”
(g) Insert a new column after Cleaning header ‘furniture’ and enter ₹385 for March and ₹37.99 for May
.Calculate the total for Furniture.
 (h) Make the following changes:
a. Cleaning in April is now ₹14
b. Coffee/Tea cost ₹3.50 in February
c. The Stationery heading is now read Stationary/Disks
d. Merge and Center the worksheet heading across all the data columns. Format the heading with
a blue background, bold and font size 20.
(i) Save the changes.
 Open MS Office  MS Excel  File  New Blank Worksheet.
 Type the contents of the table in each cell
 Format the table by selecting appropriate borders.
 Center align the column headers and right align the amounts. For description the alignment should be
left.
 Click on save from file tab and specify the name of the excel as cash.xls.
 Right clickInsert rows and Center align the row headers as PETTY CASH EXPENSES.
 Right click on the cellCopy the data series of the first column.
 Use SUM formula and select the data set to be totaled. Copy this formula (Ctrl+C) and paste it under
each row beneath the data. Name this row as ‘Total Package Cost”
 Select all the Columns with data and go to Home FormatAutoFit Column Width
 Select Column Heading and select Font style as ‘Bold’ and Font Size as 14
 Select the data set and press Ctrl+Shift+4
 Click on save from file tab and specify the name of the excel as office.xls.
 Select column with header ‘Furniture’. Right click and select ‘Insert Column’. Name the column as
“Furniture”. Enter ₹385 for March and ₹37.99 for May. Use SUM formula and select the Furniture
column and press ‘Enter’.
 Go to Column ‘Cleaning’, select cell for ‘April’ and edit the value to ₹14
 Go to Column ‘Coffee/Tea, select cell for ‘February’ and edit the value to ₹3.50
 Go to Column ‘Stationary, edit the header name to Stationary/Disks
 Select the headers and go to HomeFormatFill Color (Use Blue)
 Select Column Heading and select Font style as ‘Bold’ and Font Size as 20
 Click on save from file tab and specify the name of the excel and also choose the drive/folder to be
saved.
 Preview & close the excel.

3. Calculate Cumulative Frequency for the given data and generate Scatter graph.
Item Price Frequency
1-10 20
11-20 21
21-30 13
31-40 8
41-50 4
a. Create the above table and save as CF.xls
b. Insert a new column Cumulative Frequency
c. Calculate Cumulative Frequency using appropriate formula
d. Select Item Price and Cumulative Frequency Column only and generate Scatter Graph.

 Open MS Office  MS Excel  File  New Blank Worksheet.

 Type the contents of the table in each cell
 Format the table by selecting appropriate borders.
  Center align the column headers and right align the amounts. For description the alignment should be
left.
 Select the entire Column after Frequency. Right clickInsert Columns and name it as ‘Cumulative
Frequency’
 In first Row of ‘Cumulative Frequency’ column enter the first value in Frequency column
 In Second Row of ‘Cumulative Frequency’ column add the first value in ‘Cumulative Frequency’ with
Second Value in ‘Frequency’ column. Copy the formula throughout the succeeding column cells till
end
 Select only ‘Item Price’ and ‘Cumulative Frequency’ columns and go to InsertScatter Chart
 Click on save from file tab and specify the name of the excel and also choose the drive/folder to be
saved.
 Preview & close the excel.

4. Worksheet to demonstrate Statistical functions :

Function Purpose Syntax
MIN Returns the minimum value in a list of MIN(number1, [number2], ...)
arguments
MAX Returns the largest value in a set of values. MAX(number1, [number2], ...)
COUNT The COUNT function counts the number of COUNT(value1, [value2], ...)
cells that
contain numbers, and counts numbers within
the list of arguments
COUNTIF The COUNTIF function counts the number COUNTIF(range, criteria)
of cells
within a range that meet a single criterion
that you specify.
COUNTA The COUNTA function counts the number COUNTA(value1, [value2], ...)
of cells that are not empty in a range (range:
Two or more cells on a sheet. The cells in a
range can be adjacent or
nonadjacent.).
STDEV The standard deviation is a measure of how STDEV(number1,number2,…..)
widely
values are dispersed from the average value.
MEAN Returns the geometric mean of an array or GEOMEAN(number1, [number2], ...)
range of positive data

Returns the harmonic mean of a data set. The HARMEAN(number1, [number2], ...)
harmonic mean is the reciprocal of the
arithmetic mean of reciprocals.
MODE Returns the most frequently occurring, or MODE(number1,number2,….)
repetitive,
value in an array or range of data.
MEDIAN Returns the median of the given numbers. MEDIAN(number1, [number2], ...)
The median is
the number in the middle of a set of
numbers.
VARIANCE Calculates variance based on the list of VAR(number1,[number2],...])
values

CORRELATION Returns the correlation coefficient of the CORREL(array1, array2)

array1 and
array2 cell ranges.
PERCENTILE Returns the k-th percentile of values in a PERCENTILE(array,k)
range, where k is in the range 0..1, exclusive.

QUARTILE Returns the quartile of the data set, based on QUARTILE(array,quart)

percentile values from 0..1, exclusive. IF quart 0-> min, 1->first quartile, 2-
>second quartile, 3->third quartile, 4-
>max
  Open MS Office  MS PowerPoint Presentation  File  New PowerPoint Presentation.
 Type the contents of the table in each cell
 Format the table by selecting appropriate borders.
 Center align the column headers and right align the amounts. For description the alignment should be
left.
 Type the above given formulas in the cell.
 Click on save from file tab and specify the name of the excel and also choose the drive/folder to be
saved.
 Preview & close the excel.

ACCESS :
5. Illustrate Access Database and its operations
a) Create a new database, save it as “School database” on desktop
b) Create a table in the School database with the following :
Field Name Data Field size /
type format
ID Number Text 10
Name Text 15
Surname Text 15
Telephone Number Long Integer
number
Date of Birth Date / Medium Date
Time
Stipend Currency Currency
Foreigner Yes/No Yes/No
c) Make “ID Number” field as Primary Key.
d) Save the table as “student” and Return to main Access window.
e) Open student table enter 5 complete records
f) Sort the table in ascending order by surname.
g) Delete the last record you have entered.
h) Change the field size of “Surname” to 20.
i) Create a Form and make ID Number of each student, Red
j) Insert Picture in the form
k) Create a report based on the student table showing fields Name, Surname and Telephone number
l) Save the report as Telephone List
m) Create a query, showing all fields of those students who have particular surname.
n) Create a query showing all fields of those students who has born after 2000.
o) Create a query showing only the Name, Surname and Date of Birth.

 Open MS Office  MS Access  File  New Blank Database click in the File Name text box and
type a descriptive name as ‘School database’ for your database. Click the Create button to create your
database file. Access displays a blank database.
 Click Home tab from Menu barViewDesign view and type the above contents. Select the ID
Number field and select Primary key option from the View tab in Menu bar. After entering each field
name below a field properties table appears where we need to specify Field size.
 Right click on eg: table1 on the screen and save as student. Return to main access window and double
tap on student table and enter the student details in the table.
 Select the Surname column. Next to surname column a down arrow is present select it. A pop up list
appears select ascending order(Sort A-Z).
 Right click on the edge of the row and select ‘delete record’.
 Again, select Home tab from Menu barViewDesign view and type the above contents. Select the
field name for which we need to change the Field size and type the new Field size.
 Click Create tab from Menu bar Form. Click Home tab from Menu barStudent ID NumberFont
colorred.
 Form Layout Design Insert image.
 Click Create tab from Menu bar Report. Report must contain Fields like Name,Surname and
Telephone number.
 Right click on eg: table1 on the screen and save as Telephone List.
 Click Create tab from Menu bar Query wizard. A pop up window appears there select Simple Query
WizardOk. There select Field:SurnameNextFinish.
 Again, Click Create tab from Menu bar Query wizard. A pop up window appears there select Simple
Query WizardOk. There select Field: Date of BirthNextModify Query DesignFinish Date
of Birth Property Table CriteriaBorn after 2000Query DesignRunSave your
Query.ViewDatasheet view.
 Click Create tab from Menu bar Query wizard. A pop up window appears there select Simple Query
WizardOk. There select Field: Name, Surname and Date of Birth NextFinish.
 Click on FileSave.