What-if analysis in Excel is a powerful tool for individuals and organizations to use to explore various scenarios, predict the impact of choices, and make decisions confidently. It allows you to create scenarios with different sets of values for the same cells in a worksheet. With this feature, a user can view multiple forecasts of data in a single Excel document.
From the 2023 Volume 3 release onward, the Syncfusion Excel Library (XlsIO) supports a what-if analysis scenario manager feature in Excel documents using C#.
Users can perform the following actions using the what-if analysis scenario manager:
- Create scenarios with different sets of values and apply them to view the forecast results.
- Apply scenarios and save the results as separate Excel documents.
- Protect or hide the scenarios to prevent users from changing them.
Let’s see how to perform these actions in an Excel document using C#.
Note: If you are new to our Excel Library, following our getting started guide is highly recommended.
Creating scenarios
You can create multiple scenarios in an Excel document, and each scenario can have up to 32 variable cell ranges with values.
The following code example illustrates how to create what-if analysis scenarios in an Excel document.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
FileStream inputStream = new FileStream("WhatIfAnalysisTemplate.xlsx", FileMode.Open, FileAccess.Read);
IWorkbook workbook = application.Workbooks.Open(inputStream, ExcelOpenType.Automatic);
inputStream.Dispose();
IWorksheet worksheet = workbook.Worksheets[0];
// Access the collection of scenarios in the worksheet.
IScenarios scenarios = worksheet.Scenarios;
//Initialize list objects with different values for scenarios.
List<object> currentChangePercentage_Values = new List<object> { 0.23, 0.8, 1.1, 0.5, 0.35, 0.2};
List<object> increasedChangePercentage_Values = new List<object> { 0.45, 0.56, 0.9, 0.5, 0.58, 0.43};
List<object> decreasedChangePercentage_Values = new List<object> { 0.3, 0.2, 0.5, 0.3, 0.5, 0.23};
List<object> currentQuantity_Values = new List<object> { 1500, 3000, 5000, 4000, 500, 4000 };
List<object> increasedQuantity_Values = new List<object> { 1000, 5000, 4500, 3900, 10000, 8900 };
List<object> decreasedQuantity_Values = new List<object> { 1000, 2000, 3000, 3000, 300, 4000 };
//Add scenarios in the worksheet with different values for the same cells.
scenarios.Add("Current % of Change", worksheet.Range["F5:F10"], currentChangePercentage_Values);
scenarios.Add("Increased % of Change", worksheet.Range["F5:F10"], increasedChangePercentage_Values);
scenarios.Add("Decreased % of Change", worksheet.Range["F5:F10"], decreasedChangePercentage_Values);
scenarios.Add("Current Quantity", worksheet.Range["D5:D10"], currentQuantity_Values);
scenarios.Add("Increased Quantity", worksheet.Range["D5:D10"], increasedQuantity_Values);
scenarios.Add("Decreased Quantity", worksheet.Range["D5:D10"], decreasedQuantity_Values);
//Saving the workbook as a stream.
using (FileStream stream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite))
{
workbook.SaveAs(stream);
}
}The following is the output image.
Applying scenarios
You can get a cell value after applying specific scenarios in the worksheet. You can save each scenario’s results as a separate Excel document. This way, the resultant document can be shared with other users.
The following code example illustrates how to apply scenarios in a worksheet and resave the results in separate Excel documents.
//Access the collection of scenarios in the worksheet.
IScenarios scenarios = worksheet.Scenarios;
for (int pos =0; pos < scenarios.Count; pos++)
{
//Apply scenarios.
scenarios[pos].Show();
IWorkbook newBook = excelEngine.Excel.Workbooks.Create(0);
IWorksheet newSheet = newBook.Worksheets.AddCopy(worksheet);
newSheet.Name = scenarios[pos].Name;
//Saving the new workbook as a stream.
using (FileStream stream = new FileStream(scenarios[pos].Name + ".xlsx", FileMode.Create, FileAccess.ReadWrite))
{
newBook.SaveAs(stream);
}
//To restore the cell values from the previous scenario results.
scenarios["Current % of Change"].Show();
scenarios["Current Quantity"].Show();
}Refer to the following images. Here, we’ve applied the Current % of Change and Current Quantity scenarios.
Protect scenarios
You can protect a scenario in an Excel document to keep other users from modifying it. By default, the scenarios are protected when the sheet is protected. This can be disabled by making the IScenario.Locked property false.
The following code example illustrates how to protect scenarios in an Excel document.
IWorksheet worksheet = workbook.Worksheets[0];
//To make a scenario editable after protecting the sheet set scenarios[0].Locked = false;
//Enable worksheet protection.
worksheet. Protect("scenario");The following is the output image. You can see that the edit option is disabled.
Hiding scenarios
You can also hide a scenario in an Excel document, so only specific users can view the scenario results. The following code example illustrates how to hide a scenario in an Excel document.
//Access the collection of scenarios in the worksheet.
IScenarios scenarios = worksheet.Scenarios;
//Disable the protection for a specific scenario.
scenarios["Increased % of Change"].Hidden = true;
worksheet. Protect("Scenario");Refer to the following output image. In it, we’ve hidden the Increased % of Change scenario.
GitHub samples
You can download examples of the C# what-if analysis scenario manager for Excel from this GitHub page.
Conclusion
Thanks for reading! In this blog, we’ve explored the new C# what-if analysis scenario manager feature in the Syncfusion Excel Library. This tool allows you to modify cell values and observe the resulting impact on worksheet formulas.
Take a moment to peruse the documentation, where you’ll find other Excel Library options and features like conditional formatting, tables, pivot tables, and charts. Using this library, you can also export Excel data to PDF, image, data table, CSV, TSV, HTML, collections of objects, ODS, JSON, and more file formats.
Are you already a Syncfusion user? You can download the product setup here. If you’re not a Syncfusion user yet, you can download a free 30-day trial here.
Please let us know in the comments section below if you have any questions about these features. You can also contact us through our support forum, support portal, or feedback portal. We are always happy to assist you!
