Appscript for Google Sheets that build a comprehensive and interactive financial model for a GPU rental business on a decentralized rental network

gpu-rental-financial-model-appscript.js

/*****************************************************
 * Google Apps Script for a Comprehensive 
 * GPU Rental Business Financial Model in Google Sheets
 *
 * This script:
 *   1. Creates/clears four sheets: 
 *       - "Inputs"
 *       - "Calculations"
 *       - "Dashboard"
 *       - "Scenario Results"
 *   2. Inserts default values (including multiple GPU variants, overhead, depreciation, 
 *      quantity options, etc.) 
 *   3. Builds row-by-row calculations for all variants and quantity options 
 *   4. Creates a Dashboard summarizing key metrics (with charts)
 *   5. Stores scenario toggles ("Best", "Worst", "LowRevenue", "HighRevenue") 
 *      and a function (runAllScenarios) that systematically sets each scenario, 
 *      logs the results, and writes them to the "Scenario Results" sheet
 *
 * HOW TO USE:
 *   1. Create a new, blank Google Sheet.
 *   2. Open Extensions → Apps Script (or Tools → Script Editor in older UIs).
 *   3. Delete any existing code in the script editor and paste this entire script.
 *   4. Click Save (you may be prompted to name your project).
 *   5. In the toolbar, select "setupModel" from the function dropdown and click "Run".
 *   6. Authorize if prompted. Return to the sheet to see your new tabs and custom menu.
 *
 * NOTE: The code attempts to be as comprehensive as possible, but feel free to extend 
 *       scenario toggles, depreciation calculations, or advanced rate logic as needed.
 *****************************************************/

/** 
 * onOpen() - Automatically adds our custom menu for quick actions 
 */
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('GPU Rental Model')
    .addItem('Set Up Model', 'setupModel')
    .addItem('Load Best Case Scenario', 'loadBestCase')
    .addItem('Load Worst Case Scenario', 'loadWorstCase')
    .addItem('Load LowRevenue Scenario', 'loadLowRevenueScenario')
    .addItem('Load HighRevenue Scenario', 'loadHighRevenueScenario')
    .addItem('Run All Scenarios', 'runAllScenarios')
    .addToUi();
}

/** 
 * setupModel() - Main entry point to configure or refresh the Sheets
 */
function setupModel() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  // Create or clear each sheet we'll use
  createOrClearSheet(ss, 'Inputs');
  createOrClearSheet(ss, 'Calculations');
  createOrClearSheet(ss, 'Dashboard');
  createOrClearSheet(ss, 'Scenario Results');

  // Fill out the sheets
  const inputsSheet = ss.getSheetByName('Inputs');
  fillInputsSheet(inputsSheet);
  formatInputsSheet(inputsSheet);

  const calculationsSheet = ss.getSheetByName('Calculations');
  setupCalculationsSheet(calculationsSheet);
  formatCalculationsSheet(calculationsSheet);

  const dashboardSheet = ss.getSheetByName('Dashboard');
  setupDashboardSheet(dashboardSheet);
  formatDashboardSheet(dashboardSheet);

  const scenarioResultsSheet = ss.getSheetByName('Scenario Results');
  setupScenarioResultsSheet(scenarioResultsSheet);
  formatScenarioResultsSheet(scenarioResultsSheet);

  // (Optional) Load a default scenario to populate sample data
  loadBestCase();
}

/**
 * createOrClearSheet() - Utility to create a sheet if non-existent,
 * or clear it if it already exists
 */
function createOrClearSheet(ss, sheetName) {
  let sheet = ss.getSheetByName(sheetName);
  if (!sheet) {
    ss.insertSheet(sheetName);
  } else {
    sheet.clear();
    sheet.clearFormats();
    sheet.clearNotes();
  }
}

/**
 * fillInputsSheet() - Inserts default input values, including
 * different GPU variants, overhead, depreciation, etc.
 *
 * Rows 1-...: General parameters
 * Following rows: GPU Variants table
 * Another section: Quantity options
 */
