In the Data Analytics Cases in the previous chapter, you used Tableau to examine a data set and create charts to examine two (hypothetical) publicly traded companies: GPS Corporation and Tru, Inc., to examine the effect of the Tax Cuts and Jobs Act of 2017 on these companies' operations and financial position. Now, you examine the funded status of the two companies' pension plans and any changes in that funded status during the previous ten years. You will also observe the change in the way components of pension expense are reported in the income statement. Tableau Instructions: Download the "GPS Tru Financials.xlsx" Excel file available in Connect, or under Student Resources within the Library tab. Save it to the computer on which you will be using Tableau For this case, you will create calculations to produce the funded status of the companies' pension plans to allow you to compare and contrast the two companies. After you view the training videos, follow these steps to create the charts you'll use for this case: 1. Open Tableau and connect to the Excel spreadsheet you downloaded 2. Click on the Sheet 1 tab, at the bottom of the canvas, to the right of the Data Source at the bottom of the screen. Drag "Company" and "Year" under "Dimensions" to the Columns shelf. Change "Year" to discrete by right-clicking and selecting "Discrete." 3. Create a calculated field by clicking the "Analysis" tab at the top of the screen and selecting "Create Calculated field." Name the calculation "Pension asset/liability." In the Calculation Editor window, from "Measures," drag "Pension Plan Assets", type a minus sign, and then drag "Projected Benefit Obligation". Make sure the window says that the calculation is valid and click OK. 4. Drag the newly created "Pension asset/liability" under "Measures" to the Row shelf. Click on the "Show Me" and select "side-by-side bars." Add labels to the bars by clicking on "Label" under the "Marks card" and clicking the box "Show mark label." Format the labels to Times New Roman. bold. black and 10-point font. Edit the color on the "Color Mark" card if desired. sign, and then drag "Projected Benefit Obligation". Make sure the window says that the calculation is valid and click OK. 4. Drag the newly created "Pension asset/liability" under "Measures" to the Row shelf. Click on the "Show Me" and select "side-by-side bars." Add labels to the bars by clicking on "Label" under the "Marks card" and clicking the box "Show mark label." Format the labels to Times New Roman, bold, black and 10-point font. Edit the color on the "Color Mark" card if desired. 5. Change the title of the sheet to be "Pension asset/liability." by right-clicking and selecting "Edit title." Format the title to Times New Roman, bold, black and 15-point font. Change the title of "Sheet 1" to match the sheet title by right-clicking, selecting "Rename" and typing in the new title 6. Format all other labels to be Times New Roman, bold, black and 12-point font 7. Click on the New Worksheet tab on the lower left ("Sheet 2" should open) and follow the procedure outlined in Instruction #2 for the company and year. 8. Drag "Pension service cost" and "Pension non-service cost component" under "Measures" into the Rows shelf. Click on the "Show Me" and select "side-by-side bars." Edit the axis of each by selecting the axis, right-clicking, and clicking on "Edit Axis. . .". Select "Fixed" and change the lower range to be -20 and the upper range to be 600 for both charts. 9. If not already included, add the labels to the bar chart. Add labels to the bars by clicking on "Label" under the "Marks" card and clicking the box "Show mark labels." Format the labels to Times New Roman, bold, black and 10-point font. Edit the color on the "Marks" card if desired. 10. Change the title of the sheet to be "Pension Service Cost and Non Service Cost" by right-clicking and selecting "Edit title." Format the title to Times New Roman, bold, black and 15-point font. Change the title of "Sheet 2" to match the sheet title by right-clicking, selecting "Rename" and typing in the new title. 11. Format all other labels to be Times New Roman, bold, black and 12-point font 12. Once complete, save the file as "DA17_Your initials.twbx." Required: Based upon what you find, answer the following questions: 11. Format all other labels to be Times New Roman, bold, black and 12-point font. 12. Once complete, save the file as "DA17_Your initials.twbx." Required: Based upon what you find, answer the following questions: A. In which years is GPS's pension plan underfunded during the period 2012-2021? B. In which years is Tru, Inc.'s pension plan underfunded during the period 2012-2021? C. In which year did the two companies begin reporting the service cost and non-service cost components of the net pension cost separately in their income statements? D. What are the (a) service cost and (b) non-service cost components of the net pension cost for GPS in 2021? (Enter your answer in thousands.) A. GPS's pension plan was underfunded from B. Tru's pension plan was underfunded from C. 1.GPS Corporation 2. Tru, Inc. D. 1. Service cost 2. Non-service cost to 2018 to 2021 2012 2012 2018 2018 In the Data Analytics Cases in the previous chapter, you used Tableau to examine a data set and create charts to examine two (hypothetical) publicly traded companies: GPS Corporation and Tru, Inc., to examine the effect of the Tax Cuts and Jobs Act of 2017 on these companies' operations and financial position. Now, you examine the funded status of the two companies' pension plans and any changes in that funded status during the previous ten years. You will also observe the change in the way components of pension expense are reported in the income statement. Tableau Instructions: Download the "GPS Tru Financials.xlsx" Excel file available in Connect, or under Student Resources within the Library tab. Save it to the computer on which you will be using Tableau For this case, you will create calculations to produce the funded status of the companies' pension plans to allow you to compare and contrast the two companies. After you view the training videos, follow these steps to create the charts you'll use for this case: 1. Open Tableau and connect to the Excel spreadsheet you downloaded 2. Click on the Sheet 1 tab, at the bottom of the canvas, to the right of the Data Source at the bottom of the screen. Drag "Company" and "Year" under "Dimensions" to the Columns shelf. Change "Year" to discrete by right-clicking and selecting "Discrete." 3. Create a calculated field by clicking the "Analysis" tab at the top of the screen and selecting "Create Calculated field." Name the calculation "Pension asset/liability." In the Calculation Editor window, from "Measures," drag "Pension Plan Assets", type a minus sign, and then drag "Projected Benefit Obligation". Make sure the window says that the calculation is valid and click OK. 4. Drag the newly created "Pension asset/liability" under "Measures" to the Row shelf. Click on the "Show Me" and select "side-by-side bars." Add labels to the bars by clicking on "Label" under the "Marks card" and clicking the box "Show mark label." Format the labels to Times New Roman. bold. black and 10-point font. Edit the color on the "Color Mark" card if desired. sign, and then drag "Projected Benefit Obligation". Make sure the window says that the calculation is valid and click OK. 4. Drag the newly created "Pension asset/liability" under "Measures" to the Row shelf. Click on the "Show Me" and select "side-by-side bars." Add labels to the bars by clicking on "Label" under the "Marks card" and clicking the box "Show mark label." Format the labels to Times New Roman, bold, black and 10-point font. Edit the color on the "Color Mark" card if desired. 5. Change the title of the sheet to be "Pension asset/liability." by right-clicking and selecting "Edit title." Format the title to Times New Roman, bold, black and 15-point font. Change the title of "Sheet 1" to match the sheet title by right-clicking, selecting "Rename" and typing in the new title 6. Format all other labels to be Times New Roman, bold, black and 12-point font 7. Click on the New Worksheet tab on the lower left ("Sheet 2" should open) and follow the procedure outlined in Instruction #2 for the company and year. 8. Drag "Pension service cost" and "Pension non-service cost component" under "Measures" into the Rows shelf. Click on the "Show Me" and select "side-by-side bars." Edit the axis of each by selecting the axis, right-clicking, and clicking on "Edit Axis. . .". Select "Fixed" and change the lower range to be -20 and the upper range to be 600 for both charts. 9. If not already included, add the labels to the bar chart. Add labels to the bars by clicking on "Label" under the "Marks" card and clicking the box "Show mark labels." Format the labels to Times New Roman, bold, black and 10-point font. Edit the color on the "Marks" card if desired. 10. Change the title of the sheet to be "Pension Service Cost and Non Service Cost" by right-clicking and selecting "Edit title." Format the title to Times New Roman, bold, black and 15-point font. Change the title of "Sheet 2" to match the sheet title by right-clicking, selecting "Rename" and typing in the new title. 11. Format all other labels to be Times New Roman, bold, black and 12-point font 12. Once complete, save the file as "DA17_Your initials.twbx." Required: Based upon what you find, answer the following questions: 11. Format all other labels to be Times New Roman, bold, black and 12-point font. 12. Once complete, save the file as "DA17_Your initials.twbx." Required: Based upon what you find, answer the following questions: A. In which years is GPS's pension plan underfunded during the period 2012-2021? B. In which years is Tru, Inc.'s pension plan underfunded during the period 2012-2021? C. In which year did the two companies begin reporting the service cost and non-service cost components of the net pension cost separately in their income statements? D. What are the (a) service cost and (b) non-service cost components of the net pension cost for GPS in 2021? (Enter your answer in thousands.) A. GPS's pension plan was underfunded from B. Tru's pension plan was underfunded from C. 1.GPS Corporation 2. Tru, Inc. D. 1. Service cost 2. Non-service cost to 2018 to 2021 2012 2012 2018 2018