FMS

FY BCOM-SEM I

2024-2025

Prof. Vijayalaxmi Suvarna

 PRACTICAL No:01

1)Prepare the following Excel worksheet with font “Times New Roman, Font Size
12 and for heading 14, cell alignment is “Center”, table borders with width 1.5pt
,border colour blue, fill colour of your choice to Column [Link] the
sheet as Sales orders. Copy this data to sheet 2. Rename the sheet 2 as New. Move
this New sheet before Sales orders.
Save the workbook with your name followed by roll number
STEPS:
To ENTER THE DATA:
1. Open a New worksheet.
2. Type the column headings Customer_#, Order_Date, Product_#, Quantity, Unit_Price,
order_ Category in cells A1:F1 respectively.
3. To adjust the column width:
a. Select A1:F1.
b. Click on Home tab of the ribbon.
c. In the Cell Size section Select Autofit Column width.
NOTE: The column width adjusts so the headings in the column is displayed in full.
4. Type the given data in cells A2: F15
To FORMAT:
5. Select the table Or Select A1:F15 .
6. Click on the Home Tab and then from FONT select font style as “ Times New Roman”,
Size =12 and alignment = center and then click for border select the border, select the color
for border as blue and filling colour select the colour = _____________

Select the column headings from A1:F1 , click on Home tab and then from FONT
select SIZE=14.
To Rename the sheet as Sales orders:
7. Right click on the sheet tab and choose Rename option and Type new name as Sales orders.
To Copy this data to sheet 2:
8. Select the table. Right click from the available option select Copy .
9. Click on sheet 2. select cell A1 Right click select Paste(Keep source column widths). So
To Rename the sheet 2 as New:
10. Double click on the sheet tab sheet 2 and Type new name as NEW.
To Move the sheet New before Sales orders:

11. Right click on the sheet tab NEW and choose option-MOVE . In the dialog Box select Before
sheet - Sales orders. Click OK.

To Save the workbook with your name followed by roll number:

12. Click on File Select Option Save . Type file Name as ______________ Save as Type Excel
Workbook. Click SAVE.
PROBLEM 2:
Prepare the following Excel sheet.(fill handle to generate roll numbers. Observe the Merged
cells) Enter data in the sheet.
STEPS to Create the worksheet:
1. Type Roll Number, Name, Marks Scored in , in cells B2: D2 respectively.
2. Type Subject 1, Subject 2, Subject 3, Subject 4 in cells D2:G2 respectively.
3. To adjust the Column width of B2:
• Position the mouse pointer on the column address bar between columns B & C.
When the double arrow appears Double –Click the mouse button.
• Column B will change the width to be just enough to fit the typed word.
STEPS For Merge & Center:
4. Select cells B2:B3. Click on Home tab.
5. In the Alignment group, Click on merge and center.
6. Select cells C2:C3. Click on Home tab.
7. In the Alignment group, Click on merge and center.
8. Select cells D2:G2. Click on Home tab,
9. In the Alignment group , Click on merge and center.
10. Select B2:G3 click on Home tab select font group select fill colour → choose the colour
Blue.
To Enter the Data:
11. Type 1,2 in cell B3 and B4 respectively. Select B3:B4 activate the fill handle and drag the
mouse pointer till Roll Number is 10.
12. From C4 to G13 type the data.
 FMS
FYBCOM – SEM I
2023-24
Practical 02
Conditional Formatting, Sort and
Filter
 Conditional Formatting, Sort and
Filter
1. Use the excel sheet created in Problem 1 of Practical 1.
2. Highlight top 4 costlier products. (Conditional
Formatting)
3. Highlight the sheet cells with quantity sold is less than
12. (Conditional For- matting)
4. Sort the in descending order on column E.
5. Sort the sheet on Order Date (Oldest to newest).
6. Filter all Large orders.
7. Sort according to order category and inside each
category arrange the orders in decreasing order of
quantity sold.
2. The following data has been entered in a worksheet

NAME CITY PROFIT

RAKESH BANGALORE 45000
UMESH MUMBAI 72000
RAKSHA PUNE 69000
FARHAN BANGALORE 54000
SUMAN PUNE 86000
AMAN MUMBAI 74000
KARTHIK BANGALORE 85000

Write steps to do the following:-

i) Display only those rows where the Profit is more than 70,000.

