MS Excel Activities - 1st year: 1st Sem PDF

Preview

Summary

An activity given by Prof. Erwin Acorda for students to be familiar with MS Excel. This activity would enhance their skills and knowledge about the said software. ...

Description

Activity 1: Navigating Worksheet Microsoft Excel 2007

Objective/s: Enable the students to manage data in a worksheet and its components, discover different data formats and enable them to do the efficient way of entering data such as series. 1. Open a new excel workbook. 2. Type the following data to the corresponding cell address:

1 2 3 4 5 6 7

A

B

C

D

E

Product Selling Price Product Cost

Freight Cost

Tax and Tariff

Markup

Selling Price

23.50 19.00

5.25 3.40

2.35 1.9

22.35

1.25

2.235

24.15 18.25

2.10 3.30

2.415 1.825

3. Set the font size into Century Gothic, 11, Regular 4. Change column width to fit contents. 5. Center-align all column headings. 6. Set the font size of all column headings into Century Gothic, 12, Boldface. 7. Insert 3 columns before column A and type the following: A Line Item 1

B Product Name Bath Soap Lotion(Sachet) Detergent Bar

C Date of Last Purchase 04/15/2008 05/10/2008 04/15/2008

Shampoo(Sachet)

05/25/2008

Dishwashing Paste

06/09/2008

8. Fill the Line Item column with a number series starting at 1. Use Fill Series. 9. Move the content of cell C2 to A2. 10. Set the font size of the main heading into Century Gothic, 14, Boldface.

11. Merge cells across A1 to H1. 12. Compute for the markup at 5% based on the sum of Product Cost, Freight Cost, and Tax and Tariff. 13. Compute for the Selling Price. (Formula: get the sum of columns D to G). 14. Change date format (date of last purchase) to this format: April 15, 2007. 15. Set decimal places to 2. (Markup and Selling Price only) 16. Move the contents of cell A1 to H8, to cell B5 to I12. 17. Copy contents of cell B5 to I12 and paste in cell B15 to I22. 18. Change the name of the worksheet to “Selling Price”. 19. Delete worksheets 2 and 3. 20. Save workbook with filename “MS EXCEL ACTIVITY 1”.

Activity 2: Formula Microsoft Excel 2007 Objective/s: Familiarize the students in the use of excel formulas in performing mathematical operations. This will also enable the students to use referenced values both the absolute and relative addressing. 1. Open a new excel workbook. 2. Type the following data to the corresponding cell addresses: A 1 2 3 4 5 6 7 8 9 10 11 12

B

C

D

E

F

Sales Performance Employee

January Sales 300000 120000 350000 63000 25440 1500000

Total Commission Rate

5%

Commission

February Sales 150000 285000 452100 252130 562210 150000

Commission

March Sales 350000 180000 232100 162130 75000 300000

3. Center align the months to Sales and Commission columns using merge cell. 4. Compute the commission per month using the rate at cell B12. 5. Compute the Total Sales and Total Commission. 6. Set the font size into Century Gothic, 11, Regular. 7. Save the workbook as MS EXCEL ACTIVITY 2.

G

Commission

H

Total Sales

I

Total Commission

Activity 3: Formula Microsoft Excel 2007 1. Open a new excel workbook and type the following to the corresponding cells. A 1 2 3

B 1

1

C

D

E

F

G

H

I

J

K

4 5 6 7 8 9 10 11

2. Fill the range A2 to A11 with numbers 1 to 10 using Fill-Series. 3. Fill the range B1 to K1 with numbers 1 to 10 using Fill-Series. 4. Create a multiplication table out of the two number series. 5. Set the font size into Century Gothic, 11, Regular. 6. Save the workbook as MS EXCEL ACTIVITY 3.

Activity 4: Formula Microsoft Excel 2007 1. Open a new workbook. 2. Type the following data to the corresponding cell addresses: Year-End Income Statements for Company ABC

19981999

19992000

20002001

20012002

20022003

20032004

20042005

20052006

20062007

20072008

Sales 300000 400000 234000 650322 678909 453456 700909 456547 999878 999609 Less 3432 3214 3456 22222 65454 23416 70007 85456 37657 65004 Discounts Net Sales Less Cost of Goods 120000 101000 45000 300000 200000 100050 300000 90000 100000 200000

Sold Gross Profit Less Administrative 56000 Expenses Miscellaneous 2000 Expenses Other 1233 Expenses Net Profit Before Tax Less Income Tax (35%) Net Profit After Tax

54000

53000

34000

47000

56456

19760

45389

45000

78000

2300

2100

2000

4500

6700

4500

5670

9000

9750

1456

1234

2345

1000

3500

3490

3800

4050

5000

3. Compute for the Net Sales from year 1998-2008. (sales minus discount) 4. Get the Average. (Insert as the last column) 5. Compute for the Gross Profit (net sales minus cost of goods sold) 6. Compute for the Net Profit Before Tax (gross profit minus the sum of administrative miscellaneous and other expenses) 7. Less 35% income tax. 8. Compute profit after tax (net profit before tax-income tax) 9. Set the font size into Century Gothic, 11, Regular. 10. Save workbook as “MS EXCEL ACTIVITY 4”. Activity 5: Formula Microsoft Excel 2007 Objective/s: Enable the students to use AutoFilter in managing data in a worksheet. This will allow students to present the data in a flexible manner. 1. Open a new workbook. 2. Type the following data to the corresponding cell’s address: A

