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	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.