31:T658,

Key Features

${md` - **PERT Integration** ⇟: Use **optimistic**, **pessimistic**, and **most likely** duration estimates to model variability and real-world uncertainties. - **Monte Carlo Simulation** ⇟: Run customizable simulations (e.g., 100'000 iterations) to analyze schedule risks. View histograms of completion times and probabilities of meeting deadlines. - **Critical Path Visualization** ⇟: Generate an **Activity-On-Node** (**AON**) diagram to visualize activities and dependencies. Highlight the **most likely critical path** based on PERT estimates. `}

How to Use

${md` Simply fill in the built-in spreadsheet ⇟ with project data, including PERT estimates (optimistic, pessimistic, and most likely durations), and specify the number of iterations for the Monte Carlo simulation. The tool processes the data and generates detailed visualizations and results. You can also download the CSV data ⇟ for further analysis. `}

32:T550,standalone_package = html`

Optimized Standalone Version and CodePen Project

${md` 🌟 [ CodePen version ] For your convenience, an enhanced and optimized CodePen project—including the beta-PERT model—brings together the essential features of this notebook, allowing you to run schedule analyses on the fly. ↥ This CodePen project is also available as a standalone package:

📦 PERT-based_Monte_Carlo_Schedule_Analysis.zip (3.2 MB)
This package includes all the necessary libraries and the XLSM template.
⚖️ The spreadsheet used in this package is Jspreadsheet CE (MIT License), and the package is free to use without any restrictions.
Once deployed on your PC, 🔌 it can run offline without an internet connection.

`33:T54a,progress_bar_with_counter = { // Dimensions of the progress bar const width = 360; const height = 16; // Canvas for the progress bar const context = DOM.context2d(width, height); context.canvas.style.border = progress > 0 ? "solid 1px black" : "solid 1px #f0f0f0"; // Container div for the progress bar and text const container = document.createElement("div"); container.style.display = "flex"; container.style.alignItems = "center"; container.style.gap = "10px"; // Add the canvas (progress bar) to the container container.appendChild(context.canvas); // Create the iteration counter const iterationCounter = document.createElement("div"); iterationCounter.id = "iterationCounter"; iterationCounter.innerHTML = progress > 0 && progress < selected_Iterations && selected_Iterations == Nb_iterations.value ? `Progress: ${Math.round(100 * (progress / selected_Iterations))}% [${progress.toLocaleString('fr-CH')} / ${selected_Iterations.toLocaleString('fr-CH')}]` : ``; iterationCounter.style.fontSize = "15px"; container.appendChild(iterationCounter); // Render the progress bar context.clearRect(0, 0, width, height); context.fillStyle = progress > 0 ? "#4caf50" : "#ffffff"; context.fillRect(0, 0, Math.round((progress / selected_Iterations) * width), height); yield container; } 34:T456,${activityMap.get("Finish")?.ES == 0 ? `` : md` - **Minimum**: ${MonteCarloMinDuration.toFixed(1)} units - **Maximum**: ${MonteCarloMaxDuration.toFixed(1)} units - **Average** (${tex`\mu`}): ${MonteCarloMeanDuration.toFixed(1)} units - **Standard deviation** (${tex`\sigma`}): ${Math.sqrt(MonteCarloVariance).toFixed(2)} units - **Variance** (${tex`\sigma^2`}): ${MonteCarloVariance.toFixed(2)} [units²] `} ${generateDot(activities, dependencies, activityMap)?.criticalPath ? `This result is to be compared with the PERT expected duration:
**${activityMap.get("Finish").ES.toFixed(1)} units** (critical path ⇟: **${generateDot(activities, dependencies, activityMap)?.criticalPath}**)

` : "

"} 35:T77a,download_result = { if (activityMap.get("Finish")?.ES > 0) { return htl.html`

${ DOM.download(() => serialize(legend_Monte_Carlo), "legend_Monte_Carlo", "Save legend (SVG)") }

${ DOM.download(() => serialize(histogram_Monte_Carlo), "histogram_Monte_Carlo", "Save histogram (SVG)") }

${ DOM.download(conv2csv(cumulativeData), "MonteCarlo_Simulation", "Download data (CSV)") }

🡄 Click on the 1^st button to save the legend as an SVG vectorial illustration.
🡄 Click on the 2^nd button to save the graph as an SVG vectorial illustration.
🡄 Click on the 3^rd button to download the cumulative data, represented by the red curve, as a CSV file (${cumulativeData.length} values). This format is compatible with popular spreadsheet tools like Excel.
🡆 Note: In addition, you can also 📥 download the raw simulation data (${MonteCarloSimulationResults.length.toLocaleString('fr-CH')} values) for further analysis. Output data are in CSV format.

