Reference no: EM13494934
Please give best answers to all questions on the exam with use of Excel, Excel sheets need to be attached when you submit the exam. The exam is open material, but you may not consult in any way with others.
1. Please write the formula you use in excel and also attach the excel answers.
a) With interest rate 8%, calculate the PV of following cash flows.
|
Year
|
Payment
|
PV
|
|
1
|
1,000
|
|
|
2
|
1,000
|
|
|
3
|
1,000
|
|
|
4
|
1,000
|
|
|
5
|
1,000
|
|
b) You borrow $10,000 today with interest rate 7%, and you plan to pay the bank back with 8 equal annual payment. Please fill up the following table.
|
Year
|
Principal
beg. year
|
Payment
end year
|
Interest
|
Principal
|
|
|
|
|
|
|
|
1
|
|
|
|
|
|
2
|
|
|
|
|
|
3
|
|
|
|
|
|
4
|
|
|
|
|
|
5
|
|
|
|
|
|
6
|
|
|
|
|
|
7
|
|
|
|
|
|
8
|
|
|
|
|
2. a)Please calculate NPV and IRR for both projects, and fill the blank which project you decide to say yes.
|
Discount rate
|
12%
|
|
|
|
|
|
|
Year
|
Project A
|
Project B
|
|
0
|
-1000
|
-800
|
|
1
|
500
|
420
|
|
2
|
500
|
420
|
|
3
|
500
|
420
|
|
4
|
500
|
420
|
|
5
|
500
|
420
|
|
6
|
200
|
300
|
|
7
|
350
|
300
|
|
8
|
360
|
300
|
|
9
|
200
|
450
|
|
10
|
200
|
260
|
|
11
|
200
|
260
|
|
|
|
|
|
NPV
|
|
|
|
IRR
|
|
|
Which project will you make a decision? Project A or Project B?
b)Please calculate the following cash flow and show us your decision about which project should be chosen and why.
|
Date
|
Cash flow A
|
Cash Flow B
|
|
1-Jan-02
|
-10,000
|
-13000
|
|
1-Jul-02
|
3,000
|
3500
|
|
1-Jul-03
|
3,000
|
4000
|
|
1-Jul-04
|
3,000
|
3500
|
|
1-Jul-05
|
3,000
|
4000
|
|
1-Jul-06
|
3,000
|
4000
|
3. Statistics and Regression Analysis
a) Please calculate average, variance and standard deviation for annual return column.
|
Date
|
Price
|
Dividend
|
Annual
return
|
|
31-Dec-98
|
34.13
|
0.92
|
|
|
31-Dec-99
|
30.81
|
0.96
|
-8.89%
|
|
29-Dec-00
|
26.25
|
0.99
|
-12.59%
|
|
31-Dec-01
|
30.10
|
1.01
|
18.51%
|
|
31-Dec-02
|
34.27
|
1.01
|
17.21%
|
|
31-Dec-03
|
38.08
|
1.01
|
13.06%
|
|
31-Dec-04
|
44.66
|
1.01
|
19.93%
|
|
30-Dec-05
|
43.22
|
1.06
|
-0.85%
|
|
29-Dec-06
|
50.06
|
1.14
|
18.46%
|
|
31-Dec-07
|
52.43
|
1.20
|
7.14%
|
|
31-Dec-08
|
42.73
|
1.30
|
-16.02%
|
|
|
|
|
|
|
Average return, E(rK)
|
|
|
|
|
Variance of return, s2K
|
|
|
|
|
Standard deviation of return, sK
|
|
|
|
b) Please show your regress analysis table and fill form for slope, intercept and R-square.
|
|
Return for the month
|
|
Date
|
S&P 500
|
IBM
|
|
3-Jan-07
|
|
|
|
1-Feb-07
|
-2.18%
|
-6.98%
|
|
1-Mar-07
|
1.00%
|
2.42%
|
|
2-Apr-07
|
4.33%
|
8.44%
|
|
1-May-07
|
3.25%
|
4.70%
|
|
1-Jun-07
|
-1.78%
|
-1.27%
|
|
2-Jul-07
|
-3.20%
|
5.14%
|
|
1-Aug-07
|
1.29%
|
5.83%
|
|
4-Sep-07
|
3.58%
|
1.95%
|
|
1-Oct-07
|
1.48%
|
-1.42%
|
|
1-Nov-07
|
-4.40%
|
-9.10%
|
|
3-Dec-07
|
-0.86%
|
2.78%
|
|
2-Jan-08
|
-6.12%
|
-0.92%
|
|
1-Feb-08
|
-3.48%
|
6.71%
|
|
3-Mar-08
|
-0.60%
|
1.12%
|
|
1-Apr-08
|
4.75%
|
4.83%
|
|
1-May-08
|
1.07%
|
7.66%
|
|
2-Jun-08
|
-8.60%
|
-8.42%
|
|
1-Jul-08
|
-0.99%
|
7.97%
|
|
1-Aug-08
|
1.22%
|
-4.51%
|
|
2-Sep-08
|
-9.21%
|
-3.92%
|
|
1-Oct-08
|
-16.83%
|
-20.51%
|
|
3-Nov-08
|
-7.48%
|
-11.74%
|
|
1-Dec-08
|
0.78%
|
3.13%
|
|
2-Jan-09
|
-8.57%
|
8.90%
|
|
Slope
|
|
|
|
|
|
|
|
|
|
|
|
Intercept
|
|
|
|
|
|
|
|
|
|
|
|
R-squared
|
|
|
|
|
|
|
4. Bond
a) Please calculate YTM.
|
Date
|
Bond cash flow
|
|
15-Dec-09
|
-1,000.00
|
|
15-Dec-10
|
80.00
|
|
15-Dec-11
|
80.00
|
|
15-Dec-12
|
80.00
|
|
15-Dec-13
|
80.00
|
|
15-Dec-14
|
80.00
|
|
15-Dec-15
|
80.00
|
|
15-Dec-16
|
1,080.00
|
|
|
|
YTM of bond
|
|
b) Compute the price for following bond with YTM of 5%.
|
Date
|
Payment
|
|
1
|
80
|
|
2
|
80
|
|
3
|
1,080
|
|
Bond price
|
|
5. Stocks Valuation
ABC company's current FCF is $2,000,000, it will grown at 20% for the first 4 years and back to a steady growth rate 7% after 4 years. The WACC is 10%, outstanding shares is 4,000,000. Please use FCF model to estimate the value of their stock. Show you steps and calculations in Excel. Assume all FCFs happen at the end period.
|
Current FCF
|
2,000,000
|
|
Anticipated growth rate, years 1-4
|
20%
|
|
WACC
|
10%
|
|
Long-term growth rate, after year 4
|
7%
|
|
Number of shares outstanding
|
4,000,000
|
|
Write a short memo to audrey offering your opinion
: Use textbooks or Internet sources for information on the format and contents of a statement of cash flows. To complete this problem, you may wish to use the Excel template provided in the resources.
|
|
Obtain the average power delivered to the train
: The electric motor of a model train accelerates the train from rest to 0.600 m/s in 17.0 ms. The total mass of the train is 585 g. Find the average power delivered to the train during its acceleration
|
|
Explain johnson company financial data
: Create T-accounts for each of the accounts on a balance sheet and enter the balances at the end of 2012 as beginning balances for 2013. Prepare a trial balance for 12/31/12.
|
|
Estimate the radius of the orbit
: A particle with a charge of -9 x 10-6C and a mass of 13 g moves uniformly with a speed of 4.6 m/s in a circular orbit around a stationary particle of charge 1 x 10-6C
|
|
Calculate npv and irr for both projects
: Please give best answers to all questions on the exam with use of Excel, Excel sheets need to be attached when you submit the exam. The exam is open material, but you may not consult in any way with others.
|
|
What is the current coming from the wall socket
: The rechargeable batteries for a laptop computer need a much smaller voltage than what a wall socket provides. What is the current coming from the wall socket
|
|
How to distribute net income
: Prepare a table with the following column headings. Use the table to show how to distribute net income of $450,000 for the calendar year under each of the alternative plans being considered(Round answers to the nearest whole dollar).
|
|
What is the minimum number of turns that the coil must have
: The maximum strength of the earth's magnetic field is about 7.0 x 10-5 T near the south magnetic pole. What is the minimum number of turns (area per turn = 0.021 m2) that the coil must have to produce an rms voltage of 170 V
|
|
Calculate the currents in the secondary lines
: Calculate the nominal currents in the primary and secondary windings of the transformer shown in Fig. 10.18, knowing that the windings are connected in delta-wye.
|