function fillInputsSheet(sheet) {
  // High-level inputs
  const inputsData = [
    // The columns are: [ Label, Default Value ]
    ['Electricity Rate (USD/kWh)', 0.12],
    ['Additional System Overhead (W)', 50],
    ['Monthly Depreciation (Low)', 0.015],
    ['Monthly Depreciation (High)', 0.05],
    ['Annual Depreciation (Best)', 0.10],
    ['Annual Depreciation (Worst)', 0.25],
    ['Platform Fee (%) Single GPU', 10], // Updated to a realistic default (10%)
    ['Platform Fee (%) Eight GPUs', 25], // Updated to a realistic default (25%)
    ['Maintenance Cost/Month (USD)', 0],
    ['Hours of Operation per Day', 24],
    ['Days in Month for Calc', 30],
    ['Break-even Rate (Single GPU)', 0.25],
    ['Break-even Rate (Eight GPUs)', 2.00],
  ];

  sheet.getRange(1, 1, inputsData.length, 2).setValues(inputsData);

  // Label GPU Variants section
  const startRow = inputsData.length + 2; // 13 + 2 = 15
  sheet.getRange(startRow, 1).setValue('GPU Variants (Name, Price USD, Power Draw W)');

  // Define GPU variants
  const gpuVariants = [
    ['Variant A (Low)', 1800, 350],
    ['Variant B (Mid)', 2800, 400],
    ['Variant C (High)', 3000, 400],
  ];

  // Place them in the sheet: columns A, B, C
  sheet.getRange(startRow + 1, 1, gpuVariants.length, 3).setValues(gpuVariants);

  // Label Quantity Options
  const qtyStartRow = startRow + gpuVariants.length + 3; // 15 + 3 + 3 = 21
  sheet.getRange(qtyStartRow, 1).setValue('Quantity Options');

  const quantityData = [
    ['Single GPU', 1],
    ['Eight GPUs', 8]
  ];
  sheet.getRange(qtyStartRow + 1, 1, quantityData.length, 2).setValues(quantityData);
}

/**
 * formatInputsSheet() - Applies formatting to the Inputs sheet
 */
