BPC MS EXCEL GOAL SEEK Function Examples

BPC MS EXCEL GOAL SEEK Function Examples

Fiancé Modeling by using GOAL SEEK Functions
Our considerable experience ranges from operational models developed for internal use to financial models for transaction structuring and bank negotiations.
• Operational modelling
• Budgeting, planning and forecasting
• Margin analysis
• Inventory management
• Cash management and reporting
• Performance analysis
• Cost and pricing analysis
• Capital structure optimisation
• Working capital
• Capital investments
• Feasibility modelling
• Acquisition analysis
• Project finance models
• PPP tender models
• process modelling (chemical & mining)
• Information management systems
• Debt optimisation models
• JV partnership models
• Contract modelling


Goal seek functions Works
• From the Excel menu bar, click on Tools
• From the drop down menu, click on Goal Seek
• A dialogue box pops up like the one below:

The dialogue box needs a little explaining. "Set cell" is the answer you're looking for, this is the Goal. Set cell needs a formula or function to work with. Our formula is in cell B3, so if your "Set cell" text box does not say B3, click inside it and type B3.
"To Value" is the actual answer you're looking for. With "Set cell", you're just telling Excel where the formula is. With "To Value" you have to tell Excel what answer you're looking for. We wanted an answer of 56 for our formula. So click inside the "To Value" text box and type 56.
"By Changing Cell" is the missing bit. This is the part of the formula that needs to change in order to get the answer you want. In our formula we have an 8 and a 6. Clearly, the 6 is the number that has to go. So the cell that needs to change is B2. So go ahead and enter B2 in the "By Changing Cell" text box. Your dialogue box should now look like this:

Click OK when your dialogue box looks like the one above. Excel will then Set the cell B3 to the Value of 56, and change the figure in cell B2. You'll also get a dialogue box like the one below:

Click OK on the dialogue box. Your new spreadsheet will look like this one:

So Goal Seek has given us the answer we wanted: it is 7 that when times by 8 equals 56.

Increase your profits with Goal Seek
To give you a more practical example of what Goal Seek does, consider this problem. You have a business that generates 25 thousand pounds worth of profit. You currently sell 1000 items at 25 pounds each. You want to increase your profit to 35 thousand pounds. Assume that you're still going to sell 1000 items. By how much does the price of each item have to increase by in order to generate the new profit total?
We'll work it out together using Goal Seek. And then you can have a try yourself with an exercise. First, here's a new spreadsheet for you to download:
When you open the spreadsheet, you'll notice that the Current Sales Figures and the Future Sales Figures are exactly the same. The formula in Cells B4 and E4 are = B2 * B3 and = E2 * E3
We can use Goal Seek to solve our problem. What we want to know is, What should be the new Price Per Item in order to generate Profits of 35 thousand?
• So, from the Excel menu bar, click on Tools
• From the drop-down menu, click Goal Seek
• The Goal Seek dialogue box appears

This time, our formula is in cell E4. So we want to Set the cell to the cell that has our formula. So type E4 into the "Set cell" text box.
The "To Value" text box will hold our new Profits. The Goal we are aiming for is 35 thousand. So type in 35000 in the "To value" text box.
The cell we want to change is the Price Per item figure. So in the "By changing cell" text box, type in E3.
Click OK when you're done. Excel will give you this dialogue box:

The dialogue box is telling you that Goal Seek has found a solution. Click OK. Your spreadsheet will already have changed. The new Future Sales Figures will be displayed. Your spreadsheet should look something like the one below:

Goal Seek has given us the answer of 35 pounds. So the cost of each item has to increase by 10 pounds if we want a profit of 35 thousand.




Example 2



Excel's Goal Seek feature allows you to alter the data used in a formula in order to find out what the results will be.
The different results can then be compared to find out which one best suits your requirements.


Example Using Excel's Goal Seek feature:
For help with this example, see the image above.
• This example first uses the PMT Function to calculate the monthly payments for a loan.
• It then uses Goal Seek to lower the monthly payment by altering the loan period.
1. Enter the following data into the cells indicated:
Cell - Data

D1 - Loan Repayment
D2 - Rate:
D3 - # of Payments:
D4 - Principal:
D5 - Payment:
1. E2 - 6%
E3 - 60
E4 - $225,000
1. Click on cell E5 and type the following formula:

= pmt ( e2 / 12 , e3 , -e4 )

and press the ENTER key on the keyboard
2. The value $4,349.88 should appear in cell E5. This is the current monthly payment for the loan.
Altering the monthly payment using Goal Seek
1. Click on the Data tab.
2. Choose What-If Analysis from the ribbon to open the drop down list.
3. Click on Goal Seek... in the list to bring up the Goal Seek dialog box.
4. In the dialog box, click on the Set cell: line.
5. Click on cell E5 in the spreadsheet since we are interested in altering the monthly payments for this loan.
6. In the dialog box, click on the T0 value: line.
7. Type 3000 since we would like to lower the monthly payment to $3000.00.
8. In the dialog box, click on the By changing cell: line.
9. Click on cell E3 in the spreadsheet since we want to change the monthly payment by altering the total number of payments to be made.
10. Click OK.
11. At this point, Goal Seek should begin searching for a solution. If it finds one, the Goal Seek dialog box will inform you that a solution has been found.
12. In this case, the solution is to change the number of payments in cell E3 to 95.25.
13. To accept this solution, click OK in the Goal Seek dialog box and Goal Seek will alter the data in cell E3.
14. To find a different solution, click Cancel in the Goal Seek dialog box. Goal Seek returns the value in cell E3 to 60. You are now ready to run Goal Seek again.