Sample Test: Analytical Skills for Product Management

This test simulates the kind of analytical questions you will face in product management interviews, especially for marketplace and operations roles. You will work with two datasets — daily balloon flight data and weekly operator stats — to answer quantitative and qualitative questions. The actual test CSV files are formatted deliberately with quirks to mirror real-world data challenges.

Your actual job is to extract actionable insights from imperfect data and explain your reasoning clearly. This is a core skill for PMs — not just knowing Excel formulas, but understanding what the numbers mean for the business.

The test context: Hot air balloon tours in Cappadocia

The dataset is based on a travel agency organizing hot air balloon tours in Cappadocia, Turkey, in 2014. The agency uses a website for customers to book balloon trips. Each balloon has an operator who registers availability with the agency.

There are two datasets:

• data1.csv: Daily data for 2014 including website visitors, balloons available, reservations, actualized flights, capacity, and balloon color.
• data2.csv: Weekly stats of balloon operators including flight counts, duration, and capacity.

Columns in data1.csv:

The dataset has 365 rows — one per day.

The balloon operators’ dataset (data2.csv) tracks weekly data for each operator: number of flights, flight durations, days worked, balloon capacity, and more.

The questions test your ability to analyze these datasets — using pivot tables, formulas, charts, and logical reasoning — to reveal insights on demand, capacity, cancellations, operator incentives, and pricing.

Preparing your Excel environment for the test

The CSV files may not import cleanly in Excel due to intentional formatting quirks. This simulates real-world messy data and tests your data cleaning skills.

If you face trouble importing or viewing columns correctly, watch this video for guidance:

A key tip: create a new column labeled Month next to the Date column using the formula =TEXT(Date,"mmmm") to help group data by month in pivot tables.

Understanding pivot tables for the test

Pivot tables are your primary tool to summarize and aggregate data efficiently.

For example, to find the average number of reservations per month:

1. Select the entire dataset.
2. Insert a pivot table.
3. Set Row Labels to Month.
4. Set Values to Sum of Reservations.
5. Divide total reservations by 12 months for the average.

Practice this technique as many questions require monthly or weekly aggregation.

Part 1: Daily data analysis questions using data1.csv

Question 1: Average reservations per month

Calculate the average number of balloon ride reservations made per month in 2014.

Use the pivot table steps above. The total reservations sum to 7248, so the average is 7248 / 12 = 604.

Question 2: Average total balloon capacity in last 3 months

Find the average monthly balloon capacity for October, November, and December.

Sum the capacity for these months (6425 total), then divide by 3, resulting in approximately 2142.

Question 3: Most frequent dominant balloon color

Which balloon color was dominant most often during 2014?

Use a pivot table counting days by Color. The dominant color is Red.

Question 4: Highest capacity-to-visitor ratio day

On which date is the ratio of total balloon capacity to website visitors the highest?

Calculate capacity / visitors for key dates. March 31, 2014, has the highest ratio (~0.433).

Question 5: Highest website visitors between Jan-April

Which day had the most website visitors in this period?

Use Excel’s MAX formula over the date range, then search for the date. March 22, 2014, is the peak.

Question 6: Day with most reservations when balloon color is black

Among days with black as the dominant balloon color, which weekday had the highest reservations?

Filter data by color = black and aggregate reservations by day. The answer is Saturday.

Question 7: Period with highest reservation cancellations

Identify the date range where the ratio of actualized flights to reservations is lowest (indicating highest cancellation rates).

Calculate average actualized/reservations over the four given periods. The period January 22 – February 12, 2014, shows the lowest ratio.

Question 8: Best day of week to close business

If closing one day a week, which day has the lowest total actualized flights?

Sum actualizations by weekday. Tuesday has the lowest actualized flights, so it is the best candidate for closure.

Question 9: Maximum balloons available in a month

Which month had the highest total balloons available?

Pivot table sums balloons by month. October had the highest with 199 balloons.

Question 10: Do reservations always increase with more balloons?

Plot balloons available vs reservations. The graph shows cases where more balloons do not lead to more reservations.

Answer: False — reservations do not always increase with balloon availability.

Question 11: Week with lowest balloons available

Among given weeks, which had the least total balloons available?