function formatInputsSheet(sheet) {
  // Apply bold to general input headers
  const inputsHeaderRange = sheet.getRange(1, 1, 13, 2);
  inputsHeaderRange.setFontWeight('bold');

  // Define rows based on inputsData length
  const inputsDataLength = 13;
  const startRow = inputsDataLength + 2; // 15
  const gpuVariantsStartRow = startRow;
  const gpuVariantsCount = 3;
  const qtyStartRow = gpuVariantsStartRow + gpuVariantsCount + 3; // 15 + 3 + 3 = 21

  // Apply borders to GPU Variants section
  const gpuVariantsRange = sheet.getRange(gpuVariantsStartRow + 1, 1, gpuVariantsCount, 3);
  gpuVariantsRange.setBorder(true, true, true, true, true, true);

  // Apply borders to Quantity Options section
  const quantityCount = 2;
  const quantityRange = sheet.getRange(qtyStartRow + 1, 1, quantityCount, 2);
  quantityRange.setBorder(true, true, true, true, true, true);

  // Bold section headers
  sheet.getRange(gpuVariantsStartRow, 1).setFontWeight('bold');
  sheet.getRange(qtyStartRow, 1).setFontWeight('bold');

  // Enable text wrapping for all cells
  sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).setWrap(true);

  // Auto-resize columns
  sheet.autoResizeColumns(1, 3);

  // Set number formats
  // General Parameters (Column B)
  // Rows 1: Electricity Rate (USD/kWh) - Currency
  // Row 2: Overhead - Number
  // Rows 3-6: Depreciation - Percentage
  // Rows 7-8: Platform Fee (%) - Percentage
  // Row 9: Maintenance Cost - Currency
  // Rows 10-11: Hours and Days - Number
  // Rows 12-13: Break-even Rates - Number

  // Apply percentage format to depreciation and fees
  sheet.getRange(3, 2, 4, 1).setNumberFormat('0.00%'); // Rows 3-6
  sheet.getRange(7, 2, 2, 1).setNumberFormat('0.00%'); // Rows 7-8

  // Apply currency format to relevant cells
  sheet.getRange(1, 2, 1, 1).setNumberFormat('$0.00'); // Electricity Rate
  sheet.getRange(9, 2, 1, 1).setNumberFormat('$0.00'); // Maintenance Cost

  // Apply number format to other numerical inputs
  sheet.getRange(2, 2, 1, 1).setNumberFormat('0'); // Overhead
  sheet.getRange(10, 2, 1, 1).setNumberFormat('0'); // Hours of Operation
  sheet.getRange(11, 2, 1, 1).setNumberFormat('0'); // Days in Month
  sheet.getRange(12, 2, 2, 1).setNumberFormat('0.00'); // Break-even Rates

  // GPU Variants Table Formatting
  // Bold headers and background color
  sheet.getRange(gpuVariantsStartRow + 1, 1, 1, 3)
       .setFontWeight('bold')
       .setBackground('#D9E1F2');

  // Set number formats for GPU Variants
  sheet.getRange(gpuVariantsStartRow + 1, 2, gpuVariantsCount, 1).setNumberFormat('$#,##0');
  sheet.getRange(gpuVariantsStartRow + 1, 3, gpuVariantsCount, 1).setNumberFormat('0');

  // Quantity Options Table Formatting
  // Bold headers and background color
  sheet.getRange(qtyStartRow + 1, 1, 1, 2)
       .setFontWeight('bold')
       .setBackground('#D9E1F2');

  // Set number formats for Quantity Options
  sheet.getRange(qtyStartRow + 1, 2, quantityCount, 1).setNumberFormat('0');
}

/**
 * setupCalculationsSheet() - Builds a table of calculations referencing
 * the GPU variants and quantity options from the Inputs sheet, computing
 * monthly revenue, electricity cost, net revenue, profit, depreciation, ROI, etc.
 */