ii) Display only those rows where the Profit is more than ` 60,000 but less than
80,000.

iii) Display only those rows where name contains “U”.

STEPS:
1. Use the excel sheet created in Problem 1 of Practical 1.
Copy paste the table in a new excel file.
To Highlight top 4 costlier products using Conditional
Formatting:
2. Select the Unit_Price of the products. Click on ‘Conditional
Formatting’ on the Home Tab. Select ‘Top/Bottom Rules’,
and click on ‘Top 10 items’. Format cells that rank in the
Top – Choose this value as 4 and click on ‘Ok’.
To Highlight the sheet cells with quantity sold is less than
12 using Conditional Formatting:
3. Since we are working with the quantity sold, select that
column. Click on ‘Conditional Formatting’, ‘Highlight
Cell Rules’, ‘Less than’ and insert the value 12 when
prompted to format cells that are less than.
To Sort the sheet in descending order on column E:
4. Select column E. On the Home Tab under ‘Editing’ click on
‘Sort & Filter’, choose ‘Largest to Smallest’ from the
dropdown menu.
To Sort the sheet on Order Date (Oldest to newest):
5. Select the column have information on the Order Date.
Under Sort & Filter choose ‘Oldest to Newest’.
To Filter all Large orders:
6. Select the column of Order Category (along with the header
row), on the Home Tab under ‘Editing’ click on ‘Sort &
Filter’, choose ‘Filter’ from the dropdown menu. This will
insert a downward arrow on the header of column E.
7. Click on this arrow and make sure only the Large Order
option is selected (√).
To Sort according to order category and inside each
category arrange the orders in decreasing order of
quantity sold:
8. Select the Order Category and sort it A to Z (under Sort &
Filter). Now apply filter to this same column and first
select ‘Large Order’ and arrange it in decreasing order
using sort (Largest to Smallest).
9. Next select ‘Normal Order’ in the filter option and sort
this also in decreasing order.
10. Under the filter option, select both ‘Large Order’ and
‘Normal Order’ in order to display the entire table.
PROBLEM 2:
STEPS:
Insert the following table:
NAME CITY PROFIT
RAKESH BANGALORE 45000
UMESH MUMBAI 72000
RAKSHA PUNE 69000
FARHAN BANGALORE 54000
SUMAN PUNE 86000
AMAN MUMBAI 74000
KARTHIK BANGALORE 85000

To Display only those rows where the Profit is more than

70,000:
1. Apply ‘Sort & Filter’ on the column of Profit. Clicking on the
downward arrow select ‘Number filters’ and then ‘Greater
than…’. Insert the value as 70000 and click on ‘Ok’.
To Display only those rows where the Profit is more than `
60,000 but less than 80,000:

2. This time under ‘Number Filters’ choose ‘Between…’ and then

choose ‘is greater than’, insert 60000, and second dropdown
choose ‘is less than’ and insert 80000.

To Display only those rows where name contains “U”:

3. Since we are now concerned with names, choose the column

containing the names and apply the filter to this column. Under
the filter option choose ‘Text filters’ and then ‘Contains’, insert
the letter as ‘U’ and click on ‘Ok’.
PRACTICAL NO: 03 FORMULAS AND LOGICAL OPERATORS 1

PROBLEM 1: Use the excel file created in Problem 1 of Practical 1.

(a) In the column G, calculate amount payable for each order.

(b) In the column H, Calculate net amount payable for each order after giving
6.8% discount on large orders and 4.2% discount on normal orders.

(c) Calculate product wise quantity sold.

(d) Calculate product wise revenue generated.

(e) Calculate revenue generated in the year 2015.

STEPS:

a) TO CALCULATE AMOUNT PAYABLE FOR EACH ORDER:

1. In G1 type Amount and press enter

2. In G2 type =D2*E2 press enter. Select G2 activate the fill handle and drag till G15.

b) TO CALCULATE NET AMOUNT PAYABLE(REVENUE) FOR EACH

ORDER:

1. In H1 type Net Amount and press enter

2. In H2 type = IF(F2="Large Order",G2-G2*6.8%,G2-G2*4.2%)press enter. Select

H2 activate the fill handle and drag till H15.

c) TO CALCULATE PRODUCT WISE QUANTITY SOLD:

SORTING ON PRODUCT:
1. Select the data A1:F15
2. From DATA TAB select SORT.
3. In Sort By select Product_# and order select A to Z and click on Ok.
SUBTOTAL OF PRODUCT WISE QUANTITY SOLD:
1. Select the data A1:F15
2. From DATA TAB select Subtotal.
3. In the option ‘At each Change in’ select PRODUCT_#.
 4. In the option ‘Use Function’ select SUM.
5. In the option ‘Add Subtotal to’ select QUANTITY and click on Ok.

d) TO CALCULATE PRODUCT WISE REVENUE GENERATED:

SORTING ON PRODUCT:
1. Select the data A1:H15
2. From DATA TAB select SORT.
3. In Sort By select Product_# and order select A to Z and click on Ok.

SUBTOTAL OF PRODUCT WISE REVENUE GENERATED:

1. Select the data A1:H15
2. From DATA TAB select Subtotal.
3. In the option ‘At each Change in’ select PRODUCT_#.
4. In the option ‘Use Function’ select SUM.
5. In the option ‘Add Subtotal to’ select REVENUE and click on Ok.

e) TO CALCULATE REVENUE GENERATED IN THE YEAR 2015.

1. Select cell I1 type Year.

2. Select cell I2 type = year (B2) press enter.
3. Select the cell I2 activate the fill handle and drag till I15.
4. Select cell J1 type heading: revenue generated for 2015.
5. Select cell J2 type
=SUMIF (I2:I15, "=2015", H2:H15) press enter. OR = SUMIF (I2:I15, 2015,
H2:H15) press enter

