Financial Modeling
Ask your modeling questions here! Feel free to copy/paste specific sections from filings as appropriate.
Questions/Discussions
Sort by Date ▼ / Top Rated
Register for free or log in at the top right of this page to join the discussion
Break out depreciation from cost of goods sold or operating expenses
Hi WST,
I am wondering if we should break out the depreciation expenses from either cost of goods sold or operating expense when preparing for the historical portion of the income statement and make the projection accordingly?
I am wondering if we should break out the depreciation expenses from either cost of goods sold or operating expense when preparing for the historical portion of the income statement and make the projection accordingly?
bank's credit costs calculation
Hi,
Can I know how do I calculate the credit costs for impaired loans for banks? Is it correct if it total provisions made for impaired loans / total loans for the time period.
Based on the reported figures for 4Q 2020:
total provisions made for impaired loans are : S$241 million
Total loans...
Read More
Banks - Key CAMELS Ratio
Can I understand for banks' operating profit, do we commonly take preprovision profit before tax? If so, how do we calculate preprovision profit before tax?
Please correct me if I'm wrong for the PPOP calculations for banks:
PPOP = (net interest income + total non interest income - i.e. fee in...
Read More
Bank Financial Modeling
We are looking to improve our modeling ability specifically around banks. I've taken a look at some competitors and their models seem to be stuck in Basel II and aren't updated to the new FASB CECL methodology. We did take a look at Wall Street Prep and it looked like their course was written in 201...
Read More
Depreciation Schedule
Hello, I am having difficult completing the deprecation schedule from the template. Do we assume a useful life for buildings and machinery/equipment to calculate the weighted average useful life of gross PPE? I'm not sure how % accumulated depreciation metric is incorporated or how gross % breakdow...
Read More
Adv LBO Modeling TL Logic
Hello,
I'm currently looking at the super advanced LBO model and I'm trying to understand the logic on the Term Loan Borrowing / Paydown line. Can you please explain what the formula is about? How can you draw on a TL? The formula looks very similar to a Revolver Borrowing / Paydown line. Your el...
Read More
Historical Cash & Equivalent Reconciliation
Hi WST, I have a somehow stupid question. I realized that we only make projection in the cash flow statement without bothering with the historical cash flow items. I kind of tried to use the same approach in cash flow statement to calculate the historical Cash & Equivalent to see if they would mat...
Read More
Intangible Asset Amortization
Hi WST, I am trying to build a more detail projection by including amortization of intangible asset amortization; instead of grouping it with depreciation and deducting it from PP&E with depreciation.
Now, I am running into a scenario where the company does not break out the depreciation and amor...
Read More
Over Estimating Depreciation
Hi WST,
Quick question: if we can't have the residual value of a company's depreciate assets; does that mean we could very well over estimate the depreciation expense? Ex. A company has $1000 of machinery and Equipment at the beginning of the period and with 3 years of life. However, without kno...
Read More
Trump's Tax Law - Interest Expense deduction limit?
How does one account for the updated Interest Expense deduction limit in financial models?
Convertible Bonds
I am building a projection model and at the stage of building the debt schedule. Now, I have a situation where the company has convertible bond (CB) being classified on its balance sheet into liability portion and equity portion. Now, I am a bit confused. EX, the company raised $1,000 of CB, incl...
Read More
Re: Excel Formula of running a scenario analysis
I'm now at the end of the DCF model and I'm about to run a scenario analysis with changes of some variables, e.g. dividend payout ratio and beta. So if I want to set up a table with different combinations of these two variables, is there any excel formula that I can use to fill in the intrisic value...
Read More
DCF modeling in an M&A situation
I’m doing a DCF model for a deal, a briefing of the case is as followed:
1. The listed company acquired company A for 39% shares in 2016, therefore there is a profit of associate in 2016.
2. The listed company acquired the rest 61% shares of company A in 2017, the consolidation date is 31/8/20...
Read More
Waterfall distribution IRR
I have recently came across a GP structure where the 2nd level waterfall distribution is based on XIRR. But it specifically states that the XIRR is not compounded. I thought inherent in the XIRR calculation is compounding effect. How to build a model that satisfies the non-compounding XIRR?
Question on the model
Question on the model:
1. For the cash flow statement, I understand the min cash balance I should input the amount the firm needs to continue operations. But trying to better understand the mandatory debt repayment cell and what is most appropriate to enter here?
2. For the interest calculati...
Read More
Quarterly Modeling - Current Portion LT Debt
When modeling a company and making projections using quarterly financials, should the quarterly balance sheet item for Current Portion of LT Debt include the next 4 quarters of mandatory payments? If so, to not add debt to the balance sheet, do you then decrease the Long Term Debt liability by the ...
Read More
WST macro query
Hi everyone,
Does anyone this problem whereby if they use WST macro's formula auditing function ie. Control + Shift + [
Should the formula get too long, it would be obscured by the window pane. For example, see (https://dl.dropboxusercontent.com/u/17604288/Problem.jpg)
Any solutions to thi...
Read More
ROIC
Why does ROIC (net income-dividends)/(debt+equity) include all stakeholders in the denominator but only equityholders in the numerator?
Why is Long Term Liabilities and LT Compensation included in changes in Working Capital?
I thought Working Capital encompassed only current assets-current liabilities
Why Is Minority Interest a positive for cash from operating activities?
Estimated Projected
Fiscal 2016 2017 2018 2019 2020 Calculation
Cash From Operating Activities:
Net Income $ 730 $ 801 $ 868 $ 937 $ 1,021 From Income Statement
Plus: Depreciation & Amortization 718 738 760 785 814 ...
Read More
Future Stock-Based Compensation and Adding it to Share Count
What is the best way (balancing accuracy and simplicity) to model future stock-based compensation expense? Related, what is the best way for these expenses to flow through taxes (expense, DTA, etc.) as well as share count (end of period, average, diluted average)? Do you attempt to model out future ...
Read More
investments in JV or associates
If the investments in JV or associates are core to the company, e.g. JV with local governments in running Toll Road business for toll road operator company,
1) shall we include the profits/loss from JV or associates to the EBIT, EBITDA, Net profit
2) shall we include the interests in JV or assoc...
Read More
Minority interest included as debt
I believe that before we were told to include minority interest as debt. I am somewhat confused as to whether we should do this when levering and unlevering beta. By including it as debt, wouldn't we be assuming minority interest is tax deductible?
equity IRR
What do we use in calculating equity IRR assuming we need to calculate and show one in an asset acquisition assuming you have control?
Also, saw your cheat sheet of cash flow. It seems all have pitfalls particularly FCFE. Then what is the best among them and usually use in practice? Why is FCFE n...
Read More
Prior year's assets - Bank Financial Modeling
What is the logic behind using prior year's assets to calculate the Average Interest Earning Assets (in the Intermediate Bank Financial Modeling)? Thanks you!
Difference between cost of sales and cost of inventory.
- This is a company in Hong Kong. In the P&L, the "cost of sales" is higher than the "cost of inventories sold" in the note from financial statement. The note talks about each expenses (ie staff cost, rent, dep etc) have been charged to arrive the operating profit. Just wonder why those two numbers...
Read More
Convertible Securities
Convertible securities and the Balance Sheet, if converted S/O increase, Debt decrease by the current PV of the face value. Does APIC increase by that amount, how do you adjust the equity section of BS. thank you.
Modeling a Serial Acquirer
Numerous companies (a mainstream notable is Valeant) make acquisitions part of the business model. What do you recommend is best practice to model/project a company that will likely derive a material amount of sales/earnings from lines of business that are not currently in the financial statements? ...
Read More
Accounting for vested but unexercised stock options...and then again when they are exercised
I believe I understand how to account for stock options on all 3 statements: NI goes down, CFFO remains neutral (ignoring taxes for simplicity) because of offsetting NI reduction and addition of same amount of stock options (non cash charges). Retained earnings goes down and then APIC goes up by the...
Read More
How do stock options affect all 3 statements?
If a company chooses to pay its executives in stock options amounting to $100 I assume that reduces NI by $100 (ignoring taxes for simplicity). As for the cash flow statement, NI is lower by $100, but that same $100 is added back in CFFO as a non cash item leaving that section unchanged. On the bala...
Read More
Stock Options Tables in 10ks do not seem to be itemized like they are in the course
Throughout school and Wall ST Training courses I have always learned to construct fully diluted shares outstanding by looking at a table of options that presents several strike prices and several corresponding share amounts for a given year.However, in practice I rarely see stock tables in 10Ks pres...
Read More
Calculating Free Cash Flow for a business unit
A company has 3 separate business units however it doesn't provide 3 separate balance sheets in reporting, only the consolidated financial statements. How can I calculate free cash flow for a particular business unit?
Projecting minority interest
How should I project Minority Interest expense on the BS and CF?
WACC for private company
: if the company has no intention to list its stocks in the foreseeable future, should we still consider the cost of equity in WACC calculation?
Implied statement of cash flows vs. actual statement of cash flows
In your class, we entered the historical income statement and balance sheet and then solved for the statement of cash flows. When I've tried this on my own, I've found that my working capital and CFI numbers differ significantly from items found in the company's reported statement of cash flows and ...
Read More
Dealing with Stock-based comp in the CF Stmt
What is the easiest way to deal with SBC in the CFFO section and then the CFFF sections of the CF Statement? Is there a way to estimate forward looking comp?
Cash Circular
I have seen the videos (sometimes twice). I have read *everything* on circular references. I have checked that my income expense is keying off of my ending debt values (this company has no debt) and that it has a negative sign. I have similarly checked interest income. It too keys off the ending deb...
Read More
Beginning Cash Balance
Hi
In the debt schedule, we did the following:
Cash Available for Debt Servicing
Plus: Beginning Cash Balance
Less: Minimum Cash Balance
Equals: Cash Available before Mandatory Debt Payments
Then we pay debt and revolver; which essentially means that we have utilized beginning cash balan...
Read More
Income Tax Payable
I just wanted to know if Income Tax payable can be lumped together as AP on the balance sheet or do we categorized it as accrued income taxes? Can you please explain? Thank you.
Questions about total debt & preferred stock
Q1: The section of Company Overview in Package shows how to calculate total debt.
In financial summary, cell F34, total debt = commercial paper+current portion of long-term debt +long-term debt.
However, in cell L32, total debt/total capitalization, total debt here = F34(total debt) +preferred stock...
Read More
Questions from Advanced Financial Modeling course
Hi I'm going through the advanced financial modeling course and a few questions that came up (they're a little elementary but would be helpful in fully understanding the topic):
When going up and down the capital structure, why are things such as working capital (AR, AP, Accrued Liabilities etc.) n...
Read More
Plugs
I have been taught to "close" financial statements using plugs. I think this is a lazy way to do it. Is there an alternative to this tricky solution? Thanks for your answer.
DCF model - working capital item
Hi there,
I would like to ask a question on the Discounted Cash Flow model; in particular, the change in working capital item.
Just wondering how I can calculate this item?
Do I add the following items: change in inventory, change in account receviables and change in account payables to find ...
Read More
Adv Merger Model
Balance Sheet Adjustment Intangible Assets:
Have listened to the justification on the video and it is not hitting home. Please explain why the Balance Sheet amount of the Target Company is deducted from the Intangible Assets adjustment (PPA amount)
Thanks
Have listened to the justification on the video and it is not hitting home. Please explain why the Balance Sheet amount of the Target Company is deducted from the Intangible Assets adjustment (PPA amount)
Thanks
Question about total Capex
Does total Capex equal payments for PPE minus sales of PPE?
Thank you.
Thank you.
Adv Merger model error
Pls confirm whether this is an error or not in your model:
- you have not dollared the formula in cells I54 and I55 meaning you are taking forward zero values in rows 54 and 55 for book/tax depreciation
- this makes a minor difference to the tax charge of c7k which is then carried forward through t...
Read More
Adv Merger Model - debt sweep
PLease xplain why in the Debt Sweep the debt water fall is structured as follows:
1. New Senior Debt
2. Target/Acq debt
3. New Notes & Revolver
SHouldnt it be All new debt first, then old debt then new revolver. I guess they way you have done is is assumed that all old debt is senior but sits ...
Read More
Off Balance Sheet Capital Commitment
Hi WST,
I am evaluating a company and come across a disclosure on its financial report stating that there is, say, XYZ amount of capital commitment for building construction not included in the financial statements. So my question is should we capitalize that capital commitment on the balance sheet...
Read More
Create a simplified model from the full blown core model
How do I create a simplified model from the full blown core model, removing the debt sweep page?
Monthly Model Balance Sheet Drivers
In our LBO modeling class we calculated inventory and accounts receivable using days inventory outstanding and days sales outstanding. However, this model was produced on an annual basis. When developing a model on a monthly basis, would you still use this formula or would you setup separate inven...
Read More
Short Term Investment treatment
I hv short term investment of 100 amount so that represented a -100 in CFI.
so if i get interset income from that, that will give me + 10 in CFI.
but let's say if i want to draw down 25 from the CFI to to finance the company. so i reduce CFI by -25, but where does my 25 goes in the rest o...
Read More
How do you define Interest Coverage Ratio?
How do you define Interest Coverage Ratio?
Do you use EBIT or EBITDA or something else in the numerator?
Please explain briefly you reason for preferring one over the other.
Do you use EBIT or EBITDA or something else in the numerator?
Please explain briefly you reason for preferring one over the other.
Debt and Capex
According to you model CAPEX requirements are calculated in the Cash before Debt figure. LT Debt and the respective CPLTD stays unchanged and the mdoe, balances with the Revolver. This implies that all CAPEX requirements for the year that are not financed through CFO and postitive CFI or CFF (excl d...
Read More
Fixing #VALUE errors in my model
I built my own lbo model that links IS, BS, CF stmts and some other tabs.
After changing around my Summary page my entire model has "VALUE" errors. I traced it back to the Interest line on the IS ==> which is linked to the changes in debt figures on the BS that have the same "VALUE" errors ==>...
Read More
Merging Models into one Excel file
I built a monthly model for 2008 and for 2009, but I saved them as
separate files. How do I merge them into one model by adding each tab in
the 2009 model to the 2008 model? Thanks.
separate files. How do I merge them into one model by adding each tab in
the 2009 model to the 2008 model? Thanks.
Balance Sheet doesn't balance
Im having a problem, I built the model as outlined in the training session. My model has no debt element. Yet the Balance Sheet does not balance. I would very much appreciate it if you could kindly let me know what the problems could be, or which areas I should look into.
Adding in additional debt sweep logic
I'm finishing up a model where we add a senior debt piece and a
seller note. I'm trying to model an excess cash sweep above the
minimum cash requirements to pay down the senior debt piece with the
excess cash. I've completed that in the place of a revolver (I
assume the company will ne...
Read More
Advanced bank financial modeling question
When we project NCO for a bank we are focused (in addition to everything else) on gross loans before NCO that is quite logical. Dealing with historical Gross Loans we claimed that they were reported net of NCO (that is in a good agreement to GAAP of course) BUT when we move from historical data to f...
Read More
Share Repurchase
Hi WST,
I remember that in the advance financial modeling course that you used the trailing basis for the projected EPS to calculate the company share repurchase. I am just curious why was "trailing EPS" used instead of the projected EPS? Was it because the EPS projection was not complet...
Read More
Why and how does the revolver balance the BS
Usually I use a simple plug to balance the BS but after taking your class I would like to incorporate a debt sweep to do this. However, I am unsure as to why the revolver balances the BS and can’t get the debt sweep to balance my models. I understand the formula pays down debt if there is cash a...
Read More
Other Income and Expense
I have a question regarding other income and other expense. Should these be placed below the line along with interest expense/income and capital leases or should it be placed on the very top just after net sales on the income statement? If other income/expenses should be placed below the line (EBI...
Read More
Inventory Days semi-annual calculation
just want to check if i'm to calculate the inventory day for 1H, do I average the inventory YE 2010 and Jun 2011 in the balance sheet, or use Jun 2010 and Jun 2011 inventory level?
WMT model question
Hi
I've just attended the Advanced Financial Modeling - Core Model workshop.
After going over the WMT model today I'm unsure about the following. Please can you clarify for me.
In the WMT B/S you have "Minority Interest", in cell G36 =397+1794, under Liabilities.
However in the actual ...
Read More
D&A is different on the I/S and CF, which to use ??
When the D&A figure is different from the I/S to CF statements, which one should I use on the income statement when modeling forward and what is the reason for the D&A figures being different?
Modifying the Debt Sweep to Target a Debt/Capital Ratio
Hi - Can you help me figure out how to modify the Debt Sweep Schedule to target a specific debt/capital ratio? You explain this in your Credit & Leverage Statistics videos without an excel example. Could you provide an example in Excel?
Joseph
Joseph
Modeling ADR shares
Hello,
I have a question in regards to conducting valuations for ADR listed companies. From my understanding, it looks like the models are conducted and valued at the local currency. In order to translate the valuation model into USD do we typically use the Temporal/Current Method for translation...
Read More
Growth Vs %rev
In the income statement support, how do we know which items we want as % growth and % of revenue? Does it matter?
Technology Sector Valuation
Hello,
I was wondering if there will be an industry specific model for the technology sector that will be created in the future? I'm very interested in this area but didn't hear anything about it coming soon. I thought I would ask. Thank you
I was wondering if there will be an industry specific model for the technology sector that will be created in the future? I'm very interested in this area but didn't hear anything about it coming soon. I thought I would ask. Thank you
Altman Z-Score
Does WST have a setup or excel equation for computing the Altman Z-Score? Thanks!
Power plants company: Free Cash Flow?
I have a consolidated power company model that has current operational power plants and plants under construction. I’m using FCFF in valuing both types so far. But I think it is wrong to use FCFF for a project that has not yet started because I am not taking into account the debt in the future. Ho...
Read More
Hospital Model
Is there an available example of a financial model of a hospital? Thank you.
Cash Circular
Ok, here's the test of lifetime support. I have seen the videos (sometimes twice). I have read *everything* on circular references. I have checked that my income expense is keying off of my ending debt values (this company has no debt) and that it has a negative sign. I have similarly checked intere...
Read More
Change in Net Working Capital
thx
Advance Financial Modeling- Core Model Questions on CF
Hi,
In the Cash Flow Statement, the model included Other assets and Deferred Charges in the changes in working capital calculation. From my other studies, I learnt working captial only included current asset and current liability, however, other assets and deferred charges is under long term asset...
Read More
WACC question
If you are a buyer and evaluating a potential target, and now you have the target's standalone Free Cash Flow. For DCF valuation, is it right to apply the buyer's's WACC to the free cash flow, with the logic that the buyer should value how much the cash flow worth to it using its own cost of capital...
Read More
How do I model out zero-coupon accretion / PIKs?
Full Question:
I have one question on modeling out a company with discount notes that accrete over the forecast period. I have already separated the different debt tranches. But I find that every time I include the accretion for the notes and then add back the after-tax non-cash interest expense to...
Read More
Circular references clarified
Full Question:
My model calculated, but I had a question about the final (key) point surrounding the iterations. I understand that we entered a choose function which will alternate using the average cash balances (2) or the beginning value (1). I also understand how to set up and run the iterations...
Read More
How do I account for stub periods in an LBO model?
Full Question:
If an LBO transaction does not happen at the end of the Pro Forma fiscal year (ie, 2004) but, say, at the end of Q2 2005, how can I account for this?
If an LBO transaction does not happen at the end of the Pro Forma fiscal year (ie, 2004) but, say, at the end of Q2 2005, how can I account for this?
How does one correctly calculate Days Payable Outstanding?
Full Question:
I took your advanced financial modeling class yesterday and have a question for you regarding the DPO (Days Payable Outstanding) formula. The formula we used was AP * (365/total expenses). I thought DPO used COGS to calculate. How come we’re using total expenses instead? Is it some...
Read More
How do I simplify sensitivity analysis in a financial model?
Full Question:
we are constantly setting up models and running different case scenarios based on variable inputs we create in our models. Often times we need to summarize these cases onto one table and because the models are complex and we change more than one variable at a time it sometimes means ...
Read More
What if discretionary debt sweep is positive?
Full Question:
In calculating Discretionary Sweep for WMT, the formula had: -Min(Beginning Balance Revolver, Cash Flow before Discretionary Debt Repayment). In this case, Cash Flow before Discretionary Debt Repayment was negative and hence Discretionary Sweep became a positive number.
I just com...
Read More
How to project minority interest?
Full Question:
I'm working on a company called Central European Media Enterprises and on the balance sheet there is minority interest. I know in the video we did not project that account because we did not have enough information. However, I do have quite a bit of information on the ownership inter...
Read More
Modeling out working capital
Full Question:
For change in working capital, you use the change in operating expenses. Normally, and what I’ve been taught, is that you use current assets minus current liabilities. Can you explain to me why you would use the change in operating expenses?
For change in working capital, you use the change in operating expenses. Normally, and what I’ve been taught, is that you use current assets minus current liabilities. Can you explain to me why you would use the change in operating expenses?
LTM / Quarterly Financials
Is there a package or session that covers integrating quarterly info into the core model to create LTM financials?
Cash Flow Projection / Capital Structure Change Modeling
which would be the best package for modeling out cash flow projections based on capital structure changes? After completing the advanced modeling module, I know I can just input new tranches of debt with assumed interest rates and flow that to come up with free cash flow projections...but wondering...
Read More
Undistributed Earnings Allocated to Participating Securities
I saw a company reduces its net income by removing "undistributed earnings allocated to participating securities" in its basic or diluted EPS calculation. It explains in its note:
Net income attributable to common shareholders
Less: undistributed earnings allocated to participating securit...
Read More
Revenue Sensitivity
I am trying to set up a sensitivity toggle for price points of a particular product. Basically, I have a min and max for the range of the price and I'd like to have three different scenarios which switch between the min, max and average. What is the best way to set this up without using a bunch of I...
Read More
Enhancements to the core model - Part II tax schedule
Hi,
In the last step of the tax schedule, I understand that we need to go the CF statement and make changes. I just want to make it clear that do we treat the change in DTA and DTL as part of change in wokring capital in the CFO?
Thank you.
In the last step of the tax schedule, I understand that we need to go the CF statement and make changes. I just want to make it clear that do we treat the change in DTA and DTL as part of change in wokring capital in the CFO?
Thank you.
The Financial Modeling Process
I participated in the 2 day long University Bootcamp at my school. It was a valuable class, but the brevity of the program led some of us in the class to blindly follow the instructor at times. I am attempting to redo the WMT financial model on my own so that I can reinforce the skills I learned i...
Read More
debt
Is there a course which speaks to debt for project financing? ie. debt sculpting as well as sizing the potential debt capacity based on a target DSRC over the life of a project?
Minority Interest To the CF
Hello,
Can I please ask why we did not add back the Minority Interest to the Net Income on the CF on the advanced Core Model ? We added only depreciation to the Net Income ,but not the other non-cash items other than depreciation. Can you please advis ?
Thanks
Eylem
Can I please ask why we did not add back the Minority Interest to the Net Income on the CF on the advanced Core Model ? We added only depreciation to the Net Income ,but not the other non-cash items other than depreciation. Can you please advis ?
Thanks
Eylem
Circular Reference Explained (Summary)
Many folks have asked us to quickly summary in as succinct a manner as possible the reason why a circular reference is required in financial modeling.
Recall, a circ is when a cell needs itself to calculate itself. In general, the only reason for a circ is as follows:
- End balance of debt and cash ...
Read More
taxes
Could you please explain exactly how the figures form the tax schedule (ie DTAs, DTLs) wrap into the CF Statement.
calculating CHANGE in N.W.C , why monthly and not annual?
Question:
in PACKAGE 2 "Basic FM and DCF"...........Free Cash Flow Calculation "video"
why was the Change in NWC calculated on a monthly basis and not on annual basis (cell I14, tab: DCF (simple) in HRH Model Template) taking into consideration that the column is for the w...
Read More
Projected balance sheet challenge
Hi,
Thank you for the great job you are doing.I am new to financial modelling,but i have alittle challenge,I have projected my income statement and balance sheet,but the balance sheet is not adding up to zero,i have a positive no as the difference for all of the projected years,this is what i hope...
Read More
Inventory Writedown
Hello,
I'm trying to integrate an inventory writedown to the fully integrated financial model that I've used from Hamilton. The model currently balances w/o any inventory writedown feature. I was wondering how I can add in an inventory writedown into the IS/BS/CF and still have the entire model b...
Read More
How to model for existing revolver
Having worked through core model and enhancement classes, I am now trying to recreate this model for a new company. My question is that the new company already has an existing revolver with an outstanding balance. My initial instinct is to treat the outstanding balance on the revolver as a term loan...
Read More
Balancing my M&A Model
Hello,
I don't even know where to begin...but I'm trying to use the super advanced M&A model to evaluate an M&A. However, I tried to follow more or less the same design as what Hamilton initially constructed but am having a hard time balancing my model for my projection period. I was able...
Read More
Modeling an Equity Raise (IPO/FO)
This may be a stupid question since you don't cover it in your first 6 modules, but I was wondering what other considerations there are when modeling the pro forma impact of an IPO, follow-on or even a PIPE. Clearly the big items are the increase in cash and the corresponding increase to Common Stoc...
Read More
Completed Model: Advanced Bank Model
Hello, I took the advanced bank modeling course. I had a few problems in building out my model while watching the videos and am wondering if anybody has a complete model that I can review to see where I made my errors (aka: balance sheet not balancing). Also, I am attempting to modify this model to ...
Read More
Financial Modeling - Enhancements Part 2
Hi - I am having a little trouble understanding how Goodwill impairment is treated in the GAAP financial statements. I am under the impression that if there is an impairment, the impairment is deducted from Operating Income to arrive at the GAAP Pre-Tax Income. Is my understanding correct? If yes, t...
Read More
Modeling Questions for Mature Company
Hello,
I have a few questions for modeling a mature company relative to the model Hamilton showed us for GT a few weeks ago. Please feel free to answer these one at a time or all together, which ever is earier.
1) I’m modeling a company (Medtronics) which has consistent commercial paper borrow...
Read More
Financial Modeling - Enchancements Part 2
Hamilton explains at the end of the video how to integrate the Tax Schedule file with the financial model developed in Part 1. I had a query regarding this, while integrating the Tax Schedule with the model, where does one obtain the Ending NOL figure (if any) in cell F23 from (this was hard coded f...
Read More
Historical Cash Flow statement
What are the pros/cons with creating a historical CF statement using the historical balance sheet and income statements to calculate instead of plugging in the numbers directly from the financials?
What is considered best practice for historical analysis of the CF statement?
Thanks.
What is considered best practice for historical analysis of the CF statement?
Thanks.
Debt Extinguishment - Equity offered
In addition to the 2010 debt extinguishment, approximately $225.8 million aggregate principal amount of the 11% Senior Subordinated Notes held by certain affiliates of the Company’s controlling stockholder were transferred
to the Company’s indirect stockholders and ultimately to XYZ Holdings, th...
Read More
Current Liabilities - Taxes
Most of the companies I model out have both deferred income taxes and income taxes payable line items in current liabilities. Your core model only has accrued income taxes. Curious how you would handle the additional account for modeling purposes.
Adjusted EBITDA - Core Model
I have been through your basic financial modeling and all parts of the Core Model and Enhancements. Right now I am going through the Complex Trading comps to get a handle on all adjustments that might be out there. However, (if memory serves me right), it was only in the basic course that you ac...
Read More
Package 3 - Advanced Financial Modeling - DCF analysis quest
I'm working on Package 3, Enhancement to the Core Model Part I - DCF.
The DCF analysis is for 5 years, with 2006 being time = 1 (1st year).
Also, the NPV function assumes cash flows happen at the end of the
year - so 12/31/2006. So if I'm running a NPV function on the 5 years
of forecasted unlevere...
Read More
Building IS for DCF Model
I am currently building the PEP model for the online self-study certificate and I have a question regarding the final net income number and the final diluted EPS number. From what I have learned from the video, Mr. Lin used the 10-K to check the final net income and diluted EPS number and they match...
Read More
Shareholder momentum analysis
Not sure if this is the appropriate topic to post this question. I've been asked to put together a S/H momentum analysis going back 3 years for a public company. Some questions on this:
1) Do you start with, say top 25 holders today, and work backwards to trace how they reached this point? In order...
Read More
Manufacturer With Captive Finance Co.
In general, do you have a preferred approach to analyzing a manufacturing company with a captive finance unit that is integral to the overall company business? Would you consider expenses like interest and depreciation on loans and leases incurred by a captive finance company like Paccar Financial ...
Read More
Vlookup Formula - Convert Text Input to Numerical Score
Dear WST,
1) How do I write a Vlookup formula to convert text input to numberica score with example as follows:
High 10
Medium 5
Low 3
2) Per Hamilton Lin, we should avoid Nestedif statement at all cost. In the case above, is it easier to do a Nestedif statement, simple and str...
Read More
Modeling 'Names' used in IB/PE - Question
Hi Fellas!
I have an empty background (education/experience) except in snow-shoveling..and when I read about Private Equity or Investment Banking, I come across the modeling subject and this one and only question-
How many [types of] financial models do IB & PE firms use? I mean what analyst, ...
Read More
). So if I walked in on, say, JP Morgan doing a merger deal on Office Depot & OfficeMax, and had 'some' knowledge about what they were actually doing [such as what each major line in excel meant and was calculated - EPS, Revenue, EBITDA, and so on..], I could probably take a seat and join them or continue someone's unfinished work.
LBO of a public company
I am modeling out an private equity sponsored LBO of public company - using the construct of the core model. Naturually I have historical financials - in addition I have a proforma income statement and balance sheet -- to reflect the go-forward position of the company. I am having difficulty get...
Read More
Circular reference problem in my model due to interest-HELP!
Full Question:
I have created an interest tab where I would like to derive my interest expense and debt amortization schedule. I would like both of these to feed into the income statement and balance sheet in financials tab. In the debt amortization schedule I would like to derive the cash flow swe...
Read More
Footing my accretion/dilution model with reported numbers
Full Question:
Why is it that in attempting to replicate a merger analysis of an announced deal, an accretion/dilution merger model does not foot to companies' presentation materials even after including all relevant data items?
Why is it that in attempting to replicate a merger analysis of an announced deal, an accretion/dilution merger model does not foot to companies' presentation materials even after including all relevant data items?
Convert an annual model into a quarterly tracking model
Full Question:
In my new job, where I follow the retail industry as a high-yield analyst, I have been told that I might want to prepare quarterly forecasts in order to gauge how a company is doing relative to my annual forecast. How do you suggest amending the forecast we prepared in class to prepa...
Read More
Forecasting working capital off future quarters' sales?
Full Question:
When forecasting working capital levels on a quarterly basis, can I assume that the best approach, given the seasonality of retail stores, is to base my inventory/payables forecasts on that same quarter’s sales? Alternatively, would it be better to base my current quarter inventory...
Read More
Amortizing debt out month by month vs annually
Full Question:
Why don't you amortize the debt out, month-by-month, to get the estimated interest and principal paydown (as opposed to using a simple interest rate times the beginning or average balance)?
Why don't you amortize the debt out, month-by-month, to get the estimated interest and principal paydown (as opposed to using a simple interest rate times the beginning or average balance)?
Estimated Taxes
Hello:
I am modeling out a company that has been experiencing NI losses (and pre-tax income losses) over the last few years and recently has started turning a profit. In attempting to figure out what taxes will be next quarter and for the full year, what is the best way to do this? I am not loo...
Read More
Accounting for Discontinued Ops on financial statements
Full Question:
How do you typically account for Discontinued Operations in the BS, IS and CF?
With regards to the BS: I am looking at the 3Q2006 results of WMT and reported the affect on the Cash & Equivalents and Current Assets of Discontined Operations, but have not detailed the affects on...
Read More
Inputting scheduled lease payments in the debt sweep
Full Question:
How should I input the scheduled lease payment in the page of debt sweep of your spreadsheet? Should I input the scheduled payment of each year and leave interest rate zero?
How should I input the scheduled lease payment in the page of debt sweep of your spreadsheet? Should I input the scheduled payment of each year and leave interest rate zero?
Modeling out pension expenses on the cash flow statement
Full Question:
I am building an IFRS full model on a UK company at the moment and need to ask soemthing regarding the Cash Flow statement. Would you include payments to the Company Pension Fund as a negative item in calculations for the Cash Flow from Operations ? It seems many of the sell side rep...
Read More
Valuing a negative EPS and EBIT company
I'm currently valuing a company that is generating negative EPS for the next five years and at the same time generating negative tax effected EBIT. If I simply took the advanced financial model on WMT built by Hamilton in the video, using the DCF tab, I stick in a 6/7x EV/EBITDA multiple gives me a...
Read More
Finding the depreciation expense form 10K
Hi
I am trying to do some financial modelling and was going to input the annual depreciation expense for the past 5 years.
By looking at the consolidated income statement in form 10K (see below), the item "depreciation expense" was not stated.
Net revenue
Cost of sales
Gross margin
...
Read More
Share Based Compensation Forecast
Just wonder if you can help on my question. how should I deal with share based compensation? as they are non-cash item?
I'm trying to forecast for share based compensation for a company, let's say it has option plan A, B, C with strike price at 5, 10, 15. if the stock price is now 11. A and...
Read More
Gain on the disposal of assets, in CFO or CFI on CF?
Hi there,
This is a more general accounting question. Recently I have been seeing lots of companies alternate between reporting their gain or loss on the disposal of assets in either CFO or CFI in the cash flow statement, yet, I remember learning that such disposals were typically only in CFI as it...
Read More
Complex LBO - putting short term and long term debt together
In the video Hamilton explains how to put in the current proporiton of the short term debt into the long term debt in order to allow for the fact that if we are modelling out various debt tranches we may not have the short term proportion of the debt available.
He deletes the short term debt(481 i...
Read More
revolver
I forget what the discretionary revolver is intended to do. If I recall correctly from the lesson, there was no actual revolving line of credit from WMT, but you put in a revolver line item to calculate the discretionary debt sweep.
Can you please give me a refresher and let me know why this is on...
Read More
Balancing the Core Model
I am done with all the core and advanced financial modeling videos. I don't quite understand why the balance sheet balances in the end. I understand the logic behind the debt sweep pertaining to the revolver, and how its used to pay down or borrow cash, and the fact that the ending debt balances imp...
Read More
about the diluted shares outstanding
I am taking the Advanced Financial Modeling course. I have a question about the calculation of the diluted shares outstanding.
In the beginning, Mr. Lin assumed that the diluted share outstanding in 2006 equals to that in 2005 in IS and then calculated EPS. Next, he used projected diluted EPS from ...
Read More
Question about EPS
Mr. Lin assumed that the diluted share outstanding in 2006 equals to that in 2005 in IS and then calculated EPS. Next, he used projected diluted EPS from IS to calculate the repurchased shares in CF and then calculated the basic shares outstanding. Next, he used the basic shares outstanding to recal...
Read More
a question about circular refences
The calculation of share repurchase involves circular reference? If it does and circular reference at share repurchase is not solved, what to do?
Let's say I have exactly the same projected revenue no growth at all and the same assumed days of receivable for projection, by using average account receivable the projected receivable would produce a saw like projected account receivables which it kind of does not make sense. Should not the same revenue, the same days of account receivable should produce the same account receivables? Read More