Required information

Applying Tableau Analytics 11-01 (Static) Return on Investment [LO11-1]

Skip to question

To complete this exercise, you will need to download and install Tableau on your computer. Tableau provides free instructor and student licenses as well as free videos and support for utilizing and learning the software. Once you are up and running with Tableau, watch the three "Getting Started" Tableau videos. All of Tableaus short training videos can be found here.

[The following information applies to the questions displayed below.] Calloway Company is a merchandiser that has provided the following operating results for this year.

Sales	$ 2,750,000
Variable expenses	2,024,000
Contribution margin	726,000
Fixed expenses	572,000
Net operating income	$ 154,000
Average operating assets	$ 1,306,350

For next year the company is considering three investment alternatives with the following estimated incremental sales, variable expenses, fixed expenses, and operating assets:

	Option 1	Option 2	Option 3
Sales	$ 2,500,000	$ 2,887,500	$ 3,575,000
Variable expenses	$ 1,840,000	$ 2,125,200	$ 2,631,200
Fixed expenses	$ 600,000	$ 520,000	$ 750,000
Average operating assets	$ 780,000	$ 1,850,000	$ 1,200,000

You have been asked to create some data visualizations that depict the projected margin, turnover, and return on investment (ROI) for the three investment alternatives relative to this years performance. Download the Excel file, which you will use to create the Tableau visualization that aid your explanation.

Upload the Excel file into Tableau by doing the following:

1. Open the Tableau Desktop application.
2. On the left-hand side, under the Connect header and the To a file sub-header, click on Microsoft Excel.
3. Choose the Excel file and click Open.
4. Since the only worksheet in the Excel File is Calloway Company it will default as a selection with no further import steps needed

Applying Tableau Analytics 11-01 (Static) Part 3

Create a formula that calculates this years return on investment (ROI) and the estimated ROI for each of the three investment alternatives. Then prepare a vertical bar chart that compares the four ROIs for the four scenarios:

• Begin by creating a new calculated field
  • Click on Analysis from the menu dropdowns at the top and choose Create a calculated field
    • Replace the current calculated field name Calculation1 with Return on Investment
    • Type in the following formula:
      • [Margin]*[Turnover]
      • This formula is stating that you want the value of the Margin multiplied by the Turnover
• Double click on new sheet at the bottom of the workbook and change the name of the newly created Sheet 3 to ROI Analysis
• On the left-hand side under Dimensions (sometimes labeled as Tables), click on Option and drag and drop it into the Columns area.
• On the left-hand side under Measures, click on the newly created measure of Return on Investment and drag and drop it into the Rows area
  • This should default to a vertical column chart
• On the left-hand side under Dimensions (sometimes labeled as Tables), click on Option and drag and drop it onto the Colors Marks card ().
  • This will allow the options to stand out better
• Click on the Label button () at the top of the screen to show the margin values
• To show the data labels as percentages, do the following:
  • Right-click on the Y-axis and choose Format:
    • In the Format pane, click on the Pane tab, and in the Default section, change the Numbers dropdown to Percentage and modify the decimal places to 1.
• To improve viewing, locate the Standard dropdown option in the menu bar at the top of the screen. Click on that dropdown and choose Entire View.
• Your visualization should appear as follows:

Required: (Note that for all questions below you may select more than one answer. Single click the box with the question mark to produce a check mark for a correct answer and double click the box with the question mark to empty the box for a wrong answer. Any boxes left with a question mark will be automatically graded as incorrect.) 3a. Which of the following investment alternatives for next year earns the highest return on investment (ROI)?

check all that apply 1

• Option 1unanswered
• Option 2unanswered
• Option 3unanswered

3b. Which of the following investment alternatives for next year earns the lowest return on investment (ROI)?

check all that apply 2

• Option 1unanswered
• Option 2unanswered
• Option 3unanswered

3c. Which of the following statements is true?

check all that apply 3

• None of the three options earns a higher return on investment (ROI) than this year.unanswered
• Only one of the three options earns a higher return on investment (ROI) than this year.unanswered
• Two of the three options earn a higher return on investment (ROI) than this year.unanswered
• All three options earn a higher return on investment (ROI) than this year.

Option	Average operating assets	Sales	Variable expenses	Contribution margin	Fixed expenses	Net operating income
This Year	$ 1,306,350	$ 2,750,000	$ 2,024,000	$ 726,000	$ 572,000	$ 154,000
Option 1	$ 780,000	$ 2,500,000	$ 1,840,000	$ 660,000	$ 600,000	$ 60,000
Option 2	$ 1,850,000	$ 2,887,500	$ 2,125,200	$ 762,300	$ 520,000	$ 242,300
Option 3	$ 1,200,000	$ 3,575,000	$ 2,631,200	$ 943,800	$ 750,000	$ 193,800