PROBLEM 2:
STEPS:
To find Grade:
1. Select cell C2 and type:
=IF(B2>=80,"O",IF(B2>=70,"A+",IF(B2>=60,"A",IF(B2>=55,"B+",(IF(B2>=50,"B",IF
(B2>=45,"C", IF(B2>=40,"D","F"))))))))
Select cell C2 and activate the fill handle and drag till the grade is calculated for all the
marks.
To find Grade Point:
2. Select cell D2 and type:
=IF(B2>=80,10,IF(B2>=70,9,IF(B2>=60,8,IF(B2>=55,7,
(IF(B2>=50,6,IF(B2>=45,5,IF(B2>=40,4,0))))))))
Select cell D2 and activate the fill handle and drag till the grade point is calculated for all
the marks.
To find C*G:
3. Select cell F2, and type:
=E2*D2
 Select cell F2 and activate the fill handle and drag till the grade point is calculated for all
the marks.
To find SGPI:
4. Select cell B8 and type Total Credits and select cell C8 and type:
=SUM(E2:E6) and press enter.
5. Select cell B9 type SGPI and select cell C9 and type:
=SUM (F2:F6)/C8 and press enter.

PROBLEM 3: Consider the following worksheet.

Write the steps to compute the Wages in column D given that the rate per day
is Rs. 400, Rs. 750 and Rs. 800 for unskilled, semi-skilled and skilled workers
respectively.
STEPS:

1. Select D2 and type the formula

=IF(C2="UNSKILLED",B2*400,IF(C2="SEMI-SKILLED" , B2*750
,B2*800) and press enter.

2. Select D2 and drag the fill handle to D6.

PROBLEM 4:

Enter the following data into an excel sheet.

 (a) calculate Total and Average in column F and G respectively.

(b) Declare the result as Pass if Average is above 40 else declare it as Fail.

(c) Fill the cell with red colour if the result is Fail

STEPS:

a) To CALCULATE TOTAL MARKS:

1. Select F2 and type = SUM(B2:E2) and press enter.

2. Select F2 and drag the fill handle to F11.
AVERAGE:

1. Select G2 and type=F2/4 OR = Average (B2:E2) and press enter.

2. Select G2 and drag the fill handle to G11.

b) DECLARATION OF RESULT:

1. Select cell H1 type RESULT.

2. In cell H2 type
=IF(G2>=40,"PASS","FAIL") press enter.

3. Select H2 and drag the fill handle to H11.

b) FILL THE CELL WITH RED COLOUR IF THE RESULT IS FAIL:

 1. Select cell H2 from Home menu select conditional formatting-→ Highlight cell
rules-→ equal to -→ in the dialog box format cells that are equal to ---type FAIL
and select light red fill with dark red text-→ OK.

2. Select H2 and drag the fill handle to H11.

PROBLEM 5:

For the above worksheet

(f) Declare the result as Pass if marks in each subject is above or equal to 40
else declare it as FAIL.

(g) Assign the grade as per the following Criterion given in the previous example.

STEPS:

To Declare the result as Pass if marks in each subject is above 40 else declare it as
FAIL:

1. Select cell I1 type RESULT2 press enter.

2. Select cell I2 and type

=IF(AND(B2>=40, C2>=40, D2>=40, E2>=40),"PASS","FAIL")

3. Select I2 and drag the fill handle to I11.

To Assign the grade:

1. Select cell J1 type GRADE and press enter.

2. Select cell J2 and type

=IF(G2>=80,"O",IF(G2>=70,"A+",IF(G2>=60,"A",IF(G2>=55,"B+",
(IF (G 2>=50,"B", IF(G 2>=45,"C", IF(G2>=40,"D","F"))))))))
Select cell J2 and activate the fill handle and drag till the grade is calculated for
all the marks.
PROBLEM 6:

In an Excel worksheet Name and Basic pay are entered in columns A1:A11 and
B1:B11 respectively. In first row enter headings and in the remaining rows enter
data values. (Enter your own data.) Calculate the following.

a) DA @ 60% of basic Pay in Column C. =B2*60%

b) HRA @30% of Basic Pay or Rs.6000 whichever is less in column D.

c) PF in Column E as 8.33% of Basic Pay, if Basic Pay is above 10000 else

PF is nil.

d) Gross Salary as Basic Pay + DA + HRA in column F.

e) Net Salary in Column G as Gross – PF.

f) Round up the net salary to nearest hundred.

STEPS:

a) TO CALCULATE DA:

1. Type DA, HRA, PF, [Link], NET SALARY in cells C1: G1 respectively.

2. Enter the formula =B2*60% in cell C2 and press enter key.

3. Using the fill handle of cell C2 copy the formula to range of cells C2:C11.

b) TO FIND HRA:

[Link] the formula =MIN(B2*30%,6000) in cell D2 and press enter key.

[Link] the fill handle of cell D2 copy the formula to range of cells D2:D11.

c) TO FIND PF:

1. Enter the formula = IF(B2>10000, B2*8.33%,0) in cell E2 and press enter key.

2. Using the fill handle of cell E2 copy the formula to range of cells E2:E11.

3. d) TO FIND GROSS SALARY:

1. Enter the formula =B2+C2+D2 in cell F2 and press enter key.

2. Using the fill handle copy the formula to range of cells F3:F11.

e) TO FIND NET SALARY:

 1. Select cell G2 . Type = F2- E2 in cell G2 and press enter key.

