Financial Modeling Questions and Answers

Financial Modeling Questions and Answers How would you forecast revenues? There are two approaches to model building — making your model realistic, or keeping it simple and robust. The first principles approach identifies various methods to model revenues with high degrees of detail and precision. There are also industry-specific considerations that need to be taken into account. For instance, when forecasting revenue for the retail industry, you will forecast expansion rate and derive income per square meter. When forecasting revenue for the telecommunications industry, you will predict the market size and use current market share and competitor analysis. When forecasting revenue for any service industries, you will estimate the headcount and use the income for employee trends. On the other hand, the quick and dirty approach to robust models outlines how you can model revenues in a much more straightforward way with the benefit that the model will be more simple and easy to use. In this approach, you will predict the future growth rate based on historical figures and trends. Tell me the difference between NPV and XNPV functions in Excel The NPV function calculates the net present value given a series of cash flows and a discount rate. The NPV function assumes that payments are spaced on equal periodic payments. The function argument is =NPV(rate, [value 1], [value 2],...) where rate is the rate of discount over the length of the period, and value 1, value 2,... are numeric values that represent series of payments and income. The XNPV function is far more precise than the NPV function because it takes into account the specific dates each of the cash flows takes place. This function requires the input of a discount rate, a series of cash flows, and a series of corresponding dates for each cash flow. The function argument is =XNPV(Rate, Cash Flows, Dates of Cash Flow) In valuing a security, investment or company, the XNPV function provides a more accurate net present value because it factors in the time value of money while NPV function does not. How do you forecast free cash flow? : Free cash flow to the firm (a.k.a. unlevered free cash flow) is the preferred approach when valuing equities using the DCF method. The formula to calculate FCFF is: FCFF = EBIT x (1 - Tax%) + Depreciation & Amortization - Net Capital Expenditure - Increase in Working Capital You can also calculate the free cash flow to equity, which is the amount of cash available to equity investors after paying off debt, interest, and investment required to keep the company operating. It can be calculated using the formula: FCFE = Cash from operations - Capital Expenditures + Net Debt Issued Q: What is sensitivity analysis and how do you perform one in Excel? Sensitivity analysis is a tool used in financial modeling to analyze how different values of a set of independent variables impact a specific dependent variable under certain conditions. For example, a financial analyst wants to examine how a company's profit margin will be impacted when variables such as the cost of goods sold and labor costs change. He can perform a sensitivity analysis to test the different sets of values for these variables and see how the profit margin changes accordingly. One of the most useful tools in Excel for performing sensitivity analysis is data tables, where you can show the output sensitivity by changing up to two independent variables. Tornado charts are also a great way of showing the impact of changes to many variables at once Why do capital expenditures increase assets (PP&E), while other cash outflows, like paying salary, taxes, etc., do not create any asset, and instead instantly create an expense on the income statement that reduces equity via retained earnings? Capital expenditures are capitalized because of the timing of their estimated benefits - the lemonade stand will benefit the firm for many years. The employees' work, on the other hand, benefits the period in which the wages are generated only and should be expensed then. This is what differentiates an asset from an expense. Walk me through a cash flow statement. Start with net income, go line by line through major adjustments (depreciation, changes in working capital and deferred taxes) to arrive at cash flows from operating activities Mention capital expenditures, asset sales, purchase of intangible assets, and purchase/sale of investment securities to arrive at cash flow from investing activities. Mention repurchase/issuance of debt and equity and paying out dividends to arrive at cash flow from financing activities. Adding cash flows from operations, cash flows from investments, and cash flows from financing gets you to total change of cash. Beginning-of-period cash balance plus change in cash allows you to arrive at end-of-period cash balance. What is working capital? Working capital is defined as current assets minus current liabilities; it tells the financial statement user how much cash is tied up in the business through items such as receivables and inventories and also how much cash is going to be needed to pay off short term obligations in the next 12 months. In banking, working capital is normally defined more narrowly as current assets (excluding cash) less current liabilities (excluding interest-bearing debt). Is it possible for a company to show positive cash flows but be in grave trouble? Absolutely. Two examples involve unsustainable improvements in working capital (a company is selling off inventory and delaying payables), and another example involves lack of revenues going forward in the pipeline. How is it possible for a company to show positive net income but go bankrupt? Two examples include deterioration of working capital (i.e. increasing accounts receivable, lowering accounts payable), and financial shenanigans I buy a piece of equipment, walk me through the impact on the 3 financial statements Initially, there is no impact (income statement); cash goes down, while PP&E goes up (balance sheet), and the purchase of PP&E is a cash outflow (cash flow statement) Over the life of the asset: depreciation reduces net income (income statement); PP&E goes down by depreciation, while retained earnings go down (balance sheet); and depreciation is added back (because it is a non-cash expense that reduced net income) in the cash from operations section (cash flow statement). Why are increases in accounts receivable a cash reduction on the cash flow statement? Since our cash flow statement starts with net income, an increase in accounts receivable is an adjustment to net income to reflect the fact that the company never actually received those funds