M.A.

J Foundation’s
DHANWANTARI ACADEMY FOR MANAGEMENTSTUDIES
Chikkabanavara,Bengaluru-560090

LABORATORY CERTIFICATE

SUBJECT:Office Management Tools Lab

UUCMS No.: Date:

This is to certify that

Mr. /Miss. , has
satisfactorily completed the course of experiments presented by the Bengaluru University for
BCA Semester II in the Laboratory of this college for the academic year 2023-24.

Valued Examiner

1)

Staffin-charge Principal
2)
SL NAME OF THE EXPERIMENTS PG NO.
NO
SECTION-A
1 Creating the documents with Special effects like underline, bold, different
size, different font and different color.
2 Creating Paragraphs Inserting Date & Time, Pictures, Bullets & Numbering ,
indentation etc. in MS-Word.
3 Formatting features of MS-Word.-it includes paper-size, margins, header and
footer, page no. and creation of table options.(Time Table).
4 Creation of mail merge for sending the new year wish to your class group .
5 Creating Worksheets in Excel, Inserting, Deleting, Copying, Moving
worksheets in Excel Usage of formulas, Built-in functions in Excel.
(Monthly
income and expenditure statement.)
6 Graph-Plotting facilities in MS Excel.(Display student results using excel
charts.)
7 Writing conditional expressions (using IF) and Using logical functions
(AND, OR, NOT) Using lookup and reference functions (Arithmetic
functions,
logical functions,text functions).
8 Data Validations :Specifying a valid range of values for a cell, Specifying a
list of valid values for a cell, Specifying custom validations based on
formula for a cell, Sorting and Filtering Data facility in MS Excel.
(Employees of a
company)
9 Creating a presentation in PowerPoint- Inserting / Deleting slides in
PowerPoint.
10 Creation of Slide transition and Editing special effects in PowerPoint.
11 Creation of Inserting sound and picture in PowerPoint.
SECTION-B
1 Write the procedure to create a personal Letter.(with proper format).
2 Write the procedure to create a resume.(with proper format).
3 Write the procedure to create a letter head of a company.(with proper format).
4 Write the procedure to create a cover page of a project report.(with proper
format).
5 Write the procedure to create a macros in word.
6 Create a Visiting Card of your college using page size as follows
i)Page width=”3.2” ii)Page height=”2.2” And use different font styles, sizes,
alignments
7 Creation of Inserting chart and organization chart in PowerPoint.( Eight
salesmen sell three products for a week.)
8 Write the procedure to store the data and calculate total and percentage of the
following details for 10 Students.( In Ms Access)
9 Write the procedure to store the data and calculate total and percentage of the
following details for 10 [Link] marks card.( In Ms Access
10 Create a report containing pay details of employees of a company.( In Ms
Access).
11 Creation of slides using SMARTART in PowerPoint.
1. Creating the documents with Special effects like underline, bold, different size,
different font and different color.

Procedure
1. Open MS Office-MS Word – File – New - Type the heading

2. Type the topic heading-Bold,increase the size using font tools.

3. Type a paragraph on the above mentiond topic.

4. To insert go to Insert- Picture-From file- and browse for the required picture/file-then click

Insert.

5. Format the text by changing the font size and color by selecting the required text and chose

font size, style and color in the formatting tool bar below the menu.
OUTPUT
2. Creating Paragraphs Inserting Date & Time, Pictures, Bullets & Numbering , indentation
etc. in MS-Word.

Procedure
1. Open MS Office-MS Word – File – New - Type the heading

2. Type the topic heading-Bold,increase the size using font tools.

3. Type few points on the above mentiond topic.

4. Add bullets to the typed points by selecting Bullets from Home toolbar.

5. Apply indentation to the points and sub points.

6. To insert go to Insert- Picture-From file- and browse for the required picture/file-then click

Insert.

7. Format the text by changing the font size and color by selecting the required text and chose

font size, style and color in the formatting tool bar below the menu.

8. To insert date and time go to Insert tab and select date/time and select the required format.
OUTPUT
3. Formatting features of MS-Word.-it includes paper-size, margins, header and footer, page no.
and creation of table options.(Time Table).

Procedure
1. Open MS Office-MS Word – File – New

2. Go to Layout- Select any Margin option you like and– Click Ok.

3. Go to Layout-select paper-size from SIZE.

4. Go to Insert-add header, footer and pageno.

5. Go to Table-Insert-Table- chose Number of Columns and Rows as per the data to be

entered.(Both education qualification and language known)

6. Enter the details, format it (bold and increase the font size via standard tool Bar).

7. When ever you want to increase the number of column in the existing row, Select that

row and go to Table-click Split Cells- enter number of columns- click Ok.

8. In order to decrease the existing column numbers, select that columns and Go to Tables-

click Merge cells.

4. Creation of mail merge for sending the new year wish to your class group.

1. Open MS Office-MS Word – File – New

2. Type your letter which you want to send to the multiple addresses.

3. Go to Tool- Mailings- Click: Starting Mail merge- next Select Recipients- Click Type a

new list- Click Create – Enter the address- to enter one more click New Entry.

4. Add 5 entries and savethe file by your name.

5. Add Greeting line and address block fields and if any other.

6. Then finally in the mail Merge tool bar click Finish & Merge – Edit individual document-

All- Ok- Save the file.

OUTPUT
OUTPUT
5. Creating Worksheets in Excel- , Inserting, Deleting, Copying, Moving worksheets in Excel
Usage of formulas, Built-in functions in Excel.(Monthly income and expenditure statement.)
A housewife maintains the budget expenditure in a spreadsheet under the headings
Income and Expenses. Income includes husband’s and Wife’s income separately under
different headings. Expenses include Rent, Bills, Household expenses and medical
expenses.
7. Type the Income and Expenses data for the entire month in the spreadsheet.
8. Format all numbers as currency.

9. Center the spreadsheet headings across the spreadsheet.

10. Create a formula to compute the Total expenditure and copy this to all the cells.

11. Create a formula to compute the savings and copy this to all the cells.

12. Draw a bar graph to show expenditure under each heading.

13. Draw Pie chart to show the distribution of salary.

A B C D E F G H I
1 Budget for the Month
2 Week Income Expenses
Total Savings
Number
Expenditure
3 Husband Wife Rent Bills Household Medical

Procedure
1. Enter the details for the columns such as week of the month, income for husband
and wife, expenses for rent, bill, house hold, medical for 4 weeks
2. Enter the formula =sum (D4:G4) in the cell H4 to calculate the expenditure. Apply
the formula for all the cells till h8.
3. Select rows --> HOME --> Cells --> Format --> auto fit Row Height.
Select columns --> HOME --> Cells --> format --> Auto fit column
Width.
4. To center the spreadsheet
heading Select A1:I1
HOME --> Merge and Center.

5. FORMULA:
H4 Total expenditure for Week I =SUM(D4:G4)
H8 Total expenditure for month =SUM (H4:H7
D8 Total Rent for Month =SUM(D4:G4)
E8 Total Bill for Month =SUM(E4:E7)
F8 Total Household for Month =SUM(F4:F6)
G8 Total Medical Expense for Month =SUM(G4:G7)
I8 Total Savings For Month =B8+C8-H8
6. Draw a bar graph to show expenditure tinder each heading.
 Select the cells D8:G8
 Insert --> 2 D Bar
 Choose design --> select data
 Select horizontal axis label --> Edit - select the cells D3:63 --> ok
 Select series 1--> Edit --> type series name as Expenses.
 Layout --> chart title --> above the chart --> type "Monthly expenditure chart"
 Layout --> axis title --> primary vertical axis --> vertical title --> type "Headings"
 Layout -->axis title -->primary horizontal axis -->title below axis -->type "amount in
Rs".
7. Draw- a pie chart to show the distribution of salary.
 Select the cells D8:G8and select 18 holding Ctrl key.
 Insert --> pie --> 2-D pie
 Layout --> Chart title --> Above the chart --> Distribution of monthly salary
 Right Click on graph --> select data --> horizontal axis label --> edit --> select d3
to g3 headings --> ok
 Right Click on the graph --> select data --> horizontal axis label --> edit --> ctrl key
+ select the heading savings --> ok.
OUTPUT
 OUTPU
A B C D E F G H I

1 Salary for Month

2 Income Expenses Total Savings

Week
Number
Expenditure
3 Husband Wife Rent Bills Household Medical

4 WEEK 1

5 WEEK 2

6 WEEK 3

7 WEEK 4

8 TOTAL
OUTPUT(Graph)
6. Graph-Plotting facilities in MS Excel.(Display student results using excel charts.)
Enter the following details for 10 Students Register Number, Name, Subject1
Marks,Subject2 Marks, Subject3 Marks, Subject4 Marks, Total Marks and
Percentage.
a) Type the Register Number, Name and marks of four subjects for 10students in the
spreadsheet.
b) Format all text and numeric data appropriately.

