Detailed Solutions for Analytical Test Questions

This lesson walks you through detailed solutions for 32 analytical test questions based on the “data1.csv” and “data2.csv” datasets. The actual job is to translate raw data into clear answers using Excel skills — primarily Pivot Tables, formulas, and charts. You will see how to approach each question methodically, saving time and avoiding guesswork.

Mastering these techniques is essential for product managers who must analyze data quickly during interviews or in real work. The pattern is consistent: prepare your data for analysis, use Pivot Tables to aggregate, apply formulas for ratios or percentages, and visualize when needed.

Creating a Month Column is the Foundation for Monthly Aggregations

The first step in many questions is to group daily data by month. The provided “data1.csv” lacks a Month column, which makes summarizing by month impossible without preprocessing.

Add a Month column next to the Date column using either:

• The formula =TEXT(date,"mmmm") which extracts the month name from the date, or
• Manually entering month names next to dates and copying them down.

This enables you to build Pivot Tables with Month as a row label and aggregate values like reservations or capacity.

Question 1 & 2: Calculating Monthly Averages Using Pivot Tables

Question 1: Average number of reservations per month.

• Select all cells in the MASTER sheet.
• Insert a Pivot Table.
• Set Row Labels to Month.
• Set Values to Sum of Balloon Reservations.
• The Grand Total sum is 7,248 reservations for the year.
• Divide 7,248 by 12 months = 604 average reservations per month.

Question 2: Average total balloon capacity per month for the last 3 months.

• Use the same Pivot Table approach with Sum of Total Balloon Capacity.
• Add values for October, November, December: total 6,425.
• Average = 6,425 / 3 = 2,142 (rounded).

These questions show how monthly aggregation is straightforward once the Month column exists.

Question 3: Identifying the Most Frequent Dominant Balloon Color

• Build a Pivot Table with Dominant Balloon Color as Row Labels.
• Use Count of Day as Values to count occurrences.
• The color with the highest count is Red.

This demonstrates counting categorical data frequencies.

Question 4: Finding Maximum Ratio Without a Pivot Table

The ratio of total balloon capacity to website visitors is a per-row calculation.

• Locate the rows for the given dates.
• Add a formula column to calculate capacity / visitors.
• Compare the values; 03/31/2014 has the highest ratio (0.433).

This shows when a simple formula suffices over aggregation.

Question 5: Finding Maximum Website Visitors in a Date Range Using MAX Formula

• Use the formula =MAX(range) over the visitor counts from January to April.
• The maximum value found is 499.
• Search for this value to find the corresponding date: 03/22/2014.

This approach avoids scanning rows manually.

Question 6: Day of Week with Most Reservations for Dominant Black Balloon Color

• Filter the Pivot Table by Dominant Color = Black.
• Set Row Labels to Day of the Week.
• Sum Balloon Reservations as Values.
• Friday has the highest reservations.

Combining filters with aggregation answers detailed queries.

Question 7: Period with Minimum Ratio of Actualizations to Reservations

• Highlight date ranges specified.
• For each range, calculate actualizations / reservations per day.
• Average these ratios over the period.
• Lowest average ratio occurs in 07/19/2014 – 08/21/2014.

This identifies when customers cancel most.

Question 8: Best Day to Close Business Based on Least Actualizations

• Create a Pivot Table with Days as Row Labels.
• Sum Balloon Reservations as Values.
• The day with the least total actualizations is Tuesday.

This informs business decisions on low-impact closures.

Question 9: Maximum Number of Balloons Available in Any Month

• Pivot Table grouped by Month.
• Sum Balloons Available as Values.
• October has the maximum total of 199 balloons.

Monthly supply insights are critical for capacity planning.

Question 10: Reservations Do Not Always Increase With Balloon Availability

• Create a line chart with Reservations and Balloons Available columns.
• Visualize trends over time.
• Identify points where reservations drop despite increasing balloons.
• Conclusion: False, reservations do not always increase with more balloons.

Visual analysis reveals non-linear relationships.

Question 11: Week with Least Balloons Available in 2014

• Highlight each week’s date range.
• Sum Balloons Available using Excel’s status bar or SUM formula.
• The week 01/20/2014 – 01/26/2014 has the lowest total.

Weekly supply fluctuations guide operational planning.

Question 12: Confirming Data Completeness

• Data has 365 rows, one for each day of 2014.
• True.

Simple data sanity checks are important first steps.

Question 13: Percentage of Total Visitors in January

• Pivot Table with Month as Row Labels.
• Sum Visitors as Values.
• Calculate January Visitors / Total Visitors * 100.
• Result: 8.73%.

Percentage metrics contextualize monthly traffic.

Question 14: Comparing Reservations Between Weekends and Weekdays by Season

• Use Pivot Table with Day filter (weekends or weekdays).
• Month as Row Labels.
• Sum Balloon Reservations as Values.
• Weekdays of July have the highest reservations among options.

Seasonal and day-of-week patterns affect demand.

Question 15: Day of Week in May to Add 5 Balloons Based on Reservations vs Capacity

• Filter Pivot Table for May.
• Rows: Days of the Week.
• Values: Sum of Balloon Reservations and Sum of Total Balloon Capacity.
• Compute ratio capacity / reservations next to the Pivot Table.
• Lowest ratio indicates highest need: Saturday.

This analysis supports targeted capacity increases.

Question 16 & 17: Calculating Promotion Costs for Balloon Operators Using Filters

Alternative 1:

• Filter operators with capacity > 10, flight duration ≤ 1 hour, working ≥ 4 days/week.
• Count qualifying operators: 27.
• Total payout = 27 * 25 = 675 USD.

