Course (MBA PCM): 25VAC103 - Basics of Excel for Data Analysis
Course (MTech CM): 25VAC102 - Basics of Excel for Data Analysis
Time: 2 Hours
Total Marks: 50
Dataset Provided: Construction Project Master Table + Entry Form Template (Attached). There are
data of 20 construction projects given in Master Data sheet
Solution Sheet: It is mandatory to solve any two questions from each section. You can solve all the
mandatory questions right below the data table in Master Data sheet
-----------------------------------------------------------------------------------------------------------------------------------
SECTION A – DATA ENTRY & CLEANING (15 marks)
Q1. Correct Data Entry (5 marks)
Using the Entry Form sheet provided:
a) Enter one new activity record for any project of your choice using proper and appropriate data
validation rules as per your judgement.
Your entry must include:
Project Name
Contractor
Activity Name
Start Date & End Date
Planned Cost & Actual Cost
Material Used, Quantity, Rate
Engineer Name
Remarks
b) Ensure start date < end date, cost values positive, and text length rules are followed.
Q2. Text to Columns (5 marks)
Pick any 5 Engineer Names from the Master Dataset.
Use Text to Columns to split them into:
First Name
Last Name
Format both columns properly.
Store your collection of 5 Engineer Names and their split output right below the data table in Master
Data sheet with proper question number mentioned
Q3. Number & Date Formatting (5 marks)
For any 10 rows of your choice in the Master Dataset:
Format costs in Indian Rupee with comma separators
Format dates in DD-MMM-YYYY format
Round Material Rate to 2 decimals
SECTION B – FORMULAS & CALCULATIONS (20 marks)
Q4. Basic Arithmetic (5 marks)
Select any activity from the dataset and calculate:
a) Activity Duration (days)
b) Material Cost = Quantity × Rate
c) Cost Variance = Actual Cost – Planned Cost
Explain in one line where these values are used in real construction cost control.
Store your calculation right below the data table in Master Data sheet only with proper question
number mentioned
Q5. SUM, AVERAGE, COUNT Functions (5 marks)
Choose any one Project Name from the dataset.
Using only the activities belonging to that project, compute:
Total Planned Cost
Total Actual Cost
Average Material Rate
Count of Activities executed
Show formulas clearly. Store your calculation right below the data table in Master Data sheet only with
proper question number mentioned
Q6. SUMIF, COUNTIF, AVERAGEIF (5 marks)
Choose any one Material Type from the dataset (example: Steel, Cement, Sand, etc.).
Using only rows containing that material, compute:
Total Quantity used
Number of Activities using that material
Average Material Rate
Store your calculation right below the data table in Master Data sheet only with proper question
number mentioned
Q7. Logical/Lookup Scenario (5 marks)
Choose any Contractor from the dataset.
Calculate:
a) Total number of activities the contractor is executing
b) Total Actual Cost under that contractor
c) Average Duration of activities under that contractor
Store your calculation right below the data table in Master Data sheet only with proper question
number mentioned
SECTION C – ADVANCED TOOLS (15 marks)
Q8. Data Validation (5 marks)
Design a New Entry Form on a fresh sheet that includes the following:
Drop-down for Contractor Name
Date validation for Start/End Dates
Only whole numbers allowed for Cost fields
Limit Engineer Name to max 20 characters
Reject blank entries for Project Name
Q9. Chart Creation (5 marks)
Create any three chart using a dataset subset of your choice:
Options:
Pie Chart – Share of Activity Status (Completed/In Progress/Not Started)
Column Chart – Planned vs Actual Cost comparison for any one project
Bar Chart – Material Quantity by Material Type
Line Chart – Cost trend of consecutive activities in any project
Stacked Column Chart – Material Cost by Project
100% Stacked Column Chart – % Material Distribution by Project
Store your calculation right below the data table in Master Data sheet only with proper question
number mentioned
Q10. Summary Table Creation (5 marks)
Create a 4-row summary table for any project of your choice:
Total Material Cost
Total Duration
Max Material Rate
Min Actual Cost
Use appropriate Excel functions
Store your calculation right below the data table in Master Data sheet only with proper question
number mentioned