{"id":1936,"date":"2026-03-15T23:16:59","date_gmt":"2026-03-15T23:16:59","guid":{"rendered":"https:\/\/hksmnow.com\/project-management\/?p=1936"},"modified":"2026-04-19T20:15:57","modified_gmt":"2026-04-19T20:15:57","slug":"from-excel-to-certainty-a-step-by-step-workflow-for-automated-risk-analysis","status":"publish","type":"post","link":"https:\/\/hksmnow.com\/project-management\/monte-carlo-risk-analysis\/from-excel-to-certainty-a-step-by-step-workflow-for-automated-risk-analysis\/","title":{"rendered":"From Excel to Certainty: A Step-by-Step Workflow for Automated Risk Analysis"},"content":{"rendered":"\n

You have the theory. You understand the math of the triangle. You have seen how a Tornado Chart can pinpoint your biggest threats. But in the real world, you are not managing three tasks in a vacuum. You are managing dozens, sometimes hundreds, in a complex project schedule.<\/p>\n\n\n\n

The biggest hurdle for most project managers is not the math – it is the ‘data bridge.’ How do you get your carefully crafted schedule out of a static spreadsheet and into a living, breathing Monte Carlo simulation?<\/p>\n\n\n\n

Today, we are moving from theory to production. We are going to build a complete, automated workflow. We will take a 5-task project schedule, export it, and run a full-scale risk report that compares distributions and identifies risk drivers automatically. If you have ever felt like your Excel sheet was a ‘dead’ document, this is the article that brings it to life.<\/p>\n\n\n\n

The Problem: The ‘Static Schedule’ Trap<\/h2>\n\n\n\n

Most project managers live in Excel or MS Project. These tools are fantastic for planning, but they are fragile. They tell you what will happen if everything goes exactly according to plan, but they are silent about what happens when it doesn’t.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

When you share a static Gannt chart, your stakeholders see a ‘final’ date. They don’t see the risk hidden behind those bars. By connecting your schedule to Python, you transform that static list into a dynamic engine. You move from ‘tracking’ to ‘simulating.’<\/p>\n\n\n\n

The Workflow: From MS Project to Monte Carlo<\/h2>\n\n\n\n

The process is surprisingly simple. You do not need to rebuild your schedule from scratch. Here is the exact workflow:<\/p>\n\n\n\n

    \n
  1. Copy your task list, durations, and predecessors directly from MS Project into an Excel sheet.<\/li>\n\n\n\n
  2. Add two new columns at the end: ‘Optimistic’ and ‘Pessimistic’.<\/li>\n\n\n\n
  3. For each task, enter your ‘best-case’ and ‘worst-case’ durations.<\/li>\n\n\n\n
  4. Save the file as a CSV (e.g., ‘Project_Example_1.csv’).<\/li>\n\n\n\n
  5. Donwload example CSV file <\/li>\n\n\n\n
  6. Update path on your google drive – file_path = ‘\/content\/drive\/MyDrive\/Colab Notebooks\/Monte Carlo Simulation\/Project_Example_1.csv’<\/strong><\/li>\n\n\n\n
  7. Copy paste the script and run in your Colab. <\/li>\n<\/ol>\n\n\n\n
    Project_Example_1<\/a>Download<\/a><\/div>\n\n\n\n

    By doing this, you keep all your hard-earned scheduling logic (like dependencies) but add the ‘range’ of uncertainty required for a simulation.<\/p>\n\n\n\n

    The Setup: Our 5-Task Example Data<\/h2>\n\n\n\n

    For this walkthrough, we are using the exact data from our example file. This represents a typical sequence of tasks where some happen in parallel and some are dependent on others.<\/p>\n\n\n\n

    Task Name<\/strong><\/td>Duration (Most Likely)<\/strong><\/td>Predecessors<\/strong><\/td>Optimistic (A)<\/strong><\/td>Pessimistic (B)<\/strong><\/td><\/tr><\/thead>
    Task 1<\/td>3<\/td>–<\/td>1<\/td>6<\/td><\/tr>
    Task 2<\/td>7<\/td>1<\/td>3<\/td>14<\/td><\/tr>
    Task 3<\/td>4<\/td>1<\/td>2<\/td>6<\/td><\/tr>
    Task 4<\/td>6<\/td>2<\/td>4<\/td>9<\/td><\/tr>
    Task 5<\/td>3<\/td>4<\/td>1<\/td>7<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

    This data structure is all the computer needs to start ‘rolling the dice.’<\/p>\n\n\n\n

    Try It Yourself: The Complete Automation Script<\/h2>\n\n\n\n

    If you are feeling brave or if you know Python, open Google Colab and paste the code below. This script is a “ready-to-run” version that mounts your Google Drive, reads your CSV, handles complex dependency chains, and produces a three-part visual report.<\/p>\n\n\n\n

    import pandas as pd\nimport numpy as np\nimport random\nimport matplotlib.pyplot as plt\nfrom google.colab import drive\n\n# --- 1. MOUNT DRIVE AND LOAD DATA ---\ndrive.mount('\/content\/drive', force_remount=True)\n\n# Update this path to match your file location in Google Drive\nfile_path = '\/content\/drive\/MyDrive\/Colab Notebooks\/Monte Carlo Simulation\/Project_Example_1.csv'<\/strong>\n\ntry:\n    df = pd.read_csv(file_path)\n    df.columns = df.columns.str.strip()\n    print('Data loaded successfully.')\nexcept Exception as e:\n    print(f'Error loading file: {e}')\n\niterations = 10000\n\n# --- 2. SIMULATION FUNCTIONS ---\ndef get_triangular(a, m, b):\n    u = random.random()\n    fc = (m - a) \/ (b - a)\n    if u < fc:\n        return a + (u * (b - a) * (m - a))**0.5\n    else:\n        return b - ((1 - u) * (b - a) * (b - m))**0.5\n\ndef get_pert(a, m, b):\n    alpha = 1 + 4 * ((m - a) \/ (b - a))\n    beta = 1 + 4 * ((b - m) \/ (b - a))\n    return a + (random.betavariate(alpha, beta) * (b - a))\n\n# --- 3. THE SCHEDULE SIMULATOR ---\ndef simulate_project(dist_type='triangular'):\n    finishes = np.zeros(len(df))\n    for i in range(len(df)):\n        row = df.iloc[i]\n        a, m, b = row['Optimistic'], row['Duration'], row['Pessimistic']\n        \n        dur = get_triangular(a, m, b) if dist_type == 'triangular' else get_pert(a, m, b)\n        \n        pred = row['Predecessors']\n        if pd.isna(pred) or str(pred).strip() == '' or str(pred).lower() == 'nan':\n            start_time = 0\n        else:\n            p_list = [int(float(p.strip())) for p in str(pred).split(',') if p.strip()]\n            start_time = max(finishes[p - 1] for p in p_list) if p_list else 0\n            \n        finishes[i] = start_time + dur\n    return np.max(finishes)\n\n# --- 4. RUN SIMULATIONS ---\nprint('Running simulations...')\ntri_totals = np.array(sorted([simulate_project('triangular') for _ in range(iterations)]))\npert_totals = np.array(sorted([simulate_project('pert') for _ in range(iterations)]))\n\n# --- 5. COMPARISON RESULTS ---\ndef calculate_metrics(data):\n    return {\n        'Mean': np.mean(data),\n        'P10': np.percentile(data, 10),\n        'P50': np.percentile(data, 50),\n        'P90': np.percentile(data, 90)\n    }\n\ntri_m = calculate_metrics(tri_totals)\npert_m = calculate_metrics(pert_totals)\n\nprint('\\n--- Comparison Results ---')\nprint(f\"{'Metric':<16} | {'Triangular':<10} | {'Beta (PERT)':<10}\")\nprint('-' * 42)\nprint(f\"{'Expected Mean':<16} | {tri_m['Mean']:<10.2f} | {pert_m['Mean']:<10.2f}\")\nprint(f\"{'P10 (Optimistic)':<16} | {tri_m['P10']:<10.2f} | {pert_m['P10']:<10.2f}\")\nprint(f\"{'P50 (Median)':<16} | {tri_m['P50']:<10.2f} | {pert_m['P50']:<10.2f}\")\nprint(f\"{'P90 (Safe)':<16} | {tri_m['P90']:<10.2f} | {pert_m['P90']:<10.2f}\")\n\n# --- 6. SENSITIVITY ANALYSIS (TORNADO) ---\nbaseline_finishes = np.zeros(len(df))\nfor i in range(len(df)):\n    row = df.iloc[i]\n    pred = row['Predecessors']\n    if pd.isna(pred) or str(pred).strip() == '' or str(pred).lower() == 'nan':\n        start = 0\n    else:\n        p_list = [int(float(p.strip())) for p in str(pred).split(',') if p.strip()]\n        start = max(baseline_finishes[p - 1] for p in p_list) if p_list else 0\n    baseline_finishes[i] = start + row['Duration']\n\nswings = []\nfor i in range(len(df)):\n    row = df.iloc[i]\n    swings.append({\n        'Task': row['Task Name'], \n        'Opt': row['Optimistic'] - row['Duration'], \n        'Pess': row['Pessimistic'] - row['Duration'], \n        'Width': row['Pessimistic'] - row['Optimistic']\n    })\n\ntornado_df = pd.DataFrame(swings).sort_values('Width')\n\n# --- 7. VISUALIZATION ---\nplt.figure(figsize=(22, 7))\n\n# Plot 1: S-Curve\nplt.subplot(1, 3, 1)\nprobs = np.linspace(0, 1, iterations)\nplt.plot(tri_totals, probs, label='Triangular', color='blue', linewidth=2)\nplt.plot(pert_totals, probs, label='Beta\/PERT', color='green', linewidth=2)\nplt.axhline(0.90, color='red', linestyle='--', label='90% Confidence')\nplt.title('Project S-Curve (Confidence)')\nplt.xlabel('Total Project Days')\nplt.ylabel('Probability')\nplt.grid(alpha=0.3)\nplt.legend()\n\n# Plot 2: Distribution\nplt.subplot(1, 3, 2)\nplt.hist(tri_totals, bins=50, alpha=0.4, color='blue', label='Triangular', density=True)\nplt.hist(pert_totals, bins=50, alpha=0.4, color='green', label='Beta\/PERT', density=True)\nplt.title('Distribution of Outcomes')\nplt.xlabel('Total Project Days')\nplt.ylabel('Frequency')\nplt.grid(alpha=0.3)\nplt.legend()\n\n# Plot 3: Tornado\nplt.subplot(1, 3, 3)\nplt.barh(tornado_df['Task'], tornado_df['Opt'], color='seagreen', label='Optimistic Swing')\nplt.barh(tornado_df['Task'], tornado_df['Pess'], color='indianred', label='Pessimistic Swing')\nplt.axvline(0, color='black', linewidth=1)\nplt.title('Tornado Chart: Risk Drivers')\nplt.xlabel('Days Deviation from Baseline')\nplt.legend()\n\nplt.tight_layout()\nplt.show()\n\n\n--- Comparison Results ---\nMetric           | Triangular | Beta (PERT)\n------------------------------------------\nExpected Mean    | 21.33      | 20.17     \nP10 (Optimistic) | 17.53      | 16.74     \nP50 (Median)     | 21.24      | 20.07     \nP90 (Safe)       | 25.25      | 23.65   \n<\/code><\/pre>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    Interpreting Your Professional Risk Report<\/h2>\n\n\n\n
    Once the script finishes, you are looking at the “Nerve Center” of your project risk. Here is how to explain these results to your stakeholders.<\/p>\n\n\n\n
    1. The Comparison Table: P90 vs. Reality<\/h2>\n\n\n\n
    Look at the P90 (Safe)<\/strong> row in the printed table. This is the date you should use for your external commitment. You will notice that the Triangular P90 is likely several days higher than the Beta P90.<\/p>\n\n\n\n
      \n
    • The Insight:<\/strong> The Triangular result is your ‘Insurance Policy.’ It covers the ‘unknown unknowns’ that the more optimistic Beta model might miss.<\/li>\n<\/ul>\n\n\n\n
      2. The S-Curve: Measuring Confidence<\/h2>\n\n\n\n
      The S-Curve is your most powerful visual.<\/p>\n\n\n\n
        \n
      • Steepness:<\/strong> If the lines are very steep, your project is predictable. If they are flat and stretched out, your project is a wildcard.<\/li>\n\n\n\n
      • The Red Line:<\/strong> Where the blue and green lines cross the 0.90 (90%) horizontal line tells you your project deadline with high confidence.<\/li>\n<\/ul>\n\n\n\n
        3. The Distribution of Outcomes: Visualizing the “Spread”<\/h2>\n\n\n\n
        The middle graph (the histogram) shows you where the most “mass” of your project duration lives.<\/p>\n\n\n\n
          \n
        • The Triangular<\/strong> distribution (blue) will look wider and flatter.<\/li>\n\n\n\n
        • The Beta\/PERT<\/strong> distribution (green) will look like a tighter bell curve.<\/li>\n\n\n\n
        • The Insight:<\/strong> If there is a massive gap between the peaks of these two curves, your project is highly sensitive to how you estimate risk.<\/li>\n<\/ul>\n\n\n\n
          4. The Tornado Chart: Your Battle Plan<\/h2>\n\n\n\n
          This identifies your “Risk Drivers.” The tasks at the top with the longest red bars are your biggest threats.<\/p>\n\n\n\n
            \n
          • Action Item:<\/strong> If ‘Task 2’ is at the top, do not waste time micromanaging ‘Task 5’ at the bottom. Even if Task 5 goes perfectly, it won’t move the needle. You must focus your energy on the top of the tornado.<\/li>\n<\/ul>\n\n\n\n
            Summary: From Guessing to Knowing<\/h2>\n\n\n\n
            By taking your MS Project data and adding just two columns, you have unlocked a professional level of risk insight. You are no longer guessing. You have a safe deadline backed by 10,000 simulations and a prioritized list of tasks that actually drive your project risk.<\/p>\n\n\n\n
            This workflow is repeatable. Every time your schedule changes, you simply hit ‘Run,’ and your entire risk profile updates instantly. This is the new standard for modern project leadership.<\/p>\n\n\n\n
            \n
            \n
            \n