B

1

C

D

January

E February

2

Employee

Sales

Commission

Sales

Commission

3

Rizal,

300000

15000

150000

7500

Jose 4

Bonifacio, Andres

120000

6000

285000

14250

5

Mabini, Apolinario

350000

17500

452100

22605

6

Luna, Antonio

63000

3150

252130

12607

7

Silang, Diego

254440

12722

562210

28111

8

Del Pilar, Gregorio

1500000

75000

150000

7500

F 1

G

H

I

March

2

Sales

Commission

Total Sales

Total Commission

3

350000

17500

800000

40000

4

180000

9000

585000

29250

5

232100

11605

1034200

51710

6

162130

8106.5

477260

23863

7

75000

3750

891650

44582.5

8

300000

15000

1950000

97500

3. Sort the data arranged by Total Commission. 4. Sort the data arranged by Employees then by Total Sales. 5. Select the range A1 to I1 and apply filter to the range. 6. Filter the data by name. 7. Filter the data where Total Commission is less than 10,000. 8. Filter the data where Total Sales is greater than 500,000 and less than 1,000,000. 9. Remove the Filter. 10. Set the font size into Century Gothic, 11, Regular. 11. Set the font size of all column headings into Century Gothic, 12, Boldface. 12. Save the workbook as “MS EXCEL ACTIVITY 5”.

Activity 6: If Function Microsoft Excel 2007 Objective/s: Manage the data entry and processes based on a certain condition. 1. Open a new workbook and type the following to the corresponding cells:

1

A Agent #

2 3 4 5 6 7 8 9

8011 8012 8034 8045 8046 8052 8063 8064

B Last Name

C First Name Reyes Michael De Leon Ruth Dela Cruz Janice Gomez Raymond Villanueva Robert Concepcion Shella Sta. Maria Veronica Ramos Eugene

D Total Sales 9800 3800 13500 12000 6300 7600 21000 10500

E Commission

10 11

8071 8095

Santiago Cruz

Percy Hazel

19800 15000

2. Set the font size into Century Gothic, 11, Regular. 3. Set the font size of all column headings into Century Gothic, 12, Boldface. 4. Center-align all column headings. 5. Change display format of column D and E to comma style with 2 decimal places. 6. Compute commission based on the table below. (Commission = Total Sales * % Commission) From 0 5,001.00 10,001.00 15,001.00 20,001.00

to 5,000.00 10,000.00 15,000.00 20,000.00 up

% commission 10% 15% 20% 25% 30%

7. Save with filename “MS EXCEL ACTIVITY 6”. Activity 7: Vertical Lookup and IF Function Microsoft Excel 2007 Objective/s: Manage the data entry and processes based on a certain criteria present in a reference set of values. 1. Open a new workbook. 2. Type the following to the corresponding cells: B Last Name

2 3

A Employee No. 20019865123 20019865249

4 5

1

Rizal Bonifacio

C First Name Jose Andres

D E Monthly Remark Sales 750000 600000

20019865276

Luna

Antonio

1500000

20019865386

Mabini

Apolinario 1200000

6

20019865496

Del Pilar

Gregorio

400000

7

20019865514

Quezon

Manuel

150000

8

20019865548

Aguinaldo

Emilio

500000

9

20019865792

Roxas

Manuel

3000000

10

20019865843

Quirino

Elpidio

450000

11

20019865973 Magsaysay

Ramon

700000

G

H

1

400000

“POOR”

2

500000

“GOOD”

3

1000000

“VERY GOOD”

4

3000000

“EXCELLENT”

3. Get the Remark using lookup function (reference: G1 to H4). 4. Change column width of each column to 12. 5. Wrap text in cell F1. 6. Set the font size into Century Gothic, 11, Regular. 7. Save with filename “MS EXCEL ACTIVITY 7”.

Activity 8: Vertical Lookup and IF Function Microsoft Excel 2007 1. Open a new workbook and type the following: Ten(10) customers were asked to test the new computers to be launched by SAN BEDA COMPUTER COMPANY . They were then asked to assess the performance of these new computers, based on the following criteria. Speed – 22% Span of Memory – 24%

User-Friendliness – 28% Design – 26%

The ratings are as follows: Respondents Speed Span of Design UserMemory Friendliness 1 Aguinaldo, 76 93 90 92 Emilio 2 Bonifacio, 54 99 91 98 Andres 3 Del Pilar, 55 98 87 98 Gregorio

4 5 6 7 8 9 10

Luna, Antonio Mabini, Apolinario Magsaysay, Ramon Quezon, Manuel Quirino, Elpidio Rizal, Jose Roxas, Manuel Average:

