Jim Nelson, owner of Hula Hut Burger has contacted your accounting firm to review his fixed asset records. Hula Hut Burger is the home of the “tastiest burger in the world” and specializes on serving all-organic beef and locally sourced food with a Hawaiian flair. The company has restaurants throughout the United States and divides its reporting into six regions (South, Mid-Atlantic, Southwest, Northeast, West, and Midwest).

Jim 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, Jim 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.

Note:  This data analytics activity uses an anonymized fixed asset record from a small company for the years of 1991 – 2014 and contains approximately 1,500 records.

Data dictionary

• RegionalArea : Indicates the regional 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/2014.
• CurrentYearDepr: Current year depreciation for 2014.

Requirements

For each of the following requirements, create a new pivot table in a new worksheet. 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.

• Prepare a fixed asset schedule that contains the totals for cost, beginning accumulated depreciation, current year depreciation, ending accumulated depreciation, and ending book value by regional areas. (Hint: Create a calculated field for Ending Accumulated Depreciation and Ending Book Value.) What is the total ending book value for the Northeast region? How much depreciation was taken during the year for the entire company?
• Which region(s) does not have land?
• Jim is concerned that there might be some errors in the data for the Northeast. Prepare a fixed asset schedule (similar to requirement 1) for the Northeast 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.)
• What is the total cost of assets purchased by asset category in 2014? (Hint: Use the Timeline Tool.)
• How much equipment was purchased in the south in 2014? (Hint: Use the Timeline Tool.)
• What are the total cost of assets purchased by category before 2000?What is the amount per year per category? (Hint: Use the Timeline Tool.)
• Prepare a pivot table that shows the book value of the furniture category by region. Prepare a pivot chart using the column chart type for this information.