Ok, Im using Excel 2007, and I’ve had this problem once before in Excel 2003 but don’t remember how I solved it or what the issue was.
In my DCF, I have data tables, obviously. When I have everything plugged in, and press and hold F9, my numbers flicker. The enterprise value changes, but as soon as I take my finger off F9, its back to normal. Its like the iterations are causing it to calculate different TEVs, but the TEV is in a lot of times VERY big differences between flickers, maybe 10-15%.
I figure its most likely because I’m backing into an existing value by taking the (DCF value) – (Equity Investment) = Existing equity.
So the DCF results are circ’d to the capital structure, and the capital structure is circ’d to WACC, and the WACC is circ’d to the DCF. Right, makes sense? Atleast its always give me the same value.
The problem is, some other formulas are not working on the DCF sheet now for some reason. For example, I put in 80% in a cell, the cell below that I put in =1-S19 (which is the 80% cell)…. That should equal 20%... 1-80% = 20%. Instead it says 99%. What the fuck is up with this thing? Anyone know?
edit:
SON OF A BITCH. Now that formula is working. But Id still like to know if anyone knows why my numbers are flickering and fluctuating so much. I have another group of cells where it says what portion of TEV is operating period value and what portion is Terminal value. That is then summed to bring it to 100%, when it flickers, that 100% is sometimes above and sometimes below 100%... is this just a function of the order in which excel calculates cells?
Actually I have my Discount rate hardcoded into my DCF. SO there is no circ between DCF and the rest of the model. Nothing that feeds DCF is being impacted by the DCF's concluded value.
WHat a clusterfuck.
I had this issue with one of the models I built, but the problem was with convertible (diluted) securities, which was based of expected stock price, which then affects stock price if you use any type of equity multiplier. If you have some sort of system like this, which it sounds like, it will keep recalculating value, its a circular reference you want to try to avoid. I avoided the above by using BV per share to calculate value, since it does not rely on diluted securities.
I thought it was all because of the circ.
But just now I made a brand new worksheet, calculated the DCF ALL OVER AGAIN. So this new worksheet is in no way any part of the rest of the model, not feeding into anything, not circ'd.
No links to WACC, no link to CompCo, no link to operating model/income statement, all the numbers are hardcoded in.
I put in the data tables, and the exit multiple has the same value for all 4 discount rates in the data tables. I cant even fucking give this to someone else to deal with.
maybe something with iterations? how high are they set to? 100, 1000?
Invite People Here: //www.wallstreetoasis.com/invite $9 WSO Guides: //www.wallstreetoasis.com/wsoguides Elite GMAT Tutors: //www.wallstreetoasis.com/page/one-on-one-gmat-prep
100, max change .0001
I often have this problem on excel 2003 also with the interest expense. Interest expense links to debt which links to rate which fucks up pro-formas...
Try hilighting the cells that are fucking up, then paste them over to another sheet (paste all, not as values), then delete original cells, then push F9, then paste the cells that were once fucking up.
Sorry if that is confusing.
Vel ipsam et suscipit aliquam aperiam sequi repudiandae. Ut aliquid voluptatem expedita necessitatibus aspernatur et. Totam nihil assumenda iure similique doloremque. Nostrum velit voluptatem recusandae beatae. In ullam deserunt mollitia culpa et aliquam et dolor. Nihil fuga fuga eum dolorum aut. Praesentium facere laudantium fuga cupiditate placeat necessitatibus cum.
Ut ipsum sit est deleniti voluptatum et aliquam. Exercitationem nihil ipsa voluptas placeat nulla animi iste. Velit assumenda nam doloremque animi.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...