Practice — build a 12-month multi-cloud cost model in a spreadsheet

This practice exercise builds a 12-month multi-cloud cost model for the IPL Scorecard Platform, quantifying the total cost of ownership across AWS, Azure, and GCP components. The model must produce a month-by-month forecast, distinguish between fixed baseline costs and variable traffic-driven costs, apply committed-use discounts where applicable, and identify the top five cost optimisation opportunities. The completed model becomes the input for the FinOps team’s commitment purchasing decisions and the engineering team’s architecture trade-off evaluations.

Analogy🏏Cricket

🏏 Think of it like cricket: In Test cricket, the ICC publishes playing conditions — governing over rates, DRS quotas, pitch inspection protocols, and player conduct — that both captains sign before the first session, whether the match is at Lord’s, the MCG, or Eden Gardens. Just as the playing conditions give umpires a single authoritative standard so every ruling references the same document rather than personal judgement, the Well-Architected Framework gives architects a shared evaluation language so every workload is measured against the same six pillars rather than each engineer’s intuition. Just as a team posting a slow over rate incurs penalties regardless of their score, a workload with Security or Reliability gaps carries structural risk regardless of how quickly it shipped. Just as every specialist role — opener, keeper, tail — has defined performance expectations against which selectors evaluate each player, every workload component is evaluated against pillar-specific best-practice questions. This reveals why the framework must precede any advanced architectural decision: a shared, evidence-based standard transforms subjective trade-offs into structured, auditable risk assessments that hold across teams, accounts, and regions.

Cost modelling requires three types of inputs: architecture inputs (which services, which sizes, which regions), traffic inputs (expected request volumes, data transfer volumes, storage growth rates), and pricing inputs (on-demand rates, Savings Plan rates, reserved rates from the cloud provider’s pricing pages). The model structure should separate each of these input categories into distinct worksheets or sections, enabling sensitivity analysis by changing a single input and observing the total cost impact without recalculating the entire model.

The exercise uses a spreadsheet format (Google Sheets or Excel) as the modelling tool rather than a purpose-built FinOps platform, because the spreadsheet’s transparency and flexibility enable teams to document their assumptions alongside the calculations, share the model with finance and business stakeholders who may not have access to cloud billing consoles, and perform what-if analysis by modifying input assumptions without code changes. The spreadsheet model is the finance team’s primary artifact for budget approval; it must be self-documenting.

Scenario

The IPL Scorecard Platform operates across three clouds: AWS hosts the primary scorecard API (Lambda + API Gateway + DynamoDB), the CDN layer (CloudFront), and the DR infrastructure (Route 53 health checks + S3 cross-region backup). Azure hosts the European fan-facing deployment (Container Apps + Azure Database for PostgreSQL + Azure CDN). GCP hosts the analytics pipeline (BigQuery + Cloud Storage + Cloud Dataflow). The model must capture all three cloud environments for a 12-month forecast starting from the current month.

Analogy🏏Cricket

🏏 Think of it like cricket: this resize scenario is like the media crew on a busy match day handling a predictable flow of highlight stills with sudden bursts during a collapse. Just as the IPL media team uploads about 500 images per match day, each 8 MB, needing broadcast, web, and mobile variants within 30 seconds, the crew shoots a steady stream of key moments that must hit the big screen, website, and app almost immediately. Just as the pipeline must absorb occasional bursts of 50 to 100 images when wickets tumble in a cluster, the crew faces a flurry of must-capture frames during a dramatic middle-order collapse and cannot drop any. The 30-second processing target is like the rule that a replay must reach the screen before the next ball is bowled. The payoff: like a crew sized for the steady rhythm yet ready for a sudden rush, the design must meet the everyday load cheaply while scaling instantly through a burst without losing a single frame.

Traffic assumptions: the platform serves 500 million API calls per month during the 5-month IPL season (April to August) and 20 million calls per month during the off-season. DynamoDB stores 100 GB of live match data growing at 5 GB per month. Cloud Storage holds 2 TB of historical match analytics data growing at 100 GB per month. BigQuery processes 500 GB of analytical queries per month during season and 50 GB per month off-season. The Azure Container Apps service runs 5 minimum instances during season and 1 during off-season.

Commitment assumptions: the team has not yet purchased any Savings Plans or Reserved Instances. The model must show both the current on-demand cost and the cost after applying a 1-year Compute Savings Plan covering 70% of the stable Lambda + EC2 baseline, and a 1-year DynamoDB reserved capacity covering the stable 100 GB baseline. The cost difference between the on-demand and committed scenarios is the annual savings from commitment purchasing.

Architecture Requirements

