Learn EXCEL with practical exercises


Télécharger Learn EXCEL with practical exercises

Formation Excel en ligne par vidéo

205 leçons vidéos + 20 Livres PDF + 20 TP + Sous supervision + Certificat de réussite à la fin du cours



★★★★★★★★★★3.5 étoiles sur 5 basé sur 1 votes.
Votez ce document:

Télécharger aussi :


Open a new Excel workbook and save it as "Solutions to Exercises"

RENAME SHEET1 AS "Basic Concepts".In this sheet copy the following table and >

EMP CODENAME         DEPT        DESGN    BASIC SALDA           HRA          TOTAL

                                                 ACCN                                 3000

                                                 ACCN                                 4000

                                                 SALES                                1500

                                                 SALES                                3000

                                                 ACCN                                 1650

                                                 SALES                                1560

                                                 PROD                                 3000

                                                 ADMIN                                1750

                                                 PROD                                 3000

                                                 SALES                                1575

                                                 ACCN                                 4000

                                                 ACCN                                 2000

                                                 SALES                                3000

                                                 PROD                                 5000

                                                 ACCN                                 4000

1.Insert an autofill series for the employee code in the format A01,A02…. Etc

2.Generate a list of the employees working in your organisation, in the same order automatically by taking the na

Deepak        Inderjit       Arun          Tarun        George      Nirmala     Rajan        Naseer      Arjun         Saji

3. Under the Desgn Column,add a suitable validation in a manner that the entries can be either "manager","sr ma (note: the user should choose the desgn options from a drop down should not be allowed to make any o Then insert the Dsignations in the following order for each of the employees:

Deepak        Inderjit       Arun          Tarun        George      Nirmala     Rajan        Naseer      Arjun         Saji

Manager       Sr ManagerExecutive Manager     Executive Executive Manager      Executive Manager    Executive

4.Format the DEPT column such that all the Similar depts have the same format

5.Insert the following comment against the Designation Column heading

Please choose the designations from the drop-down menu against each of the cells.

6.Create an AutoCorrect entry for your company the same as the Heading for this Salary statement.

8.Save the "Solutions to Exercises" file with a Password such that only you are allowed to access it.

9.Rename Sheet2 as " Dummy".Copy the updated data in "Basic Concepts sheet" and paste it in a manner that a (To check change the basic salary of Arun to 1750 and observe the changes.

10.Create Cell Names for each of the Column headings in the Above table. Using these Cell Names, in the Dum and the Gross Salaries for all the Employees

11.Transpose the above table and paste it in the "Dummy Sheet"

ames from the list below:

Kapil             Rahul        Saket        Sanjay       Geetha

anager","executive" or "admin"

other entries nor should he be allowed to type)

Kapil             Rahul        Saket        Sanjay       Geetha

Sr ManagerExecutive Manager        Executive Sr Manager

any changes made to the original table will be reflected in the "Dummy" sheet

mmy Sheet ,calculate the Total Basic,Hra,DA


 

Rename Sheet 3 as "Functions" and derive solutions for the following queries:

CALCULATING ESTIMATES

You have received an enquiry for the supply of open storage tanks of one metre diameter. You have to estimate the cost and prepare quotations with the details given below.

Consider the Table given below:

Drawing no: AAB/PROJ/PL2/EQP3/ASS1

Material requirement for the tank of one metre diameter:

SLNO

ITEM/MATERIAL

Wt

 

Wastage allowance

   

(kg)

 

0.1

1

ms sheet 4mm

 

109

 

2

ms p1 6 mm

 

41

 

3

ms p1 10mm

 

4

 

4

ms p1 10mm

 

0.8

 
 

TOTAL MATERIAL COST

     

Material cost@ Rs.20/kg.

QUESTION A

1.Calculate wastage at 10% on the relevant weights for each material/ITEM and enter it in the

2.Calculate total weight by adding weight and wastage allowance for each material/ITEM in th

3.Open a new column and calculate the cost by multiplying TOTAL WEIGHT COLUMN with th

Rename this column as "COST"

4.Calculate the TOTAL MATERIAL COST FOR ALL THE ITEMS/MATERIALS

QUESTION B

1.Calculate the following:(PRINT RESULTS IN THE SHADED GREEN AREAS)

CONVERSION COST

a.Shell Rolling Cost

=(TOTAL WT. OF ITEM 1 * Rs 3/kg)

b.LEG CUTTING/EDGE PREPARATION/WELDING COST =(TOTAL WT. OF ITEM 2,3,4 * Rs 3/kg)

c.WELDING COST: 8 MAN HOURS* Rs 25/hr

500

d.DRILLING/ASSEMBLY/PAINTING

TOTAL CONVERSION COST

QUESTION C

BOUGHT OUT COMPONENTS

 

QUESTION D

CONSOLIDATION

0

1 RAW MATERIAL COST

(PRINT TOTAL MATERIAL COST VALUE FROM QUESTION A USING PASTE SPECIAL)

2 LABOUR CHARGES

(PRINT TOTAL CONVERSION COST VALUE FROM QUESTION B USING PASTE SPECIAL)

