Hello, I have already completed the assignment, but I need Part A and Part B in ONE Excel file with all excel functions and formulas shown. Can you please add the Word document (which is Part A) t

Hello, I have already completed the assignment, but I need Part A and Part B in ONE Excel file with all excel functions and formulas shown.

Can you please add the Word document (which is Part A) to the excel file and make sure that all answers were answered correctly?

Attachment 1

Attachment 2

ATTACHMENT PREVIEW

Problem Assignments: Global Financial Investment
Assigned
Problems
1
Ann Page Co. … fixed costs \$30,000 per year. Variable costs per unit are \$17. Sales price per unit is \$30.
a) What is the contribution margin of the product?
\$13.00
Contribution margin is unit sales price less unit variable cost.
Contribution margin = Sales – Variable cost
=
30-17
=
\$13.00
b) Calculate the breakeven point in unit sales and dollars.
Breakeven in units is
2,308
Breakeven in dollars =
\$69,230.77
Breakevent point in units = Fixed cost / (Sales – variable cost)
=
30000/(30-17)
=
2308
Breakeven point in dollars = Fixed cost / Contribution margin ratio
Contribution margin ratio = Contribution margin / sales
=
13/30
=
43.33%
Break even point in dollars = 30000/43.33%
=
\$69,236.10
c) What is the operating profit (loss) at:
i) 1,500 units per year?
-10500
ii) 3,600 units per year?
24000
Units
1500
3600
Sales
\$45,000.00
\$108,000.00
Variable cost
\$25,500.00
\$61,200.00
Contribution margin
\$19,500.00
\$46,800.00
Fixed cost
\$30,000.00
\$30,000.00
Operating Profit
\$(10,500.00)
\$16,800.00
d) Plot a breakeven chart using the foregoing figures.
Units
Sales
Fixed cost
Total cost
0
\$-
\$30,000.00
\$30,000.00
1500
\$45,000.00
\$30,000.00
\$55,500.00
2308
\$69,240.00
\$30,000.00
\$69,236.00
3600
\$108,000.00
\$30,000.00
\$91,200.00
2
Mrs. Jones owns 100 shares of stock in Daimler-Benz valued at 16.5 Euros per share. What is the value in \$U.S. of her stock if:
a)
0.90
€ = \$1
1,650.00
Euros
Value of
1,650.00
Euros at
0.90
€ = \$1 is
\$1,833.33
Euro price
16.50
No. of shares
100.00
Shares value
1,650.00
Conversion to dollar = 1650/.90
=
1,833.33
b)
0.70
€ = \$1
Value of
1,650.00
Euros at
0.70
€ = \$1 is
\$2,357.14
Euro price
16.50
No. of shares
100.00
Shares value
1,650.00
Conversion to dollar = 1650/.70
=
2,357.14
c)
1.20
€ = \$1
Value of
1,650.00
Euros at
1.20
€ = \$1 is
\$1,375.00
Euro price
16.50
No. of shares
100.00
Shares value
1,650.00
Conversion to dollar = 1650/1.20
=
1,375.00
3
John is planning on purchasing his German dream car for
65,000
Euros
How much does he need in \$U.S. if there are
0.98
Euros to the \$U.S.?
Value of
65,000
Euros at
0.98
€ = \$1 is
\$66,326.53
Value
65,000.00
Conversion to dollar = 65000/0.98
=
66,326.53
Value of 100 shares at €16.5 per share
0
1500
2308
3600
\$-
\$20,000.00
\$40,000.00
\$60,000.00
\$80,000.00
\$100,000.00
\$120,000.00
Sales
Fixed cost
Total cost

ATTACHMENT PREVIEW