Respondents 1 Aguinaldo, Emilio 2 Bonifacio, Andres 3 Del Pilar, Gregorio 4 Luna, Antonio 5 Mabini, Apolinario 6 Magsaysay, Ramon 7 Quezon, Manuel 8 Quirino, Elpidio 9 Rizal, Jose 10 Roxas, Manuel Average:

66

98

88

95

21

97

88

90

22

98

88

98

34

99

88

99

55

99

88

92

78 99

99 99

86 97

96 98

Score

Comment

Condition

2. Compute the SCORE for each of the 10 employees. 3. Compute the AVERAGE grade per criteria and the AVERAGE of the SCORE. 4. Rename sheet 1 as “SAN BEDA COMPUTER”. 5. Copy the table of the Summary of Results to sheet 2, then sort it from HIGHEST TO LOWEST using FINAL RATING as basis. Name sheet 2 as COMPU2. 6. Fill up the COMMENT COLUMN using the table below:

SCORES COMMENT 0 RE-DESIGN! 80 DISMAL 85 SO-SO 90 TOLERABLE 93 OK 96 GOOD 98 EXCELLENT 7. Use VLOOKUP statement for no. 6 instruction. 8. Fill up the CONDITION COLUMN, whether the final rating is “BACK TO THE DRAWING BOARDS” or “OK FOR SALE”. Use if statement. Final Ratings that will reach 96 or higher are considered OK FOR SALE, while those final ratings which are lower than 96 shall be considered BACK TO THE DRAWING BOARDS. 9. Save with filename “MS EXCEL ACTIVITY 8”.

Activity 9: Statistical Function Microsoft Excel 2007 Objective/s: This exercise will familiarize the students on the use of statistical functions in Excel. 1. Open a new worksheet. 2. Encode the given Salaries Expenses of the different departments:

1 2 3 4 5 6 7 8 9 10 11

A Department Sales Operations Admin Purchasing Accounting Human Resources Security Marketing Audit Systems

B Total Salaries 1205555 1800200 500000 300000 750000 300000 150000 465000 230000 155000

12 13

IT Planning

650000 352000

3. Compute for the corresponding values: A 14

Mean

15

Median

16

Mode

17

Standard Dev.

18

3rd Quartile

19

20th Percentile

20

60th Percentile

21

44th Percentile

4. Set the font size into Century Gothic, 11, Regular. 5. Save with filename “MS EXCEL ACTIVITY 9”.

B (ans)

# 1 2 3 4 5 6 7 8

Transaction Date 1/12/2007 2/25/2007 3/14/2007 12/19/2007 07/07/2007 10/29/2007 01/15/2007 12/29/2007

9 10

03/30/2007 03/06/2007

Sales Agent

Amount

City

Emmanuel Nelson Carina Cris Thommy Albert Mau Mark Christian Lorens Malou

5500 9750 95600 15400 7800 6500 6000 2500

Pasay Caloocan Malabon Quezon City Baguio Davao Cebu Valenzuela

4000 32000

San Jose Meycauayan

Activity 10: Pivot Table Microsoft Excel 2007 Objective/s: To present data in a more presentable and intelligent manner, work with the table using its flexible features. 1. Open a new workbook. 2. Type the following to the corresponding cells:

1 2 3 4 5 6 7 8

A B First Semester Sales Report Employee Aguinaldo, Emilio Bonifacio, Andres Del Pilar, Gregorio Luna, Antonio Mabini, Apolinario

C

D

January February March 300000 150000 350000 120000

285000

180000

350000

452100

232100

63000

252130

162130

25440

562210

75000

9

4 5 6 7 8 9

Magsaysay, 1500000 Ramon

150000

300000

E F G April May June 50000 41000 326000 9000 1522200 452000 160000 362200 63000 125000 52000 45222 45222 40000 451000 36222 150000 362000

3. Select a cell range from A3 to G9. 4. Create a Pivot Table out of this range on the same worksheet. 5. Select the table format. 6. Place Employee to the Row Fields. 7. Place the months to the Data Item and to Page Fields. 8. Apply other functions for table summary. 9. Save the workbook as “MS EXCEL ACTIVITY 10”.

Activity 11: Chart Microsoft Excel 2007 Objective/s: To present data in a graphical form through a chart. This will also enable the students to modify the chart to one’s preference. 1. Open a new workbook. 2. Type the following to the corresponding cells.

1 2 3 4 5 6 7

A B First Semester Sales Report Employee Aguinaldo, Emilio Bonifacio, Andres Del Pilar, Gregorio Luna,

C

D

January February March 300000 150000 350000 120000

285000

180000

350000

452100

232100

63000

252130

162130

8 9

4 5 6 7 8 9

Antonio Mabini, 25440 Apolinario Magsaysay, 1500000 Ramon

562210

75000

150000

300000

E F G April May June 50000 41000 326000 9000 1522200 452000 160000 362200 63000 125000 52000 45222 45222 40000 451000 36222 150000 362000

3. Create a column chart out of this data as an object of the worksheet. 4. Make the columns as the chart data range series. 5. Display data table. 6. Change chart type to 3D column. 7. Save this workbook as “MS EXCEL ACTIVITY 11”....