Skip to main content

Tutorial on Goal Seek (Project File Included)

Using Goal Seek in Excel

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:

Excel Rows and Columns

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:

Excel Worksheet

  • Select the D6 cell. Move to the DATA tab > What-If Analysis > Choose Goal Seek.

Input Data on 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 - 

Goal Seek 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:

Date Input for Goal Seek

  • 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 . . . 

Goal Seek Example

  • 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 - 

Goal Seek Output

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 . . . 

Stay with Marks PC Solution to get more interesting IT topics!


Popular posts from this blog

GTA San Andreas Cheat Code (PDF)

After Vice City, San Andreas is the most popular game released by Grand Theft Auto (GTA). Usually GTA releases role playing game. San Andreas is such a game where you can act like a person. You can do everything. You have to take part in missions and complete them.  To make your missions easier, there are lots of cheat codes. If you can properly apply these codes, the tough parts of the game will be easier. Cheat codes are somewhat like keyboard shortcuts of computer programs.  I've shared 63 cheat codes below. There were more cheat codes in my collection, but I've omitted the less important cheats. Effects or result of the cheats are on the left side, and the codes are on the right side.  You can copy the following codes in your PC. And for your convenience, I've also uploaded a PDF file of these codes. You will find the download link at the bottom.  Effect Cheat Code Adrenaline Mode MUNASEF All Cars Explode ALLCARSGOB

Rules for Bengali Typing in PDF (Bijoy Layout)

Bijoy is the most popular layout for Bangla typing. The layout was developed by Mostafa Jabbar. He is the pioneer of Bangla typing in computer. More than 90% people use his Bijoy layout for Bangla typing. Today, I'm gonna share a PDF file with you that contains the Bijoy Keyboard layout with English fonts! If your keyboard doesn't have bengali fonts then you can print this PDF file to know where the bengali fonts are located in your keyboard. Additionally this file contains all the necessary rules and tips which are essential for Bangla Typing.  Now there are many other software available for Bangla Typing Layout. Such as Avro Easy, Unicode, Unibijoy etc. And there rules are also somewhat different from Bijoy. And some of their layouts follow the same rules like Bijoy. Hopefully this PDF file will support most of the rules when a layout is similar to Bijoy Layout.  Click to enlarge the image Download the PDF file from the button below. Follow

Download Puzzle Flash Game - Save Them

Hey Flash Game Lovers! Today you're getting an amazing puzzle game - Save Them! It's a flash game and the download size is only 382 KB. In this game, you have to move 3 missionaries and 3 cannibals from one side to another through a boat. In any side, if the number of cannibals is more than the number of missionaries, then cannibals will eat the missionaries!  Download Save Them Download the game from the above link. It's a zipped file. Download size is only 382 KB! After downloading the game, you have to unzip it first. Then double click on the SaveThem to start the game.  How to Play? After starting the game, you will get the instruction. You have to make sure that in no side, the number of cannibals is not more than the number of missionaries. This is your only challenge. You have to take them from left side to the right side of the lake through a boat.  Press on a character to jump to the boat. Thus you can take any two of them a