__MS-EXCEL 2007 PRACTICAL TASK__

**1:- What is Excel: =**** Excel is an Electronic spreadsheet package for performing calculations related work.**

** This spreadsheet program allows you to manipulate and present your data in almost any way you choose. It allows you work on a tabular pad consisting of columns and rows where you can perform all your mathematical, financial and statistical calculations. These calculation can be as simple as totaling a row or a column of values or as complex as figuring the rate of return on an investment under varying circumstances. It also allows you to make a graphical representation of all your data in the form of 2-dimentional or 3-dimentional graph or pie chart.**

**Example: –**** ****Microsoft Office Excel, Lotus 1-2-3, Symphony, VP-Planner etc.**

**2:- How to Open Excel in Your Computer: =**

**i) Start**è** All programs**è** Microsoft Office**è** Microsoft Office Excel 2007**

** ii)Start****è**** Run…. (Type Excel in the Open Box) ****è****Press OK/Enter key from the Keyboard**

**3:- What is Cell in Excel: =**** A cell is the intersection of a column and row. Each and every cell has a unique address known as cell address. Cell address is the combination of Column name and Row number. For example, A1 is a cell address; where A is the Column name and 1 is the Row number.**

**4:-What is Worksheet in Excel: –**** ****Worksheet consists of horizontal numbered rows and vertical columns identified with letters. The resulting boxes are called cells. A cell can hold number, text and formula. A worksheet consists of ****16384**** columns (A to XFD****) and ****1048576**** rows**** (ver. – 2007****).**

**5:-What is Workbook in Excel: –**** ****A workbook is a collection of worksheets and the number of sheets for a workbook is limited to your permanent storage area. Whenever you save the file in Excel, it is stored as workbook file that has the default extension name**** (Excel-2007)**** .xls****x****. By default a workbook can hold three worksheet as sheet1, sheet2, sheet3.**

**6:- What is Workspace in Excel 2007:-**** ****Workspace is a file having extension name .xlw, which holds the references of other workbook files. You can open a group of workbooks in one step by creating a workspace file. A workspace file saves information about all open workbooks, such as their locations, window sizes, and screen positions. Procedure== File**

**è**

**save workspace**

**è**

**save.**

**6:- Number of Rows in a worksheet in version 2007 Excel – 1048576.**

** Numbers of Columns in a worksheet in version 2007 Excel– 16384.**

**7:- How you insert a New sheet in Excel 2007 Version:-**

**i) ****Right click on the sheet tab.**

**ii) ****Select insert option.**

**iii) ****Choose worksheet.**

**iv) ****Click Ok.**

**8:- How we Delete a Sheet in Excel 2007 Version:-**

**i) ****Right click on the sheet tab, which you want to delete.**

**ii) ****Select delete option.**

**iii) ****Click ok.**

**9:- How we Rename a Sheet in Excel 2007 Version:-**

**i) ****Right click on the sheet tab, which you want to rename.**

**ii) ****Select rename option.**

**iii) ****Give the name.**

**iv) ****Press enter.**

**10:- How to move a sheet within the current workbook?**

**i) ****Click the sheet on the sheet tab, which you want to move.**

**ii) ****Drag the selected sheets along the row of sheet tabs holding the left mouse button.**

**iii) ****Release the mouse button at the place where you want to keep the sheet.**

**11:- How to copy a sheet within the current workbook?**

**i) ****Click the sheet on the sheet tab, which you want to copy.**

**ii) ****Hold down CTRL, and then drag the sheet along the row of sheet tabs holding the left mouse button.**

**iii) ****Release the mouse button before you release the CTRL key at the place where you want to keep the copy of the sheet.**

**13:- Operators in Excel 2007:-**

**i) ****Mathematical Operator in Excel: –** **to perform basic mathematical operations such as addition, subtraction, or multiplication etc.**

Arithmetic operator | Meaning | Example |

+ (plus sign) | Addition | 3+3=6 |

– (minus sign) | Subtraction | 3–1=2 |

* (asterisk) | Multiplication | 3*3=9 |

/ (forward slash) | Division | 5/3=1.666667 |

% (percent sign) | Percent | 20% |

^ (caret) | Exponentiation | 3^2 (the same as 3*3)=9 |

**ii) ****Comparison Operator / Logical Operator in Excel: –**** To compare two values.**

Comparison operator | Meaning | Example |

= (equal sign) | Equal to | A1=B1 |

> (greater than sign) | Greater than | A1>B1 |

< (less than sign) | Less than | A1<B1 |

>= (greater than or equal to sign) | Greater than or equal to | A1>=B1 |

<= (less than or equal to sign) | Less than or equal to | A1<=B1 |

<> (not equal to sign) | Not equal to | A1<>B1 |

**iii) ****Text concatenation operator:-**** Text concatenation operator Use the ampersand (&) to join, or concatenate, one or more text strings to produce a single piece of text.**

Text operator | Meaning | Example |

& (ampersand) | Connects, or concatenates, two values to produce one continuous text value | “Super” & “star” produce “Superstar” |

SOME EXCEL FUNCTION | |||

TEXT FUNCTION IN EXCEL 2007 | |||

FUNCTION NAME | CALCULATION | RESULT | |

UPPER() | =UPPER(“abcd”) | ABCD | |

LOWER() | =LOWER(“ABCD”) | abcd | |

LEN() | =LEN(“RAJ”) | 3 | |

REPT() | =REPT(“RAJ “,3) | RAJ RAJ RAJ | |

RIGHT() | =RIGHT(“RAHUL”,3) | HUL | |

LEFT() | =LEFT(“RAHUL”,3) | RAH | |

MID() | =MID(“KARAN”,2,3) | ARA | |

CONCATENATE() | =CONCATENATE(“GOOD”,”MORNING”) | GOODMORNING | |

PROPER() | =PROPER(“WEBEL COMPUTER CENTRE”) | Webel Computer Centre | |

TRIM() | =TRIM(“ WEBEL”) | WEBEL | |

VALUE() | =VALUE(“123”) | 123 | |

ROWS() | =ROWS(A:A) | 65536 | |

COLUMNS() | =COLUMNS(1:1) | 256 | |

MATHEMATICAL AND TRIGONOMETRIC FUNCTION IN EXCEL 2007 | |||

FUNCTION NAME | CALCULATION | RESULT | |

ABS() | =ABS(-210.67) | 210.67 | |

COS() | =COS(0.2) | 0.980066578 | |

EVEN() | =EVEN(210.61) | 212 | |

EXP() | =EXP(3) | 20.08553692 | |

FACT() | =FACT(3) | 6 | |

INT() | =INT(210.67) | 210 | |

LOG() | =LOG(20) | 1.301029996 | |

MOD() | =MOD(5,2) | 1 | |

ODD() | =ODD(210.61) | 211 | |

PI() | =PI(19) | 30141592654 | |

POWER() | =POWER(5,2) | 25 | |

PRODUCT() | =PRODUCT(5,5,5) | 125 | |

ROMAN() | =ROMAN(5) | V | |

ROUND() | =ROUND(210.67,0) | 211 | |

SQRT() | =SQRT(25) | 5 | |

SUBTOTAL() | =SUBTOTAL() | ||

SUM() | =SUM(5,5,5) OR SUM(A1:A10) OR SUM(A1+A2) | 15 | |

SUMIF() | =SUMIF() | ||

STATISTICAL FUNCTION IN EXCEL 2007 | |||

FUNCTION NAME | CALCULATION | RESULT | |

AVERAGE() | =AVERAGE(5,5,5) | 5 | |

COUNT() | =COUNT(1,7,8,6,3) | 5 | |

COUNTIF() | =COUNTIF() | ||

MAX() | =MAX(4,5,7,9,3) | 9 | |

MIN() | =MIN(4,7,3,1,4) | 1 | |

LOOKUP AND REFERENCE FUNCTION IN EXCEL 2007 | |||

FUNCTION NAME | CALCULATION | RESULT | |

VLOOKUP() | VERTICAL LOOK UP CALCULATION | | |

HLOOKUP() | HORIZANTAL LOOK UP CALCULATION | | |

DATE AND TIME FUNCTION IN EXCEL 2007 | |||

FUNCTION NAME | CALCULATION | RESULT | |

DATE() | =DATE(2014,4,2) | 2/4/2014 | |

DAY() | =DAY(2/4/2014) | 2 | |

HOUR() | =HOUR(12:00:15) | 12 | |

MINUTE() | =MINUTE(12:01:12) | 1 | |

MONTH() | =MONTH(2/4/2014) | 4 | |

NOW() | =NOW() | 4/2/2014 12:02 | |

SECOND() | =SECOND(12:00:15) | 15 | |

TIME() | =TIME(12,2,5) | 12:02PM | |

TODAY() | =TODAY() | 2/4/2014 | |

WEEKDAY() | =WEEKDAY(2/4/2014) | 4 | |

YEAR() | =YEAR(2/4/2014) | 2014 | |

FINANCIAL FUNCTION IN EXCEL 2007 | |||

FUNCTION NAME | CALCULATION | RESULT | |

PMT() | =PMT(interest_rate, number_payments, PV, [FV], [Type] ) | | |

IPMT() | =IPMT(interest_rate, period, number_payments, PV, [FV], [Type]) | | |

PPMT() | =PPMT(interest_rate, period, number_payments, PV, [FV], [Type]) | | |

FV() | =FV() | | |

IRR() | =IRR() | | |

NPV() | =NPV(discount_rate, value1, [value2, … value_n]) | | |

PV() | =PV(interest_rate, number_payments, payment, [FV], [Type]) | | |

RATE() | =RATE(number_payments, payment, PV, [FV], [Type], [Estimate]) | | |

NPER() | =NPER(interest_rate, payment, PV, [FV], [Type]) | | |

SYD() | =SYD(cost, salvage, life, period) | | |

SLN() | =SLN() | | |

LOGICAL FUNCTION IN EXCEL 2007 | |||

FUNCTION NAME | CALCULATION | RESULT | |

AND() | =AND( condition1, [condition2], … ) | | |

IF() | =IF( condition, [value_if_true], [value_if_false] ) | | |

NOT() | =NOT( logical_value ) | | |

OR() | =OR( condition1, [condition2], … ) | | |

FALSE() | =FALSE( ) | | |

TRUE() | =TRUE( ) | |

ð __PRACTICAL TASK-1__

STOCK SHEET CALCULATION | |||||||||

SL NO | PRODUCT NAME | QUANTITY | PRICE/QUANTITY | TOTAL PRICE | DISCOUNT (10%) | TOTAL PRICE (AFTER DISCOUNT 10%) | VAT (4%) | TOTAL PRICE (WITH VAT 4%) | |

1 | COMPUTER | 12 | Rs. 18,500.00 | Rs. 222,000.00 | Rs. 22,200.00 | Rs. 199,800.00 | Rs. 7,992.00 | Rs. 207,792.00 | |

2 | PRINTER | 25 | Rs. 2,600.00 | Rs. 65,000.00 | Rs. 6,500.00 | Rs. 58,500.00 | Rs. 2,340.00 | Rs. 60,840.00 | |

3 | UPS | 45 | Rs. 1,850.00 | Rs. 83,250.00 | Rs. 8,325.00 | Rs. 74,925.00 | Rs. 2,997.00 | Rs. 77,922.00 | |

4 | KEYBOARD | 200 | Rs. 225.00 | Rs. 45,000.00 | Rs. 4,500.00 | Rs. 40,500.00 | Rs. 1,620.00 | Rs. 42,120.00 | |

5 | CD-R/W | 25 | Rs. 1,250.00 | Rs. 31,250.00 | Rs. 3,125.00 | Rs. 28,125.00 | Rs. 1,125.00 | Rs. 29,250.00 | |

6 | DVD-R/W | 45 | Rs. 1,600.00 | Rs. 72,000.00 | Rs. 7,200.00 | Rs. 64,800.00 | Rs. 2,592.00 | Rs. 67,392.00 | |

7 | MOUSE | 120 | Rs. 150.00 | Rs. 18,000.00 | Rs. 1,800.00 | Rs. 16,200.00 | Rs. 648.00 | Rs. 16,848.00 | |

8 | PEN DRIVE | 35 | Rs. 425.00 | Rs. 14,875.00 | Rs. 1,487.50 | Rs. 13,387.50 | Rs. 535.50 | Rs. 13,923.00 | |

| | | GRAND TOTAL | Rs. 551,375.00 | Rs. 55,137.50 | Rs. 496,237.50 | Rs. 19,849.50 | Rs. 516,087.00 |

**CALCULATION:-**

** TOTAL PRICE= (PRICE/QUANTITY*QUANTITY)**

** DISCOUNT= (TOTAL PRICE*10%)**