function setupCalculationsSheet(sheet) {
  // Header row
  const headers = [
    'GPU Variant',
    'Quantity',
    'GPU Price (USD)',
    'Power Draw (W)',
    'Rental Rate (USD/hour)',
    'Monthly Hours',
    'Monthly Revenue',
    'Electricity Cost',
    'Platform Fee',
    'Net Revenue',
    'Profit',
    'Monthly Dep (Low)',
    'Value after 1 month (Low)',
    'ROI (Low Dep) %'
  ];
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const inputsSheet = ss.getSheetByName('Inputs');

  // Define where GPU Variants are located in Inputs sheet
  const inputsDataLength = 13;
  const gpuVariantsStartRow = inputsDataLength + 2; // 15
  const gpuVariantsCount = 3;
  const variantsRange = inputsSheet.getRange(gpuVariantsStartRow + 1, 1, gpuVariantsCount, 3);
  const variantsData = variantsRange.getValues();

  // Define where Quantity Options are located in Inputs sheet
  const qtyStartRow = gpuVariantsStartRow + gpuVariantsCount + 3; // 15 + 3 + 3 = 21
  const qtyCount = 2;
  const qtyRange = inputsSheet.getRange(qtyStartRow + 1, 1, qtyCount, 2);
  const qtyData = qtyRange.getValues(); // (Label, quantity)

  // Create Calculations table: for each variant, for each quantity => 1 row
  let rowIndex = 2; // Start writing from row 2 (row 1 is headers)

  variantsData.forEach(variant => {
    qtyData.forEach(qty => {
      // GPU variant data
      const variantName = variant[0];
      const variantPrice = variant[1];
      const variantPower = variant[2];
      
      // Put them into the sheet
      sheet.getRange(rowIndex, 1).setValue(variantName);  // GPU Variant
      sheet.getRange(rowIndex, 2).setValue(qty[1]);       // Quantity
      sheet.getRange(rowIndex, 3).setValue(variantPrice); // GPU Price
      sheet.getRange(rowIndex, 4).setValue(variantPower); // Power Draw

      // Placeholder for Rental Rate
      sheet.getRange(rowIndex, 5).setValue(0.45);

      // Monthly Hours = Hours of Operation per Day * Days in Month
      sheet.getRange(rowIndex, 6).setFormula('=Inputs!B10 * Inputs!B11');

      // Monthly Revenue = Rental Rate * Monthly Hours * Quantity
      sheet.getRange(rowIndex, 7).setFormula(
        `=E${rowIndex} * F${rowIndex} * B${rowIndex}`
      );

      // Electricity Cost 
      // = (Power Draw + overhead) * monthly hours * (electricity rate / 1000) * quantity
      sheet.getRange(rowIndex, 8).setFormula(
        `= (D${rowIndex} + Inputs!B2) * F${rowIndex} * (Inputs!B1 / 1000) * B${rowIndex}`
      );

      // Platform Fee 
      // IF Quantity=1 => Single GPU fee => Inputs!B7
      // ELSE => Eight GPUs fee => Inputs!B8
      sheet.getRange(rowIndex, 9).setFormula(
        `=IF(B${rowIndex}=1, (Inputs!B7/100)*G${rowIndex}, (Inputs!B8/100)*G${rowIndex})`
      );

      // Net Revenue = Monthly Revenue - Platform Fee - Maintenance
      sheet.getRange(rowIndex, 10).setFormula(
        `=G${rowIndex} - I${rowIndex} - Inputs!B9`
      );

      // Profit = Net Revenue - Electricity Cost
      sheet.getRange(rowIndex, 11).setFormula(
        `=J${rowIndex} - H${rowIndex}`
      );

      // Monthly Dep (Low)
      sheet.getRange(rowIndex, 12).setFormula('=Inputs!B3');

      // Value after 1 month (Low) = Price * (1 - monthly_depr)
      sheet.getRange(rowIndex, 13).setFormula(
        `=C${rowIndex}*(1 - L${rowIndex})`
      );

      // ROI (Low Dep) % = (Profit + Dep Value) / Price * 100
      sheet.getRange(rowIndex, 14).setFormula(
        `=((K${rowIndex} + M${rowIndex}) / C${rowIndex})*100`
      );

      rowIndex++;
    });
  });

  // Apply formatting
  sheet.setFrozenRows(1); // Freeze header row
  sheet.getRange(1, 1, 1, headers.length)
       .setFontWeight('bold')
       .setBackground('#D9E1F2')
       .setHorizontalAlignment('center');

  // Enable text wrapping for all cells
  sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).setWrap(true);

  // Auto-resize columns
  sheet.autoResizeColumns(1, headers.length);

  // Set number formats
  // Identify columns:
  // C: GPU Price (USD) - Currency
  // D: Power Draw (W) - Number
  // E: Rental Rate (USD/hour) - Currency
  // F: Monthly Hours - Number
  // G: Monthly Revenue - Currency
  // H: Electricity Cost - Currency
  // I: Platform Fee - Currency
  // J: Net Revenue - Currency
  // K: Profit - Currency
  // L: Monthly Dep (Low) - Percentage
  // M: Value after 1 month (Low) - Currency
  // N: ROI (Low Dep) % - Percentage

  // Apply currency format
  sheet.getRange(2, 3, sheet.getLastRow() - 1, 1).setNumberFormat('$#,##0.00');
  sheet.getRange(2, 5, sheet.getLastRow() - 1, 1).setNumberFormat('$#,##0.00');
  sheet.getRange(2, 7, sheet.getLastRow() - 1, 1).setNumberFormat('$#,##0.00');
  sheet.getRange(2, 8, sheet.getLastRow() - 1, 1).setNumberFormat('$#,##0.00');
  sheet.getRange(2, 9, sheet.getLastRow() - 1, 1).setNumberFormat('$#,##0.00');
  sheet.getRange(2, 10, sheet.getLastRow() - 1, 1).setNumberFormat('$#,##0.00');
  sheet.getRange(2, 11, sheet.getLastRow() - 1, 1).setNumberFormat('$#,##0.00');
  sheet.getRange(2, 13, sheet.getLastRow() - 1, 1).setNumberFormat('$#,##0.00');

  // Apply percentage format
  sheet.getRange(2, 12, sheet.getLastRow() - 1, 1).setNumberFormat('0.00%');
  sheet.getRange(2, 14, sheet.getLastRow() - 1, 1).setNumberFormat('0.00%');

  // Align numerical columns to the right
  const numericalColumns = ['C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N'];
  numericalColumns.forEach(col => {
    sheet.getRange(`${col}2:${col}${sheet.getLastRow()}`).setHorizontalAlignment('right');
  });
}