c) Center the spreadsheet headings across the spreadsheet.

d) Create a formula to compute the Total marks and copy this to all the cells.

e) Create a formula to compute Percentage and copy this to all the cells.

f) Create a formula to compute the highest and lowest score using a libraryfunction.

g) Draw a bar graph for Register Number against total marks.

h) Draw Pie chart for one student showing his marks in different subject from total score

A B C D E F G H I J
1 Test Marks data of a Class
2 Register Name Subject1 Subject2 Subject3 Subject4 Total Percentage Highest Lowest
Number Marks Marks Marks Marks Marks
3
Procedure
1. Enter the details for 10 students such as Regno, name, subl marks, sub2 marks,

sub3 marks, sub4 marks.

2. Resize the worksheet's rows and columns.

 Select row --> HOME --> Cell --> Format Auto fit Row Height.

 Select column --> HOME --> Cells --> Format --> Auto fit column Width.

3. To center the spreadsheet heading.

 Select A1:I1 --> HOME --> Merge and Center.

4. Create the formula to compute total marks, percentage, highest marks and lowest

marks in the respective cells

5. Draw a bar graph for Register number against total marks

  Select the cells G3:G12

 Insert --> 2 D Bar

 Choose design-->select data

Select Horizontal axis label --> Edit --> select the cells A3:A12 --> ok