2. Using the fill handle of cell G2 copy the formula to range of cells G3:G11.

f) Round up the net salary to nearest hundred.

1. Select cell G2. type = roundup (G2, -2) press enter

2. Using the fill handle of cell G2 copy the formula to range of cells G3:G11.
PROBLEM 7:

Create an excel sheet with Names of salesman and his sales as headings. Enter
your own data for 10 salesmen. Calculate commission, additional commission and
total commission. (Commission is 15.5 % of sales. If sales exceed Rs. 150000
then an additional commission of 5% of sales exceeding Rs.150000 is given.) Also
find the total commission he received.
STEPS :

Type Name, Sales, Commission, Add Commission, Total Commission in cell A1: E1
respectively.

Enter the data in cells A2:B11 respectively.

COMMISSION

1. Select C2 and type =B2*15.5% and press enter.

2. Select C2 and drag the fill handle to C11.

ADDITIONAL COMMISSION:

1. Select D2 and type =IF(B2>150000, (B2-150000)*5% ,0)and press enter.

2. Select D2 and drag the fill handle to D11.

TOTAL COMMISSION:

1. Select E2 and type =C2+D2 and press enter.

2. Select E2 and drag the fill handle to E11.

***************************************************************
Practical 4: Interest, Present Value and Future Value

1. Find the simple interest, compound interest and the difference between them
on Rs.125000 at 8% p.a. at the end of 4 years.
2. Find the maturity amount of a 2-year fixed deposit of Rs.100000 with
compound interest 12% p.a. compounded quarterly.
3. Find the maturity amount of a 3-year fixed deposit of Rs.250000 with
interest rate 24% p.a. and the interest is compounded
(a) annually
(b) semi-annually
(c) quarterly
(d) monthly
4. Find the future value of Rs.20000 after 4 years if the compound interest is at
10% p.a.
5. Find the present value of Rs. 4000000 required 4 years from now if the
compound interest rate is 5%.
Practical 4: Interest, Present Value and Future Value

Q 1. Find the simple interest, compound interest and the difference between
them on Rs.125000 at 8% p.a. at the end of 4 years.
Steps:
1. In A1 write Principal, in B1 write Rate and in C1 write No of years. In
A2, B2 and C2, enter the values 125000, 8 and 4 respectively
2. In D1 write SI. In D2 calculate using the formula: =A2*B2*C2/100
3. In E1 write Amount. In E2 calculate using the formula:
=A2*(1+B2/100)^C2
4. In F1 write CI. In F2 calculate using the formula: =E2-A2
5. In G1 write Difference. In G2 calculate using the formula: =F2-D2

Q 2. Find the maturity amount of a 2-year fixed deposit of Rs.100000 with

compound interest 12% p.a. compounded quarterly.
Steps:
1. In A4 write Principal, in B4 write Rate and in C4 write No of years. In
A5, B5 and C5, enter the values 100000, 12 and 2 respectively.
2. In D4 write Amount. In D5 calculate using the formula:
=A5*(1+B5/(4*100))^(C5*4)
3. We can also calculate using the Annuity formula for Future Value as:
=FV(B5/(4*100),C5*4,0,-A5,)
Note: The arguments for the formula are FV(rate, nper, pmt, [pv], [type])
– Rate is written as i, nper is no of periods, pmt is annuity payment which
is zero here, pv is present value i.e. the principal and type is either
immediate or due which is not required in this case. Also, the principal is
taken as negative in the formula.

Q 3. Find the maturity amount of a 3-year fixed deposit of Rs.250000 with

interest rate 24% p.a. and the interest is compounded (a) annually (b) semi-
annually (c) quarterly (d) monthly
Steps:
1. In A7, A8 and A9 write Principal, Rate and No of years respectively. In
B7, B8 and B9 enter values 250000, 0.24 (rate as i) and 3 respectively.
2. In A10, A11, A12 and A13 write the sub parts of the question (a)
annually, (b) semi-annually, (c) quarterly and (d) monthly respectively.
 3. In B10, B11, B12 and B13 write the corresponding compounding periods
for a year i.e. 1, 2, 4 and 12 respectively.
4. Name C9 as Amount. In C10 calculate using the formula:
=FV($B$8/B10,$B$9*B10,0,-$B$7,)
5. Use the fill handle and calculate for (b), (c) and (d).
Note: Using the dollar sign “fixes” the cell.

Q 4. Find the future value of Rs.20000 after 4 years if the compound interest is
at 10% p.a.
Steps:
1. In In A15, B15 and C15, write Present Value, Rate and No of years. In
A16, B16 and C16, enter values 20000, 0.1 and 4 respectively.
2. In D15 write Future Value. In D16, calculate using the formula:
=FV(B16,C16,0,-A16,)

Q 5. Find the present value of Rs. 4000000 required 4 years from now if the
compound interest rate is 5%.
Steps:
1. In In A18, B18 and C18, write Future Value, Rate and No of years. In
A19, B19 and C19, enter values 4000000, 0.05 and 4 respectively.
2. In D18 write Present Value. In D19, calculate using the formula:
=PV(B19,C19,0,-A19,)
Practical 5: Nominal and Effective Rate of Interest,
Internal Rate of Return, Annuity.

