B H Useful Life AssetID Cost Ra BegAccumDepr CurrentYearDepr 516 6 48 38 1,145 957 262 1 RegionalArea 2 South 3 South 4 South 5 South 6 South 7 South 8 South 9 South 10 South 11 South 12 South 13 South 14 South 15 South 16 South 17 South 18 South 19 South 20 South 21 South 22 South 23 South 24 South 25 South AssetCategory 1 Land 2 Equipment 3 Equipment 4 Equipment 5 Furniture 6 Furniture 7 Furniture 8 Furniture 9 Furniture 10 Furniture 11 Furniture 12 Furniture 13 Furniture 14 Furniture 15 Furniture 16 Furniture 17 Furniture 18 Furniture 19 Furniture 20 Furniture 21 Furniture 22 Furniture 23 Furniture 24 Furniture InSvcDate 11/1/2014 11/1/2014 11/1/2014 11/1/2014 11/1/2014 11/1/2014 11/1/2014 11/1/2014 11/1/2014 11/1/2014 11/1/2014 11/1/2014 11/1/2014 11/1/2014 11/1/2014 11/1/2014 11/1/2014 11/1/2014 11/1/2014 11/1/2014 11/1/2014 11/1/2014 11/1/2014 11/1/2014 13,600 16,300 200 2,200 2,100 1,700 50,600 42,300 11,000 800 2,500 36,300 31,600 97,600 88,400 62,600 37,900 8,600 800 29,200 12,100 55,000 3,200 46.600 SalvageValue DeprMeth - NoDep 815 SL 10 SL - SL 105 SL 85 SL 2,530 SL 2,115 SL - SL 40 SL 125 SL 1,815 SL 1,580 SL 4,880 SL 4,420 SL - 1,895 SL 430 SL 40 SL 1,460 SL 605 SL 2,750 SL 160 SL 2.330 SL 18 57 821 715 2,208 2,000 1,490 857 195 18 660 SL 274 1,244 72 1.054 Hula Hut Burger (Financial Accounting) 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 6. Use the slicer tool in a pivot table 7. Analyze data for errors 8. Use the timeline tool in a pivot table 9. Create a pivot chart in Excel Data set background 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. 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. Depr Meth: 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. Instructions Download the Excel file of data, and perform the requirements using that file. Make sure that you are using a recent version of Excel (Excel 2016 or Excel 365). Older versions do not have the slicer or timeline tools. Go through the attached Powerpoint slides for detailed instructions on the Excel aspects of the assignment. Answer the questions noted below in bold by typing your answer in the Excel tab below the pivot table. Save the file and submit it on Blackboard. 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 numbers in each pivot table or pivot chart using the accounting format with zero decimal places. 1. 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? 2. Which region(s) does not have land? 3. 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.) 4. What is the total cost of assets purchased by asset category in 2014? (Hint: Use the Timeline Tool.) 5. How much equipment was purchased in the south in 2014? (Hint: Use the Timeline Tool.) 6. 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.) 7. 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. Hula Hut Burger dataset 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. 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 6. Use the slicer tool in a pivot table 7. Analyze data for errors 8. Use the timeline tool in a pivot table 9. Create a pivot chart in Excel B H Useful Life AssetID Cost Ra BegAccumDepr CurrentYearDepr 516 6 48 38 1,145 957 262 1 RegionalArea 2 South 3 South 4 South 5 South 6 South 7 South 8 South 9 South 10 South 11 South 12 South 13 South 14 South 15 South 16 South 17 South 18 South 19 South 20 South 21 South 22 South 23 South 24 South 25 South AssetCategory 1 Land 2 Equipment 3 Equipment 4 Equipment 5 Furniture 6 Furniture 7 Furniture 8 Furniture 9 Furniture 10 Furniture 11 Furniture 12 Furniture 13 Furniture 14 Furniture 15 Furniture 16 Furniture 17 Furniture 18 Furniture 19 Furniture 20 Furniture 21 Furniture 22 Furniture 23 Furniture 24 Furniture InSvcDate 11/1/2014 11/1/2014 11/1/2014 11/1/2014 11/1/2014 11/1/2014 11/1/2014 11/1/2014 11/1/2014 11/1/2014 11/1/2014 11/1/2014 11/1/2014 11/1/2014 11/1/2014 11/1/2014 11/1/2014 11/1/2014 11/1/2014 11/1/2014 11/1/2014 11/1/2014 11/1/2014 11/1/2014 13,600 16,300 200 2,200 2,100 1,700 50,600 42,300 11,000 800 2,500 36,300 31,600 97,600 88,400 62,600 37,900 8,600 800 29,200 12,100 55,000 3,200 46.600 SalvageValue DeprMeth - NoDep 815 SL 10 SL - SL 105 SL 85 SL 2,530 SL 2,115 SL - SL 40 SL 125 SL 1,815 SL 1,580 SL 4,880 SL 4,420 SL - 1,895 SL 430 SL 40 SL 1,460 SL 605 SL 2,750 SL 160 SL 2.330 SL 18 57 821 715 2,208 2,000 1,490 857 195 18 660 SL 274 1,244 72 1.054 Hula Hut Burger (Financial Accounting) 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 6. Use the slicer tool in a pivot table 7. Analyze data for errors 8. Use the timeline tool in a pivot table 9. Create a pivot chart in Excel Data set background 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. 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. Depr Meth: 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. Instructions Download the Excel file of data, and perform the requirements using that file. Make sure that you are using a recent version of Excel (Excel 2016 or Excel 365). Older versions do not have the slicer or timeline tools. Go through the attached Powerpoint slides for detailed instructions on the Excel aspects of the assignment. Answer the questions noted below in bold by typing your answer in the Excel tab below the pivot table. Save the file and submit it on Blackboard. 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 numbers in each pivot table or pivot chart using the accounting format with zero decimal places. 1. 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? 2. Which region(s) does not have land? 3. 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.) 4. What is the total cost of assets purchased by asset category in 2014? (Hint: Use the Timeline Tool.) 5. How much equipment was purchased in the south in 2014? (Hint: Use the Timeline Tool.) 6. 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.) 7. 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. Hula Hut Burger dataset 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. 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 6. Use the slicer tool in a pivot table 7. Analyze data for errors 8. Use the timeline tool in a pivot table 9. Create a pivot chart in Excel