Excel Zoom

Recently I found myself deciding if I should sell some stock in order to provide a little extra cash to my bank account.  I purchased shares of the company’s stock four times in various quantities, and at varying prices.  Due to the downturn in the stock market recently the prices dropped steadily, so I was able to purchase some of the shares at a low price.  The company has done a little better in recent months and has produced a gain for some of the shares I bought at the lower price.

The question I want to solve is: if I sell all the shares that have produced a gain, how many of the shares should I sell at a loss in order to offset the gains, and the resulting taxes?

To do this, I set up my spreadsheet like the one below:

Note: these are not the actual amounts I used in my own personal situation.  They are only provided for illustration purposes.

Download the example file here.

In cell G8 (labeled “Total Losses” in cell F8), I have the following formula: =(G10*C3)-(G10*E3), which I want to equal the total gains amount, of $1,936.10.  Right now it is showing a zero value because I haven’t determined how many shares need to be sold at a loss to equal the gains.

To do this, select cell G8, then click Tools | Goal Seek.  In the box that pops up, enter the following:

• Set cell: Keep as G8.  If it isn’t G8, enter G8 here.
• To value: Enter 1,936.10, as this is the value we wish to set the losses to.
• By changing cell: Enter cell G9, as you want to determine how many shares need to be sold in order to break even.
• Click OK

The result will tell you that you should sell 43.54 shares in order to break even.

For more information on Excel’s Goal Seek feature, take a look at the post Goal Seek to Solve a Problem.

Microsoft Excel is jam packed with cool useful features that tend to go unused by most users, simply because they don’t know how to use them, or even that they exist.  One of these features is Excel’s Goal Seek tool.

First, some background on what it does.  Goal Seek is essentially the answer to every middle school aged kid’s math test word problem.  It is best used when you know the answer to the problem you’re trying to solve, but don’t know all the inputs.

For example, a simple word problem might be “You have 432 sheets of paper to give out to a class of 36 students.  How many sheets of paper does each student get.”  Well you know that 36 multiplied by something will give you 432, but you don’t know what that number is.  You could divide 432 by 36 to get the answer, but let’s assume that isn’t possible right now.  With Goal Seek, you need several things:

• An answer expressed as a formula, in this case 432 is equal to 36 times something.  Don’t worry, the result of the formula doesn’t have to equal 432 yet.  That’s the magic of Goal Seek.
• You need one part of the equation, in this case 36.

To start, type the following into your spreadsheet in the cells indicated.

Next, open Goal Seek (Tools | Goal Seek, or Data tab on the Excel 2007 ribbon | What If Analysis | Goal Seek).

You are now going to be asked for three things. 

1. Set Cell: this is the reference to the cell that contains your formula, in which you want Excel to produce your answer of 432.  In our example it should be A3.  Note: the Set Cell MUST contain a formula or function.
2. To Value: this is the value you want in your Set Cell (A3).  The value here should be 432.
3. By Changing Cell: Since we know that the formula in cell A3 is correct, and the value of 36 in cell A1 is correct, we want Excel to produce the answer of 432 by changing cell A2.  Type A2 in this box.  Note: the Changing Cell MUST contain a value.
4. Click OK.

After clicking OK, Excel will attempt to find a solution to the equation.  Once it does, it will enter the missing number in cell A2 and your formula should now equal 432.

Obviously this is a basic example, and doesn’t use Goal Seek’s capabilities to their fullest extent.  Let’s take another example that looks at a company’s sales by product to get their desired level of revenue.

XYZ Company makes four different products, Product A, Product B, Product C, and Product D.  The company can produce 1,000, 750, 500, and 100 of each product respectively.  The products also sell for $15, $20, $35, and $50 respectively.  Their maximum revenue for products produced that month, therefore is as follows:

Now say the company feels that their underdog, Product D, could be producing much more.  They also want their revenues to be 60,000.  How many more of Product D will they need to produce?

To figure this out, run Goal Seek just like before, except this time, the “Set Cell” is cell D6, “To Value” is 60,000, and the “By Changing Cell” is cell B5.  After clicking OK, you’ll notice that Excel has changed the value in cell B5 to 250, indicating that you will need to increase production of Product D to 250 units from 100 in order to have revenues of $60,000, assuming everything else remains the same.