** TOTAL PRICE AFTER DISCOUNT=TOTAL PRICE-DISCOUNT**

** VAT (4%) = (TOTAL PRICE AFTER DISCOUNT*4%)**

** TOTAL PRICE WITH VAT (4%) =TOTAL PRICE AFTER DISCOUNT+ VAT(4%)**

ð __PRACTICAL TASK-2__

ð **IF() Function :-**** IF is a Logical Function are used to compare values and perform calculations accordingly.**

ð **Syntax:-**** IF(Condition, True Value , False Value), where Condition is a logical expression that can be evaluated to TRUE or FALSE, True Value is the value or expression that is returned if the condition is true and False Value is the value or expression that is returned if condition is false.**

ð **Example:-**** IF(100>50, “100 is Greater than 50” , “100 is less than 50”)**

§ **Return:- 100 is Greater than 50**

ð **Example:-**** IF(100<50,1,0)**

§ **Return:- 0**

ð **Example: –**** IF (F2>=7000, F2*15%, F2*10%)**

ð **Nested IF()**** Statement Structure for Three Conditions:-**

§ **Syntax:- IF(Condition1, True Value , IF(Condition2 , True Value , False Value))**

§ **Syntax:- IF(Condition1,IF(Condition2, True Value, False Value), False Value)**

ð __PRACTICAL TASK-3__

SL NO | NAME | AGE | STATUS |

1 | RIJU | 15 | NONVOTER |

2 | NILAS | 21 | VOTER |

3 | RAJU | 12 | NONVOTER |

4 | NILAVA | 20 | VOTER |

5 | SUMAN | 17 | NONVOTER |

**CONDITION:-**

** STATUS=IF AGE>=18 THE HE/SHE IS VOTER OR HE/SHE WILL BE NOT VOTER**

ð **AND() Function:- This Function is used to check whether multiple conditions are true simultaneously or not.**

ð **Syntax:- AND( LogicalArgument1, LogicalArgument2, LogicalArgument3, ………., LogicalArgumentN)**

ð **Example:-**

§ **AND(1+2=3, 2+3=5) Return TRUE**

§ **AND(1+2=3, 2+3=4) Return FALSE**

§ **AND(1<B15,B15<100) Return TRUE**

ð __PRACTICAL TASK-4__

SELECTION PROCEDURE | |||||||

NAME | HEIGHT | WEIGHT | CHEST | AGE | MARKS(M.P) | STATUS | |

ANIRBAN | 168 | 51 | 80 | 21 | 60 | SELECTED | |

SUMAN | 155 | 42 | 82 | 32 | 78 | CANCLED | |

TARUN | 145 | 55 | 84 | 17 | 54 | CANCLED | |

RAMESH | 166 | 58 | 81 | 22 | 61 | SELECTED | |

NIRMAL | 165 | 57 | 80 | 19 | 62 | CANCLED | |

BISWAJIT | 168 | 51 | 89 | 23 | 67 | SELECTED |

**CONDITION:-**

** STATUS= IF HEIGHT>=165 AND WEIGHT>=45 AND CHEST>=80 AND AGE>=20 AND MARKS (M.P)>=60 THEN HE/SHE WILL BE SELECTED OTHERWISE HE/SHE WILL BE CANCLED**

ð **OR() Function:- This Function is used to check whether at least a single condition out of multiple condition out of multiple is true or not.**

ð **Syntax:- OR(LogicalArgument1, LogicalArgumrnt2, LogicalArgument3, ……., LogicalArgumrntN)**

ð **Example:-**

§ **OR(1+2=3, 2+3=5) Return TRUE**

§ **OR(1+2=3, 2+3=4) Return TRUE**

§ **OR(1+2=4, 2+3=4) Return FALSE**

ð **IF() AND() Condition:-**

§ **IF(AND(Logical Condition1 , Logical Condition2),True Value, False Value)**

ð **IF() OR() Condition:-**

§ **IF(OR(Logical Condition1, Logical Condition2), True Value, False Value)**

ð __PRACTICAL TASK-5__

GRADE OF SALESMAN | |||

SALSEMAN | SALE AMOUNT | PROFIT | GRADE |

AA | 70000 | 8000 | A |

BB | 80000 | 5500 | A |

CC | 45000 | 1000 | C |

DD | 50000 | 4000 | B |