Build worksheets for: (1) Traffic Inputs, (2) AWS Pricing, (3) Azure Pricing, (4) GCP Pricing, (5) Optimisation Opportunities, (6) 12-Month Summary.
AWS costs: Lambda invocations ($0.20/million), Lambda duration (based on memory and duration per invocation), API Gateway HTTP API ($1.00/million), DynamoDB on-demand reads ($0.25/million), CloudFront data transfer ($0.085/GB first 10 TB), S3 standard storage ($0.023/GB-month), Route 53 health checks ($0.50/endpoint/month).
Azure costs: Container Apps per-vCPU-hour ($0.000012/vCPU-second) and per-GB-hour, Azure Database for PostgreSQL Flexible Server (Standard_D2s_v3 at $0.098/hour), Azure CDN data transfer ($0.081/GB first 10 TB).
GCP costs: BigQuery on-demand query ($5/TB scanned), Cloud Storage standard ($0.020/GB-month), Cloud Dataflow ($0.056/vCPU-hour + $0.003375/GB-hour).
Apply 1-year Compute Savings Plan discount of 40% to the stable Lambda baseline (on-demand equivalent) and show the committed versus on-demand cost comparison.
Identify and quantify the top five optimisation opportunities in the Optimisation worksheet with an estimated monthly saving for each.

Design Task 1 — AWS Cost Calculation

Calculate the monthly AWS cost for both the IPL season months (May to September) and off-season months. For Lambda: the season months have 500 million invocations at an average 100ms duration with 512 MB memory. For API Gateway: 500 million HTTP API calls per season month. For DynamoDB: 2 billion read request units per season month (4 reads per API call × 500M calls) plus 100 GB storage. Show all intermediate calculations explicitly, not just the totals, so the model is auditable.

Analogy🏏Cricket

🏏 Think of it like cricket: A trustworthy scorer does not just announce the final total — every run is written against the batter, every extra itemised, so any auditor can retrace exactly how 187 for 4 was reached. Just as that ball-by-ball ledger makes the scorecard verifiable rather than a bare number, this task requires every intermediate AWS figure shown explicitly, not just the monthly total. Just as the scorer computes each batter's contribution separately before summing the innings, you compute Lambda invocation cost and duration cost (500 million invocations at 100ms on 512MB), API Gateway (500 million HTTP calls), and DynamoDB (2 billion read units — four reads per API call — plus 100GB storage), for both the busy season months and the quiet off-season. Just as a disputed total can be settled by pointing to the delivery where it diverged, a visible calculation lets a reviewer find any error at the line it occurred. The payoff: an auditable, formula-driven AWS cost breakdown that finance can trust and engineers can adjust when a single traffic assumption changes.

python

# Python calculation for the AWS cost components (use in a script or notebook).
# In the actual exercise, implement these in a spreadsheet with visible formulas.

# Pricing constants (as of 2024).
LAMBDA_INVOCATION_PRICE = 0.20 / 1_000_000   # per invocation
LAMBDA_DURATION_PRICE   = 0.0000166667        # per GB-second
APIG_HTTP_PRICE         = 1.00 / 1_000_000    # per API call
DDB_READ_PRICE          = 0.25  / 1_000_000   # per RRU
DDB_STORAGE_PRICE       = 0.25  / 1           # per GB-month
CF_TRANSFER_PRICE       = 0.085               # per GB, first 10 TB
S3_STORAGE_PRICE        = 0.023               # per GB-month
R53_HEALTH_CHECK_PRICE  = 0.50                # per endpoint per month

def aws_monthly_cost(api_calls_millions: float, ddb_gb: float, cf_gb: float, month: str):
    lambda_invocations = api_calls_millions * 1_000_000 * LAMBDA_INVOCATION_PRICE
    # 512 MB = 0.5 GB; 100ms = 0.1 seconds; duration per invocation = 0.05 GB-second
    lambda_duration    = api_calls_millions * 1_000_000 * 0.05 * LAMBDA_DURATION_PRICE
    apig               = api_calls_millions * 1_000_000 * APIG_HTTP_PRICE
    ddb_reads          = api_calls_millions * 4 * 1_000_000 * DDB_READ_PRICE
    ddb_storage        = ddb_gb * DDB_STORAGE_PRICE
    cloudfront         = cf_gb * CF_TRANSFER_PRICE
    s3_storage         = 2_000 * S3_STORAGE_PRICE  # 2 TB backup storage
    r53                = 3 * R53_HEALTH_CHECK_PRICE  # 3 health check endpoints

    total = lambda_invocations + lambda_duration + apig + ddb_reads + ddb_storage + cloudfront + s3_storage + r53
    print(f'{month}: Lambda=${lambda_invocations+lambda_duration:.2f} + APIG=${apig:.2f} + DDB=${ddb_reads+ddb_storage:.2f} + CF=${cloudfront:.2f} + S3=${s3_storage:.2f} = ${total:.2f}')
    return total