Select series l --> Edit --> type series name as total marks

 Layout --> Chart title --> Above the chart --> type "Total marks of a class

with register

number"

 Layout --> Axis title --> Primary vertical axis --> vertical title --> type

Register number

 Layout --> Axis title --> Primary horizontal axis --> title below axis --> type

Total marks

6. Draw a pie chart for one student showing his marks in different subjects from total score

 Select the cells C3: F3

 Insert --> pie --> 2-D pie

 Layout --> Chart title --> Above the chart --> type the respective student name

 Right Click on graph --> select data -->horizontal axis label --> edit --> select c2 to

f2 headings --> ok

COLUMN NAME CELL ADDRESS FORMULA

Total Marks G3 =sum(c3:f3)

Percentage H3 =average(c3:f3)

Highest Marks I3 =max(c3:f3)

Lowest marks J3 =min(c3:f3)

 A B C D E F G H I J

1
Test Marks data of a Class
2 Register Name Subject 1 Subject Subject Subject Total Marks Percentage Highest Lowest
Number Marks 2 Marks 3 Marks 4 Marks
3

10

11

12
OUTPUT(Graph)
7. Writing conditional expressions (using IF) and Using logical functions (AND, OR, NOT)
Using lookup and reference functions (Arithmetic functions, logical functions,text
functions).
Procedure
Click on to start program Microsoft office Microsoft excel
Step 1. Type the formula/function as given in the following tables and observe the
result. Formula must start with equal (=) sign.
Arithmetic functions:

Function Example Output

Sum() sum(24,68,12,41)

Mod() mod(15,2)

power() power(5,3)

product() product(25,10)

