Dataset Background: Jaines, the owner of Future Electronics has contacted you to review his fixed asset records. Future Electronics was a Canadian conusmer and home electronics store chain. It was established in 2017 in Vancouver, British Columbla. The company opened a few new stores in Ontario in 2019 and continued its expansion into Qubec in 2020. James has provided you with a spreadsheet containing the company's fixed isset data (over 1500 records) and has asked you to prepare a fixed asset schedule, which will be included in the notes to the company's financlal staternents for fiscal 2020 James included the following information from the company's fixed asset policies: - The company has five fixed assets categories land, buildings, equipment, fumiture, and land improvements feg. fencing and parking lots) - All fored assets but land are depreclated using the straight-line method. Estimated residual values are included in the spreadsheet. Estimated usefut ilves are given at the top of this page. - The company charges a full month's depreciation in the month when a fixed asset is put into service, regardless of when in the month the asset is put into service. For example, if a foxed asset is put irito service on March 21, a full-month depreciation will be charged for March, although the asset is to be used for only to days of March. Data Fields: - Province indicates the provincial location of the fired asset - Assetio This field is a sequental number assigned at purchase. This is a unique identified - AssetCategory Indicates the fixed asset category. - Insandice Date Thisis the date the ascet was nut into cervirn. 8. In Req. 7, which of the following formulas, if entered in the cell N2, will work? =E212 E2+12MZ =12+M2 M2 5. Enter an appropriate formula to obtain the current year depreciation expense for cach asset in the ficld of CurrentYearDepr. (Use the ROUND formula to round each osset's depreciation expense to the neorest whole number.) 6. In Req. 5, which of the following formulas, if entered in the cell M2, will work? Hint if DeprMethod is "Not depreciated", then the formula should retum 0. Otherwise, calculate (Cost - ResidualVolue) / Usefultife ) 12 . CurrentYearDeprMonths and round to the nearest whole numbet HF(G2 - Not depreciated", OROUNO(F2A1212*120) 9. Create a Pivottable and complete the following fixed asset schedule. Future Electronics war include the schedule in the notes to its Financial statements for fiscal 2020 (Express every number as a postive number.) 3. Use the IF and DATEDIF formula to obtain the number of months during 2020 , for each of which a full-month depreciation was charged, in the field of CurrentYearDeprMonths. Hint If InService Date preceeds January 1.2020, then the formula should return 12 Otherwise, the formula should return a number less than or equal to 12, that is, the number of months between InService Date and December 31, 2020, where the month of InService Date should be counted as a full month in accordance with the company's fixed asset policies. For example, If InService Date is March 21, 2020, the formula should return 10. 4. In Req. 3, which of the following formulas, If entered in the cell L2, will work? 1 IF (D2>12.12 DATEDIF(D2,K2,"M)+1) IIF(J2>02.12.DATEDIF(D2.K2, M7)+1) =IF(D2>J2,12, DATEDIF (O2,K2CM7)) 10. Create a Privottable and determine total cost, accumulated depreciation and carrying amount of fixed assets at December 31,2020 by each province. (Express every number os a postive number.) Before you proceed, please locate the "Depreciation Policy" worksheet in the downloaded Excel flle and enter the useful life of each asset category as given below: Important Check: Have you entered the above useful life of each asset category in your Excel file? (Note: the above useful tives may be different from those shown in the tutorial video) Yes No - Province: Indicates the provincial location of the fixed asset. - AssetiD. This field is a sequential number assigned at purchase. This is a unique identified. - AsserCotegory: Indicates the fixed asset category. - InService Date: This is the date the asset was put into service. - Cost The acquisition cost of the asset. - ResidualVolue: The residual value assigned by the company. - DeprMethod: The depreciation method used for the asset. - UsefulLife: The useful life assigned by the company. - BegAccumDepr. Beginning accumulated depreciation at January 1, 2020. - CurrentYeorBeg: Current fiscal year beginning for 2020 - CurrentYearEnd: Current fiscal year end for 2020 - CurrentYearDeprMonths: the number of months during 2020 for each of which a full-month depreciation was charged. - CurrentYearDepr: Current year depreciation for 2020. - EndAccumDepr. Ending accumulated depreclation at December 31, 2020. Required: 1. Use the VLOOKUP formula to fill in appropriate content in the fields (1.e, columns) of DeprMethod and UsefulLife. 1. Use the VLOOKUP formula to fill in appropriate content in the fields (i.e., columns) of DeprMethod and UsefulLife. 2. In Req. 1, which of the following formulas, if entered in the cell H2, will work? -VLOOKUP(C2:Depreciation Policy'\$\$87:\$D\$11,3,FALSE) -VLOOKUP(C2:Depreciation Policy'IB7:D11,3,TRUE) =VLOOKUP(C2:Depreciation Policy'1B7:D11,3,FALSE) - VLOOKUP(C2:Depreciation Policy'\$\$B 7,\$D\$11,3.TRUE) Dataset Background: Jaines, the owner of Future Electronics has contacted you to review his fixed asset records. Future Electronics was a Canadian conusmer and home electronics store chain. It was established in 2017 in Vancouver, British Columbla. The company opened a few new stores in Ontario in 2019 and continued its expansion into Qubec in 2020. James has provided you with a spreadsheet containing the company's fixed isset data (over 1500 records) and has asked you to prepare a fixed asset schedule, which will be included in the notes to the company's financlal staternents for fiscal 2020 James included the following information from the company's fixed asset policies: - The company has five fixed assets categories land, buildings, equipment, fumiture, and land improvements feg. fencing and parking lots) - All fored assets but land are depreclated using the straight-line method. Estimated residual values are included in the spreadsheet. Estimated usefut ilves are given at the top of this page. - The company charges a full month's depreciation in the month when a fixed asset is put into service, regardless of when in the month the asset is put into service. For example, if a foxed asset is put irito service on March 21, a full-month depreciation will be charged for March, although the asset is to be used for only to days of March. Data Fields: - Province indicates the provincial location of the fired asset - Assetio This field is a sequental number assigned at purchase. This is a unique identified - AssetCategory Indicates the fixed asset category. - Insandice Date Thisis the date the ascet was nut into cervirn. 8. In Req. 7, which of the following formulas, if entered in the cell N2, will work? =E212 E2+12MZ =12+M2 M2 5. Enter an appropriate formula to obtain the current year depreciation expense for cach asset in the ficld of CurrentYearDepr. (Use the ROUND formula to round each osset's depreciation expense to the neorest whole number.) 6. In Req. 5, which of the following formulas, if entered in the cell M2, will work? Hint if DeprMethod is "Not depreciated", then the formula should retum 0. Otherwise, calculate (Cost - ResidualVolue) / Usefultife ) 12 . CurrentYearDeprMonths and round to the nearest whole numbet HF(G2 - Not depreciated", OROUNO(F2A1212*120) 9. Create a Pivottable and complete the following fixed asset schedule. Future Electronics war include the schedule in the notes to its Financial statements for fiscal 2020 (Express every number as a postive number.) 3. Use the IF and DATEDIF formula to obtain the number of months during 2020 , for each of which a full-month depreciation was charged, in the field of CurrentYearDeprMonths. Hint If InService Date preceeds January 1.2020, then the formula should return 12 Otherwise, the formula should return a number less than or equal to 12, that is, the number of months between InService Date and December 31, 2020, where the month of InService Date should be counted as a full month in accordance with the company's fixed asset policies. For example, If InService Date is March 21, 2020, the formula should return 10. 4. In Req. 3, which of the following formulas, If entered in the cell L2, will work? 1 IF (D2>12.12 DATEDIF(D2,K2,"M)+1) IIF(J2>02.12.DATEDIF(D2.K2, M7)+1) =IF(D2>J2,12, DATEDIF (O2,K2CM7)) 10. Create a Privottable and determine total cost, accumulated depreciation and carrying amount of fixed assets at December 31,2020 by each province. (Express every number os a postive number.) Before you proceed, please locate the "Depreciation Policy" worksheet in the downloaded Excel flle and enter the useful life of each asset category as given below: Important Check: Have you entered the above useful life of each asset category in your Excel file? (Note: the above useful tives may be different from those shown in the tutorial video) Yes No - Province: Indicates the provincial location of the fixed asset. - AssetiD. This field is a sequential number assigned at purchase. This is a unique identified. - AsserCotegory: Indicates the fixed asset category. - InService Date: This is the date the asset was put into service. - Cost The acquisition cost of the asset. - ResidualVolue: The residual value assigned by the company. - DeprMethod: The depreciation method used for the asset. - UsefulLife: The useful life assigned by the company. - BegAccumDepr. Beginning accumulated depreciation at January 1, 2020. - CurrentYeorBeg: Current fiscal year beginning for 2020 - CurrentYearEnd: Current fiscal year end for 2020 - CurrentYearDeprMonths: the number of months during 2020 for each of which a full-month depreciation was charged. - CurrentYearDepr: Current year depreciation for 2020. - EndAccumDepr. Ending accumulated depreclation at December 31, 2020. Required: 1. Use the VLOOKUP formula to fill in appropriate content in the fields (1.e, columns) of DeprMethod and UsefulLife. 1. Use the VLOOKUP formula to fill in appropriate content in the fields (i.e., columns) of DeprMethod and UsefulLife. 2. In Req. 1, which of the following formulas, if entered in the cell H2, will work? -VLOOKUP(C2:Depreciation Policy'\$\$87:\$D\$11,3,FALSE) -VLOOKUP(C2:Depreciation Policy'IB7:D11,3,TRUE) =VLOOKUP(C2:Depreciation Policy'1B7:D11,3,FALSE) - VLOOKUP(C2:Depreciation Policy'\$\$B 7,\$D\$11,3.TRUE)