FP&A Exam part 2 sec 1

Current Ratio

Current Assets/Current Liabilties

Contribution Margin

Revenue not consumed by variable cost.

PV of Termination Value

PV(Interest Rate, Termination Period, Payment, -Termination Value)

Cash flow from financing

Increase(Decrease) long term debt from Balance sheet - Debt less Dividends Paid from inputs

Cost of Equity

Risk Free Rate + (Market Return - Risk Free Rate) * Beta

Cash flow from Investing

Capex Spend=Gross PPE, Changes in LT Asstes Liabilties (other Noncurrent Assets or Deferred Income Taxes other

Risk Free rate

Theoretical return on risk free investment. Often use US T-bill Rate

An asset costs $60 million and can be salvaged for $10 million after 2 years. It produces revenue and cash expenses that are listed in the downloadable worksheet. The organization's WACC is 11% and its effective tax rate is 34%. If the organization uses straight-line depreciation for this asset, what is the net present value of all after-tax cash flows for the project? Note: The salvage value is included in the Year 2 cashflows, but you will need to remove this for tax calculation purposes.

First, calculate annual depreciation. To straight line depreciation, you simply take the purchase price minus the expected salvage value and divide by the number of years. There is a straight line depreciation function in excel as well, where you would enter =SLN(Purchase Price, Salvage Value, Useful Life), or in this example =SLN(B3,B4,B5). Second, calculate the book value at year 2 by subtracting accumulated depreciation from the original purchase price. This value is the same as your expected salvage value from step 1.Third, determine if there is a gain on disposal. If the actual salvage value is the same as the expected salvage value then there will be no gain from the disposal. If the actual salvage value exceeded the expected, then a gain would result which would be subject to income tax in step 4. There is no gain on the disposal in this scenario. Fourth, Calculate income tax and income after tax. This is done by subtracting your cash expenses and depreciation from your revenue to get your income before tax. You can determine the tax due in each year by multiplying the income before tax by the tax rate provided (34% in this scenario). Income after tax is calculated by subtracting your income tax amount from your income before tax number. Fifth, Calculate your discounted after tax cash flow by adding back depreciation to the income after tax number you calculated in step 4 (depreciation is added back because it is a non-cash expense). Year 0 should be the $60 million investment. Year 2 will include the cash flow related to the salvage value as well. The NPV of years 1 through 2 after tax cash flows plus the year 0 cash outlay is the discounted cash flow for the project.

An FP&A Professional is working on a high level forecasted Income Statement for a Firm who will split their stock 2 for 1 during the next calendar year. Given the completed Income Statements for 2XX8 and 2XX9 and the partial line items for 2XX0, please complete the forecasted Income Statement. Assume that the revenue growth rate will remain constant. Using the downloadable worksheet, solve for the forecasted earnings per share for 2XX0?

First, calculate your revenue growth rate. Then calculate your average COGS %, SG&A % and the tax rate. These rates will be used to forecast many of the necessary line items. Fill in the necessary math to get Gross Profit, EBITDA and Income before Tax. The number of shares will double due to the 2 for 1 stock split. Divide your net income by the number of shares to arrive at your EPS for 2XX0.

Market Return

Average return on Stock Portfolio. Stock portfolio

Cash Ratio

(Cash&Cash Equivalents + Market Securities)/Current Liabilities

Quick Ratio

(Cash&Cash Equivalents + Short Term Invest + Acct Rec)/Current Liabilities or (Current Assets-inventories-other current assets)/current liabilities

Convertible bond after tax interest

(Conv bond value * Couponrate)* (1-eff. Tax rate)

EBITDA margin

(EBITDA ÷ Revenue)

Diluted EPS

(Net Income- Pref Dividends)+ (Convertible pref dividends + Convertble Bond after tax interest)/outstanding shares + diluted shares

EPS

(Net income - Pref Dividends)/Outstanding shares

Percentage Change

(New Value ÷ Old Value) - 1

PV of the Growth Period

(Payment Amount/Interest Rate-Growth Rate) X 1 -((1+Growth rate)/(1+interest Rate))^Growth Periods) =(B3/(B4-B6))*(1-((1+B6)/(1+B4))^B5)

WACC

(Weight of Debt * (Cost of Debt * (1-Marginal Tax Rate))) + (Weight of Equity * Cost of equity)

An organization sells its water purification systems for $100 per unit. They have a variable cost of $20 per unit and fixed costs of $350,000. They have created a base case of 9,000 units sold in the coming period, with a worst case of 6,000 units and a best case of 12,000 units. What is the best case contribution margin?

Contribution margin represents the portion of sales revenue not consumed by variable costs and therefore can contribute to the coverage of fixed costs. To find the best-case contribution margin, Calculate the best-case revenue by multiplying the best-case units sold by the unit price. Next, find the variable costs by multiplying the best-case units sold by variable cost per unit. Lastly, subtract variable costs from the revenue. The downloadable spreadsheet shows this done for each scenario, but only the best case needs to be completed to answer the question.