sqrt() sqrt(25)

=sum() it find the sum of values of a given range

=mod() it find the remainder from the division
=power() it calculates the x power y
=product() it calculates the product
=sqrt() it find the square root of a number
Date Functions:
Function Example Output
date() date(2015,10,6)
today() today()
day() day(today())
month() month(today())
year() year(today())

=date() it returns the serial number of a date. It counts number of days since 1900
=today() it returns today's date.
=day() it returns day from the given date.
=month() it returns month from the given date.
=year() it returns year from the given date.
Time Functions:
Function Example Output
now() now()
time() time(10,15,45)
hour() hour(now())
minute() minute(now())
second() second(now())
=now() it returns the today’s date and time
=time() it returns serial number of time
=hour()it extract the hours form the time
=minute() it extract the minute form the time
=second() it extract the seconds form the time
Logical Functions:Type the below table

A B C D E F G H

1 Regno Name Marks1 Marks2 Marks3 Marks4 Total Result

2 S12562 Apoorva 65 78 56 86

Function Example Output

AND() AND(2<5,10>20)
OR() OR(35=35,22>1)
NOT() NOT(55>44)
IF() IF(G2>=140,”Pass”,”Fail”)
SUMIF() SUMIF(C2:F2,”<35”)
=AND()it returns True, if expressions are True, otherwise return FALSE
=OR() it returns TRUE, if expressions are TRUE, otherwise return FALSE
=NOT() it returns TRUE, if expressions are TRUE, otherwise return FALSE
=IF()return first value/statement, if condition is TRUE, otherwise returns second
value/statement. i.e. result is depending on condition status.
=SUMIF() it finds the sum of values in a range which are satisfied by the condition.
Test Functions:

Function Example Output

CHAR() Char(66)
CODE() Code(“B”)
LEN() Len(“computer”)
UPPER() upper(“Knowledge”)
LOWER() lower(“SCIENCE”)

=CHAR() it returns a character for a given code

=CODE() it returns a ASCII value of a character
=LEN() it counts the number of characters in the input text
=UPPER() it converts lower case text into upper case
=LOWER() it converts upper case text into lower case.
8. Data Validations :Specifying a valid range of values for a cell, Specifying a list of valid
values for a cell, Specifying custom validations based on formula for a cell, Sorting and
Filtering Data facility in MS Excel.
1) Type the Employee Code, Employee Name, Basic Salary and Loan Amount data for
10 employees in the spreadsheet.
2) Format all numbers as a currency.
3) Center the spreadsheet headings across the spreadsheet.
4) Format all text.
5) Create a formula to compute DA as 50% of the Basic salary.
6) Create a formula to compute HRA as 12% of the Basic salary
7) Create a formula to compute Total salary
8) If Total salary is greater than 5,00,000, compute Tax as 20% of Total salary
otherwise 10% of the Total salary using a formula.
A B C D E F G H I
1 Salary for Month
2 Emp Employee Basic DA HRA Loan Total Annual Tax
code Name Salary Salary Income
3
Procedure
1. Enter the details for 10 employees for employee code, name, Basic salary and loan.
2. Resize the worksheet's rows and columns.
 Select rows HOME --> Cells --> Format -->Auto fit Row Height.
 Select columns --> HOME --> Cells --> Format --> Auto fit column Width.
3. Format all the numbers as currency.
 Select C3:112
 Press Ctrl+A or HOME --> Format --> Format cells.
 Select Number tab -->Choose proper currency from the category list box.
4. To center the spreadsheet heading.
 Select A1:I1HOME --> Merge and Center Apply the formula to compute DA,
HRA, monthly salary, total annual salary, and tax under the respective columns.
5. To align worksheet data.
 Select the entire table
 Press Ctrl+A or HOME --> Format --> Format cells
 Select Alignment tab --> select Center from Horizontal list box and vertical list
box--> OK

COLUMN NAME CELL ADDRESS FORMULA