print('=== AWS Monthly Costs ===')
season_months    = ['May','Jun','Jul','Aug','Sep']
offseason_months = ['Oct','Nov','Dec','Jan','Feb','Mar','Apr']

for m in season_months:
    aws_monthly_cost(500, 100+season_months.index(m)*5, 50_000, m)
for m in offseason_months:
    aws_monthly_cost(20, 100+5*5+offseason_months.index(m)*5, 2_000, m)

Design Task 2 — Azure and GCP Cost Calculation

Calculate the Azure and GCP monthly costs using the same seasonal traffic assumptions. For Azure Container Apps: 5 minimum instances during season at 0.5 vCPU and 1 GB each for 730 hours per month. For GCP BigQuery: 500 GB scanned per season month at $5 per TB. For Cloud Dataflow: a daily batch job processing 10 GB of match data at 4 vCPUs for 30 minutes per run. Show the annual total for all three clouds combined and identify which cloud represents the largest share of annual spend.

Analogy🏏Cricket

🏏 Think of it like cricket: Once you have costed the home franchise, you must cost the overseas satellite side and the analytics unit on the same basis before you can say which consumes the biggest share of the budget. Just as the European side's cost is driven by keeping a minimum bench continuously on the payroll, Azure Container Apps bills for 5 minimum instances at 0.5 vCPU and 1GB running the full 730 hours a month whether or not requests arrive. Just as the analytics unit is charged by how much archive footage it reviews rather than by the clock, GCP BigQuery bills $5 per TB scanned — 500GB a season month — and Cloud Dataflow bills for its daily 10GB batch at 4 vCPUs for 30 minutes. Just as a franchise finally totals every league's spend to see whether players, travel, or analytics dominates, you sum the annual cost across all three clouds and identify which holds the largest share. The payoff: costing Azure and GCP on the same seasonal assumptions reveals where the money actually goes and where optimisation effort will pay off most.

Design Task 3 — Commitment Savings Analysis

Calculate the annual saving from applying a 1-year Compute Savings Plan covering 70% of the stable Lambda invocation and duration baseline. The stable baseline is the off-season Lambda cost, which represents the compute spend that runs regardless of IPL season. A 1-year Compute Savings Plan provides approximately 40% discount on the covered compute spend. Show the commitment cost, the on-demand cost for the covered portion, the saving for the covered portion, and the net annual saving after the commitment fee.

Analogy🏏Cricket

🏏 Think of it like cricket: Deciding a marquee player's multi-year retention is a precise sum, not a gut feel — you take the guaranteed floor of matches he will play every season, lock that portion in at a discount, and leave the uncertain playoff appearances on match fees. Just as only the reliable, every-season core is worth committing to, this task treats the off-season Lambda cost as the stable baseline — the compute that runs regardless of IPL season — and commits a 1-year Compute Savings Plan to 70% of it at roughly a 40% discount. Just as a franchise compares the retention fee against what those matches would cost at open-market rates to prove the deal saves money, you show the commitment cost, the on-demand cost of the covered portion, and the gross saving on it. Just as the honest figure is the net after the guaranteed outlay, you report the net annual saving after the commitment fee, not the headline discount. The payoff: a rigorous commitment analysis that quantifies real savings and guards against over-committing to volatile, seasonal demand.

Evaluation Criteria

The 12-month summary shows distinct seasonal cost patterns with season months 3 to 5 times higher than off-season months, reflecting the traffic assumption inputs correctly.
All intermediate calculations are visible with formulas (not hardcoded values) so that changing the traffic input changes all downstream cost calculations automatically.
The commitment savings analysis correctly applies the 40% Savings Plan discount to 70% of the stable Lambda baseline and shows net annual saving, not gross saving.
The top five optimisation opportunities include at least one for each cloud and are ranked by estimated annual saving, with the calculation methodology documented.
The 12-month total for all three clouds combined is between $15,000 and $35,000 based on the provided pricing and traffic assumptions (deviations indicate calculation errors).
The model includes a sensitivity analysis row showing total cost at 50%, 100%, and 150% of the base traffic assumption to demonstrate cost behaviour under different scenarios.

Warning: Do not include AWS free tier credits in the cost model for production workloads. The free tier (1 million Lambda invocations, 25 GB DynamoDB storage) applies only to new AWS accounts and only for the first 12 months. A production cost model that includes free tier credits will underestimate actual billing once the free tier period expires or once the workload exceeds free tier limits, producing a budget shortfall that was not anticipated in the financial model.