What is the correlation between student pretest and posttest scores that are provided in the following downloadable worksheet?

Correlation can be determined using the CORREL function, as shown in the downloadable worksheet. These results indicate a strong positive correlation relationship between the pre-test and post-test scores.

An organization monitors its debt to total assets to determine if its risk is increasing or decreasing. Using the balance sheets in the downloadable worksheet, what was the year-over-year percent change in debt to total assets for the organization for 2XX4?

Debt to total assets is calculated here as current portion of long-term debt plus long-term debt divided by total assets. This is done for each year, then the change in value is calculated as the new value divided by the old value minus one.

What is the diluted earnings per share for 2XX9 for an organization with $3.6 billion in convertible bonds with a bond coupon rate of 4.25%, convertible at a conversion price of $25 per share, and 50 million non-convertible preferred shares with dividends of $750 million? Note that this information along with a set of income statements is included in the downloadable worksheet.

Diluted EPS is calculated below in two parts: the numerator and the denominator. The numerator is then divided by the denominator to find the diluted EPS. The numerator is earnings available to common shareholders (net income less preferred dividends) plus convertible preferred dividends (there are none), plus convertible-bond after-tax interest. Convertible bond after tax interest is also calculated in a prior step (see the formula bar in the graphic below): it is the convertible bond value times the bond coupon rate times one minus the effective tax rate. The effective tax rate is income tax expense divided by income before tax. The denominator of diluted EPS is the weighted average shares issued and outstanding plus diluted shares. In this case this is the shares issued and outstanding plus the convertible bond shares. Convertible bond shares are calculated as the convertible bonds value divided by the convertible bond conversion price. Note that the basic earnings per share calculations also assume there were $750 million in preferred dividends in both years.

Given the following excerpts from the a company's income statement and balance sheet for 2XX2 and 2XX3 as well as some partially filled out information for their pro forma 2XX4 income statement, what is the organization's projected 2XX4 EBIT? Click for Worksheet. Note: This problem requires determining the 2XX4 depreciation. The exercise assumes that the company will acquire assets that have the useful lives that are the same for 2XX3 (i.e., assume that the useful lives of assets remains constant for purposes of this exercise).

Follow the below sequence of steps to reach the correct answer: 1 Determine the depreciation for 2XX3 by subtracting the 2XX2 Accumulated Depreciation from the 2XX3 Accumulated Depreciation (this is the same as Depreciation and Amortization on the income statement in this example). 2 Determine the depreciation rate for 2XX3 by dividing the depreciation for 2XX3 by the gross PP&E for 2XX2. Remember that the Balance Sheet is a snapshot at the end of the year. Gross PP&E ending for 2XX2 on the balance sheet would also be the starting Gross PP&E value for 2XX3. 3 Apply the rate in 2XX3 to the projected 2XX4 PP&E to get the depreciation expense. 4 Complete the remainder of the Projected 2XX4 Income Statement as shown and calculate the projected EBIT.

A project that costs $300,000 will return after-tax cash flows for five years and then be worth $100,000 in liquidation value in Year 6. The cash flows for the first five years can be viewed on the downloadable worksheet below. Note that these are all undiscounted cash flows including the liquidation value. The organization has a WACC of 4.25%. What is the internal rate of return for this project?

IRR is calculated using a worksheet as =IRR(values,[guess]), where values is the range of values and guess is an optional guess at what the IRR percentage should be. In the example below, a guess is not used, and the rate is calculated as 5.52%. Note that the Year 6 period was added and the liquidation value was added so that this cash flow could be included in the calculation. The IRR uses undiscounted cash flows, so the WACC is not used in the calculation. However, WACC is often used as a benchmark value to determine if the project is worthwhile. By this measure, the project is worthwhile because IRR exceeds WACC.

The government in which an FP&A Professional's Company is domiciled has recently passed new tax legislation. The complete effects of the legislation haven't been determined but it is believed that the corporate tax rate will go down, potentially to as low as the new statutory rate of 20%. Management has asked an FP&A Professional to perform a sensitivity analysis on all potential rates and report the effect that a 1% move in the rate will have on profit margin, using the downloadable worksheet, perform this analysis.

In order to answer the question, you will need to look at all potential combinations of tax rate and profit margin. The previous year's tax rate can be calculated from the data provided and results in a tax rate of 35%. The lowest potential tax rate for the new year is 20%. The next step is to build a new chart for every tax rate from 34% to 20% and calculate the net income and profit margin for each. Next, calculate the change in profit margin at each rate. The calculations at the 20% level are shown in the completed downloadable spreadsheet.

An FP&A Professional is working on an analysis on a product line that has been in decline. The forecasted decline for this product is 2% per year. If COGS stay at the same percentage of revenue and SG&A stays at the same dollar value, what will be the EBITDA margin in Year 3?