3 BOUGHT OUT COMPONENTS

(PRINT TOTAL BOUGHT OUT COST VALUE FROM QUESTION C USING PASTE SPECIAL)

4 CONSOLIDATED TOTAL

=RAW MATERIAL COST+LABOUR CHARGES+BOUGHT OUT COMPONENTS

QUESTION E

Calculate the following:

1 ADMIN

=30% * CONSOLIDATED TOTAL

2 COMMISSION

=5% * CONSOLIDATED TOTAL

3 NEGOTIATION MARGIN

=10% * CONSOLIDATED TOTAL

4 TOTAL QUOTE

=ADMIN+COMMISION+NEGOTIATION+CONSOLIDATED TOTAL

20

 

e WASTAGE ALLOWANCE COLUMN the TOTAL WEIGHT COLUMN. he MATERIAL COST


 

 

Sl No

Name

Month

Account

Sales

 

Sl No

Name

Month

1.

Wilson

May

BCD Corp

35000

17.

Benedict

July

               

 
               

July Max


   

2.

Lorenzo

May

Rosebud

C      45000

 

12.

Benedict

June

               

 
               

June Max


   

3.

Wilson

May

Gen Corp

26000

 

7.

Benedict

May

               

   

4.

Benedict

May

OH Assoc

78000

 

4.

Benedict

May

               

 
               

May Max


   

5.

Horwitz

May

World Inc

55000

 

15.

Horwitz

July

               

   

6.

Wilson

May

Rosebud

C      68000

 

18.

Horwitz

July

               

 
               

July Max


   

7.

Benedict

May

BCD Corp

95000

 

10.

Horwitz

June

               

   

8.

Lorenzo

May

World Inc

15000

 

13.

Horwitz

June

               

 
               

June Max


   

9.

Lorenzo

June

Gen Corp

20000

 

5.

Horwitz

May

               

 
               

May Max


   

10.

Horwitz

June

BCD Corp

37000

 

19.

Lorenzo

July

               

 
               

July Max


   

11.

Wilson

June

Gen Corp

40000

 

9.

Lorenzo

June

               

   

12.

Benedict

June

Rosebud

C      36000

 

14.

Lorenzo

June

               

 
               

June Max


   

13.

Horwitz

June

World Inc

14000

 

2.

Lorenzo

May

               

   

14.

Lorenzo

June

OH Assoc

55000

 

8.

Lorenzo

May

               

 
               

May Max


   

15.

Horwitz

July

BCD Corp

65000

 

16.

Wilson

July

               

 
               

July Max


   

16.

Wilson

July

OH Assoc

80000

 

11.

Wilson

June

               

 
               

June Max


   

17.

Benedict

July

Rosebud

C      72500

 

1.

Wilson

May

               

   

18.

Horwitz

July

Gen Corp

66000

 

3.

Wilson

May

               

 

19.

Lorenzo

July

World Inc

125000

6.

Wilson

May

May Max Grand Max

Highest sales month-wise for each month

Sl No

Name

Month

Account

Sales

1.

Wilson

May

BCD Corp

35000

2.

Lorenzo

May

Rosebud

C      45000

3.

Wilson

May

Gen Corp

26000

4.

Benedict

May

OH Assoc

78000

5.

Horwitz

May

World Inc

55000

6.

Wilson

May

Rosebud

C      68000

7.

Benedict

May

BCD Corp

95000

8.

Lorenzo

May

World Inc

15000

9.

Lorenzo

June

Gen Corp

20000

10.

Horwitz

June

BCD Corp

37000

11.

Wilson

June

Gen Corp

40000

12.

Benedict

June

Rosebud

C      36000

13.

Horwitz

June

World Inc

14000

14.

Lorenzo

June

OH Assoc

55000

15.

Horwitz

July

BCD Corp

65000

16.

Wilson

July

OH Assoc

80000

17.

Benedict

July

Rosebud

C      72500

18.

Horwitz

July

Gen Corp

66000

19.

Lorenzo

July

World Inc

125000


   

Account

Sales

Rosebud

C      72500

Rosebud

C      72500


 
 

72500


   

Rosebud

C

36000

Rosebud

C

36000


 
 

36000


   

BCD Corp

95000

BCD Corp

        95000


   

OH Assoc

78000

OH Assoc

        78000


 
 

95000


   

BCD Corp

65000

BCD Corp

        65000


   

Gen Corp

 

66000

Gen Corp

M

66000


 
 

66000


   

BCD Corp

37000

BCD Corp

        37000


   

World Inc

 

14000

World Inc

M

14000


 
 

37000


   

World Inc

 

55000

World Inc

M

55000


 
 

55000


   

World Inc

125000

World Inc

M 125000


 
 

125000


   

Gen Corp

 

20000

Gen Corp

M

20000


   

OH Assoc

55000

OH Assoc

        55000


 
 

55000


   

Rosebud

C

45000

Rosebud

C

45000


   

World Inc

 

15000

World Inc

M

15000


 
 

45000


   

OH Assoc

80000

OH Assoc

        80000


 
 

80000


   

Gen Corp

 

40000

Gen Corp

M

40000


 
 

40000


   