/**
 * formatCalculationsSheet() - Applies additional formatting to the Calculations sheet
 * (This function is kept for extensibility; current formatting is handled in setupCalculationsSheet)
 */
function formatCalculationsSheet(sheet) {
  // Additional formatting can be added here if needed in the future
}

/**
 * setupDashboardSheet() - Creates a summary view
 * of the calculations, including some references and charts.
 */
function setupDashboardSheet(dashboardSheet) {
  // Clear existing content
  dashboardSheet.clear();

  // Title
  dashboardSheet.getRange('A1').setValue('GPU Rental Dashboard');

  // Subtitle or description
  dashboardSheet.getRange('A2').setValue('Example: Profit (Variant A, Single GPU)');

  // Reference Profit from Calculations
  dashboardSheet.getRange('B2').setFormula('=Calculations!K2');

  // Insert a bar chart comparing "Profit" across all rows in Calculations
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const calcSheet = ss.getSheetByName('Calculations');
  const lastCalcRow = calcSheet.getLastRow(); 
  const chartRangeLabels = calcSheet.getRange(2, 1, lastCalcRow - 1, 1); // GPU Variant names
  const chartRangeData = calcSheet.getRange(2, 11, lastCalcRow - 1, 1); // Profit in column K

  let profitChart = dashboardSheet.newChart()
    .setChartType(Charts.ChartType.BAR)
    .addRange(chartRangeLabels)
    .addRange(chartRangeData)
    .setOption('title', 'Profit by GPU Variant & Quantity')
    .setOption('hAxis', { title: 'Profit (USD)', format: '$#,##0.00' })
    .setOption('vAxis', { title: 'GPU Variant & Quantity' })
    .setOption('legend', { position: 'none' })
    .setPosition(4, 1, 0, 0)
    .build();

  dashboardSheet.insertChart(profitChart);

  // Process Scenario Results to create a time series data table
  const resultsSheet = ss.getSheetByName('Scenario Results');
  const lastResultsRow = resultsSheet.getLastRow();

  if (lastResultsRow > 1) { // Ensure there is data beyond headers
    const resultsData = resultsSheet.getRange(2, 1, lastResultsRow - 1, 9).getValues();

    // Create a map to hold scenario profits by date
    const scenarioMap = {};

    resultsData.forEach(row => {
      const timestamp = new Date(row[0]);
      const scenarioName = row[1];
      const profit = row[7];

      // Normalize the date to the first day of the month for grouping
      const month = timestamp.getMonth();
      const year = timestamp.getFullYear();
      const normalizedDate = new Date(year, month, 1);

      const dateKey = Utilities.formatDate(normalizedDate, ss.getSpreadsheetTimeZone(), 'MM/dd/yyyy');

      if (!scenarioMap[dateKey]) {
        scenarioMap[dateKey] = {};
      }
      scenarioMap[dateKey][scenarioName] = profit;
    });

    // Convert the map to a sorted array
    const sortedDates = Object.keys(scenarioMap).sort((a, b) => new Date(a) - new Date(b));

    const timeSeriesData = [['Date', 'Best Case', 'Worst Case', 'LowRevenue', 'HighRevenue']];

    sortedDates.forEach(date => {
      const row = [date];
      ['Best Case', 'Worst Case', 'LowRevenue', 'HighRevenue'].forEach(scenario => {
        row.push(scenarioMap[date][scenario] || 0); // Use 0 if no data
      });
      timeSeriesData.push(row);
    });

    // Write the time series data to the Dashboard sheet starting at A5
    dashboardSheet.getRange(5, 1, timeSeriesData.length, timeSeriesData[0].length).setValues(timeSeriesData);

    // Insert a time series chart for Profit over Time by Scenario
    let timeSeriesChart = dashboardSheet.newChart()
      .setChartType(Charts.ChartType.LINE)
      .addRange(dashboardSheet.getRange(5, 1, timeSeriesData.length, timeSeriesData[0].length))
      .setOption('title', 'Profit Over Time by Scenario')
      .setOption('hAxis', { title: 'Date', format: 'MM/dd/yyyy' })
      .setOption('vAxis', { title: 'Profit (USD)', format: '$#,##0.00' })
      .setOption('curveType', 'function') // Smooth lines
      .setOption('legend', { position: 'bottom' })
      .setOption('series', {
        0: { color: '#1c91c0' }, // Best Case
        1: { color: '#e7711b' }, // Worst Case
        2: { color: '#4374e0' }, // LowRevenue
        3: { color: '#e2431e' }  // HighRevenue
      })
      .setPosition(20, 1, 0, 0)
      .build();

    dashboardSheet.insertChart(timeSeriesChart);
  }

  // Enable text wrapping for all cells
  dashboardSheet.getRange(1, 1, dashboardSheet.getMaxRows(), dashboardSheet.getMaxColumns()).setWrap(true);

  // Auto-resize columns
  dashboardSheet.autoResizeColumns(1, 5);
}

