EXCELCBT

MS Excel CBT Examination

Time Left: 30:00

Theory Questions

1. Explain absolute cell reference in Excel.

Absolute reference uses $ sign (e.g., $A$1) and does not change when copied.

2. Explain Pivot Table.

Pivot Table summarizes large data into totals, averages, and grouped analysis.

3. Explain VLOOKUP.

VLOOKUP searches vertically in a table and returns matching value.

4. Define Gross and Net Salary.

Gross Salary is total earnings before deductions. Net Salary is take-home pay after deductions.

5. Explain Goal Seek.

Goal Seek finds input value needed to achieve a specific result.

EXCELPART5

MS Excel Professional Applications

SECTION 1: Engineering Calculations in Excel

Example 1: Area of a Rectangle

Length = 25m (A1)
Width = 12m (A2)

Step 1: Area Formula = Length × Width
Step 2: 25 × 12 = 300 m²

Excel Formula:
=A1*A2

Result: 300 m²

Example 2: Beam Load Calculation

Load (W) = 5000N
Length (L) = 6m

Bending Moment Formula:
M = W × L / 4

Step 1: 5000 × 6 = 30000
Step 2: 30000 ÷ 4 = 7500 Nm

Excel Formula:
=(A1*A2)/4

Result: 7500 Nm

Example 3: Ohm’s Law

Voltage (V) = 240V
Resistance (R) = 60Ω

Formula:
I = V / R

Step 1: 240 ÷ 60 = 4 Amps

Excel Formula:
=A1/A2

Result: 4A

SECTION 2: Accounting Applications

Example 1: Profit Calculation

Revenue = 150,000 (B1)
Expenses = 95,000 (B2)

Profit Formula:
Profit = Revenue – Expenses

Step 1: 150,000 – 95,000 = 55,000

Excel Formula:
=B1-B2

Result: 55,000

Example 2: VAT Calculation (7.5%)

Amount = 200,000
VAT Rate = 7.5%

Step 1: Convert 7.5% to decimal → 0.075
Step 2: 200,000 × 0.075 = 15,000

Excel Formula:
=A1*7.5%

VAT = 15,000
Total Amount = 200,000 + 15,000 = 215,000

Example 3: Discount Calculation

Selling Price = 50,000
Discount = 10%

Step 1: 50,000 × 10% = 5,000
Step 2: 50,000 – 5,000 = 45,000

Excel Formula:
=A1*10%
=A1-B1

SECTION 3: Payroll System (Step-by-Step)

Employee Payroll Example

Basic Salary = 120,000
Housing Allowance = 30,000
Transport Allowance = 20,000

Step 1: Gross Salary = Basic + Housing + Transport
120,000 + 30,000 + 20,000 = 170,000

Excel Formula:
=SUM(A1:A3)

Gross Salary = 170,000

Tax Deduction (PAYE 10%)

Gross Salary = 170,000
Tax Rate = 10%

Step 1: 170,000 × 10% = 17,000

Excel Formula:
=A4*10%

Tax = 17,000

Net Salary Calculation

Gross Salary = 170,000
Tax = 17,000
Pension = 8% (13,600)

Step 1: Total Deduction = 17,000 + 13,600 = 30,600
Step 2: Net Salary = 170,000 – 30,600 = 139,400

Excel Formula:
=A4-(A5+A6)

Final Net Salary = 139,400

Summary

Excel can be used professionally in:

• Structural & Electrical Engineering
• Business & Accounting
• Payroll Management
• Financial Analysis

EXCELPART4

MS Excel Lecture Part 4

Loan Calculation Example

Loan Amount = 100,000
Interest Rate = 10%

Step 1: Interest = 100,000 × 10%
Step 2: 100,000 × 0.10 = 10,000
Step 3: Total Repayment = 100,000 + 10,000 = 110,000

Excel Formula:
=A1*A2
=A1+A3

Goal Seek Example

If you want profit of 50,000:
Step 1: Click Data → What-if Analysis → Goal Seek
Step 2: Set cell (Profit Cell)
Step 3: To value = 50000
Step 4: Change Sales Cell

Excel adjusts automatically.

Pivot Table Example

Sales Data:
John – 2000
Mary – 3000
John – 1500

Pivot Result:
John = 3500
Mary = 3000

EXCELPART3

MS Excel Lecture Part 3

Sorting Example

Scores: 45, 70, 30, 90

Step 1: Highlight cells
Step 2: Click Data → Sort Smallest to Largest

Result: 30, 45, 70, 90

Conditional Formatting Example

Goal: Highlight scores below 50

Step 1: Select score column
Step 2: Click Conditional Formatting
Step 3: Choose Highlight Cell Rules → Less Than → 50

All values below 50 will turn red.

Creating a Chart

Step 1: Select sales data
Step 2: Click Insert
Step 3: Choose Column Chart

Excel automatically generates a visual graph.

EXCEL PART2

MS Excel Lecture Part 2

AVERAGE Function

Step 1: Enter 50 in A1
Step 2: Enter 60 in A2
Step 3: Enter 70 in A3
Step 4: In A4 type =AVERAGE(A1:A3)
Step 5: Press Enter

Manual Calculation:
(50 + 60 + 70) ÷ 3 = 180 ÷ 3 = 60
Excel Result: 60

IF Function

Condition: Pass mark is 50

Step 1: Enter 45 in B1
Step 2: In B2 type =IF(B1>=50,"Pass","Fail")
Step 3: Press Enter

Since 45 is less than 50 → Result: Fail

Percentage Calculation

Step 1: Total Marks = 500 (C1)
Step 2: Student Score = 350 (C2)
Step 3: In C3 type =(C2/C1)*100
Step 4: Press Enter

Manual:
350 ÷ 500 = 0.7
0.7 × 100 = 70%
Excel Result: 70%

EXCEL PART1

MS Excel Lecture Part 1

Introduction to Excel

Microsoft Excel is used for calculations, data storage, and analysis.

Basic Arithmetic in Excel (Step-by-Step)

Example 1: Addition

Step 1: Enter 25 in cell A1
Step 2: Enter 15 in cell A2
Step 3: Click cell A3
Step 4: Type =A1+A2
Step 5: Press Enter

Result: 40

Example 2: Multiplication

Step 1: Enter 12 in B1
Step 2: Enter 5 in B2
Step 3: In B3 type =B1*B2
Step 4: Press Enter

Result: 60

Using SUM Function

Step 1: Enter 10 in C1
Step 2: Enter 20 in C2
Step 3: Enter 30 in C3
Step 4: In C4 type =SUM(C1:C3)
Step 5: Press Enter

Manual Check: 10 + 20 + 30 = 60
Excel Result: 60