Question

Capital Budgeting Risk Analysis Project Instruction Create the pro forma income statement and estimate the cash flows for the following project. Decide whether to accept this project based on analysis of the NPV, Profitability Index, and IRR. Project Assumptions (Base Case) Equipment Life 6 Years Initial Equipment Cost $2,500,000 in year 0 Depreciation Straight Line Method Initial Revenue $1,000,000 in year 1 Revenue growth rate year 2 10% Revenue growth rate year 3 15% Revenue growth rate year 4 10% Revenue growth rate year 5 5% Variable Costs 60% of this year's revenue Fixed Costs $75,000 in year 1 Fixed Costs Inflation Rate 3% per year Long-term growth rate 2% per year Net Working Capital 4% of next year's revenue Tax Rate 35% Discount Rate 18% Model Structure Since this project does not have an end date we need to decide how many years of detailed analysis we will conduct. For this assignment, we will estimate detailed cash flows for 5 years and estimate the terminal value at the end of year 5. The Income Statement is the building block for cash flow estimation. Your income statement should contain the following items. You may include additional items if you find them useful in your model. Revenue Variable Cost Gross Profit Cash Fixed Cost Depreciation EBIT (Earnings Before Interest and Tax) Tax Net Income Cash Flows: • Operating Cash Flow = EBIT + Depreciation – Taxes • Other cash flow items o initial investment o change in Net Working Capital o Terminal value   Analyses 1. Scenario Analysis: prepare a scenario summary report. Use the values in the original assumption as the base case and add the following two cases. Case 1 (worst case) Variable Costs 65% of this year's revenue Fixed Costs $80,000 in year 1 Fixed Costs Inflation Rate 5% per year Long-term growth rate 1% per year Case 2 (best case) Variable Costs 55% of this year's revenue Fixed Costs $70,000 in year 1 Fixed Costs Inflation Rate 3% per year Long-term growth rate 3% per year 2. Sensitivity Analysis: prepare a one-way data table. Make sure to use the base case values. Allow the long-term growth rate to vary from -3.0% to +3.0% in increments of 0.5%. Show the impact on NPV and IRR. 3. Breakeven Analysis: identify the initial revenue level that will result in $0 NPV. Things to turn in: 1. A one-page memo explaining the results of your analysis and your recommendation. The memo should include important results of your analysis such as a summary table or graph. The memo is limited to one page so be very selective on what information to include. 2. An Excel spreadsheet showing the following: • Entire model for the base case • Scenario Analysis (Scenario Summary Report) • Sensitivity Analysis (Data Table) • Breakeven Analysis (Goal Seek result) Check Figures (Base case): Model Year 0 1 5 6 Pro Forma Incremental Income Statement Revenue 1,000,000 1,461,075 1,490,297 Net Income (59,583) 54,178 Pro Forma Incremental Balance Sheet Net Working Capital 40,000 44,000 59,612 Pro Forma Incremental Cash Flows Total Net AT CF (2,540,000) 353,083 3,463,856

Answer 1

Lets consider the question,

That's Cash Flows for year 0. That one is correct!

Now let's consider the years,

So that tallies with your figures too! The revenue calculation for the 6th year at the long term growth rate of 2% is also correct! You only have to add the growth in revenues to the revenue of previous years.

Now, moving on to other parts of the table. The next row is the Net Income. Its given below: