Goal Seek helps you to find a specific target from a given data. Suppose you are required to adjust either cost or price or production unit to make a certain amount of profit. In this situation, you can use Goal Seek to find out the optimum level of input to meet your target. Here you must know that the Goal Seek can only work with a single variable.
Note: This tutorial is prepared on Excel 2013. Excel 2007 and 2010 will be much similar to version 2013.
Example 1
Let's start by a simple example. Go to Excel > Open a Blank Worksheet > Input the data as shown below:
The red figures are negative values.
- Input the figures in Sales for Product A, B and C.
- Enter Cost of Goods Sold in the next row.
- Deduct 3rd row from 2nd row to find out the Gross Profit. (i.e. =B2-B3)
- Enter the other expenses in the 5th row.
- Deduct expenses from Gross profit to find Net Profit. (i.e. =B4-B5)
Important: you must find out the Gross Profit, Net Profit through formula. Otherwise, the Goal Seek function may not work.
At the 6th row, we get the Net Profit for Product A, B and C as 250000, 180000 and 195000 respectively.
Problems & Solutions
In this settings, how can we increase the Net Profit of Product C from 195000 to 200000?
Ans: You can either increase sales, or decrease COGS and other expenses. But you have only one choice. Choose any of the three variables what you are supposed to change. Suppose, you have to change COGS. That means, cell D3. Use Goal Seek as follows:
- Select the D6 cell. Move to the DATA tab > What-If Analysis > Choose Goal Seek.
- Make sure that the Set Cell box contains D6.
- Type 200000 in To Value box.
- Click on the By changing cell box and choose D3 from the worksheet. Then it will be automatically converted to $D$3.
- Hit OK > You will get the following result -
This example is easy to calculate. You can find the result through manual process. Now I would like to move to the more complex example.
Example 2
Go to Excel > Create a new worksheet. Input the data as shown below:
- Input the price of finished goods in B1.
- Per unit variable cost in B2.
- Enter the total number of units produced in B3.
- Get total variable cost by multiplying variable cost and total unit (=B3*B2).
- Enter the fixed cost as given.
- Multiply price with total number of units produced to find out Revenue (=B3*B1).
- Deduct fixed cost and variable cost from revenue to determine profit (=B6-B5-B4).
In the above example, the profit is 3000. How to make it 5000?
Well, you may have several choice as:
- Changing the price
- Reducing the variable cost
- Increasing the production unit etc.
Suppose, you have no way to reduce the cost or increase the production. In that situation, you can only adjust the price. Let's raise it through Goal Seek . . .
- Click on the cell B1
- Move to Data > What-It Analysis > Goal Seek
- Set Cell B1
- To Value 5000
- By Changing Cell, choose B1 ($B$1)
- Hit OK > Finally Get the following result -
Within wasting much time, you can calculate the optimum product price to earn your target profit. You just raise the price by .40 to increase the profit from 3000 to 5000. Similarly, you can also adjust variable cost or production unit. But you can't work with more than one variable at a time.
Project File
I've uploaded the project file in Google Drive. You can download this if necessary. The project file is saved as .xlsx format. You must need at least Excel 2007 or above to open this file.
There are two sheets - Sheet 1 and Sheet 2. Download the Goal Seek tutorial . . .
Project File
I've uploaded the project file in Google Drive. You can download this if necessary. The project file is saved as .xlsx format. You must need at least Excel 2007 or above to open this file.
There are two sheets - Sheet 1 and Sheet 2. Download the Goal Seek tutorial . . .
Comments
Post a Comment