Financial planning and analysis often involves working backwards through data. This is where Excel’s Goal Seek feature comes in handy.
By using Goal Seek, you can avoid reverse calculations and let Excel find inputs that result in your desired outputs. While there are dozens of financial planning tasks where you can use Goal Seek, I find it particularly helpful for these common analyses.
Cost Volume Profit Analysis
Cost volume profit (CVP) or break-even analysis is a technique for analyzing the effect of changes in volume or cost on a company’s profit. With Excel’s Goal Seek feature, you can perform CVP analysis more efficiently.
To explain with a simple example, let’s say you want to calculate how many units you must sell to achieve break-even. Start by creating a simple statement that shows the sales price, variable costs, and fixed costs. For now, you can enter an estimated figure for units and calculate the contribution margin and profit.
Then, from the Data tab, choose What-If Analysis and then Goal Seek. A dialog box will pop up asking you to enter three parameters.
Once you have entered the parameters, click OK and Excel will change the values to tell you how many units you must sell to break even.
Similarly, if you want to find out how many units you must sell so that you can earn $5000, use the Goal Seek feature again, setting the To value to 5000 instead of 0. Alternatively, if you want to find out the sales price or variable cost to reach the target profit (keeping units constant), you can refer to the Sales Price/Cost cell by changing the Cell parameters.
Net Present Value Analysis
Net present value (NPV) analysis is an investment appraisal technique used to decide whether to pursue a specific project or business decision. When using NPV analysis, goal seeking can be useful for many calculations.
For example, you can use it to figure out what price you should sell your product for to get a certain NPV from a project.
To do this, build your NPV model using all expected costs and an estimated sales price. This will give you an NPV based on the sales price you estimated. Now, using goal seeking, select the cell containing the NPV as Set Cell, enter the target NPV (say $1,000,000) in the Price field, and enter the cell number containing the sales price in the Change Cell field. Voila! Excel will tell you how much you should charge to achieve the target NPV.
Apart from the selling price, you can also change other inputs, such as units or cost of capital/discount rate.
Ratio Analysis
You have borrowed from the bank and as per the terms of the loan you have 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 violating the covenants.
For example, import your financial statements into Excel and calculate the quick ratio. Then, use Goal Seek, set the To cell as the cell containing the quick ratio, keep the To value as 1 (or your target ratio), and ask Excel to adjust the ratio by changing the trade payables. Goal Seek will tell you how much your trade payables can increase while maintaining the ratio within limits.
Creating a Budget
While creating a budget for the next year, you discover 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 pay to sales agents.
Based on your current commission percentage and sales revenue, calculate the projected expense. Here the Set cell will be the one that contains the commission expense, the To value will be 50,000, and the Cell will change to the cell that contains the commission percentage.
When you click OK, Goal Seek will calculate the commission percentage for you, making your budgeted expense $50,000.