In order to determine the EBITDA margin (EBITDA ÷ Revenue) for Year 3, you must forecast each line item out based on the percentages laid out in the question. Sales are shrinking at a constant rate of (2%) each year. Calculate the COGS % of sales and then extrapolate that forward to Years 1 - 3. SG&A is flat for the duration of the forecast. The formulas for Year 3 are laid out in the solved downloadable worksheet.

An FP&A Professional is attempting to forecast financials for a current product line. Sales are expected to grow at 8% per year. The cost of goods sold is expected to grow faster at 11% annually due to raw materials constraint. SG&A expenses will remain flat for the next two years, then will increase 20% annually in Years 3 & 4 due to a new planned aggressive marketing campaign. Using the downloadable worksheet, What is the expected EBITDA margin in Year 4? (rounded to one decimal place)

In order to determine the EBITDA margin (EBITDA ÷ Revenue) for Year 4, you must forecast each line item out based on the growth rates laid out in the question. Sales and COGS are growing at a constant rate of 8% and 11% each year. SG&A is flat for the first 2 years, then grows at 20% each year for the last two years.

An Organization has developed a decision tree and has asked an FP&A Professional to assist in completing the analysis. A project is being contemplated currently, if they choose to make the investment it will cost the Company $120 million dollars. Once the investment is made there is a 55% chance that the best case will happen, which would be an annual cash flow of $55 million in Year 1, then increase by the rate of inflation through the 4th and final year of the investment. There is also a 45% chance that cashflows will only amount to $25 million in Year 1 and rise with the rate of inflation through the 4th and final year. The Company has a WACC of 7.25%. Using the downloadable worksheet, what is the NPV of the expected case?

In order to determine the net present value of the expected case, you first need to chart out the cash flows for each year with their respective growth rates. The expected case is calculated by taking the probability of the best case and the worst case and weighting them against the cash flow for each. The present value is calculated on the expected cash flows and netted against the initial investment in order to arrive at the net present value for the expected case. Calculations for the best case and expected case are shown in the solved downloadable worksheet.

As an analyst for a copper mine, you determine that a proposed mine will be terminated in Year 24 (the current year being Year 0), and that the mine will produce $24 million in after-tax cash flows in Year 24. This estimate was produced under the assumptions that copper prices will grow at an annual rate of 5.625% and that the organization's discount rate is 10.5%. What would the present value impact be if the mine were operable for an additional five years beyond its estimated termination date? (Rounded to the nearest hundred thousand)

In order to determine the present value of running the mine for an additional five years, first determine the cashflows for years 25 through 29 using the annual growth rate of 5.625%. Next, calculate the value at Year 24 for the cashflows from years 25 through 29. Lastly, discount the value calculated at year 24 back to today's value. NPV @year 24-Extra 5 years=NPV(B4,C13:G13). NPV today of extra 5 years (26-29) = Value at Year 24/(1+Discount Rate)^Termination year or use PV formula.

During the year end accounting close of 2XX9, you are given income statement data for 2XX8 and partially completed 2XX9 data. The tax rate to use for the year's income tax expense is still not finalized, but you need to provide a preliminary profit margin for analysis. You are asked to assume for now that the tax rate from 2XX8 will continue. In 2XX8, the statutory tax rate was 30% and the marginal tax rate was 23%. What is the 2XX9 profit margin?

Neither of the tax rates provided is the correct rate to use for income tax expense. The income tax expense is calculated as the effective tax rate from 2XX8, which is then assumed to continue for 2XX9. The effective tax rate = income tax expense/income before tax. The income before tax times this rate equals the 2XX9 income tax expense. Then the income before tax minus the income tax expense equals net income. Once net income is determined, divide this amount by the 2XX9 revenue to find the profit margin. Click Here to view the full solutions worksheet.

Cash Flow from Operating activities

Net Income Add back in Depreciation and Amorization then Changes in Working capital AR, Inventory, other Current Assets, AP, Accrued Expenses, Incoem taxes other

Return on Average Equity

Net Income/Average shareholder equity

A company is in the midst of their annual planning cycle. Their planning cycle consists of two parts, the status quo called base case and alternative case. The alternative case looks at different risks and opportunities (R&O) the company may face in the coming year. These R&Os are layered in the planned revenues, expenses, and cashflows. During the current planning year, the company determined their status quo base case cash flow balance as shown in the top part of the downloadable worksheet. Also during the planning cycle, the company identified the certain risks and opportunities with their respective cashflows and probabilities as shown in the bottom part of the downloadable worksheet. What is the risk adjusted ending cash balance by the end of the year?

The fastest way to arrive at the correct answer is to use the SUMPRODUCT function on the annual total risk and opportunities and their probabilities (as is shown in the formula bar in the calculation cell) then to add this line and the annual ending cash balance to arrive at the correct answer. The quarter-ending risk adjusted cash balance calculations are not necessary to find the full year ending cash balance.

