Risk and Uncertainty in Finance WS 2016/2017 Sensitivity Analysis in MS Excel Cash Flow Sensitivity Analysis A company plans to invest in a new production line used for the production of just one product. Expected volume of sales of the new product in the first year should reach 100 000 units. Its price is estimated on the level of 3000 EUR/unit. Variable production costs are 2000 EUR unit and fixed costs (without amortization) are 50 Mil. EUR. The initial investment will be 80 mil. EUR. Project duration is 10 years. The income tax is 19%. Tasks: 1. Calculate the cash flow in the first year of the investment 2. Draw a graph projecting cash flow sensitivity to 10% change in each individual input variable. A horizontal axis x presents a percentage decrease of the cash flow and a vertical axis y presents the risk factors from the most to the least significant. 3. Interpret the graph from the exercise 2. 4. Construct a graph depicting cash flow sensitivity by 10% increase of each individual variable-tornado graph 5. Interpret the graph from the exercise 4. 6. Create a table presenting the effects of the three most significant risk factors on the cash flow. The values changes from -20% to 20% by 5%. 7. Create a spider graph using the information from the exercise 6. A horizontal axis x should present a percentage change of a risk factor and a vertical axis y an appropriate cash flow value. Moreover, the graph should depict an influence of the three most significant risk factors. Interpret the results. 8. Calculate a relative cash flow change by changes of the three most significant factors from -20% to 20% by 5% 9. Construct a spider graph using the data from the exercise 8 10. Calculate the zero point for the price and for the production 11. Create a graph of the cash flow sensitivity to the price. A horizontal axis should present the price from 2000 to 3500 EUR by 100 and a vertical axis y should depict the cash flow values S-yearly sale volume of the product P-unit price V-unit variable costs F-fixed costs I-initial investment T-project duration D-income tax rate CF - calculated cash flow Risk and Uncertainty in Finance WS 2016/2017 Sensitivity Analysis in MS Excel Cash Flow Sensitivity Analysis A company plans to invest in a new production line used for the production of just one product. Expected volume of sales of the new product in the first year should reach 100 000 units. Its price is estimated on the level of 3000 EUR/unit. Variable production costs are 2000 EUR unit and fixed costs (without amortization) are 50 Mil. EUR. The initial investment will be 80 mil. EUR. Project duration is 10 years. The income tax is 19%. Tasks: 1. Calculate the cash flow in the first year of the investment 2. Draw a graph projecting cash flow sensitivity to 10% change in each individual input variable. A horizontal axis x presents a percentage decrease of the cash flow and a vertical axis y presents the risk factors from the most to the least significant. 3. Interpret the graph from the exercise 2. 4. Construct a graph depicting cash flow sensitivity by 10% increase of each individual variable-tornado graph 5. Interpret the graph from the exercise 4. 6. Create a table presenting the effects of the three most significant risk factors on the cash flow. The values changes from -20% to 20% by 5%. 7. Create a spider graph using the information from the exercise 6. A horizontal axis x should present a percentage change of a risk factor and a vertical axis y an appropriate cash flow value. Moreover, the graph should depict an influence of the three most significant risk factors. Interpret the results. 8. Calculate a relative cash flow change by changes of the three most significant factors from -20% to 20% by 5% 9. Construct a spider graph using the data from the exercise 8 10. Calculate the zero point for the price and for the production 11. Create a graph of the cash flow sensitivity to the price. A horizontal axis should present the price from 2000 to 3500 EUR by 100 and a vertical axis y should depict the cash flow values S-yearly sale volume of the product P-unit price V-unit variable costs F-fixed costs I-initial investment T-project duration D-income tax rate CF - calculated cash flow