GOAL SEEK needs here a formula for the calculation. Or was your issue only regarding the designation "Formula cell must contain a formula" or an in principle issue regarding the use of GOAL SEEK? If it was only regarding the designation then I would agree that in this case it would be better and clear to name it "Formula cell must contain a ...
But there is an even easier way to find the answer. That is to use Goal Seek, an Excel function provided by Excel. Here is how to use Goal Seek to find the breakeven point. From the menu, select Tools -> Goal Seek. Fill in the boxes as shown below: Set Cell is reference to the net profit cell D15.
Make sure the input has no formula — not even something like =5. If you are trying to make a goal seek for IRR, it is much better to use the NPV at the target IRR as the target cell with the formula. If you want to make a goal seek with dynamic goal seeks you can create a user defined function — see the excel section..
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code. Figure 4: How the table looks after you’ve entered the formula. To actually see how this formula works, we shall look at a firm that produces construction materials. The fixed cost is $8, 000, unit cost is $65, price is $180 each. Note that when using the Goal seek process, demand is set to be 1. We need to enter this data in our table. Dec 11, 2019 · The formula in cell D24 (the target cell) is =B24-C24 Now use Goal Seek... to vary A24 to make the target cell, D24, equal to zero. The result is shown in Figure 8-32. Goal Seek doesn't seem to allow you to set the goal as a cell reference. However, you can use Solver to achieve your objective. Create another cell, say X1, with the formula =YourTargetCell-TargetValueCellReference, where those references refer to the two cells you would have used in goal seek. Leave this cell selected. Dec 11, 2019 · The formula in cell D24 (the target cell) is =B24-C24 Now use Goal Seek... to vary A24 to make the target cell, D24, equal to zero. The result is shown in Figure 8-32.