EE | 52000 | 3400 | C |

FF | 60000 | 1000 | C |

**CONDITION:-**

** 1:- ****IF SALE AMOUNT IS MORE THAN 70000 OR PROFIT IS MORE THAN OR EQUAL TO 10% OF SALE AMOUNT THEN GRADED AS A.**

** 2:- IF SALE AMOUNT IS MORE THAN 60000 OR PROFIT IS MORE THAN OR EQUAL TO 8% OF SALE AMOUNT THEN GRADED AS B OTHERWISE GRADED AS C . **

ð __PRACTICAL TASK-6__

MARK SHEET OF M.P (RESULT) | |||||||||||||||||||

FIRST LANGUAGE | SECOND LANGUAGE | HISTORY | GEOGRAPHY | LIFE SC. | PHY. SC. | MATHEMATICS | OVER ALL STATUS | ||||||||||||

| |||||||||||||||||||

ROLL NO | STUDENT NAME | MARKS OBTAINED | GRADE | MARKS OBTAINED | GRADE | MARKS OBTAINED | GRADE | MARKS OBTAINED | GRADE | MARKS OBTAINED | GRADE | MARKS OBTAINED | GRADE | MARKS OBTAINED | GRADE | TOTAL | PERCENTAGE | OVERALL GRADE | PERFORMANCE INDICATOR |

1 | ANIRBAN KUNDU | 35 | B | 58 | B+ | 65 | A | 37 | B | 69 | A | 54 | B+ | 65 | A | 383 | 54.71% | B+ | GOOD |

2 | SUKUMAR PATRA | 29 | C | 57 | B+ | 39 | B | 35 | B | 45 | B+ | 38 | B | 58 | B+ | 301 | 43.00% | B | SATISFACTORY |

3 | SAIKAT JANA | 48 | B+ | 69 | A | 84 | A+ | 69 | A | 84 | A+ | 69 | A | 79 | A | 502 | 71.71% | A | VERY GOOD |

4 | RAMGOPAL BARMAN | 14 | D | 23 | D | 47 | B+ | 27 | C | 24 | D | 21 | D | 18 | D | 174 | 24.86% | D | DISQUALIFIED |

5 | SANJU MAITY | 82 | A+ | 89 | A+ | 86 | A+ | 88 | A+ | 92 | AA | 96 | AA | 99 | AA | 632 | 90.29% | AA | OUTSTANDING |

**CONDITION:-**

** GRADE=1:- IF MARKS>=90 THEN GRADED IS AA**

** 2:- IF MARKS>=80 THEN GRADED IS A+**

** 3:- IF MARKS>=60 THEN GRADED IS A**

** 4:- IF MARKS>=45 THEN GRADED IS B+**

** 5:- IF MARKS>=35 THEN GRADED IS B**

** 6:- IF MARKS>=25 THEN GRADED IS C**

** 7:- IF MARKS>BELOW 25 THEN GRADED IS D**

ð __PRACTICAL TASK-7__

CALCULATION | QUATERLY | FIXED | MONTHLY | INSTALLMENT |

DEPOSITE AMOUNT | 10000 | 50000 | 5000 | |

DURATION | 15 | 12 | 10 | 15 |

RATE | 6.50% | 5.50% | 6% | 6% |

INSTALLMENT | 500 | | 1250 | Rs557.98 |

RETURN AMOUNT | Rs76,473.05 | Rs95,060.37 | Rs205,873.43 | 150000 |

ð __PRACTICAL TASK-8__

TET APPEAR TESTING-2014(FEB-MAR) | ||||

EXAM NAME | GRADUATE | POST GRADUATE | MARKS(GRADUATE) | TET STATUS |

WBSSC | PASS | P/G | 50% | TET APPEAR |

WBSSC | HONS | NILL | 60% | TET APPEAR |

WBSSC | PASS | APPEAR | 41% | NOT APPEAR |

WBSSC | PASS | NILL | 36% | NOT APPEAR |

WBSSC | PASS | P/G | 45% | TET APPEAR |

WBSSC | HONS | P/G | 47% | TET APPEAR |

WBSSC | PASS | NILL | 61% | TET APPEAR |

ð __PRACTICAL TASK-9__

PIVOT TABLE | ||

NAME | DESIGNATION | SALARY |

S.ROY | MANAGER | 20000 |