An FP&A Professional is working on a sensitivity analysis for macroeconomic factors for next year's forecasted Income Statement. The forecasted Income Statement is included. The Professional has noticed that sales tend to move positively with GDP, capturing 75% of the GDP growth in sales growth. The current forecast assumes a growth rate of 0% for GDP. What would be the impact to the profit margin if GDP growth exceeds expectations by 1%?

The first step is to determine your percent of revenue for COGS, SG&A and the tax rate as these percentages will remain constant when forecasting the increase in sales from higher GDP growth. Next, you will calculate your sales growth rates. Since sales are positively correlated, but only capture 75% of GDP growth, you will need to multiply the capture rate by the GDP growth figure. For 1% GDP growth, the sales growth rate is 0.75%. Next, you will need to build your Income Statement figuring in the percentages previously calculated. The profit margin is simply net income divided by revenue. To calculate the change in profit margin, simply subtract the base case forecast from the 1% GDP growth.

Assume COGS, SG&A percentages and the tax rate remain the same. Assume planned depreciation and interest expense will not change.

The first step is to determine your percent of revenue for COGS, SG&A and the tax rate as these percentages will remain constant when forecasting the increase in sales from higher GDP growth. Next, you will calculate your sales growth rates. Since sales are positively correlated, but only capture 75% of GDP growth, you will need to multiply the capture rate by the GDP growth figure. For 1% GDP growth, the sales growth rate is 0.75%. Next, you will need to build your Income Statement figuring in the percentages previously calculated. The profit margin is simply net income divided by revenue. To calculate the change in profit margin, simply subtract the base case forecast from the 1% GDP growth. The solved downloadable worksheet shows several different iterations of GDP growth to show that the profit margin change is consistent, the later two iterations are not necessary to solve the problem.

You need to develop a weighted average cost of capital (WACC) for a financial projection for a proposed restaurant chain. As this line of business is a bit more risky than the overall market, the organization's beta is 1.15. Treasury has provided you with the return on an average stock portfolio and the U.S. T-Bill rate to use as a risk-free asset when calculating the cost of equity. They also provided you with the weight of debt, book values of debt, market values of debt, the marginal tax rate and the effective tax rate. These are provided in the downloadable worksheet. What is WACC for purposes of this projection?

The formula for the Weighted Average Cost of Capital (WACC) is as follows: WACC = (Weight of Debt * (Cost of Debt * (1-Marginal Tax Rate))) + (Weight of Equity * Cost of equity) To calculate the cost of equity, the capital asset pricing model (CAPM) equation must be used: Risk Free Rate + (Market Return - Risk Free Rate) * Beta. All calculations are shown as part of the solved worksheet, Click Here to view that worksheet. Note that the book value of debt and the effective tax rate are not used in the WACC equation. Also the weight of equity can be found by taking the inverse of the weight of debt.

An FP&A Professional is asked to perform an analysis on a potential long-term investment in an automated manufacturing line. The project has an upfront cost of $250 million dollars. The annual efficiencies gained are estimated to be $35 million in Years 1- 3 then rise to $54 million for Years 4 - 8 and finally decline to $40 million for the last 2 years. Using the downloadable worksheet, what is the profitability index for the expected annual future cash flows for this proposed investment?

The formula for the profitability index is: Present Value of Future Cash Flows ÷ Initial Cost. In order to calculate the PV of future cash flows, first lay out your chart by annual cash flow for the 10 years specified in the question. Next, calculate the present value of the cash flows using the NPV formula. Lastly, divide the present value of the cash flows by the initial investment to arrive at the profitability index. A ratio value greater than 1 means that the project would return more than it costs in current dollars. For more information, please see the solved downloadable worksheet.

Given the customer list in the downloadable worksheet, what is the operating income provided by the organization's highest operating margin customer?

The highest operating margin customer is the one with the highest operating income to revenue ratio. To calculate this, divide operating income by revenue for each customer. The customer with the highest ratio is GHI, which provides operating income of $9.6 million. Click Here to view the full solutions worksheet.

An organization introduced a new water filtration system that needed to be priced at $100 per unit to be competitive with a competitor's product. In 2XX3, they sold 5,000 units for revenue of $500,000, but fixed costs were $500,000 and variable costs were $150,000, for an operating income loss of $150,000. This next year, they still must charge $100 per unit, but have managed to reduce their fixed costs to $400,000 and their variable costs per unit from $30 to $25. They have projected a base case sales at 8,000 units, but also projected a 5,000 unit worst case and a 10,000 unit best case. After accounting for last years' $150,000 loss, what will the 2XX4 unit cost be if the base case is achieved?

