Question

1 Approved Answer

Posted on Aug 29, 2024

Its a Taxation data analytics assignment and the first 3 steps are done but stuck on 4 Detailed Instructions for Data Analytics Assignments (both Individual

Its a Taxation data analytics assignment and the first 3 steps are done but stuck on 4 image text in transcribed

Detailed Instructions for Data Analytics Assignments (both Individual & Corporate): 1. Select the cells that you would like to analyze (full date, description and dollar amount columns). 2. Under the Data menu, select "Filter" to create filters at the top of each column. 3. Click the arrow next to the "Description" column and uncheck the "Select all" button. Next check off the three "Income..." descriptions to filter the data to only show income amounts. 4. Scroll down to find the January income deposit from the following year (i.e. the year after the current taxation year) and adjust the amount to match the accounts receivable balance noted by the taxpayer in the appendix information. 5. Go back to the filter menu for the "Description" column and check the "Select all" button to make all of the raw data visible again. 6. Now create a PivotTable by selecting all of the cells that you would like to analyze and click on the "Pivot Table" button under the "Insert" menu. 7. In the PivotTable pop-up window, select a location for your Pivot Table. 8. In the "Pivot Table Fields" menu check off the "Description" and 'S" options. 9. Now, a PivotTable should have appeared that summarizes the total dollar values for each income or expense description. 10. Click on the Pivottable and select "Insert Timeline" from the PivotTable Tools menu. nacure that Date - - Description 1/3/2021 Income - Alberta Health Region 1/5/2021 Income - Alberta Health Region 1/6/2021 Income - Alberta Health Region 1/6/2021 Income - Alberta Health Region 1/10/2021 Income - Alberta Health Region 1/12/2021 Income - Alberta Health Region 1/13/2021 Income - Alberta Health Region 1/15/2021 Income - Alberta Health Region 1/17/2021 Income - Alberta Health Region 1/17/2021 Income - Alberta Health Region 1/19/2021 Income - Alberta Health Region 1/24/2021 Income - Alberta Health Region 1/26/2021 Income - Alberta Health Region 1/26/2021 Income - Alberta Health Region 1/31/2021 Income - Alberta Health Region 1/31/2021 Income - Alberta Health Region 2/2/2021 Income - Alberta Health Region 2/9/2021 Income - Alberta Health Region 2/9/2021 Income - Alberta Health Region 2/14/2021 Income - Alberta Health Region 2/14/2021 Income - Alberta Health Region 2/15/2021 Income - Alberta Health Region 2/16/2021 Income - Alberta Health Region 2/19/2021 Income - Alberta Health Region 2/23/2021 Income - Alberta Health Region 3/1/2021 Income - Alberta Health Region 3/6/2021 Income - Alberta Health Region 3/8/2021 Income - Alberta Health Region 3/20/2021 Income - Alberta Health Region 3/22/2021 Income - Alberta Health Region 3/22/2021 Income - Alberta Health Region 4/10/2021 Income - Alberta Health Region $ $ 244.55 $ 2,448.83 $ 1,250,00 $ 1,000.00 $ 5.84 $ 1,567.24 $ 3,197.51 $ 5,438.06 $ 854.95 $ 8,337.66 $ 1,960.61 $ 359.16 $ 70.08 $ 5,935.32 $ 877.44 $ 20,203.65 $ 1,667.39 $ 8,455.63 $ 14,553.98 $ 33.06 $ 2,389.77 $3,956.93 $ 7,128.85 $ 11,872.50 $5,454.02 $ 1,319.50 $ 877.44 $ 37.96 $ 16,375.99 S 4,196.49 $ 10,496.20 Y $ > - Date 20 21 22 23 24 25 26 27 28 229 230 231 232 33 634 235 36 237 238 239 240 241 242 243 244 245 246 |247 248 249 250 251 Description 4/10/2021 Income - Alberta Health Region 4/17/2021 Income - Alberta Health Region 4/26/2021 Income - Alberta Health Region 4/26/2021 Income - Alberta Health Region 4/29/2021 Income - Alberta Health Region 4/30/2021 Income - Alberta Health Region 5/3/2021 Income - Alberta Health Region 5/10/2021 Income - Alberta Health Region 5/14/2021 Income - Alberta Health Region 5/23/2021 Income - Alberta Health Region 5/29/2021 Income - Alberta Health Region 6/7/2021 Income - Alberta Health Region 6/12/2021 Income - Alberta Health Region 6/14/2021 Income - Alberta Health Region 6/14/2021 Income - Alberta Health Region 6/14/2021 Income - Alberta Health Region 6/28/2021 Income - Alberta Health Region 7/3/2021 Income - Alberta Health Region 7/3/2021 Income - Alberta Health Region 7/S/2021 Income - Alberta Health Region 7/12/2021 Income - Alberta Health Region 7/15/2021 Income - Alberta Health Region 7/19/2021 Income - Alberta Health Region 7/19/2021 Income - Alberta Health Region 7/19/2021 Income - Alberta Health Region 7/26/2021 Income - Alberta Health Region 7/26/2021 Income - Alberta Health Region 7/31/2021 Income - Alberta Health Region 7/31/2021 Income - Alberta Health Region 8/2/2021 Income - Alberta Health Region 8/14/2021 Income - Alberta Health Region 8/16/2021 Income - Alberta Health Region 19 $ $5,725.20 $ 2,767.18 $ 6,217.86 $1,619.70 $ 10,594.97 $ 5,250.00 $ 4,250.51 $ 12,557.94 $ 24.68 $ 28.82 $ 877.44 $1,515.74 $ 16,905 24 S 1,893.10 $ 917.00 $ 2,021.59 $ 5,833.55 $ 5,500.00 $ 3,509.76 $ 8,154.62 $ 7,970.96 $ 1,826.06 $ 306.60 $ 12,382.63 $ 23,479.50 $ 9,855.74 $ 3,388,42 $ 877.44 $ 541.86 S 32.12 $ 2,311.61 $ 2,787.56 Date - - 6 Description 8/23/2021 Income - Alberta Health Region 8/23/2021 Income - Alberta Health Region 8/30/2021 Income - Alberta Health Region 9/6/2021 Income - Alberta Health Region 9/18/2021 Income - Alberta Health Region 9/20/2021 Income - Alberta Health Region 9/27/2021 Income - Alberta Health Region 10/4/2021 Income - Alberta Health Region 10/4/2021 Income - Alberta Health Region 10/4/2021 Income - Alberta Health Region 10/9/2021 Income - Alberta Health Region 10/9/2021 Income - Alberta Health Region 10/11/2021 Income - Alberta Health Region 10/17/2021 Income - Alberta Health Region 10/25/2021 Income - Alberta Health Region 10/30/2021 Income - Alberta Health Region 11/1/2021 Income - Alberta Health Region 11/13/2021 Income - Alberta Health Region 11/14/2021 Income - Alberta Health Region 11/22/2021 Income - Alberta Health Region 11/27/2021 Income - Alberta Health Region 11/29/2021 Income - Alberta Health Region 12/6/2021 Income - Alberta Health Region 12/18/2021 Income - Alberta Health Region 12/21/2021 Income - Alberta Health Region 12/27/2021 Income - Alberta Health Region 1/30/2022 Income - Alberta Health Region 1/10/2021 Income - Calgary Clinic 11/7/2021 Income - Calgary Clinic 12/12/2021 Income - Calgary Clinic 1/11/2021 Income - Winnipeg Health Region 1/14/2021 Income-Winnipeg Health Region $ 116.80 $ 19,531.72 $ 2,632.32 $ 52.64 $ 3,316.56 $ 2,380.69 $ 7,157.60 $ 921.38 $ 2,845.63 $ 2,412.96 $ 541.86 $ 15,653.00 $ 102.20 $ 4,750.00 $ 514.44 $ 1,535.52 $ 9,957.88 $ 8,480.56 $ 1,080.41 $ 10,005.67 $ 2,120.14 $ 2,768.70 $1,650.45 $ 3,509.76 $ 4,891.27 $ 4,481.46 $ 22,841.36 $ 8,857.61 $ 12,457.84 $ 355.24 $ 5,044.17 $ 8,455.75 18 9 10 11 12 3 14 5 6 7 18 19 30 31 12 33 TY $ Date Description TZVELTIME TIME TORTUITETUTT 1/14/2021 Income - Winnipeg Health Region 1/16/2021 Income-Winnipeg Health Region 1/25/2021 Income - Winnipeg Health Region 2/8/2021 Income-Winnipeg Health Region 2/20/2021 Income - Winnipeg Health Region 3/18/2021 Income-Winnipeg Health Region 3/22/2021 Income-Winnipeg Health Region 3/22/2021. Income-Winnipeg Health Region 3/22/2021 Income - Winnipeg Health Region 4/3/2021 Income - Winnipeg Health Region 4/5/2021 Income - Winnipeg Health Region 4/16/2021 Income-Winnipeg Health Region 4/19/2021 Income-Winnipeg Health Region 4/19/2021 Income-Winnipeg Health Region 5/3/2021 Income - Winnipeg Health Region 5/23/2021 Income-Winnipeg Health Region 6/28/2021 Income - Winnipeg Health Region 7/16/2021 Income-Winnipeg Health Region 7/26/2021 Income-Winnipeg Health Region 8/8/2021 Income-Winnipeg Health Region 8/22/2021 Income - Winnipeg Health Region 10/15/2021 Income-Winnipeg Health Region 11/15/2021 Income-Winnipeg Health Region 12/26/2021 Income-Winnipeg Health Region $ 8,455.75 $ 2,139.95 $ 18,899.63 $ 10,203.67 $ 208.90 $ 4,073.17 $5,479.50 $ 211.31 $ 5,758.18 $ 300.00 $ 3,800.02 $ 4,741.59 $ 11,588.94 $ 800.04 $ 8,886.76 $ 19,273.41 $ 458.78 $ 4,691.65 $ 6,025.33 $ 19,147.97 $ 17,760.15 $ 21,801.02 $ 1,246.52 S 604.00 Requirements for the Excel analysis 1. There are some transactions not from the current traction year in the data set (see the Date field. Remove any transactions from another tation year and adjust the January income deposit based on the taxpayer's accounts receivable information 2. Analyze the taxpayer's income by location wine a pivot table, Analyze the income by inserting a pivot chart that show the total income by location 3. Analyze the home office expenses using a pivot table 4. Analyze the motor vehide expenses using a pivot table. S Analyse the travel expenses using a pivot table 6. Analyze the conference penes using a pivottable 7. Analyze the administrative expenses, including any rebates, using a pivot table Analyze the capital et purchases and calculate the maximum Capital Cont Alowance available using the table provided CHECKFIGURES Requirement Utility All Payment - Electricity $890.66 Requirement of change and filter service $458.01 Requirements Accommodations - Winnipeg $1,328.98 Requirement 4 Pede $41.30 Requirements CCA. Balance Cell 12 54.602.63 Requirement 6 Income Tax Returns. Total Positive sources $586,137 of income-Cell 10 Detailed Instructions for Data Analytics Assignments (both Individual & Corporate): 1. Select the cells that you would like to analyze (full date, description and dollar amount columns). 2. Under the Data menu, select "Filter" to create filters at the top of each column. 3. Click the arrow next to the "Description" column and uncheck the "Select all" button. Next check off the three "Income..." descriptions to filter the data to only show income amounts. 4. Scroll down to find the January income deposit from the following year (i.e. the year after the current taxation year) and adjust the amount to match the accounts receivable balance noted by the taxpayer in the appendix information. 5. Go back to the filter menu for the "Description" column and check the "Select all" button to make all of the raw data visible again. 6. Now create a PivotTable by selecting all of the cells that you would like to analyze and click on the "Pivot Table" button under the "Insert" menu. 7. In the PivotTable pop-up window, select a location for your Pivot Table. 8. In the "Pivot Table Fields" menu check off the "Description" and 'S" options. 9. Now, a PivotTable should have appeared that summarizes the total dollar values for each income or expense description. 10. Click on the Pivottable and select "Insert Timeline" from the PivotTable Tools menu. nacure that Date - - Description 1/3/2021 Income - Alberta Health Region 1/5/2021 Income - Alberta Health Region 1/6/2021 Income - Alberta Health Region 1/6/2021 Income - Alberta Health Region 1/10/2021 Income - Alberta Health Region 1/12/2021 Income - Alberta Health Region 1/13/2021 Income - Alberta Health Region 1/15/2021 Income - Alberta Health Region 1/17/2021 Income - Alberta Health Region 1/17/2021 Income - Alberta Health Region 1/19/2021 Income - Alberta Health Region 1/24/2021 Income - Alberta Health Region 1/26/2021 Income - Alberta Health Region 1/26/2021 Income - Alberta Health Region 1/31/2021 Income - Alberta Health Region 1/31/2021 Income - Alberta Health Region 2/2/2021 Income - Alberta Health Region 2/9/2021 Income - Alberta Health Region 2/9/2021 Income - Alberta Health Region 2/14/2021 Income - Alberta Health Region 2/14/2021 Income - Alberta Health Region 2/15/2021 Income - Alberta Health Region 2/16/2021 Income - Alberta Health Region 2/19/2021 Income - Alberta Health Region 2/23/2021 Income - Alberta Health Region 3/1/2021 Income - Alberta Health Region 3/6/2021 Income - Alberta Health Region 3/8/2021 Income - Alberta Health Region 3/20/2021 Income - Alberta Health Region 3/22/2021 Income - Alberta Health Region 3/22/2021 Income - Alberta Health Region 4/10/2021 Income - Alberta Health Region $ $ 244.55 $ 2,448.83 $ 1,250,00 $ 1,000.00 $ 5.84 $ 1,567.24 $ 3,197.51 $ 5,438.06 $ 854.95 $ 8,337.66 $ 1,960.61 $ 359.16 $ 70.08 $ 5,935.32 $ 877.44 $ 20,203.65 $ 1,667.39 $ 8,455.63 $ 14,553.98 $ 33.06 $ 2,389.77 $3,956.93 $ 7,128.85 $ 11,872.50 $5,454.02 $ 1,319.50 $ 877.44 $ 37.96 $ 16,375.99 S 4,196.49 $ 10,496.20 Y $ > - Date 20 21 22 23 24 25 26 27 28 229 230 231 232 33 634 235 36 237 238 239 240 241 242 243 244 245 246 |247 248 249 250 251 Description 4/10/2021 Income - Alberta Health Region 4/17/2021 Income - Alberta Health Region 4/26/2021 Income - Alberta Health Region 4/26/2021 Income - Alberta Health Region 4/29/2021 Income - Alberta Health Region 4/30/2021 Income - Alberta Health Region 5/3/2021 Income - Alberta Health Region 5/10/2021 Income - Alberta Health Region 5/14/2021 Income - Alberta Health Region 5/23/2021 Income - Alberta Health Region 5/29/2021 Income - Alberta Health Region 6/7/2021 Income - Alberta Health Region 6/12/2021 Income - Alberta Health Region 6/14/2021 Income - Alberta Health Region 6/14/2021 Income - Alberta Health Region 6/14/2021 Income - Alberta Health Region 6/28/2021 Income - Alberta Health Region 7/3/2021 Income - Alberta Health Region 7/3/2021 Income - Alberta Health Region 7/S/2021 Income - Alberta Health Region 7/12/2021 Income - Alberta Health Region 7/15/2021 Income - Alberta Health Region 7/19/2021 Income - Alberta Health Region 7/19/2021 Income - Alberta Health Region 7/19/2021 Income - Alberta Health Region 7/26/2021 Income - Alberta Health Region 7/26/2021 Income - Alberta Health Region 7/31/2021 Income - Alberta Health Region 7/31/2021 Income - Alberta Health Region 8/2/2021 Income - Alberta Health Region 8/14/2021 Income - Alberta Health Region 8/16/2021 Income - Alberta Health Region 19 $ $5,725.20 $ 2,767.18 $ 6,217.86 $1,619.70 $ 10,594.97 $ 5,250.00 $ 4,250.51 $ 12,557.94 $ 24.68 $ 28.82 $ 877.44 $1,515.74 $ 16,905 24 S 1,893.10 $ 917.00 $ 2,021.59 $ 5,833.55 $ 5,500.00 $ 3,509.76 $ 8,154.62 $ 7,970.96 $ 1,826.06 $ 306.60 $ 12,382.63 $ 23,479.50 $ 9,855.74 $ 3,388,42 $ 877.44 $ 541.86 S 32.12 $ 2,311.61 $ 2,787.56 Date - - 6 Description 8/23/2021 Income - Alberta Health Region 8/23/2021 Income - Alberta Health Region 8/30/2021 Income - Alberta Health Region 9/6/2021 Income - Alberta Health Region 9/18/2021 Income - Alberta Health Region 9/20/2021 Income - Alberta Health Region 9/27/2021 Income - Alberta Health Region 10/4/2021 Income - Alberta Health Region 10/4/2021 Income - Alberta Health Region 10/4/2021 Income - Alberta Health Region 10/9/2021 Income - Alberta Health Region 10/9/2021 Income - Alberta Health Region 10/11/2021 Income - Alberta Health Region 10/17/2021 Income - Alberta Health Region 10/25/2021 Income - Alberta Health Region 10/30/2021 Income - Alberta Health Region 11/1/2021 Income - Alberta Health Region 11/13/2021 Income - Alberta Health Region 11/14/2021 Income - Alberta Health Region 11/22/2021 Income - Alberta Health Region 11/27/2021 Income - Alberta Health Region 11/29/2021 Income - Alberta Health Region 12/6/2021 Income - Alberta Health Region 12/18/2021 Income - Alberta Health Region 12/21/2021 Income - Alberta Health Region 12/27/2021 Income - Alberta Health Region 1/30/2022 Income - Alberta Health Region 1/10/2021 Income - Calgary Clinic 11/7/2021 Income - Calgary Clinic 12/12/2021 Income - Calgary Clinic 1/11/2021 Income - Winnipeg Health Region 1/14/2021 Income-Winnipeg Health Region $ 116.80 $ 19,531.72 $ 2,632.32 $ 52.64 $ 3,316.56 $ 2,380.69 $ 7,157.60 $ 921.38 $ 2,845.63 $ 2,412.96 $ 541.86 $ 15,653.00 $ 102.20 $ 4,750.00 $ 514.44 $ 1,535.52 $ 9,957.88 $ 8,480.56 $ 1,080.41 $ 10,005.67 $ 2,120.14 $ 2,768.70 $1,650.45 $ 3,509.76 $ 4,891.27 $ 4,481.46 $ 22,841.36 $ 8,857.61 $ 12,457.84 $ 355.24 $ 5,044.17 $ 8,455.75 18 9 10 11 12 3 14 5 6 7 18 19 30 31 12 33 TY $ Date Description TZVELTIME TIME TORTUITETUTT 1/14/2021 Income - Winnipeg Health Region 1/16/2021 Income-Winnipeg Health Region 1/25/2021 Income - Winnipeg Health Region 2/8/2021 Income-Winnipeg Health Region 2/20/2021 Income - Winnipeg Health Region 3/18/2021 Income-Winnipeg Health Region 3/22/2021 Income-Winnipeg Health Region 3/22/2021. Income-Winnipeg Health Region 3/22/2021 Income - Winnipeg Health Region 4/3/2021 Income - Winnipeg Health Region 4/5/2021 Income - Winnipeg Health Region 4/16/2021 Income-Winnipeg Health Region 4/19/2021 Income-Winnipeg Health Region 4/19/2021 Income-Winnipeg Health Region 5/3/2021 Income - Winnipeg Health Region 5/23/2021 Income-Winnipeg Health Region 6/28/2021 Income - Winnipeg Health Region 7/16/2021 Income-Winnipeg Health Region 7/26/2021 Income-Winnipeg Health Region 8/8/2021 Income-Winnipeg Health Region 8/22/2021 Income - Winnipeg Health Region 10/15/2021 Income-Winnipeg Health Region 11/15/2021 Income-Winnipeg Health Region 12/26/2021 Income-Winnipeg Health Region $ 8,455.75 $ 2,139.95 $ 18,899.63 $ 10,203.67 $ 208.90 $ 4,073.17 $5,479.50 $ 211.31 $ 5,758.18 $ 300.00 $ 3,800.02 $ 4,741.59 $ 11,588.94 $ 800.04 $ 8,886.76 $ 19,273.41 $ 458.78 $ 4,691.65 $ 6,025.33 $ 19,147.97 $ 17,760.15 $ 21,801.02 $ 1,246.52 S 604.00 Requirements for the Excel analysis 1. There are some transactions not from the current traction year in the data set (see the Date field. Remove any transactions from another tation year and adjust the January income deposit based on the taxpayer's accounts receivable information 2. Analyze the taxpayer's income by location wine a pivot table, Analyze the income by inserting a pivot chart that show the total income by location 3. Analyze the home office expenses using a pivot table 4. Analyze the motor vehide expenses using a pivot table. S Analyse the travel expenses using a pivot table 6. Analyze the conference penes using a pivottable 7. Analyze the administrative expenses, including any rebates, using a pivot table Analyze the capital et purchases and calculate the maximum Capital Cont Alowance available using the table provided CHECKFIGURES Requirement Utility All Payment - Electricity $890.66 Requirement of change and filter service $458.01 Requirements Accommodations - Winnipeg $1,328.98 Requirement 4 Pede $41.30 Requirements CCA. Balance Cell 12 54.602.63 Requirement 6 Income Tax Returns. Total Positive sources $586,137 of income-Cell 10