Shelly Cashman Excel 365/2021 | Module 10: SAM Critical Thinking Project 1c скачать в хорошем качестве

Shelly Cashman Excel 365/2021 | Module 10: SAM Critical Thinking Project 1c

Shelly Cashman Excel 365/2021 | Module 10: SAM Critical Thinking Project 1c #shelly #module10 If you directly want to get the project from us then contact us on our Whatsapp. Link is given here, Whatsapp Contact Link: https://api.whatsapp.com/message/4B6N... Whatsapp Number: +919116641093 +918005564456 Gmail Id: assignmentstutorial@gmail.com 1. Ravi Mehta is the finance director for the Home Security Group of Franklin National, a North American manufacturing company. Ravi is developing an Excel workbook that his managers can use to track and interpret data about customers, contracts, and invoices. He asks for your help in setting up tools to analyze the data and to automate the workbook in order to make it easier to use. Go to the Home Page worksheet. Ravi wants to include a list of links to resources his managers are likely to need. However, the first link he added is unnecessary. Remove the hyperlink from the Links text. 2. Ravi wants to make it easy for users to navigate to the other worksheets. Add links to other places in the workbook as follows: a. Create a link from the Customers text to the Customers worksheet. b. Create a link from the Contracts text to the Contracts worksheet. c. Create a link from the Invoices text to the Invoices worksheet. 3. Ravi also wants users to be able to access archived contract data as they prepare new contracts. Ravi stores the archived data in an Excel workbook. Add a link to the file as follows: a. Create a link from the Archived data text to the file Support_EX365_2021_CT10c_Archive.xlsx. b. Add the following text as a ScreenTip that explains what happens when users click the link: Open an archive of contract data. 4. The Franklin National website provides information Ravi's managers use every day. Create a link from the Franklin National website text to the www.fn.example.com website. 5. Ravi also wants to include a way for managers to request information from the home office of Franklin National. Create a link from the Contact Franklin National text to the info@fn.example.com email address. 6. Ravi's plan is that when users click the Add Customer button in the range D3:E3, the Customers worksheet opens and Excel adds a new, blank record to the customer table, where users can insert the new customer information. Record a macro to automate the Add Customer button as follows: a. Enable all macros in the workbook, and then record a macro stored in this workbook using Add_Customer as the name of the macro. b. While recording the macro, display the Customers worksheet, and then add a new blank record to the end of the customer table. Stop recording the macro. c. Return to the Home Page worksheet. Assign the Add_Customer macro to the appropriate button, and then run the Add_Customer macro to make sure it works correctly. 7. Ravi has already created similar macros to add a new contract record and a new invoice record. Return to the Home Page worksheet, and then assign the Add_Contract and Add_Invoice macros to the appropriate buttons. 8. 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: 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 data from the Customer Query as row headings and the SigningDate data from the Contract Query as column headings, and then sum the ContractAmt values from the Contract Query. c. Display the signing dates 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 data from the Contract Query as row headings and the InvoicePaid data from the Invoice Query as column headings, and then sum the InvoiceAmt values from the Invoice Query. c. Filter the PivotTable using the ContractType data from the Contract Query. d. Group the signing dates to display them as years and months only. 10. To properly combine data from the customer, contract, and invoice tables, create relationships between the tables in the Power Pivot for Excel window as follows: a. 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 he wants to