The information on the 2XX3 loss is not necessary for calculating the unit cost for 2XX4 as this loss is a sunk cost. Therefore, the first step in calculating the base case unit cost is to multiply the variable cost per unit times the number of units. Then the variable cost plus the fixed costs are divided by the number of units to determine the unit cost. The spreadsheet shows the unit cost for the other two scenarios, but this information is not needed to calculate the answer.

Net present value

The present value of the cash inflows minus the present value of the cash outflows.

Given the income statement for ShopNow! in the downloadable worksheet, what is the common-size percent value for net income in 2XX2? (Assume the most typical common-size income statement and use a single-point estimate of the values for the equations rather than an average.)

The most typical common-size income statement arranges everything as a percent of revenue. Therefore the correct answer is the net income divided by the revenue. The downloadable worksheet shows the common size calculated for each line item, but only the net income line needs to be calculated to find the correct answer.

Your organization can invest $10 million in Project A to use diamond particles to manufacture diamond tooth saw blades. It creates these particles as a by-product of its synthetic diamond manufacturing. An alternate proposal, Project B, is to process the particles further into dust to sell as a raw material, which will also cost $10 million to develop. If Project A is pursued, there is a 70% chance it can develop the product with a high contribution margin, but this leaves a 30% risk that it will fail to do so and have a product that is more expensive to produce. Project B's value depends almost entirely on the market price of diamond dust, and there is a 50/50 chance this will be favorable. Each project is studied over a three year period (see the undiscounted revenue figures in the downloadable spreadsheet). The organization's WACC is 10%. Round to the nearest whole dollar. What is the expected value of the opportunity cost of pursuing Project A?

The opportunity cost of pursuing Project A is the inability to invest in Project B, since using the by-product for one use prevents it from being used for the other use. The expected value of Project B is the net present value of the high market price times the probability plus the NPV of the low market price times its probability. Note that NPV is used for periods 1 to 3 only, and the period 0 amount is added back separately. This solutions worksheet shows how a SUMPRODUCT formula can be used to calculate the correct answer once the NPVs have been calculated.

An orange grove farming company with a WACC of 6.8% can purchase some orange groves that should return $200,000 at the end of each year for ten years and be saleable at the end of ten years for $6 million. This purchase is consistent with its risk profile. Alternatively, it can invest the same amount of funds in a new grove that will take ten years to mature before it bears any fruit but could be sold at that time for $10 million. This venture has a risk premium of 2% because it has a riskier profile than its current operations. If these projects are mutually exclusive, what is the opportunity cost of engaging in the more profitable investment?

The opportunity cost of the more profitable investment is the present value of the less profitable investment. The PV of the existing grove uses WACC and 10 periods, a payment of -$200,000, and a FV of -$6 million. This results in $4.5 million, the higher of the two values. The PV of the new grove uses WACC plus the risk premium as the rate, 10 periods, no payment, and a FV of -$10 million. This is the correct answer as it is the lower of the two values and is the amount being given up to pursue the other opportunity.