The Gibson Company is a United States (US) firm that is considering a joint venture with Brasilia, DF, a
Brazilian firm that grows and processes coffee beans. Gibson has a patent for a new coffee processing
method. This intellectual property is motivating Gibson to expand beyond importing coffee to engaging in a
joint venture to process the coffee. Gibson will invest \$8 million in the proposed joint venture project, which
will help to finance Brasilia ‘s production using the newly patented process.
The Brazilian government has guaranteed that the after-tax profits (denominated in Reals, the Brazilian
currency) can be converted to US dollars at the current exchange rate and sent to the Gibson Company
each year. Current exchange rates can be found at
http://www.oanda.com.
For each of the first five years, 60 percent of the total profits will be distributed to Brasilia, while the
remaining 40 percent will be converted to dollars to be sent to Gibson. The income tax rate for the joint
venture will be 10%. However, the Brazilian government is considering raising the income tax rate to 30%.
At the present time, the Brazilian government doe not impose a separate income tax on profits sent out of
the country. However, the Brazilian government is considering imposing an additional 10 percent income tax
on profits distributed to a foreign company. Assume that there are no other forms of tax. After considering
the taxes paid in Brazil, assume an additional seven percent tax imposed by the US government on profits
The expected total profits resulting from the joint venture per year are as follows:
Year
Total Profits from Joint Venture (in
BRL)
1
40 million
2
60 million
3
70 million
4
90 million
5
120 million
Gibson’s average cost of debt is 6 percent before taxes. Its average cost of equity is 9 percent. Assume that
Gibson’s US income tax rate is 10 percent. Gibson’s capital structure is 70 percent debt and 30 percent
equity. Gibson adds between 2 and 5 percentage points to its cost of capital when deriving its required rate
of return on international joint ventures. Gibson plans to account for country and other risks within its cash
flow estimates.
Gibson is concerned about country risk in the following two forms:
(1) Will the Brazilian government increase the corporate income tax rate from 10 percent to 30 percent
(20 percent probability)? If this occurs, Gibson will receive additional tax credits on its US taxes,
resulting in no US taxes on the profits from this joint venture.
(2) Will the Brazilian government impose a separate income tax of 10 percent on the profits distributed
to foreign companies such as Gibson (20 percent probability)? If this occurs, Gibson will not
receive additional tax credits, and the company will still be subject to US tax on the profits from this
joint venture.
Assume that the two types of country risk are mutually exclusive. If it does anything, the Brazilian
government will only implement one of these changes in its tax policies (i.e., the increase in the basic
income tax on the profits of the joint venture or the additional income tax on profits distributed to foreign
companies). The Brazilian government may also choose to leave things as they are.
Assignment
1.
Determine Gibson’s cost of capital and required rate of return for the joint venture in
Brazil.
WACC = Proportion of debt * pre tax Cost of debt * (1- Tax rate) + proportion of equity * post
tax cost of equity

Unlock Solution Unlocking…

=70%*6%*(1-7%) + 30%*9%
=6.61%
Required rate of return from Joint Venture in Brasilia = WACC + risk premium for international
JV’s
=Between 6.61% + 2.00% =8.61% to 6.61% + 5.00% =11.61%
Thus, the required rate of return is between 8.61% to 11.61% depending on where the
company classifies Brazil within the international risk scenario. Assuming an average level of
risk, we can take an average of the range and set the required rate of return to 10.11%
2. Determine the discrete probability distribution of Gibson’s Net Present Value for this joint
venture and calculate the Expected Net Present Value.
Scenario 1:
Based on original assumptions
Year
Total Profits
From Joint
Venture (in
million BRL)
Gibson’s
share of
profits(in
million
BRL)
Income Tax
paid to
Brazilian
Govt @10%
(in million
BRL)
After tax
profits
distributed
to Gibson
(in million
BRL)
Convert
Yuan into
US \$
@0.51878
(in million
\$)
Income tax
paid in US
@7% (in
million \$)
Net cash
flow after all
taxes (in
million \$)
1
40.00
16.00
1.60
14.40
7.47
0.52
6.95
2
60.00
24.00
2.40
21.60
11.21
0.78
10.42
3
70.00
28.00
2.80
25.20
13.07
0.92
12.16
4
90.00
36.00
3.60
32.40
16.81
1.18
15.63
5
120.00
48.00
4.80
43.20
22.41
1.57
20.84
Year
Cash Flows
Asscoited with
JV (in million
\$)
Discount
Factor
@10.11%
Discounted
value
0
-8.00
1.000
-8.00
1
6.95
0.908
6.31
2
10.42
0.825
8.60
3
12.16
0.749
9.11
4
15.63
0.680
10.63
5
20.84
0.618
12.88
NPV
39.52

Show entire document