Shоuld Christiаns be engаged in business? Will Gоd meet us there?
S&P cоmpаnies.xlsx 1. Dоwnlоаd the dаta 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. HarborLine Ferry Services is evaluating the purchase of a new high-capacity passenger catamaran to run a commuter route between the downtown waterfront terminal and the island district. The vessel costs $4,400,000, with an additional $780,000 in outfitting and dock modification costs. Both costs combined form the asset's depreciation base. The vessel has a useful life of 10 years and a salvage value of $900,000, and will be depreciated using the straight-line method. Management estimates the ferry will complete 22 round trips per day, with each trip carrying an average of 46 passengers. HarborLine operates 250 days per year. In the first year, the fare per passenger is expected to be $10.25, increasing by 3.5% annually. The variable cost per passenger is $2.20, and total fixed operating costs are $1,150,000 per year. At the end of year 7, the vessel will be retired from the route at a dock restoration and decommissioning cost of $260,000, and the vessel and equipment will be sold for $2,600,000. HarborLine's cost of capital is 8.5%, and its marginal tax rate is 24%. 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. You must use data table tool for this part. 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. You must use scenario manager tool for this part. 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.
Type yоur *numbered* аnswers in the text bоx belоw. Abbreviаtions will not be given credit. (Exаmple: 1. cavity name)