BCD Corp

35000

BCD Corp

        35000


   

Gen Corp

 

26000

Gen Corp

M

26000


 

Rosebud C        68000

Rosebud

C

68000

68000 x            125000


 

Rename Sheet 4as "Filters" and derive  solutions for the following queries:

QUESTION A

Sl No

Name

Month

Account

Sales

1.

Wilson

May

BCD Corp

35000

2.

Lorenzo

May

Rosebud Corp

45000

3.

Wilson

May

Gen Corp

26000

4.

Benedict

May

OH Assoc

78000

5.

Horwitz

May

World Inc

55000

6.

Wilson

May

Rosebud Corp

68000

7.

Benedict

May

BCD Corp

95000

8.

Lorenzo

May

World Inc

15000

9.

Lorenzo

June

Gen Corp

20000

10.

Horwitz

June

BCD Corp

37000

11.

Wilson

June

Gen Corp

40000

12.

Benedict

June

Rosebud Corp

36000

13.

Horwitz

June

World Inc

14000

14.

Lorenzo

June

OH Assoc

55000

15.

Horwitz

July

BCD Corp

65000

16.

Wilson

July

OH Assoc

80000

17.

Benedict

July

Rosebud Corp

72500

18.

Horwitz

July

Gen Corp

66000

19.

Lorenzo

July

World Inc

125000

QUESTION B

The following table shows the distribution of the major subjects taken by students of various ethnic gro aim of this table is to analyse the popularity of some of the majors.

 

Gender

Age

Ethnicity

Planned major

Category

 

1

male

18

white

business

business

 

2

male

18

white

electrical engineering

engineering

 

3

male

19

white

biology

natural science

 

4

male

18

white

maths

math

 

5

male

19

white

computer science

computer science

 

6

male

19

white

computer science

computer science

 

7

male

20

white

graphic design

fine and performing art

 

8

male

19

hispanic

secondary education

education

 

9

male

20

african-american

business

business

 

10

male

19

african-american

political science

social science

 

11

female

22

white

business

business

 

12

female

19

white

french

humanities

 

13

female

18

white

anthropology

social science

 

14

female

19

white

english

humanities

 

15

female

20

white

elementary education

education

 

16

female

21

white

computer science

computer science

 

17

female

19

african-american

secondary education

education

 

18

female

21

asian

early childhood education

education

19

female

19

hispanic

english

humanities

20

female

22

african-american

political science

social science

21

male

19

white

theatre

fine and performing art

22

male

19

hispanic

communications

humanities

Using Advanced Filters obtain the following filtered records:

a.Create a criteria range that displays the details of "african-american" ethnic groups who have majored

b.Create a criteria range that displays the details of all "whites" who are majoring in either "business" o

c.Create a criteria range that displays the details off all "male " "whites" who are studying under the cat

d.Create a criteria range that displays the details of all students above 18 who are majoring in " graphic

e.Create a criteria range that displays the details of all students of all students who are between 20 and

f.Create a criteria range that displays the details of all "females"  majoring in either "english" or "theatre Answer a

 

Gender

Age

Ethnicity

Planned major

Category

 

10

male

19

african-american

political science

social science

 

20

female

22

african-american

political science

social science

Answer b

 

Gender



Age

Ethnicity

Planned major

Category

 

1

male

18

white

business

business

 

5

male

19

white

computer science

computer science

 

6

male

19

white

computer science

computer science

 

11

female

22

white

business

business

 

16

female

21

white

computer science

computer science

Answer c

 

Gender

Age

Ethnicity

Planned major

Category

 

1

male

18

white

business

business

 

2

male

18

white

electrical engineering

engineering

 

3

male

19

white

biology

natural science

 

4

male

18

white

maths

math

 

5

male

19

white

computer science

computer science

 

6

male

19

white

computer science

computer science

 

7

male

20

white

graphic design

fine and performing art

 

8

male

19

hispanic

secondary education

education

 

9

male

20

african-american

business

business

 

10

male

19

african-american

political science

social science

 

11

female

22

white

business

business

 

12

female

19

white

french

humanities

 

13

female

18

white

anthropology

social science

 

14

female

19

white

english

humanities

 

15

female

20

white

elementary education

education

 

16

female

21

white

computer science

computer science

 

17

female

19

african-american

secondary education

education

 

18

female

21

asian

early childhood education

education

 

19

female

19

hispanic

english

humanities

 

20

female

22

african-american

political science

social science

21

male

19

white

theatre

fine and performing art

22

male

19

hispanic

communications

humanities

Answer d

 

Gender

Age

Ethnicity

Planned major

Category

 

5

male

19

white

computer science

computer science

 

6

male

19

white

computer science

computer science

 

7

male

20

white

graphic design

fine and performing art

 

16

female

21

white

computer science

computer science

Answer e

 

Gender

Age

Ethnicity

Planned major

Category

 

7

male

20

white

graphic design

fine and performing art

 

9

male

20

african-american

business

business

 

11

female

22

white

business

business

 

15

female

20

white

elementary education

education

 

16

female

21

white

computer science

computer science

 

18

female

21

asian

early childhood education

