Excel features are such that they allow you to solve tasks that cannot be performed manually or too difficult. These can be time-consuming tasks with enumerating a huge number of options or calculating large numbers. Knowing the capabilities of Excel, to solve them is not difficult.
Parameter Matching Function
An example of such a laborious and practically unsolvable task is the following. You know what calculation result you need to get, what calculations lead to it, but the starting point is not known - the value of the source data. Sometimes you can take the opposite action, and sometimes such a task can be confusing.
Excel offers a method for solving this problem, which is called the selection of a parameter. The function call is located on the “Data” tab of the “Work with Data” toolbar. In versions starting with MS Excel 2007, “What-if analysis”, menu item “Parameter selection”.
Attributes are set for the function: indicate the cells in which the contents must be changed to achieve the desired result, and the fields in which the selection result is located. Another attribute of the function is the value to be obtained.
As you can see in the picture, the formula allows you to enter only one argument for changing and substituting. This does not always satisfy the user. In case you need to select several values to obtain the desired result, the selection of Excel parameters is not suitable. In this situation, a special add-in is used, which we will dwell on below.
Loan Amount Calculation
One of the most demanded tasks that this module helps to solve is calculating the possible loan amount or bank loan based on monthly payments, term and interest rate. Suppose the interest rate on a loan is 10%, we want to borrow money for 1 year and can pay 7 thousand rubles a month.
In Excel 2007, there is a suitable function for calculating monthly loan payments with known interest and maturity. It is called PMT. Command syntax:
PLT (rate; nper; ps; [bs]; [type]), where:
- Rate - interest on the loan.
- Nper - the number of payments (for an annual loan in the case of a monthly payment, this is 12 times).
- PS - the initial amount.
- BS - future value (if you intend to pay not the entire amount, but only a part of it, it indicates what debt should remain). This is an optional argument; by default it is 0.
- Type - when payment is made - at the beginning of the month or at the end. This parameter does not have to be indicated if it is not filled, it is taken equal to 0, which means payment at the end of the month.
It should be borne in mind that the “Rate” attribute does not indicate annual interest, but monthly, so the interest rate we know should be divided by the number of payments per year - 12.
We will add the necessary data to the Excel 2007 sheet. As an initial amount, we’ll register the conditional 1 000 000 rubles for now and will find the real amount. Call the dialog box for selecting a parameter. The starting point for finding is the amount of the monthly payment. The PMT formula returns negative data, so we enter a number with a minus sign: - 7,000 rubles in the "Value" field. We must receive this amount in the cell with payment, changing the information in the field with the loan.
We write all this in the window and start the selection of the Excel parameter. As a result, the function calculated which loan we can afford - 79,621.56 rubles.
Interest rate determination
We now consider the inverse problem. The Bank grants a loan of 100 thousand rubles for 2 years and wants to receive an income of 10 thousand rubles. What is the minimum interest rate you need to set to make such a profit?
To the information already available on the sheet, add the line “Profit”. It is calculated by the formula:
Profit = | Payment | * Term-Amount
We set a period of 24 months. Pay attention to the “Bet” field. The numerical value must be expressed as a percentage. To do this, select the number format “Percent” in Excel: tab “Home” - toolbar “Number” - button with the image of percentage.
We call the selection function and set its arguments. The expected result will be recorded in the “Profit” field by changing the value in the “Rate” cell and amount to 10,000 rubles. After starting the program shows the required percentage equal to 9.32354423334073%.
Selection of several parameters to find the best result
As mentioned above, the parameter selection function in Excel does not always satisfy the requirements of the task. If you need to pick up a few arguments, an add-in called “Search for a Solution” is used. With its help, you can get the optimal result by selecting several parameters.
Check if it is available for use: tab “Data”, toolbar “Analysis”. If the program does not have such a panel or does not have the required command on it, activate it. Go to Excel options (Microsoft Office button in Excel 2007, the File menu in versions 2010 and above) and find the “Add-ons” item. Go to the management of add-ons and select the check box for the "Solution Search" element. Now the function is activated.
Transport task
The classic application of the method is to solve a transportation problem. Suppose a company stores goods in several warehouses and delivers them to several stores. The question is, from which warehouses to deliver products to which stores, so that transportation costs are minimal.
We set the input data. To do this, we will build tables with information on delivery costs, the volume of goods in each warehouse and the required number of goods for each outlet.
We will select such supply values so that the conditions are met:
1) Total costs were minimal.
2) The total supply of goods to outlets met the requirements.
3) The total export of products from warehouses did not exceed the available stocks.
4) The number of units of production must be integer and non-negative.
Result of finding a solution.
Other methods of data analysis
In addition to the options listed above, there are other methods of data analysis. They are located in the menu item “What-if analysis”. These are the “Script Manager” and the “Data Table”.
The script manager allows you to select possible scenarios of events using a selection of values in a range of cells. With its help, a forecast is made of possible outcomes of the current process, for example, the result of a company’s work for a period. After entering various options, the result is displayed in the report according to the scenario, according to which it is possible to analyze the parameters.
A similar function is performed by the “Data Table” with the only difference being that all totals are not displayed in a separate report, but are contained on the same sheet. This makes analysis easier. But tables process only two parameters, therefore, in case of more input data, they use scripts.
Above, we dwell in detail on the Excel function "Parameter Selection" and practical examples of its use. Knowing this opportunity, you can easily solve problems with its help.