In the Chapter 16 Applying Tableau, you examined the effect of the Tax Cuts and Jobs Act of 2017 on the operations and financial position of two (hypothetical) publicly traded companies: GPS Corporation and Tru, Incorporated Now, you will 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:

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.

Follow these steps to create the charts youll use for this case:

Download the Excel file GPS_Tru_Financials.

Open Tableau and connect to the Excel file.

Click on the Sheet 1 tab, at the bottom of the canvas and drag Company and Year to the Columns shelf. Change Year to discrete by right-clicking and selecting Discrete.

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 Measure Names, drag "Pension Plan Assets", type a minus sign, and then drag Projected Benefit Obligation. Make sure the calculation is valid and click OK.

Drag the newly created Pension asset/liability under Measure Names 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 according to preference and edit the color on the Color Mark card if desired. To format the marks labels, right-click on the field in the Rows Shelf and choose format. Then select Pane and edit the number format.

Change the title of the sheet to be Pension asset/liability. by double-clicking the tab at the bottom of the page and typing the new title.

Format all other labels according to preference.

Click on the New Worksheet tab on the lower left (Sheet 2 should open) and follow the procedure outlined above for the company and year.

Drag Pension service cost and Pension non-service cost component under Measure Names into the Rows shelf. Edit the axis of each account on the y-axis 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.

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 according to preference and edit the color on the Marks card if desired.

Change the title of the sheet to be "Pension Service Cost and Non Service Cost" by double-clicking the tab at the bottom of the page and typing the new title.

Format all other labels according to preference.

Save your work.

Required:

Based upon your output, answer the following questions:

In which years is GPSs pension plan underfunded during the period 2012-2021?

In which years is Tru, Incorporateds pension plan underfunded during the period 2012-2021?

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?

What are the (a) service cost and (b) non-service cost components of the net pension cost for GPS in 2021?

Note: Enter your answer in thousands.

A.	GPSs pension plan was underfunded from			to
B.	Trus pension plan was underfunded from			to
C.	1.	GPS Corporation
	2.	Tru, Incorporated
D.	1.	Service cost
	2.	Non-service cost