60.1K

Verified Solution

Link Copied!

I need step 4 a. done please

I need step 4 a. done please

Step 1: - Add a line to the "Assumptions" table. Label it "Scenario" and put 1 (the number) in the value column. - Somewhere on the same worksheet, make a table with the following four columns "Scenario", "Sales @ 1", "Economic Salvage", and "Increase in Sales". In the first row (Scenario 1) put the values for Sales at time 1, Economic Salvage value, and Increase in Sales that were used in the problem (type these numbers in). Step 2: - Make a 10 scenario table (make sure to number your scenarios in the "Scenario" column) with random values for "Sales @ 1", "Economic Salvage" and "Increase in Sales." Assume that Sales @ 1 are distributed normally with a mean of $500,000a standard deviation of $10,000. Assume that Economic Salvage Value is distributed normally with mean of $600,000 and a standard deviation of $20,000. Assume that Increase in Sales is distributed normally with a mean of $50,000 and standard deviation of $2,500. - To create a random draw from a normal distribution with mean x and standard deviation y, use the function =NORM.INV(RAND (),x,y) - At this point, to avoid the numbers constantly updating (if you want to), go to the Formulas tab, then Calculation Options, and select Manual. Also, go to File>Options>Formulas and under Calculation Options make sure to select Manual and uncheck "Recalculate workbook before saving." Two things to note: this will make it easier to do the rest of the assignment and you will need to tell excel to recalculate formulas if you drag a formula around. Also, you will want to undo these selections when you are done with the assignment. - NOTE: The values in these cells will change every time you change something in the spreadsheet. That is OK! They are supposed to be random anyway. Step 3: - In the Assumptions Table, change the values for Sales 1, Economic Salvage, and Increase in Sales. Specifically, put = VLOOKUP(Scenario Number, Scenario Table, Value Column) where - Scenario Number is a reference to the value of the cell in the Scenario part of the Assumptions Table, - Scenario Table is the table you made in Step 2 (including the Scenario column), - Value column is the number of the column in the Scenario Table that corresponds to the quantity of interest (eg. If you are doing the Sales 1 value, then the column number should correspond to the column of your table with "Sales @ 1 values.") Step 4: It's Data Table time! - First, expand your table from Step 2 to be 1,000 scenarios. DON'T FORGET TO CHANGE YOUR VLOOKUP FUNCTIONS FROM PART 3 TO INCLUDE ALL 1,000 SCENARIOS! - Make a list somewhere on the sheet from 1-1,000. You are going to make a table with NPV for 1,000 different scenarios. - Above the column next to your list from 1-1,000, reference the cell containing NPV (E19 in the original spread sheet). - Highlight both columns, including the cell with the NPV reference in it, and go to Data > What-if-analysis > Data Table - In Column Input Cell, reference the cell that contains your scenario number in the Assumptions Table. - Click OK through the menus and watch the magic happen! - The Data Table is plugging each scenario number into the scenario cell of the assumptions table - The VLOOKUP formulas in Sales 1, Economic Salvage, and Sales Increase is finding the value for each of these variables that corresponds to the scenario number Data Table plugged into the Scenario cell of the Assumptions table. - The Data Table is then taking the NPV that results from plugging these values in, and puts it in the column to the left of your list from 1-1,000 - In Excel, answer the questions below. In all cases, try to make your answer DYNAMIC (or live), meaning they will change as the random numbers change. a. Create a Histogram of NPV outcomes with $10,000 buckets. HINT: The most recent version of excel has a built in Histogram function. Otherwise look up COUNTIFS to create your own, or Histogram (as part of the data analysis (stats) package in Excel. You may have to Enable Analysis ToolPak). Step 1: - Add a line to the "Assumptions" table. Label it "Scenario" and put 1 (the number) in the value column. - Somewhere on the same worksheet, make a table with the following four columns "Scenario", "Sales @ 1", "Economic Salvage", and "Increase in Sales". In the first row (Scenario 1) put the values for Sales at time 1, Economic Salvage value, and Increase in Sales that were used in the problem (type these numbers in). Step 2: - Make a 10 scenario table (make sure to number your scenarios in the "Scenario" column) with random values for "Sales @ 1", "Economic Salvage" and "Increase in Sales." Assume that Sales @ 1 are distributed normally with a mean of $500,000a standard deviation of $10,000. Assume that Economic Salvage Value is distributed normally with mean of $600,000 and a standard deviation of $20,000. Assume that Increase in Sales is distributed normally with a mean of $50,000 and standard deviation of $2,500. - To create a random draw from a normal distribution with mean x and standard deviation y, use the function =NORM.INV(RAND (),x,y) - At this point, to avoid the numbers constantly updating (if you want to), go to the Formulas tab, then Calculation Options, and select Manual. Also, go to File>Options>Formulas and under Calculation Options make sure to select Manual and uncheck "Recalculate workbook before saving." Two things to note: this will make it easier to do the rest of the assignment and you will need to tell excel to recalculate formulas if you drag a formula around. Also, you will want to undo these selections when you are done with the assignment. - NOTE: The values in these cells will change every time you change something in the spreadsheet. That is OK! They are supposed to be random anyway. Step 3: - In the Assumptions Table, change the values for Sales 1, Economic Salvage, and Increase in Sales. Specifically, put = VLOOKUP(Scenario Number, Scenario Table, Value Column) where - Scenario Number is a reference to the value of the cell in the Scenario part of the Assumptions Table, - Scenario Table is the table you made in Step 2 (including the Scenario column), - Value column is the number of the column in the Scenario Table that corresponds to the quantity of interest (eg. If you are doing the Sales 1 value, then the column number should correspond to the column of your table with "Sales @ 1 values.") Step 4: It's Data Table time! - First, expand your table from Step 2 to be 1,000 scenarios. DON'T FORGET TO CHANGE YOUR VLOOKUP FUNCTIONS FROM PART 3 TO INCLUDE ALL 1,000 SCENARIOS! - Make a list somewhere on the sheet from 1-1,000. You are going to make a table with NPV for 1,000 different scenarios. - Above the column next to your list from 1-1,000, reference the cell containing NPV (E19 in the original spread sheet). - Highlight both columns, including the cell with the NPV reference in it, and go to Data > What-if-analysis > Data Table - In Column Input Cell, reference the cell that contains your scenario number in the Assumptions Table. - Click OK through the menus and watch the magic happen! - The Data Table is plugging each scenario number into the scenario cell of the assumptions table - The VLOOKUP formulas in Sales 1, Economic Salvage, and Sales Increase is finding the value for each of these variables that corresponds to the scenario number Data Table plugged into the Scenario cell of the Assumptions table. - The Data Table is then taking the NPV that results from plugging these values in, and puts it in the column to the left of your list from 1-1,000 - In Excel, answer the questions below. In all cases, try to make your answer DYNAMIC (or live), meaning they will change as the random numbers change. a. Create a Histogram of NPV outcomes with $10,000 buckets. HINT: The most recent version of excel has a built in Histogram function. Otherwise look up COUNTIFS to create your own, or Histogram (as part of the data analysis (stats) package in Excel. You may have to Enable Analysis ToolPak)

Answer & Explanation Solved by verified expert

See Answer