R.SEN | ACCOUNTER | 18000 |

K.MAJI | CASHIER | 15000 |

P.SAMANTA | WORKER | 10000 |

A.SEN | PEON | 12000 |

K.SAMANTA | MANAGER | 20000 |

P.ROY | CASHIER | 15000 |

S.KHAN | WORKER | 10000 |

S.SEN | PEON | 12000 |

G.MAJI | ACCOUNTER | 18000 |

PIVOT TABLE RESULT | |||||||

Sum of SALARY | DESIGNATION | | | | | | |

NAME | ACCOUNTER | CASHIER | MANAGER | PEON | WORKER | Grand Total | |

A.SEN | | | | 12000 | | 12000 | |

G.MAJI | 18000 | | | | | 18000 | |

K.MAJI | | 15000 | | | | 15000 | |

K.SAMANTA | | | 20000 | | | 20000 | |

P.ROY | | 15000 | | | | 15000 | |

P.SAMANTA | | | | | 10000 | 10000 | |

R.SEN | 18000 | | | | | 18000 | |

S.KHAN | | | | | 10000 | 10000 | |

S.ROY | | | 20000 | | | 20000 | |

S.SEN | | | | 12000 | | 12000 | |

Grand Total | 36000 | 30000 | 40000 | 24000 | 20000 | 150000 |

ð __PRACTICAL TASK-10__

FINANCIAL FUNCTION | |||

LOAN AMOUNT | Rs100,000.00 | ||

RATE OF INTEREST | 6% | ||

DURATION | 8 | ||

INSTALLMENT | Rs1,314.14 | PMT | |

LOAN PORTION | Rs814.14 | PPMT | |

INTEREST PORTION | Rs500.00 | IPMT |

ð __PRACTICAL TASK-11__

A to Z Software Pvt. Ltd. | | |||||||||||||||||

| | | | | Staff Salary for the month of March 30, 2014 | | | | | | ||||||||

Staff Salary Sheet | | | | | | | | | | Date: 01/03/2014 | ||||||||

ID | Name | Post | Basic | House Rent Allowance | Medical Allowance | Vehicle Allowance | Other Allowance | Gross Salary | Working day | Attendance | Holiday | Overtime Hours | Gross Payable | Life Insurance | Advance | Other Deduction | Net Payable | Signature of Receiver |

1 | AMIT | MD | 60000 | 6000 | 3000 | 3000 | 1200 | 73200 | 30 | 26 | 4 | 18 | 84180.00 | 1200 | | | 82980.00 | |

2 | ANUP | CHAIRMAN | 50000 | 5000 | 2500 | 2500 | 1000 | 61000 | 30 | 26 | 4 | 12 | 67100.00 | 1000 | | 700 | 65400.00 | |

3 | SUMAN | ACCOUNTS | 45000 | 4500 | 2250 | 2250 | 900 | 54900 | 30 | 15 | 4 | 2 | 35685.00 | 900 | | | 34785.00 | |

4 | TAPAS | CLARK | 10000 | 1000 | 500 | 500 | 200 | 12200 | 30 | 25 | 4 | | 11793.33 | 200 | 5000 | | 6593.33 | |

5 | SAMIR | ASST. MANAGER | 40000 | 4000 | 2000 | 2000 | 800 | 48800 | 30 | 26 | 4 | | 48800.00 | 800 | | | 48000.00 | |

6 | PULAK | TYPIST | 15000 | 1500 | 750 | 750 | 300 | 18300 | 30 | 23 | 4 | 5 | 17232.50 | 300 | 2357.5 | | 14575.00 | |

7 | SANDIP | INCHARGE | 25000 | 2500 | 1250 | 1250 | 500 | 30500 | 30 | 12 | 4 | | 16266.67 | 500 | | | 15766.67 | |

8 | TARUN | PION | 12000 | 1200 | 600 | 600 | 240 | 14640 | 30 | 26 | 4 | | 14640.00 | 240 | | | 14400.00 | |

9 | SUBHA | HELPER | 10000 | 1000 | 500 | 500 | 200 | 12200 | 30 | 26 | 4 | | 12200.00 | 200 | | 500 | 11500.00 | |

10 | SAGAR | SECURITY | 5000 | 500 | 250 | 250 | 100 | 6100 | 30 | 26 | 4 | | 6100.00 | 100 | | | 6000.00 | |