education

 

20

female

22

african-american

political science

social science

Answer f

 

Gender

Age

Ethnicity

Planned major

Category

 

14

female

19

white

english

humanities

 

19

female

19

hispanic

english

humanities

Total gross salary of all the employess in HR only

Total gross salary of managers only No. of people in sales only

people with the rating above 12.

Use Auto Filters to get the following results(Paste the results in a dif

a.The details of Wilson

b.The details of Lorenzo for May

c.The details of Benedict and Horwitz for the month of June and July

d.The details of clients whose sales is in the range of 50,000 to 1,50,0

e.Accounts of  Rosebud Corp and Gen Corp

f.Accounts of Lorenzo and Horwitz where the sales amount is greater

main

 

College

business

engineering

arts and sciences

arts and sciences

arts and sciences

arts and sciences

arts and sciences

education

business

arts and sciences

business

arts and sciences

arts and sciences

arts and sciences

education

arts and sciences

education

education

 

 

arts and sciences

 

 

arts and sciences

 

 

arts and sciences

 

 

arts and sciences

 

       

d in " political science". or "computer science"

tegory of "fine and performing arts"

c design"or are majoring in "computer science"

 22 years old e"

College

arts and sciences

arts and sciences

College

business

arts and sciences

arts and sciences

business

arts and sciences

College

business

engineering

arts and sciences

arts and sciences

arts and sciences

arts and sciences

arts and sciences

education

business

arts and sciences

business

arts and sciences

arts and sciences

arts and sciences

education

arts and sciences

education

education

arts and sciences

arts and sciences

 

arts and sciences

arts and sciences

College

arts and sciences

arts and sciences

arts and sciences

arts and sciences

College

arts and sciences

business

business

education

arts and sciences

education

arts and sciences

College

arts and sciences

arts and sciences


 

fferent location)

000

r than 50,000.

College



   

Question B

Using SUMIF,evaluate the following:

 

InvoiceNum

Office

Amount

DateDue

Today

Difference

AG-0145

Oregon

$5,000.00

2-Apr

6-May

-34

 

AG-0189

California

$450.00

20-Apr

6-May

-16

 

AG-0220

Washington

$3,211.56

29-Apr

6-May

-7

 

AG-0310

Oregon

$250.00

1-May

6-May

-5

 

AG-0355

Washington

$125.50

5-May

6-May

-1

 

AG-0409

Washington

$3,000.00

11-May

6-May

5

 

AG-0581

Oregon

$2,100.00

24-May

6-May

18

 

AG-0600

Oregon

$335.39

24-May

6-May

18

 

AG-0602

Washington

$65.00

29-May

6-May

23

 

AG-0633

California

$250.00

31-May

6-May

25

 

                         
 

TOTAL

Total no of days past due days

Total amount past due days

Total amount for Oregon only

Total amount for all except Oregon

Total amount with due date beyond Apri

-63

9037.06

$7,685.39

7102.06

6125.89


 

Rename Sheet 5 as "Multiple" and derive solutions for the following queries:

QUESTION A

EMPLOYEE SALARY STATEMENT

                          Consider the following salary statement of ABC LTD.,                               current date

CURRENT DATE=31ST DECEMBER 1999

Name

Annual Salary

Monthly Salary

Location

Date Hired

 

James Brackman

$42,400

New York

03/01/98

 

 

Michael Prenthal

$20,900

New York

04/16/97

 

 

Francis Jenikins

$67,700

Arizona

11/12/98

 

 

Peter Yates

$19,950

Arizona

04/05/99

 

 

Walter Franklin

$43,000

Arizona

03/28/99

 

 

Louise Victor

$48,500

Conneticut

07/05/97

 

 

Sally Rice

$24,500

New York

06/16/97

 

 

Charles K. Barkley

$52,000

Conneticut

09/09/98

 

 

Melinda Hindquest

$102,000

New York

06/04/97

 

 

Linda Harper

$24,000

Arizona

02/16/99

 

             

1.Calculate the MONTHLY SALARY for each employee as

MONTHLY SALARY=ANNUAL SALARY/12

2.Format the Table so as to represent all employees from the similar locations accord

LOCATION

FORMAT

New York

Bold Italic,Dark red

Arizona

Bold Italic, Dark Green

Conneticut

Bold Italic, Dark blue

3. Calculate the WORK EXPERIENCE of each employee as

WORK EXPERIENCE=CURRENT DATE-DATE HIRED

NOTE: FORMAT THE EXPERIENCE COLUMN TO DISPLAY AS NUMBER WITH A SING

4.Assign a category to each employee on the following rule:

a.           IF Experience is less than or equal to  2 years, than category="JU

b.          iF Experience is greater than 2 years, than category="SENIOR"

4. Calculate the Income Tax for each employee as follows

a.           if the ANNUAL SALARY IS LESS THAN OR EQUAL TO $50,000, TH

b.          if the ANNUAL SALARY IS BETWEEN $50,000 TO $ 80,000, THEN

c.           if the ANNUAL SALARY IS GREATER THAN OR EQUAL TO $80,00

QUESTION B