1. Find the maturity amount if Rs.50000 is invested for 3 years at 7% p.a.

compounded continuously.
2. What will be the effective rate of interest, if the nominal rate of interest is
40% p.a. compounded quarterly?
3. Calculate the effective annual rate of compound interest equivalent to
nominal rate of 15% per annum compounded half yearly. Also, at that
effective annual rate what will be the amount received if Rs. 25,000 is
invested for half a year?
4. Find the rate of interest from an investment that, for an initial payment of
100, yields a return of 60 at the end of the first two periods.
5. Find the present value of an immediate annuity of Rs.50000 p.a. for 4 years
with interest compounded at 8% p.a.
6. A housing society is forming a sinking fund by investing Rs.100000 at the
end of each year with interest compounded at 11% p.a. How much money
will be accumulated at the end of 6 years?
Practical 5: Nominal and Effective Rate of Interest, Internal Rate of Return,
Annuity

1. Find the maturity amount if Rs.50000 is invested for 3 years at 7% p.a. compounded
continuously.
Steps:
1. In cells A1, A2 and A3 type Principal, No of Years and Rate of Interest respectively. In
B1, B2 and B3 type 50000, 3 and 0.07 respectively.
2. In cell A5 type Maturity Amount
3. In cell B5 calculate using the formula: =B1*EXP(B3*B2)

2. What will be the effective rate of interest, if the nominal rate of interest is 40% p.a.
compounded quarterly?
Steps:
1. In cells A1 and A2 type Nominal Rate and No of Compounding Periods respectively.
In B1 and B2 type 0.4 and 4 respectively.
2. In cell A4 type Effective Rate.
3. In cell B4 calculate using the formula: =EFFECT(B1,B2)
4. You may convert this value to % using the ‘Number Format’ option given in the
HOME tab.

3. Calculate the effective annual rate of compound interest equivalent to nominal rate of 15%
per annum compounded half yearly. Also, at that effective annual rate what will be the
amount received if Rs. 25,000 is invested for half a year?
Steps:
1. In cells A1 and A2 type Nominal Rate and No of Compounding Periods respectively.
In B1 and B2 type 0.15 and 2 respectively.
2. In cell A4 type Effective Rate.
3. In cell B4 calculate using the formula: =EFFECT(B1,B2)
4. In cells A6 and A7 type Principal and No of years respectively. In B6 and B7 type
 25000 and 0.5 respectively.
5. In cell A9 write amount Received.
6. In cell B9 calculate using the formula: =FV(B4,B7,0,-B6)

4. Find the rate of interest from an investment that, for an initial payment of 100, yields a
return of 60 at the end of the first two periods.
Steps:
1. In cells A1, A2 and A3 type PV, FV, and No of Periods respectively. In cells B1,
B2 and B3 type 100, 160 and 2 respectively. (since the return is 100, FV = 100 + 60
= 160)
2. In cell A5 type Rate of Interest.
3. In cell B5 calculate using the formula: =RATE(B3,0,-B1,B2)

5. Find the present value of an immediate annuity of Rs.50000 p.a. for 4 years with interest
compounded at 8% p.a.
Steps:
1. In cells A1, A2 and A3 type Annuity Payment, No of Years and Rate of Interest
respectively. In cells B1, B2 and B3 type 50000, 4, 0.08 respectively.
2. In cell A5 type Present Value of Annuity.
3. In cell B5 calculate the value using the formula: =PV(B3,B2,-B1)

6. A housing society is forming a sinking fund by investing Rs.100000 at the end of each year
with interest compounded at 11% p.a. How much money will be accumulated at the end of 6
years?
Steps:
1. In cells A1, A2 and A3 type Annuity Payment, No of Years and Rate of Interest
respectively. In cells B1, B2 and B3 type 100000, 6, 0.11 respectively.
2. In cell A5 type Accumulated Value.
3. In cell B5 calculate the value using the formula: =FV(B3,B2,-B1)
Practical 6: Loan Amortization

1. A loan of Rs.40000 is to be repaid in 4 monthly instalments, beginning

from the end of the first month. If the rate of interest is 12% p.a.
compounded monthly, calculate the EMI. Also construct amortization
table.

2. Anita purchased a new sound system that was selling at Rs.2,94,000.

She agreed to make a down payment of Rs.70,000 and to pay the
remaining amount in 24 months with an interest of 18% p.a.
compounded monthly. Find EMI. Also prepare loan amortization table.

3. A loan of Rs.30 lakhs is to be repaid in 3 years in monthly instalments,

beginning from the end of the first month. If the rate of interest is 1.5%
per month, calculate the EMI. Also construct amortization table.
Practical 6: Loan Amortization

1. A loan of Rs.40000 is to be repaid in 4 monthly instalments,

