Case Study: Pro Forma Forecasting and Capital Budgeting Techniques You are a financial analyst at XYZ Corporation. The company is considering investing in a new project, Project Z. Below are the details for Project Z: Initial investment: $500,000 Project life: 5 years Annual revenue: $200,000 Annual operating expenses: $70,000 Depreciation (straight-line): $100,000 per year Required rate of return: 10% Tax rate: 30% Additionally, XYZ Corporation has the option to invest in Project Y, an alternative project with the following details: Initial investment: $400,000 Project life: 5 years Annual revenue: $180,000 Annual operating expenses: $60,000 Depreciation (straight-line): $80,000 per year Required rate of return: 10% Tax rate: 30% Requirements: Pro Forma Financial Statements (10 points) 1. Create the pro forma income statements for both Project Z and Project Y for each of the 5 years. Calculate the after-tax cash flows for each project. 2. Capital Budgeting Analysis (10 points) Calculate the NPV, IRR, and PI for both projects. Differentiate between relevant cash flows and sunk costs in your analysis. 3. Opportunity Cost and Ranking Problems (10 points) Consider the opportunity cost of capital and incorporate it into your analysis. Compare the two projects and make a recommendation based on your analysis. Discuss any ranking problems that arise and how you would address them. 4. Scenario and Sensitivity Analysis (10 points) Perform a scenario analysis by considering a 10% increase and a 10% decrease in annual revenue for both projects. Conduct a sensitivity analysis on the operating expenses by increasing and decreasing them by 10% for both projects. Answer the following 4 questions using excel sheet with formula with like NPV, IRR, and PI show all calculation.