1.Calculate the Total Annual Salary Location-wise and the Maximu Performance Ratin

2.Consider the following Table.Return the missing details in this report by referring to

Name

Michael Prentha

Louise Victor

Mathews

Melinda Hind

Department

Production

Sales

Production

Sales

Designation

Executive

Manager

Sr Manager

CEO

Location

???

???

???

???

QUESTION C

                                                                                                     Height                             Weight

Sl. No          Name                      Age                    (in cms)                           (in kgs)        Qualification

1.       Ravi B. S   21         157       55 GR

2.       Shanker    19         161       60 PGR

3.       Arun Kumar          20         163       59 P.U.C

4.       Praveen Sharma   22         161       62 PGR

5.       Ahmed R.  23         165       62 GR

6.       Sebastian D’Souza           20         170       63 P.U.C

7.       Gautam Kapoor     22         162       64 GR

8.       Kiran Patil 23         163       63 PGR

9.       Mahesh Gupta       25         164       62 GR

10.       Dinesh Kumar       22         165       61 P.U.C

The above is a database of applicants for recruitment in the Defense Forces. The Selection proc Test 1 : The candidate’s age  should  be 21 or above.

Test 2 : The candidate’s age  should  be 21 or above and his height should be above 161 cms.

Test 3 : The candidate’s age  should  be 21 or above, his height should be above 161 cms and  h Test 4 : The candidate’s age  should  be 21 or above, his height should be above 161 cms , his w a.You are to do the following :

ØEnter the given data into an Excel Spreadsheet.

ØCreate a Column for each test. Enter the IF Function so as to display a YES fo

ØCopy the formulae for all the candidates.         2 08/19/2005     12/31/1999

Exempt

Performance

Rating             Experience

Category RANK

Income Tax

                 FALSE                     7                    1

                 FALSE                     6                    2

TRUE  8          1 FALSE           9          0

                 FALSE                     8                    0

                 FALSE                     5                    2

                 FALSE                     6                    2

                 FALSE                     7                    1

                   TRUE                     5                    2

                 FALSE                     8                    0

ding to the following colour combinations

GLE DECIMAL POINT.

UNIOR"

HEN INCOME TAX IS ZERO  INCOME TAX IS 20% OF ANNUAL SALARY 00, THEN INCOME TAX=30% OF ANNUAL SALARY

ng Location-wise o the Master table (USE VLOOKUP/HLLOKUP)

Linda Harper

Production

Executive

???

Test 1              Test2              Test3             Test4

y        n          n n       n          n n       n          n y         n          n y        y          y n        n          n y         y          y y        y          y y        y          y y         y          n

cess consists of four eligibility tests for which criteria is given.

3

his weight should 60 kgs or above. weight should 60 kgs or above and he should be either a Graduate (GR) or a PostGraduate (PGR). or the eligible  candidates and a NO for the candidates who are not eligible.

Insert a new sheet and Rename this Sheet  as "Logic Practice-1" and derive solutions for th On the basis of the pricing fixed below,arrive at the sales price for each of the gallo

Olive Oil Logic - 1

Cost/gallon for the first 500 gallons:        $23 Cost/gallon for gallons above 500:        $20

Number of gallons:

                                                                 10                                               23                230

                                                               483                                              23            11109

                                                               500                                              20            10000

                                                              1600                                             20            32000

he following query: on sales made below:

Logic Used for the Olive Oil

Problem in the Proficiency

Exercises

Insert a new sheet and Rename this Sheet  as "Grade evaluation" and derive solutions for the following q

Consider the following table where the marks scored by the students have been listed below: On the basis of the scores, obtained assign the grades to each of the students.

score

grade

Student Name

Score

Grade

Deepak

45

Err:508

Inderjit

90

 

Arun

78

 

Tarun

52

 

George

63

 

Nirmala

68

 

Rajan

69

 

Naseer

64

 

Arjun

52

 

Saji

31

 

Kapil

35

 

Rahul

63

 

Saket

68

 

Sanjay

66

 

Geetha

100

 

Raghav

58

 

<=35 d 35-60 c

                                                                                                                                                                                    60-85        b

                                                                                                                                                                                    >=85          a

query:

Insert a new sheet and Rename this Sheet  as "Logic Practice-2" and derive solutions for the

On the basis of the pricing fixed below,arrive at the sales price for each of the gallon s

Number of gallons:

 

Proble

1,600

30500

 

483

11109

 

2001

36515

 

Olive Oil Logic - 2

                          Cost/gallon for the first 500 gallons:             $23

                          Cost/gallon for the next 500 gallons:             $20

                          Cost/gallon for gallons >1,000:                                           $15 Logic

 
 
 

 

 following query: sales made below:

 Used for the Olive Oil em in the Proficiency

Exercises

Data

dummy

One instance of each value

Data

Data

 
 

1

FALSE

Unique values

 

1

1

 

1

FALSE

   

2

2

 

1

TRUE

   

3

3

 

2

TRUE

   

4

4

 

3

TRUE

   

5

5

 

4

FALSE

   

6

6

 

4

FALSE

   

7

7

 

5

FALSE

   

8

8

 

6

FALSE

       
 

