Page 1 :
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, 2. Create and Design PROJECT REPORT template, (a) Index Page, (b) Front Page, (c) Certificate, (d) Create links between Index page to Front page and Certificate, 3. Create and Design, (a) Document with Drop cap (write 1 paragraph), (b) Insert the watermark with company name in the document, (c) Organization chart of a company, (d) Add Quick part – Document property insert company – company address – email – fax – phone
Page 2 :
4. Mail merge, (a) Create 5 student record in Excel sheet (Regno, studname, mark-1, mark-2, mark-3, total, grade), (b) Create a marksheet template (college name, department name, marksheet table, signature), (c) Use header and footer to create College name – Department name – Page number, (d) Mail merge excel with marksheet template., 5. Explore File->options, (a) Change office theme (File->options->General -> office theme-> Dark gray-> ok., (b) Change space representation (File->options->display->check tab character & spaces), (c) Check spelling & grammar proofing (File->options->proofing->spelling & grammar), (d) Quick access tool (File->options->Quick access->find) inserts search icon in quick access tool, , 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, o, , Create a title. Give it a larger and non-default font. Make it look important., Include the following subtitles:, ▪ 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., o, , 4. Fourth Slide: List, o, , Make a list, with sub-bullets. For example:, ▪ 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.
Page 3 :
▪, ▪, ▪, ▪, , Insert a hyperlink., Insert a table., Insert multimedia (without going over 20MB file size limit)., Be impressive and creative!, , 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, 101, 102, 103, 104, 105, , Percentage Grade, , (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., 2. A Company records the details of total sales (in Rs.) sector wise and month wise in the following, format :, 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), (b), (c), (d), , Enter the data in a worksheet and save it as sector.xls, Use appropriate formula, calculate total sale for each sector & each month, Create a 3-D column chart to show sector wise data for all four months., Create a 3-D pie chart to show sales in Jan in all sectors., , 3. Create PETTY CASH Expenses details, Dates, Postage, Coffee/Tea, January, 13.5, 11, 7.65, 2.5, 19.38, 3.45, 9.23, 4.15, 11.68, 2.17, , Cleaning, 14, 14, 14, 17, 17, , (a) Create the above table and save the worksheet as cash.xls, , Stationery, 15.75, 17.38, 20.75, 9.5, 12.45
Page 4 :
(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 headed 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., , 4. 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., 5. Worksheet to demonstrate Statistical functions :, , Function, , Purpose, , Syntax, , MIN, , Returns the minimum value in a list of, arguments, , MIN(number1, [number2], ...), , MAX, COUNT, , Returns the largest value in a set of values., The COUNT function counts the number of, cells that, , MAX(number1, [number2], ...), COUNT(value1, [value2], ...), , contain numbers, and counts numbers within, COUNTIF, , the list ofarguments, The COUNTIF function counts the number, of cells, , COUNTIF(range, criteria), , within a range that meet a single criterion, COUNTA, , that youspecify., The COUNTA function counts the number, of cells that are not empty in a range (range:, Two or more cells on asheet. The cells in a, range can be adjacent or, nonadjacent.)., , COUNTA(value1, [value2], ...)
Page 5 :
STDEV, , MEAN, , MODE, , MEDIAN, , VARIANCE, , CORRELATION, , PERCENTILE, , QUARTILE, , The standard deviation is a measure of how, widely, values are dispersed from the average value., Returns the geometric mean of an array or, range of positive data, , STDEV(number1,number2,…..), , GEOMEAN(number1, [number2], ...), , Returns the harmonic mean of a data set. The, harmonic mean is the reciprocal of the, arithmetic mean of reciprocals., Returns the most frequently occurring, or, repetitive,, value in an array or range of data., Returns the median of the given numbers., The median is, the number in the middle of a set of, numbers., Calculates variance based on the list of, values, , HARMEAN(number1, [number2], ...), , Returns the correlation coefficient of the, array1 and, array2 cell ranges., Returns the k-th percentile of values in a, range, where k is in the range 0..1, exclusive., , CORREL(array1, array2), , Returns the quartile of the data set, based on, percentile values from 0..1, exclusive., , QUARTILE(array,quart), IF quart 0-> min, 1->first quartile, 2>second quartile, 3->third quartile, 4>max, , MODE(number1,number2,….), , MEDIAN(number1, [number2], ...), , VAR(number1,[number2],...]), , PERCENTILE(array,k)
Page 6 :
ACESS :, 6. 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.