Alternative 2:

• Filter operators with ≥ 6 flights per day, working ≥ 6 days/week.
• Sum total flights: 468.
• Total payout = 468 * 2 = 936 USD.

Pivot Tables combined with filtering help calculate incentive costs.

Question 18: Operators Eligible for Both Promotions

• List operators satisfying Alternative 1 and Alternative 2 criteria.
• Count intersection: 3 operators.

Cross-analysis identifies overlap in eligibility.

Question 19: Maximum Weekly Passengers Per Operator

• Add a calculated column: (Days working per week) * (Balloon Capacity) * (Flights per day).
• Use MAX formula on this column.
• Maximum is 630 passengers.

Capacity planning requires such multiplicative metrics.

Question 20: Longest Total Time Spent in Air Per Week by an Operator

• Add a calculated column: (Days working per week) * (Flights per day) * (Duration per flight).
• MAX value found: 3,150 minutes.

Time-on-air impacts costs and scheduling.

Question 21: Least Relevant Metric for Estimating Propane Gas Usage

• Number of flights, duration, and days working affect propane use.
• Average age of passengers does not.
• Answer: Average age of passengers.

Understanding cost drivers improves cost management.

Question 22: Calculating Net Annual Earnings of an Operator

• Weekly trips: (2 flights * 5 weekdays) + (4 flights * 2 weekend days) = 18 trips/week.
• Working weeks: 52 - 12 (winter off) = 40 weeks.
• Total trips/year = 40 * 18 = 720.
• Net per flight = 300 USD - 250 USD = 50 USD.
• Annual net = 720 * 50 = 36,000 USD.

Annualizing weekly metrics provides financial insight.

Question 23: Required Gross Earnings to Recover Investment in One Year

• Investment: 42,000 USD.
• Additional net per flight needed = 42,000 / 720 ≈ 58.33 USD.
• Current cost per flight: 200 USD.
• Required gross = 200 + 58.33 = 258 USD.

ROI calculations guide pricing decisions.

Question 24: Maximum Balloons Eligible for Online Booking System

• Total balloons: 200.
• 75% meet height requirement → 150.
• Of these, 50% meet capacity → 75.
• Age filter ignored as not a requirement.
• Maximum eligible balloons: 75.

Applying sequential filters narrows inventory.

Question 25: Metric Least Impacting Price Increase Decision

• Number of reservations, weather forecast, and capacity affect pricing decisions.
• Average flight duration does not.
• Answer: Average flight duration.

Identifying irrelevant factors clarifies pricing strategy.

Question 26: Metric Not Expected to Drop During Low Demand Winter Season

• Completed flights, weekly earnings, and website visitors drop.
• Ratio of actualizations to reservations remains stable.
• Answer: Ratio of actualizations to reservations.

Ratio metrics often show stability despite volume changes.

Question 27: Least Important Factor When Deciding to Keep a Complaint-Prone Operator

• Important: Complaint details, flights completed, days worked, passengers served.
• Least important: Total revenue generated.
• Answer: Total revenue.

Customer satisfaction outweighs revenue in quality decisions.

Question 28: Expected Weekend and Holiday Trends

• Reservations increase on weekends and holidays.
• Number of flights increases accordingly.
• Customer satisfaction may vary.
• Answer: Number of flights increase.

Demand spikes affect operational metrics.

Question 29: Writing a Supportive Email to a Low-Performing Operator

The email should:

• Avoid judgmental language.
• Express support and team spirit.
• List possible reasons for low performance.
• Offer solutions and open communication.
• Encourage sharing issues confidentially.

Example excerpt:

Dear [Operator],
We have noticed a decrease in your flight numbers and want to support you in any challenges you might be facing... Please feel free to reach out so we can assist you in getting back on track.

This approach builds trust and motivates improvement.

Question 30: Addressing Passenger Safety Concerns Concisely

• Acknowledge the concern respectfully.
• Provide facts about safety records and certifications.
• Share customer satisfaction statistics.
• Offer resources for more information.
• End with a positive, reassuring note.

Example:

Hello [Passenger],
Our service is certified by the Balloon Safety Commission and maintains an impeccable safety record... 98% of our customers rate their experience highly. We look forward to providing you a memorable and safe flight.

Clear communication eases fears effectively.

Question 31: Explaining Increased Commission with Reduced Flight Times to Operators

• Present numbers showing net earnings remain stable or increase.
• Highlight cost savings on propane due to shorter flights.
• Emphasize other benefits like support, incentives, or scheduling flexibility.
• Avoid negative remarks about competitors.
• Suggest practical tips for maximizing earnings during peak times.

This balances transparency and motivation.

Question 32: Making Operators Feel Part of a Big Family

• Emphasize constant communication and personal connection.
• Recognize small gestures build loyalty.
• Commit to maximizing earnings and openly sharing efforts.
• Foster a culture of inclusion and support.

Example:

We prioritize staying connected with our operators through regular check-ins and support... This creates a family-like environment that encourages dedication and satisfaction.

Building culture is as important as operations.

Test yourself: Practice these data analysis techniques on the provided datasets.

Where to go next

• If you want to strengthen your core Excel and data analysis skills: Excel for Product Managers
• If you want to practice interpreting data to make product decisions: Data-Driven Product Management
• If you want to prepare for marketplace case studies in interviews: Marketplace Product Strategy
• If you want to improve your stakeholder communication: Effective Product Communication
• If you want to learn how to write clear business emails: Business Writing for PMs