beginning from the end of the first month. If the rate of interest is
12% p.a. compounded monthly, calculate the EMI. Also construct
amortization table.
Steps:
1. In cell A1, A2, A3 write Principal, No of months and Rate of interest
respectively. In cells B1, B2, B3 write 40000, 4 and 12% p.a.
respectively. In C3 write 0.01 (12% p.a. ÷ 12 = 1 % per month and i =
r/100)
2. In cell A5 write EMI. In B5 calculate using the formula: =PMT(C3,B2,-
B1)
3. Now we will make the column headings of the amortization table. In
cells A7, B7, C7 and D7 write “Month”, “Outstanding Principal at
beginning of month”, “EMI” and “Break up of EMI” respectively.
Increase the column sizes and use “Wrap Text” from Alignment group
on the Home Tab to fit the column names appropriately. Skip E7 and
write “Outstanding Principal at end of month” in F7. In cells D8 and E8
write “Interest Component” and “Principal Repayment” respectively.
Merge cells D7 and E7 using “Merge and Centre” from Home tab. Also
merge A7 and A8, B7 and B8, C7 and C8, F7 and F8. Your column
headings should look like this:

Outstanding Principal Break up of EMI Outstanding Principal

Month at beginning of month EMI Interest Component Principal Repayment at end of month

4. In cells A9 and A10 write 1 and 2. Choose both and drag till 4 by
activating the fill handle.
5. In cell B9 write: =B1
6. In cell C9 write: =$B$5
7. In cell D9 write: ==B9*$C$3
8. In cell E9 write: =C9 – D9
9. In cell F9 write: = B9 – E9
10. In cell B10 write: =F9. Now activate the fill handle in B10 and drag till
down. Similarly activate the fill handles in C9, D9, E9 and F9 and drag
 them down one by one. The value in F12 should be 0.00.

2. Anita purchased a new sound system that was selling at

Rs.2,94,000. She agreed to make a down payment of Rs.70,000 and
to pay the remaining amount in 24 months with an interest of 18%
p.a. compounded monthly. Find EMI. Also prepare loan amortization
table.
Steps:
1. In cell A1 write SP of Sound Sytem. In B1 write 294000.
2. In cell A2 write Down Payment. In B2 write 70000.
3. In cell A4, A5, A6 write Principal, No of months and Rate of interest
respectively. In cell B4 calculate using the formula: =B1-B2. In cells B5
and B6 write 24 and 18% p.a. respectively. In C6 calculate using the
formula: =18/1200
4. In cell A8 write EMI. In B8 calculate using the formula: =PMT(C6,B5,-
B4)
5. From A10 onwards make amortization table structure as above.
6. In cells A12 and A13 write 1 and 2. Choose both and drag till 24 by
activating the fill handle.
7. In cell B12 write: =B4
8. In cell C12 write: =$B$8
9. In cell D12 write: ==B12*$C$6
[Link] cell E12 write: =C12 – D12
[Link] cell F12 write: = B12 – E12
12. In cell B13 write: =F12. Now activate the fill handle in B13 and drag
till down. Similarly activate the fill handles in C12, D12, E12 and F12
and drag them down one by one. The value in F35 should be -0.00.

3. A loan of Rs.30 lakhs is to be repaid in 3 years in monthly

instalments, beginning from the end of the first month. If the rate of
interest is 1.5% per month, calculate the EMI. Also construct
amortization table.
Steps:
1. In cell A1, A2, A3 write Principal, No of months and Rate of interest
respectively. In cells B1, B2, B3 write 3000000, 36 and 1.5% p.m.
 respectively. In C3 write 0.015 (1.5/100)
2. In cell A5 write EMI. In B5 calculate using the formula: =PMT(C3,B2,-
B1)
3. Construct the amortization table as before.
4. In cells A9 and A10 write 1 and 2. Choose both and drag till 36 by
activating the fill handle.
5. In cell B9 write: =B1
6. In cell C9 write: =$B$5
7. In cell D9 write: ==B9*$C$3
8. In cell E9 write: =C9 – D9
9. In cell F9 write: = B9 – E9
10. In cell B10 write: =F9. Now activate the fill handle in B10 and drag till
down. Similarly activate the fill handles in C9, D9, E9 and F9 and drag
them down one by one. The value in F44 should be 0.00.
Practical 7: Working with Matrices I
1. a. Enter the given data from A1 to I7
1. b. Name J1 as “Weighted Rating”
In J2 enter the formula: =3*I2+2*C2+H2
Once the value appears in J2, double click on the + sign at the bottom
right of the cell to complete the column.
1. c. We do this differently. Create a row by naming A8 as “Weight”. Enter
values 5, 3 2 and 10 in C8, E8, H8 and I8 respectively. Name K1 as New
Rating.
In K2 enter the formula: =C2*$C$8+E2*$E$8+H2*$H$8+I2*$I$8
(Recall: Using $ in the cell reference fixes the value)
Once the value appears in K2, double click on the + sign at the bottom
right of the cell to complete the column.

2. a. Enter “0” in all the cells from A10 to D12

0 0 0 0
0 0 0 0
0 0 0 0
2. b. Enter numbers in the cells from A14 to C16. Above diagonal, all numbers
must be zero, other numbers can be anything.
1 0 0
2 4 0
3 5 6

2. c. Enter numbers in the cells from A18 to E22. All numbers other than those
on the diagonal are zero.
-1 0 0 0 0
0 2 0 0 0
0 0 3 0 0
0 0 0 4 0
0 0 0 0 1.50
Practical 8: Working with Matrices II

