Location Analytics in Excel: Distances, Catchments & Territories

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Calculate the great-circle distance from Maple & Rye's central kitchen to its five bakery-cafés with RADIANS, SIN, COS, ASIN and SQRT.
• Build an 8 × 5 distance matrix from customer towns to Maple & Rye stores with one formula and mixed references, then summarise each store's reach.
• Find the nearest Maple & Rye store for every customer town, count the households each store serves and chart the result.
• Size the Alder Falls store's primary, secondary and tertiary catchment with COUNTIFS and SUMIFS on distance and estimate its annual sales.
• Turn straight-line miles from the Mill Creek kitchen into estimated drive minutes with a circuity factor and road speeds, and count the stops reachable within 25 minutes.
• Calculate the yearly workload of Maple & Rye's three wholesale reps, measure how unbalanced the territories are and test a proposed move of one border account.
• Run a Huff model for six towns and four bakery-cafés (two Maple & Rye, two competitors) to estimate each store's expected sales and Maple & Rye's market share.
• Score five candidate sites for Maple & Rye's sixth café on four criteria, combine them with weights and plot candidates and existing stores on a longitude/latitude scatter map.