S&P companies.xlsx 1. Download the data above, which contai…
S&P companies.xlsx 1. Download the data above, which contains selected information from S&P 500 companies. Create a pivot table from the original data that shows the top 5 GICS sector by sum of market value. The pivot table result should show only top 5 results. Sort them from highest to lowest. Create a new pivot table in a separate tab to report each industry’s operating profitability using Operating Profit Margin. Graph a pivot chart to visualize the results. Create a new pivot table in a separate tab to examine relationship between dividend yield and return. First, use “calculated field” tool within pivot table to create a calculated field called “3-year average”, which is the average of 2016,2017,2018 returns. Report each company’s 3-year average return along with its dividend yield in the pivot table. Then, copy paste both columns into an empty area and run a simple linear regression to examine whether a company’s dividend yield can predict its 3-year average return. Report the regression output and type out your findings briefly: What does the regression coefficient suggest? Is the coefficient significant? 2. In the same file, just create a new tab for question 2. SkyPeak Mountain Resort is evaluating the installation of a new high-speed gondola lift to connect its base lodge to the summit. The gondola system costs $8,200,000 to purchase, with an additional $620,000 in installation and site preparation costs. Both costs combined form the asset’s depreciation base. The system has a useful life of 8 years and a salvage value of $750,000, and will be depreciated using the straight-line method. Management estimates the gondola will complete 40 trips per day, with each trip carrying an average of 18 passengers. The resort operates 140 days per year. In the first year, the fare per passenger is expected to be $18.00, increasing by 3% annually. The variable cost per passenger is $4.50, and total fixed operating costs are $680,000 per year. At the end of year 6, the gondola infrastructure will be decommissioned at a removal cost of $310,000, and salvageable components will be sold for $3,000,000. The resort’s cost of capital is 9.0%, and its marginal tax rate is 25%. Set up the input, calculate initial outlay, annual after-tax cash flow for each year, and the terminal cash flow. Calculate NPV, IRR, MIRR, PI of the gondola lift. Is the project acceptable? Conduct a sensitivity diagram containing 3 variables of your choice. Create a best-case scenario and a worst-case scenario. Change 3 variables of your choice, report NPV, IRR, MIRR and PI in the scenario summary. If there’s a 80% probability of base case, 10% probability of best case, and 10% probability of worst case, calculate expected NPV, Variance, Standard deviation, and the probability of a negative NPV.
Read DetailsProblem 1 (25 pts) Let A{“version”:”1.1″,”math”:”A”} and B{“…
Problem 1 (25 pts) Let A{“version”:”1.1″,”math”:”A”} and B{“version”:”1.1″,”math”:”B”} be two events in a probability space (S, F, P){“version”:”1.1″,”math”:”(S, F, P)”}. Is it true that P(A\B)=P(A)-P(B){“version”:”1.1″,”math”:”P(A\B)=P(A)-P(B)”}? Why or why not? As a reminder, A\B={ω∈S: ω∈A, ω∉B}{“version”:”1.1″,”math”:”A\B={ω∈S: ω∈A, ω∉B}”}. Problem 2 (25 pts) Consider a random experiment with sample space S{“version”:”1.1″,”math”:”S”} = {1, 2, 3, 4, 5, 6}, where the outcomes in S{“version”:”1.1″,”math”:”S”} are all equally likely. Let A{“version”:”1.1″,”math”:”A”} and B{“version”:”1.1″,”math”:”B”} be independent events. Assuming that A{“version”:”1.1″,”math”:”A”} has four elements, what values can B{“version”:”1.1″,”math”:”B”} take, where B{“version”:”1.1″,”math”:”B”} is the number of elements in B{“version”:”1.1″,”math”:”B”}? Problem 3 (25 pts) Consider two probability measures P1{“version”:”1.1″,”math”:”P1″} and P2{“version”:”1.1″,”math”:”P2″} defined on the same event space F{“version”:”1.1″,”math”:”F”}. Under what conditions on the real-valued constants a1{“version”:”1.1″,”math”:”a1″} and a2{“version”:”1.1″,”math”:”a2″} is the function a1P1(A)+a2P2(A), ∀A∈F{“version”:”1.1″,”math”:”a1P1(A)+a2P2(A), ∀A∈F”} , a valid probability measure? Problem 4 (25 pts) A coin is tossed with P(H)=P(T)=12{“version”:”1.1″,”math”:”P(H)=P(T)=12″}. If the coin comes up heads, you lose 2 dollars. If it comes up tails, you are equally likely to win any amount of money in (0, 10) dollars, i.e., any real number between 0 and 10 dollars. Let X{“version”:”1.1″,”math”:”X”} be the amount of money you win. Find the cumulative distribution function of X. Note that X is negative if you lose money. Congratulations, you are almost done with Exam 1. DO NOT end the Honorlock session until you have submitted your work to Gradescope. When you have answered all questions: Use your smartphone to scan your answer sheet and save the scan as a PDF. Make sure your scan is clear and legible. Submit your PDF as follows: Email your PDF to yourself or save it to the cloud (Google Drive, etc.). Click this link to go to the assignment in Gradescope: Exam 1 Submit your answer sheets. Return to this window and click the button below to agree to the honor statement. Click Submit Quiz to end the exam. End the Honorlock session.
Read Details