`} }36:T79c,Excel_template = md` ## Stacked Histogram in Excel Creating a basic histogram in Excel is straightforward and won’t be covered here. However, creating a **stacked histogram**—like the one generated in this notebook—is significantly more challenging because Excel lacks a built-in function for this purpose. To address this, I have prepared an **XLSM template** that dramatically simplifies the process. #### Files to download * 📥 Raw Simulation Data (CSV File) * 📥 XLSM Template (Macro-Enabled Excel File)
*You can also download the XLSM file directly from the* ᥫ ***Files Pane***. #### Before using the XLSM file 1. **Unblock the file**: Right-click on the downloaded file, select *Properties*, and check the box labeled ‘*Unblock*’ at the bottom. 2. **Enable macros**: When prompted, enable VBA macros, as they are required for the tool’s functionality. #### Instructions The next steps are detailed in the XLSM file. By following the provided guidelines, you can reproduce a **stacked histogram**—similar to the one displayed in this notebook—with just a few clicks. ### How the XLSM Template Works Since Excel (as of 2025) doesn’t natively support stacked histograms, the workaround is to use a **Stacked Column Chart**: - Go to **Insert > Charts > 2D Column > Stacked Column** to create this type of graph. - Define histogram bins and prepare the data for each category. The **XLSM template** completes the entire process: - It uses **Pivot Tables** to automatically structure the data. - A **VBA script** streamlines the workflow, enabling the user to update the spreadsheet with just one click. `37:T44d, ### Tool Performance The tool has undergone thorough testing to ensure its robustness. However, in rare instances, the simulation results may show inconsistencies in the list of critical paths. This stems from the random value generation approach, which uses a normal distribution based on the specified mean (${tex`\mu`}) and standard deviation (${tex`\sigma`}) derived from the PERT formula. While this method approximates PERT distributions effectively, it does not inherently enforce the optimistic and pessimistic bounds of a PERT distribution. As a result, some generated values may occasionally fall outside these bounds. In certain cases, this could lead to randomly generated task durations approaching zero, effectively removing tasks from the critical path. Users should be mindful of this limitation and carefully analyze simulation results—like any simulation—with due diligence to ensure reliability. If you wish to address this limitation or seek additional guidance, refer to the Appendix for the function normInv = ƒ(mean, stdDev).38:T66c,- 🔗 PERT-based Monte Carlo Schedule Analysis: This CodePen features all the tools presented in this notebook and many other functionalities. ★★★ - 🔗 Gantt Chart: Create a Gantt chart (Frappe Gantt) from an embedded spreadsheet (JSpreadsheet CE). ★★ - 🔗 Activity-on-Node (AON) Diagram: Visit this CodePen page to dynamically create AON diagrams directly from a spreadsheet. - 🔗 Critical Path Method (CPM): This Observable notebook explores the CPM. The Activity-On-Node (AON) diagram displayed here is based on that notebook. - 🔗 LinkedIn Post (January 2025): Project Management with PERT-Based Monte Carlo Schedule Analysis. - ▶️ Three-Point Estimates and PERT In this video, Pat J. Heffernan briefly explains the three-point estimation technique using PERT. - ▶️ Monte Carlo simulation using Excel + VBA: In this video, Pat J. Heffernan uses Excel and a VBA micro to analyze uncertainty of project durations using Monte Carlo simulation.39:T50d,function parseActivities(activitiesInput) { // Split the input into lines const lines = activitiesInput.split("\n"); // Parse each line into an activity object const activities = lines .map(line => { // Trim any extra whitespace and skip empty lines const trimmedLine = line.trim(); if (!trimmedLine) return null; // Extract columns using regular expressions, handling quoted descriptions const match = trimmedLine.match(/^(\S+)\s+(-|\d+(\.\d+)?)\s+(-|\d+(\.\d+)?)\s+"(.+)"$/); if (!match) return null; // Skip lines that don't match the expected format const [_, id, duration, , variance, , name] = match; // Parse and limit duration and varuabce to 2 significant digits after the decimal const parsedDuration = duration === "-" ? 0 : parseFloat(duration).toFixed(2); const parsedVariance = variance === "-" ? 0 : parseFloat(variance).toFixed(2); return { id, duration: parseFloat(parsedDuration), // Convert to a number again after toFixed variance: parseFloat(parsedVariance), // Convert to a number again after toFixed name }; }) .filter(activity => activity !== null); // Filter out invalid lines return activities; // Return the parsed activities array } 3a:T45c,function parseDependencies(dependenciesInput, activities) { // Extract the IDs from activities for easy lookup const activityIds = new Set(activities.map(activity => activity.id)); // Split the input into lines and remove any extra spaces const lines = dependenciesInput.split("\n").map(line => line.trim()).filter(line => line.length > 0); const dependencies = lines .map(line => { // Split the line by '>' and trim any extra spaces const parts = line.split('>').map(part => part.trim()); // Ensure the line has exactly two parts (source and target) if (parts.length !== 2) return null; const [source, target] = parts; // Return null if target is empty or source equals target if (target === "" || source === target) return null; return { source, target }; }) // Filter out null values .filter(dependency => dependency !== null); // Check if all sources and targets are in activityIds const allValid = dependencies.every(dep => activityIds.has(dep.source) && activityIds.has(dep.target)); return allValid ? dependencies : []; } 3b:T7b0,function calculateES(activities, dependencies) { // Ensure "Start" is included in the activities if (!activities.some(activity => activity.id === "Start")) { activities.unshift({ id: "Start", duration: 0, name: "Start" }); // Add Start activity if not present } let activityMap = new Map(); // Populate the activityMap with initial values activities.forEach(activity => { activityMap.set(activity.id, { ...activity, ES: activity.id === "Start" ? 0 : undefined, // ES = 0 for Start EF: activity.id === "Start" ? 0 : activity.duration, // EF = 0 for Start LS: undefined, LF: undefined, TotalFloat: undefined }); }); // Forward pass to calculate ES and EF for each activity let updated; do { updated = false; activities.forEach(activity => { let maxEF = 0; // Default value if no predecessors // Check all dependencies of the current activity dependencies.forEach(dep => { if (dep.target === activity.id) { let depActivity = activityMap.get(dep.source); if (depActivity && depActivity.EF !== undefined) { maxEF = Math.max(maxEF, depActivity.EF); // Get maximum EF of predecessors } } }); // Update ES if it is undefined or a higher value is found if (activityMap.get(activity.id).ES === undefined || maxEF > activityMap.get(activity.id).ES) { activityMap.get(activity.id).ES = Math.round(maxEF * 100) / 100; // Round to 2 decimal places updated = true; // Mark that an update occurred } // Calculate EF for each activity (EF = ES + Duration) when ES is updated if (activityMap.get(activity.id).ES !== undefined) { activityMap.get(activity.id).EF = Math.round((activityMap.get(activity.id).ES + activity.duration) * 100) / 100; // Round to 2 decimal places } }); } while (updated); // Repeat until no changes occur return activityMap; } 3c:Tac3,function calculateLS(activityMap, activities, dependencies) { // Ensure the "Finish" activity exists let finishActivity = activityMap.get("Finish"); if (!finishActivity) { // Calculate the largest EF value from activityMap let maxEF = 0; activityMap.forEach(activity => { if (activity.EF !== undefined) { maxEF = Math.max(maxEF, activity.EF); } }); // Initialize "Finish" activity with max EF activityMap.set("Finish", { id: "Finish", duration: 0, name: "Finish", ES: maxEF, EF: maxEF, LS: maxEF, LF: maxEF, TotalFloat: 0 }); finishActivity = activityMap.get("Finish"); } // Initialize all LS and LF with the ES of "Finish" const finishES = finishActivity.ES || 0; // Default to 0 if "Finish" ES is undefined activities.forEach(activity => { const activityData = activityMap.get(activity.id); if (activityData) { activityData.LS = finishES; activityData.LF = finishES; } }); let updated; do { updated = false; // Iterate through all activities (order-independent) activities.forEach(activity => { const activityData = activityMap.get(activity.id); if (!activityData) return; // Find the minimum LS among all successors let minLS = activityData.LF; // Start with the current LF value dependencies.forEach(dep => { if (dep.source === activity.id) { const successor = activityMap.get(dep.target); if (successor && successor.LS !== undefined) { minLS = Math.min(minLS, successor.LS); // Take the minimum LS of successors } } }); // Update LF if a smaller value is found if (activityData.LF > minLS) { activityData.LF = minLS; // Store raw value updated = true; } // Calculate LS: LS = LF - Duration const newLS = activityData.LF - activity.duration; if (activityData.LS > newLS) { activityData.LS = newLS; // Store raw value updated = true; } // Calculate TotalFloat: TotalFloat = LS - ES if (activityData.LS !== undefined && activityData.ES !== undefined) { activityData.TotalFloat = activityData.LS - activityData.ES; // Store raw value } }); } while (updated); // Repeat until no further updates occur // Apply rounding to the final values activityMap.forEach(activity => { if (activity.LF !== undefined) activity.LF = Math.round(activity.LF * 100) / 100; if (activity.LS !== undefined) activity.LS = Math.round(activity.LS * 100) / 100; if (activity.TotalFloat !== undefined) activity.TotalFloat = Math.round(activity.TotalFloat * 100) / 100; }); return activityMap; } 3d:Ta4f,// Main function to perform both passes // Main function to calculate schedule (ES, EF, LS, LF, TotalFloat) function calculateSchedule(activities, dependencies) { // Reset the error message div before each function call document.getElementById("spreadsheetMessage").innerHTML = ""; document.getElementById("criticalPathMessage").innerHTML = ""; // Helper function to detect cycles using topological sort function detectCycle(dependencies) { const inDegree = new Map(); const adjacencyList = new Map(); activities.forEach(activity => { inDegree.set(activity.id, 0); adjacencyList.set(activity.id, []); }); dependencies.forEach(({ source, target }) => { adjacencyList.get(source).push(target); inDegree.set(target, inDegree.get(target) + 1); }); const queue = []; const result = []; inDegree.forEach((degree, id) => { if (degree === 0) queue.push(id); }); while (queue.length > 0) { const node = queue.shift(); result.push(node); adjacencyList.get(node).forEach(neighbor => { inDegree.set(neighbor, inDegree.get(neighbor) - 1); if (inDegree.get(neighbor) === 0) queue.push(neighbor); }); } return result.length !== activities.length; } // Step 1: Detect if there's a cycle in the dependencies try { if (detectCycle(dependencies)) { // Display the error message in a div if a cycle is detected document.getElementById("spreadsheetMessage").innerHTML = `⚠ Cycle detected in the project dependencies. Please revise the spreadsheet.`; document.getElementById("criticalPathMessage").innerHTML = `⚠ Cycle detected in the project dependencies. Cannot proceed with schedule calculation.`; return; // Stop execution if cycle is detected } // Step 2: First pass - Calculate ES and EF let activityMap = calculateES(activities, dependencies); // Step 3: Second pass - Calculate LS, LF, and Total Float activityMap = calculateLS(activityMap, activities, dependencies); return activityMap; } catch (error) { mutable isRunning = false; // Interrupt any computation console.error(error); document.getElementById("spreadsheetMessage").innerHTML = `Error: ${error.message}`; document.getElementById("criticalPathMessage").innerHTML = `Error: ${error.message}`; } }3e:T2344,function generateDot(activities, dependencies, activityMap) { try { // Helper function for topological sorting function topologicalSort(activities, dependencies) { const inDegree = new Map(); const activityGraph = new Map(); // Initialize graph activities.forEach(activity => { inDegree.set(activity.id, 0); activityGraph.set(activity.id, []); }); // Build graph and in-degree map dependencies.forEach(dep => { if (!activityGraph.has(dep.source) || !activityGraph.has(dep.target)) { throw new Error(`Invalid dependency: ${dep.source} -> ${dep.target}`); } activityGraph.get(dep.source).push(dep.target); inDegree.set(dep.target, inDegree.get(dep.target) + 1); }); // Queue of activities with no incoming dependencies (in-degree 0) const queue = []; activities.forEach(activity => { if (inDegree.get(activity.id) === 0) { queue.push(activity.id); } }); const sortedActivities = []; while (queue.length > 0) { const activityId = queue.shift(); sortedActivities.push(activityId); activityGraph.get(activityId).forEach(neighbor => { inDegree.set(neighbor, inDegree.get(neighbor) - 1); if (inDegree.get(neighbor) === 0) { queue.push(neighbor); } }); } if (sortedActivities.length !== activities.length) { mutable isRunning = false; // Interrupt any simulation throw new Error("Cycle detected in activity dependencies"); } return sortedActivities; } // Perform topological sort on the activities const sortedActivityIds = topologicalSort(activities, dependencies); // Prepare the activity nodes with HTML table labels const activityNodes = activities .map(activity => { const activityData = activityMap.get(activity.id); if (!activityData) { throw new Error(`Activity data missing for ID: ${activity.id}`); } // If Total Float is 0, highlight the activity in red const isCritical = activityData.TotalFloat === 0; // For activities with a duration of 0, use a circle shape if (activityData.duration === 0) { return `${activity.id} [label=<${activity.name} ${activity.id === "Start" ? "

🚩" : activity.id === "Finish" ? "

🏁" : ""}>, shape=circle, width=1.3, color=black, penwidth=2.8, fillcolor="#F7F1EB", style=filled, fontsize=16];`; } // For activities with non-zero duration, use the table format return `${activity.id} [label=<

${activityData.ES+StartDateConvention}	${activityData.duration}	${activityData.EF}
${hideActivityID? "" : "Activity "+activity.id+" "}${DescriptionItalic? "" : ""}${activity.name}${DescriptionItalic? "" : ""}
${activityData.LS+StartDateConvention}	${activityData.TotalFloat}	${activityData.LF}