ShopNow! retail stores are projecting a large relative decrease in the cost of goods sold for 2XX4 due to some new supplier agreements and new investments in store brands. Given the income statements and statements of cash flows included in the downloadable worksheet link, how much cash flow is available to the organization to distribute as regular and/or special dividends (assuming it doesn't borrow funds to pay a dividend) in 2XX4? Note that the 2XX4 statement of cash flows is partially completed because they have not yet determined the amount of their dividend payout.

The question is asking you to determine the formula you should use to calculate cash available to distribute as dividends. The 3 options are free cash flow (FCF), free cash flow to the firm (FCFF), and free cash flow to equity (FCFE). The question asks you to assume that no borrowing will be done to fund the dividend, so you can exclude FCFE as it factors in cash that is raised from debt issuance. FCFF includes interest expense and is used as a gauge of all cash available, generated by the business, that is available to pay all debt and equity holders. As ShopNow! Would not forgo required interest payments to debt holders to fund the dividend, we can eliminate FCFF. The correct formula to use is the FCF as it provides free cash generated by the business that is not part of required debt payments or has been raised from debt issuance. Click Here to view the full solutions worksheet.

An organization produces a budget and later records some actual results, both of which are shown on the downloadable worksheet. What is the organization's operating income sales-volume variance? Note: express an unfavorable variance as a negative value and a favorable variance as a positive value.

The sales volume variance for operating income is the flexible budget operating income minus the static budget operating income. Therefore to calculate the sales-volume variance, you need to construct a flexible budget, which uses actual units sold but uses budget amounts for all other figures. The direct labor is the units sold times the direct labor hours per unit times the direct labor rate for the appropriate column. Once the operating incomes are determined, the flexible budget less the static budget results in the sales-volume variance.

Given the 2XX3 income statement and balance sheet and partially completed 2XX4 pro forma statements in the downloadable worksheet, what is the net PP&E for 2XX4?

To calculate pro forma net PP&E for 2XX4, first determine the accumulated depreciation by starting with 2XX3 accumulated depreciation and adding the 2XX4 pro forma depreciation and amortization from the income statement as shown in the formula bar in the graphic below. Net PP&E is then the gross PP&E minus the accumulated depreciation.

Present Value

The value of an expected future investment or cash flow today. Used when a deciding whether to make an investment at the present time that will pay cash flows in the future.

An organization is considering purchasing a used robotic assembly machine with three years of useful life left. The organization's WACC is 10%, and expects to receive $3 million in gross profit from the robot in the first year, with compound growth in gross profit of 5% per year for the following two years. Selling, General, and Administrative (SG&A) costs allocated to this robot, including maintenance costs, should be 15% of gross profit. The robot should have no residual value after three years and the organization uses straight-line depreciation. The income tax rate is 30%. What is the most the organization should pay for this robot? Click for Worksheet Note: While you could use a function such as Goal Seek to find the solution to this problem, Goal Seek is not available on the exam. Therefore, a trial and error process may be necessary.

This is an exercise in determining the asset purchase price that equals a positive net present value (NPV) that is as close to zero as can be found in terms of after-tax cash flows. To find the maximum asset purchase price using trial and error, enter a guess price so you can ensure your later calculations are working correctly. First, calculate the gross profit for each year. In Year 1, this is $3 million. In Years 2 and 3, it is calculated as Prior Year * (1 + Growth Rate). In the next row, calculate the SG&A costs as the year's gross profit times 15%. In the third row, calculate the depreciation. For Year 1, this will be =SLN(B11,(B11*B5),B6) and the formula is the same for the other two years. In the fourth row, calculate the income before tax as the gross profit less the SG&A costs less the depreciation expenses. In the fifth row, calculate the Tax expense; which is income before tax multiplied by the tax rate provided. In the sixth row, calculate the net income by subtracting the tax expenses from the Income before Tax. In the next row, calculate the after tax cash flow as the income after tax plus the depreciation deducted in the third row (remember that depreciation is a non-cash expense). Finally, calculate the NPV as shown in the spreadsheet in cells E19:F19. Once this model is working correctly, continue to increase or decrease the asset purchase price incrementally until you find the value that is as close to an NPV of zero as can be found without resulting in a negative NPV.

Given the income statements and balance sheets in the downloadable worksheet below, what is this organization's net cash flow from investing activities that would be reported on the 2XX4 statement of cash flows?

To calculate the 2XX4 cash flows from investing activities, start with the change in gross PP&E. The 2XX4 value less the 2XX3 value is the change in gross PP&E. The formula bar shows how this amount is negated for the Capex Spend line item. The next line item is similarly the negative amount of the change in other noncurrent assets. The third line item is the positive amount of the change in deferred income taxes and other, which happens to be zero in this case. The sum of these line items is the net cash flow from investing activities. Note that the information on the income statements are not needed to answer this question.

An organization has an opportunity to invest $200 million in a new restaurant chain. They estimate that if the chain does well, it will produce $110 million in after-tax cash flow per year for 5 years, but there is a 30% chance it could do poorly and cause a $10 million loss in after-tax cash flow per year over the same period. The organization's WACC is 10% but they consider this investment to be riskier than their normal line of business and so assess a 3% risk premium. An analyst points out that if the chain does poorly in its first year, it could be sold in its second year. They estimate that they could get $30 million for the investment in the second year in that case if they sell it. In terms of NPV, what is the difference between the expected value of the initial scenario and the abandon option?

To calculate the difference in the expected value NPVs, you can copy the table and paste it below the initial table. Next, calculate the NPV of the initial scenario for the expected value line. This is calculated as =NPV((B2+B3),D10:H10)+C10. Next, replace the period 2 worst case with $30 million and enter $0 for periods 3 through 5 for the worst case. Then for periods 2 through 5, recalculate the expected value of the after-tax cash flows. To do this, you can use SUMPRODUCT. Once the expected value of the changed cash flows is determined, take the NPV of this row. The difference between expected values is the correct answer. Click Here to view the full solutions worksheet

An organization is planning to spend $120 million on an asset that should produce $56 million in after-tax cash flows in the first year. After-tax cash flows are expected to grow at a rate of 12% per year for two years after that. The organization's WACC is 9.5%. What is the asset's discounted payback period?

To calculate the discounted payback period, first calculate the after-tax cash flows for the second and third years. This is Prior Year * (1 + After-Tax Cash Flow Growth Rate). This creates compound growth. Next, calculate the discounted cash flow per year. This is the present value of the cash flow. For example, the Year 1 amount is calculated as =PV(B2,C4,0,-C5). On the third row, calculate the cumulative discounted cash flow. This is the initial investment plus the first year discounted cash flow for the first year, then it is the prior year cumulative discounted cash flow plus the current year discounted cash flow. Finally, calculate the discounted payback period. The formula bar shows an automated method of arriving at this rate regardless of what year the payback occurs. Another way to do it is to manually determine when the cash flows turn positive, then use a simplified version of the formula, such as =(0-D7/E6)+D4. Click Here to view full solutions worksheet.

Given information from the following downloadable worksheet on net income and equity for your own organization for three competitors, what is the percent change trend in return on average equity for the organization with the best trend?

To calculate the percent change in the ROE, first calculate the return on average equity for each competitor for 2XX2 and 2XX3. (Average Equity is used because Net Income is full year cumulative while Equity is a pinpoint in time. By using the starting and ending balances and averaging them you match up time periods.) The AVERAGE function can be used to average the equity values. For example, the calculation for cell D6 below is =D4 ÷ AVERAGE(C5:D5). When this is done for each competitor, calculate the percent change between 2XX2 and 2XX3 using (New Value ÷ Old Value) - 1 to calculate the percent change. Since a larger value for ROE is better, the organization with an increasing trend, Competitor A, has the best percent change trend. Click Here to view the full solutions worksheet.

An organizations new subsidiary is expected to return $350 million next year and grow in perpetuity at a rate of 4% each year. It is valued in this way for a period of ten years and in Year 11 it has been assigned a termination value of $2 billion. The organization's WACC is 5.5%. What is the present value of this subsidiary?

To calculate the present value of this subsidiary is done in two parts, first the present value of the perpetual growth annuity is calculated and then then present value of the termination value is calculated. The total of these two values is the present value of the subsidiary.----- PV of the Growth Period= (Payment Amount/Interest Rate-Growth Rate) X 1 -((1+Growth rate)/(1+interest Rate))^Growth Periods) =(B3/(B4-B6))*(1-((1+B6)/(1+B4))^B5).-----PV of Termination Value = PV(Interest Rate, Termination Period, Payment, -Termination Value)