7

FALSE

       
 

8

FALSE

       

Data

1

2

3

4

5

6

7

8

Benefit Calculations

Write formulas to calculate the Retirement Contribution and Health Plan Costfor each employee.

Name

Employm

t. Status

Health Plan

Salary

Hire Date

Retireme

# Years         nt

Employe Contribu      Health

          d          tion    Plan Cost

Gopnik

Mahfouz

Bryson

Peters deVries

Talento

Yang

Marks

Heller

part time full time full time full time full time

part time full time

part time full time

family family individual individual individual family

other plan family family

$45,000 $120,000

$145,000

$100,000

$115,000 $55,000

$95,000

$15,000

$124,000

Jan-98

May-89

Mar-01

Nov-00

Jul-97

Aug-95

Apr-99

May-01

Oct-00

5

 

10000

13

 

10000

2

 

8000

2

 

8000

5

 

8000

7

 

10000

4

 

0

1

 

10000

2

 

10000

The Retirement Contribution Calculation Instructions

The company contributes to each eligible employee’s retirement plan at the rate of 4% of the employee’s annual salary. However, to be eligible for this benefit, an employee must have full-time status with two or more years of employment. A calculation for the retirement contribution requires  a test of two conditions: Full- or part-time status and number of years of employment.

The Health Plan Cost Calculation Instructions

The company supplies two health plan options:

· Up to $10K of annual coverage for employees who choose the family plan.

· Up to $8K of annual coverage for employees who choose the individual plan. These benefits do not apply if the employee or employee-and-family are already covered by some other health plan. A calculation for health insurance requires a test of three conditions: Individual, Family, Already Covered.

Rename Sheet 6 as "Database Functions" and derive solutions for the following queries:

The following illustration shows a database for a small orchard. Each record contains information about o

 

Tree

Height

Age

 

Yield

 

Profit

Apple

18

 

20

 

14

105

Pear

12

 

12

 

10

96

Cherry

13

 

14

 

9

105

Apple

14

 

15

 

10

75

Pear

9

 

8

 

8

76.8

Apple

8

 

9

 

6

45

 

Tree

Height

Age

Yield

Apple

     

Tree

Height

Age

Yield

Apple

 

>10

 

Tree

Height

Age

Yield

                                                                                                                                         Apple        >10

Using the Appropriate function return the following details

1.  Furnish details of the number of apple trees whose height ranges between 10 and 16 units.

2.  calculate the highest profit between apple and pear trees

3.  Calculate the least profit of those apple trees over 10 years old

4.  Calculate the Total Profit from Apple Trees

5.  Calculate the Total Profit from apple trees with a height between 10 and 16 units 6. Calculate the product of the yields from apple trees aged above 10 years

7. Calculate the average yield of all apple trees over 10 feet in height.

1      Err:504

2      105

3      75

4      225

5      75

6      140

7      12

Tree

Height

Age

Yield

Profit

Apple

 

>10

   

one tree.

Profit

Profit

Profit

Height

<16

1

105

75

225

75

140

12

Rename Sheet 7 as "Consolidate" and derive  solutions for the following queries:

The following are  reports for 1992 and 1993 sales for the Eastern and Western regions of a tour com

Eastern 1992

   
 

Qtr 1

Qtr 2

Qtr 3

Qtr 4

Golf

5,000

2,000

1,500

2,000

Safari

9,000

6,000

4,000

5,000

Tennis

1,500

500

600

1,500

Total Sale

s     15,500

8,500

6,100

8,500

Western 1992



     
 

Qtr 1

Qtr 2

Qtr 3

Qtr 4

Windsurfi

n       1,800

5,000

6,500

1,750

Golf

3,500

2,500

6,430

4,590

Tennis

6,000

3,200

4,070

5,000

Total Sale

s     11,300

10,700

17,000

11,340

Eastern 1993

     
 

Qtr 1

Qtr 2

Qtr 3

Qtr 4

Golf

5,500

1,500

1,400

2,500

Safari

10,000

6,500

4,400

4,500

Tennis

1,000

800

550

1,000

Total Sale

s     16,500

8,800

6,350

8,000

Western 1993

     
 

Qtr 1

Qtr 2

Qtr 3

Qtr 4

Windsurfi

n       1,850

4,000

5,500

1,550

Golf

1,500

2,500

4,075

2,500

Tennis

6,500

2,000

4,590

5,055

Total Sale

s       9,850

8,500

14,165

9,105

Use the Consolidate to obtain a total of the above data.

Link the Consolidated data to the source data such that if a change is made in the source mpany.

e data this is reflected in the report.

Advanced Fitlers solutions

Gender

Age

Ethnicity

Planned major

Category

     

african-american

political science

 

Gender

Age

Ethnicity

Planned major

Category

     

white

business

 

white

computer science

Gender

Age

Ethnicity

Planned major

Category

 

male

   

white

fine and performin

Gender

Age

Ethnicity

Planned major

Category

   

>18

>18

 

computer science

 

graphic design

Gender

Age

Ethnicity

Planned major

Category

   

>=20

     

Gender

Age

Ethnicity

Planned major

Category