>, shape=plaintext, width=2, height=1.5, color=${isCritical ? 'red' : 'black'}];`; }) .join("\n"); // Extract critical activities (activities with TotalFloat === 0 and non-zero duration) const criticalActivities = sortedActivityIds.filter(activityId => { const activityData = activityMap.get(activityId); if (!activityData) { throw new Error(`Activity data missing for ID: ${activityId}`); } return activityData.duration !== 0 && activityData.TotalFloat === 0; }); // Check if there are multiple critical paths let criticalPath = ""; if (criticalActivities.length === 0) { criticalPath = ""; } else { // Initialize an array to hold all potential critical paths const criticalPaths = []; // Function to explore a path recursively function explorePath(currentPath, activityId) { currentPath.push(activityId); // Find the next critical activity that is dependent on the current activity const nextActivities = dependencies.filter(dep => dep.source === activityId && criticalActivities.includes(dep.target)); if (nextActivities.length === 0) { // If no further dependencies, store the completed path criticalPaths.push([...currentPath]); } else { // Recursively explore all next critical activities nextActivities.forEach(dep => { explorePath([...currentPath], dep.target); }); } } // Start exploring paths from all critical activities criticalActivities.forEach(activityId => { // To avoid exploring the same path multiple times, we check if the activity is already in any path const alreadyInPath = criticalPaths.some(path => path.includes(activityId)); if (!alreadyInPath) { explorePath([], activityId); } }); // After exploring all paths, check if we have multiple paths if (criticalPaths.length === 1) { criticalPath = criticalPaths[0].join(" > "); } else if (criticalPaths.length > 1) { criticalPath = "Multiple critical paths"; } } // Prepare the links (dependencies) between activities const activityLinks = dependencies .map(dep => { const depSource = dep.source; const depTarget = dep.target; const sourceData = activityMap.get(depSource); const targetData = activityMap.get(depTarget); if (!sourceData || !targetData) { throw new Error(`Dependency data missing for: ${depSource} -> ${depTarget}`); } const isCriticalLink = sourceData.TotalFloat === 0 && targetData.TotalFloat === 0; return `${depSource} -> ${depTarget} [${isCriticalLink ? 'color=red, penwidth=2' : ''}];`; }) .join("\n"); // Legend node in a separate graph const legendNode = ` graph [ fontname="Arial", fontsize = 15, label=<

༺ Legend ༻

ES	Duration	EF
Activity Description
LS	Total Float	LF

➔ Critical Path Link

Start day is Day ${StartDateConvention}

>, color=blue]; } `; // Return the full Graphviz DOT format string and the critical path return { dot: ` digraph CPM { rankdir=${ orientation_LR ? "LR" : "TD"}; bgcolor = "${background}"; graph [fontname="Arial", labelloc="t", labeljust="l", pad=0.2]; edge [fontname="Arial"]; node [fontname="Arial", shape=plaintext]; ${activityNodes} ${activityLinks} ${show_legend? legendNode: ""} } `, criticalPath: criticalPath }; } catch (error) { mutable isRunning = false; console.error(error.message); return { error: error.message }; } } 3f:T522,// Extract data from spreadsheet function extractFromSpreadsheet() { const data = spreadsheet.getData(); // Extract Activity Data const activityData = data .filter(row => row[0] && row[0].trim() !== '') // Filter out rows where Activity ID is empty .map(row => { const activityID = row[0] || ''; const duration = (row[6] !== undefined && row[1] !== null) ? row[6] : '?'; const variance = (row[7] !== undefined && row[1] !== null) ? row[7] : '?'; const nameDesc = row[2] || row[0] || ''; return `${activityID} ${duration} ${variance} "${nameDesc}"`; }); mutable activities_data = activityData.join('\n'); // Extract Dependence Data let dependenceData = []; data.forEach(row => { const activityID = row[0] || ''; const precedence = row[1] || ''; // Ignore row if Precedence is empty or "-" if (activityID && precedence && precedence !== '-') { const precedences = precedence.split(',').map(p => p.trim()); precedences.forEach(dep => { if (dep) { dependenceData.push(`${dep} > ${activityID}`); } }); } }); mutable dependencies_data = dependenceData.join('\n'); } 40:Tfdb,spreadsheet = { const container = document.getElementById('spreadsheet'); const headers = [ 'Activity
ID', 'Predecessors', 'Description', 'Optimistic
Duration (O)', 'Most Likely
Duration (ML)', 'Pessimistic
Duration (P)', 'Expected
Duration (PERT)', 'Variance
(PERT)' ]; // Initialize Handsontable correctly const hot = new Handsontable(container, { data: spreadsheet_data, colHeaders: true, rowHeaders: true, afterGetColHeader(col, TH) { if (TH && col >= 0) { TH.innerHTML = `

${headers[col]}

`; } }, manualRowMove: true, contextMenu: { items: { row_above: {}, row_below: {}, remove_row: {}, undo: {}, redo: {}, make_read_only: {}, alignment: {}, copy: {}, cut: {}, } }, columns: [ { data: 0, type: 'text' }, // Activity ID { data: 1, type: 'text' }, // Precedence { data: 2, type: 'text' }, // Name/Description { data: 3, type: 'text', className: 'htCenter' }, // Optimistic Duration (center-aligned) { data: 4, type: 'text', className: 'htCenter' }, // Most Likely Duration (center-aligned) { data: 5, type: 'text', className: 'htCenter' }, // Pessimistic Duration (center-aligned) { data: 6, type: 'numeric', className: 'htCenter', readOnly: true }, // Expected Duration (center-aligned) { data: 7, type: 'numeric', className: 'htCenter', readOnly: true } // Variance (center-aligned) ], // Custom logic to calculate Expected and Variance inside afterChange afterChange: function(changes) { if (!changes) return; // Early return if there are no changes let updates = []; changes.forEach(function(change) { var row = change[0]; var prop = change[1]; var optimistic = hot.getDataAtCell(row, 3); // Column index for 'Optimistic' var mostLikely = hot.getDataAtCell(row, 4); // Column index for 'Most Likely' var pessimistic = hot.getDataAtCell(row, 5); // Column index for 'Pessimistic' // Handle empty cells and "-" using Most Likely or 0 function handleEmptyOrDash(value, defaultValue) { return value === "" || value === "-" ? defaultValue : value; } // Update 'Expected' and 'Variance' cells based on changes in 'Optimistic', 'Most Likely', or 'Pessimistic' if (prop === 3 || prop === 4 || prop === 5) { // 3: Optimistic, 4: Most Likely, 5: Pessimistic optimistic = handleEmptyOrDash(optimistic, "-"); pessimistic = handleEmptyOrDash(pessimistic, "-"); if (optimistic === "-" || pessimistic === "-" ) { // Set 'Expected' to Most Likely or 0 if any field is empty or "-" updates.push([row, 6, mostLikely === "-" || mostLikely === "" ? 0 : mostLikely]); updates.push([row, 7, 0]); // Variance = 0 when any field is empty or "-" } else { // Calculate Expected Duration var expectedDuration = (parseFloat(optimistic) + 4 * parseFloat(mostLikely) + parseFloat(pessimistic)) / 6; var roundedExpectedDuration = isNaN(expectedDuration) ? mostLikely : expectedDuration.toFixed(2); updates.push([row, 6, roundedExpectedDuration]); // Set 'Expected' column // Calculate Variance var variance = Math.pow((parseFloat(pessimistic) - parseFloat(optimistic)) / 6, 2); var roundedVariance = isNaN(variance) ? 0 : variance.toFixed(2); updates.push([row, 7, roundedVariance]); // Set 'Variance' column } } }); // Apply all updates in a batch to minimize rendering if (updates.length) { hot.setDataAtCell(updates); } }, licenseKey: 'non-commercial-and-evaluation' }); return hot; } 41:T430,// This function generates random values following a normal distribution // with the specified mean and standard deviation, using the Box-Muller transform. // // While it approximates values for PERT distributions, it does not inherently enforce // the Optimistic and Pessimistic bounds of a PERT distribution. As a result, // generated values may sometimes fall outside these bounds. // // To mitigate this, one can combine this function with truncation or resampling // to enforce the desired range. In the runMonteCarloSimulation function, normInv // is combined with truncation to ensure the range remains close to the optimistic // and pessimistic estimates (mean ± 3 standard deviations). function normInv(mean, stdDev) { // Box-Muller transform for standard normal distribution const u1 = Math.random(); // Random number [0, 1) const u2 = Math.random(); // Another random number [0, 1) const z0 = Math.sqrt(-2.0 * Math.log(u1)) * Math.cos(2.0 * Math.PI * u2); // Scale and shift to desired mean and standard deviation return z0 * stdDev + mean; }42:T812,function runMonteCarloSimulation(Nb_iterations, activities, dependencies) { mutable selected_Iterations = Nb_iterations; return new Promise((resolve) => { if (mutable isRunning) { resolve([]); return; } mutable isRunning = true; // Set running state to true when started let results = []; let i = 0; function processNextBatch() { if (!(mutable isRunning)) { resolve(results); // Resolve with the results if stopped early return; } const batchSize = 251; // Using this odd number ensures smoother and more visually gradual increments in the progress bar. // The normInv function is combined with truncation to ensure the range remains close // to the optimistic and pessimistic estimates (mean ± 3 standard deviations). for (let j = 0; j < batchSize && i < Nb_iterations; j++, i++) { const activitiesMonteCarloSimulation = activities.map(activity => { const { id, duration, variance } = activity; const plausibleMin = duration - 3 * Math.sqrt(variance); // Mean minus 3 standard deviations const plausibleMax = duration + 3 * Math.sqrt(variance); // Mean plus 3 standard deviations return { id, duration: parseFloat(Math.max(plausibleMin, Math.min(plausibleMax, normInv(duration, Math.sqrt(variance)))).toFixed(2)), }; }); const schedule = calculateSchedule(activitiesMonteCarloSimulation, dependencies); const duration = schedule.get("Finish").ES; const criticalPath = generateDot(activitiesMonteCarloSimulation, dependencies, schedule)?.criticalPath; results.push({ iteration: i + 1, duration, criticalPath }); } if (i < Nb_iterations) { mutable progress = i; // Progress bar is updated setTimeout(processNextBatch, 0); } else { mutable isRunning = false; // Reset running state after completion mutable progress = 0; resolve(results); } } processNextBatch(); }); }43:T5cb,histogram_data = { // Extract durations from raw data let durations = MonteCarloSimulationResults.map(d => d.duration) let n = durations.length; // Number of data points // Sturges’ rule: Number of bins let numBinsSturges = Math.floor(Math.log2(n) + 1); // Freedman-Diaconis rule: Calculate bin width let q1 = d3.quantile(durations, 0.25); let q3 = d3.quantile(durations, 0.75); let iqr = q3 - q1; // Interquartile range let binWidthFD = 2 * iqr * Math.pow(n, -1/3); // Bin width using FD // Step 3: Dynamic bin width calculation let minDuration = d3.min(durations); let maxDuration = d3.max(durations); // Setting up a more refined domain range with 100 bins in mind let binCountDesired = 100; let binWidthDynamic = (maxDuration - minDuration) / binCountDesired; // Step 4: Define bins using D3's bin() with dynamic width let bins = d3.bin() .domain([minDuration, maxDuration]) .thresholds(d3.range(minDuration, maxDuration + binWidthDynamic, binWidthDynamic))(durations); // Step 5: Map bins to get counts let binCounts = bins.map(bin => ({ binStart: bin.x0, binEnd: bin.x1, count: bin.length, })); // Step 6: Calculate percentages let totalCount = durations.length; let binDataWithPercentage = binCounts.map(bin => ({ binStart: bin.binStart, binEnd: bin.binEnd, count: bin.count, percentage: (bin.count / totalCount) * 100, })); return binDataWithPercentage }44:T452,// Cumulative data to be displayed together with the histogram cumulativeData = { // Step 1: Calculate bin centers (x-axis: midpoint between binStart and binEnd) let binsWithCenters = histogram_data.map(d => ({ binStart: d.binStart, binEnd: d.binEnd, duration: (d.binStart + d.binEnd) / 2, // x is the midpoint between binStart and binEnd count: d.count, percentage: d.percentage // y is the percentage in each bin })); // Step 2: Calculate cumulative percentages let cumulativeData = binsWithCenters.reduce((acc, bin, i) => { if (i === 0) { // First bin's cumulative is its own percentage acc.push({ duration: bin.duration, count: bin.count, percentage: bin.percentage }); } else { // Subsequent bins' cumulative is the previous bin's cumulative + this bin's percentage let lastCumulativePercent = acc[i - 1].percentage; let lastCumulativeCount = acc[i - 1].count; acc.push({ duration: bin.duration, count: lastCumulativeCount + bin.count, percentage: lastCumulativePercent + bin.percentage }); } return acc; }, []); return cumulativeData }45:T5a4,layout = { let _id = 0; return function(layout) { return function * (parts, ...args) { yield html`

