| There are many analyses you will undertake that will | | | | the day for each of the data points. For example, |
| require the need to calculate a weighted average | | | | the weight for the $4.00 price would be 10.7% (1,500 |
| instead of a simple average, and there are a few | | | | divided by the total shares traded that day of 14,000 |
| different methods by which to do so. This article will | | | | shares). Once you have the weightings completed, |
| first define the concept and then illustrate the | | | | you can do the contribution column, which would be |
| methods used to calculate the correct answer. Once | | | | the weighting value multiplied by the actual stock |
| you understand the shortcut that Excel provides in | | | | price. In the $4.00 example, you would take the |
| determining the result, you will likely not return to the | | | | 10.7% and multiply by $4.00, yielding $0.43, which is |
| longer, more cumbersome approach. | | | | the contribution to the VWAP by the $4.00 share |
| First, what is a weighted average ("WAVG")? The | | | | price. Summing the contribution column yields the |
| WAVG uses a parameter, like market capitalization or | | | | VWAP, which, in this case, is $4.73, higher than the |
| shares traded, to modify a simple average calculation. | | | | simple average of $4.40. The result would indicate |
| As an example, let's assume that you have five data | | | | that the actual value of this stock is closer to $4.73 |
| points that are stock prices: $3.00, $4.00, $4.50, $5.00 | | | | than $4.40. |
| and $5.50. If these are the closing prices over the last | | | | To avoid the need for two additional columns, Excel |
| five trading days, the average price over this period | | | | provides the SUMPRODUCT function. The form of |
| would be $4.40. But, if we assume that these prices | | | | this function is |
| were intraday prices, we could say that the average | | | | =SUMPRODUCT(array1,[array2],[array3],etc). This |
| price for the day was $4.40. This is misleading, | | | | formula takes an array (row or column) and multiplies |
| however, as it does not consider the volume of | | | | it by one or more other arrays of the same size. To |
| shares at each trade. The volume at the time of | | | | calculate the VWAP from our prior example, all you |
| each trade provides a clearer picture of what the | | | | would need is =SUMPRODUCT(prices,volume)/total |
| market thinks of valuation. The calculation used to | | | | volume. This would result in the same answer as |
| weight these trades is the volume weighted average | | | | above, $4.73. The answer is calculated without the |
| price ("VWAP") and is a common practice in private | | | | additional columns. When you look at the formula and |
| capital transactions involving equity or equity-linked | | | | break it down, you can see that it takes the form of |
| securities, like convertible debt (note that VWAP and | | | | (A x B)/C, which can be rewritten as A x (B/C). The |
| WAVG are calculated in the same fashion, and I use | | | | B/C component is the same as the "weighting" |
| VWAP to illustrate a real life application). | | | | column described in the step-by-step approach, with |
| In keeping with the prior example, let us assume that | | | | A being the stock price used in determining the |
| we have volume information associated with the | | | | "contribution" column in the former method. |
| prices as follows: 1,000 shares, 1,500 shares, 1,000 | | | | Finally, you can also utilize an array method to |
| shares, 10,000 shares and 500 shares. You can see | | | | calculate the VWAP using the form of |
| from the data that many more shares traded at | | | | ={SUM((prices)*(volume))/total volume}, which is |
| $5.00, or 71.4% of the total daily volume to be exact. | | | | entered using ctrl+shift+enter. You can clearly see |
| How does one incorporate that information to | | | | that 1) this is not much of a time saver and 2) |
| determine the VWAP? There are two approaches | | | | SUMPRODUCT is similar to this form. I only show this |
| for doing the calculation: the step-by-step method or | | | | to illustrate that there are several ways to |
| the SUMPRODUCT method. | | | | accomplish the WAVG calculation, and depending |
| Assume that in the first column of your spreadsheet | | | | upon space, modeling skill or other personal factors, |
| contains the stock prices and the next column | | | | you can use whichever works for you. I recommend |
| contains the shares traded at each price. The | | | | minimization of superfluous data and err towards |
| step-by-step approach would dictate that you create | | | | using the functionality contained in the Excel formulas, |
| two new columns: weighting and contribution. The | | | | and would use SUMPRODUCT over the lengthier |
| weighting column would contain the result of that | | | | method. |
| day's shares divided by the total shares traded for | | | | |