Finish Question 2 and 3

Business Finance

Duke University

Question Description

Finish question 2 and 3 only. Following the instructions. Will add a file for reference too.

Question 3:

a. Create a Profitability Index (PI) calculator where PI =

SUM(PV of Positive CF)

SUM(PV of Negative CF)

To get started, you may use a discount rate (r) that equals 10% with the following cash flows (from year 0 to year 5):

-120,000; 39,000; 30,000; -21,000; 37,000; 46,000

a1. The column "CashFlow" is the only column that can be hard-coded.

a2. Must use the Excel table naming convention.

b. Write a subroutine and create a micro button "InputCashflow" that asks the user what each of the cashflow amount is and immediately fill in the cashflow in the table.

(Make sure you FIRST ask the user for the total number of periods so that you know how many times the For Next Loop needs to run.)

Unformatted Attachment Preview

UserInput StartDate EndDate Stock .INX Date Close ######### 1/3/2017 1/4/2017 1/5/2017 1/6/2017 1/9/2017 1/10/2017 1/11/2017 1/12/2017 1/13/2017 1/17/2017 1/18/2017 1/19/2017 1/20/2017 1/23/2017 1/24/2017 1/25/2017 1/26/2017 1/27/2017 1/30/2017 1/31/2017 2/1/2017 2/2/2017 2/3/2017 2/6/2017 2/7/2017 2/8/2017 2/9/2017 2/10/2017 2/13/2017 2/14/2017 2/15/2017 2/16/2017 2/17/2017 2/21/2017 2/22/2017 2/23/2017 AAPL Close BBBY Close R.Market R.APPL R.BBBY XAxis YAxis Question 1: a. Use the Google spreadsheet "S generate the closing prices of the Bath & Beyond (BBBY) Share the Goold Sheet file with f when you subbmit your final exa b. Copy and paste the data you Then caluclate the daily returns f c. Prepare an XY chart (aka scatte APPL or BBBY. (The X axis is R.M R.BBBY. Make sure a linear trend The users should be able to as which series to be charted data.) d. Use dynamically named range "EndDate". Also use data validati e. Use dynamically named range (If the "scope" for the names "XA the x axis for the chart should be the y axis for the chart should be (If the "scope" for the names the x axis for the chart should be the y axis for the chart should be If you renamed your file, you sho new file name.) new file name.) 2/24/2017 2/27/2017 2/28/2017 3/1/2017 3/2/2017 3/3/2017 3/6/2017 3/7/2017 3/8/2017 3/9/2017 3/10/2017 3/13/2017 3/14/2017 3/15/2017 3/16/2017 3/17/2017 3/20/2017 3/21/2017 3/22/2017 3/23/2017 3/24/2017 3/27/2017 3/28/2017 3/29/2017 3/30/2017 3/31/2017 4/3/2017 4/4/2017 4/5/2017 4/6/2017 4/7/2017 4/10/2017 4/11/2017 4/12/2017 4/13/2017 4/17/2017 4/18/2017 4/19/2017 4/20/2017 4/21/2017 4/24/2017 4/25/2017 4/26/2017 4/27/2017 4/28/2017 5/1/2017 5/2/2017 5/3/2017 5/4/2017 5/5/2017 5/8/2017 5/9/2017 5/10/2017 5/11/2017 5/12/2017 5/15/2017 5/16/2017 5/17/2017 5/18/2017 5/19/2017 5/22/2017 5/23/2017 5/24/2017 5/25/2017 5/26/2017 5/30/2017 5/31/2017 6/1/2017 6/2/2017 6/5/2017 6/6/2017 6/7/2017 6/8/2017 6/9/2017 6/12/2017 6/13/2017 6/14/2017 6/15/2017 6/16/2017 6/19/2017 6/20/2017 6/21/2017 6/22/2017 6/23/2017 6/26/2017 6/27/2017 6/28/2017 6/29/2017 6/30/2017 7/3/2017 7/5/2017 7/6/2017 7/7/2017 7/10/2017 7/11/2017 7/12/2017 7/13/2017 7/14/2017 7/17/2017 7/18/2017 7/19/2017 7/20/2017 7/21/2017 7/24/2017 7/25/2017 7/26/2017 7/27/2017 7/28/2017 7/31/2017 8/1/2017 8/2/2017 8/3/2017 8/4/2017 8/7/2017 8/8/2017 8/9/2017 8/10/2017 8/11/2017 8/14/2017 8/15/2017 8/16/2017 8/17/2017 8/18/2017 8/21/2017 8/22/2017 8/23/2017 8/24/2017 8/25/2017 8/28/2017 8/29/2017 8/30/2017 8/31/2017 9/1/2017 9/5/2017 9/6/2017 9/7/2017 9/8/2017 9/11/2017 9/12/2017 9/13/2017 9/14/2017 9/15/2017 9/18/2017 9/19/2017 9/20/2017 9/21/2017 9/22/2017 9/25/2017 9/26/2017 9/27/2017 9/28/2017 9/29/2017 10/2/2017 10/3/2017 10/4/2017 10/5/2017 10/6/2017 10/9/2017 ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### 11/1/2017 11/2/2017 11/3/2017 11/6/2017 11/7/2017 11/8/2017 11/9/2017 ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### 12/1/2017 12/4/2017 12/5/2017 12/6/2017 12/7/2017 12/8/2017 ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### 1/2/2018 1/3/2018 1/4/2018 1/5/2018 1/8/2018 1/9/2018 1/10/2018 1/11/2018 1/12/2018 1/16/2018 1/17/2018 1/18/2018 1/19/2018 1/22/2018 1/23/2018 1/24/2018 1/25/2018 1/26/2018 1/29/2018 1/30/2018 1/31/2018 2/1/2018 2/2/2018 2/5/2018 2/6/2018 2/7/2018 2/8/2018 2/9/2018 2/12/2018 2/13/2018 2/14/2018 2/15/2018 2/16/2018 2/20/2018 2/21/2018 2/22/2018 2/23/2018 2/26/2018 2/27/2018 2/28/2018 3/1/2018 3/2/2018 3/5/2018 3/6/2018 3/7/2018 3/8/2018 3/9/2018 3/12/2018 3/13/2018 3/14/2018 3/15/2018 3/16/2018 3/19/2018 3/20/2018 3/21/2018 3/22/2018 3/23/2018 3/26/2018 3/27/2018 3/28/2018 3/29/2018 4/2/2018 4/3/2018 4/4/2018 4/5/2018 4/6/2018 4/9/2018 4/10/2018 4/11/2018 4/12/2018 4/13/2018 4/16/2018 4/17/2018 4/18/2018 4/19/2018 4/20/2018 4/23/2018 4/24/2018 4/25/2018 4/26/2018 4/27/2018 4/30/2018 5/1/2018 5/2/2018 5/3/2018 5/4/2018 5/7/2018 5/8/2018 5/9/2018 5/10/2018 5/11/2018 5/14/2018 5/15/2018 5/16/2018 5/17/2018 5/18/2018 5/21/2018 5/22/2018 5/23/2018 5/24/2018 5/25/2018 5/29/2018 5/30/2018 5/31/2018 6/1/2018 6/4/2018 6/5/2018 6/6/2018 6/7/2018 6/8/2018 6/11/2018 6/12/2018 6/13/2018 6/14/2018 6/15/2018 6/18/2018 6/19/2018 6/20/2018 6/21/2018 6/22/2018 6/25/2018 6/26/2018 6/27/2018 6/28/2018 6/29/2018 7/2/2018 7/3/2018 7/5/2018 7/6/2018 7/9/2018 7/10/2018 7/11/2018 7/12/2018 7/13/2018 7/16/2018 7/17/2018 7/18/2018 7/19/2018 7/20/2018 7/23/2018 7/24/2018 7/25/2018 7/26/2018 7/27/2018 7/30/2018 7/31/2018 8/1/2018 8/2/2018 8/3/2018 8/6/2018 8/7/2018 8/8/2018 8/9/2018 8/10/2018 8/13/2018 8/14/2018 8/15/2018 8/16/2018 8/17/2018 8/20/2018 8/21/2018 8/22/2018 8/23/2018 8/24/2018 8/27/2018 8/28/2018 8/29/2018 8/30/2018 8/31/2018 9/4/2018 9/5/2018 9/6/2018 9/7/2018 9/10/2018 9/11/2018 9/12/2018 9/13/2018 9/14/2018 9/17/2018 9/18/2018 9/19/2018 9/20/2018 9/21/2018 9/24/2018 9/25/2018 9/26/2018 9/27/2018 9/28/2018 10/1/2018 10/2/2018 10/3/2018 10/4/2018 10/5/2018 10/8/2018 10/9/2018 ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### 11/1/2018 11/2/2018 11/5/2018 11/6/2018 11/7/2018 11/8/2018 11/9/2018 ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### 12/3/2018 12/4/2018 12/6/2018 12/7/2018 ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### 1/2/2019 1/3/2019 1/4/2019 1/7/2019 1/8/2019 1/9/2019 1/10/2019 1/11/2019 1/14/2019 1/15/2019 1/16/2019 1/17/2019 1/18/2019 1/22/2019 1/23/2019 1/24/2019 1/25/2019 1/28/2019 1/29/2019 1/30/2019 1/31/2019 2/1/2019 2/4/2019 2/5/2019 2/6/2019 2/7/2019 2/8/2019 2/11/2019 2/12/2019 2/13/2019 2/14/2019 2/15/2019 2/19/2019 2/20/2019 2/21/2019 2/22/2019 2/25/2019 2/26/2019 2/27/2019 2/28/2019 3/1/2019 3/4/2019 3/5/2019 3/6/2019 3/7/2019 3/8/2019 3/11/2019 3/12/2019 3/13/2019 3/14/2019 3/15/2019 3/18/2019 3/19/2019 3/20/2019 3/21/2019 3/22/2019 3/25/2019 3/26/2019 3/27/2019 3/28/2019 3/29/2019 4/1/2019 4/2/2019 4/3/2019 4/4/2019 4/5/2019 4/8/2019 4/9/2019 4/10/2019 4/11/2019 4/12/2019 4/15/2019 4/16/2019 4/17/2019 4/18/2019 4/22/2019 4/23/2019 4/24/2019 4/25/2019 4/26/2019 4/29/2019 4/30/2019 5/1/2019 5/2/2019 5/3/2019 5/6/2019 5/7/2019 5/8/2019 5/9/2019 5/10/2019 5/13/2019 5/14/2019 5/15/2019 5/16/2019 5/17/2019 5/20/2019 5/21/2019 5/22/2019 5/23/2019 5/24/2019 5/28/2019 5/29/2019 5/30/2019 5/31/2019 6/3/2019 6/4/2019 6/5/2019 6/6/2019 6/7/2019 6/10/2019 6/11/2019 6/12/2019 6/13/2019 6/14/2019 6/17/2019 6/18/2019 6/19/2019 6/20/2019 6/21/2019 6/24/2019 6/25/2019 6/26/2019 6/27/2019 6/28/2019 7/1/2019 7/2/2019 7/3/2019 7/5/2019 7/8/2019 7/9/2019 7/10/2019 7/11/2019 7/12/2019 7/15/2019 7/16/2019 7/17/2019 7/18/2019 7/19/2019 7/22/2019 7/23/2019 7/24/2019 7/25/2019 7/26/2019 7/29/2019 7/30/2019 7/31/2019 8/1/2019 8/2/2019 8/5/2019 8/6/2019 8/7/2019 8/8/2019 8/9/2019 8/12/2019 8/13/2019 8/14/2019 8/15/2019 8/16/2019 8/19/2019 8/20/2019 8/21/2019 8/22/2019 8/23/2019 8/26/2019 8/27/2019 8/28/2019 8/29/2019 8/30/2019 9/3/2019 9/4/2019 9/5/2019 9/6/2019 9/9/2019 9/10/2019 9/11/2019 9/12/2019 9/13/2019 9/16/2019 9/17/2019 9/18/2019 9/19/2019 9/20/2019 9/23/2019 9/24/2019 9/25/2019 9/26/2019 9/27/2019 9/30/2019 10/1/2019 10/2/2019 10/3/2019 10/4/2019 10/7/2019 10/8/2019 10/9/2019 ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### 11/1/2019 11/4/2019 11/5/2019 11/6/2019 11/7/2019 11/8/2019 ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### 12/2/2019 12/3/2019 12/4/2019 12/5/2019 12/6/2019 12/9/2019 ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### ######### IndexNums StartYearIndex EndYearIndex StockIndex TotalYearCount StartDateAllowed EndDateAllowed Google spreadsheet "StockPriceRetrieve" that you created in class to he closing prices of the S&P 500 (.INX), Apple Inc (APPL), and Bed yond (BBBY) for the period between 12/30/2016 and 12/31/2019. Goold Sheet file with financestrose@gmail.com before or at the time subbmit your final exam. d paste the data you generated from part a to columns B, C, & D. late the daily returns for the three series in columns E, F & G. chart (aka scatter chart) to show the charcteristic line of either (The X axis is R.Market whereas the Y axis is either R.APPL or ake sure a linear trendline is turned on.) should be able to define the start and end dates of the chart as well eries to be charted. (The chart should show at least 30 trading days of named ranges in data validation for "StartDate" and for . Also use data validation for "Series". amically named ranges to define "XAxis" and "YAxis" pe" for the names "XAxis" and "YAxis" is at the "worksheet" level: or the chart should be ='Question 1'!XAxis or the chart should be ='Question 1'!YAxis) pe" for the names "XAxis" and "YAxis" is at the "workbook" level: or the chart should be ='[Week15 FinalExam.xlsx]XAxis or the chart should be ='[Week15 FinalExam.xlsx]YAxis med your file, you should replace "Week15 FinalExam" with your Question 2: The following table includes information on the number of people in different gender/age groups who watched TV shows on various networks and the cost (in thousands of dollars) of placing a 30-second ad on each show/network. For example, it costs $180,000 to place a 30-second ad on CBS Primetime. The show was watched by 6 million males between the ages 18 and 35, 3 million males between 36 and 55, 1 million males over 55, 9 million females between 18 and 35, 4 million females between 36 and 55, and 2 million females over 55. The data also includes the number of people in each age group (in millions) to whom you would want to show the ad. For example, the advertiser wanted at least 60 million 18 to 35 year old males to see its ads. a. Use the solver tool to find the cheapest way (i.e. # of ads for each show) to meet these goals. b. Redo part a but with the added constraints that at least one ad must be placed with Fox News and one with CNN. c. Use the senario analysis tool to prepare a scenario summary with your results to parts a and b as the two scenarios. d. Explain your answer in the textbox provided. # of ads (in 000s) Cost 180 100 80 9 13 16 8 85 Show/Network CBS Primetime Fox News MSNBC HBO ESPN Food network CNN MTV # of viewers required # of viewers watching Total Cost Explain your answer here: M 18-35 6 6 5 0.5 0.7 0.1 0.1 1 60 Gender and Age Groups for TV Viewers M 36-55 M >55 W 18-35 W 36-55 3 1 9 4 5 3 1 1 2 0 4 2 0.5 0.3 0.1 0.1 0.2 0 0.9 0.1 0.1 0 0.6 1.3 0.2 0.3 0.1 0.2 2 4 1 3 60 28 60 60 ge groups who second ad on metime. The show 6 and 55, 1 million 55, and 2 million ons) to whom you o 35 year old males News and one s a and b as the two Viewers W >55 2 1 0 0 0 0.4 0.3 4 28 Question 3: a. Create a Profitability Index (PI) calculator where PI = SUM(PV of Positive CF) SUM(PV of Negative CF) To get started, you may use a discount rate (r) that equals 10% with the following cash flows (from year 0 to year 5): -120,000; 39,000; 30,000; -21,000; 37,000; 46,000 a1. The column "CashFlow" is the only column that can be hard-coded. a2. Must use the Excel table naming convention. b. Write a subroutine and create a micro button "InputCashflow" that asks the user what each of the cashflow amount is and immediately fill in the cashflow in the table. (Make sure you FIRST ask the user for the total number of periods so that you know how many times the For Next Loop needs to run.) PI r Period CashFlow DiscountedCF -120000 39000 30000 -21000 37000 46000 ...
Student has agreed that all tutoring, explanations, and answers provided by the tutor will be used to help in the learning process and in accordance with Studypool's honor code & terms of service.

This question has not been answered.

Create a free account to get help with this and any other question!

Similar Questions
Related Tags