1. A lumber yard carried wood in di↵erent prices and grades. An inventory of the number
of board feet of each kind they had is reported in a matrix A. Matrix P is the price
matrix. (Columns represent Grades I, II and III respectively and rows represent types
of woods Oak, Cherry and Pine respectively)
0 1 0 1
750 526 300 $1.80 $1.95 $3.00
A = @200 127 300A , and P = @$0.95 $1010 &2.00A
250 750 750 $3.00 $2.50 $2.00

Find the product C = AP T . What does the entry C11 represent? How much money
does the dealer have tied up in Cherry wood?
0 1 0 1
12 13 14 67 113 26
2. Construct the matrices A = @16 5 15 A and B = @ 54 42 67A in Excel.
12 14 23 54 42 67
Execute the following.

(a) Print A + B.
(b) Print AB.
(c) Print transpose of matrix A.
(d) Print the determinants of A and B.
(e) Print inverse of matrix A.
(f) Try to print inverse of B also. Is there any error? Why?
✓ ◆
7 13 6
(g) Let C = . Can you find AC? Justify.
4 2 6
(h) Can you find CA? If yes, what is the order of CA?

11
Practical 8: Working with Matrices II

1. Enter the values of the matrices – A in A1 to C3 and P in E1 to G3. Do

not use dollar symbols in matrix P. Also note P22 entry is 1.01 (there is an
error in the sheet)
Select cells for the product – A5 to C7. In the formula bar type the
formula: =MMULT(A1:C3,TRANSPOSE(E1:G3)) and press
Ctrl+Shift+Enter to get the resulting matrix C.

3275.7 1843.76 4165

1507.65 918.27 1517.5
4162.5 2495 4125

C11 represents money tied up in Oak of Grade I.

To find money tied up in Cherry wood, add all the values of the second
row of the resulting matrix C. In D6 click on autosum or enter the
formula: =SUM(A6:C6). Answer obtained: 3943.42

2. Enter the values of the matrices – A in A9 to C11 and B in E9 to G11.

a. Select cells A13 to C15. In the formula bar type the formula:
=(A9:C11)+(E9:G11) and press Ctrl+Shift+Enter
b. Select cells A17 to C19. In the formula bar type the formula:
=MMULT(A9:C11,E9:G11) and press Ctrl+Shift+Enter
c. Select cells A21 to C23. In the formula bar type the formula:
=TRANSPOSE(A9:C11) and press Ctrl+Shift+Enter
d. For determinant of A, in A25 type the formula:
=MDETERM(A9:C11) and press enter. For determinant of B, in
E25 type the formula: =MDETERM(E9:G11) and press enter.
(Note, since the two rows of B are identical, determinant is zero. B
is a singular matrix.)
e. Select cells A27 to C29. In the formula bar type the formula:
=MINVERSE(A9:C11) and press Ctrl+Shift+Enter
f. Select cells E27 to G29. In the formula bar type the formula:
=MINVERSE(E9:G11) and press Ctrl+Shift+Enter. We get a
number error as the inverse of a singular matrix does not exist.
g. Enter matrix C from I9 to K10. We cannot find AC as the number
of columns in A is 3 while the number of rows in C is 2 (they are
not same).
h. We can find CA as the number of columns in C is 3 and the number
of rows in A is also 3. To find CA, select cells A31 to C32. In the
formula bar type the formula: =MMULT(I9:K10,A9:C11) and
press Ctrl+Shift+Enter. CA is of order 2x3.
Practical 9:
Data Representation using Diagrams I
1. Consider the following set of 20 scores in a 100 marks exam.

76, 80, 78, 76, 94, 75, 98, 77, 84, 88, 81, 72, 91, 72, 74, 86, 79, 88, 72, 75,

Determine appropriate class intervals and construct frequency table (in Excel). Include
columns for relative frequency and cumulative frequency and interpret the cumulative
frequency for this case.

2. Carbon dioxide is released into atmosphere primarily by the combustion of fossil fuels
(oil, coal, natural gas). The following table lists eight countries that emit the most carbon
dioxide. Make bar graphs for the total emission and the emission per person. Display
Graph title, vertical scale and title, horizontal scale and title and legends.
09: Data Representation using Diagrams I

1. Consider the following set of 20 scores in a 100 marks exam. 76,

80, 78, 76, 94, 75, 98, 77, 84, 88, 81, 72, 91, 72, 74, 86, 79, 88,
72, 75. Determine appropriate class intervals and construct
frequency table (in Excel). Include columns for relative
frequency and cumulative frequency and interpret the
cumulative frequency for this case.
i. Enter the given marks in cells A1:A20.
ii. Select cells A1:A20, in the name box (box located to
the left side of the formula bar) enter ‘marks.
iii. Enter Minimum marks, Maximum marks in cells B1:B2
respectively.
iv. Select cell C1 and type
=MIN(marks)
v. Select cell C2 and type
=MAX(marks)
vi. Enter Class Interval, Frequency, Relative frequency,
Cumulative frequency (less than type) in cells E1:I1
respectively.
vii. Enter 71-75, 76-80, … , 95-100 in cells E2:E7
respectively.
viii. Select cells F2:F7 and type =FREQUENCY(marks, D2:D7)
and instead of enter press CTRL + SHIFT + ENTER
ix. Select cell F8 and type
=SUM(F2:F7)
x. Select cell G2 and type
=F2/$F$8
Using the fill handle enter values for the remaining rows.
xi. Select cell H2 and type
=F2
xii. Select H3 and type
=H2+F3
Using the fill handle enter values for the remaining rows.
 2. Carbon dioxide is released into the atmosphere primarily by the
