practical sheet 4a_4b

COM1012

DOCUMENT NUMBER COM1012/PRAC4

REVISION 4

EFFECTIVE DATE 20 SEP 2012

PAGE

of 9

INTERNATIONAL COLLEGE OF AUTOMOTIVE

KOMPLEKS AUTOMOTIF DRB – HICOM PEKAN,

KAWASAN PERINDUSTRIAN PERAMU JAYA,

KARUNG BERKUNCI NO.8

26607 PEKAN, PAHANG DARUL MAKMUR

PRACTICAL SHEET 4

PROGRAMMES

DIPLOMA IN VEHICLE INSPECTION (DVI)

DIPLOMA IN VEHICLE ASSEMBLY MANAGEMENT (DVA)

DIPLOMA IN AUTOMOTIVE MANAGEMENT SYSTEMS (DMS)

DIPLOMA IN AUTOMOTIVE SYSTEM TECHNOLOGY (DST)

DIPLOMA IN AUTOMOTIVE PARTS MANAGEMENT (DPM)

DIPLOMA IN AUTOMOTIVE RETAIL MANAGEMENT (DRM)

DIPLOMA IN PROCUREMENT VENDOR MANAGEMENT (DPV)

DIPLOMA IN ENTERPRISE RESOURCE PLANNING (DRP)

MODULE NAME / CODE COMPUTER APPLICATION / COM1012

SEMESTER / YEAR 1 OR 2 / 1

TOPIC NUMBER / TITLE 7 / SPREADSHEET (MICROSOFT EXCEL 2010)

PREPARED BY

CHECKED BY

NUR HANIS KAMALUDDIN

ELLYZIANA ABU BAKAR

COM1012


REVISION 4


PAGE

of 9

STUDENT’S DETAILS

NAME :

ID NO :

PROGRAMME : DVA / DMS / DVI / DST / DRM / DPM / DPV / DRP

SEMESTER : 1 / 2 GROUP : A / B / C / D

LECTURER’S

NAME

MDM ELLYZIANA BINTI ABU BAKAR

MR CHAROMIE A/L TAT WI

: MISS NURUL AINI BINTI OTHMAN

MDM RAJA SITI NURHIDAYAH BINTI RAJA IDRIS

MISS NUR HANIS BINTI KAMALUDDIN

COMPLETED

DATE FOR

PRACTICALS’

PARTS

:

4A 4B

STUDENT’S

SIGNATURE :

COM1012


REVISION 4


PAGE

of 9

ASSESSMENT MARKS

PRACTICAL 4A: SPREADSHEET (EXCEL 2010)

GRADING CRITERIA TOTAL MARKS MARKS ACHIEVED

COMPLETION

ITEMS:

- Able to build and manipulate data in a

spreadsheet 20

- Able to apply worksheet formatting 16

- Able to use Move or Copy option and

rename a worksheet 4

ATTITUDE:

- Responsible 5

- Punctuality 5

- Job Finish on Time 5

- Listen to Instruction Well 5

TOTAL / 60

PRACTICAL 4B: SPREADSHEET (EXCEL 2010)

GRADING CRITERIA TOTAL MARKS MARKS ACHIEVED

COMPLETION

ITEMS:

- Able to create simple formula and use

drag&drop/copy&paste method 12

- Able to perform total sum function 24

- Able to perform and calculate functions 10

- Able to create and modify charts 4

ATTITUDE:

- Responsible 5

- Punctuality 5

- Job Finish on Time 5

- Listen to Instruction Well 5

TOTAL / 70

COM1012


REVISION 4


PAGE

of 9

TABLE OF CONTENT PAGE

1. FRONT PAGE 1

2. STUDENT’S DETAILS 2

3. ASSESSMENT MARKS 3

4. TABLE OF CONTENT 4

5. PRACTICAL 4A 5

6. PRACTICAL 4B 8

COM1012


REVISION 4


PAGE

of 9

PRACTICAL 4A: WEEK 11: TOPIC 7: SPREADSHEET (EXCEL 2010)

MODULE OUTCOME 2: To apply the suitable office application to fulfill given task. PRACTICAL OBJECTIVES:

At the end of the practical session, student should be able to:

Build spreadsheet.

Manipulate data in spreadsheet.

Apply worksheet formatting.

PRACTICAL INSTRUCTIONS: TASK 1: (40 MARKS)

You have to build a spreadsheet of Sales Forecast by monthly basis. Complete this task by working in pair. 1. Build the following table in MS Excel 2010.

COM1012


REVISION 4


PAGE

of 9

Rename each column of Food 1, Food 2, Food 3 and Food 4 with the name of your

served foods. Adjust the column width and height accordingly. For the table’s title:

Sales Forecast, use Merge and Center format. Add border accordingly to the table

and for the totals (Total Unit Sales and Total Sales), use top and double bottom

border. Apply Bold to the table’s title, months, unit sales, total unit sales, unit prices,

sales and total sales. Then, change their font size to 12 pt with font style Arial. Set

the rest fonts with Arial and adjust the font size to 11 pt.

2. The data for the month of January has been filled in for your reference. Complete the

table below by inserting a rational forecast for the rest of the months.

Use currency option, insert row function and use font size 10 pt for the data.

3. At Sheet1 tab, change the name to “Sales Forecast”. Copy Sales Forecast using

Move or Copy option and locate it before Sheet2. Color the Sales Forecast tab

“Green” and rename Sales Forecast (2) tab to “Sales Forecast 2012”. Color the tab

“Red”.

COM1012


REVISION 4


PAGE

of 9

EXPECTED COMPLETION TIME:

120 minutes in computer laboratory.

REFERENCE:

Practical Sheet

Lecture Notes

Reference Book

COM1012


REVISION 4


PAGE

of 9

PRACTICAL 4B: WEEK 12: TOPIC 7: SPREADSHEET (EXCEL 2010)

MODULE OUTCOME 2: To apply the suitable office application to fulfill given task. PRACTICAL OBJECTIVES:

At the end of the practical session, student should be able to:

Manipulate data using formula and function in spreadsheet.

Insert charts in spreadsheet.

PRACTICAL INSTRUCTIONS: TASK 1: (50 MARKS)

By working in pair, you are required to completely set your account of Sales Forecast for year 2012. 1. Open your previous spreadsheet (Sales Forecast 2012). At the Sales section,

calculate each sale for each item (food and beverages) by multiplying unit sales and

unit price (price per unit) accordingly. Use drag and drop function or copy and paste

method to duplicate the formula.

2. Use Total Sum function to calculate the Total Unit Sales and Total Sales. Then,

calculate the Grand Total for both total unit sales and total sales. Sum up each

amount of total unit sales and total sales from January to December in order to get a

Grand Total Unit Sales and a Grand Total Sales.

3. Calculate the average sales for each month. Find the minimum average sales and

colour the data RED using Conditional Formatting. Find the maximum average sales

and colour the data GREEN using Conditional Formatting.

4. Now, represent your forecast graphically by constructing a line graph. Choose Chart

Layout 1. Below is the example of line graph chart.

COM1012


REVISION 4


PAGE

of 9

This illustration is example only. Construct your own chart with complete data.

EXPECTED COMPLETION TIME:

120 minutes in computer laboratory.

REFERENCE:

Practical Sheet

Lecture Notes

Reference Book

RM0.00

RM1,000.00

RM2,000.00

RM3,000.00

RM4,000.00

RM5,000.00

RM6,000.00

RM7,000.00

Axi

s Ti

tle

Total Sales Forecast

Other

Beverages

Food 4

Food 3

Food 2

Food 1

practical sheet 4a_4b

Documents