What is Solve in One Variable? Excel single variable solution case analysis

As a financial person, if you want to be promoted and get a salary increase, you must not just collect statistics. You also need to make some data predictions based on the needs of the company. If you do it manually, you need to go through very complex mathematical operations. In fact, Excel provides us with a tool that can easily complete data prediction, and that is the Excel single variable solving function.

Nowadays, analysis and forecasting are increasingly becoming an important part of the work of financial personnel. The most common and effective one is univariate analysis. There are many methods to complete univariate analysis, such as interpolation method, trial and error method, etc. But for an Excel expert, single variable solution is undoubtedly the best answer.

1. What is single variable solution?

As shown in the figure below, cell B2 is a constant, and cell B4 is the result of a specific operation on B2. We can change the value of B2 so that B4 also changes. Here we call B2 the variable cell and B4 the target cell.

This sequence calculation is very simple. For example, B2 is 95 and B4 is 200. So here comes the question, if we want the target cell (B4) to be equal to a specific value, how should we solve the corresponding variable cell (B2) value? Just like in elementary school mathematics, we know that the X value can be used to find the Y value, but how to find the X value based on the Y value? You need help with one variable solver. Single variable solution is the inverse operation of the function formula.

So where is the single variable solution in Excel, as well as the principles of single variable solution in Excel and Excel single variable solution cases, we will tell you in this article.

As an example, we assume that the target value is 250. The specific operation is as follows:

Step 01 Click the Excel [Data] tab - [Simulation Analysis] - [Single Click the "Single variable solution" button to pop up the "Single variable solution" dialog box.

Step 02 In the dialog box, set the target cell to $B$4, then manually enter the target value of the target cell to 250, and set The variable cell is $B$2. As shown below.

It should be noted that the target value of 250 is not set casually, but needs to meet the following two conditions:

1. It is an integer or decimal, text or logical value cannot be entered;

2. It should be included in the target cell value range, that is, there is a variable cell value that makes the target cell value equal to the target value. Otherwise, the single-variable solution will run longer and eventually prompt that a solution that satisfies the conditions cannot be obtained.

Step 03 Click the OK button to perform single variable solution. Excel automatically performs iterative operations, and finally obtains the variable cell value (120) when the target cell (B4) is equal to the target value (250), and automatically assigns it to the variable cell (B2). As shown below, click OK.

2. Excel single variable solution example

Excel single variable solution Case 1 Loan interest rate issue

Employee A is the financing specialist of Company A. The company plans to apply for a five-year loan of 35 million from the bank and repay the principal and interest in equal monthly installments. and pay interest. Based on the bank's initial annual interest rate plan of 6.3%, employee A calculated that Company A would need to pay 681,500 per month.

As shown in the figure below, using the formula: =PMI (annual interest rate/12, number of periods, loan amount), you can calculate the monthly repayment interest of 681,500.

However, after calculation, after the company obtains the loan, the funds available for loan repayment are expected to be only 675,000 per month. The financial director asked employee A to calculate the maximum annual interest rate acceptable to the company in order to negotiate with the bank.

A employee analyzed that cell B5 of the annual interest rate is a variable cell, B6 of the monthly principal and interest repayment is the target cell, and the target value is 67.5. Isn't this exactly a single variable solution?

Step 01 According to the data relationship that has been set, click the [Single Variable Solution] button, set the target cell to $B$6, the target value to 67.5, and the variable cell to $ B$5, click [OK] to perform single variable solution.

#Step 02 After iterative operation, excel quickly provides a solution that meets the conditions. Click [OK] to complete the single variable solution. As follows.

From the above chart we can see that the maximum annual interest rate the company can accept is 5.9%. This is single variable solution, which is not only efficient, but also accurate!

Excel single variable solution case 2 Equivalent interest rate problem

B employee is an investment of a certain company Manager, the company plans to acquire a new project, which requires an investment of 1.6 billion yuan (the current value of the cumulative investment). The planned unit price is 18,800, and the average payment period is expected to be 5 years. After calculation, the equivalent interest rate of the company's investment in the project is is 8.8%.

As shown in the figure below, using the formula: =RRI (repayment period, present value of total investment, sales income), you can calculate the actual equivalent interest rate of the current investment value corresponding to the future value.

According to the company's internal requirements, the investment equivalent interest rate for a billion-level project cannot be less than 10%, so the selling price needs to be adjusted.

Employee B uses the selling price B3 as the variable cell, the equivalent interest rate B8 as the target cell, and the target value is 10%.

Step 01 According to the data relationship that has been set, click the [Single Variable Solution] button, set the target cell to $B$8, the target value to 10%, and the variable cell to $B$3, click [OK] to perform single variable solution.

Step 02 After iterative operation, excel quickly provides a solution that meets the conditions. Click [OK] to complete the single variable solution.

As shown in the picture above, we can see the results. The minimum selling price is adjusted to 19,879.09 yuan, which can meet the company's requirements.

Excel single variable solution case 3 Break-even point problem

Employee C is the financial manager of a manufacturing company. The company has a factory. Due to the market Due to poor performance, the company plans to reduce the factory output to 200,000. Employee C needs to calculate the profit and loss of the factory at this output. It is known that the average selling price of a product decreases as output increases, and the three fees are in a certain proportion to sales and revenue. After calculation, the total profit under the output of 200,000 yuan is 13.8368 million yuan. As shown below.

The market performance of the company's products continues to decline, and the company's senior management is considering continuing to reduce production and is required to control production around the break-even point.

Employee C thought about it and used output B3 as the variable cell, total profit B12 as the target cell, and the target value was 0, and the break-even output was calculated in one fell swoop.

Step 01 According to the data relationship that has been set, click the [Single Variable Solution] button, set the target cell to $B$12, the target value to 0, and the variable cell to $ B$3, click [OK] to perform single variable solution.

Step 02 After iterative operation, excel quickly provides a solution that meets the conditions. Click [OK] to complete the single variable solution.

We can see from the picture above that the company controls the production volume around 81,900, which can break even.

Single variable solution can be regarded as a high-powered and unpopular application in Excel. It has the feeling of "it's worthless to say it, but it's exhausting if you don't say it". In the past, you may have spent a lot of time trying to solve this type of single-variable analysis problem manually. From now on, you can be freed from such inefficient work, and single-variable solving will become your right-hand assistant.

Related learning recommendations: excel tutorial

The above is the detailed content of What is Solve in One Variable? Excel single variable solution case analysis. For more information, please follow other related articles on the PHP Chinese website!