Answered step by step
Verified Expert Solution
Question
1 Approved Answer
I am stuck on these question, could anyone help? here is the link to the original table 8. a. Go to the Customers worksheet. Ravi
I am stuck on these question, could anyone help? here is the link to the original table
8. a. Go to the Customers worksheet. Ravi wants to create PivotTables that include customer, contract, and invoice data. He already added the tables containing this data to the Data Model. Ravi wants to list and total each contract amount by city and signing date. Create a Pivottable that displays this information as follows: Use Power Pivot to create a PivotTable on a new worksheet, using Contracts by City as the name of the worksheet. b. Display the City field values from the Query - Customer data source as row headings. Display the signingDate field values from the Query - Contract data source as column headings. d. Add the ContractAmt field from the Query - Contract data source to the Values area to sum the field values. (Ignore the alert that states "Relationships between tables may be needed.", these relationships will be created in a later step.) Group the signing dates to display them as years and quarters only. (Hint: Expand the year headings to display the quarter headings, if necessary.) C. e. 9. a. c. Ravi also wants to list the paid and unpaid invoices for each month and then filter them by contract type. Create a Pivottable that displays this information as follows: Use Power Pivot to create a PivotTable on a new worksheet, using Invoices by Month as the name of the worksheet. b. Display the signingDate field values from the Query - Contract data source as row headings. Display the InvoicePaid field values from the Query - Invoice data source as column headings. d. Sum the InvoiceAmt field values from the Query - Invoice data source. (Ignore the alert that states "Relationships between tables may be needed.", these relationships will be created in a later step.) Use the ContractType field from the Query - Contract data source as a filter. f. Group the signing dates to display them as years and months only. (Hint: Expand the year headings to display the month headings, if necessary.) 10. To properly combine data from the Customer, Contract, and Invoice tables, create relationships between tables as follows: In the Power Pivot for Excel window, create a relationship between the Customer and Contract tables using the Customer ID column to relate the tables. b. Create a relationship between the Contract and Invoice tables using the ContractNum column to relate the tables. e. a. a. C. 11. Ravi wants to show invoices for security plans only and want to make the PivotTable easier to interpret. Modify the Pivottable as follows to meet Ravi's requests: Use Unpaid as the column heading in cell C4. b. Use Paid as the column heading in cell 04. Filter the PivotTable to display invoices for the Security plan and Security plan for apartment building contract types. 12. Return to the Customers worksheet. In the range 22:65, Ravi wants to list the total contract amounts for three cities. Extract this information from the PivotTable on the Contracts by City worksheet as follows: In cell K3, use the GETPIVOTDATA function to display the total contract amount for Atlanta from the PivotTable on the Contracts by City worksheet (cell 19). (Hint: Type an equal sign in cell K3 and then click the cell containing the value you want the GETPIVOTDATA function to get from the PivotTable.) b. In cell K4, use the GETPIVOTDATA function to display the total contract amount for College Park from the PivotTable on the Contracts by City worksheet (cell J16). In cell K5, use the GETPIVOTDATA function to display the total contract amount for Dunwoody from the PivotTable on the Contracts by City worksheet (cell J20). Save the workbook, making sure to save it as a macro-enabled workbook. a. c C. Franklin National FN Home Security Group Add Customer Add Contract Add Invoice Links Customers Contracts Invoices Archived data Franklin National website Contact Franklin National B H I FN Home Security Systems Contracts by City Atlanta College Park Dunwoody CustomerID First Last Phone Address C-001 Margaret Alexander 774 Button 1 C-005 Jeff Jordan 770-392-0622 2611 5 Sara Dr C-008 Chris Hackbart 404-985-1122 3478 Woodline Rd C-012 Ramona Earley 770-454-9801 33 E Pine St C-014 Vernon Crowley 706-396-1972 566 Kent Rd C-015 Alberto Cruz 770-637-7783 456 Winchester St C-020 Michelle McMurray 404-963-0808 815 Pulley Rd = C-027 Karen Li 706-483-9244 402 5 Melody Rd C-030 Stephen Rosner 770-393-1228 111 E 78th Ave : C-031 Daniel Plummer 404-842-4603 29 Graham Blvd - C-032 Laquan Richardson 404-857-1276 80 Cedar St C-038 Clint Schmitt 706-482-1400 2268 N Jackson C-040 Victor Ramirez 770-988-0777 144 E Tower Ave 5 C-042 Michael Levander 404-337-9208 10 Lincoln Dr Shana Liang 770-988-1320 40 Monroe St City State Zip Newnan GA 30263 Peachtree City GA 30270 Atlanta GA 31107 Alpharetta GA 30004 Augusta GA 30917 Canton GA 30114 Decatur GA 30031 Augusta GA 30918 Dunwoody GA 30346 Atlanta GA 30347 Atlanta GA 30350 Columbus GA 31998 Holly Springs GA 30115 Atlanta 30336 Alpharetta GA 30022 GA - C-043 B D E F G FN Home Security Systems 1 2 ContractNum 3 9111 4 9112 5 9115 6 9117 $ $ $ 79120 8 9121 9 9122 70 9123 11 9125 12 9126 13 9127 14 9130 15 9131 6 9132 17 9133 CustomerID ContractAmt SigningDate Contract Type C-001 $ 2,000.00 2/6/2021 Indoor alarm system C-027 $ 990.00 2/18/2021 Front door alarm system C-005 $ 2,500.00 3/1/2021 Indoor alarm system C-012 $ 1,250.00 3/1/2021 Indoor alarm system C-055 3,500.00 2/19/2021 Indoor alarm and video surveillance C-040 8,000.00 5/3/2021 Indoor and outdoor alarm system with video C-043 $ 2,000.00 4/14/2021 Indoor alarm system C-070 9,000.00 3/22/2021 Indoor and outdoor alarm system with video C-083 $ 5,500.00 3/25/2021 Indoor and outdoor alarm system with backup C-038 $ 5,000.00 3/11/2021 Indoor wireless alarm system C-oo8 $ 1,250.00 4/17/2021 First floor alarm system C-042 5,250.00 6/11/2021 Indoor and outdoor alarm system C-070 8,500.00 6/3/2021 Indoor and outdoor system with video C-014 1,500.00 6/22/2021 Security plan for apartment building C-005 1,250.00 7/8/2021 Indoor wireless alarm system C-088 2,500.00 5/28/2021 Garage system with video C-088 $ 1,500.00 5/28/2021 Security plan C-045 8.000.00 6/22/2021 Indoor and outdoor system with video and backup Home Page Customers Contracts Invoices $ $ 8 9134 $ 19 9135 20 9137 --- A MI A B C D E F G FN Home Security Systems 1 2 InvoiceNu 3 1011 4 1012 5 1021 6 1031 7 1032 8 1033 9 1034 10 1041 11 1051 12 1061 13 1062 14 1063 15 1064 16 1071 17 1072 18 1073 19 1081 20 1082 21 1083 22 1091 23 1101 24 1102 25 1103 26 1111 27 1112 Contract InvoiceDa Invoiceltem InvoiceAr - InvoicePa- 9111 2/27/2021 Indoor alarm system $ 500.00 TRUE 9111 5/10/2021 Indoor alarm system $ 1,500.00 TRUE 9112 2/25/2021 Front door alarm system $ 990.00 TRUE 9120 4/19/2021 Indoor alarm system $ 1,000.00 TRUE 9120 8/19/2021 Indoor alarm system $ 1,000.00 TRUE 9120 9/20/2021 Indoor alarm system $ 1,000.00 TRUE 9120 10/20/2021 Indoor alarm system $50,000.00 TRUE 9115 4/1/2021 Indoor alarm system $ 2,500.00 TRUE 9117 4/1/2021 Indoor alarm system $ 1,250.00 TRUE 9126 3/11/2022 Indoor wireless system $ 500.00 TRUE 9126 9/12/2022 Indoor wireless system $ 1,000.00 FALSE 9126 7/11/2023 Indoor wireless system $ 1,000.00 FALSE 9126 4/4/2023 Indoor wireless system $ 2,500.00 FALSE 9123 11/22/2021 Indoor and outdoor alarm system $3,000.00 TRUE 9123 7/22/2022 Indoor and outdoor alarm system $ 3,000.00 TRUE 9123 91212023 Indoor and outdoor alarm system $3,000.00 FALSE 9125 7/23/2021 Indoor and outdoor alarm system $ 1,500.00 TRUE 9125 12/20/2021 Indoor and outdoor alarm system $ 2,000.00 TRUE 9125 6/24/2022 Indoor and outdoor alarm system $ 2,000.00 TRUE 9127 6/7/2021 First floor alarm system $ 1,250.00 TRUE 9122 7/14/2021 Indoor alarm system $ 500.00 TRUE 9122 11/15/2021 Indoor alarm system $ 500.00 TRUE 9122 7/14/2022 Indoor alarm system $ 1,000.00 TRUE 9121 10/12/2021 Indoor and outdoor alarm system $2,000.00 TRUE 9121 11/12/2021 Indoor and outdoor alarm cuictam $ 2nnnnn TBUIE Home Page Customers Contracts Invoices + Ready 8. a. Go to the Customers worksheet. Ravi wants to create PivotTables that include customer, contract, and invoice data. He already added the tables containing this data to the Data Model. Ravi wants to list and total each contract amount by city and signing date. Create a Pivottable that displays this information as follows: Use Power Pivot to create a PivotTable on a new worksheet, using Contracts by City as the name of the worksheet. b. Display the City field values from the Query - Customer data source as row headings. Display the signingDate field values from the Query - Contract data source as column headings. d. Add the ContractAmt field from the Query - Contract data source to the Values area to sum the field values. (Ignore the alert that states "Relationships between tables may be needed.", these relationships will be created in a later step.) Group the signing dates to display them as years and quarters only. (Hint: Expand the year headings to display the quarter headings, if necessary.) C. e. 9. a. c. Ravi also wants to list the paid and unpaid invoices for each month and then filter them by contract type. Create a Pivottable that displays this information as follows: Use Power Pivot to create a PivotTable on a new worksheet, using Invoices by Month as the name of the worksheet. b. Display the signingDate field values from the Query - Contract data source as row headings. Display the InvoicePaid field values from the Query - Invoice data source as column headings. d. Sum the InvoiceAmt field values from the Query - Invoice data source. (Ignore the alert that states "Relationships between tables may be needed.", these relationships will be created in a later step.) Use the ContractType field from the Query - Contract data source as a filter. f. Group the signing dates to display them as years and months only. (Hint: Expand the year headings to display the month headings, if necessary.) 10. To properly combine data from the Customer, Contract, and Invoice tables, create relationships between tables as follows: In the Power Pivot for Excel window, create a relationship between the Customer and Contract tables using the Customer ID column to relate the tables. b. Create a relationship between the Contract and Invoice tables using the ContractNum column to relate the tables. e. a. a. C. 11. Ravi wants to show invoices for security plans only and want to make the PivotTable easier to interpret. Modify the Pivottable as follows to meet Ravi's requests: Use Unpaid as the column heading in cell C4. b. Use Paid as the column heading in cell 04. Filter the PivotTable to display invoices for the Security plan and Security plan for apartment building contract types. 12. Return to the Customers worksheet. In the range 22:65, Ravi wants to list the total contract amounts for three cities. Extract this information from the PivotTable on the Contracts by City worksheet as follows: In cell K3, use the GETPIVOTDATA function to display the total contract amount for Atlanta from the PivotTable on the Contracts by City worksheet (cell 19). (Hint: Type an equal sign in cell K3 and then click the cell containing the value you want the GETPIVOTDATA function to get from the PivotTable.) b. In cell K4, use the GETPIVOTDATA function to display the total contract amount for College Park from the PivotTable on the Contracts by City worksheet (cell J16). In cell K5, use the GETPIVOTDATA function to display the total contract amount for Dunwoody from the PivotTable on the Contracts by City worksheet (cell J20). Save the workbook, making sure to save it as a macro-enabled workbook. a. c C. Franklin National FN Home Security Group Add Customer Add Contract Add Invoice Links Customers Contracts Invoices Archived data Franklin National website Contact Franklin National B H I FN Home Security Systems Contracts by City Atlanta College Park Dunwoody CustomerID First Last Phone Address C-001 Margaret Alexander 774 Button 1 C-005 Jeff Jordan 770-392-0622 2611 5 Sara Dr C-008 Chris Hackbart 404-985-1122 3478 Woodline Rd C-012 Ramona Earley 770-454-9801 33 E Pine St C-014 Vernon Crowley 706-396-1972 566 Kent Rd C-015 Alberto Cruz 770-637-7783 456 Winchester St C-020 Michelle McMurray 404-963-0808 815 Pulley Rd = C-027 Karen Li 706-483-9244 402 5 Melody Rd C-030 Stephen Rosner 770-393-1228 111 E 78th Ave : C-031 Daniel Plummer 404-842-4603 29 Graham Blvd - C-032 Laquan Richardson 404-857-1276 80 Cedar St C-038 Clint Schmitt 706-482-1400 2268 N Jackson C-040 Victor Ramirez 770-988-0777 144 E Tower Ave 5 C-042 Michael Levander 404-337-9208 10 Lincoln Dr Shana Liang 770-988-1320 40 Monroe St City State Zip Newnan GA 30263 Peachtree City GA 30270 Atlanta GA 31107 Alpharetta GA 30004 Augusta GA 30917 Canton GA 30114 Decatur GA 30031 Augusta GA 30918 Dunwoody GA 30346 Atlanta GA 30347 Atlanta GA 30350 Columbus GA 31998 Holly Springs GA 30115 Atlanta 30336 Alpharetta GA 30022 GA - C-043 B D E F G FN Home Security Systems 1 2 ContractNum 3 9111 4 9112 5 9115 6 9117 $ $ $ 79120 8 9121 9 9122 70 9123 11 9125 12 9126 13 9127 14 9130 15 9131 6 9132 17 9133 CustomerID ContractAmt SigningDate Contract Type C-001 $ 2,000.00 2/6/2021 Indoor alarm system C-027 $ 990.00 2/18/2021 Front door alarm system C-005 $ 2,500.00 3/1/2021 Indoor alarm system C-012 $ 1,250.00 3/1/2021 Indoor alarm system C-055 3,500.00 2/19/2021 Indoor alarm and video surveillance C-040 8,000.00 5/3/2021 Indoor and outdoor alarm system with video C-043 $ 2,000.00 4/14/2021 Indoor alarm system C-070 9,000.00 3/22/2021 Indoor and outdoor alarm system with video C-083 $ 5,500.00 3/25/2021 Indoor and outdoor alarm system with backup C-038 $ 5,000.00 3/11/2021 Indoor wireless alarm system C-oo8 $ 1,250.00 4/17/2021 First floor alarm system C-042 5,250.00 6/11/2021 Indoor and outdoor alarm system C-070 8,500.00 6/3/2021 Indoor and outdoor system with video C-014 1,500.00 6/22/2021 Security plan for apartment building C-005 1,250.00 7/8/2021 Indoor wireless alarm system C-088 2,500.00 5/28/2021 Garage system with video C-088 $ 1,500.00 5/28/2021 Security plan C-045 8.000.00 6/22/2021 Indoor and outdoor system with video and backup Home Page Customers Contracts Invoices $ $ 8 9134 $ 19 9135 20 9137 --- A MI A B C D E F G FN Home Security Systems 1 2 InvoiceNu 3 1011 4 1012 5 1021 6 1031 7 1032 8 1033 9 1034 10 1041 11 1051 12 1061 13 1062 14 1063 15 1064 16 1071 17 1072 18 1073 19 1081 20 1082 21 1083 22 1091 23 1101 24 1102 25 1103 26 1111 27 1112 Contract InvoiceDa Invoiceltem InvoiceAr - InvoicePa- 9111 2/27/2021 Indoor alarm system $ 500.00 TRUE 9111 5/10/2021 Indoor alarm system $ 1,500.00 TRUE 9112 2/25/2021 Front door alarm system $ 990.00 TRUE 9120 4/19/2021 Indoor alarm system $ 1,000.00 TRUE 9120 8/19/2021 Indoor alarm system $ 1,000.00 TRUE 9120 9/20/2021 Indoor alarm system $ 1,000.00 TRUE 9120 10/20/2021 Indoor alarm system $50,000.00 TRUE 9115 4/1/2021 Indoor alarm system $ 2,500.00 TRUE 9117 4/1/2021 Indoor alarm system $ 1,250.00 TRUE 9126 3/11/2022 Indoor wireless system $ 500.00 TRUE 9126 9/12/2022 Indoor wireless system $ 1,000.00 FALSE 9126 7/11/2023 Indoor wireless system $ 1,000.00 FALSE 9126 4/4/2023 Indoor wireless system $ 2,500.00 FALSE 9123 11/22/2021 Indoor and outdoor alarm system $3,000.00 TRUE 9123 7/22/2022 Indoor and outdoor alarm system $ 3,000.00 TRUE 9123 91212023 Indoor and outdoor alarm system $3,000.00 FALSE 9125 7/23/2021 Indoor and outdoor alarm system $ 1,500.00 TRUE 9125 12/20/2021 Indoor and outdoor alarm system $ 2,000.00 TRUE 9125 6/24/2022 Indoor and outdoor alarm system $ 2,000.00 TRUE 9127 6/7/2021 First floor alarm system $ 1,250.00 TRUE 9122 7/14/2021 Indoor alarm system $ 500.00 TRUE 9122 11/15/2021 Indoor alarm system $ 500.00 TRUE 9122 7/14/2022 Indoor alarm system $ 1,000.00 TRUE 9121 10/12/2021 Indoor and outdoor alarm system $2,000.00 TRUE 9121 11/12/2021 Indoor and outdoor alarm cuictam $ 2nnnnn TBUIE Home Page Customers Contracts Invoices + Ready
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started