Total | 313997.50 | 5440 | 7357.5 | 1200 | 300000.00 | | ||||||||||||

TOTAL (IN WORDS): THREE LAKH ONLY. | | | | |

ð __PRACTICAL TASK-12__

PROFIT SHEET OF A COMPANY | ||||||||

COST PURPOSE | COMPANY NAME | |||||||

AA | BB | |||||||

VARIABLE COST(VC) | Rs. 50.00 | Rs. 75.00 | ||||||

METIRIAL COST(MC) | Rs. 100.00 | Rs. 150.00 | ||||||

FIXED COST(FC) | Rs. 7,000.00 | Rs. 7,000.00 | ||||||

SELLING PRICE(SP) | Rs. 250.00 | Rs. 275.00 | ||||||

COMPANY RESULT = AA | ||||||||

DATE | UNIT | V.C. | M.C. | F.C. | T.C. | S.P. | PROFIT | |

1/1/2014 | 0 | Rs. 0.00 | Rs. 0.00 | Rs. 7,000.00 | Rs. 7,000.00 | Rs. 0.00 | -Rs. 7,000.00 | |

2/1/2014 | 200 | Rs. 10,000.00 | Rs. 20,000.00 | Rs. 7,000.00 | Rs. 37,000.00 | Rs. 50,000.00 | Rs. 13,000.00 | |

3/1/2014 | 500 | Rs. 25,000.00 | Rs. 50,000.00 | Rs. 7,000.00 | Rs. 82,000.00 | Rs. 125,000.00 | Rs. 43,000.00 | |

4/1/2014 | 350 | Rs. 17,500.00 | Rs. 35,000.00 | Rs. 7,000.00 | Rs. 59,500.00 | Rs. 87,500.00 | Rs. 28,000.00 | |

5/1/2014 | 700 | Rs. 35,000.00 | Rs. 70,000.00 | Rs. 7,000.00 | Rs. 112,000.00 | Rs. 175,000.00 | Rs. 63,000.00 | |

6/1/2014 | 480 | Rs. 24,000.00 | Rs. 48,000.00 | Rs. 7,000.00 | Rs. 79,000.00 | Rs. 120,000.00 | Rs. 41,000.00 | |

7/1/2014 | 610 | Rs. 30,500.00 | Rs. 61,000.00 | Rs. 7,000.00 | Rs. 98,500.00 | Rs. 152,500.00 | Rs. 54,000.00 | |

TOTAL PROFIT | Rs. 235,000.00 | |||||||

ð __PRACTICAL TASK-13__

GOAL SEEK EXAMPLE | ||||||

EX:=1 | ||||||

SALESMAN | SALEAMOUNT | BASIC | COMMISSION | TA | TOTAL | |

A ROY | 65000 | 2000 | 3250 | 1300 | 6550 | |

| 85714.28571 | 2000 | 4285.714286 | 1714 | 80 | |

EX:= 2 | ||||||

PURCHASE PRICE | Rs42,027.02 | Rs57,923.17 | ||||

DOWN PAYMENT | Rs8,405.40 | Rs14,480.79 | ||||

REST AMOUNT | Rs33,621.61 | Rs43,442.38 | ||||

RATE | 6% | 6.50% | ||||

DURATION | 5 | 5 | ||||

INSTALLMENT | Rs650.00 | Rs850.00 |

ð __PRACTICAL TASK-14__

SUBTOTALS EXAMPLE | ||||

AC NO | NAME | DATE | DEPOSITE | |

AC1 | AA | M1 | 850 | |

AC1 | AA | M2 | 1200 | |

AC1 | AA | M3 | 920 | |

AC1 Total | 2970 | |||

AC2 | BB | M1 | 700 | |

AC2 | BB | M2 | 580 | |

AC2 | BB | M3 | 1200 | |

AC2 Total | 2480 | |||

AC3 | CC | M1 | 900 | |

AC3 | CC | M2 | 960 | |

AC3 | CC | M3 | 1650 | |

AC3 Total | 3510 | |||

Grand Total | 8960 |

ð __PRACTICAL TASK-15__

BANKING SYSTEM | |||||||

AC NO | NAME | DATE | DEPOSIT | WITHDRAWL | CURRENT BALANCE | AC BALANCE | |