female       english female   theatre

Pivot table Question A Solution

pagerow

1Ethnic groupsPlanned majors Gender

2GenderPlanned majors

3College

Ethnicity

Age

Gender

Planned majors

Ethnicity

                                                                                                                   4College                           Ethnicity

                                                                                                                   5College                           Category

Question c

1 Desgn

Dept

2

Dept

3 Desgn

Dept,emp

4

Dept,Desgn

5 Dept

Desgn


 

College

College

College

ng art

College

College

Age

<=22

College

col              data

Count of sl no

count of sl no

Count  of sl no

Max age max age Max annd min Gross sala

Max rating

Average Gross

Min Rating

Count of Sl no

Rename Sheet 9 as "Pivot Table" and derive solutions for the following queries:

QUESTION A

The following table shows the distribution of the major subjects taken by students of various aim of this table is to analyse the popularity of some of the majors.

 

Gender

Age

 

Ethnicity

Planned

mCategory

College

 

1

male

 

18

white

business

business

business

 

2

male

 

18

white

electrical

eengineerin

engineering

 

3

male

 

19

white

biology

natural sci

arts and sciences

 

4

male

 

18

white

maths

math

arts and sciences

 

5

male

 

19

white

computer

scomputer

sarts and sciences

 

6

male

 

19

white

computer

scomputer

sarts and sciences

 

7

male

 

20

white

graphic de

fine and p

earts and sciences

 

8

male

 

19

hispanic

secondary

education

education

 

9

male

 

20

african-am

business

business

business

 

10

male

 

19

african-am

political sc

social scie

arts and sciences

 

11

female

 

22

white

business

business

business

 

12

female

 

19

white

french

humanitie

sarts and sciences

 

13

female

 

18

white

anthropol

osocial scie

arts and sciences

 

14

female

 

19

white

english

humanitie

sarts and sciences

 

15

female

 

20

white

elementar

yeducation

education

 

16

female

 

21

white

computer

scomputer

sarts and sciences

 

17

female

 

19

african-am

secondary

education

education

 

18

female

 

21

asian

early child

education

education

 

19

female

 

19

hispanic

english

humanitie

sarts and sciences

 

20

female

 

22

african-am

political sc

social scie

arts and sciences

 

21

male

 

19

white

theatre

fine and p

earts and sciences

 

22

male

 

19

hispanic

communic

humanitie

sarts and sciences

1.Generate a Pivot Table report for each of the following

a.Display which majors are most  popular with men and women of different ethnic groups.

b.Display which majors are most popular with various ethnic groups, of different age groups,

c.Display which majors are most popular with men or women of different ethnic groups for ea

d.Display the maximum age group of students belonging to different ethnic groups from each

e.Display the maximum age group of students belonging to different categories from each co

QUESTION B

The Following data Represents the monthly expenses incurred by you.Consolidate the same in the Column Field  of the report)

                          Rent         Ec bill       Water bill Provisionscable         self

jan

5000

1000

250

3500

180

2500

 

feb

5000

700

200

2750

180

2500

 

march

5000

850

150

3000

180

2500

 

april

5000

650

260

2900

180

2500

 

may

     

1000

 

2000

 

                      Rent         Ec bill

Water bill

Provisionscable        self

   

may                   5000

1000

250

           2500            180

2500

 

june                   5000

700

200

           2750            180

2500

 

july                    5000

850

150

3000

2500

 

august               5000

650

260

           2900            180

2500

 

september         5000

700

260

           3000            180

2500

 

october             5000

780

250

           2800            180

2500

 

november          5000

790

243

           3500            180

2500

 

december          5000

800

285

           3200            180

2500

 

august

     

180

QUESTION C

1500

 

slno

empname dept

 

desgn

basic

hra            gross

rating

 
 

1 shanthi        hr

 

mgr

2563

1281.5

3844.5

5

 

2 aishwarya hr

 

executive

4000

2000

6000

6

 

3 sandeep  sales

 

sr mgr

4000

2000

6000

4

 

4 simran     prod

 

executive

4000

2000

6000

8

 

5 veer            hr

 

mgr

3652

1826

5478

7

 

6 sachin     sales

 

sr mgr

4000

2000

6000

9

 

7 bin laden  prod

 

mgr

2589

1294.5

3883.5

6

 

8 ambani        hr

 

sr mgr

4000

2000

6000

8

 

11 brinda    prod

 

sr mgr

4000

2000

6000

4

 

12 deepak  prod

 

mgr

2365

1182.5

3547.5

5

 

13 vishnu      Qc

 

executive

2500

1250

3750

9

 

14 anand       stores

executive

2580

1290

3870

6

 

15 lokesh       prod

executive

2365

1182.5

3547.5

8

 

16 vijaya        Qc

sr mgr

4000

2000

6000

4

 

17 sreevidya stores

mgr

3000

1500

4500

5

 

18 jasim         Qc

mgr

3000

1500

4500

8

 

19 venu          hr

sr mgr

4000

2000

6000

3

                           

1.  Maximum and Minimum Gross Salary for each Desgn,Dept-wise

2.  Maximum Rating Department-wise