... loading ...

`; const str = parts.map((s, i) => s + (i < args.length ? args[i] : "")).join(""); const svg = graphviz.layout(str, "svg", layout); const id = ++_id; yield html`\

${svg}

`; setTimeout((id) => { var svgElement = d3.select(`#id_${id}`).select("svg"); // Set width dynamically svgElement.attr("width", `${width -60}px`); svgElement.attr("transform", "translate(30, 0)"); const svgHeight = svgElement.node().getBBox().height; svgElement.attr("height", `${svgHeight}px`); // Adjust the viewBox dynamically var g = svgElement.select("g"); var [x, y, _width, _height] = svgElement.attr("viewBox").split(" "); svgElement.attr("viewBox", `${x} ${y} ${_width} ${svgHeight+50}`); // Apply zoom functionality var zoom = d3.zoom(); svgElement.call(zoom.translateTo, width / 2, -svgHeight / 2 ); }, 1000, id); } } }; 46:T47c,// Legend creation function async function createLegend(MonteCarloSimulationResults) { // Check if there's no simulation data to display if (activityMap.get("Finish")?.ES == 0) { return htl.html`

⚠ No information is available to display on the histogram. Please revise the data in the spreadsheet above ⇞.

`; } // Count the number of times each criticalPath appeared const pathCounts = d3.rollup( MonteCarloSimulationResults, v => v.length, // Count occurrences instead of summing durations d => d.criticalPath ); // Sort the criticalPaths based on their count const sortedPaths = Array.from(pathCounts.entries()) .sort((a, b) => d3.descending(a[1], b[1])) .map(([key]) => key); // Create the legend plot with Plot.plot() const plot = Plot.plot({ height: 0, width: 800, // Set max width of the legend color: { legend: true, domain: sortedPaths, className: "legendHistogram"}, }); // Return the legend wrapped in a styled container return toSVG(plot); } 47:Tb06,// Function to generate the histogram plot function createHistogram() { const criticalPathData = getSortedCriticalPaths(MonteCarloSimulationResults); // If there's an error, return the error message if (criticalPathData.error) { return criticalPathData.message; } // Destructure sortedPaths from the result const { sortedPaths } = criticalPathData; // Plot the histogram const plot = Plot.plot({ height: 550, width: 800, marginLeft: 60, marginRight: 50, marginTop: 40, marginBottom: 60, y: { label: "Frequency of Events", labelOffset: 50, }, x: { label: "Duration \n(in units)", labelOffset: 55, fill: 'black', }, color: { legend: true, domain: sortedPaths, className: "legendHistogram"}, style: { fontSize: "12.5px", "font-weight": "bold", fill: 'black', }, marks: [ // Stacked histogram bars Plot.rectY(MonteCarloSimulationResults, Plot.stackY({ ...Plot.binX( { y: "count" }, { x: "duration", fill: "criticalPath", order: "sum" } ), reverse: true})), Plot.ruleX([MonteCarloMeanDuration], {stroke: "#ddd", strokeWidth: 5, strokeDasharray: "15,8"}), Plot.frame({ stroke: "black", strokeWidth: 1, }), Plot.axisX({ tickPadding: 10, fill: "black", stroke: "black", }), Plot.axisY({ tickPadding: 10, fill: "black", stroke: "black", }), // Trick for the secondary axis: see https://observablehq/dual-axis-bar-line-chart () => Plot.plot({ height: 550, width: 800, marginLeft: 60, marginRight: 50, marginTop: 40, marginBottom: 60, style: { fontSize: "12.5px", "font-weight": "bold", fill: 'black', }, marks: [ Plot.line(cumulativeData, { x: "duration", y: "percentage", stroke: "red", strokeWidth: 3 }), Plot.axisY({ anchor: "right", label: "Cumulative Frequency (%)", domain: [0, 100], grid: true, tickFormat: (d) => `${d}%`, tickPadding: 10, nice: true, line: true, fill: "black", stroke: "black", }), ], y: { axis: "right", label: "", domain: [0, 100], grid: true, tickFormat: (d) => ``, nice: true, line: true, fill: "black", stroke: "black", }, x: { axis: null } }) ] }); // Return the SVG representation of the plot return toSVG(plot); }48:Tf92,// See https://observablehq.com/@fil/plot-figure-to-svg // Topic discussed here: https://talk.observablehq.com/t/legend-not-saved-in-svg-png/6027 function toSVG(chart) { if (chart.nodeName !== "FIGURE") { return chart; } // the chart needs to be in the body if we want to read values, positions, sizes… document.body.appendChild(chart); const [x0, y0, width, height] = getBounds([chart]); const nodes = []; for (const node of d3 .select(chart) .selectChildren("h1,h2,h3,div,figcaption,svg")) { switch (node.nodeName.toLowerCase()) { case "div": { const children = d3.select(node).selectChildren("div,span"); const height = getBounds([node, ...children])[3] + 2; const svg = d3 .select(chart) .append("svg") .attr("width", width) .attr("height", height); nodes.push(svg.node()); const swatches = svg .selectAll() .data( Array.from(children, (d) => { const svg = d3.select(d).select("svg").node(); const bbox = svg.getBBox(); return { style: window.getComputedStyle(d), svg, width: bbox.width, height: bbox.height, text: d.textContent, bounds: getBounds([d]) }; }) ) .join("g") .attr( "transform", (d) => `translate(${d.bounds[0] - x0},${10 + d.bounds[1] - y0})` ); swatches .append((d) => d.svg) // "rect") .attr("width", (d) => d.width) .attr("height", (d) => d.height) .attr("y", (d) => `${-parseFloat(d.height) / 2}px`); swatches .append("text") .text((d) => d.text) .attr("x", (d) => d.width) .attr("dx", 5) .attr("dy", "0.38em") .attr("font-family", (d) => d.style.fontFamily) .attr("font-size", (d) => d.style.fontSize) .attr("fill", (d) => d.style.color); } break; case "figcaption": case "h1": case "h2": case "h3": { const svg = d3 .select(chart) .append("svg") .attr("width", width) .attr("overflow", "visible"); nodes.push(svg.node()); const children = d3.select(node).selectChildren(); let h = 0; for (const d of children.size() > 0 ? children.selectChildren() : [node]) { const style = window.getComputedStyle(d); const t = svg .append("g") .attr("transform", `translate(0,${h})`) .append(() => d3 .select( Plot.text([d.textContent], { text: (d) => d, lineWidth: (1.06 * parseFloat(style.width)) / parseFloat(style.fontSize), lineHeight: 1.2, frameAnchor: "top-left" }).plot() ) .select("text") .attr("font-family", style.fontFamily) .attr("font-size", 1.08 * parseFloat(style.fontSize)) .attr("font-weight", style.fontWeight) .attr("fill", style.color) .node() ); h += getBounds([t.node()])[3] + 4; } svg.attr("height", h); } break; case "svg": d3.select(chart).append(() => node); nodes.push(node); break; } } return serializeAll(nodes) .then((blob) => blob.text()) .then((c) => { document.body.removeChild(chart); return Object.assign(svg`${c}`, chart); }); }49:T5f5,// Given an array of SVG elements, composites them into a single SVG element, // and then serializes the result to a blob. async function serializeAll(elements, {padding = 10} = {}) { const fragment = location.href + "#"; let root; if (elements.length === 1) { root = elements[0].cloneNode(true); // optimize common case } else { const [ox, oy, dx, dy] = getBounds(elements); root = document.createElementNS(svgns, "svg"); root.setAttribute("width", dx + 2 * padding); root.setAttribute("height", dy + 2 * padding); root.setAttribute("viewBox", [-padding, -padding, dx + 2 * padding, dy + 2 * padding]); for (const element of elements) { const svg = root.appendChild(element.cloneNode(true)); const { x, y, width, height } = element.getBoundingClientRect(); svg.setAttribute("x", x - ox); svg.setAttribute("y", y - oy); svg.setAttribute("width", width); svg.setAttribute("height", height); } } const walker = document.createTreeWalker(root, NodeFilter.SHOW_ELEMENT); while (walker.nextNode()) { const node = walker.currentNode; for (const attr of node.attributes) { if (attr.value.includes(fragment)) { attr.value = attr.value.replace(fragment, "#"); } } } root.setAttributeNS(xmlns, "xmlns", svgns); root.setAttributeNS(xmlns, "xmlns:xlink", xlinkns); const serializer = new XMLSerializer(); const string = serializer.serializeToString(root); return new Blob([string], { type: "image/svg+xml" }); }4a:[{"id":"d4c4c380c7745bfc3baa97dee82675d3ba26be37076be68a067348bae9078dedc2dd3f8c8fb38f31a49097c77eb81dfd2569b38c53a8bfb5f156f4cb21b7bb22","href":"https://static.observableusercontent.com/files/d4c4c380c7745bfc3baa97dee82675d3ba26be37076be68a067348bae9078dedc2dd3f8c8fb38f31a49097c77eb81dfd2569b38c53a8bfb5f156f4cb21b7bb22","name":"graphviz.js","type":"application/javascript","lastModified":1736953736306.392,"size":659747},{"id":"bb02ab96cae5850e92c4e0640428ecd3698eadf44f35ef8d7c56b2c8f1e3f1ac9301e2d1a47bb765350d2358336102368907d7841f25bd116840a18eab965cf7","href":"https://static.observableusercontent.com/files/bb02ab96cae5850e92c4e0640428ecd3698eadf44f35ef8d7c56b2c8f1e3f1ac9301e2d1a47bb765350d2358336102368907d7841f25bd116840a18eab965cf7","name":"handsontable.full.min.css","type":"text/css","lastModified":1736940198961.152,"size":40378},{"id":"1b1617e3a6992b98bf0be5d8e689ab9244d88b166ce5923b47e4aa674eecdc430ee4164b604c4228b7a829b6de03d9ed9afc30c0c4693e9a4aafdeb76d217667","href":"https://static.observableusercontent.com/files/1b1617e3a6992b98bf0be5d8e689ab9244d88b166ce5923b47e4aa674eecdc430ee4164b604c4228b7a829b6de03d9ed9afc30c0c4693e9a4aafdeb76d217667","name":"handsontable.full.min.js","type":"application/javascript","lastModified":1736940203823.568,"size":1769611},{"id":"7e5f4efdea38b7a8fe84422bb5e1f2d8559954dc16ad8ea6e408a7d6da371a8289589a5045738ab2757f82eca874770455eb2061ffabcbe07cc3cbe23704deaa","href":"https://static.observableusercontent.com/files/7e5f4efdea38b7a8fe84422bb5e1f2d8559954dc16ad8ea6e408a7d6da371a8289589a5045738ab2757f82eca874770455eb2061ffabcbe07cc3cbe23704deaa","name":"json2csv.umd.js","type":"application/javascript","lastModified":1737042848433.572,"size":224399},{"id":"ad09b01efbe3f0a7a9409e8f219af997a55c1d2ccf84883beb8f8b644d89736ea3214d2869aa0554434ea3ead9fecf012650dda503653a2a7920bbb4650e0cf5","href":"https://static.observableusercontent.com/files/ad09b01efbe3f0a7a9409e8f219af997a55c1d2ccf84883beb8f8b644d89736ea3214d2869aa0554434ea3ead9fecf012650dda503653a2a7920bbb4650e0cf5","name":"Template_MonteCarlo_Histogram.xlsm","type":"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet","lastModified":1737276042777.474,"size":1191172},{"id":"95ba16736af1afcc09e0299f5a626dbfd62e112878fc918110183e5dc33ee182cfb9f9afa75ebd209ea3c30b6515e6d721af59b18747f22662873df236a25ce9","href":"https://static.observableusercontent.com/files/95ba16736af1afcc09e0299f5a626dbfd62e112878fc918110183e5dc33ee182cfb9f9afa75ebd209ea3c30b6515e6d721af59b18747f22662873df236a25ce9","name":"XLSM_Template_Stacked_Histogram_Screenshot.PNG","type":"image/png","lastModified":1737281834756.105,"size":165849},{"id":"3161e0ddf69eedc3bc34dfba8ee860953d170a55474e01805f73e0d74d27f23dfa69e7599f63d5f09246deb15989c160d5ec3dcce674f22764d4125c33ea7bfb","href":"https://static.observableusercontent.com/files/3161e0ddf69eedc3bc34dfba8ee860953d170a55474e01805f73e0d74d27f23dfa69e7599f63d5f09246deb15989c160d5ec3dcce674f22764d4125c33ea7bfb","name":"illustration_PERT-based_Monte_Carlo_Schedule_Analysis.png","type":"image/png","lastModified":1737314595719.977,"size":138757},{"id":"99bb3fc7901b0001654a32d405e0404d1c8851c3a25193b35c1ae1c6b8879f59d3ab1db8471bee0368799534d048216e59094fadd48492d0099670ed21cbd79f","href":"https://static.observableusercontent.com/files/99bb3fc7901b0001654a32d405e0404d1c8851c3a25193b35c1ae1c6b8879f59d3ab1db8471bee0368799534d048216e59094fadd48492d0099670ed21cbd79f","name":"PERT-based_Monte_Carlo_Schedule_Analysis@82.zip","type":"application/zip","lastModified":1740064340327.253,"size":3558817},{"id":"7151ba80091e6133ec996d89c2311d7e934168e326146f20e9ec6f0e80b6be960e2738e843c9dd0205d33149fa3cdd0e1c745d6a8b373de6d0f9b0d300d4cc0e","href":"https://static.observableusercontent.com/files/7151ba80091e6133ec996d89c2311d7e934168e326146f20e9ec6f0e80b6be960e2738e843c9dd0205d33149fa3cdd0e1c745d6a8b373de6d0f9b0d300d4cc0e","name":"YouTube_shorts_icon.svg","type":"image/svg+xml","lastModified":1737556132965.037,"size":583}] 2f:{"title":"PERT-Based Monte Carlo Schedule Analysis","cells":[{"id":0,"value":"# PERT-Based Monte Carlo Schedule Analysis","pinned":false,"hidden":false,"mode":"md"},{"id":9,"value":"**PERT**-based 🔗 schedule analysis using Monte Carlo simulation is a powerful method for project management. \n\nThe tool provided in this notebook combines the **Critical Path Method** 🔗 (**CPM**) and **Monte Carlo simulations** 🔗, enabling users to visualize schedules, estimate risks, and gain detailed insights into project timelines.\n","pinned":false,"hidden":false,"mode":"md"},{"id":3975,"value":"

Monte Carlo simulation histogram and Activity-On-Node (AON) diagram are generated on the fly.

\n","pinned":false,"hidden":false,"mode":"html"},{"id":3163,"value":"$31","pinned":false,"hidden":false,"mode":"html"},{"id":4260,"value":"$32","pinned":false,"hidden":false,"mode":"ojs"},{"id":26,"value":"hr = html`

