[ad_1]
Learning 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
Data set background Charlie Day, owner of Rocket Burger has contacted your accounting firm to review his fixed asset records. Rocket Burger is the home of the “tastiest burger in the world” and specializes on serving all- organic beef and locally sourced food. The company has restaurants throughout the United States and divides its reporting into six regions (South, Mid-Atlantic, Southwest, Northeast, West, and Midwest).
Charlie 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 1995 – 2018 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.
Spring 2020
• 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/2018. • CurrentYearDepr: Current year depreciation for 2018.
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. You should turn in a word document (with your answers) and an excel spreadsheet showing your pivot table that was used to solve the questions asked.
1. (5 points) Before you look at the data, answer the following questions, a. What is accumulated depreciation? b. Use your book or the internet, and in your own words, what is a fixed asset? c. What is the typical journal entry to recognize depreciation? d. What financial statement(s) does the above journal affect and how? e. Look at the data. What types of fixed assets are not depreciated?
2. (4 points) 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 current year for the entire company?
3. (1 point) Which region(s) does not have land?
[Button id=”1″]
[ad_2]
Source link
