How Excel’s Goal Seek Makes Any Financial Analysis a Breeze

Financial planning and analysis often involves working backwards through the data. This is where Excel’s Goal Seek feature comes in handy.

Using Goal Seek, you can avoid reverse calculations and let Excel find the inputs that result in your desired output. While there are dozens of financial planning tasks where you can use Goal Seek, I find it particularly helpful for these common analyses.

1 Cost Volume Profit Analysis

Cost Volume Profit (CVP) or break-even analysis is a technique to analyze the effect of changes in volume or cost on a company’s profit. With Excel’s Goal Seek feature, you can carry out CVP analysis much more efficiently.

To explain with a simple example, say you want to calculate how many units you should sell to achieve break-even. Begin with creating a simple statement that shows the selling price, variable cost, and fixed cost. For now, you can enter an assumed figure for units and calculate contribution margin and profit.

Then, from the Data tab, select What-if Analysis and then Goal Seek. A dialog box will pop up asking you to enter three parameters.

• Set cell: Here, enter the cell address that contains the output for which you have a target value in mind. In our case, it’ll be the cell that contains your profit/loss.
• To value: Enter the target value. For breakeven, this should be set as 0.
• By changing cell: The input that you want to vary to reach your target output. As we’re finding units to reach breakeven, this will be the cell containing the number of units.

Once you enter the parameters, click on OK and Excel will change values to tell you how many units you must sell to break even.

Similarly, if you want to find how many units you should sell to earn, say $5000, use the Goal Seek feature again, setting To value as 5000 instead of 0. Alternatively, if you want to find the selling price or variable cost to reach the target profit (keeping units constant), you can refer to the selling price/cost cell in the By changing cell parameter.

2 Net Present Value Analysis

Net Present Value (NPV) analysis is an investment appraisal technique used to decide whether to undertake a specific project or business decision. When using NPV analysis, Goal Seek can be useful for a number of calculations.

For instance, you can use it to find the price you should sell your product for to achieve a certain NPV from a project.

To do so, build your NPV model, using all expected costs and an assumed selling price. This will give you an NPV based on the selling price you assumed. Now, using Goal Seek, select the cell containing NPV as Set cell, enter target NPV (say $1,000,000) in the To value field, and enter the cell number containing the selling price in the By changing cell field. Voilà! Excel will tell you how much you should charge to achieve the target NPV.

Other than selling price, you can vary other inputs as well, such as units or cost of capital/discount rate.

3 Ratio Analysis

You have borrowed from a bank and the terms of the loan require you to maintain an interest cover of 2.5, a quick ratio of 1, and a debt-to-equity ratio of 0.5. Here, Goal Seek can help you find out how much your interest expense, cash, or debt can change without breaching the covenants.

To illustrate, import your financial statements into Excel and calculate the quick ratio. Then, use Goal Seek, setting Set cell as the cell containing quick ratio, keeping To value as 1 (or your target ratio), and ask Excel to adjust the ratio by changing trade payables. Goal Seek will let you know how much your trade payable can increase up to while keeping the ratio within the limit.

4 Budgeting

While making a budget for the next year, you realize that the expected commission expense is quite high. To reduce the expense to a target amount (say $50,000), you can use Goal Seek and find the commission percentage you should offer to sales agents.

Based on your existing commission percentage and sales revenue, calculate the estimated expense. The Set cell here will be the one containing commission expense, To value will be 50,000, and By changing cell will be the cell containing the commission percentage.

Once you click OK, Goal Seek will calculate a commission percentage for you, reducing your budgeted expense to $50,000.

5 Loan Repayment

Goal Seek can come in handy for your personal finances too. Let’s say you want to take a loan but can only pay monthly installments of $1500. Using the PMT function and Goal Seek, you can find out how much you can borrow at a fixed interest rate.

To do so, first use the PMT function to calculate payments of a loan, assuming any amount as principal. Then, use Goal Seek with the following parameters:

• Set cell as the cell containing monthly installment
• To value as the amount of monthly installment you can pay
• By changing cell as the cell with the loan amount

6 Retirement Planning

Similarly, if you’re planning your retirement and want to withdraw $7000 each month for 30 years after retirement, you can use Goal Seek to find the amount of monthly contributions you should make before retirement.

Enter the following information in Excel:

• An assumed monthly contribution figure
• Months till retirement (i.e., the number of times you’ll contribute)
• Number of withdrawals (i.e., months after retirement you want to withdraw money)

Then calculate future value in Excel using the FV function:

=FV(interest rate/12,months till retirement,assumed monthly contribution) 

This gives you the future value of your savings at your retirement age. Now use PMT to find the amount of monthly withdrawals.

=PMT(interest rate/12,number of monthly withdrawals post-retirement,-future value of savings) 

Now that you have a basic calculation, you can use Goal Seek.

• In Set cell field, select the cell that contains your monthly withdrawal amount.
• The To value will be $7000 (or the amount you wish to withdraw monthly).
• The By changing cell field should mention the cell containing your monthly contribution.

Within seconds, Excel will tell you how much you should set aside for your retirement each month now to withdraw $7000 post-retirement.

Thanks to Goal Seek and other financial functions available in Excel, your routine financial analysis tasks can become much simpler and more efficient. Other than planning and analysis, you can also use Excel to prepare your financial statements.