SB/001 | SAGAR | 1/4/2014 | 5000 | | 5000 | 5000 | |

SB/001 | SAGAR | 2/4/2014 | 2000 | | 2000 | 7000 | |

| | | | | 0 | 7000 | |

| | | | | 0 | 7000 |

ð __PRACTICAL TASK-16__

VALIDATION EXAMPLE | |||||

NAME | COURSE | FEES | CATEGORY | FEES TO BE PAID | |

RAJU | DSA | 7000 | EXCHANGE | 3500 | |

SYAM | DTP | 4800 | OTHER | 4320 | |

NIRMAL | IMPACT | 2400 | EXCHANGE | 1200 | |

SUDIP | DFA | 4800 | GENERAL | 4800 | |

DSA | 7000 | GENERAL | FULL PAID | ||

DFA | 4800 | EXCHANGE | HALF PAID | ||

CCA | 1500 | OTHER | 10% LESS | ||

IMP | 2400 | ||||

IMP+ | 3600 | ||||

DITA | 6000 |

ð __PRACTICAL TASK-17__

DEPOSITE | 50000 | EX:- 1 | TABLE CALCULATION | | ||

DURATION | 5 | | ||||

RATE | 6% | D E P O S I T E | ||||

RETURN | Rs66,911.28 | Rs.55,000.00 | Rs60,000.00 | Rs75,000.00 | Rs100,000.00 | Rs125,000.00 |

YE A R | 8 | 87,661.64 | 95,630.88 | 119,538.61 | 159,384.81 | 199,231.01 |

10 | 98,496.62 | 107,450.86 | 134,313.58 | 179,084.77 | 223,855.96 | |

12 | 110,670.81 | 120,731.79 | 150,914.74 | 201,219.65 | 251,524.56 | |

15 | 131,810.70 | 143,793.49 | 179,741.86 | 239,655.82 | 299,569.77 | |

18 | 156,988.65 | 171,260.35 | 214,075.44 | 285,433.92 | 356,792.39 | |

| | |||||

DEPOSITE | 50000 | | ||||

DURATION | 5 | EX:- 2 | | |||

RATE | 6% | | ||||

RETURN | Rs66,911.28 | 55000 | 60000 | 75000 | 100000 | 125000 |

R A T E | 7% | Rs55,224.8 | Rs60,245.2 | Rs75,306.5 | Rs100,408.7 | Rs125,510.9 |

8% | Rs55,257.0 | Rs60,280.3 | Rs75,350.4 | Rs100,467.2 | Rs125,584.0 | |

9% | Rs55,289.2 | Rs60,315.5 | Rs75,394.3 | Rs100,525.8 | Rs125,657.2 | |

10% | Rs55,321.4 | Rs60,350.6 | Rs75,438.3 | Rs100,584.4 | Rs125,730.5 | |

11% | Rs55,353.7 | Rs60,385.8 | Rs75,482.3 | Rs100,643.0 | Rs125,803.8 |

ð __PRACTICAL TASK-18__

| | GRADE SCALE | GRADE | PERFORMANCE INDICATOR |

VLOOK UP | 90 | AA | OUTSTANDING | |

80 | A+ | EXCELLENT | ||

60 | A | VERY GOOD | ||

45 | B+ | GOOD | ||

35 | B+ | SATISFACTORY | ||

25 | C | MARGINAL | ||

24 | D | DISQUALIFIED | ||

RESULT CALCULATION | ||||

ROLL NO | NAME | MARKS | GRADE | PERFORMANCE INDICATOR |

200571B | SANDIP | 90 | AA | OUTSTANDING |

200572B | SUVENDU | 45 | B+ | GOOD |

200573B | TAPAS | 60 | A | VERY GOOD |

200574B | LAXMI | 80 | A+ | EXCELLENT |

200575B | DILIP | 25 | C | MARGINAL |

ð __PRACTICAL TASK-19__

| HLOOK UP CALCULATION | | |||

ITEM CODE | 101 | 102 | 103 | 104 | 105 |

ITEM NAME | COMPUTER | MONITOR | UPS | HARD DISK | MOUSE |

ITEM PRICE | 25000 | 6000 | 2500 | 6000 | 200 |

ITEM STOCK | 15 | 25 | 30 | 25 | 20 |

ð __PRACTICAL TASK-20__

PIE CHART |

COLUMN CHART |