3.  Average Gross Salary for each Designation,dept-wise,employee-wise

4.  Least rating Department-wise,Desgn-wise

5.Number of people in each dept,desgn-wise

s ethnic main

     

,` for each gender group(male/female) ach college. h college. ollege.

 into a single report using Pivot Tables(Do not keep any data


 

Rename Sheet 12 as "Charts" and derive solutions for the following queries:

INCOME COMPARISON

Being the senior accounts executive of your company,it is your reposnsibility to make the year-e statements ready. This year there is an additional responsibility given to you. You have been asked to prepare the income statement,showing the

revenue,sales and operating expenses for the previous three a statement will show th figures for the three years ,so that a realist projection can be made for the current year The Format for the statement is as follows

ABC LIMITED

For the period 2000-2002(Rs. In Lakhs)

REVENUE

2000

2001

2002

Gross Sales

70000

75000

90000

Sales Returns

20000

23000

28000

NET SALES

50000

52000

62000

       

COST OF SALES

2000

2001

2002

Beginning Inventory

35000

36000

42000

Goods Purchased

12000

16500

18500

Total Goods Available

47000

52500

60500

Ending Inventory

3600

4200

4350

Total cost of Goods Consumed

43400

48300

56150

Gross Profit

6600

3700

5850

       

OPERATING EXPENSES

2000

2001

2002

Selling

6200

7300

4600

General/Administrative

4450

5275

6000

Total Operating Expenses

10650

12575

10600

Income before Taxes

-4050

-8875

-4750

Taxes on Income

-1822.5

-3993.75

-2137.5

Net Profit

-2227.5

-4881.25

-2612.5

You are required to do the following

QUESTION A

1.Calculate NET SALES using the formula: NET SALES=GROSS SALES-SALES RETURN

2.Calculate TOTAL GOODS AVAILABLE by adding Beginning Inventory and Goods Purchased 3.Calculate TOTAL COST OF GOODS SOLD,using the formula:

TOTAL COST OF GOODS CONSUMED=TOTAL GOODS AVAILA 4.Calculate GROSS PROFIT as:

NET SALES -TOTAL COST OF GOODS CONSUMED

5.Calculate Total Operating Expenses by adding Selling and General/Administrative Expenses.

6.Calculate Income before Taxes as:

GROSS PROFIT-TOTAL OPERATING EXPENSES

7.Calculate Taxes on Income as a percentage(45%) of Income before Taxes

8.Calculate NET PROFIT AFTER TAX by subtracting TAXES ON INCOME from INCOME BEFORE T

QUESTION B

NOTE: Place the charts as an object in the same sheet where the solution to this question exists

1.Create a Line chart to depict the Selling and General/administrative expenses for the the three y

2.Create a Column chart to depict NET PROFIT VS GROSS PROFIT for the three years

3.Create a Pie chart to depict the NET PROFIT VS GROSS PROFIT  for the year 2002

ending

he comparitive

ABLE-ENDING INVENTORY

TAX

years

Practice:

1.  Create a Macro for Conditional Formatting on the basis of the following conditions

IF the cell value is negative then apply a red colour shading to your cell iF the cell value is positive then apply a blue colour shading to your cell IF the cell value is Zero then apply a Green colour shading to your cell.

2.  Create the following function macro and run the macro on the following table

 Insert a new column called Income tax at the end of the table.Create a user defined f

Total<=10000,income tax=0

Total between 10000 to 30000,income tax=10% of total sal

Total >=30000, income tax=20% of total sal

slno

 

empname

age

 

dept

desgn

basic

hra

 

da

 

gross

 

1

shanthi

 

25

hr

mgr

3000

 

1500

 

750

5250

 

2

aishwarya

 

26

hr

sr mgr

4000

 

2000

 

1000

7000

 

3

sandeep

 

28

sales

sr mgr

4000

 

2000

 

1000

7000

 

4

simran

 

26

prod

sr mgr

4000

 

2000

 

1000

7000

 

5

veer

 

24

hr

mgr

3000

 

1500

 

750

5250

 

6

sachin

 

29

sales

sr mgr

4000

 

2000

 

1000

7000

 

7

bin laden

 

26

prod

mgr

3000

 

1500

 

750

5250

 

8

ambani

 

25

hr

sr mgr

4000

 

2000

 

1000

7000

 

9

brinda

 

28

prod

sr mgr

4000

 

2000

 

1000

7000

 

10

deepak

 

30

prod

mgr

3000

 

1500

 

750

5250

 

11

anita

 

40

Qc

mgr

3000

 

1500

 

750

5250

 

12

sebastian

 

30

Qc

Executive

2000

 

1000

 

500

3500

 

13

leema

 

45

Qc

mgr

3000

 

1500

 

750

5250

 

14

vincent

 

24

Stores

mgr

3000

 

1500

 

750

5250

 

15

shantha

 

20

Stores

Executive

2000

 

1000

 

500

3500

 

16

david

 

45

Qc

sr mgr

4000

 

2000

 

1000

7000

 

17

manu

 

30

Qc

Executive

2000

 

1000

 

500

3500

function to calculate the income tax as

Income Tax



5