EXCEL CBT2

MS Excel Oil Company CBT - 30 Questions

Time Left: 20:00

EXCEL TASK

MS Excel Oil Company Tasks

Below are 10 tasks to practice preparing Excel reports for an oil company. Each task includes a table to be filled or calculated.

Task 1: Monthly Oil Production

Fill in the revenue column using =OilSold*PricePerBarrel.

Month	Oil Produced (Barrels)	Oil Sold (Barrels)	Price per Barrel ($)	Revenue ($)
Jan	5000	4800	60
Feb	5200	5100	62
Mar	4800	4700	61

Task 2: Worker Hours

Calculate total hours using =DaysWorked*HoursPerDay.

Worker	Days Worked	Hours per Day	Total Hours
John	20	8
Mary	22	7.5
Ali	18	8

Task 3: Production Efficiency

Calculate efficiency (%) using =OilSold/OilProduced*100.

Month	Oil Produced	Oil Sold	Efficiency (%)
Jan	5000	4800
Feb	5200	5100
Mar	4800	4700

Task 4: Total Production and Revenue

Calculate totals using =SUM(...).

Total Production	Total Revenue

Task 5: Average Price per Barrel

Use =AVERAGE(PriceColumn).

Month	Price per Barrel ($)
Jan	60
Feb	62
Mar	61

Average Price:

Task 6: Profit Analysis

Use =IF(Revenue>50000,"Profitable","Not Profitable").

Month	Revenue	Profit Status
Jan
Feb
Mar

Task 7: Conditional Formatting

Highlight efficiency < 97% using Conditional Formatting.

Apply to Task 3 Efficiency Column.

Task 8: Efficiency per Worker Hour

Calculate using =TotalProduction/TotalWorkerHours.

EXCEL LECTURE TODAY

MS Excel Tutorial: Preparing Oil Company Reports

In this lecture, we will demonstrate how to prepare a detailed report for an oil company using Microsoft Excel, including formulas, calculations, tables, and examples.

Step 1: Collect Data

Example dataset for oil production and sales:

Month	Total Production	Total Worker Hours	Efficiency/Hour
Jan	5000
Feb	5200
Mar	4800

Month	Oil Produced (Barrels)	Oil Sold (Barrels)	Price per Barrel ($)
Jan	5000	4800	60
Feb	5200	5100	62
Mar	4800	4700	61

Step 2: Worker Report

Track the number of workers, days worked, and total hours.

Worker	Days Worked	Hours per Day	Total Hours
John	20	8	=B2*C2 → 160
Mary	22	7.5	=B3*C3 → 165
Ali	18	8	=B4*C4 → 144

Formula for Total Hours: =DaysWorked*HoursPerDay

Step 3: Total Revenue Calculation

Formula: =Oil Sold * Price per Barrel

January: =4800*60 → 288,000

Month	Revenue ($)
Jan	=C2*D2 → 288,000
Feb	=C3*D3 → 316,200
Mar	=C4*D4 → 286,700

Step 4: Production Efficiency (%)

Formula: =Oil Sold / Oil Produced * 100

January Efficiency: =4800/5000*100 → 96%

Step 5: Total Production & Revenue Using SUM

Formula: =SUM(B2:B4) and =SUM(E2:E4)

Total Production: 15,000 barrels

Total Revenue: $890,900

Step 6: Average Price per Barrel

Formula: =AVERAGE(D2:D4) → 61 $/Barrel

Step 7: Using IF Formula for Profit Margin Analysis

Assume cost per barrel = $40. Formula: =IF(E2>50000,"Profitable","Not Profitable")

For January: Profitable

Step 8: Conditional Formatting

Highlight months where efficiency < 97%:

• Select Efficiency column → Home → Conditional Formatting → Highlight Cell Rules → Less Than → 97

Step 9: Charts & Visual Representation

Create a column chart for monthly revenue:

• Select Months and Revenue columns
• Insert → Charts → Column Chart
• Customize chart title: Monthly Revenue for Oil Company

Step 10: Advanced Formula Examples

• VLOOKUP to fetch price by month: =VLOOKUP("Feb",A2:D4,4,FALSE) → Returns 62
• MAX formula for highest revenue: =MAX(E2:E4) → 316,200
• MIN formula for lowest efficiency: =MIN(F2:F4) → 96%

Step 11: Merging Worker Report with Production Report

We can link worker hours to production efficiency:

Month	Total Production	Total Worker Hours	Efficiency per Worker Hour
Jan	5000	469	=B2/C2 → 10.66 barrels/hour
Feb	5200	469	=B3/C3 → 11.09 barrels/hour
Mar	4800	469	=B4/C4 → 10.24 barrels/hour

Conclusion

This lecture shows how to prepare a comprehensive oil company report in Excel, including production, revenue, worker hours, efficiency, charts, and formulas. This is useful for management decisions, payroll, and productivity analysis.

PHYSICS CBT TODAY

Time Left: 1800 sec

CHEMISTRY CBT TODAY

Time Left: 1800 sec

MATHEMATICS CBT TODAY

Time Left: 1800 sec

CRS CBT TODAY

Time Left: 1800 sec