ISME

Explore - Experience - Excel

Project Analysis using Excel based Financial Modelling

(Case Study – Capital Budgeting Modelling)
KIRAN KUMAR K V

Joe Vettin, was a senior consultant with InBank PLC, a highly preferred global investment banker. In the Corporate Consulting Division. One of the Bank’s regular client – Hynesse Ltd – a leading auto Producer in Italy is considering moving a part of its manufacturing process to Vietnam. The company is looking at benefitting due to lower cost of labour, land, taxes and raw materials at the host country. The project was proposed by a team of company’s strategy team. The same was presented to the Board for approval. Several board members were not convinced about financial viability of the project. They suggested that there is a need to build a financial model for project appraisal with worst, base and best case scenario for 10-years. The strategy team approached the consultant firm. And the Joe was assigned the project and was provided the below Information relevant for the model building:

1.      Savings in cost of producing one car can be computed and thus a capital budgeting exercise can be conducted for the financial viability.
2.      Expected long term inflation rates in Italy and Vietnam are 1% and 3% respectively.
3.      Cost of production in Italy and Vietnam are € 15500 and € 11000 respectively.
4.      The volume of production in terms of capacity per year is assessed at 20000 units in normal case with plus or minus 2000 in worst and best cases. The productivity of the plant in Vietnam is expected to be variable with the below pattern:

5.    Initial investment in capex with an useful life of the project to be 10 years
Best case – € 320 million
Base Case – € 350 million
Worst case – € 380 million

6.   The plant’s residual value at the end of the 10 years of project life is expected to be € 100 million
7.  Inventory holding period is 25 days normally and Average Payables Period is 30 days in Italy. Inventory Holding Period in Vietnam is expected to be 50 days (plus or minus in worst or best cases) and Average Payables Period in Vietnam is expected to be 30 days (plus or minus in worst or best cases)
8.  Firm’s decision is to use maximum debt financing. Over and above debt financing, if there is a requirement for equity, the firm will raise equity financing in Vietnam. The firm’s banker has agreed to fund the project upto a maximum of € 200 million as Senior Facility. The applicable interest rate throughout the project’s life is 5% p.a. The same needs to be repaid in 10 years starting after two preliminary years as per the below schedule:

The bank will also put a covenant in the loan indenture that the production should not be less than 15000 units in any of the year. In case this condition is not met in any year, there will be a penalty of € 1000000 p.a.
9.  The tax rate applicable in Vietnam is 30%. In case of loss in any year, the tax need not be paid. The same loss cannot be carried forward to any subsequent year/s.
10. Cost of Debt is 5%; Risk-free rate in Vietnam is 3%; Market Risk Premium in Vietnam is 6%. The operations team has collected the below information relating to comparable companies:

In addition to building the financial model, Jow was also supposed to answer the Board’s questions on the assumption made in model building. They were also sceptical about the project’s viability in case the certain assumptions change. Their questions were as below:

1. What will be the impact of change in assumed WACC of 10% on the NPV of the project?
    You are required to conduct a one-variable sensitivity analysis, for a series of WACC numbers     ranging from 6% to 14%, with a jump of 0.5%.

2. What will be the combined impact of change in Residual Value and Initial Investment together on the      NPV of the project?
You are required to conduct a two-variable sensitivity analysis, for a series of values of residual value and initial investment as below:

3. What will be the impact of change in the residual value assumed to be $ 70 million, on the project’s          financial feasibility?
    You are required to conduct a scenario analysis assuming the below values:


4. What will be the most expected NPV if you leave the possibilities of values taken by WACC, Residual Value and Initial Investment to randomness (i.e., the values follow normal distribution)? 


INSTRUCTOR REFERENCES:

What courses are intended to be using this case?
The case can be used as part finance courses like Financial Management, M&A, Investment Banking, Corporate Finance, Financial Modeling/Analytics/Engineering
What concepts/models/theories can be explained through this case?
This case can be used to demonstrate the below concepts/theories/models/applications:
i)              Capital Budgeting Techniques
ii)            Estimation of Cash Flows
iii)          Cost of Capital Computation – Unlevering the Beta
iv)          Depreciation Scheduling
v)            International Financial Management – Multinational Capital Budgeting, FDI
vi)          Sensitivity Analysis – One Variable, Two Variable, Scenario Analysis, Monte-Carlo Simulation
vii)        Financial Modelling using Excel
            What main issue/problem is addressed in the case?
The case addresses the problem of input bias in project evaluation for financial feasibility. Testing of spreadsheet based financial model for sensitive input variations and their impact on project related decisions
  IV.           Teaching Notes:
1)  This case can best be solved using MS-Excel. Student should be comfortable in entering formulas, using functions and using What-if Analysis feature in MS-Excel.
2) A total for 4 sessions (approximately 5 hours of classroom discussion with demonstration is required). Ideal if the students are grouped into groups of 3-4 members in each.
3) For the solved solution manual, the author can be sent in a requisition (write to kirankvk@isme.in
DISCLAIMER: The views, opinions and content on this blog are solely those of the authors. ISME does not take responsibility of content which are plagiarized or not quoted.
X