In excel please

Questions and problem sets (2) EXERCISE Badmonth- - a producer of tires - considers building an additional plant for the production of tires. The following information is available. Note: all monetary values are in real (that is, inflation-adjusted) terms. (1) Total capital expenditures are 500 million. They are spread over three years, with first-year (1-0) expenditure, second-year expenditure, and third-year expenditure accounting for 60%, 30%, and 10% of the total, respectively. (2) Operation is expected to start in the fourth year (t=3), generating an annual stream of revenue of 130 million for 13 years. (3) After 13 years of production, the plant will have reached the end of its life and production ends. The plant has no residual (scrap) value. (4) Annual operating and maintenance cost comprise three items: (i) energy cost of 4 million (of which 1 million reflect a tax on energy that is levied for fiscal reasons and not to discourage energy consumption), (ii) labor cost of 22 million, (iii) and raw material cost that amount to 40 million in the first year of operation, but increase at a real rate of 2% p.a. thereafter. (5) The time profile of profit taxes is as follows (figures in million): t=0 t=1 t=2 t=3 t=4 t=5 t=6 t=7 t=8 t=9 t=10 t=11 t=12 | t=13 | t=14| t=15 0 4.61 4.49 4.36 4.23 4.10 3.97 3.83 3.69 3.54 3.39 3.24 3.08 2.92 0 0 (6) Half of the capital expenditure is financed with a loan, carrying an interest rate (in real terms) of 1%. The loan is taken in the first year (t=0). Loan repayment is in equal annual installments, starting in the first year of operation and ending in the last year. Interest rate obligations in anyone year are determined by the loan amount outstanding at the beginning of that year (that is, the end of the previous year). (7) Like most other production processes, the production of tires is not without environmental impacts. Suppose that they cause environmental cost of 12 million p. a. - cost which do not fall on Badmonth but society at large. (8) The plant will be built in a region characterized by high unemployment. As a result, the economic cost of labor are estimated to be lower than what firms have to pay. Estimates suggest that the economic cost of labor (that is, labor cost at so-called shadow wages) amount to 90% of the actual wage bill. EXERCISE (cont.) Tasks: (1) Set up a table/spreadsheet that shows all conceivable project inflows and outflows following from the information above. (2) Take a total-capital perspective before profit tax and determine the net flow (difference between inflows and outflows) that accrues to the project. (3) Show how the net flow determined under (2) is shared between the owners of Badmonth, its lenders, and the government. (4) Assess the financial profitability of this project on the basis of its NPV for a real (that is, inflation-adjusted) discount rate of 3%. (5) Assess the economic profitability of this project on the basis of its NPV for a real (that is, inflation-adjusted) discount rate of 3%. Interpret your results [Hint: the interpretation is made easier by considering the reasons for the difference between the project's financial and its economic profitability "one-by-one" rather than in "one go"]. EXERCISE (end) Tasks: (6) What is the effect on the financial NPV in case of economic policies that make the environmental cost fall on Badmonth rather than society at large? (7) What are the economic consequences and policy implications from the investment decision that Badmonth will take in light of (6)? [Hint: Part 1 and 2 of the course used a diagram with financial profitability on the vertical axis and economic profitability on the horizontal axis. This diagram might be useful for answering this question.] (8) In a variation to the information above, assume that the energy tax is imposed with a view to discouraging the use of energy because energy consumption is believed to cause environmental cost not borne by energy users (in addition to the 12 million already accounted for). Further assume that energy tax obligations of 1 million adequately reflect these costs. What is the impact on the project's financial and its economic profitability ? Questions and problem sets (2) EXERCISE Badmonth- - a producer of tires - considers building an additional plant for the production of tires. The following information is available. Note: all monetary values are in real (that is, inflation-adjusted) terms. (1) Total capital expenditures are 500 million. They are spread over three years, with first-year (1-0) expenditure, second-year expenditure, and third-year expenditure accounting for 60%, 30%, and 10% of the total, respectively. (2) Operation is expected to start in the fourth year (t=3), generating an annual stream of revenue of 130 million for 13 years. (3) After 13 years of production, the plant will have reached the end of its life and production ends. The plant has no residual (scrap) value. (4) Annual operating and maintenance cost comprise three items: (i) energy cost of 4 million (of which 1 million reflect a tax on energy that is levied for fiscal reasons and not to discourage energy consumption), (ii) labor cost of 22 million, (iii) and raw material cost that amount to 40 million in the first year of operation, but increase at a real rate of 2% p.a. thereafter. (5) The time profile of profit taxes is as follows (figures in million): t=0 t=1 t=2 t=3 t=4 t=5 t=6 t=7 t=8 t=9 t=10 t=11 t=12 | t=13 | t=14| t=15 0 4.61 4.49 4.36 4.23 4.10 3.97 3.83 3.69 3.54 3.39 3.24 3.08 2.92 0 0 (6) Half of the capital expenditure is financed with a loan, carrying an interest rate (in real terms) of 1%. The loan is taken in the first year (t=0). Loan repayment is in equal annual installments, starting in the first year of operation and ending in the last year. Interest rate obligations in anyone year are determined by the loan amount outstanding at the beginning of that year (that is, the end of the previous year). (7) Like most other production processes, the production of tires is not without environmental impacts. Suppose that they cause environmental cost of 12 million p. a. - cost which do not fall on Badmonth but society at large. (8) The plant will be built in a region characterized by high unemployment. As a result, the economic cost of labor are estimated to be lower than what firms have to pay. Estimates suggest that the economic cost of labor (that is, labor cost at so-called shadow wages) amount to 90% of the actual wage bill. EXERCISE (cont.) Tasks: (1) Set up a table/spreadsheet that shows all conceivable project inflows and outflows following from the information above. (2) Take a total-capital perspective before profit tax and determine the net flow (difference between inflows and outflows) that accrues to the project. (3) Show how the net flow determined under (2) is shared between the owners of Badmonth, its lenders, and the government. (4) Assess the financial profitability of this project on the basis of its NPV for a real (that is, inflation-adjusted) discount rate of 3%. (5) Assess the economic profitability of this project on the basis of its NPV for a real (that is, inflation-adjusted) discount rate of 3%. Interpret your results [Hint: the interpretation is made easier by considering the reasons for the difference between the project's financial and its economic profitability "one-by-one" rather than in "one go"]. EXERCISE (end) Tasks: (6) What is the effect on the financial NPV in case of economic policies that make the environmental cost fall on Badmonth rather than society at large? (7) What are the economic consequences and policy implications from the investment decision that Badmonth will take in light of (6)? [Hint: Part 1 and 2 of the course used a diagram with financial profitability on the vertical axis and economic profitability on the horizontal axis. This diagram might be useful for answering this question.] (8) In a variation to the information above, assume that the energy tax is imposed with a view to discouraging the use of energy because energy consumption is believed to cause environmental cost not borne by energy users (in addition to the 12 million already accounted for). Further assume that energy tax obligations of 1 million adequately reflect these costs. What is the impact on the project's financial and its economic profitability