Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

PLEASE HELP!!!!! Model. Ravi wants to list and total each contract amount by city and signing date. Create a PivotTable that displays this information as

PLEASE HELP!!!!!
image text in transcribed
image text in transcribed
image text in transcribed
Model. Ravi wants to list and total each contract amount by city and signing date. Create a PivotTable that displays this information as follows: a. 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. c. 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.) e. Group the signing dates to display them as years and quarters only. (Hint: Expand the year headings to display the quarter headings, if necessary.) 9. 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: a. 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. c. 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.) 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.) e. 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: a. In the Power Pivot for Excel window, create a relationship between the Customer and Contract tables using the CustomerID column to relate the tables. b. Create a relationship between the Contract and Invoice tables using the ContractNum column to relate the tables. 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: a. Use Unpaid as the column heading in cell C4. b. Use Paid as the column heading in cell D4. c. 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 J2:K5, 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: a. 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 J9). (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). c. 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 320). Save the workbook, making sure to save it as a macro-enabled workbook. Model. Ravi wants to list and total each contract amount by city and signing date. Create a PivotTable that displays this information as follows: a. 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. c. 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.) e. Group the signing dates to display them as years and quarters only. (Hint: Expand the year headings to display the quarter headings, if necessary.) 9. 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: a. 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. c. 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.) 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.) e. 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: a. In the Power Pivot for Excel window, create a relationship between the Customer and Contract tables using the CustomerID column to relate the tables. b. Create a relationship between the Contract and Invoice tables using the ContractNum column to relate the tables. 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: a. Use Unpaid as the column heading in cell C4. b. Use Paid as the column heading in cell D4. c. 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 J2:K5, 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: a. 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 J9). (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). c. 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 320). Save the workbook, making sure to save it as a macro-enabled workbook

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image_2

Step: 3

blur-text-image_3

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

GAO Yellow Book Government Auditing Standar

Authors: Comptroller General United States Government

2011edition

1479245577, 978-1479245574

More Books

Students also viewed these Accounting questions

Question

It can interfere with learning and cognitive development.

Answered: 1 week ago