Re: Revenue Sensitivity
Use the CHOOSE function instead of nested IF statements!

Take a look at: http://www.wstselfstudy.com/youtube.html
and then scroll down to video #19.1: Technical Applications - Excel and More

RE: LBO enhanced model question
You have ask yourself, what is the new Balance Sheet that you are buying? Because the old debt never makes it into the deal, you don't want to include it on the actual, historical BS else you won't balance correctly as you noted. If you are only buying the assets, you have to recast the balan... You have ask yourself, what is the new Balance Sheet that you are buying?
Because the old debt never makes it into the deal, you don't want to
include
it on the actual, historical BS else you won't balance correctly as you
noted.

If you are only buying the assets, you have to recast the balance sheet
pre-deal. Use equity as the balancer.

So based on your file, keep assets the way they are, at $26,867. blow out
Interest Payable, Revolver and Short Term & LTD. Those three add up to
$14,643. add that same amount to Common Stock. Then you balance in the
historical year.

Since this is an Asset Deal, there is NO Goodwill to be created. Thus you
can create an additional switch to force Goodwill to be zero. Read More

Re: Enhancements to the core model - Part II tax schedule
Yes, correct. Except that technically DTA and DTL changes are not part of working capital - they usually fall under "non-cash items". Still under CFO category, but below NI, above working capital. Remember, anything that changes on the BS must change somewhere on the CFS.

RE: How do I account for stub periods in an LBO model?
In general, financial models are good for a particular point in time, as these are not tracking models like quarterly Equity Research models but rather, transaction models. To adjust the model from end of 2004 to Q2 2005, you would have to build in a stub period. The stub period would reflect the 2n... In general, financial models are good for a particular point in time, as these are not tracking models like quarterly Equity Research models but rather, transaction models. To adjust the model from end of 2004 to Q2 2005, you would have to build in a stub period. The stub period would reflect the 2nd half of the year's earnings instead of full year. Likewise, the balance sheet would be updated for Q2 2005 numbers instead of year end 2004. Read More

RE: Circular references clarified
Your question can be summarized as follows: What is a circular reference, how do you avoid circular references in a financial model and once you have a circular reference, what does the Iterations option in Excel do? The logic runs as follows: In general, there is NO good reason to have a circu... Your question can be summarized as follows:
What is a circular reference, how do you avoid circular references in a financial model and once you have a circular reference, what does the Iterations option in Excel do?

The logic runs as follows:
In general, there is NO good reason to have a circular reference ("circ")- a logic that relies on itself to calcuate itself. The only legitimate reason to have a circ is in the case of getting a better, more precise financial model based on average debt and cash balances (see our FREE RESOURCES section and click on Circular Reference for more information).

I need to note that for certain complex calculations and situations, there are a handful of reasons why a circ is required. However, aside from those very limited reasons (certain return calculations, certain share repurchase estimates, self-referencing cells for souped up sensitivity analyses, etc), circs should be avoided at almost all costs.

Incorporating a circ for the purposes of calculating average balance is fine; however, once you do so, if a new circ is created (again, circs are bad and should be avoided), you will never tell that the new, bad, circ is there. Hence, my strong recommendation to build a simple choose statement switch that toggles between Beginning Balance (no circ since based off last year's figures) and Average Balance (yes circs, since requires this year's figure to calculate this year's figure). You should update and modify the financial model under the Beginning Balance scenario - this way, if a circ is accidentally created, you will either be warned by Excel or it will show in the Status Bar, indicating you have a circ. Unless you are on Average Balance, you should not have a circ!

Then, when you are ready to finalize your model (or updates finished), then switch the toggle to Average Balance for a more precise calculation. Whenever you toggle back to Beginning Balance, no circs should exist. If there is still a circ, you have a "Fatal Error" in Excel.

Whenever you have a legitimate circular reference, you need to tell Excel what to do - that is, turn on the Iterations option so Excel can calculate the numbers for you until there is no change in any number on the model. For more information on what Iterations in Excel does, see our FREE RESOURCES section and click on Excel Iterations Logic. Read More