Re: DCF modeling in an M&A situation
Hi Leon, 1. In listed co's 2016 financials, the investment in Company A will most likely be accounted for using the equity method. Strip out company A's earnings to project the list co on a standalone basis. Project both companies separately and combine as part of your consolidation. 2. For yo... Hi Leon,

1. In listed co's 2016 financials, the investment in Company A will most likely be accounted for using the equity method. Strip out company A's earnings to project the list co on a standalone basis. Project both companies separately and combine as part of your consolidation.

2. For your DCF, you are only concerned on a going forward basis (consider that no enterprise value is attributable to past earnings). Your pro forma period will include 100% of the earnings from both the listed co and Company A.

3. Project the two companies separately for your pro-forma period. Be sure to account for any synergies/incremental D&A you expect as a result of the transaction. This should not be substantially more work than projecting on a combined basis. Consider that company A will not magically assume the listed co's growth rate, margins, etc. You would need to account for these differences no matter how you project the combined entity. Read More

Re: WLSRegression using Excel VBA
You could actually simplify this by removing (try commenting first, of course) every line after your declaration of b. Then, your last line will just be: WLSregression = b Or you can just skip the need for b completely and just have: WLSregression = Application.WorksheetFunction.MMult(XwXin... You could actually simplify this by removing (try commenting first, of course) every line after your declaration of b. Then, your last line will just be:

WLSregression = b

Or you can just skip the need for b completely and just have:

WLSregression = Application.WorksheetFunction.MMult(XwXinv, Xwy)
(By the way, you could just use Application.MMult here.)

If, for some reason, you needed the k and output and all that, then you have to fix the indexing. Basically, VBA arrays start with index 0 by default. So the first element in output would be output(0). However, note that you are initializing bcnt = 1 in your loop. Thus, the first iteration will be setting output(1) to b(1, 1). The second iteration will work on output(2). But what about index(0)?

In essence, you skipped index(0) and that's why an empty 0 is sitting there in the first spot. You could fix this by simply changing that line to:

output(bcnt - 1) = b(bcnt, 1)

Now it works as expected! Read More

Re: WLSRegression using Excel VBA
Hi Brendan, After looking through your code a bit more, it looks like it actually does work as intended, as long as you first select three cells going across, paste in the formula, and hit Ctrl + Shift + Enter to make it an array formula. The formula I tested with is: =TRANSPOSE(WLSregression... Hi Brendan,

After looking through your code a bit more, it looks like it actually does work as intended, as long as you first select three cells going across, paste in the formula, and hit Ctrl + Shift + Enter to make it an array formula.

The formula I tested with is:
=TRANSPOSE(WLSregression(B2:B17,C2:E17,A2:A17))

(I entered the data you supplied in A2:E17)

Let me know if trying those steps works for you.
Read More

Re: WLSRegression using Excel VBA
Let's start with that random data you have there. We could do a few useful things, like generate a covariance matrix, etc.

What's the desired behavior of the WLSregression function? Should it calculate and then return the three calculated coefficients, or something else?

Re: VLOOK and MATCH
Hi Peter, In step 3, the MATCH function will always only point you to one value/position. In other words, if on those three trading dates, the stock all closed at exactly $50.00, then your result would simply show the first date in the search. So in this case, the latest dates are at the top, des... Hi Peter,

In step 3, the MATCH function will always only point you to one value/position. In other words, if on those three trading dates, the stock all closed at exactly $50.00, then your result would simply show the first date in the search. So in this case, the latest dates are at the top, descending into older dates at the bottom. Therefore, Excel will start at the top, and go down the list, looking for the first instance of $50.00 that it finds.

(Note that this is assuming a match_type parameter of 0; if you were to specify it to -1, then it would find the smallest value greater than or equal to $50.00, which is not really useful in this example.)

If you wanted to get *all* the different dates for a particular closing price (e.g. given the price $50.00, you wish to identify all the dates on which it closed at that price), you would have to take a different approach. For example, you could use an array formula that could get quite convoluted. You might also use Excel's built-in filter tools. Read More