/**
 * formatDashboardSheet() - Applies formatting to the Dashboard sheet
 */
function formatDashboardSheet(sheet) {
  // Format Title
  sheet.getRange('A1').setFontWeight('bold').setFontSize(16);
  
  // Format Subtitle
  sheet.getRange('A2').setFontStyle('italic');

  // Format the referenced Profit cell
  sheet.getRange('B2').setNumberFormat('$#,##0.00').setFontWeight('bold');

  // Align numerical columns to the right
  sheet.getRange('B2').setHorizontalAlignment('right');
}

/**
 * setupScenarioResultsSheet() - Prepares the "Scenario Results" sheet
 * to store scenario outcomes for each row in Calculations.
 */
function setupScenarioResultsSheet(resultsSheet) {
  // Header row:
  const headers = [
    'Timestamp',
    'Scenario Name',
    'GPU Variant',
    'Quantity',
    'Rental Rate',
    'Monthly Revenue',
    'Electricity Cost',
    'Profit',
    'ROI (Low Dep) %'
  ];
  resultsSheet.getRange(1, 1, 1, headers.length).setValues([headers]);
}

/**
 * formatScenarioResultsSheet() - Applies formatting to the Scenario Results sheet
 */
function formatScenarioResultsSheet(sheet) {
  // Apply bold to header row and set background color
  sheet.getRange(1, 1, 1, sheet.getLastColumn()).setFontWeight('bold').setBackground('#D9E1F2');

  // Enable text wrapping for all cells
  sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).setWrap(true);

  // Apply alternate row shading using conditional formatting
  const range = sheet.getRange(2, 1, sheet.getMaxRows() - 1, sheet.getLastColumn());
  const rule = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied('=ISEVEN(ROW())')
    .setBackground('#F9F9F9')
    .setRanges([range])
    .build();
  const rules = sheet.getConditionalFormatRules();
  rules.push(rule);
  sheet.setConditionalFormatRules(rules);

  // Auto-resize columns
  sheet.autoResizeColumns(1, sheet.getLastColumn());

  // Set number formats
  // Assuming columns:
  // A: Timestamp - Date
  // B: Scenario Name - Text
  // C: GPU Variant - Text
  // D: Quantity - Number
  // E: Rental Rate - Currency
  // F: Monthly Revenue - Currency
  // G: Electricity Cost - Currency
  // H: Profit - Currency
  // I: ROI (Low Dep) % - Percentage

  sheet.getRange(2, 1, sheet.getMaxRows() - 1, 1).setNumberFormat('mm/dd/yyyy hh:mm:ss'); // Timestamp
  sheet.getRange(2, 4, sheet.getMaxRows() - 1, 1).setNumberFormat('0'); // Quantity
  sheet.getRange(2, 5, sheet.getMaxRows() - 1, 1).setNumberFormat('$#,##0.00'); // Rental Rate
  sheet.getRange(2, 6, sheet.getMaxRows() - 1, 1).setNumberFormat('$#,##0.00'); // Monthly Revenue
  sheet.getRange(2, 7, sheet.getMaxRows() - 1, 1).setNumberFormat('$#,##0.00'); // Electricity Cost
  sheet.getRange(2, 8, sheet.getMaxRows() - 1, 1).setNumberFormat('$#,##0.00'); // Profit
  sheet.getRange(2, 9, sheet.getMaxRows() - 1, 1).setNumberFormat('0.00%'); // ROI %

  // Align numerical columns to the right
  const numericalColumns = ['D', 'E', 'F', 'G', 'H', 'I'];
  numericalColumns.forEach(col => {
    sheet.getRange(`${col}2:${col}${sheet.getMaxRows()}`).setHorizontalAlignment('right');
  });

  // Freeze header row
  sheet.setFrozenRows(1);
} 