Sum balloons by week. Week January 20–26, 2014, had the lowest total.

Question 12: Number of rows in data1.csv

True or False: The dataset has 365 rows.

Answer: True, one for each day of 2014.

Question 13: Percentage of visitors in January

Calculate January’s share of total website visitors.

Sum visitors in January (12785) divided by total visitors (146503) gives approximately 8.73%.

Question 14: Reservations on weekends vs weekdays in seasons

Which has the most actualized reservations: weekends in August, weekdays in July, weekdays in June, or weekends in May?

Pivot table with Day filter (weekend/weekday) and Month filter shows weekdays in July have the most.

Question 15: Best day to add 5 balloons in May

If adding 5 balloons to any day of the week in May, which day would maximize reservations relative to capacity?

Analyze reservations vs capacity by day. Friday is best.

Part 2: Operator data analysis questions using data2.csv

Question 16: Promotion alternative 1 cost

If giving operators USD 25 each for balloons with capacity >10, flight duration ≤1 hour, and working ≥4 days/week, what is total payout?

Filter operators meeting criteria, count, multiply by 25.

Answer: 675 USD.

Question 17: Promotion alternative 2 cost

If paying USD 2 per flight for operators with ≥6 flights/day and working ≥6 days/week, what is total payout?

Sum flights for eligible operators multiplied by 2.

Answer: 936 USD.

Question 18: Operators paid in both promotions

How many operators qualify for both promotion alternatives?

Answer: 2 operators.

Question 19: Maximum weekly passengers per operator

Assuming full capacity on every flight, what is the maximum weekly passengers an operator can carry?

Calculate flights × capacity × days.

Answer: 710 passengers.

Question 20: Longest total air time per week

What is the maximum total flight duration an operator can spend in the air per week?

Sum durations × flights × days.

Answer: 3240 minutes.

Question 21: Least relevant metric for propane gas cost

Which metric least affects propane gas consumption estimation?

Options: flights per day, flight duration, days worked, average age of passengers.

Answer: Average age of passengers.

Question 22: Annual net earnings of an operator

Given flight fees, expenses, schedule, and 3 months off, calculate net yearly earnings.

Answer: 36,000 USD.

Question 23: Break-even price per flight for new balloon investment

To recover a 42,000 USD investment in one year, what gross price per flight is needed?

Answer: 302 USD.

Question 24: Maximum balloons eligible for booking system

Given fleet size and percentage filters on height, capacity, and age, calculate eligible balloons.

Answer: 75 balloons.

Question 25: Pricing decision factors for holiday demand surge

Which factor least impacts price hike decisions?

Options: reservations, weather forecast, capacity, average flight duration.

Answer: Average flight duration.

Question 26: Metrics that do not drop in low-demand winter season

Which metric remains stable during winter?

Answer: Ratio of actualizations to reservations.

Question 27: Least important factor for retaining problematic operators

From customer service perspective, which is least important in deciding to keep an operator?

Answer: Total revenue generated.

Question 28: Weekend and holiday operational trends

Which is most likely during these periods?

Answer: Number of flights increase.

Essay questions: Applying analytical thinking in writing

These are practice prompts similar to those in PM interviews. Thoughtful, clear, and concise answers are more important than perfect grammar.

Question 29: Email to motivate underperforming operator

Write an email asking about flight shortfall reasons and encouraging more effort.

Question 30: Address passenger safety concerns

Respond to a passenger worried about balloon safety in 5 sentences or less, reassuring and selling the service.

Question 31: Explain commission increase to operators

Justify raising commissions while reducing flight times, convincing operators to stay rather than switch to competitors.

Question 32: Fostering operator community

Describe how to make balloon operators feel part of a close-knit family in up to 3 paragraphs.

Where to go next

• If you want hands-on practice with Excel pivot tables and formulas: Excel for Product Managers
• If you want to build your data interpretation skills: Data-Driven Decision Making
• If you want to prepare for marketplace case studies: Marketplace Product Management
• If you want to practice product sense and prioritization: Product Sense Interview Prep
• If you want to improve your communication for PM essays: Effective PM Writing

PL alumni now work at Flipkart, Google, Razorpay, PhonePe, Swiggy, Amazon, Microsoft, and 30+ other companies.