SCA_Chapter3_End

Which Excel feature is best to quickly summarize total shipments by route and month from a large transactional table?

• Subtotal function
• Advanced Filter
• Flash Fill
• PivotTable
• Goal Seek

Answer explanation

PivotTables allow fast multi-dimensional aggregation (e.g., Route vs Month) without altering source data.

Which function pair replaces VLOOKUP to efficiently retrieve carrier cost when the column order may change?

• XLOOKUP and MATCH
• OFFSET and MATCH
• INDEX and MATCH
• INDIRECT and MATCH
• CHOOSECOLS and HLOOKUP

Answer explanation

INDEX+MATCH decouples lookup and return columns, unaffected by column reordering.

In analyzing on-time delivery, which function best counts orders delivered within promised lead time stored in a separate column, e.g., DeliveredDate <= PromiseDate and Status = Delivered?

• COUNTIF
• SUMPRODUCT
• COUNT
• FILTER
• COUNTIFS

Answer explanation

COUNTIFS allows multiple criteria, e.g., DeliveredDate <= PromiseDate and Status = Delivered.

To calculate total freight cost per kg where both cost and weight may contain blanks, which function avoids errors from division by zero?

• AVERAGE
• SUMIF
• IFERROR(TotalCost/TotalWeight,0)
• SUMPRODUCT(SUM/COUNT)
• ROUND

Answer explanation

IFERROR traps #DIV/0! when weight is zero or blank, returning a safe fallback.

Which visualization is most suitable to show proportion of total shipments by transport mode (Air, Sea, Road) for a single month?

• Stacked Line Chart
• Pareto Chart
• Scatter Plot
• Histogram
• 100% Stacked Column

Answer explanation

100% stacked column emphasizes share-of-total across categories for one period.

To highlight late orders automatically (RequiredDate < ShipDate), which feature is simplest?

• Conditional Formatting
• Data Validation
• Custom Number Format
• Power Pivot
• DATA TABLE

Answer explanation

Conditional Formatting applies dynamic cell formats based on logical rules like lateness.

Which function calculates business days between planned ship and actual delivery excluding weekends?

• WORKDAY
• WORKDAY.INTL
• EOMONTH
• NETWORKDAYS
• DAYS

Answer explanation

NETWORKDAYS(start,end) returns count of working days excluding weekends (optionally holidays).

What Excel feature lets you slice a PivotTable of warehouse picks by Picker Name for interactive dashboarding?

• Filter Drop-down
• Timeline
• Slicer
• Data Validation List
• Watch Window

Answer explanation

Slicers provide button-based filtering linked to PivotTables for better usability.

Which chart best shows inventory turnover trend over 24 months with emphasis on seasonality?

• Clustered Column
• Area Chart
• Radar Chart
• Line Chart
• Treemap

Answer explanation

Line charts emphasize temporal trends and seasonality over extended periods.

To find the 5 fastest average transit time routes from a table, which approach is most efficient?

• SORT range manually
• PivotTable then sort ascending on Avg Transit
• AutoFilter then copy
• SUMIFS per route manually
• Goal Seek

Answer explanation

PivotTable aggregates and allows instant sorting to identify quickest routes.