Goal Seek is a handy Excel function that helps to determine the required input value needed to achieve a targeted output value.

What is the Goal Seek function?

One of the functions of the What-If-Analysis in Excel, which can be found under the Data tab, is Goal Seek. Essentially, the function uses a trial & error approach for the unknown input cell until it arrives at the value required to reach a targeted output.

Let's look at a simple example to demonstrate the Goal Seek function.

A small city would like to become cleaner in its energy consumption and aims to cover 10,000 of its households with green electricity using a solar park and knows that the average household consumes 4.0 MWh of electricity per year. Additionally, the city's investment board knows they can expect the solar park to run at 1,250 full load hours per year.

Given the above input, the investment board knows how much green electricity the solar park should produce annually - 10,000 households x 4.0 MWh per household = 40,000 MWh.

However, it is unknown how much capacity the park requires to produce the estimated 40,000 MWh per year. This is where the Goal Seek function comes into play.

1. After setting up the case inputs in Excel, go to the Data tab; in the Data Tools group, click What-If Analysis, and then click Goal Seek.
2. In the Set cell box, enter the reference for the cell that contains the formula you want to resolve. In our example, this is the installed capacity of the park (best initial guess of the investment board: 50 MWp) multiplied by the full load hours of 1,250 hours equates to 62,500 MWh of green electricity (slightly more than required)
3. In the To value box, type the formula result you want. In our example, it is 40,000 MWh.
4. In the By changing cell box, enter the reference for the cell that contains the value you want to adjust. In our case, it is the installed capacity of the park.
5. Click Ok.

After the Goal Seek function played its magic, we know that our park should have an installed capacity of 32 MWp to achieve an annual electricity production of 40,000 MWh. If you are confused about the difference between MWp and MWh, check out our article: What's the difference between kW and kWh?

How to build an advanced financial model for renewable energy investments?

Do you want to learn how to build an advanced financial model created explicitly for renewable energy investments? Then check out the Advanced Renewable Energy Financial Modeling course.

Article sources

Goal Seek - Excel for Microsoft 365