/** 
 * loadBestCase() - Quick scenario override for certain cells 
 * (e.g., lower overhead, higher rental rates, lower platform fee).
 */
function loadBestCase() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const inputsSheet = ss.getSheetByName('Inputs');

  // Adjust data to reflect best-case scenario
  inputsSheet.getRange('B1').setValue(0.10); // Electricity Rate (USD/kWh)
  inputsSheet.getRange('B2').setValue(40);    // Overhead (W)
  inputsSheet.getRange('B7').setValue(10);    // Platform Fee (%) Single GPU (10%)
  inputsSheet.getRange('B8').setValue(25);    // Platform Fee (%) Eight GPUs (25%)

  // Optionally set default "high" rental rate in the Calculations 
  // The user can handle variant-specific rates differently if needed
}

/** 
 * loadWorstCase() - Quick scenario override for certain cells 
 * (e.g., higher overhead, lower rental rates, higher platform fee).
 */
function loadWorstCase() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const inputsSheet = ss.getSheetByName('Inputs');

  // Adjust data for worst-case scenario
  inputsSheet.getRange('B1').setValue(0.20); // Electricity Rate (USD/kWh)
  inputsSheet.getRange('B2').setValue(100);   // Overhead (W)
  inputsSheet.getRange('B7').setValue(25);    // Platform Fee (%) Single GPU (25%)
  inputsSheet.getRange('B8').setValue(35);    // Platform Fee (%) Eight GPUs (35%) - Adjusted below

  // Enforce maximum platform fee of 25%
  // Correct the Platform Fee (%) Eight GPUs to 25% if it exceeds
  const feeEightGPU = inputsSheet.getRange('B8').getValue();
  if (feeEightGPU > 25) {
    inputsSheet.getRange('B8').setValue(25);
  }
}

/**
 * loadLowRevenueScenario() - Lower rental rates specifically,
 * while leaving other aspects default or near-default.
 */
