Applying Tableau: Assignment $5,$ Chapter $12[$similar to EX $12-48]$

Gunnell Incorporated is considering two mutually exclusive $10-$year investments. These two projects also have three different scenarios that could play out, best case, worst case, and expected case. The initial cash outlays, expected net after$-$tax cash flows, Net Present Value $($NPV$),$ and Internal Rate of Return $($IRR$)$ are presented in the excel file below. You need to put together a visualization for management as they consider these two projects.

Click here to access the Assignment $5$ Excel File of Project Data

Steps:

Open a new Tableau Workbook. Select the $$Connect to Data$$ option in the Data tab on your blank worksheet page, or you can select Data Source in the bottom left$-$hand corner. Next, select Microsoft Excel in the list of connection sources. You will then select the Excel data file you just downloaded in your file explorer.

In order for Tableau to work with any of this data, we first need to pivot the data. While still in the data source window, select the first date $(1/1/2022)$ column. Then, while holding down the shift button, select the last date $(1/1/2032)$ column to highlight all of the columns that have a date in the heading. Right click $($or select the dropdown option that appears in the top right of the column header of your highlighted cells$)$ on any of these columns and select ivot. Rename the two new columns that have been created by your pivot, $$Date$$ and $$Value$$ respectively.

Part $1$

In a blank worksheet, either click the dropdown arrow that appears on the Date attribute or right click the Date attribute. Hover over the Change Data Type Option and select Date in the list of options that appears.

Drag and drop Date onto the Columns shelf.

Drag and drop Project onto the Rows shelf. Also, drag and drop Value onto the Rows shelf as well, but be sure it appears second to Project.

Create a calculated field by either right clicking anywhere in the blank space of the Data pane or selecting Create Calculated Field or click the Analysis tab and select Create Calculated Field from the dropdown menu. Title the new variable $$Discounted Cash Flows$.$ In the space for formulas, calculate the discounted value of a projects cash flow by dividing Value by $1$ plus the discount rate. The denominator then needs to be raised to the power of the number of years out the cash flows are. We can find this number using the DATEDIFF formula. First, indicate that $$year$$ is the date$\_$part, Start Date will next tell Tableau when the first date occurs, and Date will indicate to Tableau when the cash flow will occur. Altogether the formula should look similar to this, $[$Value$]/(1+[$Annual Interest Rate$])^($DATEDIFF$(\text{'}$year$\text{'},[$Start Date$],[$Date$])).$

Drag and drop Discounted Cash Flows onto the y$-$axis located on the right side of the chart. A black dashed line will appear when you are hovering over the right spot.

Right click on either the left or right side of the screen and select Synchronize Axis.

Under Marks, select the SUM$($Value$)$ option that will provide you with a new set of Mark options that are specific to the Value values represented. Select the dropdown menu that appears at the top of the menu and click Line.

Under Marks, select the SUM$($Discounted Cash Flows$)$ option that will provide you with a new set of Mark options that are specific to the Discounted Cash Flows values represented. Select the dropdown menu that appears at the top of the menu and click Bar.

Drag and drop Date onto the Filters shelf. Select Years as the filter criteria. Next select All then deselect $2022.$ We want to remove $2022$ from the criteria because this is the first year and includes the initial outlay. The initial outlay is represented by a negative number and makes the graph harder to interpret, so we are going to remove this year from consideration.

Drag and drop Scenario onto the Filters shelf. Select Expected Case. Right click the Scenario option that appears on the Filters shelf $($or select the dropdown option that appears on the right$-$hand side of the calculation$)$ and select Show Filter.

Renavigate to the Marks All menu by selecting All under the Marks title. Drag and drop Measure Names onto the Color mark.

Answer questions $1$ through $3.$

Part $2$

In a blank worksheet, drag and drop Scenario onto the Columns shelf. Drag and drop Project onto the Columns shelf.

Drag and drop Value onto the Rows shelf. Right click the Value calculation that appears on the Rows shelf $($or select the dropdown option that appears on the right$-$hand side of the calculation$).$ Select Edit in Shelf. Replace the sum function with $$ABS$($a Tableau function for absolute value$).$ Once you have typed in $$ABS$,$ click anywhere outside of the formula on the worksheet or hit Enter. You will notice that Tableau automatically changes the formula you entered to be $$SUM$($ABS$([$Value$])).$

Drag and drop Date onto the Filters shelf. Select the year $2022,$ which is the first year of both projects and the yea