DA D3 =C3*50%
HRA E3 =C3*12%
MONTHLY SALARY G3 =C3+D3+E3-F3
TOTAL ANNUAL SALARY H3 =G3*12
TAX 13 =IF(H3>=500000,H3*20%,H3*10%)

6. FORMULAE: Apply these formulae for all the cells of the respective columns.
 OUTPU
A B C D E F G H I

1
Salary for
Month
2 Emp code Employee Name Basic Salary DA HRA Loan Total Salary Annual Income Tax

10

11

12
9. Creating a presentation in PowerPoint- Inserting / Deleting slides in PowerPoint.
Procedure
10. Creation of Slide transition and Editing special effects in
PowerPoint. Procedure
11. Creation of Inserting sound and picture in
PowerPoint. Procedure
 SECION-B
1. Write the procedure to create leave Letter(with proper format).
Procedure
1. Open MS Office-MS Word – File – New.

2. Open a blank MS-word page.

3. Start writing a letter with proper salutation.

4. To align Date to the left of the page then type the date and click Align left button in

the standard tool bar.

5. Complete first paragraph.

6. Start second paragraph & complete the letter with properclosing.

OUTPUT
OUTPUT
2. Write the procedure to create a resume(with proper format).
Procedure
1. Open MS Office-MS Word – File – New

2. Type name, mobile number and mailid in the top.

3. Go to Insert- Page Number-select the position bottom of the page and Alignment to right

in the footer– Click Ok.

4. Go to Table-Insert-Table- chose Number of Columns and Rows as per the data to be

entered.(Both education qualification and language known)

5. Enter the details, format it (bold and increase the font size via standard tool Bar).

6. When ever you want to increase the number of column in the existing row, Select that

row and go to Table-click Split Cells- enter number of columns- click Ok.

7. In order to decrease the existing column numbers, select that columns and Go to Tables-

click Merge cells.

8. Finally type the declaration and signature outside the table with your name aligning right

side and date to the left side.

3. Write the procedure to create a letter head of a company.(with proper format).

Procedure
1. Open MS Office-MS Word – File – New.

2. Go to View- Header and Footer- Type the complete address of a company.

3. Select the Text and click align right on the standard tool bar.

4. In order to insert the company logo (create a logo using paint software and save it or use

the existing one) inside the header go to Insert- Picture-From File-and browse for the

required Picture/file/logo where you have saved – click Insert.

5. After inserting the logo/image resize the logo to fit the top left corner of the page by right

clicking on the logo, go to Format Picture – select Layout tab – select the Wrapping Style

to Infront of text- click ok.

6. To insert a Line go to Insert- picture- Auto shapes- Select the line and draw below the

Logo and the address inside the header.

7. Format the line by Right clicking and selecting Format Auto shape- select the Color and

Line tab- chose your style- click ok.

8. Then go to the footer- Insert and format a line as did for header.

9. Type the text inside the footer and below the line.

10. Go to Format-Background-Printed Watermark-Picture Mark-Click Select Picture- Browse

for the required background- click Washout- Apply- Ok

OUTPUT
4. Write the procedure to create a cover page of a project report.(with proper format).

Procedure:
1. Open MS Office-MS Word – File – New.

2. Type university name, project title, guide name etc line by line. Select the text and click

align center on the standard tool bar. Keep the cursor where you want to insert the

institution logo and then go to Insert-Picture-From File-and browse for the required

picture/file - then click insert.

3. Format the title of your project by selecting and applying the Wordart. For that go to Insert-

Picture-Wordart- then chose the style you Want and click Ok. To change the color of the

wordart text, right click on the text and go to Format Wordart- chose the color- click ok

4. In order to insert the border for your project cover page Go to the Format- Border and

Shading- Select the Border tab and the style and color of your choice-click OK.
OUTPUT
5. Write the procedure to create a macros in word.

Procedure
1. Open MS Office-MS Word – File – New.

2. Go to Tool-Macro-Record Macro- Give the macro name.

3. Select keyboard icon.

4. Assign a shortcut key for the macro.

5. Go to Insert- Picture- From File- browse for the required picture- click Insert.

