Скачать или смотреть Illustrated Excel 365 | Module 11: SAM Project B | Alliancy, Inc.

Illustrated Excel 365 | Module 11: SAM Project B | Alliancy, Inc. #illustratedexcel

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/4B6NMKKBK...

Whatsapp Number:
+919116641093
+918005564456

Gmail Id:
[email protected]

We are providing help in all Online Courses, Computer Science, Business and Management, Business Math, Business and Finance, Business and Accounting, Human Resource Management, History, English.

PROJECT STEPS
1. Dario Navarro is an account manager for Alliancy, Inc., in Wilmington, Delaware. The company provides corporate learning and development services to businesses throughout the country. In an Excel workbook, Dario is tracking the service contracts he manages in Charlotte, North Carolina, and Nashville, Tennessee. He asks for your help in analyzing the contract data and correcting errors.

Go to the Charlotte worksheet. Correct the errors in the worksheet as follows:
a. In cell G6, trace the errors in the formula to identify the cause of the error message. Correct the formula so that the IF function tests whether the contract date (the range named Contract_Charlotte) is less than the date in cell C4. If it is, multiply the standard price (cell F6) by the discount price rate (cell G4) and return the result. If it is not, return the standard price (cell F6) in cell G6.
b. Copy the formula in cell G6 into the range G7:G14 to fix the errors and incorrect formulas in that range.
c. In cell F15, use Error Checking to find the formula error, and then correct the formula.
d. Correct the formula error in cell G15.
2. Dario asks you to assign names to ranges so that you can use the names in formulas. Define the names as follows:
a. Assign the name Months to the range C6:C14 to identify the months contracted.
b. Assign the name Services to the range E6:E14 to identify the service types.
c. Assign the name Discount to the range G6:G14 to identify the discounted prices.
3. Dario asks you to list the service types in alphabetic order to complete the table of service types. Add the data as follows:
a. In cell E24, enter a formula using the UNIQUE function to list the unique Service Type values in the range E6:E14 (using the defined name Services).
b. In cell F24, enter a formula using the SORT function to sort the service types in the range E24:E27 in alphabetic order.
c. Copy the sorted results in the range F24:F27 and paste only the values in the range I6:I9.
d. Hide rows 24:27 to remove clutter from the worksheet.
4. In the range B18:C23, Dario has created an area for analyzing the contracts for specific types of services. He wants to analyze the sales of cybersecurity (CYBR) contracts because they are the most popular.
Create a formula to determine the average sales price of CYBR contracts as follows:
a. In cell C20, begin to enter a formula using the AVERAGEIF function.
b. Use the Service Type values as the range (the defined name Services).
c. Use the abbreviation for cybersecurity as the criteria argument (cell I6).
d. Use the discounted prices (the defined name Discount) as the range to average.
5. Create a formula to determine the total sales price of cybersecurity contracts as follows:
a. In cell C21, begin to enter a formula using the SUMIF function.
b. Use the Service Type values as the range, the abbreviation for cybersecurity as the criteria, and the discounted prices as the range to sum.
c. Use defined names as appropriate in the formula.
6. Create a formula to determine the average sale price of 12-month cybersecurity contracts as follows:
a. In cell C22, begin to enter a formula using the AVERAGEIFS function.
b. Use the discounted prices (the defined name Discount) as the range to average.
c. Use the Service Type values (the defined name Services) as the first criteria range.
d. Use the abbreviation for cybersecruity as the criteria1 argument (cell I6).
e. Use the Months Contracted values (the defined name Months) as the second criteria range.
f. Use "12" as the criteria2 argument.
7. Create a formula to determine the total sale price of 12-month cybersecurity contracts as follows:
a. In cell C23, begin to enter a formula using the SUMIFS function.
b. Use the discounted prices as the range to sum.
c. Use the Service Type values as the first criteria range.
d. Use the abbreviation for cybersecurity as the criteria1 argument.
e. Use the Months Contracted values as the second criteria range.
f. Use the specified number of months as the criteria2 argument.
g. Use defined names as appropriate in the formula.
8. Go to the Nashville worksheet. In the range F6:F14, Dario asks you to enter a formula to calculate the follow-up dates for contacting the new Nashville customers. He wants you to use a defined name in the formula as you did on the Charlotte worksheet.
Calculate the follow-up dates as follows: