Quick Links
Financial planning and analysis often involves working backwards through the data. This is whereExcel’s Goal Seek featurecomes 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.
1Cost 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 theDatatab, selectWhat-if Analysisand thenGoal Seek. A dialog box will pop up asking you to enter three parameters.
Once you enter the parameters, click onOKand 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, settingTo valueas 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 theBy changing cellparameter.
2Net 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 asSet cell, enter target NPV (say $1,000,000) in theTo valuefield, and enter the cell number containing the selling price in theBy changing cellfield. 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.
3Ratio 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, settingSet cellas the cell containing quick ratio, keepingTo valueas 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.
4Budgeting
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. TheSet cellhere will be the one containing commission expense,To valuewill be 50,000, andBy changing cellwill be the cell containing the commission percentage.
Once you clickOK, Goal Seek will calculate a commission percentage for you, reducing your budgeted expense to $50,000.
5Loan 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, firstuse the PMT function to calculate payments of a loan, assuming any amount as principal. Then, use Goal Seek with the following parameters:
6Retirement 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:
Thencalculate future value in Excel using the FV function:
This gives you the future value of your savings at your retirement age. Now use PMT to find the amount of monthly withdrawals.
Now that you have a basic calculation, you can use Goal Seek.
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 andother financial functions available in Excel, your routine financial analysis tasks can become much simpler and more efficient. Other than planning and analysis, you can alsouse Excel to prepare your financial statements.
