Data Analytics with Excel: For SaaS & Software Companies

Learn to transform raw bookings data into quarterly trend charts by product and geography, using pivot tables and linked summary tables, with data preparation and chart-building foundations.

Explore a raw data set resembling real-world SaaS transactions, including transaction id, start and end dates, product family, location, bookings amount (TCV), and account owner, drawn from Salesforce CRM data.

Create pivot tables in Excel to summarize bookings by product family and by region on a quarterly basis, including data range selection, quarter grouping, and view duplication.

Build dynamic summary tables linked to pivot tables, add trend metrics, and format outputs for executive presentations; compute quarter-over-quarter and year-over-year changes and regional/product mix.

Build stacked column charts to show bookings by product, family, and region, using a charts template linked to data tables and pivot data for automatic refreshing.

Learn to transfer charts from Excel to Google Slides, adjust sizing and formatting, and present key observations like year-over-year bookings, product mix shifts from premium to standard, and regional performance.

Explore annual recurring revenue concepts by converting bookings into air, and analyze drivers: new air, upsell, cross-sell expansions, and churn, to quantify the value of active contracts.

Stage bookings data for air analysis, creating a quarterly customer view with four columns—start month, end month, number of days, and air—using end-of-month standardization.

Build a unique customer list from your data by duplicating and refining a pivot table, extracting customer names and bookings amount, then sort customers by bookings value in descending order.

Use sumifs in excel to build an ARR by customer summary, summing values from column m where the customer matches and the period falls between start and end dates.

Learn to decompose ARR changes into four exclusive drivers—new, expansion, down sell, and churn—by comparing prior and current periods and applying Excel formulas.

Build an ARR summary table across all customers from a starting period. Compute new, expansion, down sell, and churn and verify with a zero check that the ending ARR aligns.

Learn to build a high-level ARR trend summary in Excel, compute quarter-over-quarter and percentage changes, and compare new ARR versus land‑and‑expand contributions over time.

Learn to build ARR charts in Excel, including a year-over-year trend chart and a components chart showing new, expand down, sell, churn, and net impact.

Convert analysis into a presentation by copying charts from the data into a Google slide. Show churn improvements and a spend trend, with expansion from existing customers fueling new business.

Learn how to calculate net retention rate and its aliases, including dollar based net retention, using a 12-month cohort to compare baseline ARR with current ARR.

Calculate net retention rate in Excel by comparing the cohort ARR from 12 months ago to current ARR, using if formulas and baseline ARR to track performance.

Calculate the average contract term length by dividing bookings by annual recurring revenue, revealing trends from 12 to 19 months and illustrating this in a chart or slide.

Learn to build a customer analysis in Excel that tracks beginning, new, churned, and ending customers by period, using countif and countifs with prior and current period logic.

Compute quarter over quarter net adds, churn, and new customer velocity from the tables template, and analyze 100k and 10k thresholds to guide retention and growth.

Create two customer charts in Excel that track year-over-year customer count growth and quarterly net adds, by showing new customers and churn with a net change line.

Learn how average selling price, or ASP, plus bookings, define annualized revenue per customer. Explore land and expand ASP calculations using a simple P×Q framework.

Compute selling price by dividing land revenue by new customers to derive land ASP, and expansion revenue by expanding customers to derive expand ASP, then compare with spend per customer.

Create a two-line chart comparing land ASP and expand ASP trends, add data labels and colors, and show expand ASP stays higher and more stable than land ASP.

Analyze sales productivity by account owner using pivot tables to track bookings and deals closed over time, including last 12 month productivity and data bar visualization.

Build a high-low-close chart in Excel to visualize quarterly sales productivity ranges (min to max) and the average per sales rep with labeled data points.

Apply a practical forecasting framework for SaaS metrics, using historical data and trends to build a simple model of bookings, sales productivity, churn, land and expand, and ARR.

Forecast a five-quarter model in the template tab using bookings, sales rep ramp, 5% quarterly productivity, premium versus standard, regional mix, term length, churn, and arr.