EXERCISES: Goal seeking a solution in Excel

 Goalseek Example 1- Question

To follow along go to 15.6

Use Goalseek to find solutions as explained below

  • Product 1: Net Profit % on retail price of 12.5% given that only Costs can change
  • Product 2: Net Profit % on Selling Price of 25% given that the retail price can change
  • Product 3: What must the Retail Price be if the total of Discount and Costs can only be 750 000

 Goalseek Example 1- Solution

 

Goalseek Example 2- Question

To follow along go to 15.7

Without creating a circular reference we need to provide a bonus based on profit after tax of 10%
of the profit after tax

Goalseek Example 2- Solution

 

Goalseek Example 3- Question

To follow along go to 15.8

We need to achieve a total Profit after tax after 5 years of 500 000. Using Goalseek try and achieve this with the following constraints:

  • We can change the inflation rate on the selling price BUT the inflation on cost price must always be 1% more than the selling price inflation
  • The maximum we can allow the selling price inflation to go to is 8%
  • We can’t change year 1 and 2’s units sold as we are quiet certain about that, but we can increase the sales in Year 3 to 5. The pattern will stay the same so you need to increase it as a curve i.e. all 3 years increase by 10% for example.
  • The net result of the above changes must give a Total Profit After tax after 5 year (Cell L32) of 500 000

Goalseek Example 3- Solution

Back to: Excel What If Analysis > Goalseeking a solution