PRACTICAL-01&02 Essentials of Computer AND Spredsheet Modelling Manvi Sogani 2020BCMH006

Download as docx, pdf, or txt
Download as docx, pdf, or txt
You are on page 1of 20

PRACTICAL-01&02

ESSENTIALS OF
COMPUTER
AND
SPREDSHEET MODELLING
MANVI SOGANI
2020BCMH006
PRACTICAL-01
TIME-40minutes
PURPOSE
 After completing this practical, I am
aiming to find out the differences in fees
depending upon the institute and subject.
STEPS PERFORMED IN THIS
ACTIVITY
 The case study was read and understood
thoroughly.
 The information which was given in the
question was noted.
 Then the values were calculated for fees which
were varied by the differences of institute and
subjects.
 Through those values total fees was calculated.

RESOURCE MATERIAL
USED
 Case Study
 Laptop
 Microsoft Excel
 Microsoft Word

SKILLS ACQUIRED
 INDEX-MATCH Function
 SUMPRODUCT Function
 After completing this project, I am able
to use effective spreadsheet modelling as
best to my use.

CONCEPT INVOLVED
 The fees were calculated using index-
match function.

The formula for which is as follows-


D25=INDEX($B$16:$E$21,MATCH(B25,$A$16:$
A$21,0),MATCH(C25,$B$15:$E$15,0))
Then drag it till D32.

i.e. Index(select the range of all the fees that is


varying according to subjects and institutes,
Match(select the institute name, select the
range of institutes, select 0 for exact match),
Match( select the subject name, select the
range of subjects, select 0 for exact match))

 The total fees is calculated using


sum-product function.
The formula is as follows-
D34= SUM(D25:D32)
i.e. sum(selecting the first value of fees in
the range : selecting the last value of fees
in the range)

EXCEL SHEETS

Tuition Fees
Institution
Name Physics Chemistry Maths Biology
Yogi Sir 4000 5000 8000 5000
Merit India 6500 6500 6500 6500
Scholar 7000 10000 8000 5000
Score Well 5000 5500 7000 7500
Cent Percent 5500 5500 70000 7000
Top Ranker 3500 3500 4000 4000

Name Of Institution
Student Name Subject Fee (Rs)
AAA Yogi Sir Maths 8000
AAA Yogi Sir Chemistry 5000
BBB Merit India Maths 6500
BBB Cent Percent Physics 5500
BBB Merit India Chemistry 6500
CCC Score Well Biology 7500
CCC Top Ranker Maths 4000
DDD Scholar Biology 5000
Total     48000

CONCLUSION
 After completing this assignment, I am
able to calculate index-match function,
sum-product function.
PRACTICAL-02
TIME- 70minutes
PURPOSE
 After completing this assignment, I
should be able to use advance filter in excel
efficiently.
 I am aiming to find out high deposits
and high withdrawal for a certain time
period.
 I also aim to find out deposits and
withdrawals between two dates.

STEPS PERFORMED IN THIS


ACTIVITY
 The case study was read and understood
thoroughly.
 The information which was given in the
question was noted.
 Then the calculation for finding out high
deposit and high withdrawal was done.
 Then, the calculation was done to find out the
deposits and withdrawals between two dates.

RESOURCE MATERIAL
USED
 Case Study
 Laptop
 Microsoft Excel
SKILLS ACQUIRED
 SUMIFS Function
 IF Function
CONCEPT INVOLVED
 The high deposit of the data was
calculated using IF Function.
The formula is as follows-
E7= IF (B7>=$B$3,"*","")
And drag this till where the range ends i.e.
E13.
The formula is-
IF(deposit >= flag deposit, it will show *, if
not it will be blank)
 The high withdrawal of the data was
calculated using IF Function.
The formula is as follows-
F7= IF (C7>=$B$4,""," ←")
And drag this till where the range ends i.e.
F13.
The formula is -
IF(deposit <= flag withdrawal, it will show
blank, if not it will show ←)

 For changing the color of the cell having


high deposit, I used conditional formatting.
Select the range of deposits, then click on
conditional formatting, select new rules
then select format only cell that contain.
Select format only cells with - cell values,
greater than or equal to, then type the
amount of flag deposit.
Then click on format, and format the cell as
you wish. For this, I selected the fill color as
red.
 For changing the color of the cell having
high withdrawals, I used conditional
formatting.

Select the range of withdrawals, then click


on conditional formatting, select new rules
then select format only cell that contain.
Select format only cells with - cell values,
greater than or equal to, then type the
amount of flag withdrawal.
Then click on format, and format the cell as
you wish. For this, I selected the fill color as
green.
 For calculating deposits, between two
dates SUMIFS Function is used.

F2=SUMIFS(B7:B13,$A$7:$A$13,">="&$E$2,
$A$7:$A$13,"<="&$E$3)
The formula is
F2= SUMIFS(select the range of deposit,
select the range of dates, then using >= first
date, select the range of dates,<= select the
last date).
 For calculating withdrawals, between
two dates SUMIFS Function is used.
G2=SUMIFS(C7:C13,$A$7:$A$13,">="&$E$2
,$A$7:$A$13,"<="&$E$3)
The formula is
G2= SUMIFS(select the range of
withdrawals, select the range of dates, then
using >= first date, select the range of
dates,<= select the last date).

EXCEL SHEETS
Saving Amount      
Opening balance 50000    
Closing Balance      
       
Flag Deposit >= 100000 with *
Flag Withdrawal >= 20000 with ←

High High
Date Deposit Withdrawal Balance Deposit Withdrawal
01-07-2011 40000   50000    
05-07-2011   15000 35000    
10-07-2011   10000 25000    
16-07-2011 150000   175000 *  
23-07-2011   30000 145000   ←
24-07-2011   10000 135000    
25-07-2011 15000   150000    

  Date Deposit Withdrawal Balance


Date-1 10-07-2011
150000 40000 110000
Date-2 23-07-2011
CONCLUSION
 I am able to use different functions
effectively like SUMIFS Function, IF Function
and Conditional Formatting.
 After completing this assignment, I am
able to use spreadsheet more effectively

You might also like