`","pinned":false,"hidden":false,"mode":"ojs"},{"id":52,"value":"## Project Activities, Dependencies, and PERT Estimates","pinned":false,"hidden":false,"mode":"md"},{"id":374,"value":"Please fill in the spreadsheet with the following details for each activity:\n- **Activity ID**: A unique identifier for the activity (e.g., a letter or number).\n- **Predecessor(s)**: The ID(s) of activities that must be completed before this one can begin, separated by commas (e.g., A,B).\n- **Most Likely (ML) Duration**: The estimated time for the activity.\n\nIf the **Optimistic (O)** or **Pessimistic (P)** durations are not provided, don’t worry—**ML** will be used as the expected duration, and the variance will default to 0.\n\nThe **Expected Duration** (${tex`\\mu`}) and **Variance** (${tex`\\sigma^2`}) are automatically calculated as follows (PERT formulas):","pinned":false,"hidden":false,"mode":"md"},{"id":2186,"value":"tex`\n \\qquad \\textrm{Expected Duration} = \\mu = \\frac{P\\,+\\,4ML\\,+\\,O}{6}, \\qquad \\qquad \\textrm{Variance} = \\sigma^2 = \\big(\\frac{P\\,-\\,O}{6}\\big)^2 \n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":2212,"value":"PERT = html`
`","pinned":false,"hidden":false,"mode":"ojs"},{"id":115,"value":"viewof clear_data = Inputs.button(html`Clear Data`, {\n reduce: () => {\n mutable spreadsheet_data = JSON.parse(JSON.stringify(empty_data));\n const div = document.getElementById('spreadsheet');\n div.innerHTML = '';\n }\n});","pinned":false,"hidden":false,"mode":"ojs"},{"id":74,"value":"viewof update_spreadsheet = Inputs.button(\"Use Sample Data\", {\n reduce: async () => {\n mutable spreadsheet_data = JSON.parse(JSON.stringify(sample_data));\n \n const div = document.getElementById('spreadsheet');\n // Clear the div first\n div.innerHTML = '';\n }\n});\n\n\n\n","pinned":false,"hidden":false,"mode":"ojs"},{"id":4244,"value":"⚠️ This notebook utilizes a Handsontable spreadsheet (Freemium).\n
However, both the CodePen and the standalone package above use Jspreadsheet CE (MIT license) instead.","pinned":false,"hidden":false,"mode":"html"},{"id":56,"value":"spreadsheet_display = {\n const container = html`\n\n

\n`\n return container;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":234,"value":"
\n

\n ${Inputs.button(html`Load data from
spreadsheet`, {\n label: html`Use data from spreadsheet`,\n value: null,\n disabled: isRunning,\n reduce: () => {\n extractFromSpreadsheet(); \n }, \n })}\n

\n \n Note: You must click the button to update the AON diagram ⇟. \n Until then, the diagram will display information based on the previously saved data.\n
This action also initializes a short Monte Carlo simulation (${initial_Iterations} iterations).\n \n

","pinned":false,"hidden":false,"mode":"html"},{"id":2338,"value":"

","pinned":false,"hidden":false,"mode":"html"},{"id":2331,"value":"hr.cloneNode()","pinned":false,"hidden":false,"mode":"ojs"},{"id":790,"value":"## Monte Carlo Simulation","pinned":false,"hidden":false,"mode":"md"},{"id":1448,"value":"The simulation runs relatively quickly, but its speed depends on your computer’s performance.\nAdjust the number of iterations using the slider, then click the button to start the simulation.","pinned":false,"hidden":false,"mode":"md"},{"id":838,"value":"Nb_iterations = Inputs.range([5000, 1000000], {value: isRunning? selected_Iterations : selected_Iterations == \"null\" || selected_Iterations == initial_Iterations ? 50000: selected_Iterations, disabled: isRunning, step: 5000, label: htl.html`Number of
iterations`})","pinned":false,"hidden":false,"mode":"ojs"},{"id":956,"value":"viewof runSimulation = Inputs.button(html`Monte Carlo
simulation`, {\n label: html`Click to start calculations`,\n disabled: isRunning,\n reduce: () => {\n // Run the simulation\n mutable MonteCarloSimulationResults = runMonteCarloSimulation(Nb_iterations.value, activities, dependencies);\n },\n});\n","pinned":false,"hidden":false,"mode":"ojs"},{"id":1419,"value":"$33","pinned":false,"hidden":false,"mode":"ojs"},{"id":2367,"value":"// This button appears only when a simulation is running\nstop_simulation = {\n isRunning;\n if (mutable isRunning) { \n return html`\n ${Inputs.button(html`STOP
simulation`, {\n reduce: () => {\n mutable isRunning = false; \n extractFromSpreadsheet();\n }\n })}\n
\n `;\n } else { return html`
`;}\n}\n\n","pinned":false,"hidden":false,"mode":"ojs"},{"id":3276,"value":"#### Simulation Results\n${ progress>0 ? `⏳ *Data are being updated. Please be patient...*\\n`: ``}\nUsing Monte Carlo simulations and based on the PERT estimation, the simulated process durations are as follows:","pinned":false,"hidden":false,"mode":"md"},{"id":1450,"value":"$34","pinned":false,"hidden":false,"mode":"md"},{"id":1621,"value":"{\n MonteCarloCriticalPathCounts.length == 0? extractFromSpreadsheet() : null; // If empty, reload data from spreadsheet\n let summary = \"#### Critical Path Simulation Summary\\n\\n\";\n progress>0 ? summary += `⏳ *Critical paths are being updated. Please be patient...*\\n`: ``;\n\n MonteCarloCriticalPathCounts.forEach(item => {\n summary += `- **${item.criticalPath}** — Count: ${item.count.toLocaleString('fr-CH')} / ${MonteCarloSimulationResults.length.toLocaleString('fr-CH')}, Percentage: **${item.percentage}**\\n`;\n });\n if (activityMap.get(\"Finish\")?.ES == 0) {\n return htl.html`

⚠ No critical path detected. Please revise the data in the spreadsheet above ⇞.

`;\n }\nreturn md`${summary}\\n`;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":4856,"value":"legend_Monte_Carlo = createLegend(MonteCarloSimulationResults)","pinned":false,"hidden":false,"mode":"ojs"},{"id":1689,"value":"Monte_Carlo = md`\n### Monte Carlo Simulation Histogram\n\n${ progress>0 ? `⏳ *The histogram is being updated. Please be patient...*\\n`: ``}\nUsing ${progress==0? selected_Iterations.toLocaleString('fr-CH'): `previous`} data, this histogram displays the range of ‘Durations’.\nRefer to the legend to analyze the bar heights and compare them to the percentages summarized above.\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":4870,"value":"histogram_Monte_Carlo = createHistogram();","pinned":false,"hidden":false,"mode":"ojs"},{"id":2151,"value":"$35","pinned":false,"hidden":false,"mode":"ojs"},{"id":3935,"value":"hr.cloneNode()","pinned":false,"hidden":false,"mode":"ojs"},{"id":3868,"value":"$36","pinned":false,"hidden":false,"mode":"ojs"},{"id":3964,"value":"screenshot_XLSM_template = {\n const container = html`\n

Preview of the XLSM Template. The stacked histogram is generated in the \"Histogram\" tab. CSV data must be pasted in the \"Raw Data\" tab.\n

👆🏻 Click to resize the illustration\n

`;\n\n container.onclick = () => {\n const currentWidth = container.style.width;\n container.style.width = currentWidth === \"700px\" ? \"1100px\" : \"700px\";\n };\n\n return container; \n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":260,"value":"hr.cloneNode()","pinned":false,"hidden":false,"mode":"ojs"},{"id":349,"value":"## Critical Path Method (CPM)
and Activity-On-Node (AON) Diagram","pinned":false,"hidden":false,"mode":"md"},{"id":391,"value":"The following **Activity-on-Node (AON)** diagram is generated based on the data you have recorded for activities and their dependencies. The **critical path**, which represents the longest sequence of dependent activities, is highlighted in red: \n
critical path activity | ➔ critical path link.\n\nTo view the updated diagram, click the button labeled “**Update the AON Diagram**”. This will display the most current visualization of your project’s workflow and critical path.\n","pinned":false,"hidden":false,"mode":"md"},{"id":582,"value":"

\n ${Inputs.button(html`Update the
AON Diagram`, {\n label: html`Extract data from spreadsheet`,\n value: null,\n disabled: isRunning,\n reduce: () => {\n extractFromSpreadsheet();\n }, \n })}\n

","pinned":false,"hidden":false,"mode":"html"},{"id":660,"value":"critical_path_info = {return html`

`}","pinned":false,"hidden":false,"mode":"ojs"},{"id":711,"value":"{\n return activityMap && activityMap.size > 0 ? html`\n

\n Critical path: ${generateDot(activities, dependencies, activityMap)?.criticalPath || \n html`?`}\n ${activityMap.get(\"Finish\")?.ES > 0 ? \n html`(duration → \n ${activityMap.get(\"Finish\").ES}\n units)` : \"\"}\n

\n` : html`
`\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":1374,"value":"The **critical path** ${generateDot(activities, dependencies, activityMap)?.criticalPath ? \"(\"+generateDot(activities, dependencies, activityMap)?.criticalPath+\")\" : \"\"} and the AON diagram were derived from the estimations provided in the spreadsheet above ⇞, reflecting the PERT expected durations.","pinned":false,"hidden":false,"mode":"md"},{"id":278,"value":"CPM_diagram = dot`${generateDot(activities, dependencies, activityMap).dot || \"\"}`","pinned":false,"hidden":false,"mode":"ojs"},{"id":2576,"value":"\n

\n ${ viewof background }\n
\n ${ viewof activityColor }\n
\n ${ viewof StartDateConvention }\n
\n ${ viewof show_legend }\n
\n ${ viewof hideActivityID }\n
\n ${ viewof DescriptionItalic }\n
\n ${ viewof orientation_LR }\n

","pinned":false,"hidden":false,"mode":"html"},{"id":302,"value":"DOM.download(() => serialize(CPM_diagram.querySelector(\"svg\")), \"CPM_diagram\", \"Save as SVG\");\n","pinned":false,"hidden":false,"mode":"ojs"},{"id":1780,"value":"hr.cloneNode()","pinned":false,"hidden":false,"mode":"ojs"},{"id":3270,"value":"## Concluding Notes","pinned":false,"hidden":false,"mode":"md"},{"id":3866,"value":"### JavaScript vs Excel + VBA\nThis notebook draws partial inspiration from the work of Pat J. Heffernan (@PatJHeffernan 🔗), who implemented a similar Monte Carlo simulation using Excel and VBA (referenced below ⇟).\n\nThis web-based tool leverages modern browser optimizations, providing significant advantages over Excel + VBA. JavaScript enables faster computation speeds, better handling of large datasets, and enhanced interactivity, making it a more efficient and versatile solution.\n\nThat said, you can still download the simulation data ⇞ as a CSV file for further analysis in Excel ⇞ or any other spreadsheet software.","pinned":false,"hidden":false,"mode":"md"},{"id":2250,"value":"$37","pinned":false,"hidden":false,"mode":"md"},{"id":3636,"value":"

🎯

\n Hopefully, the tools provided in this Observable notebook have successfully debunked the complexity of Monte Carlo schedule analysis by showing how easily a simulation can be completed in just a few seconds. Not to mention the automatic generation of an Activity-on-Node diagram, making the schedule analysis process even more efficient and streamlined.\n

\n — C. Yamahata, January 2025\n

\n","pinned":false,"hidden":false,"mode":"html"},{"id":2248,"value":"useful_links = hr.cloneNode()","pinned":false,"hidden":false,"mode":"ojs"},{"id":1783,"value":"## Useful Links","pinned":false,"hidden":false,"mode":"md"},{"id":1786,"value":"$38","pinned":false,"hidden":false,"mode":"md"},{"id":4035,"value":"
\n\n
\n

\nYouTube Short: My spreadsheet doesn't do that!\n
Excel's elevator advertisement (1992) vs. today's technology...","pinned":false,"hidden":false,"mode":"html"},{"id":5259,"value":"
\n\n
\n

\nYouTube Short: IBM 7090 at RAND Corporation (1963).\n
Monte Carlo Simulations of PERT Networks with an IBM 7090.","pinned":false,"hidden":false,"mode":"html"},{"id":38,"value":"---","pinned":false,"hidden":false,"mode":"md"},{"id":40,"value":"viewof display_inspect = Inputs.toggle({label: \"⇣ Check to inspect variables and functions\", values: [\"true\", \"none\"], value: \"false\" })","pinned":false,"hidden":false,"mode":"ojs"},{"id":44,"value":"\n
\n${display_inspect == \"none\"? html`

⇣ Appendix is hidden ...

` : md`## Appendix`}","pinned":false,"hidden":false,"mode":"html"},{"id":238,"value":"$${display_inspect == \"none\"? \"\" : md`### Critical path functions`}","pinned":false,"hidden":false,"mode":"md"},{"id":242,"value":"$39","pinned":false,"hidden":false,"mode":"ojs"},{"id":245,"value":"$3a","pinned":false,"hidden":false,"mode":"ojs"},{"id":248,"value":"$3b","pinned":false,"hidden":false,"mode":"ojs"},{"id":251,"value":"$3c","pinned":false,"hidden":false,"mode":"ojs"},{"id":254,"value":"$3d","pinned":false,"hidden":false,"mode":"ojs"},{"id":257,"value":"$3e","pinned":false,"hidden":false,"mode":"ojs"},{"id":63,"value":"$${display_inspect == \"none\"? \"\" : md`### Handsontable`}","pinned":false,"hidden":false,"mode":"md"},{"id":60,"value":"Handsontable = {\n const styles = html``;\n document.head.appendChild(styles);\n return require( await FileAttachment(\"handsontable.full.min.js\").url());\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":231,"value":"$3f","pinned":false,"hidden":false,"mode":"ojs"},{"id":67,"value":"$40","pinned":false,"hidden":false,"mode":"ojs"},{"id":70,"value":"mutable spreadsheet_data = JSON.parse(JSON.stringify(sample_data))","pinned":false,"hidden":false,"mode":"ojs"},{"id":782,"value":"$${display_inspect == \"none\"? \"\" : md`### Monte Carlo`}","pinned":false,"hidden":false,"mode":"md"},{"id":771,"value":"$41","pinned":false,"hidden":false,"mode":"ojs"},{"id":832,"value":"$42","pinned":false,"hidden":false,"mode":"ojs"},{"id":994,"value":"mutable MonteCarloSimulationResults = runMonteCarloSimulation(initial_Iterations, activities, dependencies)","pinned":false,"hidden":false,"mode":"ojs"},{"id":1118,"value":"mutable isRunning = false;","pinned":false,"hidden":false,"mode":"ojs"},{"id":1391,"value":"mutable progress = 0","pinned":false,"hidden":false,"mode":"ojs"},{"id":1014,"value":"initial_Iterations = 5000","pinned":false,"hidden":false,"mode":"ojs"},{"id":1350,"value":"mutable selected_Iterations = null","pinned":false,"hidden":false,"mode":"ojs"},{"id":858,"value":"MonteCarloMinDuration = MonteCarloSimulationResults.reduce((min, result) => {\n return result.duration < min ? result.duration : min;\n}, Infinity)","pinned":false,"hidden":false,"mode":"ojs"},{"id":860,"value":"MonteCarloMaxDuration = MonteCarloSimulationResults.reduce((max, result) => {\n return result.duration > max ? result.duration : max;\n}, -Infinity); ","pinned":false,"hidden":false,"mode":"ojs"},{"id":1358,"value":"MonteCarloMeanDuration = parseFloat((MonteCarloSimulationResults.reduce((sum, result) => {\n return sum + result.duration;\n}, 0) / MonteCarloSimulationResults.length).toFixed(2));\n","pinned":false,"hidden":false,"mode":"ojs"},{"id":1793,"value":"MonteCarloVariance = parseFloat((MonteCarloSimulationResults.reduce((sum, result) => {\n return sum + Math.pow(result.duration - MonteCarloMeanDuration, 2);\n}, 0) / MonteCarloSimulationResults.length).toFixed(2));","pinned":false,"hidden":false,"mode":"ojs"},{"id":848,"value":"MonteCarloCriticalPathCounts = Object.entries(MonteCarloSimulationResults.reduce((acc, result) => {\n const path = result.criticalPath;\n if (acc[path]) {\n acc[path] += 1;\n } else {\n acc[path] = 1;\n }\n return acc;\n}, {})) // Convert the object to key-value pairs\n\n.map(([path, count]) => ({\n criticalPath: path,\n count: count,\n percentage: (100*count/mutable selected_Iterations).toFixed(1)+\"%\"\n}))\n.sort((a, b) => b.count - a.count)","pinned":false,"hidden":false,"mode":"ojs"},{"id":1823,"value":"$43","pinned":false,"hidden":false,"mode":"ojs"},{"id":1894,"value":"$44","pinned":false,"hidden":false,"mode":"ojs"},{"id":263,"value":"$${display_inspect == \"none\"? \"\" : md`### Graphviz`}","pinned":false,"hidden":false,"mode":"md"},{"id":265,"value":"graphviz = GraphvizModule.Graphviz.load();","pinned":false,"hidden":false,"mode":"ojs"},{"id":268,"value":"// GraphvizModule = import ('@hpcc-js/wasm/dist/graphviz');\nGraphvizModule = import(await FileAttachment(\"graphviz.js\").url())","pinned":false,"hidden":false,"mode":"ojs"},{"id":272,"value":"$45","pinned":false,"hidden":false,"mode":"ojs"},{"id":275,"value":"dot = {\n mutable isRunning = false; // When the graph is redrawn, we interrupt the simulation\n extractFromSpreadsheet();\n return layout(\"dot\");\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":281,"value":"viewof background = Inputs.color({label: html`Background color`, value: \"#ffffff\", datalist: [\"#ffffff\",\"#fafeff\",\"#fbfefc\",\"#faf9f5\",\"#fffafb\",\"#ededed\",\"#ebedff\",\"#e4ece7\",\"#eeeadd\",\"#f1e9eb\"]})","pinned":false,"hidden":false,"mode":"ojs"},{"id":284,"value":"viewof activityColor = Inputs.color({label: html`Activity cell color`, value: \"#f1fafa\", datalist: [\"#ffffff\",\"#f1fafa\",\"#fbfefc\",\"#faf9f5\",\"#fffafb\",\"#ededed\",\"#ebedff\",\"#e4ece7\",\"#eeeadd\",\"#f1e9eb\"]})","pinned":false,"hidden":false,"mode":"ojs"},{"id":287,"value":"viewof StartDateConvention = Inputs.radio(new Map([[\"Day 0\", 0], [\"Day 1\", 1]]), {value: 0, label: html`Start day
(recommendation)`, format: ([name, value]) => html`${name}`})","pinned":false,"hidden":false,"mode":"ojs"},{"id":290,"value":"viewof show_legend = Inputs.toggle({label: htl.html`Insert legend`, value: true})","pinned":false,"hidden":false,"mode":"ojs"},{"id":293,"value":"viewof hideActivityID = Inputs.toggle({label: htl.html`Hide activity ID`, value: true})","pinned":false,"hidden":false,"mode":"ojs"},{"id":296,"value":"viewof DescriptionItalic = Inputs.toggle({label: htl.html`Description in italic`, value: false})\n","pinned":false,"hidden":false,"mode":"ojs"},{"id":299,"value":"viewof orientation_LR = Inputs.toggle({label: htl.html`Horizontal orientation (→)`, value: true})","pinned":false,"hidden":false,"mode":"ojs"},{"id":305,"value":"$${display_inspect == \"none\"? \"\" : md`### Other`}","pinned":false,"hidden":false,"mode":"md"},{"id":5117,"value":"","pinned":false,"hidden":false,"mode":"html"},{"id":4851,"value":"// Function to check data validity and return sorted critical paths\nfunction getSortedCriticalPaths() {\n if (activityMap.get(\"Finish\")?.ES == 0) {\n return { error: true, message: \"
⚠ No information is available to display on the histogram. Please revise the data in the spreadsheet above ⇞.
\" };\n }\n\n // Count the number of times each critical path appeared\n const pathCounts = d3.rollup(\n MonteCarloSimulationResults,\n v => v.length, // Count occurrences instead of summing durations\n d => d.criticalPath\n );\n\n // Sort the critical paths based on their count\n const sortedPaths = Array.from(pathCounts.entries())\n .sort((a, b) => d3.descending(a[1], b[1]))\n .map(([key]) => key);\n\n return { error: false, sortedPaths: sortedPaths };\n}\n","pinned":false,"hidden":false,"mode":"ojs"},{"id":4854,"value":"$46","pinned":false,"hidden":false,"mode":"ojs"},{"id":4865,"value":"$47","pinned":false,"hidden":false,"mode":"ojs"},{"id":4295,"value":"$48","pinned":false,"hidden":false,"mode":"ojs"},{"id":4300,"value":"$49","pinned":false,"hidden":false,"mode":"ojs"},{"id":4309,"value":"function getBounds(elements) {\n let x1 = Infinity;\n let y1 = x1;\n let x2 = -x1;\n let y2 = x2;\n for (const element of elements) {\n const { x, y, width, height } = element.getBoundingClientRect();\n if (x < x1) x1 = x;\n if (x + width > x2) x2 = x + width;\n if (y < y1) y1 = y;\n if (y + height > y2) y2 = y + height;\n }\n return [x1, y1, x2 - x1, y2 - y1];\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":4303,"value":"svgns = \"http://www.w3.org/2000/svg\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":4305,"value":"xmlns = \"http://www.w3.org/2000/xmlns/\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":4307,"value":"xlinkns = \"http://www.w3.org/1999/xlink\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":308,"value":"// https://observablehq.com/@mbostock/saving-svg\nserialize = {\n const xmlns = \"http://www.w3.org/2000/xmlns/\";\n const xlinkns = \"http://www.w3.org/1999/xlink\";\n const svgns = \"http://www.w3.org/2000/svg\";\n return function serialize(svg) {\n svg = svg.cloneNode(true);\n const fragment = window.location.href + \"#\";\n const walker = document.createTreeWalker(svg, NodeFilter.SHOW_ELEMENT);\n while (walker.nextNode()) {\n for (const attr of walker.currentNode.attributes) {\n if (attr.value.includes(fragment)) {\n attr.value = attr.value.replace(fragment, \"#\");\n }\n }\n }\n svg.setAttributeNS(xmlns, \"xmlns\", svgns);\n svg.setAttributeNS(xmlns, \"xmlns:xlink\", xlinkns);\n const serializer = new window.XMLSerializer;\n const string = serializer.serializeToString(svg);\n return new Blob([string], {type: \"image/svg+xml\"});\n };\n}\n","pinned":false,"hidden":false,"mode":"ojs"},{"id":2160,"value":"// json2csv = require(\"json2csv@5.0.7/dist/json2csv.umd.js\")\njson2csv = require(await FileAttachment(\"json2csv.umd.js\").url())","pinned":false,"hidden":false,"mode":"ojs"},{"id":2153,"value":"function conv2csv (data) {\n let parser = new json2csv.Parser();\n let csv = parser.parse(data);\n return new Blob([csv], {type: \"text/csv\"}) \n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":3872,"value":"excel_file = await (FileAttachment(\"Template_MonteCarlo_Histogram.xlsm\").url())","pinned":false,"hidden":false,"mode":"ojs"},{"id":312,"value":"activities = parseActivities(activities_data);","pinned":false,"hidden":false,"mode":"ojs"},{"id":314,"value":"dependencies = parseDependencies(dependencies_data,activities);","pinned":false,"hidden":false,"mode":"ojs"},{"id":316,"value":"activityMap = calculateSchedule(activities, dependencies);","pinned":false,"hidden":false,"mode":"ojs"},{"id":635,"value":"empty_data = [\n [\"Start\", \"-\", \"Start\", \"\", \"-\", \"\", \"0\", \"0\"],\n [\"Finish\", \"Z\", \"Finish\", \"\", \"-\", \"\", \"0\", \"0\"],\n [\"A\", \"Start\", \"\", \"\", \"\", \"\", \"1\", \"0\"],\n [\"B\", \"\", \"\", \"\", \"\", \"\", \"1\", \"0\"],\n [\"C\", \"\", \"\", \"\", \"\", \"\", \"1\", \"0\"], \n [\"\", \"\", \"\", \"\", \"\", \"\", \"\", \"\"], \n [\"\", \"\", \"\", \"\", \"\", \"\", \"\", \"\"], \n [\"\", \"\", \"\", \"\", \"\", \"\", \"\", \"\"], \n [\"\", \"\", \"\", \"\", \"\", \"\", \"\", \"\"], \n [\"\", \"\", \"\", \"\", \"\", \"\", \"\", \"\"], \n [\"\", \"\", \"\", \"\", \"\", \"\", \"\", \"\"], \n [\"\", \"\", \"\", \"\", \"\", \"\", \"\", \"\"], \n [\"\", \"\", \"\", \"\", \"\", \"\", \"\", \"\"], \n [\"\", \"\", \"\", \"\", \"\", \"\", \"\", \"\"], \n [\"\", \"\", \"\", \"\", \"\", \"\", \"\", \"\"], \n [\"\", \"\", \"\", \"\", \"\", \"\", \"\", \"\"], \n [\"\", \"\", \"\", \"\", \"\", \"\", \"\", \"\"], \n [\"\", \"\", \"\", \"\", \"\", \"\", \"\", \"\"], \n [\"Z\", \"Start\", \"Dummy data (Z)

🗑️ ➜to delete\", \"7\", \"10\", \"13\", \"10\", \"1\"], \n [\"\", \"\", \"\", \"\", \"\", \"\", \"\", \"\"], \n ];","pinned":false,"hidden":false,"mode":"ojs"},{"id":626,"value":"sample_data = [\n [\"Start\", \"-\", \"Start\", \"\", \"-\", \"\", \"0\", \"0\"],\n [\"Finish\", \"K,N,O\", \"Finish\", \"\", \"-\", \"\", \"0\", \"0\"],\n [\"A\", \"Start\", \"\", 2, 4, 6, 4, 0.44],\n [\"B\", \"Start\", \"\", 4, 5, 12, 6, 1.78],\n [\"C\", \"B\", \"\", 3, 5, 7, 5, 0.44],\n [\"D\", \"A\", \"\", 7, 10, 13, 10, 1.00],\n [\"E\", \"A\", \"\", 11, 12, 13, 12, 0.11],\n [\"F\", \"B,D\", \"\", 5, 6, 7, 6, 0.11],\n [\"G\", \"C\", \"\", 9, 10, 11, 10, 0.11],\n [\"H\", \"C,F\", \"\", 5, 7, 9, 7, 0.44],\n [\"I\", \"D\", \"\", 7, 9, 11, 9, 0.44],\n [\"J\", \"E\", \"\", 1, 5, 9, 5, 1.78],\n [\"K\", \"G\", \"\", 15, 17, 19, 17, 0.44],\n [\"L\", \"H\", \"\", 6, 8, 10, 8, 0.44],\n [\"M\", \"I,J\", \"\", 9, 10, 17, 11, 1.78],\n [\"N\", \"G\", \"\", 12, 14, 16, 14, 0.44],\n [\"O\", \"L,M\", \"\", 16, 17, 18, 17, 0.11]\n ];","pinned":false,"hidden":false,"mode":"ojs"},{"id":326,"value":"mutable activities_data = null","pinned":false,"hidden":false,"mode":"ojs"},{"id":318,"value":"mutable dependencies_data = null","pinned":false,"hidden":false,"mode":"ojs"}],"files":"$W4a","stdlib":"1","titling":"auto"}