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

GETTING STARTED

1. Save the file IL_EX365_11B_FirstLastName_1.xlsx as IL_EX365_11B_FirstLastName_2.xlsx

a. Edit the file name by changing “1” to “2”.

b. If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically.

2. With the file IL_EX365_11B_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.

a. If cell B6 does not display your name, delete the file and download a new copy.

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:

a. Examine the formula in cell H6, which uses a defined name to calculate the discounted price.

b. Assign the same defined name (Contract_Nashville) to the range D6:D14, which resolves the errors in column H.

c. In cell F6, enter a formula without using a function to determine the one-year follow-up date by adding 365 to the defined name @Contract_Nashville. [Hint: To avoid an #SPILL error, enable implicit intersection by placing the This Row specifier (@ character) in front of the defined name.]

d. Fill the range F7:F14 with the formula in cell F6.

9. In the range J6:M10, Dario wants you to list data about new potential customers who have not signed a contract yet. On a separate worksheet named New Clients, he imported the data, but it did not appear in the correct format. For example, the company names appear in all lowercase, though each name should begin with an uppercase letter. Examine the imported data on the New Clients worksheet, and then return to the Nashville worksheet to incorporate the imported company name data in the range J6:J10 using worksheet references as follows:

a. In cell J6 on the Nashville worksheet, enter a formula using the PROPER function to capitalize the first letter in each word in the Company text in cell B3 of the New Clients worksheet.

b. Fill the range J7:J10 with the formula in cell J6 to show the company names.

10. The imported contact name data separates the first and last names, but Dario wants you to list the full name in one cell. List the first and last names of each contact in a single cell using worksheet references as follows:

a. In cell K6, enter a formula using the CONCAT function that displays the first name shown in cell C3 of the New Clients worksheet followed by a space (" "), and then the last name shown in cell D3 of the New Clients worksheet.

b. Fill the range K7:K10 with the formula in cell K6 to list the full names of the remaining contacts.

11. List the number of years in business and the state where the company is located using worksheet references as follows:

a. In cell L6, enter a formula using the LEFT function to insert the first 2 characters on the left of cell E3 of the New Clients worksheet.

b. Fill the range L7:L10 with the formula in cell L6.

c. In cell M6, enter a formula using the UPPER function to insert the uppercase state abbreviation from cell F3 of the New Clients worksheet.

d. Fill the range M7:M10 with the formula in cell M6.

12. Alliancy is considering expanding the business to open an office in Austin, Texas. Dario asks you to help analyze the three options for loans to fund the expansion. Go to the Loan Details worksheet, and then calculate the monthly payment for each loan as follows, which will update the total payments and total interest calculations:

a. In cell G4, begin to enter a formula using the PMT function to calculate the monthly payment on the first loan.

b. Use relative references to cells E4, F4, and C4 as the arguments.

c. Divide the interest rate in cell E4 by 12 to determine the monthly interest rate.

d. Make the loan amount in the pv argument negative to display the monthly payment as a positive number.

e. Copy the formula in cell G4 to the range G5:G6 to determine the monthly payments for the other two loans.

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the website to submit your completed project.

Final Figure 1: Charlotte Worksheet

Final Figure 2: Nashville Worksheet

Final Figure 3: New Clients Worksheet

Final Figure 4: Loan Details Worksheet