6. Type some text - change the font size, color and style by the standard tool bar.

7. Go to Tool- Macro- click Stop recording.

8. Open a new File-press the shortcut key assigned earlier.

OUTPUT
6. Create a Visiting Card of your college using page size as follows
i) Page width=”3.2” ii)Page height=”2.2” And use different font styles, sizes,
alignments.

Procedure:
Step 1: Open MS-Word by click on START button; go to All Programs, then select Microsoft
Office Word 2007.
Step 2: To open a new document, Click on Office Button then select New - > Blank Document
then click on create option.
Step 3: Now click on “Page Layout” from the Menu bar. Then click on Margins then click
on Custom Margins option. Then the “Page Setup” dialog box appears. In this you find three
tabs namely “Margins”, ”Paper”, ”Layout”. Then in the ‘Margins’ tab, make all the parameters
like Top, Bottom, Left, Right, and Gutter to zero and make Gutter Position to Left. Then in
the Page tab, change the width and height options to 3.2 and 2.2 respectively. Then in the
Layout tab, make the Header and Footer to zero. Now this page is set to the visiting card as
follows. Step 4: In this step we have to enter the telephone number and Fax number on the
top part of the paper. It can be done as follows:
 First go to Insert menu, then select Symbol option.
 Then change Font to “Windings”.
 Then select the appropriate to your need i.e., to the telephone option select , and to
 The Fax option select .
Step 5: Now write your institution name and make it to the center alignment button .
Step 6: Now write all the details you want to put in your visiting card as your needs. And select
the text and make it to center .
Step 7: Now change the background color by selecting Page color option from Page Layout
menu.
Step 8: This is the final step in creating Visiting Card. In this step, we have to save the letter
as
“Visiting [Link]” by selecting “Save” option from Office button. Then a prompt window
will ask you to write a file name. Now you have to give the file name and press the save button.

OUTPUT
7. Creation of Inserting chart and organization chart in PowerPoint.( Eight
salesmen sell three products for a week.)
Eight salesmen sell three products for a week. Using a spreadsheet create a sales
report. The report should include the name of the salesman, Amount of sales for each
product and the salesman's total sales in the format given below.

A B C D E
1 Sales
for
Month
2 Name Product Product Product Total Sales
1 2 3 =B3+C3+D3
Amount Amount Amount
3
4

8 Product Total

9 =SUM(B3:B7) =SUM(C3:C7) =SUM(D3:D7)

10 TOTAL SALES FOR ALL SALES REPRESENTATIVES MONTH =SUM(E3:E7)

Procedure
1. Enter the data for the columns name, product 1, product 2, product 3.
2. Resize the worksheet's rows and columns.
 Select rows - HOME - Cells - Format - Auto fit Row Height.
 Select columns - HOME - Cells - Format - Auto fit column Width.
3. Format all the numbers as currency.
 Select B3 E7
 Press HOME - Format - Format cells.
 Select Number tab - Choose proper currency from the category list box.
4. To center the spreadsheet heading.
 Select A2:E2
 HOME 4 Merge and Center.
 5. Enter the formula =B3+C3-D3 in the cell E3 to fund total sales.
6. Enter the formula =sum (B3:B7) into the cell B9 to find total amount for product
and apply the formula for product2 and product3 and total sales for all salesmen for the
month. Save the file.
7. Open Ms Powerpoint.
8. In the 1st slide copy paste the data of eight salesman which was typed in excel sheet.
9. Insert a bar gragh for the typed data in the powerpoint.
OUTPUT
OUTPUT
8. Write the procedure to store the data and calculate total and percentage of the
following details for 10 Students.( In Ms Access)
Enter the following details for 10 Students Register Number, Name, Subject1
Marks,Subject2 Marks, Subject3 Marks, Subject4 Marks, Total Marks and
Percentage.

Procedure
a) Open Ms Access->new database->give a name.

b) Type the Register Number, Name, Sub1,Sub2,Sub3,Sub4,Total and percentage and correct

