Forum Search: technical analysis
Re: Vlookup Formula - Convert Text Input to Numerical Score
1) vlookup(cell_reference_that_contains_text, cell_reference_of_table,2,0) per our vlookup module on our Advanced Excel for Data Analysis class, the last zero is for exact match. 2) your last post asked a similar question. NO nested if's under any circumstance. period. there is no such thing as a &... Read More
1) vlookup(cell_reference_that_contains_text, cell_reference_of_table,2,0) per our vlookup module on our Advanced Excel for Data Analysis class, the last zero is for exact match. 2) your last post asked a similar question. NO nested if's under any circumstance. period. there is no such thing as a &... Read More
Re: Questions about total debt & preferred stock
A1) Generally, for credit ratios, we would include all forms of debt (non-equity essentially) as part of debt and capitalization. Whereas, in the slightly more detailed schedule on the left, since we have the breakdown, we said total debt. This blends in to the larger credit analysis course. A2) Pr... Read More
A1) Generally, for credit ratios, we would include all forms of debt (non-equity essentially) as part of debt and capitalization. Whereas, in the slightly more detailed schedule on the left, since we have the breakdown, we said total debt. This blends in to the larger credit analysis course. A2) Pr... Read More
Re: Off Balance Sheet Capital Commitment
For what purpose are you evaluating the company? Assuming the capital commitment is to build a building in the future: 1) for standalone going concern valuation, we don't think that this needs to be capitalized because it seems to be part of operations; if this is not the case, please provide more ... Read More
For what purpose are you evaluating the company? Assuming the capital commitment is to build a building in the future: 1) for standalone going concern valuation, we don't think that this needs to be capitalized because it seems to be part of operations; if this is not the case, please provide more ... Read More
Re: Accounting for Discontinued Ops on financial statements
I am trying to out together a training package for BD professionals on discontinued operations, do you have any recommendations on where can I find some information? Purpose of package is not to be too technical or too deep into accounting guidelines but a high level overview of requirements, implic... Read More
I am trying to out together a training package for BD professionals on discontinued operations, do you have any recommendations on where can I find some information? Purpose of package is not to be too technical or too deep into accounting guidelines but a high level overview of requirements, implic... Read More
Re: Reference Range (Corporate Valuation)
Thank you for your inquiry. The reference ranges are our best final guess of the valuation parameters in question. Notice that they are hard coded in blue, meaning they are inputs as opposed to calculated formulas. Thus, this is what we believe is the valuation range for that particular valuation me... Read More
Thank you for your inquiry. The reference ranges are our best final guess of the valuation parameters in question. Notice that they are hard coded in blue, meaning they are inputs as opposed to calculated formulas. Thus, this is what we believe is the valuation range for that particular valuation me... Read More
Re: COUNTIF, Then What?
You need two criteria. One for date and one for Yes or No trained. If you are using Excel 2007 or 2010, use COUNTIFS function. note that this is NOT backward compatible with Excel 2003. If you are on Excel 2003, you need to use an array. This is covered extensively in our Advanced Excel for Data An... Read More
You need two criteria. One for date and one for Yes or No trained. If you are using Excel 2007 or 2010, use COUNTIFS function. note that this is NOT backward compatible with Excel 2003. If you are on Excel 2003, you need to use an array. This is covered extensively in our Advanced Excel for Data An... Read More
Re: IRR
1) your transaction premium/price and therefore, your implied transaction multiples should be a result of your valuation, which comes BEFORE the LBO model (in theory) and of course, the financial model is first. This partially stems from deal comp multiples as well as standalone "intrinsic valu... Read More
1) your transaction premium/price and therefore, your implied transaction multiples should be a result of your valuation, which comes BEFORE the LBO model (in theory) and of course, the financial model is first. This partially stems from deal comp multiples as well as standalone "intrinsic valu... Read More
Re: Issues with Center No Merge
The issue you run into is because the formatting of the now unwanted former column cells are still formatted as center no merge (technically center across selection). The solution is to select the cells you don't want and reformat. You have several options: 1) ALT E+A+F => edit, clear, formats; t... Read More
The issue you run into is because the formatting of the now unwanted former column cells are still formatted as center no merge (technically center across selection). The solution is to select the cells you don't want and reformat. You have several options: 1) ALT E+A+F => edit, clear, formats; t... Read More
Urgent : Historical Betas Database
Could someone please enlighten me if there exist any databases which have historical beta values for companies? Which are the most reliable or popular databases? Also, while computing the WACCs for a company, we sometimes use peer sets or industry averages for unlevered beta and then find out the l... Read More
Could someone please enlighten me if there exist any databases which have historical beta values for companies? Which are the most reliable or popular databases? Also, while computing the WACCs for a company, we sometimes use peer sets or industry averages for unlevered beta and then find out the l... Read More
Dear WST, Thank you so much! I would recommend Hamilton Lin's Advanced Excel for Data Analysis course without reservation. It is worth every penny spent. I have a few follow-up questions: 1. The scorecard is evaluating a dozen of project initiatives and each with 10 metrics to reach an overall ... Dear WST,
Thank you so much! I would recommend Hamilton Lin's Advanced Excel for Data Analysis course without reservation. It is worth every penny spent.
I have a few follow-up questions:
1. The scorecard is evaluating a dozen of project initiatives and each with 10 metrics to reach an overall score. For simplicity and to avoid potential model crash (it will pull from P&L tabs for financial impact metrics), will it be easier to use just one Nested If formula? Or we use can Vlookup or match combined with offset for potentially 100-spreadsheet model without worrying about model crash?
2. Personally I think Nested If forumla will be more straightforward & ease of audit given the number of metrics involved (please feel free to correct me if I am wrong). For the example above, I write the following Nested If formula:
Assumption: A1 is the input cell. Formula: B1=IF($A1>20%,12,IF($A1>=10%,8,IF($A1<10%,4,"n/a")))
This formula works well with number/% input. However, if I put text value in cell A1, such as high, low, medium, the output always shows 12 points, instead of "n/a". Could you please advise how to edit the formula to make it robust?
3. Is there a simple way to write up a nested if formula for the following metrics: "If the market size >$1B, 12 points; if the market size is between $100M-$1B, 8 points; if the market size is <$500M, 5 points."? I do not want the number in the formula to have 6-9 zeros. How can I write a simple formula for that.
4. Do you think it is a good idea to use a drop box (as taught in the Advanced Data Analysis course) to control user input? If so, given the large number of tabs involved, will this cause model to crash?
I appreciate that you provide the brilliant courses and a forum to discuss modeling issues WST students encounter at work.
Best wishes! Read More