A company forecasts its revenues on a three-month rolling basis, placing three times the weight on the most recent month and progressively less weight on each remaining month. Given the actual revenue results for January, February, and March in the following downloadable worksheet, what is the three-month moving average forecast of revenue for June?

To calculate the three-month moving average, first determine the weightings. The sum of 1 + 2 + 3 = 6, so the oldest month is 1/6, the middle month is 2/6 and the newest month is 3/6. These have been calculated below as decimal values. Then, the weighted moving average for April is calculated first, as January * 1/6 + February * 2/6 + March * 3/6. May and June are similarly calculated but the months roll forward by one month each.

An investment of $10,000,000 does not pay a return until the fourth year, but then it should repay $40,000,000. The historical WACC for the organization is 9% while the market value WACC is 11%. By what percentage would the fourth year payment need to be reduced before the investment would have a net present value (NPV) of $0?

To determine at which point a future return would have an NPV of zero, you can use future value (FV) against the initial investment and the number of periods in which the return will occur. In this case, the rate to use is the market value WACC (historical WACC is not used). The number of periods is 4, there is no payment, and the present value is the investment amount (since it is already a negative value, it doesn't need to be entered with a minus sign). The future value at the discount rate is the amount of return that will return an NPV of zero. To determine the change in value of this investment it is calculated as:c= (New Value ÷ Old Value) - 1 = ($15,180,704 ÷ $40,000,000) - 1 = 62%.

An organization has the opportunity to invest $25 million in a plastic coatings venture that should return $10 million in the first year and incrementally increase this return by $5 million per year over six years. The organization foresees that to continue this growth rate they will need to make a follow on investment of $30 million in the fourth year. The organization has historically had a WACC of 8% but interest rates are rising and its market-value WACC is 9%. What is the net present value of this opportunity?

To find the answer, use the market value WACC as the discount rate, then take the NPV of the cash flows for periods 1 through 6, then add back the period 0 investment, then add back the present value of the future investment. Alternately, you can use the net difference in cash flows for period 4 and use it in the NPV equation. In either case, the period 0 investment must be added back at the end or each valuation will be off by one period. Click Here to view the full solutions worksheet.

An electronics store divides their staff into sales floor and cost center personnel for headcount analysis. The current budget has 90 sales floor full time equivalents (FTEs) and 78 cost center FTEs per quarter. The organization wants revise headcount to improve profitability. They find 10 cost center staff who can work the sales floor and transfer these employees to the sales floor in the second quarter. In the last quarter, they transfer these employees back, but hire 10 more FTE sales floor temporary employees for the holidays. The original and revised pro forma income statements are contained in the downloadable worksheet. What is the percent change in EBIT per employee as a result of these staffing changes?

To find the correct answer, first sum the total headcount per quarter for the original and revised headcounts. Then project the EBIT per employee for each quarter by dividing quarterly EBIT by quarterly headcount in each instance. Then sum these quarterly values for the original pro forma income statement, and separately for the revised pro forma income statement. Then calculate the percent change between these year sums as (New Value ÷ Old Value) - 1

Given the raw monthly data for 2XX2, 2XX3, and 2XX4 in the following downloadable worksheet, what is the December 2XX4 de-seasonalized data value?

To find the de-seasonalized data for the month of December 2XX4, first find the year average for each of the years. This can be found using the AVERAGE function. For example, the average for 2XX2 (cell B16) is =AVERAGE(B4:B15). Next calculate an annual index for each of the Decembers by taking the raw data value for that month and year and dividing it by that year's average. For example, the December 2XX2 index (cell E15) is =B15/B16. After this is done for each of the Decembers, take the average of the annual indices to find the average seasonal index. For December (cell H15), this is =AVERAGE(E15:G15). Finally, divide the December 2XX4 raw data value by the average seasonal index for December, as is shown in the downloadable worksheet. Note that the graphic below has calculated the indices for each month, but these other months are not necessary to complete to find the correct answer.

Given the raw and de-seasonalized monthly unit sales data for 2XX6 along with the average seasonal index in the following downloadable worksheet, what is the June 2XX7 re-seasonalized forecast if a six-month moving average is used for forecasting?

To find the forecasted re-seasonalized value for June 2XX7, first apply the six-month moving average to the de-seasonalized data to project it forward to June. This can be done using the AVERAGE function using a range going back to the prior six months. For example January 2XX7 (cell D15 is =AVERAGE(D9:D14). The range rolls forward for each month. Once the de-seasonalized June forecast is determined, multiply this value by the June 2XX7 average seasonal index to find the re-seasonalized June 2XX7 forecast as is shown in the downloadable worksheet. Note that the exhibit shows the entire year of 2XX7 completed, but filling in these additional values is not needed to find the correct answer.

An organization has a risk-adjusted WACC of 12% for a potential investment of $110 million that could return $35 million at the end of each year for 8 years as a base case (a 45% chance), but in the worst case could cost the organization $11 million in annual after-tax cash flows over the same period (25% chance). In the best case it could return $55 million over the same period. Assuming that these are the only three potential scenarios and there is no possibility of terminating the project early in the worst case, what is the probability-adjusted net present value of this project?

To find the probability-adjusted Present Value, first determine the probability of the base case, which is 1 minus the worst case and best case probabilities. Next use SUMPRODUCT to calculate the expected value of the cash flows, =SUMPRODUCT(B8:B10,C8:C10). Finally, use PV to calculate the present value of this ordinary annuity, subtracting the initial investment, as shown in the downloadable worksheet.

Given the pro forma balance sheet for ShopNow! in the following downloadable worksheet, what is the quick ratio for 2XX4?

To find the quick ratio for 2XX4, add cash and cash equivalents plus short-term investments (there are none) plus the accounts receivable and divide by the total current liabilities as is shown in the worksheet.

Refer to the bridge between the January budget and January actuals in the downloadable worksheet. Note that the changes columns (C through H) are the change in value from budget to actual. Assume that the January budget had $99 budgeted for depreciation expense and $3,002 budgeted for amortization but that the Other category had $24 less depreciation than was budgeted and Product 3 had $722 less amortization than was budgeted. What is the actual operating profit for January?

To get to operating profit or EBIT from EBITDA, add a depreciation expense row below EBITDA and record the $99 budgeted amount (in column B) and -$24 in the Other column (column H). Next add an amortization row below the depreciation expense row, and record the $3,002 budgeted amount and the -$722 change in value for Product 3. Sum each of these lines in the Actuals column. Operating profit is EBITDA minus depreciation expense minus amortization, as is shown in the downloadable worksheet.

An organization had $68.8 billion in revenue in 2XX5 and a gross margin of 35.7%. They project that revenue will grow by 16% for next year. What is the projected cost of goods sold (COGS) for 2XX6?

To project 2XX6 COGS, first determine 2XX6 revenue. This is Prior Year * (1 + Revenue Growth Rate). Next, multiply the gross margin times the 2XX6 projected revenue to find the projected gross profit. The projected revenue less the projected gross profit equals the projected COGS. Click Here to view the full solutions worksheet.

An Organization has developed a decision tree and has asked an FP&A Professional to assist in completing the analysis. A project is being contemplated currently; if they choose to make the investment, it will cost the Company $120 million dollars. Once the investment is made, there is a 60% chance that the best case will happen, which would be an annual cash flow of $45 million in Year 1, then increase by the rate of inflation through the 4th and final year of the investment. There is also a 40% chance that cashflows will only amount to $22 million in Year 1 and rise with the rate of inflation through the 4th and final year. The Company has a WACC of 8.15%. What is the PV of the best case cashflows (shown as X on the decision tree)?

When constructing a decision tree, each potential outcome is assigned their own present value of the associated cashflows. The present value of the best case would represent just those cash flows from Year 1 to Year 4. To calculate the net present value, you would add the project cost in Year 0 to your present value of the cash flows. Additional calculations have been made in the solved downloadable worksheet that are not necessary to answer the question. The calculations for the best case are shown.