function loadLowRevenueScenario() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const calcSheet = ss.getSheetByName('Calculations');
  const lastRow = calcSheet.getLastRow();

  // Override the "Rental Rate (USD/hour)" column (col 5 => E) 
  // for each row to something low, e.g., 0.40
  for (let row = 2; row <= lastRow; row++) {
    calcSheet.getRange(row, 5).setValue(0.40); 
  }
}

/**
 * loadHighRevenueScenario() - Higher rental rates across the board
 */
function loadHighRevenueScenario() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const calcSheet = ss.getSheetByName('Calculations');
  const lastRow = calcSheet.getLastRow();

  // Override the "Rental Rate (USD/hour)" column (col 5 => E) to 0.60
  for (let row = 2; row <= lastRow; row++) {
    calcSheet.getRange(row, 5).setValue(0.60); 
  }
}

/**
 * runAllScenarios() - Demonstrates how to systematically apply each scenario,
 * read the resulting data from "Calculations" sheet, and store
 * them in "Scenario Results" sheet, so we can compare them side by side.
 */
function runAllScenarios() {
  const scenarioList = [
    { name: 'Best Case', loader: loadBestCase },
    { name: 'Worst Case', loader: loadWorstCase },
    { name: 'LowRevenue', loader: loadLowRevenueScenario },
    { name: 'HighRevenue', loader: loadHighRevenueScenario }
  ];

  scenarioList.forEach(s => {
    // Set the scenario
    s.loader();
    SpreadsheetApp.flush(); // Force recalc

    // Now read the calculations and log them
    saveScenarioResults(s.name);
  });

  // Update the Dashboard with the new time series chart
  const dashboardSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Dashboard');
  setupDashboardSheet(dashboardSheet);
}

/**
 * saveScenarioResults(scenarioName) - Reads the "Calculations" sheet row-by-row,
 * and writes the relevant data to "Scenario Results" with a timestamp.
 */
function saveScenarioResults(scenarioName) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const calcSheet = ss.getSheetByName('Calculations');
  const resultsSheet = ss.getSheetByName('Scenario Results');

  const lastRow = calcSheet.getLastRow();

  // We know the columns in "Calculations":
  //  A: GPU Variant (1)
  //  B: Quantity (2)
  //  C: GPU Price (3)
  //  D: Power Draw (4)
  //  E: Rental Rate (5)
  //  F: Monthly Hours (6)
  //  G: Monthly Revenue (7)
  //  H: Electricity Cost (8)
  //  I: Platform Fee (9)
  //  J: Net Revenue (10)
  //  K: Profit (11)
  //  L: Monthly Dep (Low) (12)
  //  M: Value after 1 month (Low) (13)
  //  N: ROI (Low Dep) % (14)

  // We'll gather for each row:
  //   [Timestamp, scenarioName, GPUVariant, Quantity, RentalRate, MonthlyRev, ElectricCost, Profit, ROI%]
  const outputData = [];
  const timeStamp = new Date();

  for (let row = 2; row <= lastRow; row++) {
    const gpuVariant = calcSheet.getRange(row, 1).getValue();
    if (!gpuVariant) {
      // skip empty row
      continue;
    }
    const quantity = calcSheet.getRange(row, 2).getValue();
    const rentalRate = calcSheet.getRange(row, 5).getValue();
    const monthlyRev = calcSheet.getRange(row, 7).getValue();
    const electCost = calcSheet.getRange(row, 8).getValue();
    const profit = calcSheet.getRange(row, 11).getValue();
    const roi = calcSheet.getRange(row, 14).getValue();

    outputData.push([
      timeStamp,
      scenarioName,
      gpuVariant,
      quantity,
      rentalRate,
      monthlyRev,
      electCost,
      profit,
      roi
    ]);
  }

  if (outputData.length > 0) {
    const startRow = resultsSheet.getLastRow() + 1;
    resultsSheet.getRange(startRow, 1, outputData.length, outputData[0].length)
      .setValues(outputData);
  }
}