What You Will Need
- Document Solutions for Excel
- Visual Studio 2022
- .NET 6+
Controls Referenced
- Document Solutions for Excel, C# .NET Excel Library
- Documentation | Online Goal Seek Demo
Tutorial Concept
Learn step-by-step how to implement Goal Seek using a C# .NET Excel API to achieve your desired results efficiently. Perfect for developers aiming to optimize sales projections.
Goal setting helps identify break-even points and guides decision-making for product scaling.
In today's competitive business landscape, determining the right sales target provides a clear financial direction and helps align all business activities toward achieving specific goals. The Excel “What-If” feature is an excellent tool for finding perfect sales targets through various options like Scenario Manager, Goal Seek, and Data Table, which helps in analysis, forecasting, and goal setting.
In this blog, we will use the Document Solutions for Excel (DsExcel) API to create a calculator. This calculator will determine the number of units we need to sell for a product to earn a specified profit. We will implement this solution in a .NET application, utilizing DsExcel's Goal Seek feature introduced in the recent v7.2 release. The implementation plan is as follows:
- Add Product Information to Excel Sheet Using C#
- Apply Profit/Loss Calculation Formula Using C#
- Apply Goal Seek Method in DsExcel
Add Product Information to Excel Sheet Using C
To build this calculator, we need some initial details, such as the total investment amount, the product’s unit price, and variable cost. Let's assume we invest $10,000,000 in producing the product with a selling price of $5,499 per unit and a variable cost of $1,100 per unit.
The DsExcel code to set these details in the sheet is as follows:
worksheet.Range["C2:E3"].Merge();
worksheet.Range["C2:E3"].Value = "Product Unit Calculator for Desired Profit";
worksheet.Range["B5"].Value = "Investment";
worksheet.Range["E5"].Value = "Desired Profit";
worksheet.Range["B8"].Value = "Product Unit Price";
worksheet.Range["B9"].Value = "Variable Cost per Unit";
worksheet.Range["E8"].Value = "Number of Units to Sell";
worksheet.Range["C5"].Value = 10000000; // Investment amount
worksheet.Range["C8"].Value = 5499; // Product unit price
worksheet.Range["C9"].Value = 1100; // Variable cost per unit
After applying formatting to the sheet, these values will appear like below:
Apply Profit/Loss Calculation Formula Using C
Now, it is time to calculate the number of units that we need to sell to earn a profit of $1,000,000. For this, we need to apply a formula to calculate the profit/loss based on product units and other parameters discussed above. For example, let’s use a random number for a unit and apply the following formula using DsExcel to show the earned profit/loss amount.
worksheet.Range["F5"].Formula = "=((C8+C9)*F8)-C5";
worksheet.Range["F8"].Value = 100; // Initial number of units to sell
Apply Goal Seek Method in DsExcel
Our goal is to calculate the number of units required to generate a profit of $1,000,000. To do this, we'll use the latest GoalSeek method in DsExcel, but first, let's break down how this method works.
The GoalSeek method helps you find the exact input value needed to achieve a specific result in a formula. This method is called from the cell where our formula is located (which should be a single cell). When using this method, we provide two key pieces of information: the goal (the result we want) and the changingCell (the input that will be adjusted to meet that goal).
Note:
- The formula in the calling range (the cell where GoalSeek is applied) must result in a numeric value.
- The changingCell parameter cannot contain a formula; it must be a cell with an input value that can be adjusted.
With that in mind, it's time to apply the GoalSeek method to our data and calculate the number of units we need to sell to achieve a profit of $1,000,000. We'll apply the GoalSeek on cell F5, which contains the formula for calculating profit. We'll set 1,000,000 as the goal and use cell F8 as the changing cell, which will update based on the set goal.
The DsExcel code to apply the GoalSeek method is as follows:
worksheet.Range["F5"].GoalSeek(1000000, worksheet.Range["F8"]);
That's it! We have successfully calculated the exact number of units that need to be sold to achieve the $1,000,000 profit using the Goal Seek method. Below is a screenshot showing the result:
You can also refer to this sample file implementing this exact method. Feel free to try it out with your product details and see how easily you can calculate the required units to reach your desired profit target.
Conclusion
In this blog, we learned how to use Excel’s Goal Seek feature in DsExcel to easily calculate the number of units needed to reach a specific profit goal. With DsExcel, you can simplify complex Excel tasks and add powerful automation to your .NET apps.
Exciting news! In our next major release, v8, we are adding support for the What-If Scenario feature, which expands the possibilities for advanced data analysis in Excel through the DsExcel API.
If you have any questions or feedback about this tutorial, feel free to share them in the comments below!
More References:
Top comments (0)