80.2K

Verified Solution

Link Copied!

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:

A$.$ In which years is GPS$\text{'}$s pension plan underfunded during the period $2012-2021?$

B$.$ In which years is Tru, Incorporated'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?$

Note: Enter your answer in thousands.

Answer & Explanation Solved by verified expert

See Answer