Spreadsheet Case Study

Case 20: PEPSICO CHANGCHUN
JOINT VENTURE
Capital Expenditure Analysis
Study Questions
Q1. Use the information in the case to construct two sets
of NPV and IRR analysis from joint venture view and
Pepsico. Based on the results, what would be your
decision on the proposed Changchun joint venture?
Q2. Comment on the financial projections that PepsiCo
used in its capital budgeting exercise, especially the
NOPBT Cap, foreign exchange rate projection and the
discount rate.
Q3. What differences might there be as to how the PRC
partners do the analysis (or look at the future cash
flows) versus PepsiCo?
Case Summary
In mid-June 1994, Andre Hawaux, vice-president
finance for PepsiCo East Asia (PepsiCo), was about to
put together the information he had collected on the
proposed Changchun bottling joint venture (JV) in order
to analyze the financial profitability of the project using
net present value (NPV) and internal rate of return (IRR).
Joint Venture
Before 1993,
-cooperative joint venture(CJV): a foreign company with a
local Chinese firm
- The amount of capital injected in to the business did not
necessarily equal the amount of profit-sharing
After 1993,
- Equity joint venture
- The profit would be distributed in line with the ratio of
capital injected.
Introduction to NPV and IRR

Net Present Value(
The difference between the market value of a

project and its cost
How much value is created from undertaking
an investment?
The first step is to estimate the expected future
cash flows.
The second step is to estimate the required return
for projects of this risk level.
The third step is to find the present value of the
cash flows and subtract the initial investment.
NPV Decision Rule

If the NPV is positive, accept the project
A positive NPV means that the project is
expected to add value to the firm and will
therefore increase the wealth of the owners.
Since our goal is to increase owner wealth,
NPV is a direct measure of how well this
project will meet our goal.
Computing NPV for the Project

Using the formulas:
NPV = 63,120/(1.12) + 70,800/(1.12)2 +
91,080/(1.12)3 165,000 = 12,627.42
Many financial calculators also have templates

for calculating NPV
Easiest to calculate using a computerized
spreadsheet
Do we accept or reject the project?
Internal Rate of Return

This is the most important alternative to NPV
It is often used in practice and is intuitively
appealing
It is based entirely on the estimated cash flows
and is independent of interest rates found
elsewhere
IRR Definition and Decision Rule

Definition: IRR is the return that makes the
NPV = 0
Decision Rule: Accept the project if the IRR is
greater than the required return
Computing IRR For The Project

If you do not have a financial calculator, then
this becomes a trial and error process
Again many financial calculators have
templates for estimating IRR
But IRR is most easily estimated using a
spreadsheet
Do we accept or reject the project?
IRR using Excel
DBH suggestion: Use the required return as the guess

rate requested by the Excel function (in this case 12%)
Since 16.13% > 12% we would accept the project.
Background of the Changchun Joint Venture

Changchun is one of the PepsiCo MOU cities.
The capital city of Jilin Province had a large
underdeveloped CSD market.
It was considered to be an ideal location by the
company to gain a lead over rival Coca-Cola.
Three partners for the JV:
- The Second Food Factory Changchun:37.5%
- Beijing Chong Yin Industrial & Trading Company:5%
- PepsiCo: 57.5%
NPV and IRR of the Changchun Joint

Venture
12-year NPV projection, discount rate of JV is

16%
Net Income Projection: Exhibit 7
Cap: 11% of revenue
Taxes expenses: two years of tax holiday and
three years of reduced tax rate at 7.5%; loss carry
forward for tax purposes;2001 and 2002, 10.5%
From 2003, 18 percent of income taxes.
Statutory reserve: 15 percent required by
government
See excel table(profit and loss): TN-1

Revenue
COGS
Gross Profit
Operating Expenses
Manufacturing
S&D
Distributors' Commission
A&M
G&A
Bottle & Shell Breakage& Amort.
Bad Debt Write Off
NOPBT
NOPBT Cap @ 11%
Adjusted NOPBT
Tax Expense
NOPAT
Statutory Reserve
Net Income
Exhibit 7
Adjust NOPBT-NOPBT
NOPBT down to 11% of revenue
Compute
Adjust NOPBT-Tax Expense
Compute
NOPAT-Statutory Reserve
Results of the NPV and IRR

Non-Cash Expenses: Exhibits 4 and 9
depreciation, amortization, the bottle and shell
deposit and breakage
Cash Outflow: Exhibit 4 and 8
Capital expenditure and increase in working
capital
Terminal value(constant dividend growth
model,5% growth rate): USD 55.9 million
Cash flow * (1 + growth rate) 5857 * (1 + 0.05)
Terminal value =
=
= 55,913
16% 5%
discount rate - growth rate
TN-2(NPV and IRR from Joint Venture)

Net Income
Depreciation(+)
Amortization(+)
Bottle Deposit(+)
Capital Expenditure(-)
Working Capital(-)
Net Cash Flow
Terminal Value
Net Cash Flow with TV
Discount Rate
NPV
IRR
TN-1
Exhibit 4
Exhibit 4
Exhibit 9
Exhibit 4
Exhibit 8
Compute
Compute
Compute
16%
Without TV With TV
(12868.8) (4748.8)
2.40%
12.90%
Sensitivity Analysis
Revenue growth rate

Profit margin
Exchange rate(Exhibit 6)
The timing of capital investment
How realistic are the predictions?
Other Point of View

JV from PepsiCos Point of View: TN-3
- The sales of concentrate to the bottling JV
50% of COGS
- The net contribution margin of concentrate
sales
17% of sales
TN-3(NPV and IRR from PepsiCo)

Net Cash Flow with TV
PepsiCo's Share of Joint Venture Cash Flow(B5)
COGS
Joint Venture Purchase of Concentrate PepsiCo
Contribution from Concentrate(B8)
Total Cash Flow to PepsiCo
Discount Rate
NPV
IRR
TN-2
*0.575
Exhibit 7
50% COGS
17% of sales
B5+B8
16%
Compute
Compute
Other Point of View

JV from PRC Partners Point of View
- Conflicting expectation: the Chinese partners
would prefer immediate financial returns from
the JV.
- The Chinese partners targeted a 20% return.
- What is NPV if hurdle rate were 20%?
Conclusion
The partners proceeds with the proposed
Changchun joint venture in 1994.
It has operated successfully since it
establishment.

Spreadsheet Case Study

Uploaded by

Copyright:

Available Formats

Spreadsheet Case Study

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Spreadsheet Case Study

Uploaded by

Copyright:

Available Formats

What financial analysis tools are discussed in evaluating the proposed joint venture?

What financial analysis tools are discussed in evaluating the proposed joint venture?

What are some of the assumptions used in the financial projections for the joint venture?

What are some of the assumptions used in the financial projections for the joint venture?

Case 20: PEPSICO CHANGCHUN

Introduction to NPV and IRR

The difference between the market value of a

NPV Decision Rule

Computing NPV for the Project

Many financial calculators also have templates

Internal Rate of Return

IRR Definition and Decision Rule

Computing IRR For The Project

IRR using Excel

DBH suggestion: Use the required return as the guess

Background of the Changchun Joint Venture

NPV and IRR of the Changchun Joint

12-year NPV projection, discount rate of JV is

See excel table(profit and loss): TN-1

Results of the NPV and IRR

TN-2(NPV and IRR from Joint Venture)

Revenue growth rate

How realistic are the predictions?

Other Point of View

TN-3(NPV and IRR from PepsiCo)

Other Point of View

You might also like