Johns Hopkins University Linear Combinations Excel Sheet Please utilize Excel spreadsheet for Linear Combinations and Excel Basics. Please read prompt in d

Johns Hopkins University Linear Combinations Excel Sheet Please utilize Excel spreadsheet for Linear Combinations and Excel Basics. Please read prompt in detail. Linear Combinations
1.Returns on stocks X and Y are listed below:
Period
1
2
3
4
5
6
7
Stock X
7%
1%
-2%
-3%
5%
11%
5%
Stock Y
-4%
10%
4%
8%
7%
-1%
-3%
What is the (population) covariance of returns on the two stocks?
Please round your answer to six decimal places.
Note that the correct answer will be evaluated based on the full-precision result you would obtain
using Excel.
2. Returns on stocks X and Y are listed below:
Period
1
2
3
4
5
6
7
Stock X
3%
-2%
9%
6%
-1%
-4%
11%
Stock Y
1%
-4%
7%
12%
3%
-2%
-1%
Consider a portfolio of 20% stock X and 80% stock Y.
What is the mean of portfolio returns?
Please specify your answer in decimal terms and round your answer to the nearest thousandth (e.g.,
enter 12.3 percent as 0.123).
3. Returns on stocks X and Y are listed below:
Period
1
2
3
4
5
6
7
Stock X
5%
6%
-2%
-4%
6%
10%
7%
Stock Y
1%
-3%
6%
3%
12%
7%
-5%
Consider a portfolio of 40% stock X and 60% stock Y.
What is the (population) variance of portfolio returns?
Please round your answer to six decimal places.
Note that the correct answer will be evaluated based on the full-precision result you would obtain
using Excel.
4. Summary statistics for returns on two stocks X and Y are listed below.
Mean
Variance
Stock X
2.35%
0.007000
Stock Y
4.53%
0.003000
The covariance of returns on stocks X and Y is 0.002700. Consider a portfolio of 70% stock X and
30% stock Y.
What is the mean of portfolio returns?
Please specify your answer in decimal terms and round your answer to the nearest thousandth (e.g.,
enter 12.3 percent as 0.123).
5. Summary statistics for returns on two stocks X and Y are listed below.
Mean
Variance
Stock X
2.83%
0.006000
Stock Y
5.98%
0.003000
The covariance of returns on stocks X and Y is 0.001500. Consider a portfolio of 80% stock X and
20% stock Y.
What is the standard deviation of portfolio returns?
Please specify your answer in decimal terms and round your answer to the nearest thousandth (e.g.,
enter 12.3 percent as 0.123).
Note that the correct answer will be evaluated based on the full-precision result you would obtain
using Excel.
Excel Basics
1. In a spreadsheet create a function that generates for any value x the corresponding value
y when y = 12x + 15. What is the value y when x = 10? Please specify your answer as an
integer.
2. In a spreadsheet create a function that generates for any value x the corresponding value
y when y = 10×2 + 7x – 1. What is the value y when x = 8? Please specify your answer as
an integer.
3. In a spreadsheet create a function that generates for any value x the corresponding value
y when y = (4/7)x2 – 6x + 10. What is the value y when x = 11? Please round your
answer to the nearest hundredth.
4. In a spreadsheet create a function that generates for any value x the corresponding value
y when y = (10/7)(1/x) – 15. What is the value y when x = 14? Please round your answer
to the nearest thousandth.
5. Consider the following set of numbers: 18, 15, 20, 11, and 14. Using a spreadsheet for
computations, what is the sum of the numbers? Please specify your answer as an integer.
6. Consider the following set of numbers: 19, 14, 13, 17, and 12. Using a spreadsheet for
computations, what is the product of the numbers? Please specify your answer as an
integer.
7. Consider the following set of numbers: 12, 15, 11, 16, and 20. Using a spreadsheet for
computations, what is the average of the numbers? Please round your answer to the
nearest hundredth.
8. Design a spreadsheet to compute the dollar amount in each of the next 10 years of an
initial investment returning a constant annual interest rate. Interest is reinvested each year
so that the amount returning interest grows. What is the dollar amount 10 years from now
of $500 invested at 6% annual interest? Please round your answer to the nearest
hundredth.
9. Suppose revenues for a small business are $60,000 this year and will grow 9% per year.
Design a spreadsheet to compute revenue in each of the next 10 years. In how many years
from now will revenues first exceed $108,000? Please specify your answer as an integer.
10. Suppose a small business has sales of $14,000 this month, with future sales expected to
grow by $1,300 each month. Costs consist of a fixed component, which is $8,400 per
month, and a variable component, which is 25 percent of sales. Design a spreadsheet to
compute the gross profit (revenue less fixed and variable costs) per month over a nine
month period. What is the gross profit in a single month 8 months from now? Please
specify your answer as an integer.
Inference
1
Suppose the number of residents within five miles of each of your stores is asymmetrically
distributed with a mean of 15 thousand and a standard deviation of 12.5 thousand. What is the
probability that the average number of residents within five miles of each store in a sample of
60 stores will be fewer than 17.5 thousand? Please specify your answer in decimal terms and
round your answer to the nearest hundredth (e.g., enter 12 percent as 0.12).
Answer
2
Suppose the number of residents within five miles of each of your stores is asymmetrically
distributed with a mean of 18 thousand and a standard deviation of 6.4 thousand. What is the
90th percentile for the average number of residents within five miles of each store in a sample
of 50 stores? Note that the correct answer will be evaluated based on the z-values in the
summary table in the Teaching Materials section. Please specify your answer in thousands and
round to the nearest tenth (e.g., enter 6,531 as 6.5).
Answer
3
Suppose the shipping weight of your cheese shop’s customized gift baskets is asymmetrically
distributed with unknown mean and standard deviation. For a sample of 65 orders, the mean
weight is 45 ounces and the standard deviation is 10.2 ounces. What is the upper bound of the
90 percent confidence interval for the gift basket’s average shipping weight? Note that the
correct answer will be evaluated based on the z-values in the summary table in the Teaching
Materials section. Please round your answer to the nearest tenth. Note that the correct answer
will be evaluated based on the full-precision result you would obtain using Excel.
Answer
4
Suppose the average commute time of your employees is unknown. The standard deviation of
their commute time is estimated as 29.1 minutes. How many employees must be included in a
sample to create a 90 percent confidence interval for the average commute time with a
confidence interval width of no more than 16 minutes? Note that the correct answer will be
evaluated based on the z-values in the summary table in the Teaching Materials section.
Remember to round your answer up to an integer. Note that the correct answer will be
evaluated based on the full-precision result you would obtain using Excel.
Answer
5
Suppose you are working for a regional residential natural gas utility. For a sample of 90
customer visits, the staff time per reported gas leak has a mean of 206 minutes and standard
deviation 32 minutes. The VP of network maintenance hypothesizes that the average staff time
devoted to reported gas leaks is 216 minutes. At a 10 percent level of significance, what is the
lower bound of the interval for determining whether to accept or reject the VP’s hypothesis?
Note that the correct answer will be evaluated based on the z-values in the summary table in
the Teaching Materials section. Please round your answer to the nearest tenth. Note that the
correct answer will be evaluated based on the full-precision result you would obtain using
Excel.
Answer

Purchase answer to see full
attachment