combustion of fossil fuels (oil, coal, natural gas). The following
table lists eight countries that emit the most carbon dioxide.
Make bar graphs for the total emission and the emission per
person. Display Graph title, vertical scale and title, horizontal
scale and title and legends.

i. Enter Country, Total CO2 Emission (millions of metric tons), Per

person CO2 Emissions (metric tons) in cells A1:C1.
ii. Enter China, United States, India, …, Saudi Arabia in cells A2:A9
respectively.
iii. Enter the respective values in the columns B1 and C1.
iv. Select A1:B9 and go to Insert, Column and choose the first graph in
2D.
v. Select A1:A9 and C1:C9 and go to Insert, Column and choose the
first graph in 2D.
vi. Graph title: Enter the graph title as Total CO2 Emissions (millions of
metric tons). If title isn’t available in the graph then go to Chart
Tools (make sure you have selected the graph inserted), Layout,
Chart Title and insert it above the chart.
[Link] layouts the vertical scale, horizontal scale and their
respective titles along with the legend can be added.
Practical 10:
Data Representation using Diagrams
II
1. Create the following excel sheet and create a 3-dimensional column chart comparing sales
data for men and women.

2. Create a pie chart for the following data.

3. The following table gives the percentage of electrical energy generated in the United
States from various sources (2015 data). Display the data as both bar graph and pie
chart. Then write a short paragraph discussing the pros and cons of each display in terms
of interpretation.
10: Data Representation using Diagrams II
1. Create the following excel sheet and create a 3-dimensional column chart comparing sales data
for men and women.

i. Select cell A1 and type Car Sales by Gender

ii. Enter Men, Women in cells B2:C2 respectively.
iii. Enter the car names in A3:A8 respectively and the respective percentages in the other
cells.
iv. Select A2:C8 and go to Insert, Column, and under 3D choose the first option. Add a Chart
Title, legend and title on horizontal and vertical axes.
2. Create a pie chart for the following data.

i. Select cell A1 and type Favourite Cheese Data

ii. Enter Cheese Type, Number of People given in A3:B3 respectively.
iii. Enter the data given under cheese type and number of people in the respective
columns.
iv. Select cells A3:B12 and go to Insert, Pie, select the first chart under 2D.
3. The following table gives the percentage of electrical energy generated in the United States from
various sources (2015 data). Display the data as both bar graph and pie chart. Then write a short
paragraph discussing the pros and cons of each display in terms of interpretation.

i. Enter Energy source, Percentage of total energy generated in cells A1:B1 respectively.
ii. Enter the given data under Columns A1 and B1.
iii. Select cells A1:B7 and go to Insert, Column, choose the first graph.
iv. Select cells A1:B7 and go to Insert, Pie, choose the first graph under 2D.
 SAMPLE PAPER I

Note:
1. All questions are compulsory.
2. Question 1 is to be solved on the supplement provided.
3. Questions 2, 3 and 4 are to be done on the computer in MS Excel. The steps for these
are to be written on the supplement.
4. Each question carries 5 (five) marks.

Q 1. Find the present value of an annuity of Rs.12000 paid at the end of every quarter for a
year, with interest at 12% p.a. compounded quarterly.

Q 2. For the following data find average marks. Give result as “Pass” if average is >=40, else
give result as “Fail”.
Roll No Name FMS IKS Average Result
1 Simar 66 58
2 Santosh 78 69
3 Shreya 92 85
4 Shobhit 34 38

Q 3. Represent the following data using a bar chart. Give chart title and axis titles.
College No of Boys No of Girls
A 256 269
B 298 305
C 301 278
D 335 300
E 241 256

Q 4. Construct the following matrices:

(a) Identity matrix of order 4
(b) Lower Triangular matrix of order 5
 SAMPLE PAPER II

Note:
1. All questions are compulsory.
2. Question 1 is to be solved on the supplement provided.
3. Questions 2, 3 and 4 are to be done on the computer in MS Excel. The steps for these
are to be written on the supplement.
4. Each question carries 5 (five) marks.

2 −3
Q 1. If A = ( ), find A-1 if it exists.
4 0

Q 2. A loan of Rs.60000 is to be repaid in 3 equal monthly instalments, starting from the end
of the first month. If the rate of interest is 1% per month, calculate the EMI and construct
amortization table.

Q 3. Draw a pie diagram for the following data showing how a day’s activities are distributed
across 24 hours. Give chart title and data labels.
Activity Hours Spent
Sleeping 8
Studying 5
Entertainment 3
Exercise 2
Household Chores 6

Q 4. For the following data calculate commission at 10%, additional commission at 5% if

sales exceed Rs.1 lakh (to be calculated on the amount exceeding 1 lakh) and total
commission.
Salesman Sales Commission Additional Com Total Com
Bharat 65000
Beena 110000
Brijmohan 80000