data type in design mode ->Save the table.

c) Go to Datasheet view add all the details for 10 students->save the table.
d) Go to create tab click in query wizard->select table once->close.

e) Select all the fields from the table by double clicking->add fromula for total and
percentage.
f) Click in RUN! to get the output.

COLUMN NAME FORMULA

Total :[sub1]+[sub2]+[sub3]+[sub4]

Percentage :[total]/4

OUTPUT
 A B C D E F G H

1
Test Marks data of a Class
2 Register Name Subject 1 Subject 2 Subject 3 Subject 4 Total Marks Percentage
Number Marks Marks Marks Marks

10

11

12
9. Write the procedure to store the data and calculate total and percentage of the
following details for 10 [Link] marks card.( In Ms Access)
Enter the following details for 10 Students Register Number, Name, Subject1
Marks,Subject2 Marks, Subject3 Marks, Subject4 Marks, Total Marks and
Percentage.

Procedure:
a) Open Ms Access->new database->give a name.

b) Type the Register Number, Name, Sub1,Sub2,Sub3,Sub4,Total and percentage and

correct data type in design mode ->Save the table.

c) Go to Datasheet view add all the details for 10 students->save the table.

d) Go to create tab click in query wizard->select table once->close.

e) Select all the fields from the table by double clicking->add fromula for total and
percentage.
f) Click in RUN! to get the output.

g)

A B C D E F G H
1 Test Marks data of a Class
2 Register Name Sub1 Sub2 Sub3 Sub4 Total Percentage
Number
3
COLUMN NAME FORMULA

Total :[sub1]+[sub2]+[sub3]+[sub4]

Percentage :[total]/4
[Link] a report containing pay details of employees of a company.( In Ms Access).
a) Type the Employee Code, Employee Name, Basic Salary and Loan Amount data for 10
employees in the Ms Access.
b) Create a formula to compute DA as 50% of the Basic salary.
c) Create a formula to compute HRA as 12% of the Basic salary.
d) Create a formula to compute Total salary.
e) Create a formula to compute Annual income.
f) Generate employe payroll report.
A B C D E F G H I
1 Salary for Month
2 Emp Employee Basic DA HRA Loan Total Annual Tax
code Name Salary Salary Income
3

COLUMN NAME FORMULA

DA =basicsalary*50%
HRA =basicsalary *12%
MONTHLY SALARY =basicsalary +DA+HRA-Loan
ANNUAL INCOME =totalsalary*12
 OUTPU
A B C D E F G H I

1
Salary for Month
2 Emp code Employee Name Basic Salary DA HRA Loan Total Salary Annual Income Tax

10

11

12
[Link] of slides using SMARTART in PowerPoint.
20. A Bank offers loan for housing and vehicle at an interest of 10.25% for housing
and 14.2% for vehicle. For all the 5 loan applicants compute the monthly
premium(EMI), given total installments as 24 months. Also compute the monthly
interest and monthly principal amount and the amount of principal and Interest
paid using Financial Library functions in a spreadsheet.
Enter the details for the columns such as applicant name, loan type, loan amount
number of installment as 24. Then enter the following formula in the respective cells.
A B C D E F G H I J

1 JGI BANK

2 Applicant Loan Loan No of Rate of Monthly 1st 1st Total Total

Name Type Amount Installments Interest EMI Month Month Principal Interest
Interest Principal

Rate of interest = if (B3="CAR", 14.2%, 10.25%)

Monthly EMI =PMT(E3/12,D3,-C3)
First month interest = IPMT(E3/12,1,D3,-C3)
First month principal =PPMT(e3/12,1,d3,-c3)
Total principal =CUMPRINC(E3/12,D3,C3,1,24,1)
Total interest =CUMIPMT(E3/12,D3,C3,1,24,1)
 OUTPU
A B C D E F G H I J

1 JGI BANK
2 Applicant Loan Loan Amount No Rate of Monthly 1st 1st Month Total Total Interest
Name Type of Interest EMI Month Principal Principal
Insta Interest
llme