earning objectives

$1.$ Create a pivot table in Excel

$2.$ Format a pivot table

$3.$ Apply filters to a pivot table

$4.$ Create sum columns in a pivot table

$5.$ Create a calculated field

$6.$ Use the slicer tool in a pivot table

$7.$ Analyze data for errors

$8.$ Create a pivot chart in Excel

Data set background

Toni Tapler, owner of Toni$$s Tacos has contacted your accounting firm to review her fixed asset

records. Toni$$s Tacos specializes in serving authentic Mexican food. The company divides its

reporting into six different city areas where the restaurants are located. These city areas are

Boston, Chicago, Los Angeles, Miami, New York, and San Francisco.

Toni has provided you with a spreadsheet containing the company$$s fixed asset data and has

asked that your firm prepare a fixed asset schedule and answer various questions concerning the

company$$s fixed assets.

In addition to the fixed asset data, Toni included the following information from the company$$s

fixed asset policies and procedures manual.

Fixed asset categories are defined as follows:

Land Vacant land parcels purchased for building sites, parking lots, or other purposes.

Land Improvements Site improvements $($other than buildings$)$ such as fencing and parking lots.

Buildings Permanent structures $($including permanently attached fixtures$).$

Equipment Self$-$explanatory.

Furniture Self$-$explanatory.

Data dictionary

$$ CityArea: Indicates the city area location of the fixed asset.

$$ AssetID: This field is a sequential number assigned at purchase. This is a unique

identified.

$$ AssetCategory: Indicates the fixed asset category.

$$ InSvcDate: This is the date the asset was put into service.

$$ Cost: The purchase price of the asset.

$$ SalvageValue: The salvage value assigned by the company.

$$ DeprMeth: The depreciation method used for the asset. NoDep indicates that no

depreciation is being calculated. SL indicates the asset is being depreciated using the

straight$-$line convention.

$$ Useful Life: The useful life assigned by the company.

$$ BegAccumDepr: Beginning accumulated depreciation as of $1/1/2017.$

$$ CurrentYearDepr: Current year depreciation for $2017.$

Requirements:

For each of the following requirements, create a new pivot table in a new worksheet in Excel.

Name each new worksheet as $$Req $1,$Req $2,$ etc. Format the dollar amounts in each pivot

table or pivot chart using the accounting format with zero decimal places. Format non$-$currency

numbers in each pivot table or pivot chart using the accounting format with zero decimal places.

Place your name in cell A$1$ of the worksheets. Review assignment rubric for specific grade

evaluation

$1.$ Prepare a pivot table of a fixed asset schedule that contains the totals for cost, beginning

accumulated depreciation, current year depreciation, ending accumulated depreciation,

and ending book value by city. $($Hint: Create a calculated field for Ending Accumulated

Depreciation and Ending Book Value.$)$ What is the total ending book value for the Los

Angeles area? How much depreciation was taken during the year for the entire company?

$2.$ Which city$($s$)$ does not have land?

$3.$ There is a concerned that there might be some errors in the data for the Los Angeles

area. Prepare a fixed asset schedule $($similar to requirement $1)$ for the Los Angeles area

by asset category. Identify the asset ID which has the error and explain the error

involved. $($Hint: Use the slicer. Don$$t worry about checking any calculations.$)$

$4.$ Prepare a pivot table that shows the book value of the furniture category by city

area. Prepare a pivot chart using the column chart type for this information.